Er Diagrams free essay sample

Exercise 1 1. Draw an E-R diagram for each of the following situations (if you believe that you need to make additional assumptions, clearly state them for each situation): a. A hospital has a large number of registered physicians. Attributes of PHYSICIAN include Physician_ID (the identifier) and Specialty. Patients are admitted to the hospital by physicians. Attributes of PATIENT include Patient__ID (the identifier) and Patient_Name. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the details of treatment (Treatment_Detail). Components of Treatment_Detail include Date, Time, and Results. 2. Table below shows a Grade Report that is mailed to stuat the end of each semester. We will write a custom essay sample on Er Diagrams or any similar topic specifically for you Do Not WasteYour Time HIRE WRITER Only 13.90 / page Prepare an ER diagram reflecting the data contained in Grade Report. Assume that each course is taught by one instructor. Millennium College Grade Report Fall Semester 199X Name: Campus Address: Major: Emily Williams 208 Brooks Hall Information Systems ID: 268300458 Course ID Title Instructor Name Instructo rLocation Grade IS 350 IS 465 Database Mgt System Analysis Codd Parsons B104 B317 AB 3. Prepare an E-R diagram for a real estate firm that lists property for sale. The following describes this organization: †¢ The firm has a number of sales offices in several states. Attributes of sales office include Office_Number (identifier) and Location. †¢ Each sales office is assigned one or more employees. Attributes of employee include Employee_ID (Identifier) and Employee_Name. An employee must be assigned to only one sales office. †¢ For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which she is assigned. †¢ The firm lists property for sale. Attributes of property include Property_ID (identifier) and Location. Components of Location include Address, City, State, and Zip_Code. †¢ Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties listed, or may have no properties listed. †¢ Each unit of property has one or more owners. Attributes of owners are Owner_ID (identifier) and Owner_Name. An owner may own one or more units of property. An attribute of the relationship between property ad owner is Percent_Owned. 4. Draw an E-R diagram for the following situation. Projects, Inc. , is an engineering firm with approximately 500 employees. A database is required to keep track of all employees, their skills, projects assigned, and departments worked in. Every employee has a unique number assigned by the firm, required to store his or her name and date of birth. If an employee is currently married to another employee of Projects, Inc. , the date of marriage and who is married to whom must be stored; however, no record of marriage is required if an employee’s spouse is not also an employee. Each employee is given a job title (for example, engineer, secretary, and so on). An employee does only one type of job at any given time, and we only need to retain information for an employee’s current job. There are 11 different departments, each with a unique name. An employee can report to only one department. Each epartment has a phone number. To procure various kinds of equipment, each department deals with many vendors. A vendor typically supplies equipment to many departments. We are required to store the name and address of each vendor and the date of the last meeting between a department and a vendor. Many employees can work on a project. An employee can work on many projects (for example, Southwest Refinery, California Petrochemic als, and so on) but can only be assigned to at most one project in a given city. For each city, we are interested in its state and population. An employee can have many skills (preparing material requisitions, checking drawings, and so on), but she or he may use only a given set of skills on a particular project. (For example, an employee MURPHY may prepare requisitions for the Southwest Refinery project and prepare requisitions as well as check drawings for California Petrochemicals). Employees use each skill that they possess in at least one project. Each skill is assigned a number, and we must store a short description of each skill. Projects are distinguished by project numbers, and we must store the estimated cost of each project