Part -1:
SECTION A:
Question One
Define the following terms as used in transactions.
- Transaction
- Rollback
- Abort
- Commit
Question Two
List FOUR reasons for a transaction failure in the middle of execution.
Question Three
What is meant by data mining?
Question Four
State THREE benefits of OLAP.
Question Five
List FOUR reasons for data warehousing in an organization.
Question Six
List the main advantages of having a distributed database.
Question Seven
Explain the meaning of deadlock and schedule as used in concurrent transactions.
Question Eight
List the various types of database anomalies.
Question Nine
List TWO secondary Data storage devices.
SECTION B: Answer any TWO Questions.
Question One
(a) Describe the desirable properties (ACID properties) of a transaction.
(b) By defining a distributed database outline FIVE advantages of distributed, databases.
Question Two
(a) Stating SIX benefits describe random file organization.
(b) Explain the concept of ARIES algorithm.
Question Three
(a) With the aid of a state diagram of a transition explain the state of a transaction.
Give THREE advantages of RAID Technology and TWO disadvantages of Raid 1 technology.
a) Why are I/O costs important in a DBMS?
b) Explain the difference between logical and physical data independence
c) What is the role of the buffer manager in a DBMS?
d) Define the term "most selective access path for a query"
e) Describe two possible page formats and list the trade-offs between them
f) Explain the difference between logical and physical data independence
g) Describe the use of locks for improved conflict resolution in Optimistic Concurrency Control.
h) In the context of query optimization, what is an SQL query block?
i) Assuming that the DBA is never interested in running queries, does the DBA need to understand query optimization? Why?
j) Name an important capability of a DBMS buffer manager that is not supported by a typical operating system's buffer manager.
k) Briefly describe how the recovery manager ensures atomicity of transactions and durability?
l) What is the difference between a system crash and a media failure
SECTION B
Question 2
a) Explain the difference between Hash indexes and B+-tree indexes. In particular, discuss how equality and range searches work, using an example.
b) List the goal and importance of query optimization?
i) In the context of query optimization, what is a an SQL query block
ii) Motivate and describe the use of locks for improved conflict resolution in Optimistic Concurrency Control.
Question 3
a) Consider a database with objects X and Y and assume that there are two transactions T1 and T Transaction T 1 reads objects X and Y and then writes object X. Transaction T 2 reads objects X and Y and then writes objects X and Y .
i) Give an example schedule with actions of transactions T1 and T 2 on objects X and Y
that results in a write-read conflict.
ii) Give an example schedule with actions of transactions T1 and T 2 on objects X and Y
that results in a read-write conflict.
iii) Give an example schedule with actions of transactions T1 and T 2 on objects X and Y
that results in a write-write conflict.
iv) For each of the three schedules, show that Strict 2PL disallows the schedule.
b) If a DBMS already supports discretionary and mandatory access controls, is there a need for encryption?
c) Describe pipelining and its advantages
Question 4
a) For each of the following SQL queries, for each relation involved, list the attributes that must be examined to compute the answer. All queries refer to the following relations:
Emp(eid: integer, did: integer, sal: integer, hobby: char(20)) Dept(did: integer, dname: char(20), floor: integer, budget: real)
1. SELECT * FROM Emp E
2. SELECT * FROM Emp E, Dept D
3. SELECT * FROM Emp E, Dept D WHERE E.did = D.did
4. SELECT E.eid, D.dname FROM Emp E, Dept D WHERE E.did = D.did
b) You are a painter and have an Internet shop where you sell your paintings directly to the public. You would like customers to pay for their purchases with credit cards, and wish to ensure that these electronic transactions are secure. Assume that Mary wants to purchase your recent painting of the Mona Lisa.
How can you ensure that the user who is purchasing the painting is really Mary?
c) Assume that you would like Mary to be able to verify that all your email messages are really sent from you. How can you authenticate your messages without encrypting the actual text?
Section A
Question 1
1. Define the following terms
- Strict 2 PL
- Interleaved Execution
- Serial Scheduling
- WW Conflict
2. What are the methods to prevent unauthorized users in remote accessing in distributed database
3. Describe shadow paging recovery technique.
4. Explain how user authentication can be implemented so that Amazon can be sure it is you using the card and not someone who stole it.
5. Discuss four recovery facilities any DBMS should be able to provide.
6. Consider the database given by the following schema: Customer(Cust_No, Sales_Person_No, City)
Sales_Person(Sales_Person_No, Sales_Person_Name, Common_Prec, Year of Hire) Give an expression in SQL for each of the following queries:
Display the best query with all customers by Cust_No with the City in which each is located.
Section B -
- Highlight three (3) of the main motivations for developing distributed databases.
- Ideally, a DBMS should be distribution transparent. Name and explain three (3) types of transparencies.
- With a diagram describe dataware house architecture
Question 3
- A good database design must have data Integrity. Using an example discuss the (i) Entity Integrity
(ii)Referential Integrity
B. Explain what you understand by the term Data dictionary with respect to a database management System (DBMS)
C. Why would you consider the Data Dictionary as an Important component of the Database management System (DBMS)
- Give an example of the data expected in a Data Dictionary
Question 4
- Explain the role of the query evaluation and query optimization
- Explain the timestamp-based concurrency control protocol and the modifications implemented in it.
- Consider a database with objects X and M and assume that there are two transactions T1 and T 2. Transaction T 1 reads objects X and M and then writes object . Transaction T 2 reads objects X and M and then writes objects X and M .
- Give an example schedule with actions of transactions T1 and T 2 on objects X and M that results in a write-read conflict.
- Give an example schedule with actions of transactions T1 and T 2 on objects X and M that results in a read- write conflict.
- Give an example schedule with actions of transactions T1 and T 2 on objects X and M that results in a write-write conflict.
Section A
Question 1
1. 1. Define the following terms
a. a) Serial Scheduling
b. b) Interleaved Execution
c. c) Atomicity
d. d) Durability
1. 2. What are the functionalities of data mining system
2. 3. List four common types of database failure
3. 4. What are the Basic Algorithms for Executing Query Operations
4. 5. What are the methods to prevent unauthorized users in remote accessing in distributed database
5. 6. From the schema below generate SQLs that will meet the following conditions
LECTURER (l-no, lname, status) UNIT (u-no, d-no, uname) DEPARTMENT (d-no, dname, hod)
STUDENT (s-no, sname, saddress) ASSESSMENT (s-no, u-no, final-grade) TIMETABLE (u-no, session-no, l-no, code)
(i) List the student number and unit number for those students who have passe these units with a final grade greater than 4. (5 marks)
(ii) Find the numbers of those students who have achieved one or more fina grades greater than 14 (5 marks)
Section B
Question 2
A. A. With examples Describe the following anomalies
I. I. Write-Read Conflicts (WR Conflicts)
II. II. Read-Write Conflicts (RW Conflicts)
III. III. Write-Write Conflicts (WW Conflicts)
Question 3
a. a) A good database design must have data Integrity. Using an example discuss the
(i) Entity Integrity
(ii) Referential Integrity
b) Explain what you understand by the term Data dictionary with respect to a database management System (DBMS)
a. c) Why would you consider the Data Dictionary as an Important component of the Database management System (DBMS)
b. d) Highlight three (3) of the main motivations for developing distributed databases
Question 4
a. a) Describe the following Three main recovery techniques that are commonly employed are:
I. I. Deferred update
II. II. Immediate update
III. III. Shadow paging
a. b) ARIES algorithm has three main principles. Explain each of them
I. I. Write-ahead logging
II. II. Repeating history during redo
III. III. Logging changes during undo
Question One
- Ensuring data integrity and consistency is of vital importance to a DBMS during the application of transactions to the database, particularly concurrent transactions. Using your own suitable examples, explain the following transaction-related concepts.
- ACID Properties
- COMMIT and TWO-PHASE COMMIT
- ROLLBACK and CASCADED ROLLBACK
- LOCKING - Optimistic & Pessimistic
- CHECKPOINTS and SAVEPOINTS
- Clearly distinguish between discretionary security mechanisms and mandatory access control.
- When a DB is accessed from a secure location, password based schemes are normally sufficient. However over an external network you need to be concerned about user authentication and trust. Amazon is a popular website from which we can buy books online. Suppose you want to purchase some books from Amazon using your credit card:
- Explain how user authentication can be implemented so that Amazon can be sure it is you using the card and not someone who stole it.
- Explain how trust would be ensured, such that you are sure that the form you are using to enter the credit card information is indeed Amazon's, and not some rogue site spoofing Amazon (to steal such information), and that the sensitive information is not sniffed while it is being sent over the network to Amazon.
- Highlight THREE (3) of the main motivations for developing distributed databases.
Question Two
- Given that the fundamental principle of a distributed system is that, to a database user, a distributed database system should behave exactly like a non-distributed system. Elaborate the implications of the above statement on database integrity and the consistency of distributed queries and transactions in a distributed database that supports replication.
- Ideally, a DBMS should be distribution transparent. Name and explain three (3) types of transparencies.
Question Three
- Securing a database aims to achieve the following objectives:
- Confidentiality
- Integrity
- Availability
- Briefly describe what the above objectives mean
- For each objective, describe two appropriate security controls
- Justifying your answer, explain whether the phantom problem can occur in a database where the set of database objects is fixed and only the values of objects can be changed.
Question Four
- By their very nature, data warehouses get bigger over time. As the search space increases, the query performance decreases and tuning techniques are required. For each of the two approaches listed below, using your own appropriate diagrams and examples, describe the essential concepts and techniques.
- Aggregation & Summary Data
- Indexing & Optimization
- Discuss FIVE (5) business areas in which data mining technologies can be applied.
a) Why are I/O costs important in a DBMS?
b) Explain the difference between logical and physical data independence
c) What is the role of the buffer manager in a DBMS?
d) Define the term "most selective access path for a query"
e) Describe two possible page formats and list the trade-offs between them
f) Explain the difference between logical and physical data independence
g) Describe the use of locks for improved conflict resolution in Optimistic Concurrency Control.
h) In the context of query optimization, what is an SQL query block?
i) Assuming that the DBA is never interested in running queries, does the DBA need to understand query optimization? Why?
j) Name an important capability of a DBMS buffer manager that is not supported by a typical operating system's buffer manager.
k) Briefly describe how the recovery manager ensures atomicity of transactions and durability?
l) What is the difference between a system crash and a media failure
SECTION B
Question 2
a) Explain the difference between Hash indexes and B+-tree indexes. In particular, discuss how equality and range searches work, using an example.
b) List the goal and importance of query optimization?
i) In the context of query optimization, what is a an SQL query block
iii) Motivate and describe the use of locks for improved conflict resolution in Optimistic Concurrency Control.
Question 3
d) Consider a database with objects X and Y and assume that there are two transactions T1 and T Transaction T 1 reads objects X and Y and then writes object X. Transaction T 2 reads objects X and Y and then writes objects X and Y .
v) Give an example schedule with actions of transactions T1 and T 2 on objects X and Y that results in a write-read conflict.
vi) Give an example schedule with actions of transactions T1 and T 2 on objects X and Y that results in a read-write conflict.
vii) Give an example schedule with actions of transactions T1 and T 2 on objects X and Y that results in a write-write conflict.
viii) For each of the three schedules, show that Strict 2PL disallows the schedule.
e) If a DBMS already supports discretionary and mandatory access controls, is there a need for encryption?
f) Describe pipelining and its advantages
Question 4
c) For each of the following SQL queries, for each relation involved, list the attributes that must be examined to compute the answer. All queries refer to the following relations:
Emp(eid: integer, did: integer, sal: integer, hobby: char(20)) Dept(did: integer, dname: char(20), floor: integer, budget: real)
1. SELECT * FROM Emp E
2. SELECT * FROM Emp E, Dept D
3. SELECT * FROM Emp E, Dept D WHERE E.did = D.did
4. SELECT E.eid, D.dname FROM Emp E, Dept D WHERE E.did = D.did
d) You are a painter and have an Internet shop where you sell your paintings directly to the public. You would like customers to pay for their purchases with credit cards, and wish to ensure that these electronic transactions are secure. Assume that Mary wants to purchase your recent painting of the Mona Lisa.
How can you ensure that the user who is purchasing the painting is really Mary?
c) Assume that you would like Mary to be able to verify that all your email messages are really sent from you. How can you authenticate your messages without encrypting the actual text?
Question 1
a) Define the term "most selective access path for a query"
b) State and briefly explain the differences between the relational model and tables. In particular, compare and contrast: table vs. relation, tuple vs. row, and attribute vs. column.
c) Explain the difference between logical and physical data independence
d) Assuming that the DBA is never interested in running queries, does the DBA need to understand query optimization? Why?
e) Name an important capability of a DBMS buffer manager that is not supported by a typical operating system's buffer manager.
f) Describe two possible page formats and list the trade-offs between them
g) Consider a relation stored as a randomly ordered file for which the only index is an unclustered index on a field called sal. If you want to retrieve all records with sal>20, is using the index always the best alternative? Explain.
h) In relation to extensible hashing explain why local depth and global depth are needed.
Question 2
a) What is the goal and importance of query optimization?
b) In the context of query optimization, what is a an SQL query block
c) Consider the university enrollment database schema: (9marks) Student(snum: integer, sname: string, major: string, level: string, age: integer) Class(name: string, meets at: time, room: string, fid: integer)
Enrolled(snum: integer, cname: string) Faculty(fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.
For each of the following transactions, state the SQL isolation level you would use and explain why you chose it.
a) Enroll a student identified by her snum into the class named 'Introduction to Database Systems'.
b) Change enrollment for a student identified by her snum from one class to another class.
c) Assign a new faculty member identified by his fid to the class with the least number of students.
Question 3
a) Describe the use of locks for improved conflict resolution in Optimistic Concurrency Control.
b) Briefly describe how the recovery manager ensures atomicity of transactions and durability?
c) Consider the following BCNF relation, which lists the ids, types (e.g., nuts or bolts), and costs of various parts, along with the number available or in stock:
Parts (pid, pname, cost, num avail)
You are told that the following two queries are extremely important:
- Find the total number available by part type, for all types. (That is, the sum of the num avail value of all nuts, the sum of the num avail value of all bolts, and so forth)
- List the pids of parts with the highest cost.
d) Describe the physical design that you would choose for this relation. That is, what kind of a file structure would you choose for the set of Parts records, and what indexes would you create?
e) Suppose your customers subsequently complain that performance is still not satisfactory (given the indexes and file organization you chose for the Parts relation in response to the previous question). Since you cannot afford to buy new hardware or software, you have to consider a schema redesign. Explain how you would try to obtain better performance by describing the schema for the relation(s) that you would use and your choice of file organizations and indexes on these relations.
d) How would your answers to the two questions in part a) and b) change, if at all, if your system did not support indexes with multiple-attribute search keys?
Question 4
a) What is the difference between a system crash and a media failure
b) If a DBMS already supports discretionary and mandatory access controls, is there a need for encryption?
c) You are a painter and have an Internet shop where you sell your paintings directly to the public. You would like customers to pay for their purchases with credit cards, and wish to ensure that these electronic transactions are secure. Assume that Mary wants to purchase your recent painting of the Mona Lisa.
d) How can you ensure that the user who is purchasing the painting is really Mary?
e) Assume that you would like Mary to be able to verify that all your email messages are really sent from you. How can you authenticate your messages without encrypting the actual text?