ERD to Relational Schema Md Zabirul Islam zabirul

ERD to Relational Schema Md. Zabirul Islam zabirul. cse@diu. edu. bd

1. Entities and Simple Attributes: An entity type within ER diagram is turned into a table. Each attribute turns into a column (attribute) in the table. Persons( personid , name, lastname, email, phone )

2. Multi-Valued and Composite Attributes 1. Create a table for the attribute. 2. Add the primary (id) column of the parent entity as a foreign key within the new table Persons( personid , name) Phones ( phoneid , personid, phone )

Derived Attribute Derived attribute can be calculated at any point of time. Hence we need not create a column for this attribute. It reduces the duplicity in the database.

5 Redundancy of Schemas (Cont. ) �For one-to-one relationship sets, either side can be chosen to act as the “many” side That is, extra attribute can be added to either of the tables corresponding to the two entity sets Dr. Azhar, Dept. of CSE, KUET

1: 1 Relationships Persons( personid , name, lastname, email, phone , wifeid ) Wife ( wifeid , name ) OR Persons( personid , name, lastname, email, phone ) Wife ( wifeid , name , personid)

Redundancy of Schemas 7 n Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side (add as foregirn key) n Example: Instead of creating a schema for relationship set account_branch add an attribute branch_name to the schema arising from entity set account n Account=(account-number, balance, branch-name) Dr. Azhar, Dept. of CSE, KUET

1: N Relationships Persons( personid , name, lastname, email ) House ( houseid , num , address, personid)

Representing Relationship Sets as Schemas � A many-to-many relationship set is represented as a schema with attributes for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set. � Example: schema for relationship set borrower = (customer_id, loan_number )

N: N Relationships Persons( personid , name, lastname, email, phone ) Countries ( countryid , name, num) Has. Relat ( hasrelatid , personid , countryid)

Representing Entity Sets as Schemas � A strong entity set reduces to a schema with the same attributes. � A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set loan(loan_number, amount) payment = ( loan_number, payment_date, payment_amount )

Mapping Weak Entity Sets • Create table for weak entity set. • Add all its attributes to table as field. • Add the primary key of identifying entity set. Student(Roll. No, Name) Dependent(Name, Roll. No)

Weak Entities Weak entity set and identifying relationship set are translated into a single table. When the owner entity is deleted, all owned weak entities must also be deleted. name ssn lot Employees cost Policy pname age Dependents CREATE TABLE Dep_Policy ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE ) 13

14 Extended E-R Features: Specialization � Top-down design process; we designate sub groupings within an entity set that are distinctive from other entities in the set. � Depicted by a triangle component labeled ISA (E. g. customer “is a” person). � Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked. Dr. Azhar, Dept. of CSE, KUET

15 Specialization A group of entities is divided into sub-groups based on their characteristics Dr. Azhar, Dept. of CSE, KUET

16 Extended E-R Features: Specialization � Top-down design process; we designate sub groupings within an entity set that are distinctive from other entities in the set. � Depicted by a triangle component labeled ISA (E. g. customer “is a” person). � Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked. Dr. Azhar, Dept. of CSE, KUET

17 Specialization A group of entities is divided into sub-groups based on their characteristics Dr. Azhar, Dept. of CSE, KUET

18 Specialization/Generalization Example Dr. Azhar, Dept. of CSE, KUET

Mapping Hierarchical Entities • Create tables for all higher-level entities. • Create tables for lower-level entities. • Add primary keys of higher-level entities in the table of lower-level entities. • In lower-level tables, add all other attributes of lower-level entities. • Declare primary key of higher-level table and the primary key for lower-level table. • Person(Person. ID, Name, Genger, Age) • Student(Roll. No, Person. ID) • Teacher(Emp. ID, Person. ID)

Transforming E-R Diagrams into Relations To transform the conceptual data model into a set of normalized relations Steps 1. Represent entities 2. Represent relationships 3. Normalize the relations 4. Merge the relations 20

21 Transforming E-R Diagrams into Relations Customer(Customer_ID, name, Address, City_State_ZIP, Discount)

22 Transforming E-R Diagrams into Relations Represent Relationships Binary 1: N Relationships Add the primary key attribute of the entity on the one side of the relationship as a foreign key in the relation on the many side

23 Transforming E-R Diagrams into Relations

Transforming E-R Diagrams into Relations Binary 1: 1 relationship Three possible options a. Add the primary key of A as a foreign key of B b. Add the primary key of B as a foreign key of A c. Both 24

Exercise 1 Customers(SSN, name, addr, phone); Flights(number, day, aircraft) Bookings(SSN, number, day, row, seat)

Exercise 2 Stars(name, addr); Studios(name, addr); Movies(title, year, length, genre); Contracts(Start. Name, studios. Name, titile, year, salary)

Exercise 3 Company( Company. ID) Staff( Staff. ID , dob , address , name, Wife. ID) Child( Child. ID , name , Staff. ID ) Wife ( Wife. ID , name ) Phone(Phone. ID , phone. Number , Staff. ID) Task ( Task. ID , description) Work(Work. ID , Company. ID , Staff. ID , since ) Perform(Perform. ID , Staff. ID , Task. ID )

Exercise 4