6.20
Construct appropriate relation schemas for each of the E-R diagrams in:
a. Exercise 6.1
b. Exercise 6.2.
c. Exercise 6.3.
d. Exercise 6.15.
Appropriate relation schema for exercise 6.1
customer(customer_id, name, address)
car(license_no, model, customer_id)
accident(report_id, date, place)
participated(license_no, report_id)
policy(policy_id)
covers(license_no, policy_id)
premium_payment(policy_id,payment_no,due_date,amount,received_no)
Appropriate relation schema for exercise 6.2 Figure 6.102
student(student_id, name, dept_name, tot_cred)
exam(exam_id, name, place, time)
course(course_id, title, credits)
section(course_id, sec_id, semester, year)
exam_marks(student_id, course_id, sec_id, semester, year, exam_id, marks)
Appropriate relation schema for exercise 6.2 Figure 6.103
student(student_id, name, dept_name, tot_cred)
course(course_id, title, credits)
section(course_id, sec_id, semester, year)
exam_marks(student_id, course_id, sec_id, semester, year,)
exam_marks_value(student_id, course_id, sec_id, semester, year,exam_id,marks)
Note that dropping the relation exam_marks and retaining exam_marks_value has the benefit of simplifying the resultant database schema.
Appropriate relation schema for exercise 6.3
match(match_id, date, stadium, opponent, own_score, opp_score)
player(player_id, name, age)
played(match_id, player_id, score)
Appropriate relation schema for exercise 6.15
patient(patient_id,name,phone_number,date_of_birth,…,emergency_contact)
medical_doctor(doctor_id,name,phone_number,date_of_birth,address)
tests_and_examinations(test_id,test_type,test_result,extra_remarks,timestamp)
patient_tests(patient_id, doctor_id,test_id)