DbFinal Key

CSE4020 (Databases) Fall 2001 Final Exam Time: 120 min Points:
Write answers in your own language. Answers must be short.
1a. Online transaction processing (OLTP) is controlled by a program called ---.

TP Monitor, p 60, ch2.
1b. “Data about data” is also called ----.
meta-data, ch 2, p 61.
1c. The repository of information describing the data in a database is called -------.
System-catalog, ch 2, p61.
1d. What are the three primary types of architecture for connectivity when multiple users
are allowed to access a database (just the names)
Teleprocessing, file-server, and client-server. Ch 2, p 56-57
1e. The software module of a DBMS that determines optimal strategy for the query
execution is called -----.
Query optimizer, p 55, ch 2.
1f. Of the three-level ANSI-SPARC architecture, what are the two levels you may have
worked on during your project.
Conceptual, and internal. No one has worked on creating views.
1g. “Read phase,” “validation phase” and then if necessary “Write phase” belongs to
what type of concurrency control methods?
Optimistic, p 562 and 578, ch 19.
1h. Name three potential problems when any concurrency control mechanism is absent in
a DBMS.
Lost update problem, uncommitted dependency problem, and inconsistent analysis
problem, p 555, ch 19.
1i. The point of synchronization between the database and the transaction log file, when
all buffers are force written to hard disc is ------.
Checkpoint, p586, ch 19.
1j. A situation when transactions wait for each other on a chain is called --------.
Deadlock, p 569, ch 19.
2a. A transaction processing system uses Timestamping method for concurrency control.
A transaction started at time unit 45 wants to read a data item q, and finds its (q’s) read-
stamp to be 40, and write-timestamp to be 43. Explain briefly what happens then? [3]
read is allowed and read-ts increments to 45. p 573, ch 19, no (1)
2b. Explain with some data values what type of concurrency-related problem the
following schedule will face. [4]
Time T1 T2
.1 begin trans
.2 read(x) begin trans
.3 x = x*2 read(x)
.4 write(x) x = x –3
.5 end trans write(x)
.6 end trans
Lost update problem, as in p 556, fig 19.4.
2c. Explain if the following schedule is conflict-serializable or not. [3]
Time T1 T2
.1 begin trans
.2 read(x)
.3 x = x*2
.4 write(x) begin trans
.5 read(x)
.6 x = x –3
.7 write(x)
.8 read(z)
.9 z = z –3
.10 write(z)
.11 read(y) end trans
.12 y = y*2
.13 write(y)
.14 end trans
serializable, no cycle of wait, as in fig 19.9, taken from fig 19.8, p 560
3. The following tables form part of a database held in a relational DBMS:-

Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
where Hotel contains hotel details and hotelNo is the primary key;
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;
Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms the primary key;
and Guest contains guest details and guestNo is the primary key.
Identify the foreign keys in this schema.

Explain how the entity and referential integrity rules apply to these relations.
For each relation, the primary key must not contain any nulls.
Room is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Room should either
be null or contain the number of an existing hotel in the Hotel relation. In this case study, it would
probably be unacceptable to have a hotelNo in Room with a null value.
Booking is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Booking should
either be null or contain the number of an existing hotel in the Hotel relation. However, because
hotelNo is also part of the primary key, a null value for this attribute would be unacceptable.
Similarly for guestNo. Booking is also related to Room through the attribute roomNo.
4a. List all ‘double’ or ‘family’ rooms with a price below $40.00 per night, in ascending order of price.
5.10 SELECT * FROM Room WHERE price < 40 AND type IN (‘D’, ‘F’)
ORDER BY price;
4b. Translate in plain English what does the following query want:
SELECT * FROM Booking
WHERE dateTo IS NULL;
5.11 List the bookings for which no dateTo has been specified.
5. List the rooms that are currently unoccupied at the Hilton hotel.
5.20 SELECT * FROM Room r
WHERE roomNo NOT IN
(SELECT roomNo FROM Booking b, Hotel h
WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
b.hotelNo = h.hotelNo AND hotelName = ‘Hilton’);

DbFinal Key

Uploaded by

Copyright:

Available Formats

DbFinal Key

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DbFinal Key

Uploaded by

Copyright:

Available Formats

CSE4020 (Databases) Fall 2001 Final Exam Time: 120 min Points:

Write answers in your own language. Answers must be short.

1a. Online transaction processing (OLTP) is controlled by a program called ---.

3. The following tables form part of a database held in a relational DBMS:-

Identify the foreign keys in this schema.

You might also like