ASSIGNMENT 2
Comp 3380 ASSIGNMENT 2 - Due Date Tuesday March 2, 2023, at 8:30 am Submit your Assignment in Um Learn including all your SQL queries. Please note that you must agree to the faculty of Science Honesty declaration to view your grade for this assignment . Use the library EER diagram and the library database in UM learn to answer questions 1-4 Create the Library database Using the Script provided in UMlearn, create, and populate all tables to answer the queries. Question 1 (5 marks) Write a SQL query to produce a list of authors that have written five or more books. Retrieve the Author ID, first name, last name and number of books they have written. Note: For full marks, your "number of books" column should be >=5 Question 2. (5 marks) A book's "outreach" is calculated as (? / ?) ∗ 100 Where: N is the number of people who have read the book and S is the number of stores in which the book is sold. An Analyst attempts to determine the outreach for every book using the following code. SELECT B.title, COUNT(sid)*100/COUNT(id) as outreach FROM Books B JOIN Sells S ON B.pid = S.sid JOIN Read R ON B.bid = R.bid GROUP BY B.title A. The code runs, but it gives the wrong answer. Identify at least 2 mistakes in the query written by the analysts. (2 marks) B. Re-write the query to retrieve the book title and calculate the correct outreach. (3 marks) Question 3 (3 marks) Write a query to retrieve first name and last name of Authors that have not read any book. Question 4 (3 marks) Write a query to retrieve first name, last name, and book titles of authors with more than 5 copies of book sold. Question 5 ( 6 marks) Write a query to retrieve names, reader age, book title, minimum age for the book, maximum age for the book of all people who have read books that are not specified for their age range. Example A 34 years old who has read a book written for teenagers (age 12 -20) or a book written for children Question 6 (13 marks) (Null and Pattern Matching) Create the 2 tables below in SQL database and run the following queries (2marks). Submit your create queries. The following queries must run successfully for your table query. a. Write a SQL statement to retrieve all employee with unknown department (1 mark) b. Write a query to retrieve all employee that are between age 50 and 60 as at today (4marks). (Note that you need to calculate age from Birth date) c. Write a SQL to retrieve all employee having name starting within the alphabet range B-N (3 marks) d. Write a SQL query to retrieve Name , address, and date of birth of employee living in Houston. (1 marks) e. Write a query to retrieve all Employee having alphabet ‘r' in their name (2 marks) Section 2: Functional Dependencies (15 marks) Question 7 (4 marks) Consider the following relation: CAR_SALE(Carid, Datesold, Salesmanid, Commision%, Discount Assume that a car may be sold by multiple salesmen and hence {CARid, SALESMANid} is the primary key. Additional dependencies are: Datesold →Discount and Salesmanid → commission% Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively normalize it completely? Question 8 (4 marks) Consider the following relation: R(DoctorID, PatientID, Date, Diagnosis, Treat_code, Charge) In this relation, a tuple describes a visit of a patient to a doctor along with a treatment code and daily charge. Assume that diagnosis is determined (uniquely) for each patient by a doctor. Assume that each treatment code has a fixed charge (regardless of patient). a. What are the functional dependencies from the relation b. Is this relation in 2NF? Justify your answer and decompose if necessary. Question 9 (4 marks) Consider a relation R(A,B,C,D,E) with the following dependencies: AB -> C CD -> E DE -> B Is AB a Candidate Key? (I MARK) What are the Candidate Keys? Use the closure property to determine the candidate key (3 MARKS) Question 10 (3 marks) Proof using Armstrong inference rule If A→B AND B→Z then A→BZ