Fundamentals of Databases

Database

They are a collection of organised information so the information can be easily accessed, processes and managed.

Database Management System (DBMS)

It is a software package designed to define, manipulate, retrieve and manage data in a database

Entity

It is one type of object.

Attribute

They describe the entity

They are also called fields or columns.

How is a database table organised?

In records, they can also be called rows.

Data Models

They define how data is connected to each other and how they are processed and stored inside the system.

Designed to allow easy data input.

Relational Database

It is a database that has been designed and structured so that the relationships among different entities are identified by shared attributes among different entities.

Primary Key

It is a unique identifier for a record

Foreign Key

It is a primary key from a different table used to define the relationship.

Composite Key

It is a combination of two or more attributes in a table that can be used uniquely to identify each record in the table.

Relationship

One-to-One

One entity can relate to the other entity exactly once.

Relationship

One-to-Many

One entity can relate to the other entity more than once.

Relationship

Many-to-Many

One entity can appeal multiple times in the other entity and vice versa.

First Normal Form (1NF)

It has no repeating attribute/field or groups of attributes.

It contains only atomic values.

Second Normal Form (2NF)

It is in the first normal form and all the *non-key attributes* are fully functional dependant on the primary key.

This is partially dependant on the primary key only occurs if there is a composite key.

Third Normal Form (3NF)

It is in second normal form and there is no non-key dependancies or no attributes depending on other attributes.

Database Normalisation

To reduce data redundancy.

To maintain data integrity.

Smaller tables, faster indexing, fast sorting, searching, updating.

No accidental deleting a record.

DDL (Data Definition Language)

It is a standard for commands that define the different structures in a database.

Statements create, modify and remove database objects like tables.

Inner join

Client-Server Database Model

It is a centralised database server dealing with many requests from different clients on different PCs

Advantages of using a Client-Server Database Model

Avoids data inconsistency since only one copy of the database is accessed.

All updates will be immediately available to all clients.

Avoid expensive cost of resources required to have multiple copies of a database.

Access rights and security can be centrally managed.

Disadvantages of using a Client-Server Database Model

When clients update the same record at the same time, some updates will be lost.

To prevent data inconsistency, a DBMS uses *record locking* mechanism - whenever a record is being updated, it is being locked until the transaction is completed or cancelled.

Serialisation

It is a mechanism to ensure there is not transactions overlap in time. A transaction cannot start until the previous one has finished.

Serialisation Techniques

Timestamp Ordering

A timestamp is given at the start of each transaction. For two transactions updating the same record, the transaction with the earlier timestamp will be applied first.

Serialisation Techniques

Commitment Ordering

Transactions are ordered by their dependencies on each other as well as the time they started.

If the transaction has dependencies on another, it it blocked until the dependent-upon transactions have finished/committed.