Consider the following relations in the relational schema for a database that keeps track of business trips of Sales Representatives in a sales office:

salesperson(SSN, lName, startYear, deptNo)

trip(tripID, SSN, fromCity, toCity, departureDate, returnDate)

expense(tripID, accountNumber, amount)

Part 1

Answer the Following 3 Questions. Places Your Answers in a Word Processor

identify the primary key for each table.

Identify the foreign keys in each table.

Given the above foreign keys, list the order in which the tables could be successfully created.

Write your Answers using a Word Processor

Review the Relational Schema and other necessary material in this assignment to create the metadata document for the above relational schema.

Write your Metadata using a Spreadsheet or a Word Processor. Submit the Final Version as a PDF

Open up a text editor such as Visual Studio Code.

Write the SQL SELECT Queries to respond to the following Information Requests

Find the SSN and Name or Sales Representatives that took trips to ‘CHICAGO’.

Find the total trip expenses incurred by the salesman with SSN = ‘123-45-6789’.

Find the tripID, SSN, fromCity, tocity, departureDate, returnDate, and amount for trips that exceed $2000 in expenses.

Save the three SELECT Queries in a SQL Text File with an SQL Extension

Below is the Link to Download the ER Diagram for the Database

TripExpences.pdf download

Below is the Link to Download the Sample Database

Assn10 Comp-Assignment.sql download

Submit the Following Files

A Word Processing File or PDF File Containing the Answers to Part 1

A Spreadsheet File to PDF containing the Metadata for Part 2

A SQL Text File with an SQL Extension Containing the SELECT Queries responding to the information request in Part 3

Transcribed Image Text:SSN Iname SalesPerson startYear deptNo 1-M tripID Trip sn fromCity toCity departureDate 1-M retumDate triplD Expense accountNumber amount
Transcribed Image Text:/* 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, ZName 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 sSalesperson(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');

Question

Consider the following relations in the relational schema for a database that keeps track of business trips of Sales Representatives in a sales office:

salesperson(SSN, lName, startYear, deptNo)

trip(tripID, SSN, fromCity, toCity, departureDate, returnDate)

expense(tripID, accountNumber, amount)

Part 1

Answer the Following 3 Questions. Places Your Answers in a Word Processor

identify the primary key for each table.
Identify the foreign keys in each table.
Given the above foreign keys, list the order in which the tables could be successfully created.

Write your Answers using a Word Processor

Review the Relational Schema and other necessary material in this assignment to create the metadata document for the above relational schema.

Write your Metadata using a Spreadsheet or a Word Processor. Submit the Final Version as a PDF

Open up a text editor such as Visual Studio Code.

Write the SQL SELECT Queries to respond to the following Information Requests

Find the SSN and Name or Sales Representatives that took trips to ‘CHICAGO’.
Find the total trip expenses incurred by the salesman with SSN = ‘123-45-6789’.
Find the tripID, SSN, fromCity, tocity, departureDate, returnDate, and amount for trips that exceed $2000 in expenses.

Save the three SELECT Queries in a SQL Text File with an SQL Extension

Below is the Link to Download the ER Diagram for the Database

TripExpences.pdf download

Below is the Link to Download the Sample Database

Assn10 Comp-Assignment.sql download

Submit the Following Files

A Word Processing File or PDF File Containing the Answers to Part 1
A Spreadsheet File to PDF containing the Metadata for Part 2
A SQL Text File with an SQL Extension Containing the SELECT Queries responding to the information request in Part 3

SSN
Iname
SalesPerson
startYear
deptNo
1-M
tripID
Trip
sn
fromCity
toCity
departureDate
1-M
retumDate
triplD
Expense
accountNumber
amount

/* 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,
ZName 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 sSalesperson(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');

Accepted Answer

Given:
Consider the following relations in the relational schema for a database that keeps track of…