Download 5 Problems on Database Systems - Midterm Exam | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
Name: Student ID:
MIDTERM EXAM, October 21, 2003
CS 311 Introduction to Database Management Systems Department of Computer Science University of Illinois at Urbana-Champaign
Exam Rules:
1) Close book and notes, 75 minutes
2) Please write down your name and student ID number NOW.
3) Please wait until being told to start reading and working on the exam.
4) No question can be asked during the exam (due to departmental regulations, to
be fair to off-campus students). If you think a problem is ambiguous, write
down your assumptions, argue that they are reasonable, then work on the
problem using those assumptions.
Scores:
Problem 1: out of 24 points
Problem 2: out of 20
Problem 3: out of 20
Problem 4: out of 20
Problem 5: out of 16
Total: out of 100 points
Name:
1. (24 points; 2 points each) Multiple choice questions: For each of the following statements or questions, circle the correct answer.
- Attributes in the ER model can only have atomic values. (a) true (b) false
- A multiway ER relationship can always be converted into a set of binary relationships. (a) true (b) false
- In the ER model, referential integrity constraint implies that at most one value exists in a given role. (a) true (b) false
- Updates to a relational database schema is painful; the main reason is because SQL does not provide good facilities to carry out such updates. (a) true (b) false
- Given two entity sets E and F, and a relationship R between them, when translating it is OK to combine the table of R with the table of E, if relationship R is many-one from entity set E to entity set F. (a) true (b) false
- If an is-a hierarchy has a total of three entity sets, then translating the entire is-a hierarchy into relational tables using the ER method would result in (a) 3 tables (b) 4 tables
- The six basic operations of relational algebra are: union, difference, intersection, selection, projection, and Cartesian product. (a) true (b) false
- A natural join is a special case of an equi-join. (a) true (b) false
- The default semantics of SQL queries is always bag semantics. (a) true (b) false
- If a value in a table is NULL, it means that that value is missing. (a) true (b) false
- A query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE or UNKNOWN. (a) true (b) false
- The attribute-based check condition CHECK (beer IN (SELECT name FROM Beers)) will be checked if a beer is deleted from table Beers. (a) true (b) false
3. (20 points) Schema Refinement:
Consider the following relation and dependencies: R(A, B, C, D, E, F), A B, A C, BC A, D EF, F E. Assume the above functional dependencies are the only ones that hold over R. Identify whether R is in BCNF or 3NF or neither. If R is not in BCNF, decompose R into a collection of BCNF relations. Please show each step of the decomposition process.
4. (20 points; 5 points each) Relational Algebra and SQL:
Consider the following database schema: Product(name, manf, category) manf = the manufacturer of the product Store(name, city) Sell(store-name, prod-name, price) Buy(buyer-name, prod-name, store-name, date, quantity)
(a) Write a query in relational algebra that returns the name and price of all products being sold at the stores where "John" has ever purchased some product. (For example, if "John" has ever bought a product at a store named BestBuy, then the result of executing the query should include the name and price of all products being sold at BestBuy.)
(b) Write a SQL query that returns the names of all pairs of "VCR" and "TV" products by the same manufacturer. Note that "VCR" and "TV" are product categories. (For example, suppose Sony manufactures three “VCR” products named VCR1, VCR2, and VCR3. Suppose Sony also manufactures two “TV” products named TV1 and TV2. Then the query result should include the pairs (VCR1, TV1), (VCR1, TV2), (VCR2, TV1), and so on.
5. (16 points; 8 points each) Views:
Consider the view:
CREATE VIEW Champaign-purchase AS SELECT buyer-name, prod-name , date FROM Store, Buy WHERE Store.city = "Champaign" AND Store.name = Buy.store-name
This view is defined over the relational schema of Problem 4.
(a) Is this view updateable? Please explain your answer.
(b) Suppose we add store-name to the SELECT clause of the above view, so now the view is
CREATE VIEW Champaign-purchase AS SELECT store-name, buyer-name, prod-name , date FROM Store, Buy WHERE Store.city = "Champaign" AND Store.name = Buy.store-name
Is this view updateable? Please explain your answer.
