Comp 3380 Winter 2023 ASSIGNMENT 3 - Due Date Thursday March 30,2023 at 8:30 am
This assignment is individual work. Be sure to fill out the honesty
declaration available on the course website. You will not be able to see
your grade without filling out the declaration.
Please submit your assignment in Crowdmark
Question 1 (10 marks)
(a) Briefly describe the 3 approaches to database programming including at least one
advantage and one disadvantage of each approach (3 marks)
(b) Describe impedance mismatch issues, and possible solution (2 marks)
(c) Describe any 2 issues arising from SQL injection any 2 protecting techniques (2 marks)
(d) Describe at least 2 uses and 2 issues of indexing in relational database (2 mark)
(e) Compare and contrast VIEW and TRIGGER in relational database (1 marks)
Question 2 (20 marks)
Consider the Parking Winnipeg dataset provide in UMLEARN in answering the
following questions using a programming language (Python or Java) and clearly showing
how you CONNECT-EXECUTE-CLOSE.
a. Create a database table for the dataset introducing a SURROGATE key as the
primary key. Submit you programming code for the create table showing the addition
of su
ogate key to the existing attributes (5 marks)
. Write an embedded SQL statement within your programming language to classify the
dataset to year and day name hierarchy. See expected sample output in table 1 (5
marks)
Year Day Count
2010 Monday 2343
c. Write an embedded SQL statement to retrieve the street, year, month, day, and the
hour when parking authority issued the highest number of tickets. (5 marks)
d. Write an embedded SQL query to create a database procedure (classifier) to classify
the full fine into 3 categories (5 marks)
a. “Common Ticket” if the FULLFINE is less than or equal to 70
. “Valued Ticket” if the FULLFINE is between 71 and 200 inclusive
c. “Most valued Ticket “ if the FULLFINE is greater than 200
d. “Unknown” if the full fine is null
Bonus: Call and execute the procedure within your program (1 mark)
Question 3 (5 MARKS)
Proof using Armstrong inference rule
3 Given the following relation R and its functional dependencies, Find the closure of C Using
Armstrong’s Axioms.
R(A, B, C, D, X, Y, W)
BDW -> C
Y -> W
DXY -> AB
C -> BW
BC -> Y
Question 4 (20 marks)
Use the Li
ary Database for the following questions in Neo4j
a. Create the database including all relationships in Neo4j (5 marks)
Submit the queries and the outputs
. Submit the query, graph, and text for the following
i. Write a Cypher query to retrieve first name, last name, and book titles of authors having
at least 5 copies of book in stores. (3 marks)
ii. Write cypher query to retrieve first name and last name of authors that have read another
author’s book. (3 marks)
iii. Write a cypher query to retrieve 4 topmost read book by country (2 marks)
iv. Write cypher query to retrieve the most popular publisher. (Publisher having the highest
number of published book) (3 marks)
v. Write cypher query to retrieve the book name, author’s name, audience, and count of read
for the top 5 most read books. (4 marks)