Principles of Database Management Systems

Homework 1

Ying Li

4861521

Homework 1

Exercise 1.4
Explain the difference between external, internal, and conceptual schemas.
How are these different schema layers related to the concepts of logical and physical data independence?

Answer:
External schemas, which usually are also in terms of the data model of the DBMS, allow data access to be customized (and authorized) at the level of individual users or groups of users.
Internal (physical) schemas summarize how the relations described in the conceptual schema are actually stored on secondary storage devices such as disks and tapes.
Conceptual (logical) schema describes the stored data in terms of the data model of the DBMS. In a relational DBMS, the conceptual schema describes all relations that are stored in the database.
Any given database has exactly one conceptual schema and one internal schema because it has just one set of stored relations, but it may have several external schemas, each tailored to a particular group of users.

External schemas provide logical data independence, while conceptual schemas offer physical data independence.

Exercise 1.6
Scrooge McNugget wants to store information (names, addresses, descriptions of embarrassing moments, etc.) about the many ducks on his payroll. Not surprisingly, the volume of data compels him to buy a database system. To save money, he wants to buy one with the fewest possible features, and he plans to run it as a stand-alone application on his PC clone. Of course, Scrooge does not plan to share his list with anyone. Indicate which of the following DBMS features Scrooge should pay for; in each case, also indicate why Scrooge should (or should not) pay for that feature in the system he buys.
1. A security facility.
2. Concurrency control.
3. Crash recovery.
4. A view mechanism.
5. A query language.

Answer:
A security facility is necessary because Scrooge does not plan to share his list with anyone else. Although he is running it on his stand-alone PC, a rival could break in and attempt to query his database. The security features of database would prevent the intruder.

Concurrency control is not needed because only he himself uses the database.

Crash recovery is essential for any database; Scrooge would not want to lose his data if the power was interrupted while he was using the system.

A view mechanism is needed. Scrooge could use this to develop “custom screens” that he could conveniently bring up without writing long queries repeatedly.

A query language is necessary since Scrooge must be able to analyze the dark secrets of his victims. In particular, the query language is also used to define views.

Exercise 1.9
Answer the following questions:
1. What is a transaction?
2. Why does a DBMS interleave the actions of different transactions instead of executing transactions one after the other?
3. What must a user guarantee with respect to a transaction and database consistency? What should a DBMS guarantee with respect to concurrent execution of several transactions and database consistency?
4. Explain the strict two-phase locking protocol.
5. What is the WAL property, and why is it important?

Answer:
1. A transaction is anyone execution of a user program in a user program in a DBMS. This is the basic unit of change as seen by the DBMS.

2. A DBMS is typically shared among many users. Transactions from these users can be interleaved to improve the execution time of users’ queries. By interleaving queries, users do not have to wait for other user’s transactions to complete fully before their own transaction begins.

3. A user must guarantee that his or her transaction does not corrupt data or insert nonsense in the database. For example, in a banking database, a user must guarantee that a cash withdraw transaction accurately models the amount a person removes from his or her account.
A DBMS must guarantee that transactions are executed fully and independently of other transactions. An essential property of a DBMS is that a transaction should execute atomically, or as if it is the only transaction running. Besides, DBMS ensures atomicity even if system crashes in the middle of a transaction.

4. A lock us a mechanism used to control access to database objects. Two kinds of locks are commonly supported by a DBMS: shared locks on an object can be held by two different transactions at the same time, but an exclusive lock on an object ensures that no other transactions hold any lock on this object.
A transaction must hold all the required locks before executing, and does not release any lock until the transaction has completely finished.

5. The WAL property affects the logging strategy in a DBMS. The WAL, Write-Ahead Log, property states that each write action must be recorded in the log (on disk) before the corresponding change is reflected in the database itself. This protects the database from system crashes that happen during a transaction’s database knows to undo the changes to recover from a system crash. Otherwise, the database logs the changes, then the database would not be able to detect his change during crash recovery.