Lecture 5: Database Design Review

Question 1

List the three database design

Answer 1

1. conceptual
2. logical
3. physical

Question 2

quick explanation of conceptual database design.

Answer 2

includes identification of the important:

1. entities
2. relationships
3. attributes

Question 3

quick explanation of logical database design.

Answer 3

translate the conceptual to logical structure which includes designing the relations:

Question 4

quick explanation of physical database design.

Answer 4

decide how the logical structure is to be physically implemented (as base relation) in the target DBMS. (first stage of considering issues such as data type)

Question 5

Name all relational keys plus three extra keys

Answer 5

1. superkey
2. candidate key
3. primary key
4. foreign key
5. composite key
6. alternative (secondary)
7. surrogate key
8. simple key
9. compound key

Question 6

What is a surrogate key?

Answer 6

used by develops when the PK is considered unsuitable

created and managed via the DBMS

have a numeric value which is automatically incremented for each new row

Question 7

List and explain each constraint

Answer 7

1. entity integrity:
   no attribute of a primary key can be NULL
2. referential integrity:
   if a foreign key exist, either the value of the foreign key matches the value of the candidate key in the home relation or the value of the foreign key is NULL
3. domain constraints:
   the value of each attribute in each tuple must be from the domain assigned to this attribute
4. primary key constraints:
   two distinct tuples in any state of the relation cannot have identical values for all attributes in the key.

Question 8

Quickly explain the conceptual stage of the database design

Answer 8

Step 1. build conceptual data model

• identify entity types
• identify relationship types
• identify and assosciate attributes with entity or relationship types
• determine attribute domains
• determine CK, PK, Alternate key attributes
• determine multiplicity
• validate conceptual model against user transactions

Question 9

List the logical stage of the database design

Answer 9

Step 2 build and validate logical data model

• derive relations for the logical data model
• validate relations using norms.
• define integrity constraints
• check for future growth

Question 10

List the physical stage of the database design

Answer 10

Step 3: translate the logical data model to MySQL

• design base relations
• design representation of derived data
• design general constraints
• create indexes
• create views
• define security requirements
• consider the intro of controlled redundancy