Difference between a system crash and a media failure

Assignment Help Database Management System
Reference no: EM131559645

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?

Reference no: EM131559645

Questions Cloud

What minimalist painter made large scale portraits : Artists such a Vito Acconci rejected traditional artist's materials and used what as their medium or canvas?
How much are you willing to pay for stock today : You anticipate that XSensors Co. will pay a constant dividend of $2.72 each year for the next 10 years. How much are you willing to pay for this stock today?
Market risk premium and risk-free rate-calculate the wacc : Market: 7.56 percent market risk premium and 3.12 percent risk-free rate. Calculate the WACC.
According to a geometric brownian motion process : The stock price changes according to a geometric Brownian Motion process, with time measured in months and coefficients µ=0 s=2. Calculate the probability that:
Difference between a system crash and a media failure : What is the difference between a system crash and a media failure - How can you ensure that the user who is purchasing the painting is really Mary?
What was the firm ocf for the year : Over the past year, a firm has paid $86 in dividends and $60 in interest. What was the firm's OCF for the year?
What is implied one year rate that will occur in two years : what is the implied 1-year rate that will occur in 1 year? What is the implied 1-year rate that will occur in 2 years?
Is there connection between financialization and technology : Is there a connection between financialization and technology? Do either play a role in culture of Silicon Valley or in actions of Goldman Sachs in Griftopia?
Net working capital increases when : Net working capital increases when

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd