3380 Fall 2020 Assignment 2
Parts 1 to 4 are to be handed in with Crowdmark. Submit a separate file for each question, containing only the answer for that question.
Part 5 is to be handed in with handin
handin 3380 a2 myA2Folder
Part 1 – Normalization (7 marks)
You are given the following relation R and some functional dependencies.
R(SID, Project, Code, ListOfSupplies, Name, Initials, Abbrev)
• Project → ListOfSupplies
• SID → Name
• Name → Initials
• Project, Initials → Abbrev
• SID, Project → Code
• Code → SID
1. Is R in 1NF? If not, normalize R into a collection of 1NF relations.
2. Is R in 2NF? If not, normalize R (or your collection of 1NF relations) into a collection of 2NF relations.
3. Is R in 3NF? If not, normalize R (or your collection of 2NF relations) into a collection of 3NF relations.
4. Is R in BCNF? If not, normalize R (or your collection of 3NF relations) into a collection of BCNF relations, or explain why it is impossible.
Part 2 – 3NF != BCNF (2 marks)
Provide an example of a relational schema which is in 3NF but not BCNF. You may use any example you like apart from the one discussed in class and (if relevant) the one used in Part I. Please note that changing the attribute names and nothing else does not constitute creating a new example.
Part 3 – Spreadsheet to Relational (5 marks)
Given the data for council member expenses
https://data.winnipeg.ca/Council-Services/Council-Member-Expenses/mgde-4fua
1. What are the FDs in the dataset? (Do not worry about incidental/emergent FDs that are in the table – just intentional ones. Example: amount, day->wardID could be true in the table, but is not intentional)
2. Normalize the data info BCNF – or to the highest normal form possible, explaining why BCNF is impossible. Use good table names. Show your results in the Relational short-hand notation.
3. Show the ER diagram matching your normalized tables from 2.
You may add generated IDs to your tables.
Part 4 – FD Reasoning
REMEMBER to formalize your proof steps as shown in class!
Problem #1 (2 marks)
Using only Armstrong’s Axioms, prove the Union rule
If X → Y and X → Z, then X → YZ
Problem #2 (2 marks)
Using only Armstrong’s Axioms, prove the Decomposition rule
If X → YZ, then X → Y and X → Z
Problem #3 (2 marks)
Prove (using Armstrong’s Axioms) or disprove (via counterexample)
If X → W and Y → Z, then XY → WZ
Problem #4 (5 marks)
Choose ONE of the following. State which you chose, and answer all the associated questions.
Option 1
Given R(A, B, C, D, E, F, G, H, I) and the following functional dependencies:
1. C,E,F → A,D,G,H
2. H,I → A
3. B,G,H → C
4. G → A,C,F,H
5. B → E
6. A → C
Answer the following:
1. What are ALL the candidate keys of R?
2. Choose one candidate key. Provide a formal proof that it is a candidate key.
Option 2
Given two sets of FDs F and G, G is said to be a minimal cover of F if the following are all true
1. G is equivalent to F (that is, they contain the same information and you can draw the same conclusions from them)
2. The consequent (aka RHS) of every FD in G is a singleton
3. It is not possible to make G smaller (by either removing an FD or an attribute from the LHS of an FD) and still have the above two properties be true
Given a relation S(A,B,C,D,E,F,G,H) and F, the following set of FDs
1. BGH → F
2. F → AD
3. E → F
4. ABH → C
5. A → D
6. C → E
7. BH → E
Determine a set of FDs G such that G is a minimal cover of F
Part 5 – CheerTube (10 marks)
Given the data for CheerTube (elevator pitch: Youtube for Santa’s elves), create a functioning java program that collects statistics based on the data.
The data must be loaded into java. Look at the data, and normalize it to BCNF. You assign primary keys that are not in the provided dataset where appropriate.
You will have to consider how to load the data, so that the key are made in the correct order that we can maintain referential integrity.
You have been asked to create the following reports. Perform and print results for the following:
1. Account ID (or IDs) associated with a given Elf
2. Show all the bills for the account found in 1.
3. Show the video information for a provided CheerTube link – Video name, link, number of views
4. Show the videos created by the given elf, with the total associated views.
5. Find the videos for all Elves, where the creator is the only viewer
6. Find the videos for all Elves where there is only 1 view that is not the creator elf
7. Print a report of the top 5 videos that have the highest total run time. Report: creator name, video name, and total time played.
Example output
Gimme an Elf name:
Gimme a CheerTube link
Q1 – account for Snowball Grottobow
Snowball Grottobow is associated with account 5
Q2 – Bills for Snowball Grottobow
Snowball Grottobow has has bill 58 which is for 500c
Snowball Grottobow has has bill 59 which is for 800c
Snowball Grottobow has has bill 60 which is for 1900c
Snowball Grottobow has has bill 61 which is for 200c
Snowball Grottobow has has bill 62 which is for 1200c
Snowball Grottobow has has bill 63 which is for 1900c
Snowball Grottobow has has bill 64 which is for 500c
Snowball Grottobow has has bill 65 which is for 2000c
Snowball Grottobow has has bill 66 which is for 1200c
Snowball Grottobow has has bill 67 which is for 1800c
Snowball Grottobow has has bill 68 which is for 300c
Snowball Grottobow has has bill 69 which is for 700c
Snowball Grottobow has has bill 70 which is for 2000c
Snowball Grottobow has has bill 71 which is for 400c
Snowball Grottobow has has bill 72 which is for 1000c
Snowball Grottobow has has bill 73 which is for 1300c
Snowball Grottobow has has bill 74 which is for 1000c
Snowball Grottobow has has bill 75 which is for 900c
Snowball Grottobow has has bill 76 which is for 1700c
Snowball Grottobow has has bill 77 which is for 1500c
Snowball Grottobow has has bill 78 which is for 1100c
Snowball Grottobow has has bill 79 which is for 500c
Q3 – views for video with link bz4bnJ77um
The Absent Waves/bz4bnJ77um has 928 views
Q4 – videos for Snowball Grottobow’s and number of views
Snowball Grottobow;’s video The Awkward Quiet of the Fire has 1 views
Snowball Grottobow;’s video The Awkward Quiet of the Fire Part 2 has 0 views
Snowball Grottobow;’s video The World of Flames has 2029 views
Snowball Grottobow;’s video The Eager Something has 1455 views
Q5 – views of videos with no other views than the creator
Video The Awkward Quiet of the Fire has no other views
Video The Secrets in the Boy has no other views
Q6 – Viewers who are the only viewer of a video that is NOT the creator
Video The Amazing Journey Part 2 has no other views other than Winter Morningsong
Q7 – Users with the most minutes views
Video Only Prince by Fizzy Chillygift Has total time 217654235 minutes
Video The Boy of the Silk by Tinkles Muffindash Has total time 181339484 minutes
Video The Birth’s Rainbow by Choco Milkytoy Has total time 157488104 minutes
Video The Wizard in the Soul by Brownie Chillypie Has total time 143314300 minutes
Video Last Female by Gingersnap Everguest Has total time 132912681 minutes
Exiting…
Notes:
Do not store data in data structures (ArrayLists/arrays/HashMaps/etc). The SQL commands can be run with 1 executeQuery. No SQL for the requested reporting should be in a loop. Queries should take no more than 45s.
HSQLDB uses the IDENTITY keyword, which must be applied to a primary key, to make an auto-incrementing generated primary key. You can also do this manually.
The data in the files is messy. You may have to read the files more than once to populate the tables. Populating the table may take a long time. Consider modifying the dataset.
You have been given some starter code. You may modify any part of it, or start from scratch. The CREATE TABLE statements must be clear, and your report output must be clear.
Leave in the code to choose an Elf name, and CheerTube link, so we can test your program on any Elf name, and any CheerTube link.
The makefile must work on your handed-in material. make and make run must compile, and run your program.
Hand in all material to make your assignment go: text files, JAR file for HSQLDB, your code (in any number of .java files).