代做CS 348 - Spring 2024 Assignment #1代写留学生SQL 程序

Assignment #1

CS 348 - Spring 2024

For 002 and 003 sections

Due : 11:59 p.m., Tue, June 4, 2024

Submission Instruction

This assignment consists of 2 parts covering topics on general dbms concepts, relational model, relational algebra, and SQL. Part I will be submitted through Crowdmark. See the website for more detailed instruc- tions.  In particular,  do not forget to submit one file per question to make the lives of TAs easier. You do not have to type the questions if you are using Latex.  Just specifying the question number is good enough. You do not need to keep the same font style. Here is a draft/empty latex template you may down- load and use: Overleaf Template.  Handwritten solutions scanned to pdf are also acceptable as long as they are readable.

Part 2 is a programming question and requires submission of files through Marmoset.  See the Learn post for detailed instructions about how to test your programs and submit your final files.

Part I

This part consists of 3 questions.

Question 1.

[12 marks in total]

(a)  (4 marks) Give an example of how concurrent requests can cause data inconsistency  (example should differ from but can be similar to the book example used in Lecture 1).  Discuss how locking can help mitigate such anomalies.

(b)  (4 marks) Provide two differences between foreign key constraints and tuple-based CHECK assertions (i.e., defined inside a CREATE TABLE statement)?

(c)  (4 marks) Give an example of when one would use a foreign key constraint and not a tuple-based CHECK. Give an example of when one would use a tuple-based CHECK and not a foreign key constraint.  Think of something a user can instruct the DBMS to dousing a foreign key constraint but not a tuple-based check and viceversa.

Question 2.

[30 marks in total]

Consider the following database schema modeling (i) the fleet of an airline company (stored in the Aircraft table); (ii) the employees of an airline company (stored in the Employee table), some of whom maybe pi- lots (stored in Pilot table), and information about which pilot is certified for which aircraft (stored in a Certified table). The specific schema is as follows:

Aircraft(aID: int, producer: varchar(30), cruisingrange: float) Employee(eID: int, ename: varchar(30), salary: float)

Pilot(eID : int, ranking: int)

FK: eId references Employee(eID)

Certified(eID : int, aID : int, cyear: int)

FK: eId references Pilot(eID)

FK: aId references Aircraft(aID)

As usual, the union of the underlined columns for each table T form. the primary key of T.  There are in ad- dition three foreign key (fk) constraints in the database:  (i) Pilot .eID is afk referencing Employee .eID; (ii) Certified .eID is afk referencing Pilot.eID; and (iii) Certified .aID is a foreign key referencing Aircraft .aID. These are all of the integrity constraints that are specified.  Also assume that the given database system does not allow NULL values to be used for primary key columns of tables.  We will use this database schema in Questions 2 and 3 of Part I and the programming questions in Part II. Here is also a sample instance of this database.

Based on the given schema and instance, please answer the following questions.  For True/False questions, please indicate “ T " (for “true") or “ F " (for “false") and explain your choice using no more than 4 sentences. Unless a question explicitly mentions ‘in the given database instance’, your answers should not simply be based on the given instance.

(a)  (3 marks)  (eID,  ename) is a candidate key for the Employee relation.  Ans: “T" or “ F ".

(b)  (3 marks) We can insert tuple (106, 4) into Pilot in the database instance above. Ans: “T" or “ F ".

(c)  (3 marks) We can insert tuple (104, 15, NULL) into Certified table in the database instance above. Ans: “T" or “ F ".

(d)  (4 marks) Write the output table generated by the following SQL query on the sample database instance specified in this question.

SELECT  producer,  AVG(cruisingrange)  AS  avg_cruising_range

FROM  Aircraft

GROUP  BY  producer

ORDER  BY  avg_cruising_range  DESC

LIMIT  3;

(e)  (4 marks) Consider the question:  Find  eIDs  of all pilots who are certified to fly aircrafts or have a ranking strictly greater than 2.   The following  SQL query returns all such pilots  (on an arbitrary database instance and not just the instance provided above).

SELECT  DISTINCT  p .eID

FROM  Pilot  p,  Certified  c

WHERE  p .eID  =  c .eID  OR  p .ranking  >  2;

