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

Assignement


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 surrogate key to the existing attributes (5 marks) b. 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 b. “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 Library Database for the following questions in Neo4j a. Create the database including all relationships in Neo4j (5 marks) Submit the queries and the outputs b. 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)