How to convert ER diagrams into relation schemas

flashcards made from Relational Model slide pages 23-end from week 4

How to convert ER diagrams into relation schemas?

-A database which conforms to an E-R diagram can be represented by a collection of relation schemas.

-For each entity-set and each relationship-set in the E-R diagram there is a unique relation (table) schema that is assigned the name of the corresponding entity-set or relationship-set.

-Constraints arising from E-R design can be mapped to constraints on relation schemas.

Strong entity sets

Let E be a strong entity set with attributes a₁, a₂, …, a_n.

Representation: we represent this entity by a relation scheme called E with n distinct attributes.

Each tuple in a relation on this schema corresponds to one entity of the entity set E.

Primary Key: the primary key of the entity set serves as the primary key of the resulting schema.

weak entity sets

•A: a weak entity set with attributes a₁, a₂,…, a_m.

•B: a strong entity set on which A depends.

•b₁, …, b_n : the primary key of B.

Representation: We represent the entity set A by a relation schema A with the following attributes:

  {a₁, a₂,…, a_m } U { b₁, …, b_n }

Primary key: primary key of the strong entity set together with the partial-key of the weak entity set.

Foreign Key Constraints: We also have a foreign key constraint on relation A specifying that the attributes b₁, …, b_n reference the primary key of B

relationship sets

Simply build a table where attributes are the primary keys of each of the participating entities!

(and any attributes of the relationship)

---

R: a relationship set.

•a₁, a₂, …, a_m : set of attributes formed by the union of the primary keys of each entity set participating in R,

•Descriptive attributes of R: b₁, b₂, …, b_n (if any).

Representation: a relation schema called R with one attribute for:

   {a₁, a₂,…, a_m} U {b₁, …, b_n}

relationship sets- primary keys

–Binary many-to-many relationship: the union of the primary key attributes from the participating entity sets becomes the primary key

–Binary one-to-one relationship: the primary key of either entity set can be chosen as the primary key for the relationship

relationship sets- primary keys part 2

–Binary many-to-one or one-to-many relationship: the primary key of the entity set on the “many” side of the relationship set serves as the primary key

–N-ary relationships without arrows: the union of the primary key attributes from the participating entity sets becomes the primary key

–N-ary relationships with one arrow: the primary keys of the entity sets not on the “arrow side” of the relationship set serve as the primary key for the schema.

relationship sets- foreign key constraints

•In ER diagram: E₁,…,E_k participates in relationship-set R

•Then, create a foreign key constraint from table-schema R with:

•Each primary-key in R (that was derived from primary-key of E_i) referencing the primary-key of the table-schema for E_i.

Simply reference all the primary-keys you took to their original table!

Combination of schemas

Combination of Schemas: one-to-one

•The schema for the relationship set linking a weak entity-set to its corresponding strong entity set is redundant (i.e., can be discarded)

•Why?? Because we already have the primary key of loan and the partial key of payment in the schema for Payment!

Combination of Schemas: many-to-one

Composite attributes

•Composite attributes are “flattened out” by creating a separate attribute for each component attribute

• Example: given entity set customer with composite attribute name with component attributes first_name and last_name the schema corresponding to the entity set has two attributes

        name.first_name  and name.last_name

Multi-valued Attributes + example

•Each multi-valued attribute M of an entity E is represented by a new separate schema EM.

•EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M

  Example:  Multi-valued attribute dependent_names entity  employee:
    employee_dependent_names = ( employee_id, d_name)

  Each value of the multivalued attribute maps to separate tuples EM

  Example: employee entity with primary key 103 and dependents Jack and Jane maps to two tuples:  
   (103 , Jack) and (103 , Jane)

Primary key of EM: we create a primary key of the relation schema consisting of all attributes of the schema.

Sub-classes method 1 + example

Method 1

•Form a schema for the higher-level entity-set

•Form a schema for each lower-level entity-set, that includes:

•primary key of higher-level entity set; and

•local attributes.

The primary key attributes of the higher level entity set become primary key attributes of both higher level and lower level entity sets.

Example:      
     person = (person_id, street, city)
     customer = (person_id, credit_rating)
     employee = (person_id, salary)

Sub-classes method 2

Method 2: only in case sub-classes are disjoint and total

•For each lower level entity set create a schema that includes an attribute for each of the attributes of that entity set plus one for each attribute of the higher level entity set. Don’t create schema for higher-level entity

•Example:

employee = (person_id, name, street, city, salary)

customer = (person_id, name, street, city, credit_rating)

Drawback: if there is a relationship set involving the higher level entity set, we do not have a single table to which a foreign key constraint can refer.