Canvas Assignments

PART 1: DATABASE
Q1.A) Vancouver Library has provided you with the files used to track their inventory. Use this data to design a normalized relational data model for this library. Normalize the data as necessary and list all tables using the following notations. Indicate Primary Keys as underlined and Foreign Keys as italic. (20 marks)

NOTE: missing normalized table file = -5 marks per each file

Q1.B) Use this data to design the structure of the database required for this case and visually represent this with the ER Diagram. Using any diagram tool, create an ERD with the required entities, attributes, and relationships. (20 marks)
Insert an image of your ER Diagram in the box below. Additionally, submit a high-resolution copy to Canvas as a PNG, GIF, or JPG image file.

NOTE: missing ERD file = -10 marks

Q1.C) What additional information might you require in order to provide Vancouver Library with further insights into their data? Are there any other tables that would be helpful? (5 marks)

PART 2: ETL
Q2.A) If the Publication Date has imported as a number, how might this cause a problem without analysis if we wanted to dig into the data by month, for example? (4 marks)

Q2.B) Examine the Price field. What are the benefits of this datatype being imported as a numeric field? What issues could arise if this was imported as a text field? (4 marks)

PART 3: ANALYSIS
Q3.A) Which Library name has the highest number of staff? Display your results in a tree map. (3 marks)

Q3.B) What are the top 10 Author ID’s with the highest average Price? Display your results in a bar graph. (3 marks)

Q3.C) Which Publisher ID had the highest average Price in the year 2180 (Hint: Use the Publication Date)? (1 mark)

Q3.D) Which two libraries do not have any Library Assistants? (1 mark)
Library Name #1:
Library Name #2:

Q3.E) Who is the oldest author? What year is this author born in? (2 marks)
Author’s Name:
Birthday Year:

Q3.F) Is there any correlation between the Print Run Size and the Price for a book? Please include a trend-line in your results. (2 marks)

Q3.G) In the year 2191, which month had the highest book price? Which month was it and what was the price? (2 marks)
Month:

Q3.H) There is one author who only uses the Mass Market Paperback as the format of their book. What is their Author ID? (2 marks)

Q3.I) Which book has the highest average number of pages? What is the Genre of this book? Create a bar graph and display the top 10 books by average number of pages. Use the following colour scheme to organize your data. (3 marks)

Book Title:
Genre:

Q3.J) Which Country of Residence has the lowest average Price? Display your results on a filled map, using the “Sunrise-Sunset Diverging” colour palette. (3 marks)
Country Name:
Average Price:

PART 4: DASHBOARD
Q4.A) Create a digital dashboard within Tableau with the following worksheets and filter your dashboard by the country, “United States”. (6 marks)
• Adjust the window of the dashboard to be customized (width: 1500px * height: 750px)
• Q3.A) Make sure your results are displayed in a tree map
• Q3.B) Make sure your results are displayed in a bar graph
• Q3.I) Make sure your results are displayed in a bar graph
• Q3.J) Make sure your results are displayed in a filled map

Q4.B) After completing the steps above, what additional data would you recommend that Vancouver Library analyze? What is another data visualization that would be helpful for Vancouver Library? (4 marks)

Q4.C) Upload your FINAL Tableau assignment file, in the twbx format via Canvas.

NOTE: missing or incorrect Tableau file = -10 marks

PART 5: VISUALIZATION
Q5.A) Review the dashboard provided below. List and briefly discuss at least 3 weaknesses of this visualization. For each weakness, please provide a suggestion for improvement. (6 marks)