数据库管理系统概述英文版课件：tutorial1 ER Diagrams

1、COMP231 Tutorial 1ER DiagramsE-R DiagramRectangle entity set实体集Ellipse attribute属性Diamond 菱形 relationship set 联系集Double ellipse multivalued attribute 多值Dashed ellipse derived attribute派生属性Double line total participationDouble rectangle weak entity setDouble diamond relationship set for weak entity s

2、etExercise 1A 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-i

3、d, 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

4、 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) instructorcoursecourse-offeringstudentinstructorCourse“course incl

5、uding number, title, credits, syllabus, and prerequisites”Entity set?Attribute?Relationship set? Roles?Cardinality constraint?Participation constraint?prerequisitemaincoursecourse_notitlerequirescoursesyllabuscredits“course offering, including course number, year, semester, section number, instructo

6、r(s), timings, and classroom”Course Offeringtimeroomcoursesection_nosemesteryearcourse- offeringis offeredCourse-noWeak Entity SetA weak entity set can be identified uniquely only by considering the primary key of another (owner) entity setOwner entity set and weak entity set must participate in one

7、-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 dependencyWE implies ED but not the other way round. WE has only a partial key but ED may have a primary key BABA

8、BAStudent, Instructor“student, including student-id, name, and program”“instructor, including identification number, name, department, and title”studentnamesidprograminstructortitledeptnameiidEnrollment“Further, the enrollment of students in courses and grades awarded to modeled. students in each co

9、urse they are enrolled for must be appropriately ”studentcourse-offeringenrolsgradeExercise 1A university registrars office maintains data about the following entities: course, including number, title, credits, syllabus, and prerequisites; course offering, including course number, year, semester, se

10、ction 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 a

11、ppropriately 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.instructorteachescourse-offeringA course-offering does not have to have an instructor.A c

12、ourse-offering has to have an instructor. Partial participationTotal participationtitledeptnameiidFigure 1 E-R diagram for a university registrar office.prerequisitemaincoursecoursesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringis offeredenrollsgradenamesidprogramstudentinstructorteach

13、estitlerequirescourse_noMore 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.DepartmentEach instru

14、ctor must belong to exactly one departmentEach department must have a least one instructor.instructordepartmentbelongstelephone numberdept-idnameExercise 2Design a database to organize the information about a factory and the products that are manufactured there. The relevant information is as follow

15、s: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 administrati

16、ve 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 n

17、umber 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.Exercise 2Design a database to organize the information

18、 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.Administra

19、tive 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.relationship A worker

20、 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 b

21、elong to different products may have the same serial number.Employee, Administrative staff, WorkerThe 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.ISAEmployeeAdmini

22、strative staffWorkerSeminarAdministrative 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.Administrative staffTakesSeminarProductThe factory manufactures a number of products and eac

23、h 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. WorkerWorks forProductItemA large number of items are manufactured for each product. Each item has a serial number and a color. Di

24、fferent items of the same product have different serial numbers. However, two items that belong to different products may have the same serial number.ItemProductFormsISAEmployeeAdministrative staffWorkerTakesSeminarWorks forProductItemFormsConclusionNotations of E-R DiagramGenerally, we construct an

25、 E-R diagram, by identifying: Entity setsWhat should be entities? Strong or weak entity set?Relationship setsNeed to label roles?AttributesCan be inherited? (generalization or specification)Participation constraints:Total participation or partial participation?Cardinality constraintsOne to one, one

26、to many or many to many?ororQ&AExampleWe want to create database for a bank in which we store: Customers (id, name, city). Accounts (number, balance)Loans (number, amount)Branches (name, city, assets)Each customer can have any number of accounts and loansEach account and loan is associated with one

27、branch. A customer must have either one account or loan in order to be in the databaseThe balance in each account should be $100. The last two constraints cannot be expressed by the ER diagram.SolutionParticipation constraintCardinality constraintExampleWe want to create a very simple database for S

28、CUT in which to record information about professors, students and classes. For each professor we need to store the HK-id, name and office number. For each student we need to store the student-id and nameFor each class the id (e.g., COMP 231) and the name. Each class is taught by exactly one professor.Each student must take at least one class. For each class