Database During an interview with a small business owner, a database designer is given two receipts (as shown at the end of this document). To start the data modeling process, the designer decides on the following entities:

Store (the store at which the purchase has occurred)

Invoice (basic information about the purchase)

InvItem (details on the items purchased shown on invoice)

What are the attributes for each of the above entities as shown in the receipts?

What type of relationship exists between the following pairs? Explain your answer.

Store and Invoice:

Invoice and InvItem:

Open Microsoft Access and create three new tables (Store, Invoice and InvItem) to store the data from the two receipts. (A few resources for Microsoft Access are mentioned at the end of this document.) For each table, create the fields necessary to store the data. Show a snapshot of the Design view of the two tables. How many fields does each table have?

Store the data from the receipts in these tables. Show a snapshot of the Datasheet view of the two tables. How many records does each table have?

To build the relationship between Store and Invoice entities, add a field to the Invoice table by clicking on the drop down arrow next to Click to Add and choose Lookup & Relationship. Choose I want another table or query and click Next. Then choose the Store table. Select ID (to show up on the right side), choose ID to be used for sorting and then click Finish. Name this field StoreID. In the items table, set the StoreID of each item to the store ID of the related store. ( The relationship between the tables can also be created using another way which is described in the resources/links below).

To build the relationship between Invoice and InvItem entities, add a field to the InvItem table by clicking on the drop down arrow next to Click to Add and choose Lookup & Relationship. Choose I want another table or query and click Next. Then choose the Invoice table. Select ID (to show up on the right side), choose ID to be used for sorting and then click Finish. Name this field InvoiceID. In the InvItem table, set the InvoiceID of each item to the Invoice ID of the related invoice. ( The relationship between the tables can also be created using another way which is described in the resources/links below).

Use the menu item Database Tools > Relationships to see the relationship diagram. Show a snapshot of this diagram. What are the entities? Which attributes are linked by a line? What does it mean? Hint: Move the Invoice entity between the Store and InvItem entities.

In Microsoft Access, the primary key of each table is shown by a key symbol. Which field is the primary key for each of the 3 tables above? Is there a more natural primary key for the Invoice table? What are the foreign keys, if any?