Download Midterm Examination - Database Systems | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Spring 2009, Prof. Chang
Department of Computer Science
University of Illinois at Urbana-Champaign
Midterm Examination
March 3, 2009
Time Limit: 75 minutes
- Print your name and NetID below. In addition, print your NetID in the upper right
corner of every page.
Name: NetID:
- Including this cover page, this exam booklet contains 10 pages. Check if you have
missing pages.
- The exam is closed book and closed notes. No calculators or other electronic devices
are permitted. Any form of cheating on the examination will result in a zero grade.
- Please write your solutions in the spaces provided on the exam. You may use the blank
areas and backs of the exam pages for scratch work. Please do not use any additional
scratch paper.
- Please make your answers clear and succinct; you will lose credit for verbose, convo-
luted, or confusing answers. Simplicity does count!
- Generally, we think one minute per point is a reasonable allocation of time; so plan
your time accordingly. You should look through the entire exam before getting started,
to plan your strategy.
Problem 1 2 3 4 5 6 7 8 Total
Points 11 16 17 12 14 70
Score
Grader
Problem 1 (11 points) Misc. Concepts
For each of the following statements, indicate whether it is TRUE or FALSE by circling your choice. You will get 1 point for each correct answer, -0.5 point for each incorrect answer, and 0 point for each answer left blank.
(1) Answer: T rue F alse Once the schema of a database is set, it cannot be changed.
(2) Answer: T rue F alse As a query language, SQL is more declarative than relational algebra.
(3) Answer: T rue F alse Every relationship in an E-R diagram must translate to an individual relation in the relational model.
(4) Answer: T rue F alse In a table, there is exactly one key, but there can be multiple superkeys.
(5) Answer: T rue F alse The natural join of two relations R(A, B) and S(C, D), which have no common attributes, is equivalent to their Cartesian product.
(6) Answer: T rue F alse Relational algebra can manipulate the columns and rows of a table in symmetric ways; i.e., whatever you can do on the columns, you can do on the rows, and vice versa.
(7) Answer: T rue F alse In SQL, any condition (in the where clause) involving Null values is treated as False.
(8) Answer: T rue F alse There always exists a unique BCNF decomposition for any given table.
(9) Answer: T rue F alse Given the snapshot of a table as below, we are certain that AB ā D is an F.D. of the table.
A B C D
1 3 2 2 2 3 2 4 3 1 3 6 3 1 1 6
(10) Answer: T rue F alse The purpose of normalization, in general, is to eliminate all functional dependencies.
(11) Answer: T rue F alse The relational model has been adopted since 1950ās for the first generation of database sys- tems.
(b) Convert the above ER diagram into a relational schema. Merge relations where appropriate. Your solution should have as few relations as possible, but they do not need to be normalized. Specify the key of each relation in your schema. (6 points)
Problem 3 (17 points) Relational Schema Design
Consider the following ER diagram, which describes graduate students (Grad ) and courses (Course) they serve as TAs.
(a) For each of the following statements, write a functional dependency (F.D.) that best captures the statement. (8 points)
(F 1) The studentID of each graduate student uniquely identifies the student. (F 2) No two offices have the same phone number (officePhone). (F 3) No two courses have the same courseID. (F 4) If two courses have the same course name, their course descriptions are the same.
(c) Suppose the F.D.s you specified are true. Translate this diagram into a relational schema in BCNF. Show each step of the decomposition process. (5 points)
Problem 4 (12 points) Relational Algebra
A small group has instituted a betting pool for University of Illinois football games. Bettors are given even odds (i.e., if you bet on Illinois to win, and Illinois wins, then you win the amount that you bet. If Illinois loses, you have to pay the amount you bet). There are two relations in the database for this activity. Note that each game is named, by the game attribute, with the university that Illinois played against; e.g., game = āMichiganā means the game of Illinois vs. Michigan. Also note that not all the games have been played yet: the LSU, Arizona, Tennessee, and Auburn games have not yet taken place.
- Out(game, outcome), which lists, for each game played so far, whether Illinois won the game or not. Note that āWā means that Illinois won, and āLā means that Illinois lost.
- Bets(who, amt, outcome, game), which keeps track of who bets how much on which game for which team to win. Note that āWā means that the bettor is betting that Illinois will win, and āLā means that the bettor is betting that Illinois will lose.
For the Out relation, the game attribute is the only key. For the Bets relation, the attributes who, game, and outcome together form the only key, which means that a bettor can make only one bet on each game for each outcome. Example instances of these two relations:
Out Relation Game Outcome Michigan W Yale L Georgia W USC L Notre Dame W Kansas L
Bets Relation Who Amt Outcome Game Liu 100 W Michigan Liu 20 W Yale Liu 30 W Georgia Liu 40 W USC Winslett 20 L Michigan Winslett 20 L Georgia Winslett 150 W Notre Dame Jones 250 W USC Harandi 110 L USC Reed 5 W USC Reed 5 L USC Reed 5 W Tennessee Reed 5 L Tennessee Campbell 100 L Georgia Campbell 100 W Michigan Campbell 100 W Yale Ng 100 W Georgia Ng 5 L LSU Kale 5 L Auburn
Problem 5 (14 points) SQL
Consider the same schema as Problem 5. Answer the following queries using SQL. Your queries should work for any instance of the database, not just this one. You do not need to remove duplicates in your results.
(a) List all the games that Illinois lost. (4 points)
(b) List the names of people who bet on the game between Illinois and Michigan but did not bet on the game between Illinois and LSU. (5 points)
(c) List all the games that at least one of its bet amount(s) is greater than all the amounts bet on Michigan. (5 points)
CS411 Database Systems Spring 2009, Midterm Exam Solutions
Problem 1 (11 points) Misc. Concepts
(1) F alse
(2) T rue
(3) F alse
(4) F alse
(5) T rue
(6) F alse
(7) F alse
(8) F alse
(9) F alse
(10) F alse
(11) F alse
Problem 2 (16 points) ER Model and Relational Model
(a) There can be multiple solutions for this problem, depending on studentsā assumptions. If the assumptions are reasonable and the ER diagram is drawn correctly based on the assumptions, we will give full credit. E-R diagram:
Assumptions:
- Grad relation: Grad 1 =(officePhone, office), Grad 2 =(studentID, officePhone, email, name, courseID) Possible solutions: If you did not merge TA, then the solution should be
- Course relation: Course 1 =(name, description), Course 2 =(courseID, name, location)
- Grad relation: Grad 1 =(officePhone, office), Grad 2 =(studentID, officePhone, email, name)
- TA relation: T A=(studentID, courseID)
Problem 4 (12 points) Relational Algebra
(a) ĻW hoBET S - ĻW ho(ĻGame<>ā²M ichiganā² BET S)
(b) ĻW ho(ĻGame=ā²M ichiganā² BET S) - ĻBET S 1 .W ho(BET S 1 ./C BET S2), where C = (BET S 1 .Game =ā²^ M ichiganā²) and (BET S 2 .Game =ā²^ M ichiganā²) and (BET S 1 .Amt > BET S 2 .Amt) and (BET S 1 .W ho <> BET S 2 .W ho)
Problem 5 (14 points) SQL
(a) select Game from OUT where Outcome=āLā
(b) There are different ways for solving this problem:
- (select Who from BET where Game=āMichiganā) except (select Who from BET where Game=āLSUā)
- (select Who from BET where Game=āMichiganā) intersect (select Who from BET where Game<>āLSUā)
- (select B1.Who from BET B1 where B1.Game=āMichiganā and not exists (select * from BET where Game=āLSUā and Who = B1.Who)
- (select Who from BET where B1.Game=āMichiganā and Who not in (select Who from BET where Game=āLSUā)
(c) select Game from BET where Amt > ALL ( select Amt form BET where Game=āMichiganā) or, you can also use max: select Game from BET where Amt > ( select max(Amt) form BET where Game=Michigan)
