Question

Task 1.

For each table on the list, identify the functional dependencies. List the functional dependencies. Normalize the relations to BCNF. Then decide whether the resulting tables should be implemented in that form. If not, explain why. For each table, write the table name and write out the names, data types, and sizes of all the data items, Identify any constraints, using the conventions of the DBMS you will use for implementation. Write and execute SQL statements to create all the tables needed to implement the design.

Create indexes for foreign keys and any other columns that will be used most often for queries. Insert about five records in each table, preserving all constraints. Put in enough data to demonstrate how the database will function. Write SQL statements that will process five non-routine requests for information from the database just created. For each, write the request in English, followed by the corresponding SQL command. Create at least one trigger and write the code for it.

Tables / DDL and Insert Data have been provided below:

-- DDL to create the MS SQL tables for initial relational model for Theater Group
CREATE DATABASE Theater;

CREATE TABLE Member(
    memId       INT,
    dateJoined   DATETIME,
    firstname    VARCHAR(15),
    lastName    VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber CHAR(7),
    currentOfficeHeld VARCHAR(20),
    CONSTRAINT Member_memId_pk PRIMARY KEY(memid));

CREATE TABLE Sponsor(
    sponID       INT,
    name       VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber CHAR(7),
    CONSTRAINT Sponsor_sponId_pk PRIMARY KEY(sponID));

CREATE TABLE Subscriber(
    subID       INT,
    firstname    VARCHAR(15),
    lastName    VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber   CHAR(7),
    CONSTRAINT Subscriber_subId_pk PRIMARY KEY(subID));

CREATE TABLE Play(
    title            VARCHAR(100),
    author            VARCHAR(35),
    numberOfActs   SMALLINT,
    setChanges        SMALLINT,
    CONSTRAINT Play_title_pk PRIMARY KEY(title));

CREATE TABLE Production(
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    seasonEndDate   VARCHAR(7),
    title            VARCHAR(100),
    CONSTRAINT Prod_year_seasStDate_pk primary key(year, seasonStartDate),
    CONSTRAINT Prod_title_fk FOREIGN KEY(title) REFERENCES Play(title));

CREATE TABLE Performance(
    datePerf        VARCHAR(7),
    timePerf        VARCHAR(10),
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    CONSTRAINT Performance_date_pk PRIMARY KEY(datePerf,year),
    CONSTRAINT Performance_yr_seasStart_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year, seasonStartDate));

CREATE TABLE TicketSale(
    saleID       INT,
    saleDate   DATETIME,
    totalAmount   DECIMAL(6,2),
    perfDate   VARCHAR(7),
    perfYear   SMALLINT,
    subId       INT,
    CONSTRAINT TicketSale_ID_PK PRIMARY KEY(saleId),
    CONSTRAINT TicketSale_perfDate_fk FOREIGN KEY(perfDate,perfYear) REFERENCES Performance(datePerf,year),
    CONSTRAINT TicketSale_subId_fk FOREIGN KEY(subId) REFERENCES Subscriber(subId));

CREATE TABLE DuesPayment(
    memId        INT,
    duesYear   SMALLINT,
    amount       DECIMAL(5,2),
    datePaid   DATETIME,
    CONSTRAINT DuesPayment_memId_year_pk PRIMARY KEY(memid, duesyear),
    CONSTRAINT DuesPayment_memId_fk FOREIGN KEY(memid) REFERENCES Member(memid));

CREATE TABLE Donation(
    sponId            INT,
    donationDate   DATETIME,
    donationType   VARCHAR(20),
    donationValue   DECIMAL(8,2),
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    CONSTRAINT Donation_sponId_date_pk PRIMARY KEY(sponId, donationDate),
    CONSTRAINT Donation_sponId_fk FOREIGN KEY(sponId) REFERENCES Sponsor(sponId),
    CONSTRAINT Donation_year_seasStartDate_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year, seasonStartDate));

CREATE TABLE Ticket(
    saleId            INT,
    seatLocation   VARCHAR(3),
    price            DECIMAL(5,2),
    seattype        VARCHAR(15),
    CONSTRAINT Ticket_saleid_pk PRIMARY KEY(saleId, seatLocation),
    CONSTRAINT Ticket_saleid_fk FOREIGN KEY(saleid) REFERENCES TicketSale(saleId));