Ans: “T" or “ F ".

(f)  (4 marks) Assume that all of the foreign key constraints that are mentioned in the question statement above are “cascading”, i.e., they have been defined in the schema with “on delete cascade“ statement. Consider deleting the (104, Bob, 4000.0) tuple from Employee table in the given instance.  List the set of cascading tuples from each table in the <table name>:  <tuple> format.

(g)  (4 marks) The below two SQL queries will return the same output.  Ans:  “ T" or “ F ".   Explain the output generated by the queries.

1 .         SELECT  eID  FROM  Pilot  NATURAL  JOIN  Certified

EXCEPT

SELECT  eID  FROM  Pilot

2 .         SELECT  eID  FROM  Pilot  NATURAL  JOIN  Certified

EXCEPT  ALL

SELECT  eID  FROM  Pilot

(h)  (5 points) Answer the two related questions.

1. Write a DDL query (CREATE TABLE) to create the Certified table incorporating the specified constraints.

2.  After creating the table using the above command, the airline company decides to add a constraint on the year of certification.  The company wants to allow year values greater than 1903 - the year when airplanes were invented. Write a SQL query to add this constraint on the Certified table.

Question 3.

[24 Marks in total]

Consider the database schema from Question 2.  Write the following queries in relational algebra (only use operators described in the class).

(a)  (3 marks) Names of employees who are not pilots.

(b)  (4 marks) Names of pilots who are certified for a Boeing aircraft.

(c)  (4 marks) IDs of pilots who are certified for both a Boeing and Airbus aircraft.

(d)  (4 marks) IDs of pilots who are certified for the aircrafts with the longest cruising range.

(e)  (4 marks) Producers of aircrafts that have at least 3 certified pilots.

(f)  (5 marks) IDs of pilots who are certified for every aircraft that the pilot with ID 104 is certified for.

[Consider expressing pilots that are not certified for at least 1 aircraft that 104 is certified for].

Part II.

[34 marks in total]

This is the programming part of the assignment and requires students to write 6 SQL queries on the IBM DB2 RDBMS. These will be submitted through Marmoset.  For each question, there will be  1 public test and some number of secret tests, each worth 1 mark.  Follow the instructions on the Learn post to:  (i) get started with DB2; (ii) to test your solutions using public tests; and (iii) to make your final submission through Marmoset.

Consider the database schema from Question 2. Write SQL queries to achieve the requested result (only use SQL statements/queries described in the class).  For the queries below, if we are asking for information that is directly in a column A of atable R, directly SELECT R.A and do not change the name of the column. If some aliasing is needed, we explicitly specify those columns below.

1.sql

(5 marks) Find the eid and ename of all pilots certified to fly Boeing planes in year 2010. Expected columns: eid,ename

2.sql

(5 marks) Find the average salary (as avgSal) of the pilots certified to fly the highest (i.e., most) number of aircrafts.

Expected columns: avgSal

3.sql

(6 marks) Find the aircraft ids that are certified to fly by every pilot with ranking greater than 6. List the aircraft ids in the descending order of their cruising range.  For example, if there are 3 pilots with rank higher than 6, then the listed aircrafts should be certified with all 3 pilots.

Expected columns: aid

4.sql

(6 marks) Find the names of pilots who can operate planes with a range greater than 3,000 miles, but are not certified on any aircraft from Boeing.

Expected columns: ename

5.sql

(6 marks) Find the aircraft ids, along with producers, that are certified to fly by at most 3 pilots (they can be certified by 0 pilots) and the maximum salary (as smax) of the pilots certified for such aircrafts.  If an aircraft is not certified by any pilot, the maximum salary should be 0 and not NULL.

Expected columns: aID,  producer,  smax

6.sql

(6 marks) Consider all pilots p1 who get paid less (in terms of salary) than another pilot p2 with a smaller ranking and p2 is certified for fewer (but at least 1) aircraft.  Select the IDs, names, and maximum salary difference (as maxDiff) of all such distinct p1.  maxDiff column for p1 should be the maximum salary difference of p1 with a p2 that satisfy the above 3 conditions.

Expected columns: eid,  ename,  maxDiff