Chapter 6, Problem 20E

Explanation of Solution

a.

Relation schema for Car Insurance Company (Exercise 6.1):

Customer (cust_ID, cust_Name, cust_Address, cust_Phone)

Car (car_Num, model, make, color, year)

Insurance_Policy (policy_Num, policy_Name, coverage)

Accident (report_ID, location, date, damage_Cost)

Premium_Payment (payment_ID, amount, due_date, payment_Date)

Owns (cust_ID, car_Num)

Covers (car_Num, policy_Num)

Payments (policy_Num, payment_ID)

Meets (car_Num, report_ID)

Explanation:

• The “Customer” entity contains “cust_ID”, “cust_Name”, “cust_Address” and “cust_Phone attributes. Here, “cust_ID” is a primary key.
• The “Car” entity contains “car_Num”, “model”, “make”, “color”, and “year” attributes. Here, “car_Num” is a primary key.
• The “Accident” entity contains “report_ID”, “location”, “date” and “damage_Cost” attributes...

Explanation of Solution

b.

Relation schema for Model exam as a ternary relationship (Exercise 6.2a):

Student (stud_ID, Name, dept_Name, tot_Credit)

Section (course_ID, sem, sec_ID, year)

Exam (exam_ID, Name, Place, time)

Course (course_ID, credit, title, dept_Name)

exam_marks (stud_ID, course_ID, sem, sec_ID, year)

sec_course (course_ID, sem, sec_ID, year)

Explanation:

• The “Student” entity contains “stud_ID”, “Name”, “tot_Credit”, and “dept_Name” attributes. Here, “stud_ID” is a primary key.
• The “Section” entity contains “sec_ID”, “course_ID”, “sem”, and “year” are the attributes. Here, all the attributes are primary keys.
• The “Course” entity contains “course_ID”, “dept_Name”, “credit”, and “title” are the attributes. Here, “course_IS” is a primary key.
• The “exam_marks” contains stud_ID”, “sec_ID”, “course_ID”, “sem”, and “year” attributes. Here, all are primary keys.
• The “sec_course” contains “sec_ID”, “course_ID”, “sem”, and “year” attributes...

Explanation of Solution

c.

Relation schema for Favorite team statistic (Exercise 6.3):

Match (match_ID, date, opponent, own_score, opp_score, stadium)

Player (Player_ID, name, age, season_score)

played (match_ID, Player_ID)

Explanation:

• The “Match” entity contains “match_ID”, “date”, “stadium”, “opponent”, “own_score”, and “opp_score” attributes...

Explanation of Solution

d.

Relation schema for Hospital (Exercise 6.15):

Doctors (doc_ID, doc_Name, doc_Address, specialization)

Patients (pat_Num, pat_Name, disease, date_admitted, date_checkout)

Tests (test_ID, test_Name, date, time, and result)

Treats (doc_ID, pat_Num)

Log (pat_Num, test_ID)

Explanation:

• The “Doctors” entity contains “doc_ID”, “doc_Name”, “doc_Address” and “specialization” attributes. Here, “doc_ID” is a primary key...