Instructions: Please complete this homework on your own.

Your company has had a disaster in the server room over the weekend. Luckily all database components where replicated to an off-site server. The last good backup of the database was on Thursday night, and you would like to implement a forward recovery to bring the database to the status it was at end of day Friday. How would you do that? (3pts)

List AND describe at least 5 information items that are stored in a database journal or log file (5pts).

Describe the concept of data independence (2 pts).

The ability to modify a schema definition in one level without affecting a schema definition in the next higher level is called Data Independence.

It is independence between the programs and the data. 

List and define 6 specific duties that a Database Administrator must perform (3pt).

1 Selection of hardware and software

Keep up with current technological trends

Predict future changes

Emphasis on established off the shelf products

2. Managing data security and privacy

Protection of data against accidental or intentional loss, destruction, or misuse

 

3. Managing Data Integrity

Integrity controls protects data from unauthorized use

Data consistency

Maintaining data relationship

Domains- sets allowable values

Assertions- enforce database conditions

4. Data backup

We must assume that a database will eventually fail

Establishment procedures

how often should the data be back-up?

what data should be backed up more frequently?

who is responsible for the back ups?

Back up facilities

automatic dump- facility that produces backup copy of the entire database

periodic backup- done on periodic basis such as nightly or weekly

cold backup- database is shut down during backup

hot backup- a selected portion of the database is shut down and backed up at a given time

backups stored in a secure, off-site location

5. Database recovery

Application of proven strategies for reinstallation of database after crash

Recovery facilities include backup, journalizing, checkpoint, and recovery manager Firewalls

Establishment of user privileges

Complicated by use of distributed systems such as internet access and client/ server technology.

 

6. Tuning database performance

Set installation parameters/ upgrade DBMS

Monitor memory and CPU usage

Input/ output contention

user striping

distribution of heavily accessed files

Application tuning by modifying SQL code in applications

Define the process of Archiving and describe a valid reason as to why a DBA would need to Archive (2pts).

What is the difference between a shared lock and an exclusive lock (2pt)?

 

Exclusive lock mode prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

Share lock mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

List and define 3 important characteristics of a distributed DBMS (3pts).

 

You just made an update to a remote site on a distributed DB. You noticed the communication time for your update was 58 seconds and you know that the access delay on the network is estimated to be 4 seconds. You updated 30,000 records that were 600 bits long each. Calculate the transmission rate of the network you used to make the update. (Please show your work) (2pts).

 

What is the difference between an OLTP system and an OLAP system (2pts)?

OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). 

– OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). 

Explain in your own words the difference between a relational database and an object oriented database (1pt).

The main difference is the underlying paradigm.

A relational databases relies on the relational model, on the other hand a object database relies on the OOP.

The relational model organizes information in a set of tables each are composed of rows and columns. Each column represents a property and each row represent an entity.

In a object oriented database each element resembles a object from the object oriented paradigm.