QUESION 1:)

A:)-

Draw an entity relationship diagram (ERD) for the following situations: 1. Whenever new patients are seen for the rst time, they complete a patient information form that asks their name, address, phone number, and insurance carrier, all of which are stored in the patient information le. Patients can be signed up with only one carrier, but they must be signed up to be seen by the doctor. Each time a patient visits the doc- tor, an insurance claim is sent to the carrier for payment. The claim must contain information about the visit, such as the date, purpose, and cost. It would be possible for a patient to submit two claims on the same day. 2. The state of Georgia is interested in designing a database that will track its researchers. Information of interest includes researcher name, title, position, university name, location, enrollment, and research interests. Each researcher is as sociated with only one institution, and each researcher has several research interests. 3. A department store has a bridal registry. This registry keeps information about the customer (usually the bride), the products that the store carries, and the products for which each customer registers. Customers typically register

for a large number of products, and many customers register for the same products. 4. Jim Smiths dealership sells Fords, Hondas, and Toyotas. The dealership keeps information about each car manufac- turer with whom it deals so that employees can get in touch with manufacturers easily. The dealership also keeps information about the models of cars that it carries from each manufacturer. It keeps such information as list price, the price the dealership paid to obtain the model, and the model name and series (e.g., Honda Civic LX). The dealership also keeps information about all sales that it has made. (For instance, employees will record the buy- ers name, the car the buyer bought, and the amount the buyer paid for the car.) To allow employees to contact the buyers in the future, contact information is also kept (e.g., address, phone number, e-mail).

B):-

The system development team at the Wilcon Company is working on developing a new customer order entry system. In the process of designing the new system, the team has identied the following data entity attributes:

Inventory Order

Order Number (identier)

Order Date

Customer Name

Street Address

City

State

Zip

Customer Type

Initials

District Number

Region Number

1 to 22 occurrences of:

Item Name

Quantity Ordered

Item Unit

Quantity Shipped

Item Out

Quantity Received

a. State the rule that is applied to place an entity in rst normal form. Revise this data model so that it is in rst normal form.

b. State the rule that is applied to place an entity into second normal form. Revise the data model (if necessary) to place it in second normal form.

c. State the rule that is applied to place an entity into third normal form. Revise the data model to place it in third normal form.

d. What other guidelines and rules can you follow to validate that your data model is in good form?