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 li
ary EER diagram and the li
ary database in UM learn to answer questions 1-4
Create the Li
ary 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 co
ect outreach. (3 marks)
Question 3 (3 marks)
Write a query to retrieve first name and last name of Authors that have not read any
ook.
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 XXXXXXXXXXor 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)
. 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
. 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