Download CS186 Database Systems Exam 2 - UC Berkeley, Spring 2008 and more Exams Introduction to Database Management Systems in PDF only on Docsity!
Class Account: ___________________________ UNIVERSITY OF CALIFORNIA Department of EECS, Computer Science Division CS186 Bohannon/Cooper Spring 2008 Second Exam: Introduction to Database Systems April 15, 2008 Instructions:
- Write your name on each page.
- Turn in your notes page with your test.
- There are 60 points total.
- Please read over the test and plan your time. Best to skip and go back if you are stuck on a question. The points assigned to each question reflect our estimate of the time to answer that question, so use the point allocations to plan your time. 1. SQL ( 18 points) Consider the following relations: ComicBooks(Title,PubDate,IssueNumber,Price) Publishers(Name,Address) PublishedBy(Title,PublisherName) Characters(Name,GoodOrEvil,Superpowers, Mentor) StarOf(CharacterName,BookTitle) AppearsIn(CharacterName,BookTitle,PubDate)
- Primary key of ComicBooks is (Title, Pubdate)
- Primary key of Publishers is (Name)
- Primary key of Characters is (Name)
- In PublishedBy, Title is a foreign key referencing ComicBooks(Title), and PublisherName is a foreign key referencing Publishers(Name)
- In Characters, Mentor is a foreign key referencing Characters(Name)
- In StarOf, CharacterName is a foreign key referencing Characters(Name), and BookTitle is a foreign key referencing ComicBooks(Title)
- In AppearsIn, (BookTitle,PubDate) is a foreign key referencing ComicBooks(Title,PubDate) and CharacterName is a foreign key referencing Characters(Name) In this question, excessively complex SQL will be penalized.
a. Write a SQL query to find the issue number of all comic books with title “Superman” that “Superwoman” appears in. b. Write a SQL query to find, for each character who appears in comic books with title “Batman” or “Dark Knight”, how many times they appeared altogether in those comic books.
2. Query processing (1 8 points) Consider the following schema: LibraryBooks(ISBN, Title, Author, Branch) Patrons(CardNumber, Name, Address, City, State, ZipCode) CheckedOut(CardNumber, ISBN, DueDate) a. The DBMS optimizer is considering two plans for the following query: SELECT Title, Author, Name FROM LibraryBooks, Patrons, CheckedOut WHERE LibraryBooks.ISBN = CheckedOut.ISBN AND Patrons.CardNumber = CheckedOut.CardNumber AND Branch=’Central’ AND DueDate>Now; Assume “Now” is a special value that always evaluates to, well, now. The two plans are: LibraryBooks CheckedOut Patrons NestedLoopJoin NestedLoopJoin Selection (DueDate>Now) Projection(Title,Author,Name) Selection (Branch=Central) Plan A LibraryBooks CheckedOut Patrons NestedLoopJoin NestedLoopJoin Selection (DueDate>Now) Projection(Title,Author,Name) Selection (Branch=Central) Plan B
a.1 Assuming you have no statistics and know nothing about the contents of each relation, which plan is likely to cost less? Why? a.2 Assume now that you have the following statistics:
- LibraryBooks contains 10,000,000 tuples
- There are 10 different values for the Branch attribute of LibraryBooks
- There are 100,000 Patrons tuples
- There are 10 0,000 CheckedOut tuples
- Only 5% of checked out books are overdue on average Is the same plan you chose in part a.1 likely to be the cheapest? Why or why not? a.3 Is a real cost-based optimizer likely to choose one of these two plans? Why or why not?
3. Functional dependencies and normalization ( 12 points) a. Consider a relation with the following schema and functional dependencies: Tests(PatientID, DoctorID, TestDate, Type, Lab, Technician, Fee, ResultDate, Status); PatientID, DoctorID → TestDate PatientID, TestDate, Type → Lab Technician → Lab Type → Fee PatientID, TestDate, Type → ResultDate, Status a.1 Is this relation in BCNF? Why or why not? b. Consider a relation with the following schema and functional dependencies: AstronomicalObjects(MessierNumber, CommonName, Coordinates, Type, Distance, Galaxy) MessierNumber → CommonName CommonName → Coordinates, Type MessierNumber → Distance CommonName → MessierNumber, Galaxy b.1 What are the candidate keys for this relation? Also, give at least one superkey.
b.2 Is this relation in BCNF? Is it in 3NF? Why or why not? c. Consider a relation with the following schema and functional dependencies: Recipes(Title, Genre, PreparationTime, Author, EstimatedCost) Title → Genre Title, Author → PreparationTime, EstimatedCost c.1 Which attributes of the relation are prime? Why? c.2 Is the relation in 3NF? Why or why not?
a. If we only create one index for UserProfiles, what should the index be on? Why? Should it be clustering? b. Should we create an index for Posts? If so, what attribute or attributes should it be on? If multiple attributes, what order should they be in? If we should not create an index, why not?
5. Transactions ( 5 points) An ACID transaction would permit the following situations (mark True or False for each): a. _____ After a failure, an uncommitted transaction is rolled back and all of its effects are erased. b. _____ After a failure, a committed transaction is rolled back and all of its effects are erased. c. _____ Two transactions update the same tuple and then commit, and the effects of both transactions are visible afterwards. d. _____ An unserializable schedule is executed. e. _____ A transaction reads the same tuple twice without writing it in between and sees two different values.
