Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

Transaction Management -Introduction to Database Systems - Exams, Exams of Introduction to Database Management Systems

Gujarat Ayurved UniversityIntroduction to Database Management Systems

Main points of this past exam are: Transaction Management, Consultant, National Institute, Technical Architect, Two-Phase Locking Protocol, Cascading Aborts, Andacle, Exclusive Locks, Serializable, Brief Description

Typology: Exams

2012/2013

Uploaded on 04/02/2013

shalin_p01ic
shalin_p01ic 🇮🇳

4

(7)

86 documents

1 / 16

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Name______________________________
1
UNIVERISTY OF CALIFORNIA, BERKELEY
College of Engineering
Department of EECS, Computer Science Division
CS186
J. Hellerstein
Spring 2001
Final Exam
Final Exam: Introduction to Database Systems
This exam has seven problems, each worth a different amount of points. Each problem is
made up of multiple questions. You should read through the exam quickly and plan your
time-management accordingly. Before beginning to answer a question, be sure to read it
carefully and to answer all parts of every question!
Good luck!
Transaction Management and Locking
1. [15 Points] You have been hired as a consultant to the US Government, to decide
which database system to buy for the National Institute of Health. You are
considering products from three database companies: Andacle, Notacle, and JCN.
Each company has sent you their top technical architect to tell you about their system.
All three use fancy terminology, but that does not mean they know what they are
talking about! Your job is to determine which companies clearly do not know what
they are talking about.
Some of the following statements that you hear are wrong, some are right. For each
one, circle whether it is wrong or right. If it is wrong, give a counterexample. If
it is right, explain briefly.
(over →)
You must write your answers on the exam. You also must write your name at the top of
every page, and you must turn in all the pages of the exam. Do not remove pages from
the stapled exam! Extra answer space is provided in case you run out of space while
answering. If you run out of space, be sure to make a “forward reference” to the page
number where your answer continues.
Class Account ____________________________
Do not write in this space
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff

Partial preview of the text

Download Transaction Management -Introduction to Database Systems - Exams and more Exams Introduction to Database Management Systems in PDF only on Docsity!

UNIVERISTY OF CALIFORNIA, BERKELEY

College of Engineering Department of EECS, Computer Science Division CS186 J. Hellerstein Spring 2001 Final Exam Final Exam: Introduction to Database Systems This exam has seven problems, each worth a different amount of points. Each problem is made up of multiple questions. You should read through the exam quickly and plan your time-management accordingly. Before beginning to answer a question, be sure to read it carefully and to answer all parts of every question! Good luck! Transaction Management and Locking

  1. [15 Points] You have been hired as a consultant to the US Government, to decide which database system to buy for the National Institute of Health. You are considering products from three database companies: Andacle , Notacle , and JCN. Each company has sent you their top technical architect to tell you about their system. All three use fancy terminology, but that does not mean they know what they are talking about! Your job is to determine which companies clearly do not know what they are talking about. Some of the following statements that you hear are wrong, some are right. For each one, circle whether it is wrong or right. If it is wrong, give a counterexample. If it is right, explain briefly. (over →) You must write your answers on the exam. You also must write your name at the top of every page , and you must turn in all the pages of the exam. Do not remove pages from the stapled exam! Extra answer space is provided in case you run out of space while answering. If you run out of space, be sure to make a “forward reference” to the page number where your answer continues. Do not write in this space Class Account ____________________________

a. “Because we use the Two-Phase Locking protocol (2PL) in JCN , deadlocks can never occur in our system.” Wrong Right b. “ Notacle uses Strict Two Phase Locking Protocol, and therefore we can ensure there will be no cascading aborts.” Wrong Right c. “Our lock manager in Andacle uses shared and exclusive locks, which guarantees that all schedules in our system are serializable.” Wrong Right

