DOC PREVIEW
UW CSE 444 - Exam Guide

This preview shows page 1-2-3-4-5 out of 14 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Name:CSE 444, Winter 2011, Midterm Examination9 February 2011Rules:• Open books and open notes.• No laptops or other mobile devices.• Please write clearly.• Relax! You are here to learn.• An extra page is provided in case you run out of space, but make sure to put a forward reference.Question Max Grade1 302 303 184 22Total 1001Initials:1. (30 points) SQLFor a given quarter, the table Student lists all students registered at the University, the table Courselists all courses offered, and the table Enrollment encodes the students that attend any course in thecurrent quarter.Student ( sid, sname )Course ( cid, cname )Enrollment ( cid, sid )Enrollement.sid is a foreign key that references Student.sid.Enrollement.cid is a foreign key that references Course.cid.(a) (8 points) Assume that no two courses have the same name. Call a course “big” if the numberof students enrolled is at least 50. Write a SQL query that returns the names of all big courses indecreasing order of their enrollment size.2Initials:(b) (8 points) A “classmate” of a student is another student who is enrolled in at least one sameclass. Write a SQL query that returns all pairs of classmates. You should return their studentIDs, as their names do not uniquely identify them.3Initials:(c) (14 points) Write a SQL query that computes for each student id in the Student table the totalnumber of different classmates that this student has across all courses. Note that a student maybe so busy with research (or parties ¨^) that he or she may choose not to enroll to any coursethat quarter, and hence has no classmates. Such students should still appear in the result, with0 as the number of their classmates.Hint: this is not required, but it may help you to make use of an earlier query.4Initials:2. (30 points) Conceptual Desi gn(a) (12 points) A friend is impressed by your skills in CSE 444, and asks you to help her designa database to model doctors’ offices across Seattle. She started the E/R diagram below whichalready contains all the entity sets, their attributes and relationships she wants you to consider.You need to finish the diagram and define a number of constraints to ensure that you model thesemantics of an office as closely as possible.Make sure that you do not impose additional constraints not defined by the model.i. (4 points) An office may be managed by at most one doctor. A doctor is uniquely identifiedby their badgeId, and may manage more than one office. Draw the necessary constraints tocapture this requirement.ii. (5 points) An office is identified by its address, and contains one or more exam rooms .A room can be identified by its room number, and the office that it is in. Capture theseconstraints by drawing on or modifying the diagram where necessary.iii. (3 points) When a patient visits an office, he or she has a consultation with a doctor in anexamination room. Each patient is uniquely identified by their SSN.Draw your answer directly onto the figure below. Make sure that any arrows, lines, double linesetc, are clearly visible.Office%address&Manages% Doctor%Inside%Consults%Exam%Room%room#&Pa:ent%badgeId&phone#&name&ssn&name&phone#&5Initials:(b) (12 points) Convert the E/R diagram below to a database schema. Indicate the keys for eachtable in your answer. You do not need to write SQL DDL commands.a" b"A" R"isa"c" d"B"g" h"D"C" S"e"f"6Initials:(c) (6 points) Suppose we are told that R(A, B, C, D) is in BCNF, and that 3 out of the 4 FDslisted below hold for R. Choose the FD that R does not satisfy, and explain your reasoning.1 : A → BCD2 : BC → A3 : CD → B4 : D → C7Initials:3. (18 points) Logging and RecoveryYour database server crashed due to a power outage. After rebooting, you find the following log andcheckpoint information on disk, and begin the recovery process. We assume that STEAL/NO FORCEpolicy is used, and we use the ARIES method for recovery.LSN Record prevLSN undoNextLSN30 update: T3 writes P5 null -40 update: T4 writes P1 null -50 update: T4 writes P5 40 -60 update: T2 writes P5 null -70 update: T1 writes P2 null -80 Begin Checkpoint - -90 update: T1 writes P3 70 -100 End Checkpoint - -110 update: T2 writes P3 60 -120 T2 commit 110 -130 update: T4 writes P1 50 -140 T2 end 120 -150 T4 abort 130 -160 update: T5 writes P2 Null -180 CLR: undo T4 LSN 130 150 50Transaction Table at time of checkpointTransaction ID lastLSN StatusT1 70 RunningT2 60 RunningT3 30 RunningT4 50 RunningDirty Page Table at checkpointPage ID recLSNP5 50P1 40(a) (2 points) The log record at LSN 60 denotes an update to page P5 by transaction T2. Was thisupdate to page P5 successfully written to disk? If yes, at what point could that have happened?(b) (2 points) The log record at LSN 70 denotes an update to page P2 by transaction T1. Was thisupdate successfully written to disk? If yes, at what point? Briefly explain your answers.8Initials:(c) (6 points) At the end of the Analysis phase, what will the Transaction and Dirty Page Tableslook like? Populate your answers in the tables below.Transaction ID lastLSN Status Page IDrecLSN(d) (6 points) At which LSN in the log will REDO begin? Which log records will be redone? Listtheir LSNs, and briefly justify your answers.9Initials:(e) (2 points) For each of the following questions circle the right answers.i. In an UNDO only logging scheme, what buffer management policies apply? (circle one ofSTEAL or NO STEAL, and one of FORCE or NO FORCE)STEAL / NO STEAL FORCE / NO FORCEii. During recovery with REDO only logging, we cannot use a checkpoint for which we see a<START CKPT(. . . Ti. . .)> record but no corresponding <END CKPT> record.TRUE / FALSE10Initials:4. (22 points) Concurrency ControlIn the schedules given below, the label Ri(X) indicates a read of element X by transaction Ti, andWi(X) indicates a write of element X by transaction Ti.(a) (4 points) Draw the precedence graph for schedule 1. Is schedule 1 conflict-serializable? If so,what order of the three transactions defines a conflict-equivalent serial schedule?Schedule 1R2(A) R1(C) R2(B) W2(B) R3(B) R1(A) R3(C) W3(C) W1(A)(b) (4 points) Draw the precedence graph for schedule 2. Is schedule 2 conflict-serializable? If so,what order of the three transactions defines a conflict-equivalent serial schedule?Schedule 2R2(A) R1(C) R2(B) R3(B) W2(B) R1(A) R3(C) W3(C) W1(A)11Initials:(c) (9 points) Can the two schedules from (a) and (b) occur under (non-strict) 2 Phase Locking? Ifyes, then add the proper lock/unlock


View Full Document

UW CSE 444 - Exam Guide

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Exam Guide
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Exam Guide and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Exam Guide 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?