Question

Reference: Textbook Minicase MC3, pp. 54. This is a variation of this textbook minicase with an additional constraint included in the list below. Please use the problem description below.)

Snooty Fashions is an exclusive custom fashion designer business. Using ERDplus, create the ER diagram for the Snooty Fashion Operations Database based on the following requirements. Insert the ER diagram as an image below the requirements.

The Snooty Fashions Operations Database will keep track of the following:

For each designer: a unique designer identifier and unique SSN as well as the name (composed of first and last name)

For each customer: a unique customer’s identifier as well as his or her name and multiple phone numbers

For each tailoring technician: a unique SSN as well as his or her name (composed of first and last name)

For each outfit: a unique outfit’s identifier as well as the outfit’s planned date of completion and its unreasonable price

For each fashion show: a unique show identifier as well as the date of the show and location

Each designer designs many outfits. Each outfit has only one designer.

Each outfit is sold (in advance) to exactly one customer. Customers can buy one or many outfits (Snooty Fashions will not keep track of customers that have not made any purchases yet).

For each customer, Snooty Fashions will keep track of the number of outfits that each customer buys.

Each tailoring technician must work on at least one outfit but can work on many. Each outfit has at least one tailoring technician working on it but can have many.

Snooty Fashions will keep track of the date when a tailoring technician started working on a particular outfit.

Each designer can participate in a number of fashion shows, but does not have to participate in any. Each fashion show can feature one or two Snooty Fashions designers (Snooty Fashions will not keep track of fashion shows that do not feature Snooty Fashions designers.)

Answer 1

Relational Schema

Customer (CustomerID, Name, HomePhone, OfficePhone)

Technician (SSN, FirstName, LastName)

Designer (DesignerID, SSN, FirstName, LastName)

Outfit (OutfitID, CompletionDate, UnreasonablePrice, DesignerID_FK, CustomerID_FK)

FashionShow (FashionShowID, ShowDate, Location)

OutfitTechnician (OutfitID_FK, TechnicianSSN_FK, DateStarted)

FashionshowDesigner (FashionShowID_FK, DesignerID_FK)

SQL Statements

CREATE TABLE Customer(

CustomerID INT PRIMARY KEY,

Name VARCHAR(40),

HomePhone VARCHAR(15),

OfficePhone VARCHAR(15)

);

CREATE TABLE Technician (

SSN CHAR(11) PRIMARY KEY,

FirstName VARCHAR(20),

LastName VARCHAR(20)

);

CREATE TABLE Designer(

DesignerID INT PRIMARY KEY,

SSN CHAR(11),

FirstName VARCHAR(20),

LastName VARCHAR(20)

);

CREATE TABLE Outfit (

OutfitID INT PRIMARY KEY,

CompletionDate DATE,

UnreasonablePrice NUMERIC(5, 2),

DesignerID INT NOT NULL,

CustomerID INT NOT NULL,

CONSTRAINT fk_OutfitDsgnr FOREIGN KEY(DesignerID)

REFERENCES Designer(DesignerID),

CONSTRAINT fk_OutfitCstmr FOREIGN KEY(CustomerID)

REFERENCES Customer(CustomerID)

);

CREATE TABLE FashionShow (

FashionShowID INT PRIMARY KEY,

ShowDate DATE,

Location VARCHAR(50)

);

CREATE TABLE OutfitTechnician (

OutfitID INT NOT NULL,

TechnicianSSN CHAR(11) NOT NULL,

DateStarted DATE,

CONSTRAINT pk_OutfitTech PRIMARY KEY(OutfitID, TechnicianSSN),

CONSTRAINT fk_OutfitTechOy FOREIGN KEY(OutfitID)

REFERENCES Outfit(OutfitID),

CONSTRAINT fk_OutfitTcn FOREIGN KEY(TechnicianSSN)

REFERENCES Technician(SSN)

);

CREATE TABLE FashionshowDesigner(

FashionShowID INT NOT NULL,

DesignerID INT NOT NULL,

CONSTRAINT pk_FsnDsgnr PRIMARY KEY(FashionShowID, DesignerID),

CONSTRAINT pk_OutfitTech PRIMARY KEY(OutfitID, TechnicianSSN),

CONSTRAINT fk_FsnDgnr FOREIGN KEY(FashionShowID)

REFERENCES FashionShow(FashionShowID),

CONSTRAINT fk_FsnSDgnrDsn FOREIGN KEY(DesignerID)

REFERENCES Designer(DesignerID)

);

Data Insertion

INSERT INTO Customer

VALUES(1, 'John', '6586552255', '9952222285');

INSERT INTO Customer

VALUES(1, 'Greg', '8525542587', '9875879875');

INSERT INTO Technician

VALUES('546-40-3047', 'Waylon', 'Dalton');

INSERT INTO Technician

VALUES('307-13-9416', 'Justine', 'Henderson');

INSERT INTO Designer

VALUES(1, '654-18-5942', 'Abdullah', 'Lang');

INSERT INTO Designer

VALUES(2, '518-54-1723', 'Marcus', 'Cruz');

INSERT INTO Outfit

VALUES(1, '2017-01-26', 50.45, 1, 2);

INSERT INTO Outfit

VALUES(2, '2017-02-18', 30, 2, 1);

INSERT INTO FashionShow

VALUES(1, '2017-05-17', 'New Delhi');

INSERT INTO FashionShow

VALUES(2, '2017-10-20', 'New York');

INSERT INTO OutfitTechnician

VALUES(1, '546-40-3047', '2016-11-20');

INSERT INTO OutfitTechnician

VALUES(2, '307-13-9416', '2016-10-07');

INSERT INTO OutfitTechnician

VALUES(2, '546-40-3047', '2016-11-2');

INSERT INTO FashionshowDesigner

VALUES(1, 2);

INSERT INTO FashionshowDesigner

VALUES(1, 1);

INSERT INTO FashionshowDesigner

VALUES(2, 2);

SQL query with Union statement

SELECT Name FROM Customer

UNION

SELECT FirstName + ' ' + LastName AS Name FROM Designer;

SQL query with Join

SELECT D.FirstName + ' ' + D.LastName AS Designer, OutfitID, CompletionDate

FROM Outfit O JOIN Designer D

ON O.DesignerID = D.DesignerID;

SQL query with Update

UPDATE FashionShow

SET Location = 'Vegas'

WHERE FashionShowID < 3;

SQL Intersection query

SELECT DesignerID

FROM Outfit

INTERSECT

SELECT DesignerID

FROM FashionshowDesigner;

SQL Difference query

SELECT DesignerID

FROM Outfit

MINUS

SELECT DesignerID

FROM FashionshowDesigner;