Marcia\'s Dry Cleaning Case Questions Marcia Wilson owns and operates Marcia\'s
ID: 3690284 • Letter: M
Question
Marcia's Dry Cleaning Case Questions Marcia Wilson owns and operates Marcia's Dry Cleaning, which is an upscale dry cleaner in a well to do suburban neighborhood. Marcia makes her business stand out from the competition by providing superior customer service. She 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 (CustomerlD, FirstName, LastName, Phone, Email) INVOICE anvoice Number, CustomerID, Dateln, DateOut, Subtotal, Tax, TotalAmount) INVOICE ITEM Invoice Number, Item Number, ServicelD, Quantity, UnitPrice ExtendedPrice) SERVICE (ServicelD, Service Description, UnitPrice) Assume that all relationships have been defined, as implied by the foreign keys in this table list, and that the appropriate referential integrity constraints are in place. If you want to run these solutions in a DBMS product, first create a version of the MDC database described in the Case Questions in Chapter 10A for Microsoft SQL Server 2014, Chapter 1OB for Oracle Database, and Chapter 10C for MySQL 5.6. Name the database MDC_CH08 D. Suppose that Marcia decides to allow multiple customers per order (e.g. 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 F. Suppose that Marcia considers changing the primary key of CUSTOMER to answer to part D (FirstName, LastName). Write correlated subqueries to display any data that indi- cate that this change is not justifiable. G. Suppose that (FirstName, LastName) can be made the primary key of CUSTOMER. Make appropriate changes to the table design with this new primary key H. Code all SQL statements necessary to implement the changes described in part G.Explanation / Answer
D. For Allowing multiple customers per order we can add a coloum in CUSTOMER Table as Refrences, and alloting the same CustomerID to the multiple persons referred by a single customer.
Design of the table:-
CUSTOMER(CustomerID,FirstName,LastName,Phone,Email,Refrences)
E.
Create Table CUSTOMER
(CustomerID number(5) PRIMARY KEY,
FirstName Varchar2(20),
LastName Varchar2(20),
Phone number(10),
Email Varchar2(20),
Refrences Varchar(20));
F.
We Cannot have more than 1 Primary Key in a Table so even if we write a code to have 2 primary keys in a table it will show some error.
To change from one primary key to other we use alter table Command, so although it is written to write a code to proove that it is not possible, the code will be
First to drop the Existing Primary Key
ALTER TABLE CUSTOMER
Drop Primary Key;
Now to add Primary Key Constraint according to the given Question.
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (FirstName,LastName);
One more Reason that it is not Justified to have primary key as the Name is that the Primary Key should be a Unique Value, and Names of many people can be same as a corelation.
Data Which Prooves it is not Justified to have FirstName and LastName as Primary Key:-
Here as FirstName of 2 Persons is same and as we know Primary key should have a Unique Value that is the reason it is not Justified to have FirstName or LastName as a primary Key.
G.
For Having First Name and Last Name as the Primary Key we have the Following Changes:-
CUSTOMER(CustomerID,FirstName,LastName,Phone,Email,Refrences)
H.
Code for creating a table having First Name and Last Name as Primary Key:-
Create Table CUSTOMER
(CustomerID number(5),
FirstName Varchar2(20) PRIMARY KEY,
LastName Varchar2(20) PRIMARY KEY,
Phone number(10),
Email Varchar2(20),
Refrences Varchar(20));
Code for altering the table to have First Name and Last Name as Primary Key:-
ALTER TABLE CUSTOMER
DROP PRIMARY KEY;
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (FirstName,LastName);