COMP231 Tutorial 1

1. COMP231 Tutorial 1 ER Model and ER to Relational Schema

2. E-R Diagram • Rectangles – entity sets • Ellipses – attributes • Diamonds – relationship sets • Double ellipses – multivalued attributes • Dashed ellipses – derived attributes • Double lines – total participation • Double rectangles – weak entity sets • Double diamonds – identifying relationship sets

3. Exercise 1.1 Construct E-R Diagram • A university registrar’s office maintains data about the following entities: • courses, including number, title, credits, syllabus, and prerequisites; • course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; • students, including student-id, name, and program; • instructors, including identification number, name, department, and title. • Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled must be appropriately modeled. • Construct an E-R diagram for the registrar’s office.

4. Entities course-offering instructor student course

5. Course • “courses including number, title, credits, syllabus, and prerequisites” • Attribute? • Relationship? courseno credits prerequisite requires course maincourse syllabus title

6. Course Offering • “course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom” semester time courseno course- offering is_offered room course secno year

7. Weak Entity • A weak entity can only be identified uniquely by combining the primary key of another (owner) entity and the partial key of itself. • Owner entity set and weak entity set must participate in one-to-many relationship (one owner entity, many weak entities). • Weak entity set must have total participation in this identifying relationship set.

8. Student, Instructor • “students, including student-id, name, and program” • “instructors, including identification number, name, department, and title” sid name iid name student instructor program dept title

9. Enrollment • “Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled must be appropriately modeled.” course-offering enrolls student grade

10. Anymore?? • Instructor teaches course….. course-offering teaches instructor

11. E-R Diagram for a University

12. Exercise 1.2 Covert E-R Diagram into Tables

13. Entities (Not Weak)

14. Entities (Not Weak) • course (courseno, title, syllabus, credits) • student (sid, name, program) • instructor (iid, name, dept, title)

15. Weak Entities

16. Weak Entities • course-offering (courseno, secno, year, semester, time, room)

17. Relationships (Not defining weak entities)

18. Relationships (Not defining weak entities) • enrolls (sid, courseno, secno, semester, year, grade) • teaches (courseno, secno, semester, year, iid) • requires (maincourse, prerequisite)

19. Relationships with Weak Entities

20. Relationships with Weak Entities • There is no extra table for the relationship between a weak entity and its strong entity. • The relationship is already present in the schema for the weak entity. course-offering (courseno, secno, year, semester, time, room)

21. Relational Schemas for a University • course (courseno, title, syllabus, credits) • student (sid, name, program) • instructor (iid, name, dept, title) • course-offering (courseno, secno, year, semester, time, room) • enrolls (sid, courseno, secno, semester, year, grade) • teaches (courseno, secno, semester, year, iid) • requires (maincourse, prerequisite)

22. Submitted for –www.mycollegebag.in