CREATE TABLE Member_Production(
    memId            INT,
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    role            VARCHAR(25),
    task            VARCHAR(25),
    CONSTRAINT Mem_Prod_Id_year_seas_pk PRIMARY KEY(memId, year, seasonStartDate),
    CONSTRAINT Mem_Prod_memId_FK FOREIGN KEY (memid) REFERENCES Member(memId),
    CONSTRAINT Mem_Prod_yr_seasStartDate_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year,seasonStartDate));

INSERT DATA:

-- insert some records

INSERT INTO Member values(11111,'01-Feb-2015', 'Frances','Hughes','10 Hudson Avenue','New Rochelle','NY','10801','914','3216789','President');
INSERT INTO Member values(22222,'01-Mar-2015', 'Irene','Jacobs','1 Windswept Place','New York','NY','10101','212','3216789','Vice-President');
INSERT INTO Member values(33333,'01-May-2015', 'Winston', 'Lee','22 Amazon Street','New York','NY', '10101','212','3336789',null);
INSERT INTO Member values(44444,'01-Feb-2015', 'Ryan','Hughes','10 Hudson Avenue','New Rochelle','NY','10801','914','5556789','Secretary');
INSERT INTO Member values(55555,'01-Feb-2015', 'Samantha', 'Babson','22 Hudson Avenue','New Rochelle','NY','10801','914','6666789','Treasurer');
INSERT INTO Member values(66666,'01-Feb-2015', 'Robert', 'Babson','22 Hudson Avenue','New Rochelle','NY','10801','914','6666789',null);

INSERT INTO Sponsor values(1234, 'Zap Electrics', '125 Main Street','New York','NY', '10101', '212','3334444');
INSERT INTO Sponsor values(1235, 'Elegant Interiors', '333 Main Street','New York','NY', '10101', '212','3334446');
INSERT INTO Sponsor values(1236, 'Deli Delights', '111 South Street', 'New Rochelle','NY','10801', '914','2224446');

INSERT INTO Subscriber values(123456, 'John','Smith','10 Sapphire Row', 'New Rochelle','NY','10801', '914','1234567');
INSERT INTO Subscriber values(987654, 'Terrence','DeSimone','10 Emerald Lane','New York','NY', '10101','914','7676767');

INSERT INTO Play values('Macbeth','Wm. Shakespeare', 3,6);
INSERT INTO Play values('Our Town','T. Wilder', 3,4);
INSERT INTO Play values('Death of a Salesman','A. Miller', 3,5);

INSERT INTO Production values(2015,'05-May', '14-May', 'Our Town');
INSERT INTO Production values(2014,'14-Oct','23-Oct','Macbeth');

