For Problem 1.4, do we need to worry about the possibility that a given professor could teach the same course in two different semesters?
No, the ER diagram doesn’t actually allow for this possibility.
When you have a relationship set in an ER diagram, a given combination of entities from the connected entity sets can appear at most once, regardless of the constraints that are imposed on the relationship set. In this case, a given combination of professor and course can appear at most once in the Teaches relationship, and thus a professor cannot teach the same course in two different semesters.
In answering problem 1.4, you need to decide whether the proposed schema for Teaches makes sense in light of the ER diagram from problem 1.1. – the one in which you added the constraint that every professor teaches at least one course. In particular, you need to decide whether, given that added contraint, we need the primary key of Teaches to be the combination of the attributes professor and course, or should we limit the primary key to be just one of those two attributes?
(By the way, if we did want to allow a given professor to teach the same course in more than one semester, we would need to revise the ER model so that (1) Semester is its own entity set and (2) Teaches is a ternary relationship set that connects three entity sets: Professor, Course and Semester.)
For Problem 2.3, I’m uncertain how to go about transforming the ER diagram into a relational schema. Do you have any suggestions?
Start by reviewing the slides on pages 26-33 of the coursepack. They outline the basic procedures for transforming an ER diagram into a relational schema, as well as a number of special cases that can affect how many tables you need and what their primary keys should be. In addition, pages 33-34 discuss foreign keys.
Finally, in Section 1, we derived a relational schema from the ER diagram for the airline information database, and we recommend reviewing the solutions for that task – which you find integrated into the section page itself.
In section, we only listed the schema of the tables and their primary keys, but we have updated the solutions to also include the foreign keys and their associated referential-integrity constraints.
For Problem 2.3, do we need to worry about capturing participation constraints?
No. It typically isn’t possible to capture participation constraints when converting an ER diagram to a relational schema.
However, you should include a description of any participation constraints in your answer to problem 2.2.
In SQL, we specify an explicit join condition for an outer join. Are we allowed to do that in relational algebra?
Yes. In the same way that you can do a regular theta join that includes an explicit join condition, you can also do an outer join with an explicit join condition.
For some of the problems, it seems like we could either use a
SELECT command with multiple tables in the FROM clause (i.e., a join
of some sort) or a SELECT command with one or more subquery. Do you
have a preference about which approach we use?
For the purposes of PS 1, we would recommend using multiple tables
in the FROM clause whenever possible so that you can get the
necessary practice with this type of query. However, there may be
one or more problems for which a subquery is unavoidable, and you
should of course use one in such cases.
I’m having trouble with one of the problems. Do you have any suggestions on how I should proceed?
First, make sure that you are following our rules of thumb for writing queries.
Next, try looking at the example queries in the notes and sections for queries that solve a problem that is similar to the one that you’re stuck on, and use those queries as models. The lecture notes include two sets of SQL practice problems, and we’ve posted solutions to them on the Lectures page of the course website.
Also, when writing join conditions, don’t forget that these are based on the foreign keys and primary keys in the relevant tables. Consult the schema of the tables to remind yourself of what the keys are.
For one of my queries, it seems like I am getting too many results. Do you have any suggestions?
Make sure that you have enough join conditions (for N tables, you
need N-1 join conditions), and that each table in the FROM clause
is part of at least one of the join conditions.
I’m not getting any results at all for one of my queries, and I don’t understand why. Do you have any suggestions?
If you are performing pattern matching, make sure that you use the
LIKE operator, rather than =. Also, make sure that you include the
necessary wildcard characters (% and/or _).
SQLite seems to allow me to combine a column name with an aggregate in the SELECT clause – even when I’m not grouping by that column. Is it okay if I do this in one of my answers?
No! You are correct that SQLite allows this, but it is not standard SQL. You should write a query that follows the rules for SQL mentioned in the lecture notes, so that it would work on any relational DBMS. In particular, you may want to consider using a subquery to obtain the result that you’re looking for.
I’m having trouble with problem 12. Do you have any additional hints?
A good model for this problem is problem 5 from the practice problems at the end of the SQL lecture notes – the one in which we were looking for all students who are not enrolled in a course. In that case, we used a subquery to find the ids of students who are enrolled in a course, and then we used the NOT IN operator to find students whose ids were not in the result of the subquery. You will need to do something similar here. Another good model is question 2 from the extra-practice problems.
I’m having trouble with problem 13. Do you have any additional hints?
One possible model for this problem is found in the lecture notes on
LEFT OUTER JOIN. In those notes, we considered the problem of
finding the ID and the major(s) of every student enrolled in a
course, including enrolled students with no major. In order to
include students without a major, we needed to use a LEFT OUTER
JOIN, so that students in the Enrolled table who didn’t appear in
the MajorsIn table would still be included in the result. You need
to do something similar in problem 13 to ensure that actors from
Australia that have not won any of the Oscars in the database are still
included in the result.
Last updated on September 16, 2024.