Lab 1 ER Modeling Case Studies 1 Banks

Lab 1 ER Modeling Case Studies

1) Banks Database : ER Case Study u Consider the following set of requirements for a Bank database that is used to keep track of Customer. a) Each bank has a unique name. b) Each branch has a number, name, address (number, street, city), and set of phones. c) Customer includes their name, set of address (P. O. Box, city, zip code, country), set of phones, and social security number. d) Accounts have numbers, types (e. g. saving, checking) and balance. Other branches might use the same designation for accounts. So to name an account uniquely, we need to give both the branch number to which this account belongs to and the account number. e) Not all bank customers must own accounts and a customer may have at most 5 accounts in the bank. f) An account must have only one customer. g) A customer may have many accounts in different branches. 2

Banks Database : ER Case Study u u u Design an ER schema for this application, and draw an ER diagram for that schema. Specify key attributes of each entity type and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. 3

Banks Database : ER Case Study Branch_NO{PK} Name Address Number Street City Phone[1. . *] Customer SSN {PK} Name Address [1. . *] P. O Box Code City Country 0. . * Phone [1. . *] has Account NO 1. . 1 Bank Name {PK} 1. . 1 Reason of chasm trap : Note all customer should have an account. So we can not answer this question : List all customer available on a specific branch? 0. . * Contains Owns 0. . 5 Account Type Balance 0. . * 4

Banks Database : ER Case Study To avoid chasm trap we add this relationship Branch has 1. . * 0. . * Customer SSN {PK} Name Address [1. . *] P. O Box Code City Country 0. . * Phone [1. . *] Branch_NO{PK} Name Address Number Street City Phone[1. . *] 0. . * has Account NO 1. . 1 Bank Name {PK} 1. . 1 Contains Owns 0. . 5 Account Type Balance 0. . * 5

u Primary key of account (Weak entity ): u Branch_No (PK of Strong entity) + account. No (discriminator) 6

2) Organization : ER Case Study u Organization made up of various departments, each having a name, identifying no. , and an employee who is the manager. A department may be located in different places. Information about employee includes name, identification number, birth date, address, sex, and salary. Each employee is assigned to one department. The date the manager is appointed to a department is also tracked. Employees may be directly supervised by another employee. Each project within the organization is controlled by a department. Employees (not necessarily from the controlling dept. ) are assigned to projects. Information about projects includes project name, no. , and location. Hours spent by employees on each project are also kept. 7

Organization : ER Case Study u draw an ER diagram to represent the data requirements as following: – Identify the main entity types. – Identify the main relationship types between the entity types. – Identify attributes and associate them with entity or relationship types. – Determine candidate and primary key attributes for each (strong) entity type. – Determine the multiplicity constraints for each relationship. State any assumptions necessary to support your design. 8

Organization : ER Case Study supervises Supervisee 1. . * 0. . 1 Supervisor Employee Department 1. . * NO{PK} Name Birthdate Sex Salary 1. . * Employee assigns to 1. . 1 Dep_NO{PK} Name Location[1. . *] Total_of_employee 1. . 1 Manager Manages Worker 0. . 1 1. . 1 Start Date Works on 1. . * Hours Controls Project Pro_NO{PK} Name Location 0. . * 9

3) Hospital : ER Case Study u A General Hospital consists of a number of specialized wards (such as Radiology, Oncology, etc). Information about ward includes unique name, total numbers of current patients. Each ward hosts a number of patients, who were admitted by a consultant (doctors) employed by the Hospital. On admission, the date and time are kept. The personal details of every patient includes name, Medical Recode Number (MRN), set of phone and one address (city, street, code). A separate register is to be held to store the information of the tests undertaken. Each test has unique episode No. , category and the final result of test. Number of tests may be conducted for each patient. Doctors are specialists in a specific ward and may be leading consultants for a number of patients. Each patient is assigned to one leading consultant but may be examined by other doctors, if required. 10

Hospital : ER Case Study u draw an ER diagram to represent the data requirements as following: – Identify the main entity types. – Identify the main relationship types between the entity types. – Identify attributes and associate them with entity or relationship types. – Determine candidate and primary key attributes for each (strong) entity type. – Determine the multiplicity constraints for each relationship. State any assumptions necessary to support your design. 11

Hospital : ER Case Study Ward Name {PK} Total_of_pat Doctor 1. . 1 Specializes in 0. . * Doc_NO{PK} Consultant 0. . * Examiner Admits Date Time 1. . 1 Leader Exams 0. . * Patient MRN{PK} Name Phone [1. . *] Address Code Street City consults 0. . * Test 1. . 1 conducts 0. . * Ep _NO{PK} Category Result 12

4) Department : ER Case Study u Identify the entities and relationships for the following description and draw an ER diagram. Departments, identified by ID, operate a variety of printers, each located in a particular room in a particular building. Printers are supplied by a number of suppliers, identified by name, with each supplier charging a different price for a given printer, but also providing different delivery delays, measured in days. A given room can have any number of printers, including none. 13

Department : ER Case Study 14

5) Journal : ER Case Study u Identify the entities and relationships for the following description and draw an ER diagram. Persons, described by their name, SSN, and address, subscribe to various journals. Each journal, identified by a title and an ISBN, has a set of numbered volumes and each of these has a set of numbered issues. Subscribers have an initial subscription date and a termination date for each journal to which they subscribe. 15

Journal : ER Case Study 16

TUTORIAL 17

Question 1 Suppose you are given the following requirements for a simple database for the National Hockey League (NHL): the NHL has many teams, each team has a name, a city, a coach, a captain, and a set of players, each player belongs to only one team, each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records, a team captain is also a player, a game is played between two teams (referred to as host_team and guest_team) and has a date (such as May 11 th, 1999) and a score (such as 4 to 2). Construct a clean and concise ER diagram for the NHL database. 18

Question 2 A university registrars office maintains data about the following entities: 1. courses, including number, title, credits, syllabus, and prerequisites; 2. course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; 3. students, including student-id, name, and program; 4. 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 for must be appropriately modeled. Construct an E-R diagram for the registrars of ce. Document all assumptions that you make about the mapping constraints. 19

Question 3 Consider a university database for the scheduling of classrooms for ‑final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the exam entity set, as course with attributes name, department, and c-number section with attributes s-number and enrollment, and dependent as a weak entity set on course room with attributes r-number, capacity, and building Show an E-R diagram illustrating the use of all three additional entity sets listed. 20