E r model to r elational model
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

E-R Model to R elational Model PowerPoint PPT Presentation


  • 91 Views
  • Uploaded on
  • Presentation posted in: General

E-R Model to R elational Model. E-R Model to R elational Model. Entity Sets to Tables Main Idea Each entity set maps to a new table Each attribute maps to a new table column Each relationship set maps to either new table columns or to a new table.

Download Presentation

E-R Model to R elational Model

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


E r model to r elational model

E-R Model to Relational Model


E r model to r elational model1

E-R Model to Relational Model

  • Entity Sets to Tables

  • Main Idea

  • Each entity set maps to a new table

  • Each attribute maps to a new table column

  • Each relationship set maps to either new table columns or to a new table


E r model to r elational model2

E-R Model to Relational Model

  • Entity Sets to Tables

  • (Representing Strong Entity Sets)

  • Entity set E with attributes a1,..,antranslates to table Ewith attributes a1,a2..an

  • Primary key of entity set

  • primary key of table


E r model to r elational model3

E-R Model to Relational Model

  • example


E r model to r elational model4

E-R Model to Relational Model

  • Weak entity set E translates to table E

  • A weak entity can be identified uniquely only by considering the primary key of another (owner) entity.

  • – Owner entity set and weak entity set must participate in a one-to-many relationship set (1 owner, many weak entities).

  • – When the owner entity is deleted, all owned weak entities must also be deleted.


E r model to r elational model5

E-R Model to Relational Model

  • Weak entity set E translates to table E

  • CREATE TABLE Policy (

  • pname CHAR(20),

  • age INTEGER,

  • cost NUMBER(10,2),

  • ssn CHAR(11),

  • PRIMARY KEY (pname, ssn),

  • FOREIGN KEY (ssn) REFERENCES Employees,

  • ON DELETE CASCADE)


E r model to r elational model6

E-R Model to Relational Model

  • Relationship Sets to Tables

  • Columns of table R should include

  • Attributes of the relationship set

  • Primary key attributes of each component entity set


E r model to r elational model7

E-R Model to Relational Model

  • Relationship Sets (without Constraints) to Tables

    • Create a table for the relationship set.

    • Add all primary keys of the participating entity sets as fields of the table.

    • Add fields of attribute in the relationship.

    • Declare a primary key using all key fields from the entity sets.

    • Declare foreign key constraints for all these fields from the entity sets.


E r model to r elational model8

E-R Model to Relational Model

  • Relationship Sets (without Constraints) to Tables


E r model to r elational model9

E-R Model to Relational Model

  • Relationship Sets (without Constraints) to Tables


E r model to r elational model10

E-R Model to Relational Model

  • Relationship Sets (with Constraints) to Tables

  • Create a table for the relationship set.

  • Add all primary keys of the participating entity sets as fields of the table.

  • Add a field for each attribute of the relationship.

  • Declare a primary key using the key fields from the source entity set only.

  • Declare foreign key constraints for all the fields from the source and target entity sets.


E r model to r elational model11

E-R Model to Relational Model

  • Relationship Sets (with Constraints) to Tables


E r model to r elational model12

E-R Model to Relational Model

  • Relationship Sets (with Constraints) to Tables

  • create table Directed By (

  • mn char(8),

  • staff id char(8),

  • primary key (mn),

  • foreign key (mn) references Students,

  • foreign key (staff id) references DoS);

  • Note that this has captured the key constraint, but not the participation constraint.


E r model to r elational model13

E-R Model to Relational Model

  • Mapping relationship sets (with key constraints, 2nd method)

  • Create a table for the source and target entity sets as usual.

  • • Add every primary key field of the target as a field in the source.

  • • Declare these fields as foreign keys.


E r model to r elational model14

E-R Model to Relational Model

  • Mapping relationship sets (with key constraints, 2nd method)

  • create table Students (

  • mn char(8),

  • name char(20),

  • age integer,

  • email char(15),

  • staff id char(8),

  • primary key (mn),

  • foreign key (staff id) references DoS )

  • Note that this has still not included the participation constraint on


E r model to r elational model15

E-R Model to Relational Model

  • Null values

  • In SQL, a field can have the special value null

  • A null value means that a field is either unknown/missing/unavailable, or undefined/makes no sense here.

  • Some implementations do not allow the null value to appear in primary key fields.


E r model to r elational model16

E-R Model to Relational Model

  • Mapping relationship sets with key+participationconstraints

  • Create a table for the source and target entity sets as usual.

    • Add every primary key field of the target as a field in the source.

    • Declare these fields as not null.

    • Declare these fields as foreign keys.


E r model to r elational model17

E-R Model to Relational Model

  • create table Students (

  • mn char(8),

  • name char(20),

  • age integer,

  • email char(15),

  • staff id char(8) not null,

  • primary key (mn),

  • foreign key (staff id) references DoS )


E r model to r elational model18

E-R Model to Relational Model

  • Mapping weak entity sets and identifying relationships

  • Create a table for the weak entity set.

    • Make each attribute of the weak entity set a field of the table.

    • Add fields for the primary key attributes of the identifying owner.

    • Declare a foreign key constraint on these identifying owner fields.

    • Instruct the system to automatically delete any tuplesin the table for which there are no owners


E r model to r elational model19

E-R Model to Relational Model

  • create table is_Located_In(

  • number char(8),

  • capacity integer,

  • name char(20),

  • primary key (number, name),

  • foreign key (name) references Buildings

  • on delete cascade )


E r model to r elational model20

E-R Model to Relational Model

  • Translating class hierarchies

  • Declare a table as usual for the superclassof the hierarchy.

    • For each subclass declare another table using superclass’s primary key and the subclass’s extra attributes.

    • Declare the primary key from the superclassas the primary key of the subclass, and with a foreign key constraint.


E r model to r elational model21

E-R Model to Relational Model

  • create table PT Students (

  • mn char(8),

  • pt frac integer,

  • primary key (mn),

  • foreign key (mn) references Students )


E r model to r elational model22

E-R Model to Relational Model


E r model to r elational model23

E-R Model to Relational Model

  • Translating class hierarchies

  • As in C++, or other PLs, attributes are inherited.

  • When declare A ISA B, every A entity is also considered to be a B entity.

    • Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed)

    • Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)


E r model to r elational model24

E-R Model to Relational Model

  • Translating class hierarchies

  • General approach:

  • – 3 relations: Employees, Hourly_Emps and Contract_Emps.

  • • Hourly_Emps: Every employee is recorded in Employees.

  • For hourly emps, extra info recorded in Hourly_Emps (hourly_wages, hours_worked, ssn); must delete Hourly_Empstuple if referenced Employees tuple is deleted).

  • • Queries involving all employees easy, those involving just Hourly_Emps require a join to get some attributes.

  • • Alternative: Just Hourly_Emps and Contract_Emps.

  • – Hourly_Emps: ssn, name, lot, hourly_wages, hours_worked.

  • – Each employee must be in one of these two subclasses.


E r model to r elational model25

E-R Model to Relational Model


  • Login