Download Solved Problems for Midterm Exam - Database Systems | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Fall 2005, Prof. Chang
Department of Computer Science
University of Illinois at Urbana-Champaign
Midterm Examination
October 28, 2005
Time Limit: 90 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 8 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 20 14 26 20 80
Score
Grader
Problem 1 (20 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) T rue F alse The relational database was pioneered by Dr. Ted Codd for not only the proposal of its data model but also one of its early implementations.
(2) T rue F alse Although the relational model dominates today’s database software, there are still non- relational DBMS’s on the market– e.g., the IMS system from IBM.
(3) T rue F alse The early implementations of the relational model started with the famous System R at IBM San Jose Lab (today’s Almaden) and INGRES from Stanford University.
(4) T rue F alse The SQL language was the result of the query language developed in the INGRES project.
(5) T rue F alse The E-R model was first invented in the early 1970’s, which was then followed by the more complete concept of the relational model.
(6) T rue F alse As the default, every query construct in SQL operates with the bag semantics.
(7) T rue F alse Functional dependency A → B is a special case of multivalued dependency A ։ B.
(8) T rue F alse An E-R diagram with 2 entities and 1 relationship must be translated to a relational schema with at least 3 relations.
(9) T rue F alse A schema in 4NF will also be in BCNF.
(10) T rue F alse The relational model is more complete than E-R, in the sense that the relational model has not only a data model but also an algebra for data computation.
Problem 2 (14 points) ER and Schema Design
Consider a relation R(A, B, C, D), with FD’s AB → C, BC → D, CD → A.
(a) Find the closure of AB. (3 points)
(b) Is R a good schema? Explain why or why not. (5 points)
(c) We are considering to decompose R as R1 (A, B, C) and R2 (A, C, D). Is this a good decompo- sition? If your answer is yes, explain why. If your answer is no, give a decomposition that is good; also explain why. (6 points)
Problem 3 (26 points) Query Language
Suppose we use a database to store all the pages on the Web. We decide to use the following schema, with two tables:
- Page(URL, title, content), in which each tuple stores the URL, title, and content of a Web page.
- HyperLink (URL, targetURL), in which each tuple stores a hyperlink from URL to targetURL; e.g., a tuple (’www.ece.uiuc.edu’, ’www.cs.uiuc.edu’) indicates there is a link from our ECE page to CS page.
We ask you to write queries. Please write simple and non-redundant queries – Note that we will really check if your answers are unnecessarily complex.
(a) In relational algebra, write a query to return all the page URLs that are “isolated,” i.e., not connected from any other pages. (6 points)
(b) In relational algebra, can you write a query to return all the page URLs that are reachable from www.cs.uiuc.edu? A page Y is reachable from page X if there exists a path (of links) from X to Y. If yes, give such a query. Otherwise, explain why not. (6 points)
Problem 4 (20 points) Views; Constraints and Triggers
Consider our typical “drinker” database with the following relations.
Drinker (drinker, age, address) Like(drinker, beer) Beer (beer, manufacturer) Bar (bar, owner, address) Frequent(drinker, bar) Sell (bar, beer, price)
(a) Using SQL, create a view LuckyDrinker (drinker, bar), to record those drinkers who can find all the beers he likes in a bar that he frequents. (6 points)
(b) The view LuckyDrinker is, by default, not updatable. Explain why it is not updatable. ( 3 points)
(c) Although the view is not updatable, we can write a trigger to implement the desirable update behavior. Let’s consider insertion in particular:
INSERT INTO LuckyDrinker VALUES(d, b), where d and b are values for some drinker and bar respectively.
(1) Suggest a desirable behavior that you think is appropriate to implement such insertions. (3 points)
(2) Write a trigger to implement the insertion behavior as you suggested above. (8 points)
Syntax hint: An SQL trigger has the following syntactic structure: CREATE TRIGGER <trigger_name> ... REFERENCING ... AS ... [FOR EACH ROW] WHEN BEGIN END
GROUP BY URL ) AS T WHERE T1.targetURL=T2.URL;
Problem 4 (a)
CREATE VIEW LuckyDrinker(drinker, bar) AS
SELECT * FROM Frequent WHERE NOT EXISTS ( SELECT beer FROM Like WHERE Frequent.drinker=Like.drinker EXCEPT SELECT beer FROM Sell WHERE Frequent.bar=Sell.bar );
(b) As a view, the information in LuckyDrinker depends on the table F requent, Like, Sell and should be updated automatically when the underlying tables change. For example, if we remove (”M ike”, ”pub”) from LuckyDrinker, we don’t know how to update the underlying tables. There can be many reasons for the result that (”M ike”, ”pub”) doesn’t belong to LuckyDrinker anymore. It is possible that M ike doesn’t frequently drink in ”pub” anymore; it is also possible that M ike now likes to drink ”Tsingtao” which is not available in ”pub” yet; and so on.
(c) (1) One possible solution is the following. Instead of insert into the view, change the underlying tables so that the view will be automatically updated.
- If (d, b) is not in F requent, insert (d, b) into F requent.
- Insert (b, beer, N U LL) into Sell if beer is not sold in the bar b but is liked by the drinker d. (2) We can use trigger to handle it.
CREATE TRIGGER drinkerTrigger INSTEAD OF INSERT ON LuckyDrinker REFERENCING NEW ROW AS NewTuple FOR EACH ROW BEGIN INSERT INTO Frequent VALUES(d,b) SELECT NewTuple.d, NewTuple.b WHERE (NewTuple.d, NewTuple.b) NOT IN FREQUENT; INSERT INTO Sell (bar, beer, price) SELECT NewTuple.b, Like.beer, NULL FROM Like WHERE Like.drinker = NewTuple.d AND Like.beer NOT IN (SELECT beer FROM Sell WHERE bar = NewTuple.b); END
