Download Midterm Exam Questions - Database Systems | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Sprint 2007
Department of Computer Science
University of Illinois at Urbana-Champaign
Midterm Examination
March 13, 2006
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 13 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 Total
Points 10 11 15 16 28 20 100
Score
Grader
Turn over the page when instructed to do so.
Problem 1
For each of the following statements, choose ONE answer among A,B,C,D,E. You will get 2 points for each correct answer, 0 point for each incorrect answer.
(1) Which one of the following is TRUE for E/R modelling? A. Supporting relations are used to define class hierarchies. B. Only entities can have attributes. C. Many-to-one relations cannot be precisely represented in E/R diagrams. D. The key attributes of an entity can be on other entities. E. E/R models do not support class hierarchies of entities.
(2) Which one of the following is TRUE for schema normalization? A. All relations in 3NF also satisfy BCNF. B. 3NF decomposition is always dependency preserving. C. 4NF removes redundancies due to functional dependencies. D. ”For each Function Dependency, X → Y, X is a key or super key” is the definition for 3NF. E. Relations with only three attributes are automatically in BCNF.
(3) Which one of the following is TRUE for relational algebra? A. The projection operator may affect the number of tuples in set semantics. B. The rename operator may affect the number of tuples in bag semantics. C. Intersection is considered as one of the primitive operators. D. Join is considered as one of the primitive operators. E. Projection is only defined for bag semantics.
(4) In Relation Algebra, the join operator is equivalent to which one of the following? A. Selection operator followed by group operator. B. Disjoint operator followed by rename operator. C. Projection operator followed by selection operator. D. Selection operator followed by cartesian product operator. E. Cartesian product operator followed by selection operator.
(5) Which one of the following is true for SQL? A. The ’UNION’ operator uses bag semantics by default. B. The ’SELECT-FROM-WHERE’ clauses uses set semantics by default. C. The ’DISTINCT’ keyword is used to switch to bag semantics. D. The set/bag semantics is set before the SELECT statement. E. The default set/bag semantics is not defined by the SQL standard.
Problem 2 11 ER Diagram
Consider the following domain:
- Authors have a name and email.
- Manuscripts always have one primary author and zero or more co-authors.
- Manuscripts have a title, date and zero or one publishers.
- Books and conference papers are kinds of manuscripts.
- A book has an ISBN number.
- A conference paper has a conference name and year.
- Conference papers are always republished together as a single book and never published in more than one book.
- Publishers have a name and address.
(i) Complete the following E/R diagram in Figure 1, by adding the relationships between entities. Don’t forget to indicate multiplicity constraints.
(ii) Convert the domain into a relational schema with the fewest number of relations: Select an approach that yields the fewest number of relations. Merge relations where appropriate by considering multiplicity constraints. Your answer must clearly specify each of the final relations and the attributes of each relation. Your final schema must be faithful to the original problem specification above. You do not need to specify keys.
(iv) Decompose R using BCNF decomposition. Indicate your working and summarize your final set of relations.
(v) Determine if BCNF decomposition is dependency preserving. If it is not, determine which dependency has been lost by your decomposition.
Problem 4 16 Relational Algebra
For the following relation that describes second-hand (used) cars for sale:
Car(ownername, model name, price, mileage, year, color) P erson(name, city, phone)
Write relational algebra expressions for the following. Use only join, cross product, difference, rename project, and select operators. The Selection expression may evaluate a simple boolean expression for each tuple. e.g. = < > <> & 6 = but not group operations, e.g. min, max.
In each case your expression should return only the model name of the car(s).
(i) The model names of any cars for sale at less than $8000.
(ii) The model name(s) of red cars whose owners live in Urbana.
Problem 5 28 SQL Query
Consider the following relation that tracks caffeine beverage purchases purchased by credit card for a particular coffee chain: Card(cid, fullname, loyal) Purchase(pid, cid, location, date) PurchaseItem(bid, pid) Beverage(bid, name, price)
Note, cid,pid,bid is the primary key of the Card,Purchase and Beverage table respectively. ’loca- tion’ is the location of the coffee shop. Assume that each customer has one credit card and one credit card belongs to one customer.
(i) Write the SQL to create the Purchase table. Make reasonable assumptions about the type and size of each column. You do need to specify the primary key constraint but not any foreign key constraints
(ii) Write the SQL to create a view ’report1’ that reports the total number of purchases made at each location sorted by the number of purchases, in descending order.
(iii) Complete the following statement to add the constraint ’beverage price must be greater than zero’.
ALTER TABLE Beverage ADD ....
(iv) Write SQL to delete beverages with names that begin with ’test’ or are NULL.
(v) Write SQL to update location to ’101 State st’ for purchases made at ’100 State st’
(vi) Write SQL to insert a new Beverage named Joes’s Mocha (note the single quote) with id= priced at 3 dollars.
(iii) The name(s) of beverage(s) that have been sold to no more than 100 distinct customers.
(iv) The cid(s) of the credit card(s) that has purchased the most number of drinks.
END OF CS411 MIDTERM EXAM
