Past Exam Database Questions

Front 1

Explain the terms database and DBMS

Back 1

A database is a collection of information that is organised, so that they can be easily be accessed, managed and updated

Front 2

Explain the term database

Back 2

A database is a collection of information that is organised, so that it can be easily accessed,managed, and updated

Front 3

Explain the term DBMS

Back 3

A DBMS is a collection of programs that manages the database structure and controls access to the data in the database.

Front 4

What is normalisation and why normalise a relational scheme?

Back 4

-Normalisation is a process of reducing redundancies of data in the database

-It protects the integrity of the database

-It is better to be normalised for security reasons, disk space usage, speed of queries, efficiency of updates.

-No data redundancies or update anomalies

Front 5

The five steps to transform at data model into a relational schema.

Back 5

1) Each entity becomes a table and is made plural

2)The identifying attribute becomes the primary key

3)All the other attributes become none key attributes

4)For one-to-many relationships ,the primary key of the one table goes into the many table

5)Optionality of the many end tells us if the foreign key can be Null are not.

Eg.If the many end is optional ➡️FK can be null if the many end is not optional ➡️ FK cannot be null.

Front 6

What is a database transaction and my properties should it possess

Back 6

Transaction- either a single action or a series of actions carried out by a single user or program which accesses or changes the contents of a database.

Transaction properties:

Atomicity - All or Nothing

Consistency- must transform from one consistent state to another

Isolation/independence-Transactions execute independent to each other, effects of transaction only visible after commit

Durability-Updates must be durable(If another transaction rolls back, it does not affect the other - gives transaction names)

Serialisability-Concurrent execution of transactions have consistent results, order should not matter -same results

Front 7

Explain the role of SQL commit and Rollback statements in transaction execution?

Back 7

Commit command saves changes made by a transaction to a database since the last commit or rollback took place.

- if the transaction is fully successful

Rollback undoes any actions that have not yet been committed - back as far as the last commit or rollback-this is issued if there is a failure in the transaction

Front 8

The transaction log consists of an online portion and an off-line portion give a detailed explanation of what this means

Back 8

The online portion of the transaction is stored in main memory buffer, when this becomes full it is copied to secondary storage e.g. making a back up which is off-line.

The logs starts again from the start online

Front 9

Describe the files required and the process involved to successfully recover a database?

Back 9

Transaction log files are used to successfully recover a database. The files contain a log that includes- start and end of every transaction- updates that occur-keep track of transactions that affect the values of database items.

These files are stored in the main memory buffer and are backed up frequently to secondary storage. If there is a failure for any reason and the database needs to be recovered ,the back up transaction log files are loaded and a redo of all transactions since the last back up was carried out.

Front 10

Describe, using examples, the SQL statements used to assign and remove database user privileges.

Back 10

The database administrator has full control of all system tables and is responsible for enrolling users of the system and the type of privileges they have. their are 3 different types of privileges- CONNECT-RESOURCE-DBA

CONNECT:

Least number of privileges- can perform SELECT/INSERT/DELETE, depends what the DBA has specified- CREATE views look at all the users data with permission.

RESOURCE:

CREATE Tables - CREATE indexes

GRANT other users access to their tables - look at others with permission

DBA:

-Access to everything

-Can GRANT or REVOKE privileges

GRANT Connect/Resource/DBA

TO Username/Groupname

IDENTIFIED BY Password;

GRANT All/SELECT/INSERT

ON Tablename

TO Username;

REVOKE

ON

FROM

Front 11

Explain the terms Database and DBMS

Back 11

Database: an organised collection of of related data.

DBMS: Database management system-special software for managing a database including querying data, data security and data sharing

Front 12

In the relational data model how are Integrity rules enforced?

Back 12

-Relational model requires that every table must have a primary key-this insures entity integrity is enforced when data added to a table ensuring every row is unique.

-The PK cannot store null values

-Referential integrity - A foreign key must have a matching primary key in another table and must must enforce referential integrity so that there is always a corresponding value for a FK.

Front 13

In the relational data model what are tuple and an attribute?

Back 13

Tuple - Row

Attribute - Column

Front 14

In relation Data Modelling, explain using ERD diagrams the following terms:

A) Entity type/Entity instance

B)Involuted Relationships

C)Parallel Relationships

D)Cardinality

E)Optionality/Mandatory participation

Back 14

A) Entity type/Entity instance

Entity type: is a category of a thing of object. Eg. Cars,Rooms,Order.

Entity instance: is more specific eg. An instance of a car would be “07 ky 1234 Ford Focus”.

[car] - represented on a ER diagram like this.

B)Involuted Relationships:

- where the two entities of the relationship are from the same entity type.

Eg. - Employee supervise many employees

- employee supervised by employer

C)Parallel Relationships:

Where two or more relationships exist between the same two entity types

Eg. - Lecture tutors one module

- or teaches more that one module

Front 15

Explain the term concurrent transactions.

Back 15

Concurrent transactions are transactions that take place simultaneously on a single database.

The transaction manager is responsible for controlling them with a concurrent control protocol to ensure the the database is always left in a consistent state.

Front 16

“Concurrent transactions do not always interfere with each other.” Agree or disagree,justify.

Back 16

I agree with this statement because one of the main properties is of a transaction is isolation.

Isolation allows multiple transactions to occur at once without one being able to see the effects of the other until committed.

It allows both to operate In an isolated manner without interfering with each other.

If a deadlock occurs then I believe the transactions have Interfered with each other however this is unknown to the user our application and is controlled by the DBMS

Front 17

Discuss the features of 2PL locking protocol?

Back 17

Two phased locking is based on the simple rule saying a transaction is not allowed to further lock data once it has already unlocked data.

1st Phase:

Requires permission for all locks needed

2nd Phase:

All locks obtained and the first can be released

3rd Phase:

Transaction cannot get any new locks, can only release the acquired ones.

The two phases -

Growing phase:

Obtain locks but not release

Shrinking phase:

Release locks but no new locks

Front 18

How does a cold backup differ from a warm backup and why might such backup be performed?

Back 18

Cold: -Entire DB down

- All files copied

- Users have no access during

-No updates can be made

Safest way to back up but no user access

Not - done as users continue to access

- data being updated must be noted and only copied once update completes.