CASE STUDY: 1
In a medical database with attributes DISEASE, SYMPTOM, TEST (such as pathological or clinical tests), TREATMENT and MEDICINE, following data dependencies are assumed to hold.
DISEASE →→ SYMPTOM TEST
SYMPTOM →→ DISEASE
SYMPTOM TEST → DISEASE
SYMPTOM DISEASE → TREATMENT
TREATMENT → MEDICINE
Question:
1) Compute the dependency basis of each attribute?
2) Identify the join dependencies that hold in this database?
3) Does there exist a fourth normal form decomposition that preserves the dependencies?
4) If so, design a fourth normal form lossless, join decomposition that preserves the dependencies?
CASE STUDY: 2
Consider a relational database system with following relation schemes. BANKACNT (ACCT NO, NAME, ADDR)
ACNT DETAIL (ACCT NO, DEPOSIT, WITHDRAWL, DATE, BALANCE) BANK EMPLOYEE (EMP NO, EMP NAME, JOB, SALARY)
Express in QBE and QUEL authorization language the following authorizations.
Question:
1) Anyone can read BANKACNT relation except for the ACCT NO attribute.
2) Any accountholder can read the tuple corresponding to his/her own account number from the ACNT DETAIL relation.
3) Bank employees can read tuples from ACNT DETAIL relation, but only those employees whose job is TELLER can update entries in the ACNT DETAIL relation pertaining to any ACCT NO (this field cannot be modified).
4) Bank employees can read BANK EMPLOYEE relation except for the SALARY attribute.
CASE STUDY: 3
Since computer systems are subject to many types of failure, it is essential to restore the database to a consistent state, that existed prior to the occurrence of the failure. For example, in a banking system, a program executing money transfer may be interrupted due to a system failure after it has debited a certain amount from an account but before the same amount is credited to the other. In this case, after the system recovers from the failure, to preserve the consistency of the database either the balance in the debited account should be restored or an equivalent amount should be credited in the target account. The database recovery mechanisms are designed to deal with the consequences of such a system failures.
Question:
1) Explain why transaction atomicity is one of the most important requirement for concurrency control?
2) Describe how in a multiuser environment atomicity of read and write operations can be ensured, especially for large data items i.e. when the granularity of data items is larger than a disc block?
3) Describe the functional modules of a centralized database system responsible for concurrency control and recovery?
4) Discuss how database read and write operations are implemented?