INSERT INTO Performance values('05-May','8pm',2015,'05-May');
INSERT INTO Performance values('06-May','8pm',2015,'05-May');
INSERT INTO Performance values('07-May','3pm',2015,'05-May');
INSERT INTO Performance values('12-May','8pm',2015,'05-May');
INSERT INTO Performance values('13-May','8pm',2015,'05-May');
INSERT INTO Performance values('14-May','3pm',2015,'05-May');
INSERT INTO Performance values('14-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('15-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('16-Oct','3pm',2014,'14-Oct');
INSERT INTO Performance values('21-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('22-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('23-Oct','3pm',2014,'14-Oct');

INSERT INTO TicketSale values(123456,'01-May-2015',40.00,'05-May',2015,123456);
INSERT INTO Ticket values(123456, 'A1',20.00,'orch front');
INSERT INTO Ticket values(123456, 'A2',20.00,'orch front');

INSERT INTO TicketSale values(123457,'02-May-2015',80.00,'05-May',2015,987654);
INSERT INTO Ticket values(123457, 'A3',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A4',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A5',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A6',20.00,'orch front');

INSERT INTO TicketSale values(000001,'01-Oct-2014',40.00,'14-Oct',2014, 987654);
INSERT INTO Ticket values(000001, 'A1',20.00,'orch front');
INSERT INTO Ticket values(000001, 'A2',20.00,'orch front');

INSERT INTO TicketSale values(000002,'9-Oct-2014',60.00,'14-Oct',2014,123456);
INSERT INTO Ticket values(000002, 'A1',20.00,'orch front');
INSERT INTO Ticket values(000002, 'A2',20.00,'orch front');
INSERT INTO Ticket values(000002, 'A3',20.00,'orch front');

INSERT INTO DuesPayment values(11111, 2015, 50.00, '01-Jan-2015');
INSERT INTO DuesPayment values(22222, 2015, 50.00, '15-Jan-2015');
INSERT INTO DuesPayment values(33333, 2015, 50.00, '01-Feb-2015');
INSERT INTO DuesPayment values(44444, 2015, 50.00, '30-Jan-2015');
INSERT INTO DuesPayment values(55555, 2015, 50.00, '28-Jan-2015');

INSERT INTO Donation values(1234, '01-Mar-2015','sound board',1250.00,2015,'05-May');
INSERT INTO Donation values(1235, '15-Apr-2015','cash', 500.00,2015,'05-May');
INSERT INTO Donation values(1236, '05-May-2015','food',500.00,2015,'05-May');
INSERT INTO Donation values(1236, '06-May-2015','beverges',200.00,2015,'05-May');
INSERT INTO Donation values(1236, '07-May-2015','snacks',100.00,2015,'05-May');

INSERT INTO Member_Production values(11111,2015,'05-May','Emily','sets');
INSERT INTO Member_Production values(22222,2015,'05-May','Mrs. Webb','costumes');

-- DDL to delete all of the tables, use only if you need to rebuild the DB
DROP TABLE Member_Production;
DROP TABLE Ticket;
DROP TABLE Donation;
DROP TABLE DuesPayment;
DROP TABLE TicketSale;
DROP TABLE Performance;
DROP TABLE Production;
DROP TABLE Play;
DROP TABLE Subscriber;
DROP TABLE Sponsor;
DROP TABLE Member;
DROP DATABASE Theater;

Answer 1

#1	Tables	Current Form	Dependency Details	Action
1	Member_Production	BCNF	NULL	NONE
2	Ticket	3NF	SaleID and Seat Location are candidate keys and neither of them alone are keys.	The table has to be split in 3 different tables
3	Donation	1NF	Sponsor ID is dependent on Donation date	The table has to be split into two with a new column Donation ID.
4	DuesPayment	BCNF	NULL	NONE
5	TicketSale	BCNF	NULL	NONE
6	Performance	BCNF	NULL	NONE
7	Production	BCNF	NULL	NONE
8	Play	BCNF	NULL	NONE
9	Subscriber	2NF	Transitive functional dependency exists. Street,City ,State is dependent on Zip and Zip is dependent on SubID	The table has to be split into 2 with address details with zip as seperate table
10	Sponsor	2NF	Transitive functional dependency exists. Street,City ,State is dependent on Zip and Zip is dependent on SponID	The table has to be split into 2 with address details with zip as seperate table
11	Member	2NF	Transitive functional dependency exists. Street,City ,State is dependent on Zip and Zip is dependent on MemID	The table has to be split into 2 with address details with zip as seperate table

#2:TICKET

Since we already have a table for SaleID as primary key,we can pick that up and can create two new tables as listed below.

Ticket_Seats:

Create Table Ticket_Seats
(
seatLocation VARCHAR(3),
price DECIMAL(5,2),
seattype VARCHAR(15),
CONSTRAINT Ticket_Seats_pk PRIMARY KEY(seatLocation)
  
);

INSERT INTO Ticket_Seats values('A1',20.00,'orch front');
INSERT INTO Ticket_Seats values('A2',20.00,'orch front');
INSERT INTO Ticket_Seats values('A3',20.00,'orch front');
INSERT INTO Ticket_Seats values('A4',20.00,'orch front');
INSERT INTO Ticket_Seats values('A5',20.00,'orch front');
INSERT INTO Ticket_Seats values('A6',20.00,'orch front');

Create Table Ticket_Sales_Seats
(
saleId INT,
seatLocation VARCHAR(3),
CONSTRAINT Ticket_sale_seat_pk PRIMARY KEY(saleId, seatLocation),
CONSTRAINT Ticket_sale_seat_fk FOREIGN KEY(saleid) REFERENCES TicketSale(saleId));

INSERT INTO Ticket_Sales_Seats values(123456, 'A1');
INSERT INTO Ticket_Sales_Seats values(123456, 'A2');

INSERT INTO Ticket_Sales_Seats values(123457, 'A3');
INSERT INTO Ticket_Sales_Seats values(123457, 'A4');
INSERT INTO Ticket_Sales_Seats values(123457, 'A5');
INSERT INTO Ticket_Sales_Seats values(123457, 'A6');

INSERT INTO Ticket_Sales_Seats values(000001, 'A1');
INSERT INTO Ticket_Sales_Seats values(000001, 'A2');

INSERT INTO Ticket_Sales_Seats values(000002, 'A1');
INSERT INTO Ticket_Sales_Seats values(000002, 'A2');
INSERT INTO Ticket_Sales_Seats values(000002, 'A3');

#3:

Donation_Details:

Create Table Donation_Details
(
Donation_id INT,
donationDate DATE,
donationType VARCHAR(20),
donationValue DECIMAL(8,2),
year SMALLINT,
seasonStartDate VARCHAR(7),
CONSTRAINT Donation_det_year_seasStartDate_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year, seasonStartDate));

INSERT INTO Donation_Details values(1001, '01-Mar-2015','sound board',1250.00,2015,'05-May');
INSERT INTO Donation_Details values(1002, '15-Apr-2015','cash', 500.00,2015,'05-May');
INSERT INTO Donation_Details values(1003, '05-May-2015','food',500.00,2015,'05-May');
INSERT INTO Donation_Details values(1004, '06-May-2015','beverges',200.00,2015,'05-May');
INSERT INTO Donation_Details values(1005, '07-May-2015','snacks',100.00,2015,'05-May');
  

Sponsor_Donations:

CREATE TABLE Sponsor_Donations(
Donation_Id INT,
sponId INT,
CONSTRAINT Don_sponId_fk FOREIGN KEY(sponId) REFERENCES Sponsor(sponId)
);

INSERT INTO Sponsor_Donations values(1002,1235) ;
INSERT INTO Sponsor_Donations values(1003,1236)
INSERT INTO Sponsor_Donations values(1004,1236);
INSERT INTO Sponsor_Donations values(1005,1236);

#9 ,#10, #11 :

Zip_details table can be used for all 3 table normalization.

CREATE TABLE zip_details(
  
street VARCHAR(50),
city VARCHAR(15),
state CHAR(2),
zip CHAR(5),
areaCode CHAR(3)
);

INSERT INTO zip_details values('10 Hudson Avenue','New Rochelle','NY','10801','914');
INSERT INTO zip_details values('1 Windswept Place','New York','NY','10101','212');

#9 Subscriber_Details:

CREATE TABLE Subscriber(
subID INT,
firstname VARCHAR(15),
lastName VARCHAR(20),

zip CHAR(5),
phoneNumber CHAR(7),
CONSTRAINT Subscriber_subId_pk PRIMARY KEY(subID));

INSERT INTO Subscriber_details values(123456, 'John','Smith','10101','1234567');
INSERT INTO Subscriber_details values(987654, 'Terrence','DeSimone','10801','7676767');

#10 Sponsor_Details:

CREATE TABLE Sponsor_details(
sponID INT,
name VARCHAR(20),
zip CHAR(5),
phoneNumber CHAR(7),
CONSTRAINT Sponsor_det_sponId_pk PRIMARY KEY(sponID));

INSERT INTO Sponsor_details values(1234, 'Zap Electrics', '10101', '3334444');
INSERT INTO Sponsor_details values(1235, 'Elegant Interiors', '10101', '3334446');
INSERT INTO Sponsor_details values(1236, 'Deli Delights', '10801','2224446');

#11:Member_details

CREATE TABLE Member_details(
memId INT,
dateJoined DATE,
firstname VARCHAR(15),
lastName VARCHAR(20),
zip CHAR(5),
phoneNumber CHAR(7),
currentOfficeHeld VARCHAR(20),
CONSTRAINT Member_det_memId_pk PRIMARY KEY(memid));
  

INSERT INTO Member_details values(11111,'01-Feb-2015', 'Frances','Hughes','10801','3216789','President');
INSERT INTO Member_details values(22222,'01-Mar-2015', 'Irene','Jacobs','10101','3216789','Vice-President');
INSERT INTO Member_details values(33333,'01-May-2015', 'Winston', 'Lee','10101','3336789',null);
INSERT INTO Member_details values(44444,'01-Feb-2015', 'Ryan','Hughes','10801','5556789','Secretary');
INSERT INTO Member_details values(55555,'01-Feb-2015', 'Samantha', 'Babson','10801','6666789','Treasurer');
INSERT INTO Member_details values(66666,'01-Feb-2015', 'Robert', 'Babson','10801','6666789',null);