ER Model Design for Various Databases | Exercises Database Programming

IS112 – Data Management

Extra In-Class Exercise 1 Page 1

Extra In-Class Exercise 1: ER Modeling

Note: If the question does not provide any clear statement to define the minimum cardinality, you can decide

whether the minimum cardinality is zero or one.

1. Design an ER model for keeping track of the information about votes taken in the U.S. House of

Representatives during the current two-year congressional session.

o The database needs to keep track of each U.S. State’s Name (e.g., Texas, Washington, Michigan) and the

Region of the State (in total there are five regions: Northeast, Midwest, Southeast, Southwest, and West).

o Each Congressperson in the House of Representatives is identified by his or her Name. Other information, such

as the state he represents, the StartDate when the congressperson was first elected, and the political party

(Republican, Democrat, Independent, or other) to which he or she belongs, is also recorded. Each

congressperson only represents one state, and each state has at least one congress person.

o The database keeps track of each Bill (i.e., proposed law), including the BillName, the DateOfVote on the bill,

whether the bill PassedOrFailed, and the Sponsor (the congressperson(s) who sponsored – that is, proposed -

the bill). Assume the BillName is unique, and each bill must be proposed by at least one congressperson, and a

congressperson could propose zero/one/many bills.

o The database keeps track of how each congressperson voted on each bill (i.e., support vs. against). We assume

a bill is voted by many congresspersons and each congressperson votes zero/one/many bills.

2. Draw an ER diagram for the following situation: a lab has several chemists who work on one or more projects.

Chemists also may use certain kinds of equipments on some projects.

o Attributes of Chemist include EmployeeID (identifier), Name, and PhoneNo.

o Attributes of Project include ProjectID (identifier), and StartDate.

o Attributes of Equipment include SerialNo (identifier) and Cost.

o Each chemist is assigned to at least one project and each project has at least one chemist assigned. When a

chemist is assigned to a project, both the AssignedDate and the Role played by the chemist in the assigned

project must be recorded.

o The organization has a list of equipment which can be borrowed by chemists working on projects. The

organization wishes to record BorrowedDate and ReturnedDate, the date when a given equipment item was

borrowed by a particular chemist working on a specified project, and the date when the equipment was returned.

A given equipment item can be borrowed zero time/once/multiple times, although it can only be borrowed by at

most one chemist at any time. A chemist can borrow zero/one/many equipment items over the time, although

she/he can borrow at most one item at any time.

3. Draw an ER model to keep track of Stocks Trading.

o Each Stock has StockID (unique identifier), and the name.

o Each Trader has a TraderID (unique identifier) and the trader’s name.

o The price of each stock changes, and the database needs to keep track of stock prices. We assume at any given

date (date/time), the price of a stock is fixed. Each Stock price is identified by the effective date along with

Stock ID. For example, Stock ABC is priced at SG$1.21 at 11:10 am Jan 12, 2013, and it is priced at SG$1.22

at 11:15 am Jan 12, 2013. In other words, starting from 11:10 Jan 12, 2013, Stock ABC is priced at SG$1.21

and there is no change until 11:15am Jan 12, 2013.

o A trader can purchase any quantity of the stock at varying stock price. For each purchase, the date at which a

given Stock is traded, the price, and the quantity are recorded in the database.

Partial preview of the text

Download ER Model Design for Various Databases and more Exercises Database Programming in PDF only on Docsity!

Extra In-Class Exercise 1: ER Modeling

Note: If the question does not provide any clear statement to define the minimum cardinality, you can decide whether the minimum cardinality is zero or one.

