ER-to-Relational Mapping and DDL

CLO1: Define the "database”, its architecture, the main concepts and characteristics of databases, as well as concepts related to the design, and implementation of a relational databases. (Knowledge & Understanding) CLO3: Create a logical database design based on the conceptual ER model.

(Skills) Chapter objectives:

Learn ER/EER mapping to relational model. This includes mapping all the following:

 Regular/weak Entity Types  Binary 1:1, 1:N, M:N relationship types  Multivalued attributes.

 N-ary relationship types.

 Specialization/Generalization and union types.

 Preserve all information (that includes entities, relationships and all

attributes)

 Maintain the constraints as much as possible (Relational Model

cannot preserve all constraints- e.g., max cardinality ratio such as 1:10 in ER; exhaustive classification into subtypes, e.g., STUDENTS are specialized into Domestic and Foreign).

 Minimize null values.

ER-to-Relational Mapping Algorithm 

Step 1: Mapping of Regular Entity Types



Step 2: Mapping of Weak Entity Types



Step 3: Mapping of Binary 1:1 Relation Types



Step 4: Mapping of Binary 1:N Relationship Types.



Step 5: Mapping of Binary M:N Relationship Types.



Step 6: Mapping of Multivalued attributes.



Step 7: Mapping of N-ary Relationship Types.

Example:

CREATE TABLE CUSTOMER(

Customer_id number(4) NOT NULL, CustomerName varchar(30) NOT NULL, CustomerAddress varchar(50), PRIMARY KEY (Customer_id)

);

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) );

CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int,

PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

); OR you can name the constraint:

CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int,

PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

