School of Computer ScienceWestern Illinois University

IS 342

Assignment#3 –I (10 points) Analyst: ______

Data Modelling with ER Diagrams

Diagrams must be neatly drawn and legibly labeled. (It is not necessary though to use a ruler to draw the diagrams). Only completed assignments are accepted. There is a maximum penalty of 5 points if these requirements are not met. Academic dishonesty of any sort is strictly prohibited. PLEASE USE THIS PAPER ONLY.

PART (A) – 5 PTS

Provide at least two different examples of the following types of relationships among entity classes and draw ER fragments corresponding to them. There is no need to show attributes here. The examples must be other than those discussed in class or in the textbook but must be relevant to databases i.e. they should be about information an organization might be interested in. Use correct notation.

  1. one-to-one
  2. one-to-many
  3. many-to-many
  1. recursive-one-to-many (unary one to many) -- one example is sufficient.
  2. recursive-many-to-many (unary many to many) – one example is sufficient.

Hint: For the recursive/unary relationships, think of several entity classes. Within each entity class (e.g. customer) try to visualize the individual entities (e.g. IBM, Intel etc.). Then try to visualize what possible relationships these entities might have amongst themselves. It helps to think and write down all possible relationships among entities. For example between two people (belonging to eclass PERSONS) there might be the following relationships: father, brother, relative, colleague etc. Try to enumerate all of these and identify what might be appropriate in the database/business context.

PART (B)– 5 PTS

Draw ER diagrams (ER fragments actually), using appropriate notations, for the following situations. Do not show max-min cardinalities:

  1. A client signs a contract with a company. The client has an id, name, address and phone and the contract has a date from which it is effective (eff dt). The company also has a name, address and phone. Use opposite side if more space is needed.
  1. A building is either a residential building or a commercial building. A commercial building can be an office building and/or a warehouse and/or a factory or …….
  1. A person(s) may bid for one or more items at an auction. The starting bid (bid price) should be greater than the minimum bid price. There are multiple auctions at any given time. Persons could bid multiple times for the same item and for multiple items in multiple auctions.
  1. An employee’s computer can have one or more problems. It is necessary to keep track of the following information, identification of employee, employee name, location, problem, problem status, description, computer identification and manufacturer. Problems can be one of hardware, software or network related.
  1. A national auto-repair franchise needs a database. The database must include the following data items: customer name, customer phone#, date of repair, make of automobile, model of automobile, description of work, parts needed to carry out the repair, charges for parts, charge for labor and total charges and the branch which serviced the customer. It is necessary for the database to have a record of repairs carried out. List entity classes and attributes and draw an ER diagram for this situation.

1