(Solved): CIS 365 Business Database ConceptsAssignment 1 -E/R Diagramming...

CIS 365 Business Database Concepts W2A1 – Extended ERDs (66 points)

Draw the ERDs for the following business rules using Gliffy. Copy/paste your diagrams to Word and then convert to .pdf for submission. Professional quality is expected. Identify all entities, attributes, relationships, primary & foreign keys, cardinalities and constraints where appropriate.

Each complete entity (including all attributes, primary key, and foreign key(s)) receives one point and each relationship (labeled with cardinalities in both directions) receives one point. Any constraint used correctly, receives one point. Primary keys will be bold or underlined, foreign keys in italics ONLY (no PK or FK on your diagrams). Label your relationships if there are more than one between the same two entities.

Make sure your diagrams are clear and easy to read - show one diagram per page (number them) - do not include the business rules with your diagrams. Include your name and section on all pages. Show resolved M:N relationships.

1. (6) A hospital has a large number of registered physicians which includes a physician ID and specialty. Patients are admitted to the hospital by physicians. The hospital tracks the patient ID, and 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 records the details of the treatment. Other requirements of treatment include date, time, and results.

2. (10) A real estate firm has the following business rules:

• • the firm has a number of sales offices in several states. Attributes of sales office include OfficeNo and location.
  • each sales office is assigned one or more employees. Attributes of employee include EmpID and EmpName. 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 (s)he is assigned.
  • the sales office lists property for sale. Attributes of property include PropertyID and location.
  • each unit of property has one or more owners. Attributes of owners are OwnerID and OwnerName. An owner may own one or more units of property. An attribute of the relationship between property and owner is PercentOwned.

3. (9) An art museum owns a large volume of works of art. Each work of art is described by an item code, title, type, and size; size is further composed of height, width, and weight. A work of art is developed by an artist, but the artist for some works is unknown. An artist ID, name, date of birth, and date of death (which is null for living artists) is recorded for each artist. Only data about artists for works currently owned by the museum are kept in the database. At any point in time, the status of a work of art is either on display at the museum, held in storage, away from the museum as part of a traveling show, or on loan to another gallery. If on display at the museum, the art's location within the museum is recorded. If the art work is in storage, storage date and return date from storage are recorded. A traveling show is described by a show ID, the city in which the show is currently appearing, and the start and end dates of the show. Many of the museum works may be part of a given show, and only active shows with at least one museum work of art need be represented in the database. Finally, the museum wants to retain a complete history of loaning a work of art to other galleries; a gallery ID, name, and city are recorded for other galleries. Each time a work of art is loaned, the museum wants to record the date the work was loaned and the date it was returned.

4. (11) Peck and Paw, Attorneys at Law require a database system based on the following business rules: An attorney is retained by one or more clients for each case. Attorney’s ID, name, address, city, state, zip, specialty (could be more than one), and bar (may be more than one) are recorded. Client information needed includes client ID, name, address, city, state, zip, phone, and date of birth. A client may have more than one attorney for each case. A client may also have more than one case.  Case ID, case description, and case type are maintained. An attorney may be working on more than one case. Each case is assigned to one and only one court where court ID, court name, city, state, and zip are required. Each court has one or more judges assigned and a judge can be assigned to exactly one court. For each judge, judge ID, name, and years in practice are stored.