Functional Dependencies and Decomposition

  1. [10 points] Consider the relation R (A, B, C, D), with a legal instance of R as shown below. You have not been told what functional dependencies hold on R, you have only been shown this legal instance. Each statement below can be categorized either as True, False, or Not Enough Information to be sure. Circle one of those 3 categories for each statement, and justify your answers briefly. A B C D 2 2 2 4 2 2 2 2 4 2 2 2 2 4 2 3 4 4 4 8 a. Relation R needs to be decomposed to achieve Third Normal Form. True False NotEnoughInfo b. The functional dependency A → C holds for R. True False NotEnoughInfo c. CD is a not a key for R. True False NotEnoughInfo
  1. [15 points] Consider the relation Student (SSN, Id, Email, NumericGPA, LetterGPA), also known as SIENL. The relation has the following Functional Dependencies (FDs): {S → I, I → E, E → S, N → L}. a. Which of the following is the most efficient, correct way to find the keys? − Apply Armstrong’s Axioms repeatedly until you cannot derive any more FDs. For each FD with right-hand-side equal to SIENL, the corresponding left-hand-side is a key. − Find the attribute closures for each of S,I,E,N and L. The keys are those sets of attributes for which the union of their closures is equal to SIELN. − Construct a dependency graph and test it for cycles. All nodes on a cycle correspond to a key. b. How many (minimal) keys are there for Student? Explain either by listing the keys, or by a brief explanation. c. Decompose R into BCNF, showing a tree of decompositions. For each step, note the violation you resolve.
  1. [20 points] Below is the state of a DBMS after a crash. Your job will be to perform recovery according to ARIES (Algorithm for Recovery and Isolation Exploiting Semantics). Follow the procedures on the next page. Database: (PageNo: pageLSN) Transaction Table Dirty Page Table A: 2 B: 3 C: 30 Xact ID status lastLSN page recLSN 1 running 20 A 10 D: 5 E: 40 F: 7 B 20 2 running 30 C 30 Master Log Record LSN of last checkpoint = 35 Log: LSN xid page prevLSN Type UndoNextLSN Indicate if redone: 10 1 A (null) Update (null) 20 1 B 10 Update (null) 30 2 C (null) Update (null) 35 (null) (null) (null) Begin checkpoint (null) 40 3 E (null) Update (null) 50 2 (null) 30 Commit (null) 60 3 D 40 Update (null) 70 2 (null) 50 End (null) 80 1 C 20 Update (null) 90 1 (null) 80 Commit (null) 100 3 E 60 Update (null) 105 See tables above End checkpoint (null) 110 120 130 140 150 160 170 180 190 200 (over →)

a. Analysis. Do analysis to correctly fill out the Transaction and Dirty Page Tables. You may find that you’re scratching things out as you go…please try to be neat so we can read the final state of the tables. b. Redo. Perform redo to repeat history. Put a mark in the rightmost column of the log next to each update log record that you must redo in the database during this phase. c. Undo. Perform undo. For your answer, please add the appropriate log entires while undo is being performed, but do not modify the tables you filled in for Analysis (so that we can see what you did in part (a)). d. At the end of Undo, what would the real transaction table have in it?

a) Provide CREATE TABLE statements for the tables ShoppingCarts and CartContents. You may assign types as you see fit. Be sure that integrity constraints are maintained. Also, ensure that all the common transactions are able to run to completion (i.e. they don’t have to be aborted)! (over → )

b) Assume that each transaction works as follows: an existing customer creates a cart, adds five existing products to the cart, generates a checkout page, and deletes the cart. Given this workload, for each of the following indexes say whether they should be built (YES/NO), and justify why or why not. Be sure to justify all the details of the indexes you think should be built!

  • A clustered B+-tree index on CartContents.ProductID
  • A linear hash index on Customers.CustomerID
  • A linear hash index on ShoppingCarts.CartID
  • A clustered B+-tree index on ShoppingCarts.CustomerID
  • A clustered B+-tree index on ShoppingCarts.CustomerID
  • A linear hash index on Products.ProductID (over →)

d) In fact, Postgres has other advantages over MySQL as well. One additional advantage is that Postgres supports row-level locking (i.e. its transaction manager sets locks on individual rows), while MySQL only supports table-level locking (it sets locks on entire tables). Hence in principle Postgres can provide higher concurrency than MySQL in many scenarios. Your friend loves MySQL, and tries to convince you that its locking technique is not a problem for your workload. One of his main arguments is that your “checkout page” query is a “killer query” that will effectively force both MySQL and Postgres to run one user at a time to guarantee the ACID properties. Explain why your friend is wrong.