Given database schema: Faculty (fid, name, tenured, dname, salary, chairID)Department (dname, phoneNumber, numberOfFaculty)Course (fid, dname, semester, cname) Write a trigger (one trigger command) in SQL for the following statement: When a new record is inserted into Faculty, if the department does not exist in the Department table, then insert a record into Department with the same department name and the phone number equal to null. Additionally, when a record is deleted from Faculty, if the faculty member is tenured, then raise an error (use RAISE_APPLICATION_ERROR(-10000, ’error’)).

Exercise 1 (Armstrong Axioms, Functional Dependencies) [30 points]

Referring to Table 1, if interest rates rise by 5 percentage points, then bank profits (measured using gap analysis) will

Exercise 1 (Armstrong Axioms, Functional Dependencies) [30 points]

Given database schema: Faculty (fid, name, tenured, dname, salary, chairID)Department (dname, phoneNumber, numberOfFaculty)Course (fid, dname, semester, cname) Write an assertion check in SQL for the following condition: The salary of any faculty member cannot be greater than their chair’s salary.

Consider the relation schema R(A, B, C, D, E) and the set F = {AB → C, CD → E, DE → B} of functional dependencies. Find all candidate keys of R by using the tableau (7‐step) method. Explain the steps.

We decide to save time by sampling 1000 flights at random from a single month (instead of 250 from the whole data set) and then using the same process to get a confidence interval for the percentage of flights in the year that are delayed. Is this a good idea? Choose all the correct statements.

Given database schema: Faculty (fid, name, tenured, dname, salary, chairID)Department (dname, phoneNumber, numberOfFaculty)Course (fid, dname, semester, cname) Write an assertion check in SQL for the following condition: The salary of any faculty member cannot be greater than their chair’s salary.

Consider the following relation r with the schema R(A, B, C). Find all non-trivial functional dependencies that can be derived from the data in r. Give reasons for your answer. A B C b c h e i f g i f e b a

Consider the relation schema R(A, B, C, D, E) and the set F = {A → B, B → CE, D → AE} of functional dependencies. Show that the FD AB → AC is logically implied by F by using Armstrong’s axioms only (do not use the derived inference rules and the attribute closure algorithm).