transforming an er model into a relational schema
DESCRIPTION
Transforming an ER Model into a Relational Schema. Cs263 Lecture 10. Transforming an ER Model into a Relational Schema. There are a series of steps that are recommended for converting an E/ER model into a relational schema. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/1.jpg)
Transforming an ER Model into a Relational Schema
Cs263 Lecture 10
![Page 2: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/2.jpg)
Transforming an ER Model into a Relational Schema
There are a series of steps that are recommended for converting an E/ER model into a relational schema.
These steps are not hard and fast rules, but rather a series of pertinent suggestions that the database designer must use wisely.
![Page 3: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/3.jpg)
E/ER to Relational Schema Conversion Steps
Step 1 For each ‘strong’ entity in the E/ER model,
create a base relation with a column for each simple attribute of that entity.
The key attribute for the entity becomes the primary key of the relation.
![Page 4: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/4.jpg)
Example – Converting ‘Strong’ Entities
EmpEmpNo
Salary Comm
Hire DateName
Job
EmpNo Number(4)Name Varchar2(10)Job Varchar2(9)Sal Number(7,2)Comm Number(7,2)HireDate Date
Emp Primary Key
![Page 5: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/5.jpg)
Example – Converting ‘Strong’ Entities
Dept
DeptNo
Location
Name
DeptNo Number(2)Name Varchar2(14)Location Varchar2(13)
Dept Primary Key
![Page 6: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/6.jpg)
E/ER to Relational Schema Conversion Steps
Step 2For each ‘weak’ entity, create a relation consisting of all the simple attributes of that entity and also include columns for the primary keys of those entities on whose existence it is dependent.
![Page 7: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/7.jpg)
Example – Converting ‘Weak’ Entities
Loan
LoanNo Char(8)PaymentNo Number(4)Date DateAmount Number(9,2)
Payment Composite Primary Key
PaymentDate
AmountLoan No Payment No
Discriminator
![Page 8: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/8.jpg)
E/ER to Relational Schema Conversion Steps
Step 3When two entities participate in a one-to-many (1-M) relationship, the relation representing the entity with the M (Many) cardinality must have a foreign key column representing this relationship.
![Page 9: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/9.jpg)
Example – Converting (1-M) Relationships
Dept
D#
EmpWorks in
Assigned E#
EmpNo Number(4)Name Varchar2(10)Job Varchar2(9)Sal Number(7,2)Comm Number(7,2)HireDate DateDeptNo Number(2)
Emp
Foreign Key
As an Employee ‘must’ work in a department, then the DeptNo column in the Emp relation (table) cannot be NULL!
![Page 10: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/10.jpg)
Example – Converting (1-M) Relationships
EmpNo Number(4)Name Varchar2(10)Job Varchar2(9)Sal Number(7,2)Comm Number(7,2)HireDate DateDeptNo Number(2)
Emp
Foreign Key
As an Employee ‘may not’ work in a department, then the DeptNo column in the Emp relation (table) can be NULL!
Dept
D#
EmpWorks in
Assigned E#
![Page 11: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/11.jpg)
E/ER to Relational Schema Conversion Steps
Step 4When two entities participate in a (1-1) relationship, a foreign key column must be included in the relation that represents one of these entities.
![Page 12: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/12.jpg)
Example – Converting (1-1) Relationships
Emp
E#
ID CardBelongs to
Has ID#
EmpNo Number(6)Name Varchar2(10)Address Varchar2(200)DOB DateIDNo Char(10)
Emp
Foreign Key
IDNo Char(10)Issued DateExpires Date EmpNo Number(6)
ID Card
Foreign Key
EITHER ONE IS ACCEPTABLE
![Page 13: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/13.jpg)
E/ER to Relational Schema Conversion Steps
Step 5When two entities participate in a many-to-many (M-M) relationship, then a relation must be created consisting of foreign keys for the two relations that represent the participating entities.
![Page 14: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/14.jpg)
Example – Converting (M-N) Relationships
Person
P#
CarDriven by
Drives C#
PersonNo Number(6)CarNo Char(7)
Person-Car Foreign KeyForeign Key
Person
P#
CarC#
Person-Car
P#,C#
![Page 15: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/15.jpg)
E/ER to Relational Schema Conversion Steps
Step 6Where an entity has a multi-valued attribute, create a relation with a column as a foreign key to the relation that represents the entity and a column for the multi-valued attribute.
![Page 16: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/16.jpg)
LanguageLanguage
Example – Multi-valued Attributes
Programmer
EmpNo
Language
Name
EmpNo Number(2)Name Varchar2(14)
Programmer Primary Key
EmpNo Number(2)Language Varchar2(20)
Prog-Lang Composite Primary Key
![Page 17: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/17.jpg)
E/ER to Relational Schema Conversion Steps
Step 7When more than two entities participate in a relationship, then a relation must be created consisting of foreign keys to those relations representing the entities participating in the relationship.
![Page 18: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/18.jpg)
Example – Converting Ternary Relationships
Match
Match No
PlayerPlayer No
Offence
Offence No
Booking
MatchNo Number(4)PlayerNo Number(6)OffenceNo Number(3)Time Date
BookingComposite Primary Key
![Page 19: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/19.jpg)
E/ER to Relational Schema Conversion Steps
Sub-typing (OO design not necessary for assignment)
For sub-typing, create separate relations for each subtype consisting of those attributes which are peculiar to the given subtype, and include a foreign key to the super-type, that will act as the sub-type’s primary key.
Create a separate relation for the super-type!
![Page 20: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/20.jpg)
Example – Converting Sub-types
EmployeeNoNameAddress
Employee
EMPLOYEE Employee No Name Address
SECRETARYTyping Speed
TECHNICIANGrade
ENGINEERType
EmployeeNoTypingSpeed
Secretary
EmployeeNoGrade
Technician
EmployeeNoType
Engineer
![Page 21: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/21.jpg)
E/ER to Relational Schema Conversion Steps
Step 8 – Option BFor sub-typing, create separate relations for each subtype consisting of all the attributes of the super-type, and all those which are peculiar to the given subtype. The primary key of the super-type becomes the primary key of each sub-type.
Do not create a relation for the super-type!
![Page 22: Transforming an ER Model into a Relational Schema](https://reader036.vdocuments.us/reader036/viewer/2022062422/568139d9550346895da18c24/html5/thumbnails/22.jpg)
Example – Converting Sub-types
VehicleNoLicenceNoPriceNoOfSeats
Car
VEHICLE Vehicle No Licence No Price
CARNo of Seats
TRUCKNo of AxlesTonnage
Truck VehicleNoLicenceNoPriceNoOfAxlesTonnage