In this assignment, you'll map the given ER/EER diagram to a relational data schema. You can find the mapping algorithm in our slides ER and EER to Relational Mapping.pdf.

Here's another way to represent the relational schema:

For each relation, write it in the form of R(A1, A2, A3, ..., An) (here R is the name of the relation, and A1, A2, ..., An are the attributes) and underline the primary keys. Then state the foreign keys in the form of R.A1 (where A1 is the foreign key in relation to R) references S.A2 (where A2 is the primary key in relation to S).

For example, to represent two entity types (STUDENT and COURSE) and a relationship type (student takes courses). In this case, we have M:N relationship type so we create a separate relation TAKE_COURSE. So we can represent it in the following way.

STUDENT(SID, Name, Age, Phone_no)

COURSE(Course_no, Name, Semester, Textbook)

TAKE_COURSE(Student_ID, Course_no)

TAKE_COURSE.Student_ID references STUDENT.SID

TAKE_COURSE.Course_no references COURSE.Course_no

Q1:

Map the following BANK ER schema into a relational schema. Specify all primary keys and foreign keys. Briefly discuss how you apply the mapping algorithm step by step to this problem.

Q2:

Map the following CAR_DEALER database EER schema into a relational schema. Specify all primary key and foreign keys. Briefly discuss how you apply the mapping algorithm step by step to this problem. For the VEHICLE to CAR/TRUCK/SUV generalization, specify which strategy you use from the four options we introduced in class.

1 N BANK BRANCHES BANK BRANCH Code Name Addr Addr Branch_no 1 ACCTS LOANS IN N Acct_no Balance Loan_no Amount ACCOUNT Type LOAN Type M M A_C L_C N N Ssn Name Phone CUSTOMER Addr CAR Engine_size Vin Price VEHICLE d TRUCK Tonnage Model N Date SUV No_seats SALE 1 SALESPERSON CUSTOMER Address City Street Sid Name Ssn Name State