The Queen Anne Curiosity Shop sells both antiques and current-production househo

Question

The Queen Anne Curiosity Shop sells both antiques and current-production household items that complement or are useful with the antiques. For example, the store sells antique dining room tables and new tablecloths. The antiques are purchased from vendors, including individuals, and distributors. The stores customers include individuals, owners of bed-and-breakfast operations, and interior designers. The two tables below are a sample list of customers and sales (Sale_Customer Table) and a sample list of purchased inventory items and vendors (Purchase_Vendor Table). Describe specific problems that are likely to occur when inserting, updating, and deleting data in these spreadsheets. e.g., if I update column X in list Y, the problem is Split both Table 1 and Table 2 into two tables such that each has only one theme, without loosing information. Explain how the tables in your answer to Part 2 will eliminate the problems you identified in Part 1. Using the 4 tables you designed in Part 3, how should I look for answers to the following questions? For each question, specify (1) the names of the tables which I should look up, (2) the precise information I should look up in each table, and (3) the order in which these tables should be looked up. (a) How many items were purchased from vendor European Specialist? (b) How many items from European Specialist were sold? (c) Is there any customer from Chicago bought any item from European Specialist?

Explanation / Answer

1. The Sale.customer table contains dupilcate data.. Hence if we want to change the customer name or address, we need to do it in each and every row.. any programming errors here can cause data inconsistencies. Also, while inserting, we need to put the same data again and again, which increases database size. Similarly, for vendor-purchase table, the vendor names and addresses are duplicate, they will also cause same problem. In tems of deletion, it is very difficult to delete a particualr row in these tables, because if we query by customer name, they we are likely to get multiple rows.

2. The customer order table should be splitted into customers and orders table seperately.

Customers(CustomerId, CustomerName, Phone, Address)

Orders(OrderId, ItemName, SalePrice, SaleDate, CustomerId)

Similarly,

Purchases(PurchaseId, ItemName, PurchasePrice, PurchaseDate, VendorId)

Vendors(VendorId, VendorName, VendorPhone, VendorAddress)

3. Now if we need to update the name of the customer, we can update it in customers table corresponding to customerId, which is primary key.. His address and phone can also be changed. Also, there is no data duplicacy. Due to the foreign key relationship, if we delete a customer from our database, we can delete all his orders also from our tables.

4.

a)
First get the vendorId from vendors tables for European Specialist. Then using that vendorId, we should check the purchases table for whether any items were purchased or not. Depending upon that, we can count the rows with above criteria.
select count(*) from purchases, vendors where vendors.vendorName='European Specialist' and vendors.vendorId = purchases.VendorId

b)

We should get the vendorId from vendors tables for European Specialist. Then using that vendorId, we should check the purchases table for what items are purchased form them, then those items need to be searched in orders table for if they have been sold or not. We can then count the results.

select count(*) from purchases, vendors where vendors.vendorName='European Specialist' and vendors.vendorId = purchases.VendorId and purchases.itemName in (
select distinct itemName from Orders
)

c)

We should get the vendorId from vendors tables for European Specialist. Then using that vendorId, we should check the purchases table for what items are purchased from them, then those items need to be searched in orders table against the orders which are placed by the customers based on Chicago.

select count(*) from purchases, vendors where vendors.vendorName='European Specialist' and vendors.vendorId = purchases.VendorId and purchases.itemName in (
select distinct itemName from Orders, customers where order.CustomerId = customers.customerId and customers.address like '%Chicago%'
)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.