CMPSC 431W Database Management System Mar. 8, 2021
Midterm Exam
Time: 6:15 – 7:30 PM EST, Mar 8, 2021
Part I: T/F Question [20pts]
Indicate whether each of the following statement is true or false, and explain your answer briefly in one or
two sentences.
1. It is usually faster to find data satisfying some particular properties in a large dataset when it are stored
in file system rather than a database.
2. The user of a DBMS can query data without knowing the physical data layout.
3. All attributes of a relation is always a super key of that relation.
4. The SQL query “SELECT first name from Customers” will return all distinct first names of customers.
5. According the following ER diagram, a sailor can reserve the same boat on two different days.
Part II: ER Modeling & Relation Model [30pts]
Considering the following specification for an application:
A football league has a number of teams, and each team has a number of players. Each team has a unique
name. For each team, we also need to store its city, home arena, number of fans, and total budget. Each
player has a unique pid, and also an SSN. We also need to store the first name, last name and salary of
each player. A player must play for exactly one team. Each team plays a number of matches on different
dates. Each match must involves two teams where one is home and one is away. Any two teams will only
play against each other once. We need to store the date and score of each match. Each player may have a
manager. For each manager, we need to store their first name, last name, SSN, and salary. Besides, each
manager has to manage at least one of the players.
1. Draw the ER diagram for the specification above. [10pts]
2. Convert your ER diagram to a relational database schema. [10pts]
Note that you DO NOT have to show the CREATE TABLE statement. You do have to provide types
for each attribute. In addition, you have to show the primary key and foreign keys of each relation.
Finally, you have to combine relations based on key constraints if you have to.
1
CMPSC 431W Database Management System Mar. 8, 2021
3. What’s the super keys of the relation built for teams. [5pts]
4. What additional sets of attributes can be made primary key on relations for players and teams if the
following statement is true: [5pts]
Each team locates at a different city, and no two teams have the same number of fans. No two players
with the same name have the same salary amount.
Part III: Formal Query Language [25pts]
Consider the following database schema for this part.
Customer(cname, city), Dealership(dname, city)
Car(make, model, mpg), Visit(cname, dname, timers per year)
Likes(cname, make, model), Serves(dname, make, model, price)
Answer the following questions in Relational Algebra:
1. Find the names of all customers from State College who likes “Honda Civic”, but never visited a
dealership serving this car model. [6pts]
2. Find the names of customers who like all the cars that John does not like and also like at least one car
that John likes. [7pts]
Answer the following questions in Relation Calculus:
3. Find names of all cities which has a dealership in it that sells “Toyota Camry” with a price less than
$20,000. [6pts]
4. For each dealership, return its name and the customer name(s) who has visited it the most number of
times a year. [6pts]
Part IV: SQL [25pts]
Consider the following database schema for this part.
student(sid, sname, gender, age, year, gpa), dept(dname, n phds)
prof(pname, dname), course(cno, dname, cname)
major in(dname, sid), section(dname, cno, sectno, pname)
enroll(sid, dname, cno, sectno, grade)
Write SQL queries for the following questions:
1. Show the pname of professors who are teaching more than 3 different courses. [6pts]
2
CMPSC 431W Database Management System Mar. 8, 2021
2. Show the total number of phd students for all departments that have more than 100 majors (i.e.,
department with more than 100 students majoring in that department). [6pts]
3. Find the name of the youngest student who have average grade> 85 (computed from all course he/she
has enrolled into). [6pts]
4. Find the pname of professors who have taught all CSE department courses that have more than 30
students enrolled into. [7pts]
3

欢迎咨询51作业君