Databases Revision

Página 1

A superkey is an attribute or a combination of attributes containing unique values for each tuple.

A alternate key is any candidate key that is not chosen as the primary key.

Two relations are union compatibility when they are of the same degree and they have the corresponding attributes having been defined in the same domain.

Entity Integrity Constraints specifies that the no primary key value can be null and the primary value must be unique.

Referential Integrity Constraints: Maintains consistency between the relations. If a foreign key exists, its value must either refer to an existing record in the relation it references or be wholly null.

Data Manipulating Language (DML): Data manipulating statements are used to managing data. Eg. UPDATE: existing data with a table.

Data Definition Language (DDL): Data definition language statements are used to define the database structure. Eg. ALTER: the structure of the database.

Physical Data Independence: Immunity of the Conceptual schema to changes in the physical/internal schema.

Logical Data Independence: Immunity of the external schema to changes in the conceptual schema.

Three Level ANSI-SPARC database Architecture is to separate each user's view of the database from the way the database is physically represented. It is desirable because Each users should be able to access the same data but have the view that is relevant to them. Also, users should not have to deal with physical database storage system issues such as file structure.

A base relation (table) actually contains data. A view is a query over one (or more) base relations but does not actually contains any data itself. As view can provide several benefits such as it can hide complexity as well as it can be used as a security mechanism.

Data Redundancy refers to unnecessary duplication of data. It causes wasteful storage and which results in loss of integrity.

Domain Constraints specify the value of each attribute must be an atomic value from a specific domain.

Selecting a DBMS: Need to undertake analysis of user requirements. Define objectives and scope of study. Identify possible products. Shortlist products against criteria. Evaluate products against criteria. Evaluate products more thoroughly. Recommend selection.

Three basic types of file organisation: Heap (unordered), Sequential (ordered), and hash (direct).

Heap (unordered): Advantage, Insertion is efficient good for bulk-loading of records. Disadvantages, Searching is problematic and deletion of records also causes problems.

Sequential (Ordered): Advantage, Searching is easier. Disadvantage, Insertion and deletion can be problematic.

Hash (direct): Advantage, results in an even distribution of records throughout the file. Disadvantage, Collisions.

Types of Indexes: Primary (Ordering key field), Clustering (Ordered on a non-key field), Secondary (non-ordering field).

Advantage of Index: Allow the DBMS to locate particular records in a file more quickly.

Disadvantage of Index: Both the data and index need to be updated on inserts and deletes.

Creation of Index: Create index when the attributes are frequently accessed or frequently involved in WHERE clauses, ORDER BY or GROUP BY. It is a bad idea to create index when the attributes are frequently changing.

SQL Statement for privileges: GRANT select, update, insert on to , WITH GRANT OPTION;

Denormalisation: When all information is in one table it is good to denormalise as well as the values are not frequently changing.

Transaction Management (ADBD): Atomicity - All operations of a transaction must be completed or aborted. Durability - Once a transaction has been committed, we cannot rollback. Binary lock- If transaction request for a lock state will be 1, once transaction is complete the state is 0. Deadlock - When two transactions wait for each other to release data item.

Lost update problem: Bob 10-7 Erci 10-5, Bob updater has been overridden by Eric.

Two phase locking helps to prevent lost update problem but not deadlocks. By having the growing phase which get all locks and shrinking phase that releases all locks.

Dirty data - Aging effect / change of requirements. Eg. Traditional Data VS. Modern Data.

Cleaning Dirty Data - Having Constraints and using metadata.

Data warehouse: Data warehouse can add value and historical warehouse helps in decision making.

OLTP (OnLine Transaction Processing): Not suitable for data warehousing as it is mainly used for operational level.'

Mandatory Access Control (MAC): Sets privileges to database objects. For security clearance.

Discretionary access control (DAC): Sets privileges to users.