Question

Answer this from the following tables:

1. Create the logic model based in the tables provided.

2. Denormalize the tables using Inner Join.

CUSTOMER CustomerID FirstName LastName Phone Email Nikki Kaccaton 723-543- [email protected] 100 1233 Brenda Catnazaro 723-543- [email protected] 2344 Bruce LeCat 723-543- [email protected] 3455 Betsy Miller 725-654- Betsy.Miller@ somewhere.com 105 110 3211 George Miller 120 Kathy Miller 125 725-654- George Miller@ somewhere.com 4322 723-514- Kathy. Miller@ somewhere.com 9877 723-514- Betsy.Miller@ elsewhere.com 8766 Betsy Miller 130 INVOICE InvoiceNumber CustomerID Dateln DateOut Subtotal Tax TotalAmount 2013001 100 10/4/2013 10/6/2013 158.5 12.52 171.02 2013002 105 10/4/2013 10/6/2013 25 1 .98 26.98 2013003 100 10/6/2013 10/8/2013 49 3.87 52.87 2013004 10/6/2013 10/8/2013 17.5 11.38 18.88 2013005 10/7/2013 10/11/2013 12 10.95 12.95 2013006 10/11/2013 10/13/2013 152.5 12.05 164.55 2013007 10/11/2013 10/13/2013 7 0.55 7.55 2013008 130 10/12/2013 10/14/2013 140.5 11.1 151.6 2013009 120 10/12/2013 10/14/2013 27 2.13 29.13

Answer 1

CUSTOMER INVOICE CUSTOMERID FIRSTNAME LASTNAME PHONE EMAIL INVOICE NUMBER CUSTOMER ID DATE IN DATE OUT SUBTOTAL TAX AMOUNT INVOICE ITEM SERVICE SERVICE ID INVOICE NUMBER ITEM NUMBER SERVICE ID QUANTITY UNIT PRICE EXTENDED PRICE SERVICE DESCRIPTION UNIT PRICE According to logical based tables • There are four tables having three relations Field Customers from table Invoice is foreign key to CustomerID from Table Customer Field InvoiceNumber from table Invoiceltem is foreign key to InvoiceNumber from Table Invoice Field Service ID from table Invoiceltem is foreign key to Serviceld from Table Service • •

The following query is used to denormalize the table using inner join;

SELECT cust.customerid, cust.firstname, cust.lastname, cust.phone, cust.email, inv.invoicenumber, inv.datein, inv.dateout, inv.subtotal, inv.tax, inv.amount, item.itemnumber, item.serviceid, item.quantity, item.unitprice, item.extendedprice, serv.servicedescription, serv,unitprice FROM Customer as cust INNER JOIN Invoice as inv on cust.customerid = inv.customerid INNER JOIN InvoiceItem as item on item.invoicenumber = inv.invoicenumber INNER JOIN Service as serv on serv.serviceid = item.serviceid 

As you can see the from the previous answer using the relations of Foreign Key and Primary Key on the respective tables the inner joins are denormalized i.e. it forms one table which has the combined records of all the four tables.

Hope this answers your questions and you give a positive feedback :")