- create a relational schema based on the diagram below in 3NF ONLY - underline
ID: 3731728 • Letter: #
Question
- create a relational schema based on the diagram below in 3NF ONLY - underline the primary key with a solid line - underline the foreign key(s) with a broken line - if there is a foreign key show the relation it references with an arrow- create a relational schema based on the diagram below in 3NF ONLY - underline the primary key with a solid line - underline the foreign key(s) with a broken line - if there is a foreign key show the relation it references with an arrow
TRUCK PASSENGER DINING TRUCK Cab Size Equipment supplies VENDOR RESERVATION VehicleType ReservationType PickupDate Time VEHICLE Time, Day, Month,Year) ReturnDateTime Time, Day, Month, Year)s assigned a VehicleMake OB DETAILS ParticipantsNum VehicleYear VehicleClassification VehicleType DateCompleted Destination) ReservationType RENTAL WORK ORDER WorkOrderiD O4Mileage Vehicle Type CUSTOMER CustomerFirstName BLLING INVOICE FULLTIME MECHANIC Salary is biled CustomerPhoneNumber) CustomerEmail FULLTIME OFFICE(Specialities) Availability PositionTitle CustomerDriversExpiration CustomerType CustomerType EmployeeType EMPLOYEE FACULTY STUDENT EmployeeFirstName SupervisorName Status Grade EmployeeAddr [EmployeePhoneNo) EmployeeType
Explanation / Answer
CREATE TABLE CUSTOMER(
CustomerNetID int NOT NULL PRIMARY KEY,
CustomerFirstName VARCHAR(255),
CustomerLastName VARCHAR(255),
CustomerPhoneNumber CHAR(13),
CustomerEmail VARCHAR(255),
CustomerDriversLicenseNo VARCHAR(20),
CustomerDriversLicenseSt VARCHAR(50),
CustomerDriversExpiration DATE,
CustomerType VARCHAR(20)
);
CREATE TABLE FACULTY(
CustomerNetID int NOT NULL PRIMARY KEY,
SupervisorName VARCHAR(255),
Department VARCHAR(100),
FOREIGN KEY(CustomerNetID) REFERENCES CUSTOMER(CustomerNetID)
);
CREATE TABLE STUDENT(
CustomerNetID int NOT NULL PRIMARY KEY,
StudentID VARCHAR(20),
Grade VARCHAR(20)
FOREIGN KEY(CustomerNetID) REFERENCES CUSTOMER(CustomerNetID)
);
CREATE TABLE MVR(
CustomerNetID int NOT NULL,
ConfirmationNo int NOT NULL,
Purpose VARCHAR(255),
SupervisorNetID int,
`Status` VARCHAR(255),
DateCompleted DATE,
PRIMARY KEY(CustomerNetID, ConfirmationNo),
FOREIGN KEY(CustomerNetID) REFERENCES STUDENT(CustomerNetID)
);
CREATE TABLE VENDOR(
VendorID int NOT NULL PRIMARY KEY,
VenodrName VARCHAR(255),
VendorLocation VARCHAR(100),
);
CREATE TABLE VEHICLE(
VIN int NOT NULL PRIMARY KEY,
VehicleColor VARCHAR(20),
VehicleMake VARCHAR(20),
VehicleModel VARCHAR(20),
VehicleClassification VARCHAR(20),
VehicleType VARCHAR(20),
MaxPassengers int,
FuelType VARCHAR(20),
VendorID int,
FOREIGN KEY(VendorID) REFERENCES VENDOR(VendorID)
);
CREATE TABLE TRUCK(
VIN int NOT NULL PRIMARY KEY,
Cab_Size int;
FOREIGN KEY(VIN) REFERENCES VEHICLE(VIN)
);
CREATE TABLE PASSENGER_VAN(
VIN int NOT NULL PRIMARY KEY,
NumRows int;
FOREIGN KEY(VIN) REFERENCES VEHICLE(VIN)
);
CREATE TABLE DINING_TRUCK(
VIN int NOT NULL PRIMARY KEY,
Equipment VARCHAR(50)
FOREIGN KEY(VIN) REFERENCES VEHICLE(VIN)
);
CREATE TABLE JOB_DETAILS(
VendorInvoiceNo int NOT NULL,
DateCompleted DATE,
`Description` TEXT,
VIN int,
PRIMARY KEY(VIN, VendorInvoiceNo),
FOREIGN KEY(VIN) REFERENCES VEHICLE(VIN)
);
CREATE TABLE RENTAL(
ConfirmationNo int NOT NULL PRIMARY KEY,
LengthRes int,
Mileage REAL,
VehicleType VARCHAR(50),
CustomerNetID int,
FOREIGN KEY(CustomerNetID) REFERENCES CUSTOMER(CustomerNetID)
);
CREATE TABLE BILLING_INVOICE(
InvoiceID int NOT NULL PRIMARY KEY,
AccountNum VARCHAR(10),
AccountPerc REAL,
BillingDate DATE,
ConfirmationNo int,
CustomerNetID int,
FOREIGN KEY(ConfirmationNo) REFERENCES RENTAL(ConfirmationNo),
FOREIGN KEY(CustomerNetID) REFERENCES CUSTOMER(CustomerNetID)
);
CREATE TABLE EMPLOYEE(
EmployeeID int NOT NULL PRIMARY KEY,
EmployeeNetID int,
EmployeeFirstName int,
EmployeeLastName int,
EmployeeAddrStreet VARCHAR(100),
EmployeeAddrCity VARCHAR(100),
EmployeeAddrState VARCHAR(100),
EmployeeAddrZip CHAR(6),
EmployeePhoneNo CHAR(13),
EmployeeStartDate DATE,
EmployeeEndDate DATE,
EmployeeType VARCHAR(20)
);
CREATE TABLE FULLTIME_OFFICE(
EmployeeID int MOT NULL PRIMARY KEY,
Salary REAL,
PostionTitle VARCHAR(50),
FOREGIN KEY(EmployeeID) REFERENCES EMPLOYEE(EmployeeID)
);
CREATE TABLE FULLTIME_MECHANIC(
EmployeeID int MOT NULL PRIMARY KEY,
Salary REAL,
HourlyChargeRate REAL,
Speacialities VARCHAR(255),
FOREGIN KEY(EmployeeID) REFERENCES EMPLOYEE(EmployeeID)
);
CREATE TABLE PARTTIME(
EmployeeID int MOT NULL PRIMARY KEY,
HourlyRate REAL,
Availablility INT,
FOREGIN KEY(EmployeeID) REFERENCES EMPLOYEE(EmployeeID)
);
CREATE TABLE WORK_ORDER(
WorkOrderID int,
DateComp DATE,
`Description` TEXT,
EmployeeID int,
VIN int,
PRIMARY KEY(WorkOrderID, EmployeeID, VIN),
FOREIGN KEY(VIN) REFERENCES VEHICLE(VIN),
FOREIGN KEY(EmployeeID) REFERENCES EMPLOYEE(EmployeeID)
);
CREATE TABLE RESERVATION(
ReservationID INT NOT NULL PRIMARY KEY,
ReservationType VARCHAR(50),
PickupDateTime DATETIME,
ReturnDateTime DATETIME,
VehicleRequest VARCHAR(20),
ParticipantsNum INT,
BusinessPurpose VARCHAR(50),
Destination VARCHAR(50),
Comments VARCHAR(255),
DialyRate REAL,
MileageAllowance REAL,
MonthlyRate REAL
CustomerNetID int,
VIN int,
FOREIGN KEY(CustomerNetID) REFERENCES CUSTOMER(CustomerNetID)
FOREIGN KEY(VIN) REFERENCES VEHICLE(VIN)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.