cs411-sp10-mt-modified - NetlD Problem 1(36 points Misc...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 4
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 6
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 8
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: NetlD: Problem 1 (36 points) Misc. Concepts For each of the following statements: a for true/false choices, indicate whether it is TRUE or FALSE by circling your choice, and provide an explanation to justify; o for short answer questions, provide a brief answer with clear explanation. You will get 3p0int for each correct answer with correct explanations, and no penalty (of negative points) for wrong answers. (1) Answer: True False An E-R diagram with m entities and n relationships will translate to m+n tables. => Explain: (2) Answer: True False A table with two attributes, such as R(A, B), must be in BCNF. => Explain: (3) Answer: True False An SQL query can often be translated into multiple relational algebra expressions. => Eavplain: (4) Give a relation that is in 3NF but not in BCNF. : Answerngplain: (5) Answer: True False In relational algebra, join is a derived operator. => Emplain: NetID: (6) Answer: True False Schema normalization often contributes to enhancement of query processing efficiency. => Explain: (7) Answer: True False For relation R(A,B,C), if A is a key, then the decomposition into R(A, B) and S (A, C) is lossless. : Explain: (8) Answer: True False In the following relation R(A, B, C, D), the FD. AC —> B is not violated. A B C D 5 1 3 2 2 ; 2 3 2 4 3 1 3 6 3 1 1 12 : Explain: (9) In the above relation R(A, B, C, D), what are possible keys of the table? : Answeré’fiExplain: (10) Provide a lossless—join decomposition of the above relation R(A, B , C, D) (into to two rela— tions). If there is no such decomposition, explain Why. => Answeré’fl’Explain: PJetII): (11) For the above relation R(A, B, C, D), write a relational algebra expression to return the lowest value of D. => AnswerEdEmplain: (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) => AnsweerExplam: NetID: Problem 2 (28 points) Database Design You have been asked to design a database for the university administration, which records the following information: 1. 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. 2. Each graduate student has an advisor. 3. Each undergraduate student has a major. 4. Students take courses. A student may take one course, multiple courses, or no courses. 5. Each course has a course number, course name, and days of the week the course is scheduled. 6. Each course has exactly one head TA, who is a graduate student. 7. 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) NetID: (b) Translate your ER diagram into a relational schema. Select approaches that yield the fewest number of relations; merge relations Where appropriate. Specify the key of each relation in your schema. (12 points) NetlD: 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: 0 Request(Tz’me, UserI D, Query, Results), which lists the time, the userID, the query he/she requested, the number of search results for one search process. 0 Click(Tz‘me, U serI D, Query, URL), 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 (Time, U serI D) 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 2010—02—12 19:00:00 U001 NBC 2010—02—12 19:01:00 U001 NBC Olympics 2010-02-12 18:54:00 U002 NBC 2010 2010-02-13 09:00:05 U001 Olympics wiki 2010-02—15 19:27:08 U003 Olympics 2010 medals 2010—02—16 13:24:45 U004 NBC Olympics 2010—02-16 13:25:55 U004 Vancouver 2010 2010—02—16 17:11:56 U002 NBC 2010 2010—02—20 20:13:45 U005 Olympics ski L2010—02—20 20:45:34 U005 Olympics Austria medals Results Click Relation QueryID UserID Query 1 URL 2010-02—12 19:01:06 U001 NBC Olympics www.nbcolympics.com 2010—02-12 19:01:34 U001 NBC Olympics www.mbcolympicscom/ 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.00m/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_Olympic 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. [ NetID: 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 difierent users. (10 points) Problem 4 (SQL) Consider the same schema as Problem 3. Answer the following queiries using SQL. Your statements should work for any instance of the database, not just this one. You do not need to remove duplications in your results. (a) For any clicked URL containing www.nbcolympics.com, what was the Query that provided the URL, and how many results were returned with that query? (b) List all Userle of anyone who searched for the term ‘Olympics’ and was returned more than 30 results. ...
View Full Document

Page1 / 8

cs411-sp10-mt-modified - NetlD Problem 1(36 points Misc...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online