Chapter 5 - Database Design

Question 1

What is Database Design?

Answer 1

A set of database specifications that can actually be implemented in a DBMS.

Question 2

What are the Three (3) Database Designs?

Answer 2

Conceptual Design (or Schema)
Logical Design: Modified conceptual design implemented in a specific DBMS
Physical Design: Includes aspects when implemented in the DBMS such as record, table structure, index, etc.

Question 3

What are the Two (2) Big Steps for Transforming a Data Model into a Database Design?

Answer 3

1. Create a Table for Each Entity
2. Create Relationships by Placing Foreign Keys

Question 4

What are the Sub-steps for Creating a Table for Each Entity?

Answer 4

Specify primary key
Specify properties for each column
Verify normalizations (Any functional dependencies)

Question 5

What are the Sub-steps for Creating Relationships by Placing Foreign Keys?

Answer 5

Strong entity relationships (1:1; 1:N; N:M)
ID-Dependent and non-ID-Dependent weak entity relationships
Subtypes
Recursive (1:1; 1:N; N:M)

Question 6

How do you Represent Weak Entities with the Relational Model?

Answer 6

If ID-Dependent, then you must add the primary key of the parent entity.
If not ID-Dependent, use the same techniques as for strong entities

Question 7

How do you Represent a 1:1 Relationship with the Relational Model?

Answer 7

The key from one relation is placed in the other as a foreign key. It does not matter which table gets the FK

Question 8

How do you Represent a 1:N Relationship with the Relational Model?

Answer 8

A 1:N relationship is saved by placing the key from one table into another as a foreign key.
The foreign key always goes into the many side of the relationship. The 1 side is called the parent and the N side is called the child

Question 9

How do you Represent a N:M Relationship with the Relational Model?

Answer 9

To create a N:M relationship, a new table is created. This is called the intersection table.
The intersection table has a composite key consisting of the keys from each table it connects.

Question 10

How do you Represent a Recursive Relationship with the Relational Model?

Answer 10

1:1 and 1:N relationships are saved by using a foreign key to itself.
N:M relationships are saved by creating an intersecting relation

Question 11

How does the HSD (Heather Sweeney Design) differ from the ER model in Ch. 4 to the Database Design in Ch. 5?

Answer 11

A new intersection table is created for SEMINAR_CUSTOMER and CONTACT connecting SEMINAR and CUSTOMER.

Foreign keys are added to the LINE_ITEM table, which is the many or child side to INVOICE and PRODUCT

Question 12

Know Cascading ramifications of On Update and On Delete (Parent - Child - Referential Integrity Constraint - Cascading Behavior)

Answer 12

INVOICE - LINE ITEM - InvoiceNumber in LINE_ITEM must exist in InvoiceNumber in INVOICE - Yes cascading On Delete

PRODUCT - LINE_ITEM - ProductNumber in LINE_ITEM must exist in ProductNumber in PRODUCT - Yes cascading On Update