E-R Model to R elational Model

1. E-R Model to Relational Model

2. 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

3. 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

4. E-R Model to Relational Model • example

5. 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.

6. 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)

7. 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

8. 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.

9. E-R Model to Relational Model • Relationship Sets (without Constraints) to Tables

10. E-R Model to Relational Model • Relationship Sets (without Constraints) to Tables

11. 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.

12. E-R Model to Relational Model • Relationship Sets (with Constraints) to Tables

13. 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.

14. 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.

15. 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

16. 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.

17. 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.

18. 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 )

19. 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

20. 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 )

21. 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.

22. E-R Model to Relational Model • create table PT Students ( • mn char(8), • pt frac integer, • primary key (mn), • foreign key (mn) references Students )

23. E-R Model to Relational Model

24. 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)

25. 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.

26. E-R Model to Relational Model