Database system : Data Models

Chapter 2 Assignment CIS 171This assignment consists of 10 exercises, each worth 10 points. Create a document usingMicrosoft Word or a comparable word processor that can produce PDF files. Place yourname, date, “CIS 171”, and your section number at the top of the document. Answer eachquestion, clearly indicating the question number by your answer.If you are asked to build a conceptual data model, create an Entity-Relationship Diagram(ERD) based on a set of business rules. Each entity should be named, but attributes are notnecessary. Connect appropriate entities with relationship lines. Relationships should haveconnectivity symbols on both sides showing minimum and maximum cardinalities usingthe Crow’s Foot notation. Relationships do not require verb phrases if they are not obvious.Exercise 1Best Health is a rapidly growing health care organization that needs a database common toall of its outpatient medical clinics. Each clinic has at least one doctor. Patients arescheduled appointments for the doctors. When a patient comes in for an appointment, thedoctor may create an order for the patient that may include one or more medications. Thedoctor can write a prescription for any medication not available over the counter.Write out the business rules to describe this scenario, and use the business rules to build aconceptual model. Hint: The entities should include CLINIC, DOCTOR, PATIENT, APPOINTMENT,VISIT, ORDER, MEDICATION, and PRESCRIPTION.Exercise 2The Tiny College database was written in Microsoft Access. You are provided a diagramshowing the tables in the database and the relationships between them. Reverse-engineerthe business rules of Tiny College from the diagram.Exercise 3United Broke Artists (UBA) is a broker for not-so-famous artists. UBA maintains a smalldatabase to track painters, paintings, and galleries. A painting is created by a particularartist and then exhibited in a particular gallery. A gallery can exhibit many paintings, buteach painting can be exhibited in only one gallery. Similarly, a painting is created by a singlepainter, but each painter can create many paintings.Write out the business rules for the database required to store the data for UBA, and usethose business rules to build a conceptual model. Hint: The entities include PAINTER,PAINTING, and GALLERY.Exercise 4Create a conceptual model for Acme company based on the following set of business rules:• A sales rep writes many invoices. An invoice is written by one sales rep.• A sales rep is assigned to one department. A department can have many sales reps.• A customer can have many invoices. An invoice is produced for a customer.Exercise 5Write the business rules reflected in the ERDshown in the figure to the right. The ERD wascreated making some simplifying assumptions.For example, each book is written by only oneauthor. Also, remember that the ERD is alwaysread from the “1” to the “M” side regardless ofthe orientation of the entities.Exercise 6Create a conceptual model for the following setof business rules:• Each of the MegaCo Corporation’s divisions is composed of many departments.• Each of those departments has many employees assigned to it, but each employeeworks for only one department.• Each department is managed by one employee, and each of those managers canmanage only one department at a time.Exercise 7BigVid is a store that rents movies. Customers can rent movies that are all provided onDVD disks. The manager usually buys multiple copies of the more popular movies and putsall of them out on the shelf. Create a conceptual model for BigVid to track all of it’scustomers, videos, and orders.Exercise 8The KwikTite Corporation operates many factories, each of which is located in a region. Agiven region can have many factories within it. Every factory hires many employees.Create a conceptual model for KwikTite to track it’s regions, factories, and the employeeswho work in those factories.Exercise 9Create a conceptual model for the following scenario:An employee may have earned many degrees, and each degree may have beenearned by many employees.Exercise 10Create a conceptual model for the following scenario:A high school music student is allowed check out up to two instruments at a time,and an instrument can only be checked out to a single student at a given time. Eachinstrument is provided by a specific supplier, but a given supplier can provide theschool a variety of instruments.