I. Based on the relational schema (3NF) from Project 1, analyze the tables, thei
ID: 3728823 • Letter: I
Question
I. Based on the relational schema (3NF) from Project 1, analyze the tables, their relationships, and the sample data you were given in Project 1. Create the tables shown, using the best data types based on your analysis. Follow each table name with an underscore and your first, middle, and last initials (if you do not have a middle initial, use X). For example, if your name is Alice B Chandler, you would name the Customer table Customer_abc. Also, keep in mind that foreign key relationships require the same data types on both sides (e.g., if you declare CustID as INTEGER in the Customer table, it must be declared as INTEGER in the Rental table, too). You must print a copy of each CREATE TABLE statement. In addition, execute a DESCRIBE statement for each table after it is created and print the output before proceeding to Step II.
II. After creating the tables, insert the sample data from Project 1 into each table, and print the statements used for each insert transaction. After all rows have been inserted, run a SELECT statement on each table to list all contents (all columns and all rows). Print the SELECT statement and output for each table before proceeding to Step III.
III. Execute the transactions below to modify/add to the data entered in the previous step (COMMIT the transactions if not automatically committed). Print the statements and output for each transaction before proceeding to Step IV:
Customer table
Change the phone number of Customer 85 to ‘2145551234’
Add Customer 120 (Amanda Green, no phone number)
Reservation table
Change the checkout date for reservation 1001 to 2/8/2018
Add a reservation:
ResID CheckIn CheckOut CustID AgentID
1011 3/1/2018 3/4/2018 120 14
ResDetail table
Change the RateCode of ResID 1003 to C
Change the RateAmt of ResID 1003 to $89
Add the following details for reservation 1011:
RoomNum RateType Rate
224 W $119
225 W $129
IV.Execute a SELECT statement on each of the tables to list its final contents. Print the statement and output for each SELECT statement.
Res Num Check In Check Out Cust ID Cust FirstName Cust LastName Cust Phone Cust Type Cust Desc Loyalty ID Agent ID AgentFN Agent LasName Agent Type Agent Desc Room Num Room Type Room Desc Rate Type Rate Desc Rate Amt 1001 5/2/18 7/2/18 85 Wesley Tanner (817)555-1193 C Corporate 323 20 Megan Smith FD Front Desk 224 K King Bed C Corporate $120 1001 5/2/18 7/2/18 85 Wesley Tanner (817)555-1193 C Corporate 323 20 Megan Smith FD Front Desk 225 D 2 Double Beds C Corporate $125 1002 1/2/18 3/2/18 100 Breanna Rhodes (214)555-9191 I Individual 129 5 Janice May T Telephone 305 D 2 Double Beds S Standard $149 1003 9/2/18 11/2/18 15 Jeff Minner I Individual 14 John King FD Front Desk 409 D 2 Double Beds W Weekend $99 1004 2/22/2018 2/23/2018 77 Kim Jackson (817)555-4911 C Corporate 210 28 Ray Schulz T Telephone 320 D 2 Double Beds C Corporate $110 1005 2/15/2018 2/18/2018 119 Mary Vaughn (817)555-2334 I Individual 118 20 Megan Smith FD Front Desk 302 K King Bed S Standard $139 1006 2/24/2018 2/26/2018 97 Chris Mancha (469)555-3440 I Individual 153 14 John King FD Front Desk 501 KS King Suite W Weekend $119 1006 2/24/2018 2/26/2018 97 Chris Mancha (469)555-3440 I Individual 153 14 John King FD Front Desk 502 KS King Suite W Weekend $119 1007 2/20/2018 2/25/2018 100 Breanna Rhodes (214)555-9191 I Individual 129 20 Megan Smith FD Front Desk 302 K King Bed S Standard $139 1008 3/23/2018 3/25/2018 85 Wesley Tanner (817)555-1193 C Corporate 323 5 Janice May T Telephone 320 D 2 Double Beds W Weekend $89 1008 3/23/2018 3/25/2018 85 Wesley Tanner (817)555-1193 C Corporate 323 5 Janice May T Telephone 321 K King Bed W Weekend $99 1009 1/3/18 4/3/18 28 Renee Waker (214)555-9285 I Individual 135 14 John King RC Res Center 502 KS King Suite W Weekend $129 1010 1/3/18 3/3/18 23 Shelby Day I Individual 28 Ray Schulz T Telephone 225 D 2 Double Beds W Weekend $129 Cust Type Cust Date aby ID Agant La Nama gant Type Ag Ream Num Ream Type oom Dosc ,2/1a 11,2/18Explanation / Answer
The given complete table when broken into 3 NF will give 9 tables. Below is the create statement of creating all the 9 tables with their primary key and associated foreign keys.
Note- you can replace '_abc' in each table with your name initials.
CREATE TABLE CustType_abc
(
CustType CHAR(6),
CustTypeDesc VARCHAR(255),
PRIMARY KEY (CustType)
);
CREATE TABLE Customer_abc
(
CustID INTEGER,
CustFName VARCHAR(50),
CustLName VARCHAR(50),
CustPhone VARCHAR(10),
CustType CHAR(6),
LoyaltyID INTEGER,
PRIMARY KEY (CustID),
FOREIGN KEY (CustType) REFERENCES CustType_abc(CustType)
);
CREATE TABLE AgentType_abc
(
AgentType CHAR(6),
AgentTypeDesc VARCHAR(255),
PRIMARY KEY (AgentType)
);
CREATE TABLE Agent_abc
(
AgentID INTEGER,
AgentFName VARCHAR(50),
AgentLName VARCHAR(50),
AgentType CHAR(6),
PRIMARY KEY (AgentID),
FOREIGN KEY (AgentType) REFERENCES AgentType_abc(AgentType)
);
CREATE TABLE Reservation_abc
(
ResID INTEGER,
CheckInDate DATE,
CheckOutDate DATE,
CustID INTEGER,
AgentID INTEGER,
PRIMARY KEY (ResID),
FOREIGN KEY (CustID) REFERENCES Customer_abc(CustID),
FOREIGN KEY (AgentID) REFERENCES Agent_abc(AgentID)
);
CREATE TABLE RateType_abc
(
RateType CHAR(6),
RateTypeDesc VARCHAR(255),
PRIMARY KEY (RateType)
);
CREATE TABLE RoomType_abc
(
RoomType CHAR(6),
RoomTypeDesc VARCHAR(255),
PRIMARY KEY (RoomType)
);
CREATE TABLE Room_abc
(
RoomNum INTEGER,
RoomType CHAR(6),
PRIMARY KEY (RoomNum),
FOREIGN KEY (RoomType) REFERENCES RoomType_abc (RoomType)
);
CREATE TABLE ResDetail_abc
(
ResID INTEGER,
RoomNum INTEGER,
RateType CHAR(6),
RateAmt DECIMAL(5,2),
PRIMARY KEY (ResID, RoomNum),
FOREIGN KEY (ResID) REFERENCES Reservation_abc (ResID),
FOREIGN KEY (RoomNum) REFERENCES Room_abc (RoomNum),
FOREIGN KEY (RateType) REFERENCES RateType_abc (RateType)
);
DESCRIBE TABLE CustType_abc;
DESCRIBE TABLE Customer_abc;
DESCRIBE TABLE AgentType_abc;
DESCRIBE TABLE Agent_abc;
DESCRIBE TABLE Reservation_abc;
DESCRIBE TABLE RateType_abc;
DESCRIBE TABLE ResDetail_abc;
DESCRIBE TABLE RoomType_abc;
DESCRIBE TABLE Room_abc;
Part II-
Query to insert record in all the 9 tables based on the given data.
INSERT INTO CustType_abc VALUES ('C', 'Corporate');
INSERT INTO Customer_abc VALUES (85, 'Wesley', 'Tanner', '8175551193', 'C', 323);
INSERT INTO AgentType_abc VALUES ('FD', 'Front Desk');
INSERT INTO Agent_abc VALUES (20, 'Megan', 'Smith', 'FD');
INSERT INTO Reservation_abc VALUES (11, '5/2/18', '7/2/18', 85, 20);
INSERT INTO RateType_abc VALUES ('C', 'Corporate');
INSERT INTO RoomType_abc VALUES ('K', 'King Bed');
INSERT INTO Room_abc VALUES (224, 'K');
INSERT INTO ResDetail_abc VALUES (11, 224, 'C', 120.00);
Selecting all the records from the created 9 tables-
SELECT * FROM CustType_abc;
SELECT * FROM Customer_abc;
SELECT * FROM AgentType_abc;
SELECT * FROM Agent_abc;
SELECT * FROM Reservation_abc;
SELECT * FROM RateType_abc;
SELECT * FROM RoomType_abc;
SELECT * FROM Room_abc;
SELECT * FROM ResDetail_abc;
Part III-
/ Updating Customer table
UPDATE Customer
SET CustPhone = ‘214551234’
WHERE CustID = 85;
// Inserting values to Customer table
INSERT INTO Customer (CustID, CustFName, CustLName) VALUES (120, ‘Amanda’, ‘Green’)
// Updating values reservation table
UPDATE TABLE Reservation
SET CheckOutDate = ‘2/8/2018’
WHERE ResID = 1001;
// Adding a row in Reservation table
INSERT INTO Reservation VALUES (1011, ‘3/1/2018’, ‘3/4/3018’, 120, 14);
// Updating ResDetail table
UPDATE ResDetail
SET RateType = ‘C’,
RateAmt = 89
WHERE ResID = 1003;
// Updating ResDetail table for reservation 1011, the detail is updated twice so only last detail // will persist.
UPDATE ResDetail
SET RoomNum = 224,
RateType = ‘W’,
Rate = 119
WHERE ResID = 1011;
UPDATE ResDetail
SET RoomNum = 225,
RateType = ‘W’,
Rate = 129
WHERE ResID = 1011;
// Commiting the all the transactions.
COMMIT;