COMP231 Tutorial 1,ER Diagrams,About Me,PENG Yu, Grace gracepyust.hk Office hour: TBA Office: 4212,E-R Diagram,Rectangle entity set Ellipse attribute Diamond relationship set Double ellipse multivalued attribute Dashed ellipse derived attribute Double line total participation Double rectangle weak entity set Double diamond relationship set for weak entity set,Exercise 1,A university registrars office maintains data about the following entities: course, including number, title, credits, syllabus, and prerequisites; course offering, including course number, year, semester, section number, instructor(s), timings, and classroom; student, including student-id, name, and program; instructor, 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 for must be appropriately modeled. Construct an E-R diagram for the registrars office. Document all assumptions that you make about the cardinality constraints and participation constraints.,Entity sets,“A university registrars office maintains data about the following entity sets:” (a) course (b) course offering (c) student (d) instructor,course,course-offering,student,instructor,Course,“course including number, title, credits, syllabus, and prerequisites” Entity set? Attribute? Relationship set? Roles? Cardinality constraint? Participation constraint?,prerequisite,maincourse,course_no,title,requires,course,syllabus,credits,“course offering, including course number, year, semester, section number, instructor(s), timings, and classroom”,Course Offering,time,room,course,section_no,semester,year,course- offering,is offered,course_no,Weak Entity Set,A weak entity set can be identified uniquely only by considering the primary key of another (owner) entity set Owner entity set and weak entity set must participate in one-to-many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying relationship set. Difference between weak entity and existence dependency WE implies ED but not the other way round. WE has only a partial key but ED may have a primary key,A,B,A,B,A,Student, Instructor,“student, including student-id, name, and program” “instructor, including identification number, name, department, and title”,student,name,sid,program,instructor,title,dept,name,iid,Enrollment,“Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.”,student,course- offering,enrols,grade,Exercise 1,A university registrars office maintains data about the following entities: course, including number, title, credits, syllabus, and prerequisites; course offering, including course number, year, semester, section number, instructor(s), timings, and classroom; student, including student-id, name, and program; instructor, 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 for must be appropriately modeled. Construct an E-R diagram for the registrars office. Document all assumptions that you make about the cardinality constraints and participation constraints.,Anymore ?,Instructor teaches course,instructor,teaches,course- offering,A course-offering does not have to have an instructor.,A course-offering has to have an instructor.,Partial participation,Total participation,title,dept,name,iid,Figure 1 E-R diagram for a university registrar office.,prerequisite,maincourse,course_no,course,syllabus,credits,time,room,section_no,semester,year,enrolls,grade,name,sid,program,student,instructor,teaches,title,requires,More ,Based on Figure 1 , modify the E-R Diagram so as to present the following information.Each instructor must belong to exactly one department. Each department includes dept-id, name, telephone number.Each department must have at least one instructor.,Department,Each instructor must belong to exactly one department Each department must have a least one instructor.,instructor,department,belongs,telephone number,dept-id,name,Exercise 2,Design a database to organize the information about a factory and the products that are manufactured there. The relevant information is as follows: The factory has a number of employees. For each employee you need to store the name, employee number, and salary. Each employee must be an administrative staff or a worker, but not both. Administrative employees must take seminars. For each seminar we keep its id, name and date. For the administrative staff, you must store the grade received, for each seminar taken. The factory manufactures a number of products and each product is identified by a product id and has a name.A worker is assigned to work on exactly one product; a product has multiple (one or more) workers assigned to it. A large number of items are manufactured for each product. Each item has a serial number and a color. Different items of the same product have different serial numbers. However, two items that belong to different products may have the same serial number.,