CS 348 - Spring 2024 Assignment #1

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 ".

(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