Design an ER model for keeping track of the information about votes taken in the U.S. House of Representatives during the current two-year congressional session. o The database needs to keep track of each U.S. State’s Name (e.g., Texas, Washington, Michigan) and the Region of the State (in total there are five regions: Northeast, Midwest, Southeast, Southwest, and West). o Each Congressperson in the House of Representatives is identified by his or her Name. Other information, such as the state he represents, the StartDate when the congressperson was first elected, and the political party (Republican, Democrat, Independent, or other) to which he or she belongs, is also recorded. Each congressperson only represents one state, and each state has at least one congress person. o The database keeps track of each Bill (i.e., proposed law), including the BillName, the DateOfVote on the bill, whether the bill PassedOrFailed, and the Sponsor (the congressperson(s) who sponsored – that is, proposed - the bill). Assume the BillName is unique, and each bill must be proposed by at least one congressperson, and a congressperson could propose zero/one/many bills. o The database keeps track of how each congressperson voted on each bill (i.e., support vs. against). We assume a bill is voted by many congresspersons and each congressperson votes zero/one/many bills.
Draw an ER diagram for the following situation: a lab has several chemists who work on one or more projects. Chemists also may use certain kinds of equipments on some projects. o Attributes of Chemist include EmployeeID (identifier), Name, and PhoneNo. o Attributes of Project include ProjectID (identifier), and StartDate. o Attributes of Equipment include SerialNo (identifier) and Cost. o Each chemist is assigned to at least one project and each project has at least one chemist assigned. When a chemist is assigned to a project, both the AssignedDate and the Role played by the chemist in the assigned project must be recorded. o The organization has a list of equipment which can be borrowed by chemists working on projects. The organization wishes to record BorrowedDate and ReturnedDate, the date when a given equipment item was borrowed by a particular chemist working on a specified project, and the date when the equipment was returned. A given equipment item can be borrowed zero time/once/multiple times, although it can only be borrowed by at most one chemist at any time. A chemist can borrow zero/one/many equipment items over the time, although she/he can borrow at most one item at any time.
Draw an ER model to keep track of Stocks Trading. o Each Stock has StockID (unique identifier), and the name. o Each Trader has a TraderID (unique identifier) and the trader’s name. o The price of each stock changes, and the database needs to keep track of stock prices. We assume at any given date (date/time), the price of a stock is fixed. Each Stock price is identified by the effective date along with Stock ID. For example, Stock ABC is priced at SG$1.21 at 11:10 am Jan 12, 2013, and it is priced at SG$1. at 11:15 am Jan 12, 2013. In other words, starting from 11:10 Jan 12, 2013, Stock ABC is priced at SG$1. and there is no change until 11:15am Jan 12, 2013. o A trader can purchase any quantity of the stock at varying stock price. For each purchase, the date at which a given Stock is traded, the price, and the quantity are recorded in the database.

A shipping company owns many ships. For each ship, the following information is kept: a unique identification number, its name and the year purchased. Each container is assigned to only one ship, and a ship carries one/many containers. A container is identified by the ship’s identification number and its own container number. For each container we need to keep track of its capacity (in cubic meters). Each ship has a sailing schedule for each journey which stores the expected departure date from one port and the expected arrival date to another port. For example, ship with identification number 001 has two sailing schedules, schedule 1 and schedule 2. Schedule 1 is to leave Singapore port in Jan 01, 2011 and to reach Hong Kong port in Jan 08, 2011, and schedule 2 is to leave Hong Kong port in Jan 15 2011 and to reach Shanghai Port in Jan 20 2011. A port is identified by its unique name. The daily charge for use of the port and the country it is located are also recorded.
ABC Turf Club wants to keep information about Horse Racing. Draw an ER model to keep the following information: o A race (attributes: RaceId (identifier), date) is held on a course (attributes: CourseId (identifier), Length, Surface type). o A horse has attributes: HorseId (identifier), Name, Date of Birth, Breed and Age. o A jockey has attributes JockeyId (identifier), Name, Address, Date of Birth. A jockey can ride many horses in different races but during one race we assume that a jockey can ride one horse. o A horse competes in at most one race on a course on a particular date. Over time, a horse can compete in many races on many courses. A horse can have many jockeys for different races. However, during one race we assume that a jockey can ride one horse and a horse has one only jockey. The database is interested in keeping track of the jockey who rides a horse in a given race and the result of the race (i.e., who is the no 1, who is the no 2, and so on).
Read the case study carefully and draw the ER Diagram for the database of SMU Hospital. o SMU Hospital has various Departments like Orthopedic, Pediatric, Optometric etc. Each department has attributes DepartmentID (identifier) and DeptName. o Every Department has a large number of registered physicians whose attributes include Physician ID (identifier), Specialty, PhysicianName and ContactNumber. A physician is attached to one and only one department and a department has one or more physicians. o A Patient has a unique PatientID and his PatientName is recorded as well. The hospital keeps tracks of the bed information: BedNo (identifier), Location. o We assume that a patient can be admitted multiple times to the hospital. Whenever a patient is admitted to the hospital, a patient record is created which keeps following information: - The physician who admits the patient (assumption: a patient must have one admitting physician and one physician can admit any number of patients) - The physician (if any) who treats the patient (assumption: a physician can treat any number of patients, and a patient is treated by zero/one physician during his/her stay at the hospital) - The bed which is assigned to the patient (assumption: a bed can be empty if it is not assigned) - The period that the patient is staying in the hospital A patient admitted to the hospital many times could be admitted by different physicians and treated by different physicians.

