L21 - Logical Design 2

Question 1

How does … Binary Relationships work

Answer 1

• Requires a separate table to represent the relationship
  
  • Any relationship attributes
• PKs of both entities are copied to the new table to act as FKs
• One or both FKs form new table PK
  
  • Can combine with another attribute
  • If required

Question 2

How does realtionships with attributes work?

Answer 2

• If relationships has attributes
• They should be added to the child table

Question 3

How does multi valued attributes work?

Answer 3

• 1..*
• Create separate tables to deal with
  
  • Creating the parent
  • Leaving the multi valued attribute as the child
    
    • Which uses the parent PK as a FK

Question 4

How does complex realtionships work?

Answer 4

• Ternary|Higher - new table needed to rep relationship
  
  • PKs from each entity is copied
  • To act as FKs
    
    • One or more FKs act as primary key
  • • Any relationship attributes

Question 5

What are weak entities?

Answer 5

• Entities with a insufficient PK
• Meaning entity needs to inherit two foreign keys to form a PK

Question 6

What are the different steps in lofical database design?

Answer 6

Question 7

What is the third step in logical datbase design?

Answer 7

2.3 - Checking tables have user transaction support

• Ensure all tables support required transaction
• Check transaction pathways
• Make sure nothing has been lost in translation from ER to DB
• For each translation make sure:
  
  • Data available in tables
  • If in more than one table
    
    • Tables are linked through PK/FK

Question 8

What is the fourth step in logical database deisgn?

Answer 8

2.4 - Checking Integrity Constraints

• Rules imposed to protect an DB from being:
  
  • Incomplete
  • Inaccurate
  • Inconsistent
• Through following
  
  1. Required data - is nulls allowed
  2. Domain constraints
  3. Entity integrity - no null PK
  4. Multiplicity - cardinality, participation

Question 9

What is referential integrity

Answer 9

• Each FK column must refer back to an existing record in a parent table
• FK ability to be null depends on the participation of a child table
  
  • If child participation MANDATORY
  • Null is not allowed
• Ensured through
  
  • Insert/Update/Delete operation existence constrains on PKs and FKs
  • Rules are set for these operations
    
    • If record is deleted from parent table there could be
      
      • NO action
      • CASCADE - delete all child tables referencing it
      • SET NULL - set child record to null
      • SET DEFAULT - set child record back to default values
      • NO CHECK - do nothing

Question 10

What is the fith step in logical database design?

Answer 10