MondayQuiz

• Relational model


  A model for database design that represents data as relations (tables) with rows (records) and columns (attributes)

  
• Relation (table)


  • A two-dimensional table of data
  • Consists of rows(records) and columns(attributes or field)
  • Has a unique name
  • Every attribute value must be atomic (not multivalued, not composite)
  • Every row must be unique (can't have two rows with exactly the same values for all their fields)
  • Attributes (columns) must have unique names
  • The order of the columns must be irrelevant
  • The order of the rows must be irrelevant
• Relations (tables)


  Correspond with entity types and with many-to-many relationship types in the E-R model
• Rows


  Correspond with entity instances and with many-to-many relationship instances in the E-R model
• Columns


  Correspond with attributes in the E-R model
• Key fields


  Special fields that serve two main purposes: 1) Primary keys are unique identifiers of the relation, 2) Foreign keys enable a dependent relation to refer to its parent relation
• Primary keys


  • Unique identifiers of the relation, examples include employee numbers, social security numbers, etc.
• Foreign keys


  • Identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship)
• Keys


  • Can be simple (a single field) or composite (more than one field)
  • Usually used as indexes to speed up response to user queries
• Domain constraints


  Allowable values for an attribute
• Entity integrity


  No primary key attribute may be null. All primary key fields MUST have data.
• Referential integrity


  Any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side, or the foreign key can be null
• Referential integrity constraints


  • Delete rules: 1) Restrict - don't allow delete of "parent" side if related rows exist in "dependent" side,
  • 
    
  • 2) Cascade - automatically delete "dependent" side rows that correspond with the "parent" side row to be deleted,
  • 
    
  • 3) Set-to-Null - set the foreign key in the dependent side to null if deleting from the parent side (not allowed for weak entities)
• Transforming EER diagrams to relations


  1. Mapping regular entities to relations

  2. Mapping composite attributes

  3. Mapping multivalued attributes

  4. Mapping weak entities

  5. Mapping binary relationships (1:M, M:N, 1:1)

  6. Mapping associative entities

  7. Mapping unary relationships

  8. Mapping ternary (and n-ary) relationships

  9. Mapping supertype/subtype relationships
• All relations are in 1st Normal Form
• Data Structure - Tables(relations), rows, columns
• Data Manipulation - Powerful SQL operations for retrieving and modifying data
• Data Integrity - Mechanisms for implementing business rules that maintain integrity of manipulated data
• The word relation (in relational databse) is NOT the same as the relationship (in E-R model)
• Simple attributes: E-R attributes map directly onto the relation
• Composite attributes: Use only their simple, component attributes
• Multivalued attribute: Becomes a separate relation with a foreign key taken from the superior entity
• Mapping Weak Entities: Becomes a separate relation with a foreign key taken from the superior entity

  
  

  Primary Key consist of:

  - Partial identifier of weak entity

  - Primary key of identifying relation(Strong entity)
• Mapping Binary Relationships

   One to Many Primary key on the one side

  becomes a foreign key on the many side

   Many to Many Create a new relation with the

  primary keys of the two entities as its primary key

   One to One Primary key on mandatory side

  becomes a foreign key on optional side
• Mapping Associative Entities

  If Identifier is not assigned:

  - default primary key for the association relation is composed of the primary keys of the two entities

  If Identifier is Assigned

  - it is natural and familiar to end-users

  - Default identifier may not be unique
• Mapping Unary Relationships

  One-to-Many: Recursive foreign key in the same relation

  Many to many two relations:

  - One for the entity type

  - One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity
• One relation for supertype and for each subtype
• Supertype attributes (including identifier and subtype

  discriminator) go into supertype relation
• Subtype attributes go into each subtype; primary key of

  supertype relation also becomes primary key of subtype

  relation
• 1:1 relationship established between supertype and each

  subtype, with supertype as primary table
• Entity supertype

  ◆Generic entity type related to one or more entity subtypes

  ◆Contains common characteristics
• Entity subtype

  ◆Contains unique characteristics of each entity subtype
• Relationships at the supertype level indicate that all subtypes will participate in the relationship
• Inheritance:

  Enables entity subtype to inherit attributes and relationships of the supertype

  All entity subtypes inherit their primary key attribute from their supertype

  At the implementation level, the supertype and its subtype(s) maintain a 1:1

  relationship

  Entity subtypes inherit all relationships in which the supertype entity participates

  Lower level subtypes inherit all attributes and relationships from all upper level

  supertypes
• Specialization

  •Identifies more specific entity subtypes from higher level entity supertype

  •Top down process

  •Based on grouping unique characteristics and relationships of the subtypes
• Generalization

  •Identifies more generic entity supertypes from lower level entity subtypes

  •Bottom up process

  •Based on grouping common characteristics and relationships of the

  subtypes
• Specialization Hierarchy

  Depicts arrangement of higher level entity supertypes and lower level entity

  subtypes

  Relationships described in terms of “IS A” relationships

  Subtype exists only within the context of the supertype

  Every subtype has only one supertype to which it is directly related

  Can have many levels of supertype/subtype relationships
• Completeness Constraints:

  Specifies whether entity supertype occurrence must be a member of at least one subtype
• Partial Completeness:

  • Symbolized by a circle over a single line
  • Some supertype occurrences are not members of any subtype
• Total completeness:

  • Symbolized by a circle over a double line
  • Every supertype occurence must be a member of at least one subtype