Draw an ER model for the following situation: An engineering firm needs to keep track of employees, their skills, projects assigned, departments worked in. o Each employee has a unique EmpNo. The database needs to keep information of employee Name, Birth Date and Job Title. If an employee is married to another employee, the database needs to record his/her spouse and Date of Marriage. Assume that an employee marries at most once and we need not keep track of the marriage if an employee’s spouse is not an employee. o There are many departments in the firm. Attributes of department include Name (identifier) and Phone Number. Each employee reports to one department and one department has many employees. o An employee can have many skills. Each skill is assigned a unique SkillNo. A short Description of the skill needs to be stored. We assume each employee has at least one skill, and each skill is owned by at least one employee. Skills owned by each employee are stored. o Projects are identified by ProjectNo. The database needs to keep the EstimatedCost of each project. o The engineering firm runs project in multiple cities. For each city, the database needs to keep track of the city’s Name (identifier), State and Population. For each project, the engineering firm runs it in one or multiple cities with one of them being the main city. The database needs to keep track of the cities related to each project, as well as the main city of each project. o An employee is assigned to one or more projects in the various cities based on his skills. For each assignment, the database needs to keep track of a) an employee who receives this assignment; b) a project that this assignment is about; c) the city, one of the cities related to the project, that the assigned employee needs to work on the project; d) start date and end date of this assignment; and e) the set of skills that the employee will be using in this assignment. We assume a) an employee can be assigned many times, and b) for each city that is related to any given project, at least one employee is assigned to that city working on that given project. o Each department deals with many vendors to get different equipment. A vendor typically supplies equipment to many departments. For a vendor, we record vendor Name (identifier) and Address. For equipment, we record equipment SeriesNo (identified), Name, and Description. In addition, we also record the list of equipment that is provided by each vendor, together with the unit price of each equipment. We assume the unit price of each equipment is not fixed, and it might change from vendor to vendor. We also assume each equipment is provided by at least one vendor and each vendor provides at least one equipment. A purchase order has to be issued when a purchase is made. It is assumed that a purchase order is issued by one department regarding the purchase of equipment from one vendor at one time, and one purchase may include one or multiple equipment. For each purchase order, the database needs to keep track of the purchase OrderNo (identifier), the issued department, the corresponding vendor, the Quantity of each equipment bought, the total price, and the Purchase Date.

Design an ER model to assist in scheduling classes. After several interviews, it is clear that the system has the following entities, attributes, and initial business rules. o Room is identified by BuildingID and RoomNo and also has a Capacity. A room can be either a lab or a classroom. If it is a classroom, it has an additional attribute called BoardType. If it is a lab, it has two additional attributes, i.e., Equipment which keeps track of all the equipments available in the lab and NeedBackupPower? That is a Boolean to indicate whether the lab requires a backup power. Note a lab may have multiple equipments. o Media is identified by mediatype and it has also attribute TypeDescription. Please note: Here we are tracking types of media (such as VCR, projector, etc), not the individual piece of equipment. Tracking of equipment is outside of the scope of this question. o Computer is identified by ComputerType and has attributes TypeDescription, Diskcapacity and Processorspeed. Please note, as with media, we are tracking only the type of computer, not an individual computer. You can think of this as a class of computers (e.g., MACBook Pro). o Instructor is identified by EmpID and has attributes Name, Rank, and OfficePhone. o Timeslot has identifier TSID and has attributes DayofWeek, StartTime, and EndTime. o Course has identifier CourseID and has attributes CourseDescription and Credits. Courses can have one, none, or many prerequisite courses, and a course could be prerequisite for zero, one, or many other courses. A course has one or more sections. o Each section has attributes SectionId, Semester (ie. AY2014-15 Term2) and EnrolmentLimit. Notice that the EnrolmentLimit for each section varies each semester. Within one semester, sections of the same course must have different SectionId. However, sections of the same courses in different semesters may have the same SectionId. Here are some business rules identified. o An instructor teaches one, none, or many sections of a course in a given semester while a section of a course in a given semester is taught by only one instructor. o An instructor may specify his preferred timeslot(s). o Each given section of a course in a specified semester is taught by one instructor and offered in one room at one timeslot. A room can be the venue of zero/one/many sections within a specified semester, but it can only be a venue for at most one section during one timeslot. During one time slot, zero/one/many sections can be offered. o A room can have one type of media, several types of media or no media. A media type can be in zero/one/many rooms. o Instructors are trained to use one, none, or many types of media. o A lab has one or more computer types. However, a classroom does not have any computers. A computer type can be in zero/one/many labs. o A room cannot be both a classroom and a lab. There also are no other room types to be incorporated into the system.

