DATABASE SYSTEMS USING SQL Given the following relational database schema: FLIGH

Question

DATABASE SYSTEMS USING SQL

Given the following relational database schema:

FLIGHT = ( Flight#, FromCity, ToCity, Date, DepartureTime, ArrivalTime ) // Assume all dates in the table are before the date of today. You may use <, >, !=, or = between any two dates or between any two times.

TICKET = ( Ticket#, F#, PassengerName, Cost ) // F# represents the flight #. Note that both Ticket# and F# are underlined, i.e. both represent the primary key.

PASSENGER = (PassengerName , Telephone#)


Write DDL statements to create the above tables and use appropriate data types for the attributes. The DDL statement must include at least the following constraints:

Every Primary Key;
Every Foreign Key;
For every Foreign Key constraint, the referential integrity constraints are:
ON DELETE SET NULL or SET DEFAULT whatever seems appropriate;
ON UPDATE CASCADE.
FromCity and ToCity can not be NULL;
FromCity and the ToCity are not the same;
DepartureTime < ArrivalTime and assume all flights are within the same time zone;
Cost >=50.

Explanation / Answer

create table FLIGHT( FlightID int primary key, FromCity varchar2(15) not null, ToCity varchar2(15) not ull, Date date, DepartureTime timestamp, ArrivalTime timestamp)   create table TICKET ( TicketNo int, FlightId int, PassengerName varchar2(10), Cost int, primary key(TicketNo,FlightId), foreign key(FlightId) references FLIGHT(FlightID) on update cascade on delete set null); create table PASSENGER (PassengerName varchar2(20) , TelephoneNo int primary key)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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