Question

You have been hired to design a relational database for a convenience store which is located within an apartment complex. The goal of the database is to keep track of the inventory sold in hopes of using the data to better meet the customer's convenience store needs. Up until your arrival, the store kept track of each customer’s purchases using a flat database log, as shown in the following table. Using the information provided, build a relational database that will allow for querying things such as products sold, customer purchases, total apartment purchases, and total spent per apartment. Include any created tables and identify the keys and key types that are used. Identify all relationships, labeling them 1:1, 1:N, or M:N.

Name Apt # Products Price Quantity
Joseph Anthony 1125 Orange Juice 4.59 1
Joseph Anthony 1125 Bread Loaf 2.29 1
Yolanda Burns 3221 Milk 3.67 1
Yolanda Burns 3221 Candy Bar 1.19 3
Francis Jordan 1138 Gum 0.99 2
Steve Miller 2221 Gum 0.99 1
Cho Lin 2239 Bread Loaf 2.29 1

Answer 1

Apartment (1)=====> (N) Purchases (M) =====> Product(1).

Step-by-step explanation:

So, in this question we are given the following; Name, Apt # , Products, Price and Quantity. With this data or parameters or information we will be able to know that there should be another parameters in a table which are;

=> Apartment: with this parameter and the apt # each person or Individual can be Identifed.

=> Purchases: this table will be about the details of the person or Individuals the bought the products and what quantity was bought.

=> Product: here, this parameter can be used in saving or storing the name of each products and the prices of each one of them.

Hence;

(1). APARTMENT = Apt#, Name => where Apt# will be the primary key because it is unique.

(2). PRODUCTS= Products, Price => where product is the unique key.

(3). PURCHASES = Apt #, product quantity => where Apt # is a foreign key and an attribute of product in the ''purchases" table.

Kindly check the attachment for the diagram