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