Design an ER model for a real estate firm that lists property for sale (from Modern Database Management 10 th Edition, Page 1 44 , Problem 20 ) o The firm has a number of sales offices in different locations. Each sales office is identified by its OfficeNo and location. o Each sales office has many employees and one employee is assigned to only one sales office. Attributes of employee include EmployeeID (identifier) and EmployeeName. o Each sales office is managed by one of the employees. An employee manages at most one sales office. o The firm lists properties for sale. Attributes of property include PropertyID (identifier) and Location. Components of location include Address, City, State and Postal Code. o Each property is listed by one and only one sales office while a sales office may list any number of properties. o Each property has one or more owners. The database keeps track of Owner’s ID (identifier) and Name. An owner owns one or more properties. The percentage of a property owned by each owner is tracked by the database.
Read the case study carefully, identify entities, attributes, relationships and draw the ER Diagram. o Harvey Norman is a major retail company selling electrical appliances. This company has a chain of units operating at many locations. The Unit Number (identifier), unit Name, Location and PhoneNo are recorded. o This company sells different types of appliances made by different manufacturers. The information of the manufacturer like Name (identifier), Location, Phone and the Appliances they make are recorded. o The appliances have a ModelNo (identifier), Price, and the Manufacturing Date. We assume each appliance is supplied by one manufacturer, while each manufacturer supplies at least one appliance. o There are small retail companies who are the gold customers for Harvey Norman. These companies place bulk orders with Harvey Norman. The company’s information like Name (identifier), Address, and Phone Number are stored in the system. o Gold customers can place bulk orders to purchase appliances. For each order placed, the system keeps track of the OrderID (identifier), Order Date, order taken by which Harvey Norman unit, order placed by which gold customer, appliance(s) ordered, and the quantity of each ordered appliance. We assume each order placed is to purchase one or many appliances. o Harvey Norman has warehouses in different locations, whose information like WarehouseNo (identifier), Location, and Phone are stored. The appliances must be stored in the warehouse. We assume the same appliance can be stored in different warehouses and a warehouse can store different appliances. The quantity of each appliance available in each warehouse is maintained.
Design an ER model to keep track of drugs. Pharmaceutical companies produce drugs. A pharmaceutical company can be recognized by its ID, and its Name and Startdate are also recorded in the database. The produced drug is identified by the company’s ID and its own Name, and it also has a Formula. Pharmaceutical companies usually have long-term contracts with pharmacies for the selling of their drugs. Each contract is between one pharmaceutical company and one pharmacy, corresponding to the selling of one particular drug. Each contract has a Start Date, an End Date and Text of the contract. A pharmacy sells many drugs from different pharmaceutical companies. The same drug may have different prices in different pharmacy or even in the same pharmacy at different times. We need to keep track of the selling price of each drug in each pharmacy at different times. We are also interested in pharmacy’s Name, Address and Phone number. Name together with address identifies each pharmacy.

Design an ER model for the following set of business rules and state clearly any assumption made (from Modern Database Management 10 th^ Edition, Page 1 46 , Problem 27 ): o Attributes of lawyer are LawyerID (identifier), Name, Address, Specialty (may be more than one) o Attributes of person are PersonID (identifier), Name, Address, Telephone, DateofBirth. o Attributes of case are CaseID (identifier), CaseDescription, CaseType, the Accused(s), and the Accuser(s). We assume the accused and the accuser(s) refer to persons. o Each lawyer is retained by a person for a case. A person may have more than one case and a person may have more than one lawyer for each case. A lawyer may have more than one case as well. The system keeps track of the period (StartDate, EndDate) when a lawyer is retained in one case by one person. o Attributes of court are CourtID (identifier), Name, City, State and ZipCode. Each court has one or more judges assigned to it. Attributes of judge include JudgeID (identifier), Name, and Years in Practice. Each judge is assigned to exactly one court. o Each case is assigned to one and only one court. Among all the judges related to that court, one or a panel of judges will be assigned to the case.

ER Model Design for Various Databases, Exercises of Database Programming

Related documents

Partial preview of the text

Download ER Model Design for Various Databases and more Exercises Database Programming in PDF only on Docsity!

Extra In-Class Exercise 1: ER Modeling