Please answer 1-7 /* 1.) Primary Keys for each table: Salesperson PK- ssn, Trip

Question

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

Explanation / Answer

1.Primary Key

a)SSN column in Salesperson Table

b)tripID column in Trip table

c)combination of tripID and accountNumber in Expense Table

As these are unique set of clumns ,they are primary keys

2.Foreign Keys:

a)There is no foreign key in SalesPerson table as there are no common column in other 2 tables

b)SSN in Trip table is the foreign key_comp

c)No foreign key in Expense table as there are no 2 common tables.

3.SALESPERSON table ,next Trip table and Expense Table as there are tripIds

as common column non unique .

4. create table salesperson(

SSN int NOT NULL UNIQUE,

IName varchar(20),

startYear varchar(20),

deptNo varchar(20)

)

Like the above statements we have to create table ,first column

SSN should be NOT NULL and UNIQUE

5.select a.tripID,a.SSN,a.fromCity,a.toCity,a.departureDate,a.returnDate

b.amount

from Trip a,Expense back

where a.tripID=b.tripID and b.amount >2000

6. select a.SSN from Trip a,Salesperson b

where a.SSN =b.SSN and b.toCity='CHICAGO'

7.select sum(c.amount) from Salesperson a,

Trip b,

Expense c where a.SSN=b.SSN and

b.tripID=c.tripID and

a.SSN='123-45-6789'

group by c.amount

Explanation:

Select the columns from different tables and join them with their common columns and conditions.Thats how

the above statements are created.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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