ER Modeling Exercise a) Please follow the example ER modeling exercise and solutions posted elsewhere. b) The type of ER diagram that you draw (Chens or Crows Foot) is not important. Please include in your diagram all the notations needed. c) The number of tables needed in each case should be clearly stated. d) Four statements describing the ER is needed in all cases except the supertype/subtype relationships. e) Drawing templates are provided in the assignment section. My suggestion would be to use the Microsfot Word templates.

1) In a midwestern univesity, atheletic teams have coaches. A coach can coach multiple teams (of different sports) and teams can have multiple coaches. A coach will be coaching at least one team and all teams have coaches. Coaches have name, date joined, designation, and teams have names, captain, semester (some are fall sports and others of spring/winter sports), win-loss percentage.

2) A borrower in a city library can borrow multiple books but books at a given time are borrowed by only one person. Visualize this in a general library lending scenario and draw an ER diagram. You can assume the constraints as you but state them clearly through your four statements.

3) Employees have managers. While an employee need not manage any other employee(s) all employees have exactly one manager. Employee name, date joined, department, salary, educational level and certifications.

4) In a manufacturing organization, parts can be either purchased or manufactured parts. Purchased parts have vendors, price and manufactured parts have setup time. All parts have number and name. The organization manufacutres some parts but can opt to buy the same parts in case there is an urgent need.

EXAMPLE BELOW

1. Cincinnati String Academy Cincinnati String Academy teaches violin, viola and other stringed instruments to young children. The classes are structured in sessions that start and end at a given date. A music student can enroll for multiple classes in the same session. A class is a particular time slot within a session, and this allows students to enroll in multiple classes in the same session. The fee is based on the number of classes enrolled. A class can have a maximum of 10 students. A student can take a maximum of 3 classes. A student can be in the roster without currently enrolling in any class and a class can exist without a single student while waiting for student enrollment before the registration date. One Liner: Students enroll in classes ER Diagram: Four Statements: Maximum Cardinality: - One student can enroll in up to 3 classes. - One class can have up to 10 students enrolled. Minimum Cardinality (Participation): - A student need not enroll in a class. - A class need not have any students. Number of Tables: 3 There is a many-to-many (M:N) relationship between students and classes. Therefore, we need three tables: one for students (Students), one for classes (Classes) and a bridge table (Enrollment) that connects them with one-to-many relationships (1:M). The primary keys of the Students (studentID) and Classes (classID) tables are both posted as foreign keys in the bridge table, Enrollment. In addition, these columns (studentID, classID) make up the composite primary key for the Enrollment table. 2. Cars A car is assigned to only one department and department can have multiple cars assigned to it, with a maximum of 10 cars. A car is always assigned to a department but a department may not be assigned any cars. One Liner: Departments have cars assigned to them ER Diagram: Four Statements: Maximum Cardinality: - One car is assigned to only one department. - One department can have up to 10 cars assigned to it. Minimum Cardinality (Participation): - A car must be assigned to a department. - A department need not have any cars assigned to it. Number of Tables: 2 There is a one-to-many relationship (1:M) between departments and cars. Therefore, we need two tables: one for departments (Department) and one for cars (Car). The primary key of the Department table (deptID) is posted as a foreign key in the Car table