Part I due by 11:59 p.m. on Tuesday, January 30, 2024
Part II due by 11:59 p.m. on Tuesday, February 13, 2024
If you haven’t already done so, you should complete Lab 0 before beginning this assignment.
In your work on this assignment, make sure to abide by the collaboration policies of the course.
All of the problems in this assignment are individual-only problems that you must complete on your own.
If you have questions, please come to office hours, post them on
Piazza, or email cs460-staff@cs.bu.edu
.
Make sure to submit your work on Gradescope, following the procedures found at the end of Part I and Part II.
30 points total
If you haven’t already created a folder named cs460
for your
work in this course, follow these
instructions to do so.
Then create a subfolder called ps1
within your cs460
folder,
and put all of the files for this assignment in that folder.
The problems from Part I will all be completed in a single PDF file. To create it, you should do the following:
Access the template that we have created by clicking on this link and signing into your Google account as needed.
When asked, click on the Make a copy button, which will save a copy of the template file to your Google Drive.
Select File->Rename, and change the name of the file to
ps1_partI
.
Add your work for the problems from Part I to this file.
Once you have completed all of these problems, choose
File->Download->PDF document, and save the PDF file in your
ps1
folder. The resulting PDF file (ps1_partI.pdf
) is the one that
you will submit. See the submission guidelines at the end of Part I.
10 points total
Before going any further, make sure to follow the instructions above to create your own copy of the Google Drive document that you should use for Part I of the assignment.
The ER diagram shown below is part of the design of a database that
includes information about actors and the parts that they perform in
plays, musicals, movies and TV shows. Each actor has a name and a unique id.
Each part can be uniquely identified by the combination of the id of the
corresponding show and the name of the role, and the database also keeps
track of how many lines of dialogue that each part has (the attribute called
line count
).
In the above version of the ER diagram, there are no constraints on the relationships between actors and parts.
In the ps1_partI
template that we’ve provided on Google Drive (see
above), we’ve included the beginnings of three separate versions of
this ER diagram.
(2 points) Edit the first version of the diagram, adding the connections needed to create an ER diagram whose only constraint is that every part is performed by at least one (i.e., 1 or more) actor. To do so, you should:
Click on the diagam and then click the Edit link that appears below the diagram.
From the collection of eight connectors that we have provided below the diagram, select the appropriate connectors and use them to connect the two entity sets to the relationship set. Adjust the connectors as needed to make the connections.
Click the Save & Close button.
(2 points) Edit the second version of the diagram, adding the connections needed to create an ER diagram whose only constraint is that every actor performs at most one (i.e., 0 or 1) part.
(2 points) Edit the third version of the diagram, adding the connections needed to create an ER diagram whose only constraints are (1) that every actor performs at least one part, and (2) that every part is played by exactly one actor.
(4 points) Consider your answer to part 2—the ER diagram for the situation in which every actor performs at most one part.
Is it possible to convert that diagram into a relational schema that has only two tables/relations, or would three be needed? Explain your answer briefly, and then specify the schema of the two or three tables.
Give the schema of each table in the form relation_name(attr_name1, attr_name2, ...).
Indicate the primary-key attribute(s) of each relation by underlining them.
10 points total
The ER diagram shown below in Figure 2-1 represents information that is to be stored in a bank database.
Figure 2-1:
Customers borrow loans and own accounts, and accounts have account types.
(2 points) At least one of the relationship sets captures a many-to-one relationship. Which one(s)? In your answer, you should specify the direction of the relationship (e.g., ________ is a many-to-one relationship from _________ to ___________).
(3 points) Describe all constraints on relationships that are specified by the diagram. Use words that describe the problem domain (e.g., Each course meets in at most one room...) rather than technical terminology.
(5 points) Transform this diagram into a relational schema by following the procedure discussed in lecture and fill in the table that we have provided. Here are some additional guidelines:
When appropriate, you should combine relations as discussed in lecture.
In the first column of the table, give the schema of each relation in the form relation_name(attr_name1, attr_name2, ...).
Indicate the primary-key attribute(s) of each relation by underlining them.
If a relation has one or more foreign-key attributes, specify them in the second column of the table, and specify which attribute each foreign key refers to. For example, if you were working with the MajorsIn relation from the university database covered in lecture, its foreign keys would be specified as follows:
10 points total; 2 pts. each part
We will cover the material needed for this problem in lecture on Wednesday, January 24, and Friday, January 26.
Relation R has attributes a, b and c. Relation S has attributes d, b, and a. You are given the following instances of these relations:
Relation R
a |
b |
c |
---|---|---|
1 |
2 |
3 |
3 |
4 |
6 |
5 |
6 |
1 |
Relation S
d |
b |
a |
---|---|---|
3 |
2 |
1 |
5 |
4 |
3 |
9 |
8 |
5 |
For each of the following questions, use the Insert->Table menu option in Google Drive to insert an appropriately sized table for the answer, and then fill in the cells of the table with the correct values.
Once you have completed Part I in Google Drive, choose
File->Download->PDF document, and save the resulting file
(ps1_partI.pdf
) in your ps1
folder.
Login to Gradescope by clicking the link in the left-hand navigation bar. When logging in, make sure that you use the School Credentials option and select Boston University.
Once you are in logged in, click on the box for CS 460. (If
you don’t see that box, email cs460-staff@bu.edu
ASAP and ask to
be added to the course on Gradescope. If the deadline is fast
approaching and you don’t have access to CS 460 on Gradescope,
email your PDF to cs460-staff@cs.bu.edu
before the
deadline.)
Click on the name PS 1: Part I in the list of assignments. You should see a pop-up window labeled Submit Assignment. (If you don’t see it, click the Submit or Resubmit button at the bottom of the page.)
Choose the Submit PDF option, and then click the Select PDF
button and find the ps1_partI.pdf
that you created in step 1.
Then click the Upload PDF button.
You should see a question outline along with thumbnails of the pages from your uploaded PDF. For each question in the outline:
As you do so, click on the magnifying glass icon for each page and doublecheck that the pages that you see contain the work that you want us to grade.
Once you have assigned pages to all of the problems in the question outline, click the Submit button in the lower-right corner of the window. You should see a box saying that your submission was successful.
You can use the Resubmit button at the bottom of the page to resubmit your work as many times as needed before the final deadline.
Important
It is your responsibility to ensure that the correct version of a file is on Gradescope before the final deadline. We will not accept any file after the submission window for a given assignment has closed, so please check your submission carefully using the steps outlined above.
If you are unable to access Gradescope and there is enough
time to do so, wait an hour or two and then try again. If you
are unable to submit and it is close to the deadline, email
your homework before the deadline to
cs460-staff@cs.bu.edu
70 points total
We will complete the material needed for this part of the assignment during the week of January 29.
The problem domain for this assignment is movie trivia.
Before you get started, you should carefully read over the full description of the database. Please do so now!
Go to sqlitebrowser.org/dl.
Find the appropriate download link for your machine/operating system, and download and install that version of DB Browser for SQLite.
If you have a Mac, you should download the appropriate Mac .dmg file. Double-click on the file, which will open up a window. Then drag the application’s icon to your Applications folder.
If you have a Windows machine, you should download the Standard installer for 64-bit Windows (which is an .msi file). Double-click on the file to run the installer. You should be able to use all of the default options.
If you are unable to install DB Browser on your own machine, you can use it on the virtual desktop.
Download the following files into your ps1
folder:
If your browser doesn’t allow you to specify where the file should be saved, try right-clicking on the link above and choosing Save as... or Save link as..., which should produce a dialog box that allows you to choose the correct folder for the file.
Launch the program.
Click the Open Database button, and find and open the movie.sqlite
database file that you downloaded above.
To explore the schema of the database, click on the Database Structure tab, and then click on the arrows to the left of the table names.
To explore the contents of the tables, click on the Browse Data tab, and then choose the appropriate table from the drop-down menu.
Use the Execute SQL tab to perform queries on the database. Enter your SQL command in the space provided, and press F5 or Ctrl-R to run it. (There is also a small button that you can click; it has a triangular shape that looks like the Play button of a music player.)
ps1_queries.py
is a Python file, so you could use a Python IDE
to edit it, but a regular text editor like TextEdit or Notepad++
would also be fine. However, if you use a text editor, you must
ensure that you save it as a plain-text
file.
Construct the SQL commands needed to solve the problems given below. Test each SQL command in DB Browser for SQLite to make sure that it works.
Once you have finalized the SQL command for a given problem, copy
the command into your ps1_queries.py
file, putting it between
the triple quotes provided for that problem’s variable. We have
included a sample query to show you what the format of your
answers should look like.
Unless otherwise stated, each of the problems must be solved by means of a single query (i.e., each query should have a single semi-colon). Use nested subqueries as needed.
Your queries should not use a LIMIT
clause.
Your queries should only use the keyword DISTINCT
if it is
necessary to obtain the correct results. Similarly, you should
only use an outer join when it is strictly necessary.
Make sure that the results produced by your queries contain exact answers to the problems. We should not have to infer the answer from the results. For example, if we ask you how many movies meet a given criterion, we want a number, not a list of the movies. In addition, your results should not include any extraneous information.
5 points
Make sure to read the important guidelines above before you get started!
Ryan Gosling and America Ferrera have both been nominated for an Oscar for their performances in Barbie. Write a single query to find the place of birth and date of birth for these two people. The result of the query should be tuples of the form (name of person, date of birth, place of birth).
Hint: If your initial query does not produce any results, you may
want to reconsider the logical operator (AND
, OR
, NOT
) that you
are using in your WHERE
clause.
5 points
It’s unusual to have a movie that is longer than 3 hours (180 minutes). Write a query that determines, for each movie rating, the number of movies with that rating that have a runtime of more than 180 minutes. The result of the query should be tuples of the form (rating, number of movies longer than 180 minutes).
Notes:
You should exclude from your results movies whose rating is NULL.
It is possible that not every movie rating has a movie that is longer than 180 minutes, and thus there may be some ratings that do not appear in your results. In other words, you do not need to take any special steps to include ratings with 0 movies longer than 180 minutes.
5 points
Jodie Foster has been nominated for a Best Supporting Actress Oscar for her performance in Nyad. If she wins, it will be her third Oscar. Write a query that finds her previous Oscars. The results of your query should be tuples of the form (movie name, award type, year in which the Oscar was awarded).
5 points
Write a query that finds the number of foreign-born directors who have directed one or more movies in which a foreign-born actor or actress appeared.
Hints:
'USA'
at the very end of their pob
field.NULL
value for pob
.5 points
How does the average runtime of movies change over time? Write a query that determines, for every year from 2010 until the present, the average runtime of movies from that year. The result of your query should be tuples of the form (year, average runtime for that year). Order the results from oldest to most recent.
5 points
Two of this year’s Best-Picture nominees have names that begin with the
word 'The'
: The Holdovers and The Zone of Interest. Write a query
to determine the names and years of all Oscar-winning movies whose
names begin with 'The'
. You should use pattern-matching as needed, and
make sure that you only include movies in which the first word of the
name is exactly ‘The’. The result of your query should be tuples of
the form (movie name, year in which the movie was released). If a given
movie has won more than one Oscar, it should appear only once in your
results.
Hint: Think about how to construct a pattern that obtains movie names whose first word is ‘The’ without getting ones whose first word begins with ‘The’ (e.g., ‘There’) or ones that have ‘The’ somewhere after the very beginning.
Note: You should NOT include movies whose name moves the word ‘The’ to the end of the string (e.g., ‘Usual Suspects, The’).
5 points
Write a query that determines the number of movies in the database that are shorter than all of the movies that have been named Best Picture? Hint: You will need a subquery.
5 points
Which directors have been most successful at the box office? Write a query that finds all directors who have directed at least four of the 200 top-grossing films. The result of the query should consist of tuples of the form (director, number of top grossers). List them in order of the number of top-grossers they have directed, from the highest number to the lowest. You may assume that all directors in the database have a unique name.
5 points
Write a query that determines the number of actors and actresses in the database who have not acted in any of 200 top-grossing movies. The result of the query should be a single number, and the count should not include people who are only in the database as a director.
5 points
When a movie is considered a biography, its genre attribute includes
the letter ‘B’ somewhere in its value. For example, the genre value of
the movie King Richard is 'BD'
. Write a query that summarizes the
Oscars won by biography movies in our database. Your results should
contain tuples of the form (movie name, award type). If a given movie
won multiple awards, it should have one tuple for each award; if it
has not won any awards, it should have a single tuple in which the
second value of the tuple is NULL
.
5 points
Write a query to find the most recent movie(s) in the database that have a G rating – i.e., the G-rated movies whose year is closest to this year. The result of the query should be one or more tuples of the form (movie name, year).
Important
For full credit, your answer must use a subquery. Although SQLite provides a way to solve this problem without a subquery, that technique is not standard SQL, and thus it should not be used as part of your answer.
5 points
The PG-13 rating was created in 1984 in response to concerns by parents that some movies with PG ratings were inappropriate for children. One of those movies was Indiana Jones and the Temple of Doom, which was never officially re-rated. Write a single SQL command that changes the rating of this movie to PG-13. Hint: This is the only question that does not call for a SELECT command. You will need to figure out the correct type of SQL command to use.
10 points total
Getting started
Your work on this problem should go in a separate PDF file called
ps1_problem16.pdf
. To create it, you should:
Access the template that we have created by clicking on this link and signing into your Google account as needed.
When asked, click on the Make a copy button, which will save a copy of the template file to your Google Drive.
Select File->Rename, and change the name of the file to
ps1_problem16
.
Add your work for this problem to this file.
When you are finished, choose File->Download->PDF document, and
save the PDF file on your machine. The resulting PDF file
(ps1_problem16.pdf
) should be submitted following the
instructions below.
The problems
Write a relational-algebra query for each of the following
problems. Above, you will write a SQL query for these problems;
here you should solve them using relational algebra instead.
See below for some additional guidelines.
(3 points) problem 6 (Oscars won by Jodie Foster).
Make sure to follow the guidelines below.
(3 points) problem 12 (Actors with no top grossers), but
instead of producing a count, your query should produce the ids
of the actors. Hint: Take advantage of the set difference
operator (-
).
(4 points) problem 13 (Oscars for biopics)
Additional guidelines
To form your queries, you should use the templates that we have
provided for each of the relational-algebra operators at the top
of ps1_problem16
. Copy the appropriate template(s), and replace
the placeholders with the appropriate expressions, attributes
and/or predicates to form each query. We have provided an example
in ps1_queries
of using two of the templates to form a single
query.
For a given problem, you are allowed to perform a sequence of two
or more queries in which the results of a given query are assigned
to a variable using the assignment operator (<–), and then that
variable is used in a subsequent query. We have provided an
example of this in ps1_queries
, and we recommend that you do
this whenever a given query would otherwise become too long or
difficult to read.
Note that using a sequence of queries is not allowed in your SQL answers, but it is allowed here.
You will make submissions to two separate assignments on Gradescope. The steps needed for the two submissions are different, so please make sure to follow carefully the procedures outlined below.
PS 1: Problems 4-15
Submit your ps1_queries.py
file using these steps:
Click on the name of the assignment in the list of assignments. You should see a pop-up window with a box labeled DRAG & DROP. (If you don’t see it, click the Submit or Resubmit button at the bottom of the page.)
Add your file to the box labeled DRAG & DROP. You can either drag and drop the file from its folder into the box, or you can click on the box itself and browse for the file.
Click the Upload button.
You should see a box saying that your submission was successful.
Click the (x)
button to close that box.
The Autograder will perform some tests on your file. Once it is done, check the results to ensure that the tests were passed. If one or more of the tests did not pass, the name of that test will be in red, and there should be a message describing the failure. Based on those messages, make any necessary changes. Feel free to ask a staff member for help.
Notes:
You will not see a complete Autograder score when you submit. That is because additional tests for at least some of the problems will be run later, after the final deadline for the submission has passed. For such problems, it is important to realize that passing all of the initial tests does not necessarily mean that you will ultimately get full credit on the problem. You should always run your own tests to convince yourself that the logic of your solutions is correct.
If you get a message saying that the autograder failed to
execute correctly, it is likely the case that one of your
queries is taking too long to execute. Make sure that you have
checked each query in DB Browser to ensure that it executes
correctly, and that you have copied the correct query into
your ps1_queries.py
file. If you have a query that is
causing DB Browser to hang, you should either remove that
query from your file before submitting it, or you should
comment it out by putting a #
symbol at the beginning of
each of line of the query.
If needed, use the Resubmit button at the bottom of the page to resubmit your work.
Near the top of the page, click on the box labeled Code. Then click on the name of your file to view its contents. Check to make sure that it contains the queries that you want us to grade.
PS 1: Problem 16
Submit your ps1_problem16.pdf
file using these steps:
If you still need to create a PDF file, open your file
on Google Drive, choose File->Download->PDF document, and
save the PDF file in your ps1
folder.
Click on the name of the assignment in the list of assignments on Gradescope. You should see a pop-up window labeled Submit Assignment. (If you don’t see it, click the Submit or Resubmit button at the bottom of the page.)
Choose the Submit PDF option, and then click the Select PDF button and find the PDF file that you created. Then click the Upload PDF button.
You should see a question outline along with thumbnails of the pages from your uploaded PDF. For each question in the outline:
As you do so, click on the magnifying glass icon for each page and doublecheck that the pages that you see contain the work that you want us to grade.
Once you have assigned pages to all of the questions in the question outline, click the Submit button in the lower-right corner of the window. You should see a box saying that your submission was successful.
Important
It is your responsibility to ensure that the correct version of every file is on Gradescope before the final deadline. We will not accept any file after the submission window for a given assignment has closed, so please check your submissions carefully using the steps outlined above.
If you are unable to access Gradescope and there is enough
time to do so, wait an hour or two and then try again. If you
are unable to submit and it is close to the deadline, email
your homework before the deadline to
cs460-staff@cs.bu.edu
Last updated on April 17, 2024.