Database Systems, CSCI 4380-01 Homework # 1

Database Systems, CSCI 4380-01
Homework # 1

Homework Statement. This homework is worth 5% of your total grade. If you choose to skip
it, Midterm #1 will be worth 5% more. Remember, practice is extremely important to do well in
this class. I recommend that not only you solve this homework, but also work on homeworks from
past semesters. Link to those is already provided in Teams, which I am repeating here:
http://cs.rpi.edu/~sibel/DBS_Past_Materials/
This homework aims to test relational algebra first and foremost, and a bit of normalization theory.
Please do the parts in sequence. The questions get harder and build on your knowledge of relational
algebra from previous parts. Each question is equal weight.
Database Description. Now that we have confused you all with so many sites for so many
different purposes, I assume you would like to construct a website to help manage your remote
learning lifestyle. Here is a starter database idea:
Sites(sitename, username, password, bestbrowser)
Classes(classcode, classname, semester, year, credits)
Classmeetings(classcode, dayofweek, starttime, duration, sitename, url, note)
Exams(classcode, examname, examdate, starttime, sitename, url, note)
Teaches(classcode, instructorname, email, note)
Officehours(classcode, dayofweek, starttime, duration, sitename, username)
Resources(rid, classcode, resourcetype, sitename, username, url)
where resourcetype is one of 'discussions','groups','hw','exercises','coursenotes','videos'.
Keys for each relation are underlined.
Sites are sites you have account on such as Submitty, Piazza, Gradescope, Webex Teams, etc.
You must store your username. You can also store your password in an encoded format!
Classes are classes you are currently taking or have taken in the past.
ClassMeetings are the specific meetings for that class, including lectures and study group meetings
for each. For each day and start time, you store the duration of the meeting, the site that you use
for the meeting, direct url and additional notes.
Exams stores for each class, the date, start time, site name, direct url and any additional notes.
Examname are values like exam1, exam2, final.
Teaches stores all instructors for a course and the email that they use for that course. We also
added a note so that you can note all weird habits and requests of each instructor.
OfficeHours for each class are stored for each day of the week ('Monday','Tuesday',..), start
time, duration, site and username to go for them.
Resources is the most useful relation in the database. It stores where you go for each different
part of a class (hw, exercise, discussions). You store which site to go to, which username for that
site to use and the direct URL for it.
Note: All date fields are formatted as mon-day-year, e.g. 01-31-2020. You can assume that you
can check if a date value X comes after another value Y by checking whether X > Y. Direct URLs
link to the specific URL for a specific site. For example, Submitty is a site but the direct URL for
this course on Submitty is different than the direct URL for another course.
1
Question 1. Write the following queries using relational algebra. You may use any valid relational
algebra expression, break into multiple steps as needed. However, please make sure that your
answers are well-formatted and are easily readable. Also, pay attention to the attributes required
in the output!
(a) Return classname, examname and examdate of all exams with examdate after November 1st
of 2020 that the instructor named Fogg is giving.
(b) Return classcode, dayofweek and startime of class meetings that conflict with an office
hour for the same class (i.e. start at the same time on the same day).
(c) Return classcode, classname of all classes that meet on mondays (dayofweek).
(d) Return the username and best browser for all sites to be used for hw in a class taught in Fall
2000 (classes.semester, classes.year).
(e) Find the sitename of all sites used in at least one class for class meetings, exams, office hours
or other resources.
(f) Find the name of a pair of courses that have no sites in common for any activity, i.e. class
meetings, exams, office hours or other resources.
(g) Find the code and name of courses with a single instructor in the database.
(h) Return the course code of all courses in Fall 2000 (classes.semester, classes.year) with
at least one office hours that are not on Monday and not on Wednesday. Return also the start
time and duration for each office hour.
Question 2. For the following relations, (a) find and list the keys, (b) check whether they satisfy
BCNF, discuss why or why not, (c) check whether they satisfy 3NF, discuss why or why not.
To show that a relation is not in BCNF or 3NF, you only need to show a violation. To show that
they are in BCNF or 3NF, check each functional dependency and discuss why it is ok.
1. R1(A, B, C, D, E, F), F = {AC → DE, BD → F}
2. R2(A, B, C, D, E, F), F = {ABC → DEF, AB → A, BCD → AEF}
3. R3(A, B, C, D, E, F), F = {ABC → DE, BC → AF}
4. R4(A, B, C, D, E, F), F = {ABC → DEF, BD → A}
SUBMISSION INSTRUCTIONS. Submit a PDF document or a Text document for this homework using Submitty. No other format and no hand written homeworks please. No late submissions
will be allowed.
If the Submitty for homework submissions is not immediately available, we will announce it on
Teams when it becomes available.
Help with relational algebra formatting. While in class I have been using a text version of
relational algebra, which I have allowed for many years for students who do not want to figure
out the Greek symbols. However, many past solutions use the more standard version with Greek
symbols. You can use either one in your solutions, but do not mix and match. Use one consistently.
I present you with the full syntax here in both ways (as well as the Latex symbols for it). Note
that for the standard version, I simply use the Math mode in Latex.
2
Operation Text Version Standard Version
Set Union R union S R ∪ S
Set Intersection R intersect S R ∩ S
Set Difference R - S R − S
Rename T(A,B,C) = R ρT(A,B,C)
(R)
Select select {C} (R) σC(R)
Project project {A1,..,An} (R) πA1,..,An(R)
Cartesian product R x S R × S
Theta-Join R join {C} S R ./C S
Natural Join R * S R ∗ S
As an additional help, I format one of the queries we did in class in the standard format below for
two equivalent solutions.
Query: Find name of Marvel heroes who have starred in a movie
Solution 1 (text format):
T1(hid1, mid1) = HeroInMovie
Result = project {hero} (select {hid=hid1} (MarvelHeroes x T1))
Solution 1 (in standard format):
T1(hid1, mid1) = HeroInMovie
Result = πhero(σhid=hid1 (M arvelHeroes × T1))
Solution 2 (text format):
T1(hid1, mid1) = HeroInMovie
Result = project {hero} (MarvelHeroes join {hid=hid1} T1))
Solution 2 (in standard format):
T1(hid1, mid1) = HeroInMovie
Result = πhero(M arvelHeroes ./hid=hid1 T1))
3