HyperCase Additional Exercises

Chapter 13 - Designing Databases

  1. Review the entity-relationship diagram for the GEMS system. Currently, only the Client Name is kept on the Project Master data store (Projects entity). Modify the entity-relationship diagram to include a new Client entity. Each Project is for only one client, but each client may have many projects. Make sure that the diagram is in the third normal form. Define the primary key for the Client entity and secondary keys required to implement the Project - Client relationship. List any secondary (or alternate) keys that should be included on the Client table.

  2. Add a new Deliverable entity to the entity-relationship diagram for the GEMS system. It contains the Deliverable Number, Deliverable Name and Type of Deliverable (such as software, equipment, and so on). Each project may have many deliverables, and each deliverable may be for many projects. Include the relational file linking deliverables and projects. Define the keys and foreign keys required to implement the Deliverable entity.

  3. Add a new Country entity to the entity-relationship diagram for the GEMS system. It contains the Country Number as well as other fields (refer to the last exercise in Chapter 10). Each unique project may be done in only one country, but each country may have many projects. Define the keys and foreign keys required to implement the Country entity.

  4. Add a new Language entity to the entity-relationship diagram for the GEMS system. Each country may have many languages, and each language may be spoken in many countries. Show the Language entity related to the Country entity in the third normal form. Define primary and foreign keys for the Country, Country Language and Language entities.

  5. Add a new Contact Person entity to the entity-relationship diagram for the GEMS system. Each country may have several contact persons, but each contact person works in only one country. List the primary and foreign keys necessary to show the relationship between the Contact Person and Country tables. List any secondary (or alternate) keys that would be useful on the Contact Person entity.

  6. After some careful thought, it has been decided that a resource would be able to fulfill several different requirements. Modify the GEMS entity so that a Resource may fulfill many Requirements.

  7. Create an entity-relationship diagram for the Ecommerce system. Entities are Customer, Item, Order, Order Item and Backordered Item. Each Customer may place many Orders but each Order is for only one Customer. Each Order relates to many Order Items and each Order Item is for one Order. Each Item may be sold many times or not be currently selling at all. Each Order Item is for one Item. Backordered Items are for one Item and for one Customer. Each Customer may have one or more Backordered Items. Each Item may have one or more Backordered Items but each Backordered Item is for only one Item.

  8. Define the primary, foreign, and alternate keys for exercise 6.