or

• Total Specialization Rule: Yes (double line) 
  • 　　A patient has to be either an OUTPATIENT or a ResidentPatient
  •  　  MUST IN
  • 
• Partial Specialization Rule: No (single line)
  • 　　A vehicle can be a car, a truck, but can be other....
  •        CAN OUT
  •  

• Disjoint Rule: An instance of the supertype can be only ONE of the subtypes
  • 　　d: ONLY ONE
  • 
• Overlap Rule: An instance of the supertype could be more than one of the subtypes
  • 　　o: MAYBE BOTH
  • 

subtype discriminator: an attribute of the subtype whose values determine the target subtype

• disjoint - simple attribute
  • 　　
• overlapping - a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype
  • 　　

CHAPTER 4: LOGICAL DATABASE DESIGN AND THE RELATIONAL MODEL

• relations - entity types
• rows - entity instances
• columns - attrs 

INTEGRITY CONSTRAINTS

• Entity Integrity
  • 　　No primary key attribute may be null. All primary key fields MUST have data

• Action Assertions
  • 　　Business rules

Referential Integrity

• Restrict: don't allow delete of parent if dependent exists
• Cascade: allow delete of parent and auto delete dependent
• Set-To-Null: delete parent and set FK in dependent side to NULL (not allowed for weak entity)

TRANSFORMING EER DIAGRAMS INTO RELATIONS

Mapping Regular Entities to Relations 

Mapping Binary Relationships

Mapping Associative Entities 

• identifier assigned 
  • 　　PK differs from FK
  • 

  •  

    

• identifier not assigned
  • 　　PK + PK / FK + FK

Mapping Unary Relationships

• ONE TO MANY- RECURSIVE
  • 　　
• MANY TO MANY
  • 

  •  

     　One for entity type: ITEM; One for association relation with PK + PK

Mapping Ternary (and n-ary) Relationships

Mapping Supertype/Subtype Relationships

DATA NORMALIZATION

• avoid unnecessary duplication of data 
• decomposing relations to produce well-structured relations

A table should not pertain to more than one entity type -> 1st Normal Form

results in -> data duplication and an unnecessary dependency between the entities

 

FUNCTIONAL DEPENDENCIES AND KEYS

Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute

Candidate Key:

• A unique identifier. One of the candidate keys
  will become the primary key
• Each non-key field is functionally dependent on every candidate key(cuz it is unique)

FIRST NORMAL FORM

• No multivalued attributes
• Every attribute value is atomic 
• All relations are 1st Normal Form

SECOND NORMAL FORM

• 1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key

THIRD NORMAL FORM