Please answer 1-7

/* 1.) Primary Keys for each table: Salesperson PK- ssn, Trip PK- tripId, Expense PK - tripId and accountNumber */ /* 2.)Foreign key for each table: Salesperson FK - deptNo (when Department table created) Trip FK - ssn (from Salesperson) Expense FK - tripID (Used to make Composite Primary Key) */ /* 3.) To create as is Salesperson, Trip, and than Expense (When Department table is add that will need to be first. */

drop database Flight; create database Flight; use Flight;

drop table if exists Salesperson; drop table if exists Trip; drop table if exists Expense;

create table Salesperson ( ssn char(9) not null, lName varchar(25), startYear char(4), deptNo char(3), constraint pkSalesperson primary key (ssn)) engine = innodb;

create table Trip ( tripId char(3) not null, ssn char(9) not null, fromCity varchar(10), toCity varchar(10), departureDate varchar(10), returnDate varchar(10), constraint pkTrip primary key (tripId), constraint fkSalesperson foreign key (ssn) references Salesperson(ssn)) engine = innodb;

create table Expense ( tripId char(3) not null, accountNumber char(5) not null, amount varchar (5), constraint pkTripIdAcctNum primary key (accountNumber, tripID), constraint fkTrip foreign key (tripId) references Trip(tripId)) engine = innodb; insert into Salesperson values ( '123456789', 'PETERSON', '1995', '101'); insert into Salesperson values ( '987654321', 'ALBERTS', '1989', '135');

insert into Trip values ( '100', '123456789', 'PHIL', 'CHICAGO', '11-01-2004', '11-14-2004'); insert into Trip values ( '101', '987654321', 'PHIL', 'CHICAGO', '10-01-2004', '10-10-2004'); insert into Trip values ( '102', '123456789', 'PHIL', 'L.A.', '11-16-2004', '11-19-2004'); insert into Trip values ( '103', '987654321', 'PHIL', 'MIAMI', '10-12-2004', '10-15-2004');

insert into Expense values ( '100', '25431', '500'); insert into Expense values ( '100', '67890', '350'); insert into Expense values ( '101', '25431', '780'); insert into Expense values ( '101', '85321', '525'); insert into Expense values ( '101', '67890', '450'); insert into Expense values ( '101', '54321', '250'); insert into Expense values ( '102', '25431', '850'); insert into Expense values ( '102', '67890', '450'); insert into Expense values ( '102', '85321', '300'); insert into Expense values ( '103', '25431', '350'); insert into Expense values ( '103', '67890', '650');

Consider the following relations for a database that keeps track of business trips of Sales Representatives in a sales officee a. salesperson(SSN, IName, startYear, deptNo) b. trip(tripID, SSN, fromCity, toCity, departureDate, returnDate) c. expense(triplD, accountNumber, amount) 1. Identify the primary key for each table 2. Identify the foreign keys in each table 3. Given the above foreign keys, list the order in which the tables could be successfully created 4. Write the DDL statements for the tables (in the correct order). Include constraints 5. Find the triplD, SSN, fromCity, tocity, departureDate, returnDate and amount for trips that exceed $2000 in expenses 6. Find the SSN for Sales Representatives that took trips to 'CHICAGO 7. Find the total trip expenses incurred by the salesman with SSN 123-45-6789 Salesperson SN 123456789 PETERSON 987654321 ALBERTS Name startYear eptNo 1995 1989 101 135 rl ID SSN omCi toCit departureDate returnDate 100 101 102 103 123456789 PHIL 987654321 PHIL 123456789 PHIL 987654321 PHIL HICAGO HICAGO A. 11/14/2004 10/10/2004 11/19/2004 10/15/2004 11/1/2004 10/1/2004 11/16/20 10/12/20 MIAMI se ID accountNumber amount 100 100 101 101 101 101 102 102 102 10 103 25431 67890 25431 85321 67890 54321 25431 67890 85321 25431 67890 500 350 780 525 450 250 850 450 300 350 650