--
-- CS2550 - Principles of Database Systems (Spring 2006)
-- Department of Computer Science, Univ. of Pittsburgh
--
--     Alexandros Labrinidis (labrinid@cs.pitt.edu)
--
---------------------------------------------------------


drop table seats;
drop table pricing;
drop table counters;
drop table reservation_legs;
drop table reservations;
drop table customers;
drop table flights;


-- --------------------------------------------------------------------------
--   Flight Schedule Information  -------------------------------------------
-- --------------------------------------------------------------------------
create table flights (
	flight_number	  int 	  not null,
	max_seats	      int,
	departure_city    char(3) not null,
	arrival_city 	  char(3) not null,
	departure_time	  int     not null,
	arrival_time      int     not null,
	fly_on_sun	      boolean,  -- 1 ==> flies on sunday,    0 otherise
	fly_on_mon	      boolean,  -- 1 ==> flies on monday,    0 otherise
	fly_on_tue	      boolean,  -- 1 ==> flies on tueday,    0 otherise
	fly_on_wed	      boolean,  -- 1 ==> flies on wednesday, 0 otherise
	fly_on_thu	      boolean,  -- 1 ==> flies on thurday,   0 otherise
	fly_on_fri	      boolean,  -- 1 ==> flies on friday,    0 otherise
	fly_on_sat	      boolean,  -- 1 ==> flies on sarturday, 0 otherise
    --
	-- integrity constraints --------------------------	
    --
	primary key (flight_number),
	check (max_seats > 0)
);

--
-- NOTE: We break "weekly_schedule" into 7 boolean flags, to make 
--       each field atomic, thus satisfying the first normal form.
--



-- --------------------------------------------------------------------------
--   Flight Pricing Information  --------------------------------------------
-- --------------------------------------------------------------------------
create table pricing (
	departure_city    char(3) not null,
	arrival_city 	  char(3) not null,
	high_price	      int     not null,     
	low_price	      int     not null,     
    --
	-- integrity constraints --------------------------
    --
	primary key (departure_city, arrival_city)
);

--
-- NOTE: for simplicity, we assume that dollar amounts are integers
--



-- --------------------------------------------------------------------------
--   Customer Information  --------------------------------------------------
-- --------------------------------------------------------------------------
create table customers (
	pitt_rewards	int 	 not null,
	first_name	    char(20) not null,
	last_name       char(25) not null,
	phone_number	char(12) not null,
	email_address	char(40) not null,
    --
	-- integrity constraints --------------------------
    --
	primary key (pitt_rewards)
);



-- --------------------------------------------------------------------------
--   Reservation information  -----------------------------------------------
-- --------------------------------------------------------------------------
create table reservations (
	reservation_num	int	     not null,
	pitt_rewards	int	     not null,
	date_placed	    date     not null,
	total_cost	    int	     not null,
    --
	-- integrity constraints --------------------------
    --
	primary key(reservation_num),
	foreign key (pitt_rewards) references customers
);


create table reservation_legs (
	reservation_num	int	not null,
	leg_number	    int	not null,  
	-- this is needed to make sure we do not mixup the order of the legs
	--
	flight_number	int 	not null,
	date_flying	    date 	not null,
    --
	-- integrity constraints --------------------------
    --
	primary key (reservation_num, leg_number),
	foreign key (reservation_num) references reservations,
	foreign key (flight_number) references flights
);



-- --------------------------------------------------------------------------
--   Seat information  ------------------------------------------------------
-- --------------------------------------------------------------------------
create table seats (
	flight_number	 int 	not null,
	date_flying	     date 	not null,
	available_seats	 int	not null,
    --
	-- integrity constraints --------------------------
    --
	primary key (flight_number, date_flying),
	foreign key (flight_number) references flights,
	check (available_seats >= 0)
	-- i.e. we do not allow overbooking
);



-- --------------------------------------------------------------------------
--   Counters  --------------------------------------------------------------
-- --------------------------------------------------------------------------
create table counters (
	pitt_rewards	int	not null,
	reservation_num int	not null
);

insert into counters values (1000, 60000);

--
-- NOTE: use this relation to store last number given for pitt_rewards
--       number or as a reservation number. Use relation to assign new
--       unique numbers and increment counters atomically.
--



-- --------------------------------------------------------------------------
commit;