Database Design and SQL The following relations keep track of airline flight information: Flights (flno: integer,...

Database Design and SQL

The following relations keep track of airline flight information:

Flights (flno: integer, from : string, to: string, distance: integer, departs: time, arrive: time, price: integer)
Aircraft (aid: integer, aname : string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename : string, salary: integer)

The Employees relation describe pilots and other kinds of employees as well. Every pilot is certified for some aircraft and only pilots are certified to fly.

Based on the schemas, formulate the following SQL queries:

a. A customer wants to travel from Madison to New York with no more than two changes of flight. List the choice of departure times from Madison if the customer wants to arrive in New York by 6 p.m.

b. Compute the difference between the average salary of a pilot and the average salary of all employees (including pilots).

c. Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.

Expert Solution

Flights (flno: integer, from : string, to: string, distance: integer, departs: time, arrive: time, price: integer)
Aircraft (aid: integer, aname : string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename : string, salary: integer)
The Employees relation describe pilots and other kinds of employees as well.
Every pilot is certified for some aircraft and only pilots are certified to fly.
Based on the schemas, formulate the following SQL queries:

a. A customer wants to travel from Madison to New York with no more than two changes of flight.
List the choice of departure times from Madison if the customer wants to arrive in New York by 6 p.m.
SELECT flno, from, departs
FROM Flights
WHERE CURRTIME(arrive) == "0600"

UNION

SELECT F1.flno, F1.from, f1.departs
FROM Flights F1, Flights F2
WHERE F1.from = "Madison"
AND F2.to = "New York"
AND F1.arrive < F2.depart
AND CURRTIME(F2.arrive) == "0600"

b. Compute the difference between the average salary of a pilot and the average salary of all employees (including pilots).
SELECT avg(salary) FROM Employee WHERE eid IN
(SELECT eid FROM Certified)
-
SELECT avg(salary) FROM Employee

c. Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.
SELECT ename, salary FROM Employee WHERE
eid NOT IN (SELECT eid FROM Certified)
AND salary > (SELECT avg(salary) FROM Employee WHERE eid IN (SELECT eid FROM Certified))

venereology answered 2 years ago

Design a database through the EER diagram to keep track of the teams and games of...

Design a database through the EER diagram to keep track of the teams and games of a sport league. Assume that the following requirements are collected (the English description of cardinal ration and partial/complete participate is NOT required, but you still need to provide the total/partial and cardino ration in your EER diagram) : The database has a collection of TEAM. Each Team has a unique name, players, and owner. The database also keeps the records of PLAYERS. Each player...

1. A cosmetic product retailer needs to create a database to keep track of the information...

1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase...

Consider the following set of requirements for a UNIVERSITY database that is used to keep track...

Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts. (a) The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, ..., graduate), major department, minor department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address, and to...

Using c++ Design a system to keep track of employee data. The system should keep track...

Using c++ Design a system to keep track of employee data. The system should keep track of an employee’s name, ID number and hourly pay rate in a class called Employee. You may also store any additional data you may need, (hint: you need something extra). This data is stored in a file (user selectable) with the id number, hourly pay rate, and the employee’s full name (example): 17 5.25 Daniel Katz 18 6.75 John F. Jones Start your main...

JAVA - Design and implement a class called Flight that represents an airline flight. It should...

JAVA - Design and implement a class called Flight that represents an airline flight. It should contain instance data that represent the airline name, the flight number, and the flight’s origin and destination cities. Define the Flight constructor to accept and initialize all instance data. Include getter and setter methods for all instance data. Include a toString method that returns a one-line description of the flight. Create a driver class called FlightTest, whose main method instantiates and updates several Flight...

In the airline business, "on-time" flight arrival is important for connecting flights and general customer satisfaction....

In the airline business, "on-time" flight arrival is important for connecting flights and general customer satisfaction. Is there a difference between summer and winter average on-time flight arrivals? Let x1 be a random variable that represents percentage of on-time arrivals at major airports in the summer. Let x2 be a random variable that represents percentage of on-time arrivals at major airports in the winter. A random sample of n1 = 16 major airports showed that x1 = 74.5%, with s1...

Design a simple database to track people and who they voted for. The database should have...

Design a simple database to track people and who they voted for. The database should have 3 tables: A table of candidates A table of registered voters A table of votes The candidate table should provide a listing of all candidates and information about the candidates. The registered voter table should hold all registered voters and any pertinent information about them The vote table should hold vote records for each candidate made by the voters Requirements: The system should not...

Design the database using the ER approach. Then using Java and SQL, implement the following functionality:...

Design the database using the ER approach. Then using Java and SQL, implement the following functionality: Implement a button called “Initialize Database”. When a user clicks it, all necessary tables will be created (or recreated) automatically, with each table be populated with at least 10 tuples so that each query below will return some results. All students should use the database name “sampledb”, username “john”, and password “pass1234”. Implement a user registration and login interface so that only a registered...

Given a relational database that consists of the following relations: Performer (pid: integer, pname: string, years_of_experience:...

Given a relational database that consists of the following relations: Performer (pid: integer, pname: string, years_of_experience: integer, age: integer) Movie (mname: string, genre: string, minutes: integer, release_year: integer, did: integer) Acted (pid: integer, mname: string) Director (did: integer, dname: string, earnings: real) Do the following using your Azure SQL database: a) Use SQL statements to create the relations. b) Populate the relations using SQL statements with the given data posted on Canvas. c) Implement the SQL queries for the following:...

Ava wants to use a database to keep track of the data recordsfor her insurance...

Ava wants to use a database to keep track of the data records for her insurance company and to enforce the following business policies/requirements: USE MS ACCESS TO CREATE A DATABASE & RELATIONASHIP-Every customer must be uniquely identified.-A customer can have many insurance policies.-Every insurance policy must be uniquely identified.-An insurance policy must belong to a valid customer.-Every customer must be served by a valid insurance agent (employee).-An insurance agent (employees) serves many customers.-Every insurance agent (employee) must be uniquely...

Question