Database Design and Development A Visual Approach

by Frost, Raymond; Day, John; Van Slyke, Craig

• ISBN13: 9780130351227
• ISBN10: 0130351229
• Edition: 1st
• Format: Paperback
• Copyright: 2005-10-24
• Publisher: Pearson

Summary

For students in the introductory course in database who want to learn how to design rather than just manipulate relational databases.The book that balances database theory, business problem solving, and hands-on-practice. This book prepares student for the workplace without sacrificing rigorous academic theory.

Table of Contents

Preface	xv
The Role of Databases in Electronic Business	1	(14)
Learning Objectives	1	(1)
Introduction	1	(1)
Components of an Organizational System	2	(6)
Network	2	(1)
Hardware	2	(2)
Software	4	(1)
Content	5	(3)
What Is a Database?	8
Why Are Databases Important to Business?	7	(3)
General Business Example	7	(1)
E-business Example	8	(2)
Organization of the Text	10	(1)
Summary	11	(1)
Key Terms	11	(1)
Exercises	12	(3)
Review	12	(1)
Discuss	12	(1)
Practice	12	(3)
Relational Theory	15	(14)
Learning Objectives	15	(1)
Introduction	15	(1)
Relational Model	16	(1)
Relational Database Management System (RDBMS)	16	(1)
Database Administrator (DBA)	16	(1)
Entity Relationship Diagrams	16	(6)
Primary Keys	18	(2)
Creating Relationships: Foreign Keys	20	(1)
Data Integrity	21	(1)
First Normal Form	22	(1)
Types of Database Systems	22	(3)
Relational Advantages	24	(1)
Logical Rather Than Physical Links	24	(1)
Fourth-Generation Language (4GL)	24	(1)
Data Independence	24	(1)
Summary	25	(1)
Key Terms	26	(1)
Exercises	27	(1)
Review	27	(1)
Discuss	27	(1)
Practice	27	(1)
References	27	(2)
Conceptual Design	29	(38)
Learning Objectives	29	(1)
Introduction	29	(2)
Step 1: Represent Entities as Tables	29	(1)
Step 2: Determine Relationships	30	(1)
Step 3: List Fields	30	(1)
Step 4: Identify Keys	30	(1)
Step 5: Determining Data Types	31	(1)
Designing a Single-Table Database	31	(2)
Problem Statement	31	(1)
Process	32	(1)
Designing a Database with a One-to-Many Relationship	33	(3)
Problem Statement	33	(1)
Process	33	(3)
Database with Many-to-Many Relationships	36	(6)
Problem Statement	36	(6)
Other Multitable Database Examples	42	(18)
The Hospital Database	43	(5)
The ACME Database	48	(5)
The Amazon Database	53	(7)
Summary	60	(1)
Key Terms	61	(1)
Exercises	61	(6)
Review	61	(1)
Discuss	62	(1)
Practice	62	(5)
Normalization	67	(22)
Learning Objectives	67	(1)
Introduction	67	(1)
Normal Forms	68	(1)
Denormalized Designs	68	(1)
Normalizing the Arcade Database	68	(4)
Update Problem	70	(1)
Insert Problem	71	(1)
The Normal Forms	72	(14)
First Normal Form (1NF)	73	(1)
Determinants	74	(1)
Second Normal Form (2NF)	75	(2)
Third Normal Form (3NF)	77	(3)
Boyce-Codd Normal Form (BCNF)	80	(3)
Fourth Normal Form (4NF)	83	(2)
Detecting Normalization Violations	85	(1)
Summary	86	(1)
Key Terms	86	(1)
Exercises	86	(3)
Review	86	(1)
Discuss	87	(1)
Practice	87	(2)
Advanced Database Designs	89	(30)
Learning Objectives	89	(1)
Introduction	89	(1)
Recursive Relationships	90	(5)
An Example Recursive Relationship	90	(1)
Creating the Mentor Recursive Relationship	91	(1)
A Many-to-Many Recursive Relationship	92	(3)
Hierarchies: Supertypes and Subtypes	95	(6)
A Supertype/Subtype Hierarchy Example	97	(3)
A Second Supertype/Subtype Hierarchy Example	100	(1)
Complex Designs	101	(12)
Problem Statement: Summer Reading Fun	101	(6)
Problem Statement: Swampland Real Estate	107	(6)
Summary	113	(1)
Key Terms	114	(1)
Exercises	114	(5)
Review	114	(1)
Discuss	115	(1)
Practice	115	(4)
Creating Databases with Oracle Using SQL	119	(26)
Learning Objectives	119	(1)
Introduction	119	(1)
Physical Table Design	120	(1)
Data Types and Column Widths	120	(1)
The Sky Member Database	120	(7)
Reserved Words	121	(1)
Implementation	122	(5)
The Arcade Database	127	(3)
The Enrollment Database	130	(4)
Creating a Concatenated Key	132	(1)
Referential Integrity Controls the Order of Drop and Create	132	(2)
Troubleshooting	134	(3)
Invalid Identifer	135	(1)
Missing or Invalid Option	136	(1)
Invalid User.table.column, Table.column, or Column Specification	136	(1)
Missing Expression	137	(1)
Integrity Constraint Violated -- Parent Key Not Found	137	(1)
Summary	137	(1)
Key Terms	138	(1)
Exercises	138	(7)
Review	138	(1)
Discuss	138	(1)
Practice	139	(6)
Retrieving Data with Oracle	145	(58)
Learning Objectives	145	(1)
Introduction	145	(1)
Basic Retrieval Operations in SQL	146	(23)
Displaying All Columns and Rows	146	(2)
Using a Column List	148	(1)
Sorting with Order By	149	(2)
Sorting on Multiple Columns	151	(2)
Selecting Rows with Where	153	(2)
Where Clauses Involving Text Data	155	(1)
The LIKE Operator	155	(1)
Complex Where Clauses	156	(4)
Multiple Conditions on the Same Column	160	(2)
Using the IN Operator	162	(1)
Using the Between Operator	163	(1)
The NOT Operator	164	(1)
Combining AND and OR	165	(4)
Aggregate Functions	169	(5)
The Count Function	171	(1)
Using Where with Aggregate Functions	172	(2)
Grouping Data in a Query	174	(7)
Using Distinct	174	(1)
Grouping and Aggregate Functions	174	(3)
Limiting Groups with the Having Clause	177	(2)
Combining Having and Where	179	(2)
Combining Data from Different Tables	181	(8)
Combining Joins with Other Where Conditions	184	(3)
Joining More Than Two Tables	187	(2)
Subqueries	189	(5)
Summary	194	(1)
Key Terms	195	(1)
Exercises	195	(8)
Review	195	(1)
Discuss	196	(1)
Practice	196	(7)
Creating Databases with Microsoft Access	203	(28)
Learning Objectives	203	(1)
Introduction	203	(1)
Physical Table Design	204	(1)
Data Types and Column Widths	204	(1)
The Sky Member Database	205	(9)
Creating an Access Database with a Single Table	206	(6)
Using a Query to Display Table Data	212	(2)
The Arcade Database	214	(4)
Create the Database and Tables	215	(1)
Create the Relationship Between the Tables	215	(2)
Entering the Data for the Arcade Database	217	(1)
Displaying the Data	218	(1)
The Enrollment Database	218	(4)
Creating the Enroll Database	221	(1)
Creating a Concatenated Key	221	(1)
Relationships for the Enroll Database	221	(1)
Summary	222	(1)
Key Terms	223	(1)
Exercises	223	(8)
Review	223	(1)
Discuss	223	(1)
Practice	224	(7)
Retrieving Data with Microsoft Access	231	(66)
Learning Objectives	231	(1)
Introduction	231	(1)
Basic Retrieval Operations in SQL	232	(30)
Displaying All Columns and Rows	232	(4)
Using a Column List	236	(2)
Sorting with Order By	238	(2)
Sorting on Multiple Columns	240	(2)
Selecting Rows with Where	242	(2)
Where Clauses Involving Text Data	244	(1)
The LIKE Operator	245	(1)
Complex Where Clauses	246	(6)
Multiple Conditions on the Same Column	252	(5)
The NOT Operator	257	(1)
Combining AND and OR	258	(4)
Aggregate Functions	262	(4)
The COUNT Function	264	(1)
Using Where with Aggregate Functions	264	(2)
Grouping Data in a Query	266	(8)
Using DISTINCT	266	(1)
Grouping and Aggregate Functions	267	(3)
Limiting Groups with the Having Clause	270	(2)
Combining Having and Where	272	(2)
Combining Data from Different Tables	274	(10)
Combining Joins with Other Where Conditions	278	(2)
Joining More Than Two Tables	280	(4)
Subqueries	284	(5)
Summary	289	(1)
Key Terms	290	(1)
Exercises	291	(6)
Review	291	(1)
Discuss	291	(1)
Practice	292	(5)
Creating Databases with Microsoft SQL Server	297	(36)
Learning Objectives	297	(1)
Introduction	297	(1)
Physical Table Design	298	(2)
Data Types and Column Widths	298	(2)
The Sky Member Database	300	(13)
Creating an SQL Server Database with a Single Table	301	(10)
Using Query Analyzer to Insert Data	311	(2)
The Arcade Database	313	(5)
Create the Database and Tables	314	(2)
Create the Relationship Between the Tables	316	(1)
Entering the Data for the Arcade Database	317	(1)
The Enrollment Database	318	(6)
Creating a Concatenated Key	321	(1)
Relationships for the Enroll Database	321	(3)
Summary	324	(1)
Key Terms	325	(1)
Exercises	325	(8)
Review	325	(1)
Discuss	325	(1)
Practice	325	(8)
Retrieving Data with SQL Server	333	(74)
Learning Objectives	333	(1)
Introduction	333	(1)
Basic Retrieval Operations in SQL	334	(1)
Displaying All Columns and Rows	335	(32)
The SQL Server Query Analyser	337	(1)
Using a Column List	338	(3)
Sorting with Order By	341	(3)
Sorting on Multiple Columns	344	(2)
Selecting Rows with Where	346	(2)
Where Clauses Involving Text Data	348	(1)
The LIKE Operator	349	(1)
Where Clauses with Multiple Conditions	350	(6)
Multiple Conditions on the Same Column	356	(3)
Using the IN Operator	359	(1)
Using the BETWEEN Operator	360	(1)
The NOT Operator	361	(2)
Combining AND and OR	363	(4)
Aggregate Functions	367	(12)
Creating Column Aliases	371	(2)
Using DISTINCT	373	(1)
Creating Subaggregations with Group By	374	(3)
Limiting Groups with the Having Clause	377	(2)
Combining Data from Different Tables	379	(13)
Errors Resulting from Omitting the Join Condition	382	(1)
Combining Joins with Other Where Conditions	383	(4)
Joining More Than Two Tables	387	(5)
Subqueries	392	(7)
Summary	399	(1)
Key Terms	399	(1)
Exercises	400	(7)
Review	400	(1)
Discuss	400	(1)
Practice	400	(7)
Accessing Databases from Web Applications with Microsoft ASP.NET	407	(54)
Learning Objectives	407	(1)
Introduction	407	(1)
What Is ASP.NET?	408	(2)
ASP.NET Web Forms	408	(2)
An Example Web Application	410	(8)
An Introduction to Web Developer Express	410	(1)
Creating a Web Site	411	(7)
Example 1: Displaying the Contents of a Table	418	(13)
Creating the SqlDataSource Control	419	(1)
Selecting the Data Provider	420	(1)
Setting up a Data Connection	421	(1)
Choosing a Connection for the Data Source	422	(2)
Configuring the Data Source	424	(1)
Creating a GridView Control	425	(2)
Finishing the Application	427	(1)
Setting Up a Connection to an Oracle Database	428	(3)
Example 2: Displaying Single Records from a Table	431	(9)
Creating the Web Site and Adding the Database	431	(2)
Adding the Textbox	433	(1)
Adding and Configuring the Data Source	433	(2)
Creating the Query for the Data Source	435	(1)
Setting up the Where Clause for the Query	436	(1)
Adding the GridView	437	(1)
Finishing the Application	438	(2)
Example 3: Retrieving Data with Multiple Conditions	440	(11)
Summary	451	(1)
Key Terms	452	(1)
Exercises	453	(8)
Review	453	(1)
Discuss	453	(1)
Practice	453	(8)
Maintaining Databases from Web Applications with Microsoft ASP.NET	461	(34)
Learning Objectives	461	(1)
Introduction	461	(1)
The DetailsView Server Control	462	(1)
Creating a Member Maintenance Web Application	462	(18)
Configuring the Data Source	463	(2)
Configuring the DetailsView Control	465	(1)
Adding the Ability to Insert New Rows	466	(8)
Adding the Ability to Update Rows	474	(3)
Adding the Ability to Delete Rows	477	(3)
Automatically Generating Insert, Update, and Delete Statements	480	(2)
Creating a Member Maintenance Application with a GridView	482	(3)
Summary	485	(1)
Key Terms	486	(1)
Exercises	486	(9)
Review	486	(1)
Discuss	487	(1)
Practice	487	(8)
Glossary	495	(4)
Index	499