Description

Section 2: Database Design (10 points)

In this section, you will answer 3 database design questions. The first two questions are worth 3 points each and the last question is worth 4 points. Please do not forget to provide information about entities, relationships, and attributes for each question to get full marks.

1.     You are asked to model the many to many relationship between students and classes in a relational database.

o   What changes do you need to make to support this relationship?

o   Please create an ER diagram to show how these entities will relate to each after your changes.

STUDENTS                     M:M                CLASSES

2.     You are asked to model the many to many relationship between customers and products in a relational database.

o   What changes do you need to make to support this relationship?

o   Please create an ER diagram to show how these entities will relate to each after your changes.

CUSTOMERS                 M:M               PRODUCTS

3.     Design an ER diagram for a library reservation system for a family of libraries based on the given characteristics.

o   This system is for multiple libraries

o   This system is for multiple borrowers

o   There are multiple types of content that can be borrowed

o   Borrowers can borrow multiple items at the same time

o   Borrowers can borrow multiple types of content

Be sure to list all necessary entities, relationships, and attributes to model this system in a relational database

Section 3: Data Analysis with SQL (20 points)

In this section, you’re going to use the data set san_francisco_bikeshare (under bigquery-public-data ) in Google BigQuery to answer some business questions using SQL. Take some time to familiarize yourself with the data set before answering your questions.

Your output will be a 1 page report, which diagnoses the problems you see, provides a few potential resolutions, and recommends one solution with a justification of why. The report must fit on one page.

You will also submit an appendix, which includes all the SQL you ran to get to your answer and any tables, maps, or charts you think are helpful to make your point. Please add a comment on top of each figure in your appendix to explain what insight it is providing.  

Connecting to BigQuery

1.     Navigate to the BigQuery UI (Links to an external site.)

2.     If necessary, start a New Project

3.     Click “ADD DATA”