Question
David and Daniel (D&D) Appliances is an online appliance store headquartered in Bowling Green. The table above lists some of the data that D&D maintains
David and Daniel (D&D) Appliances is an online appliance store headquartered in Bowling Green. The table above lists some of the data that D&D maintains on its sales and inventory. The data are stored in a spreadsheet that must be manually updated for each new sale and invoice. This updating is error-prone, which has harmed the business. In addition, the spreadsheet has to be sorted in many different ways to retrieve the necessary data.
1.Type name into cell F19.
2.Normalize the above flat table (1st Normal Form) from D&D's spreadsheet into a relational database (3rd Normal Form) with multiple tables that do not have any of the data anomalies explained in your textbook.
3,With the normalized table design, create relational database on MS Access and print each table created.
4.From the DATABASE TOOLS of Access, establish relationship of tables created.
5.To test the database, prepare and print the following queries. For some questions, you may have to create two queries - one to calculate a total and the second to answer the questions asked.
a)Which customers (show by their names) made purchase from Martinez? Sort by customer name in ascending order.
b)Who (show his/her number, name, address, and credit limit) has the largest credit limit?
c)What are the sales made in October? List the invoice number, date, and amount. Sort by date.
d)What were the item numbers, price, and quantity of each item sold on invoice number 103?
e)How much did each salesperson sell? Sort by salesperson's name.
f)Which customers (show their names and cities) live in Arizona?
g)How much credit does each customer have available after deducting the sales amount? Sort by customer number ascending order.
h)How much (quantity and amount) of each item was sold? Include the description of each item in your answer and sort by item number?
i)Which customers (show their names and credit) still have more than $1000 in available credit?
j)For which items (show their description and quantity on hand) are there at least 100 units remaining on hand after deducting the units sold?
Attached is the data.
Also I completed questions 1 & 2
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started