Please use SQL DDL and DML to provide deliverables that satisfy the requirements.

All the details are provided in the attached file.

 

 

1. Problem Description For this lab, assume we created the following ERD model for the Syracuse Airways database
(see below). As the model shows: the company has many airplanes which are of different specifications
(e.g. Boeing 777, Airbus A320, and so on). Each aircraft specification has the same attributes,
such as aircraftVersion, cabin number of seats and fuel capacity. The airplanes are assigned to
diverse flight routes. Each flight route has a unique flight number, departs and arrives at
particular airports at different cities in scheduled time. Every day the airplanes work
according to their flight routes, but the flight schedule may be affected by flight statuses (on
time, delay, or cancelled). 2. Instruction Imagine you are hired to implement t new database to support this business. Please use SQL
DDL and DML to provide the following deliverables that satisfy the requirements. (1) Create tables:
Create the eight tables following the ERD above. Copy and paste the complete create
statements to your lab report. Make sure you set up correct constraints for the primary keys
and foreign keys. Also the following domain constrains are required in the creating:
States Table
Primary/Foreign Key Attribute Data Type Required PK stateAbbr VARCHAR(2) Y stateName VARCHAR(20) Y Primary/Foreign Key Attribute Data Type Required PK cityKey CHAR(8) Y cityName VARCHAR(25) Y stateAbbr VARCHAR(2) Y Primary/Foreign Key Attribute Data Type Required PK airportID CHAR(3) Y airportName VARCHAR(45) Y cityKey CHAR(8) Y Domain
Constraint City Table FK Domain
Constraint Airport Table FK Domain
Constraint UNIQUE FlightRoute Table
Primary/Foreign
Key Attribute Data Type Required PK flightNumber VARCHAR(6) Y FK departAirport CHAR(3) Y Domain
Constraint FK arriveAirport CHAR(3) Y scheduledDepartTime TIME Y scheduledArrivalTime TIME Y FlightSchedule Table
Primary/Foreign Key Attribute Data Type Required PK, FK flightNumber VARCHAR(6) Y PK flightDate DATE Y FK statusID CHAR(1) Y FK airplaneID CHAR(8) Y delayDepartTime TIME N delayArrivalTime TIME N Domain
Constraint Value=”O”, “D”
or “C” Status Table
Primary/Foreign
Key Attribute Data Type Required Domain
Constraint PK statusID CHAR(1) Y Value=”O”, “D”
or “C” description VARCHAR(20) Y Airplane Table
Primary/Foreign
Key Attribute Data Type Required PK airplaneID CHAR(8) Y FK aircraftTypeID CHAR(8) Y purchaseDate DATE Y AircraftSpecs Table Domain Constraint Default=”01/02/2014” Primary/Foreign
Key Attribute Data Type Required PK aircraftTypeID CHAR(8) Y aircraftVersion VARCHAR(10) Y cabinNumOfSeats INT N fuelCapacity INT Y Domain
Constraint (2) Insert data:
Insert the following data (shown in the screenshots) to the tables. Copy and paste all insert
statements to the lab report. You need to insert exact data as shown, because the same
data will be used in future labs. (3) Select data:
Select all content of each table to prove the success of creating tables and inserting data.
Include the screenshots of the select results in the lab report. The select results should look
like follows:
States table: City table: Airport Table: FlightRoute table: Status table: AircraftSpecs table: Airplane table: FlightSchedule table: Submission instruction
Please submit your lab report (including SQL statements, and the screenshots of SQL statements and
their results) in one PDF. Make sure your screenshots have good resolution. Remember to add
comments to your SQL statements to explain the purpose of the code blocks.

Attachments:

• 1493360065-DBMS.docx