Question

You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future.

You received an email from the client with the list shown below. Currently, this list tracks their daily business. To prepare for the meeting with the client, you develop a first draft prototype of a relational database.

In your one page MS Word pdf report include the following:

1. 
   . Table(s) needed. Hint: Think of the table’s purpose.

2 Fields for each table (list ‘primary key field’ first).

3. . Add a page footer with your full name, date prepared, and filename
4. . Name at least 7 additional fields (in addition to those listed in table below).
5. . Which field or fields shown below do not belong in a table? Explain why.
6. Identify the primary key for each table by underlining.
7. . Draw an E-R diagram showing relationships between tables.

For the E-R diagram you can use Crow’s Feet symbols or join line between tables at the field level (primary key to foreign key).

You choose the method of how to show these relationships. Some options include: hand drawn with a picture of it inserted into a document, MS Access, or graphics software.

8. . Develop 3 questions to ask client at your face-to-face meeting next week that will assist in refining the prototype’s design.

LastName	FirstName	InvoiceDate	InvoiceItem	Quantity	Price	Tax	Total
Shire	Robert	12/14/2012	Antique Desk	1	3000	249	3249
Goodyear	Katherine	12/10/2012	Dining Table	1	1000	83	1083
Tierney	Doris	12/23/2012	Book Shelf	1	250	20.75	270.75
Anderson	Donna	12/22/2012	Desk Lamp	1	45	3.74	48.74
Shire	Robert	12/14/2012	Antique Chair	2	125	20.75	270.75

Answer 1

KINDLY UPDATE THE GIVEN ANSWER TO A PDF AND PROVIDE YOUR PERSONAL DETAILS IN THE FOOTER OF IT.

1.IDENTIFYING TABLES:(primary keys are in bold)

• Customer- It will store the details of the customer that is going to purchase the item.
  • customer_id
  • first_name
  • last_name
  • order_id:foreign key to order placed by customer
  • mobile_num
• Employee:It will store the details of the employee that takes the order
  • emp_id
  • first_name
  • last_name
  • hire_date
  • area_id:foreign key to area to store in which area shop the employee works in
  • age
  • mobile_num
• Item:To store details of each item and keep a track in inventory
  • item_id
  • item_name
  • item_unit_price
  • item_instock
• Area: To store details of each area a store is in.
  • area_id
  • area_address
• Order:To keep track of orders placed by customers
  • order_id
  • customer_id:foreign key to customer who placed order
  • employee_id:foreign key to employee who took order
  • invoice_date
  • area_id:foreign key to area where order is placed
  • order_detail_id
• OrderDetails:To store the products added in each order and the billed amount.
  • order_detail_id
  • order_id:foreign key to order placed
  • item_id:foreign key to item that is in the order
  • item_unitprice
  • item_quantity
  • discount
  • total_bill
• ER DIAGRAM:

• questions for clients:
  • Will the payment be accepted with different methods except cash online banking,cards?If yes we can add a new table for payment.
  • Will there be a team of employees and a manager for each store? We can add a manager table as well then.
  • Will there be different departments for each store?if yes we can add a department store as well.
  • Will there be shifts for employees?,we can add a field in employee table for that and mark their attendance .