Transforming the Conceptual Data Model to SQL

1. Transforming the ConceptualData Model to SQL Hussam A.Halim-computer science department

2. problem Given the following assertions for a relational database that represents the current term enrollment at a large university, draw an ER diagram for this schema that takes into account all the assertions given. There are 2,000 instructors, 4,000 courses, and 30,000 students. Use as many ER constructs as you can to represent the true semantics of the problem. Hussam A.Halim-computer science department

3. Assertions: • An instructor may teach one or more courses in a given term (average is 2.0 courses). • An instructor must direct the research of at least one student (average = 2.5 students). • A course may have none, one, or two prerequisites (average = 1.5 prerequisites). • A course may exist even if no students are currently enrolled. • Each course is taught by exactly one instructor. • The average enrollment in a course is 30 students. • A student must select at least one course per term (average = 4.0 course selections). Hussam A.Halim-computer science department

4. Hussam A.Halim-computer science department

5. One-to-one-to-one ternary relationship • A technician uses exactly one notebook for each project. Each notebook belongs to one technician for each project. Note that a technician may still work on many projects and maintain different notebooks for different projects. Hussam A.Halim-computer science department

6. One-to-one-to-many ternary relationship • Each employee assigned to a project works at only one location for that project, but can be at different locations for different projects. At a particular location, an employee works on only one project. At a particular location,there can be many employees assigned to a given project. Hussam A.Halim-computer science department

7. One-to-one, both entities mandatory • each entity becomes a table, and the key of either entity can appear in the other entity’s table as a foreign key. • R.MODEL: • Report (report_no, report_name) • Abbreviation( abbr_no, report_no) Hussam A.Halim-computer science department

8. One-to-one, one entity optional, one mandatory • One of the entities in an optional relationship should contain the foreign key of • the other entity in its transformed table. Employee, the other entity in could also contain a foreign key (dept_no) with nulls allowed, but this would require more storage space because of the much greater number of Employee entity instances than Department instances. • R.Model: • Department(dept_no,dept_name,mgr_id) • Employee( emp_id,emp_name) Hussam A.Halim-computer science department

9. One-to-one, both entities optional When both entities are optional , either entity can contain the embedded foreign key of the other entity, with nulls allowed in the foreign keys. R.Model: Engineer(emp_id,desktop_no) Desktop(desktop_no,emp_id) Hussam A.Halim-computer science department

10. One-to-many, both entities mandatory The one-to-many relationship can be shown as either mandatory or optional on the “many” side, without affecting the transformation. On the “one” side it may be either mandatory or optional. In all cases the foreign key must appear on the “many” side, which represents the child entity, with nulls allowed for foreign keys only in the optional “one” case. Foreign key constraints are set according to the specific meaning of the relationship and may vary from one relationship to another R.Model: constraints: Department(dept_no, dept_name) Employee(emp_id, emp_name, dept_no) dept_no not null Hussam A.Halim-computer science department

11. One-to-many, one entity optional, one mandatory The foreign key must appear on the “many” side, which represents the child entity, with nulls allowed for foreign keys only in the optional “one” case. R.Model: constraints: Department(dept_no, dept_name) Report(report_no, dept_no) dept_no null allowed Hussam A.Halim-computer science department

12. Many-to-many, both entities optional The many-to-many relationship, requires a new table containing the primary keys of both entities. The same transformation applies to either the optional or mandatory case, including the fact that the not null clause must appear for the foreign keys in both cases. Note also that an optional entity means that the SQL table derived from it may have zero rows for that particular relationship. This does not affect “null” or “not null” in the table definition. R.Model: Engineer(emp_id,..) Prof_assoc(assoc_name, ..) Belohgs_to(temp_id, assoc_name,…) Hussam A.Halim-computer science department

13. Binary Recursive Relationships One-to-one, both sides optional R.Model: constraints: Employee( emp_id, emp_name, spouse_id) spouse_id null allowed Hussam A.Halim-computer science department

14. One-to-many, one side mandatory, many side optional R.Model: constraints: Engineer(emp_id, leader_id) leader_id not null Hussam A.Halim-computer science department

15. Many-to-many, both sides optional R.Model: Employee(emp_id, emp_name) coauthor( author_id, coauthor_id) Hussam A.Halim-computer science department

16. One-to-one-to-one ternary relationship Hussam A.Halim-computer science department

17. Hussam A.Halim-computer science department

18. One-to-one-to-many ternary relationships Hussam A.Halim-computer science department

19. Hussam A.Halim-computer science department

20. One-to-many-to-many ternary relationships Hussam A.Halim-computer science department

21. Many-to-many-to-many ternary relationships Hussam A.Halim-computer science department

22. Hussam A.Halim-computer science department