1 / 13

ERD Examples

ERD Examples. Example 1. A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments.

hinto
Download Presentation

ERD Examples

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ERD Examples

  2. Example 1 • A company has several departments. • Each department has a supervisor and at least one employee. • Employees must be assigned to at least one, but possibly more departments. • At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. • The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number.

  3. Identify Entities • The entities in this system are Department, Employee, Supervisor and Project. • One is tempted to make Company an entity, but it is a false entity because it has only one instance in this problem. • True entities must have more than one instance. 

  4. Find Relationships • We construct the following Entity Relationship Matrix: 

  5. We connect the entities whenever a relationship is shown in the entity Relationship Matrix. Run by DEPARTMENT SUPERVISOR Is assigned works on EMPLOEYEE PROJECT

  6. Fill in Cardinality • From the description of the problem we see that:  Each department has exactly one supervisor.  • A supervisor is in charge of one and only one department.  • Each department is assigned at least one employee.  • Each employee works for at least one department.  • Each project has at least one employee working on it.  • An employee is assigned to 0 or more projects.

  7. Run by DEPARTMENT SUPERVISOR Is assigned works on EMPLOEYEE PROJECT

  8. Define Primary Keys • The primary keys are Department Name, Supervisor Number, Employee Number, Project Number.

  9. Identify Attributes • The only attributes indicated are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee NUMBER and a unique project number. 

  10. SUPERVISOR Sup_number Sup_Name DEPARTMENT Dep_Name Run by Is assigned EMPLOEYEE Emp_number Emp_Name PROJECT Proj_number Proj_Name works on

  11. Example 2 • Students are subdivided into graduate and undergraduate students. Students take a course in a particular semester and receive a grade for their performance. Sometimes students take the same course again in a different semester. There are no limits on how many courses a student can take, and on how many students completed a particular course. • Each graduate student has exactly one advisor, who must be a professor, whereas each professor is allowed to be the advisor of at most 20 students. Courses have a unique course number and a course title. Students and professors have a name and a unique ssn; students additionally have a gpa; moreover, graduate students have a GRE-score, and undergraduate students have a single or multiple majors. Professors can be students and take courses, but graduate students cannot be undergraduate students.

  12. COURSE Course_# Course_Tilte Grade PERSON SSN Name takes O O SUPERVISOR STUDENT GPA Run by SEMESTER Sem_ID d advises UNDERGRADUATE {Major} GRADUATE Gre

More Related