CREATE TABLE Employee ( ID int NOT NULL, EmployeeName varchar(255) NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE DEPENDENT ( Name NOT NULL, EmployeeID int NOT NULL, DateOfBirth date, Gender varchar(1) DEFAULT ‘f’,

FOREIGN KEY (EmployeeID) REFERENCES Employee(ID) CONSTRAINT PK_Dependent PRIMARY KEY (EmployeeID,Name)

);

ALTER command:

ALTER TABLE name_of_table ADD column_name column_definition; Add column, change column, etc.

DROP command:

DROP TABLE EMPLOYEE; Drop table or drop database Other commands:

TRUNCATE TABLE Table_Name; //clears table of all tuples RENAME TABLE Old_Table_Name TO New_Table_Name; //renames table

Attribute constraints:



• NOT NULL the column must have a value 
• UNIQUE the column value must be unique in the table 
• CHECK the column value conforms to an arbitrary condition

Foreign key conditions:

• FOREIGN KEY(fk) REFERENCES refrenced_table(pk) ON DELETE CASCADE / ON UPDATE CASCADE
• FOREIGN KEY(fk) REFERENCES refrenced_table(pk) ON DELETE SET NULL

Step 3: Mapping of Binary 1:1 Relation Types

 For each binary 1:1 relationship type R in the ER

schema, identify the relations S and T that correspond to the entity types participating in R.

There are three possible approaches:

1. Foreign Key (2 relations) approach
2. Merged relation (1 relation) option
3. Cross-reference or relationship relation (3 relations) option

What will happen if the primary key of S is included as a foreign key in T?

In the previous example, the S relation is DEPARTMENT and T relation is EMPLOYEE. Thus, the primary key of S is Dept_managed will be the foreign key for the EMPLOYEE table. But it will have NULL values for employee tuples who do not manage a department. If only 10% of employee manage a department, then 90% of the foreign keys would be NULL in this case.

Another possibility is to have foreign keys in both relations S and T redundantly, but this incurs a penalty for consistency

Merged relation option:

Merging the two entity types and the relationship into a single relation. (Used when both participations are total)

Cross-reference or relationship relation option: (Both participations are partial) Setting up a third relation R for the purpose of cross-referencing the primary keys of the two relations S and T representing the entity types.

The relationship R is called a relationship relation (or sometimes a lookup table), because each tuple in R represents a relationship instance that relates one tuple from S with one tuple of T.

The primary key of the relationship relation will be either one of the foreign keys that reference the participating entity relations.

This alternative is useful when few relationship instances (i.e. partial participation) exist to avoid NULL values in foreign keys.

Step 4: Mapping of Binary 1:N Relationship Types.

 For each regular binary 1:N relationship type R, identify the relation S that

represent the participating entity type at the N-side of the relationship type.

 Include as foreign key in S the primary key of the relation T that

represents the other entity type participating in R.

 Include any simple attributes of the 1:N relationship type as attributes of

S.

Example: 1:N relationship type: WORKS_FOR For WORKS_FOR we include the primary key DNUMBER of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and call it DNO.

An alternative approach is to use a Relationship relation (cross referencing

Map the locations attribute into a relation DEPT_LOCATIONS.

Include the primary key and foreign keys.

Write the DDL for this specific relation.

Step 7: Mapping of N-ary Relationship Types.



For each n-ary relationship type R, where n>2, create a new relationship S to represent R.



Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types.



Also include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S.

Example: The relationship type SUPPLY in the ER on the next slide.



This can be mapped to the relation SUPPLY

shown in the relational

schema, whose primary key is the combination of the three foreign keys {SNAME, PARTNO, PROJNAME}



The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types.

 the cardinality constraints on any of the entity types E

If participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E’ corresponding to E.

1:N unary relationships:

1. With this type of relationship, ONE relation is created: one entity represents the entity type in the relationship consisting of PK and FK in the same relation. The FK is referenced to the PK of the same relation
2. Any non key attribute of the relationship is included in the relation.

N:M unary relationships:

1. With this type of relationship, two relations are created: one to represent the entity type in the relationship and the other an associative relation to represent the M:N relationship.
2. The primary key of the associative relation consists of two attributes. These attributes (which need not to have the same name) both take their values from the primary key of the other relation. Any non key attribute of the relationship is included in the associative relation

Step8: Options for Mapping Specialization or Generalization.

 Convert each specialization with m subclasses {S1, S2,....,Sm} and

generalized superclass C, where the attributes of C are {k,a1,...an} and k is the (primary) key, into relational schemas using one of the four following options:

 Option 8A: Multiple relations-Superclass and subclasses  Option 8B: Multiple relations-Subclass relations only  Option 8C: Single relation with one type attribute  Option 8D: Single relation with multiple type attributes

Option 8A: Multiple relations-Superclass and subclasses Create a relation L for C with attributes Attrs(L) = {k,a1,...an} and PK(L) = k. Create a relation Li for each subclass Si, 1 < i < m, with the attributesAttrs(Li) = {k} U {attributes of Si} and PK(Li)=k.

This option works for any specialization (total or partial, disjoint or over -lapping).

Option 8B: Multiple relations-Subclass relations only Create a relation Li for each subclass Si, 1 < i < m, with the attributes Attr(Li) = {attributes of Si} U {k,a1...,an} and PK(Li) = k.

This option only works for a specialization whose subclasses are total (every entity in the superclass must belong to (at least) one of the subclasses).

Next Slide: Options for mapping specialization or generalization.

(a) Mapping the EER schema using option 8A.

(b) Mapping the EER schema using option 8B.

(c) Mapping the EER schema using option 8C.

(d) Mapping EER schema using option 8D with Boolean type fields Mflag and Pflag.

Step 9: Mapping of Union Types (Categories).

 For mapping a category whose defining superclass have

different keys, it is customary to specify a new key attribute, called a surrogate key, when creating a relation to correspond to the category.

 In the example below we can create a relation OWNER to

correspond to the OWNER category and include any attributes of the category in this relation. The primary key of the OWNER relation is the surrogate key, which we called OwnerID.

Step 1: strong entities SHIP (Sname, Owner) SHIP_TYPE (Type, Tonnage, Hull) STATE/COUNTRY (Name, Continent) SEA/OCEAN/LAKE (Name) Step 2: weak entities SHIP_MOVEMENT (ShipName (FK), Date, time, latitude, longitude) PORT (SCName (FK), SOLName (FK), Pname) VISIT (Pname, SCName (FK) , Sname (FK) , StartDate, EndDate)

ER-to-Relational Mapping Algorithm 

Step 1: Mapping of Regular Entity Types



Step 2: Mapping of Weak Entity Types



Step 3: Mapping of Binary 1:1 Relation Types



Step 4: Mapping of Binary 1:N Relationship Types.



Step 5: Mapping of Binary M:N Relationship Types.



Step 6: Mapping of Multivalued attributes.



Step 7: Mapping of N-ary Relationship Types.

Mapping EER Model Constructs to Relations 

Step 8: Options for Mapping Specialization or Generalization.



Step 9: Mapping of Union Types (Categories).

Some DDL basics sprinkled in. Next: Full fledged SQL!