Have a Question ?

Home / Answered Questions / CIS365 / cis-365-business-database-conceptsassignment-1-e-r-diagramming-q-947

(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.

 

 

  1. (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.
 
  1. (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.

 

 

  1. (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.
 
  1. (15) Trendy Trade is a rental business that rents musical instruments to bands. For each instrument, Trendy Trade keeps track of a unique serial number as well as the model and brand, year the instrument was made, and the age (in years) of the instrument. For each rental, the rental date and return date are recorded. The customers of Trendy Trade are bands. A unique band identifier and name are stored as well as the band’s address, contact person’s name, and several phone numbers (band members, contact person’s phone, etc.). A band does not have to rent any instruments, but may rent up to 30. Each instrument may be rented by any band. Repair technicians maintain the instruments. An instrument may not need repair, but if it does, it can be repaired by several technicians; the date of the repair and the tech’s identifier is recorded as well as the reason for the repair. For each technician, a unique technician number, name, address, and mobile phone number is kept. Shows that the bands perform in are also tracked. Each performance has a date and total tickets sold. For each show, a unique show identifier is composed of using the show venue name and the stage number (1 if there is only 1 stage). The show type and show name (a show may not have a name) is also recorded. The bands may perform in many shows and each show must have at least one band performing. Trendy Trade keeps track of the number of shows that each band performs in.

 

 

  1. (15) Superior Styles is an exclusive fashion designer business. Each designer designs many outfits, but the outfit has only one designer. For each designer, a unique identifier and name (first and last) are kept in the database. Outfits are sold to exactly one customer (in advance). A unique outfit identifier,

planned date of completion and unreasonable price is recorded. The customer’s name (first and last) and phone are stored along with a customer identifier. When the outfit is sold, the date of sale and actual sale price is recorded. The company does not keep track of customers who have not made any purchases. A tailoring tech must work on at least one outfit. Info kept for the tailoring techs include an identifier, name (first and last), and phone. Each outfit has at least one tailoring tech, but can have many. Superior Styles will keep track of the date when a tailoring tech started working on a particular outfit, and the completion date – these may be different dates for different techs working on the same outfit. Designers can participate in a number of fashion shows. Each show can feature one or two designers from Superior Styles. Shows that do not feature our designers are not tracked. For each show a unique show identifier as well as the date and location of the show is recorded.

 

 

Be sure to use the ‘official class file format’ for naming your file: firstinitialLastname-W2A1.pdf

(e.g. jsmith-W2A1.pdf). The link closes precisely at 11:59pm – submit early!

 

 

Email submissions to the professor will not be accepted under ANY circumstances – ALL submissions to Canvas only.



We have an Answer from Expert View Expert Answer

Expert Answer


 

  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.

AdmittingPhysID

 
 

 

 

We have an Answer from Expert
Buy This Answer $30

-- OR --

Subscribe To View Unlimited Answers
Subscribe $12 / Month