Download Midterm Exam Questions for Solution - Database Systems | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Spring 2010, Prof. Chang
Department of Computer Science
University of Illinois at Urbana-Champaign
Midterm Examination
March 2, 2010
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 9 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 Total
Points 36 28 18 18 100
Score
Grader
Problem 1 (36 points) Misc. Concepts
For each of the following statements:
- for true/false choices, indicate whether it is TRUE or FALSE by circling your choice, and provide an explanation to justify;
- for short answer questions, provide a brief answer with clear explanation.
You will get 3point for each correct answer with correct explanations, and no penalty (of negative points) for wrong answers.
(1) Answer: T rue F alse An E-R diagram with m entities and n relationships will translate to m+n tables.
⇒ Explain:
(2) Answer: T rue F alse A table with two attributes, such as R(A, B), must be in BCNF.
⇒ Explain:
(3) Answer: T rue F alse An SQL query can often be translated into multiple relational algebra expressions.
⇒ Explain:
(4) Give a relation that is in 3NF but not in BCNF.
⇒ Answer&Explain:
(5) Answer: T rue F alse In relational algebra, join is a derived operator.
⇒ Explain:
(11) For the above relation R(A, B, C, D), write a relational algebra expression to return the lowest value of D.
⇒ Answer&Explain:
(12) What is the result of the following query, for the above relation R(A, B, C, D)?
select A, B from R where C > (select D from R where A = 3)
⇒ Answer&Explain:
Problem 2 (28 points) Database Design
You have been asked to design a database for the university administration, which records the following information:
- All students necessarily have a unique student ID, a name, and a university email address. Each student is also either an undergraduate or a graduate student.
- Each graduate student has an advisor.
- Each undergraduate student has a major.
- Students take courses. A student may take one course, multiple courses, or no courses.
- Each course has a course number, course name, and days of the week the course is scheduled.
- Each course has exactly one head TA, who is a graduate student.
- Every head TA has an office where he or she holds office hours.
(a) Draw an ER diagram for this application. Be sure to mark the multiplicity of each relationship of the diagram. Decide the key attributes and identify them on the diagram. Please state all assumptions you make in your answers. (16 points)
Problem 3 (18 points) Relational Algebra
Nowadays, web search engine is widely used by people all over the world to find useful information. To analyze users’ search behaviors, a query log stores the history of users’ queries and clicked URLs.
There are two relations in a query log:
- Request(T ime, U serID, Query, Results), which lists the time, the userID, the query he/she requested, the number of search results for one search process.
- Click(T ime, U serID, Query, U RL), which lists the time, the userID, the query he/she re- quested and the URL clicked on after request this query.
For both Request and Click relation, the attributes (T ime, U serID) forms the only key which implies one user can request only one query or click on only one URL each time.
Example instances of these two relations are given here:
Request Relation Time UserID Query Results 2010-02-12 19:00:00 U001 NBC 100 2010-02-12 19:01:00 U001 NBC Olympics 50 2010-02-12 18:54:00 U002 NBC 2010 80 2010-02-13 09:00:05 U001 Olympics wiki 70 2010-02-15 19:27:08 U003 Olympics 2010 medals 5 2010-02-16 13:24:45 U004 NBC Olympics 50 2010-02-16 13:25:55 U004 Vancouver 2010 95 2010-02-16 17:11:56 U002 NBC 2010 80 2010-02-20 20:13:45 U005 Olympics ski 54 2010-02-20 20:45:34 U005 Olympics Austria medals 7
Click Relation QueryID UserID Query URL 2010-02-12 19:01:06 U001 NBC Olympics www.nbcolympics.com 2010-02-12 19:01:34 U001 NBC Olympics www.nbcolympics.com/video 2010-02-12 18:54:01 U002 NBC 2010 www.nbcolympics.com 2010-02-15 19:27:22 U003 Olympics 2010 medals www.vancouver2010.com 2010-02-15 19:29:01 U003 Olympics 2010 medals www.vancouver2010.com/olympic-medals/ 2010-02-16 13:25:58 U004 Vancouver 2010 www.vancouver2010.com 2010-02-16 17:12:56 U002 NBC 2010 www.nbcolympics.com 2010-02-20 20:14:00 U005 Olympics ski www.usskiteam.com 2010-02-20 20:45:40 U005 Olympics Austria medals en.wikipedia.org/wiki/Austria at the Olympics 2010-02-20 20:45:50 U005 Olympics Austria medals www.vancouver2010.com
Note that not all the queries have corresponding clicked URLs since a user might not be interested in any returned URL. Sometimes user may click on multiple URLs using one query.
Though different from the actual case, you can assume that the number of returned search results associated with the same query doesn’t change no matter when and who request this query.
Answer the following queries using relational algebra. Your answer should work for any instance of the database, not just this one.
(a) List all the queries that do not have any corresponding URL. (8 points)
(b) List all the queries that have been requested by different users. (10 points)
