Question

Marcia wants to keep track of each of her customers and their orders. Ultimately, she wants to notify them that their clothes are ready via email. Suppose that you have designed a database for Marcia’s Dry Cleaning that has the following tables:

CUSTOMER (CustomerID, FirstName, LastName, Phone, EmailAddress)

INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax, TotalAmount)

INVOICE_ITEM (InvoiceNumber, ItemNumber, ServiceID, Quantity, UnitPrice, ExtendedPrice)

SERVICE (ServiceID, ServiceDescription, UnitPrice)

The referential integrity constraints are:

CustomerID in INVOICE must exist in CustomerID in CUSTOMER

InvoiceNumber in INVOICE_ITEM must exist in InvoiceNumber in INVOICE

ServiceID in INVOICE_ITEM must exist in ServiceID in SERVICE

Assume that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, ItemID of ITEM, SaleID of SALE, and SaleItemID of SALE_ITEM are all surrogate keys with values as follows: CustomerID Start at 100 Increment by 1

InvoiceNumber Start at 2018001 Increment by 1

D. Suppose that MArcia decides to allow multiple customers per order (e.g. for customers' spouses). Modify the design of these tables to accommodate this change.

E. Code SQL statements necessary to redesign the database, as described in your answer to question D.

F.Suppose that Marcia considers changing the primary key of CUSTOMER to (FirstName, LastName). Write correlated subqueries to display any data that indicate that this change is not justifiable.

Answer 1

D.

We will assume that the name has been changed from ORDER to CUST_ORDER as described above. If this is not done, the following steps will be the same - only the table name will be different. We have to modify the relationship between CUSTOMER and CUST_ORDER.

(1) Create intersection table. There may be foreign key constraints when creating a table or as a separate step.

(2) Copy the values ​​of the primary keys from the tables for rows that have an existing foreign match between the tables.

(3) Release the original foreign key. Constraints and foreign key columns for the parent child table.

If we will:

(1) Create ORDER_CUSTOMER_INT. Since we typically create foreign key constraints when creating a table, we will resolve foreign key constraints between ORDER_CUSTOMER_INT and CUSTOMER and between ORDER_CUSTOMER_INT and CUST_ORDER .

(2) Copy the values ​​of the primary keys from CUST_ORDER to CUSTOMER for rows that have an existing foreign key match from CUST_ORDER to CUSTOMER in ORDER_CUSTOMER_INT.

(3) Drop the original foreign key constraint and foreign key column for CUST_ORDER.

E.

(1) Create an intersection table. There may be significant foreign constraints when creating a table or as a separate step. In the case of defamation,

CREATE TABLE ORDER_CUSTOMER_INT(

InvoiceNumber Int NOT NULL,

CustomerID Int NOT NULL,

CONSTRAINT CustomerOrderIntPK

PRIMARY KEY (InvoiceNumber, CustomerID),

CONSTRAINT COInt_CustOrder_FK FOREIGN KEY(InvoiceNumber)

REFERENCES CUST_ORDER(InvoiceNumber)

ON UPDATE CASCADE

ON DELETE CASCADE,

CONSTRAINT COInt_Customer_FK FOREIGN KEY(CustomerID)

REFERENCES CUSTOMER(CustomerID)

ON UPDATE NO ACTION

ON DELETE NO ACTION

);

2. Copy the values ​​of the primary keys from the rows of the parent child table for rows that have an existing foreign key match from CUST_ORDER to CUSTOMER and the foreign key is not null.

INSERT INTO ORDER_CUSTOMER_INT(InvoiceNumber, CustomerID)

SELECT InvoiceNumber, CustomerID

FROM CUST_ORDER

WHERE CustomerID IS NOT NULL

3.

Skip the foreign key column for the original foreign key constraint and CUST_ORDER

LTER TABLE CUST_ORDER

DROP CONSTRAINT CustOrderCustomerFK;

ALTER TABLE CUST_ORDER

DROP COLUMN Custome

F.

Customer (Customer, FirstName, LastName, Phone, Email) If (FirstName, LastName) is going to be a primary key, the following functional dependencies must exist: (FirstName, LastName) and Phone, (FirstName, LastName) and Email

Who can be associated with this type: (FirstName, LastName) and (Phone, Email)

SELECT C1.CustomerID, C1.FirstName, C1.LastName

FROM CUSTOMER AS C1

WHERE C1.FirstName IN

(SELECT C2.FirstName

FROM CUSTOMER AS C2

WHERE C1.FirstName = C2.FirstName

AND C1.LastName = C2.LastName

AND C1.CustomerID <> C2.CustomerID);