CustType Reom RoomNum RoomNum Roomtype RateType Agentio Agentype RoomType RateTy

Question

CustType Reom RoomNum RoomNum Roomtype RateType Agentio Agentype RoomType RateType RateType RoomType AgentType AgentType 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

Explanation / Answer

CREATE TABLE CustType(

CustType VARCHAR(10) NOT NULL PRIMARY KEY,

CustTypeDesc VARCHAR(255)

);

DESCRIBE CustType;

CREATE TABLE Customer(

CustID INTEGER NOT NULL PRIMARY KEY,

CustFName VARCHAR(100),

CustLName VARCHAR(100),

CustPhone CHAR(13),

CustType VARCHAR(10),

LoyaltyID VARCHAR(20),

FOREIGN KEY(CustType) REFERENCES CustType(CustType)

);

DESCRIBE Customer;

CREATE TABLE AgentType(

AgentType VARCHAR(10) NOT NULL PRIMARY KEY,

AgentTypeDesc VARCHAR(255)

);

DESCRIBE AgentType;

CREATE TABLE Agent(

AgentID int NOT NULL PRIMARY KEY,

AgentFName VARCHAR(50),

AgentLName VARCHAR(50),

AgentType VARCHAR(10),

FOREIGN KEY(AgentType) REFERENCES AgentType(AgentType)

);

DESCRIBE Agent;

CREATE TABLE Reservation(

ResID INTEGER NOT NULL PRIMARY KEY,

CheckInDate DATE,

CheckOutDate DATE,

CustID INTEGER,

AgentID INTEGER,

FOREIGN KEY(CustID) REFERENCES Customer(CustID),

FOREIGN KEY(AgentID) REFERENCES Agent(AgentID)

);

DESCRIBE Reservation;

CREATE TABLE RateType(

RateType VARCHAR(10) NOT NULL PRIMARY KEY,

RateTypeDesc VARCHAR(255),

);

DESCRIBE RateType;

CREATE TABLE RoomType(

RoomType VARCHAR(10) NOT NULL PRIMARY KEY,

RoomTypeDesc VARCHAR(255),

);

DESCRIBE RoomType;

CREATE TABLE Room(

RoomNum INTEGER NOT NULL PRIMARY KEY,

RoomType VARCHAR(10),

FOREIGN KEY(RoomType) REFERENCES RoomType(RoomType)

);

DESCRIBE Room;

CREATE TABLE ResDetail(

ResID int NOT NULL,

RoomNum int NOT NULL,

RateType VARCHAR(10),

RateAmt REAL,

PRIMARY KEY(ResID, RoomNum),

FOREIGN KEY(ResID) REFERENCES Reservation(ResID),

FOREIGN KEY(RoomNum) REFERENCES Room(RoomNum),

FOREIGN KEY(RateType) REFERENCES RateType(RateType)

);

DESCRIBE ResDetail;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.