S10 - Relational Databases

First Normal Form

Data must be atomic for each attribute in a record

Second Normal Form

Data must be atomic, every non key attribute must be depended on the whole of the primary key (single primary key tables that are in 1NF are automatically in 2NF)

Third Normal Form

Data must be atomic, every non-key attribute must be dependent on the whole of the primary key and non-key attributes are functionally independent

Why are databases normalised?

To minimise data duplication, eliminate update, insertion, and deletion anomalies, eliminate data inconsistency

Databases

Structured way of storing, managing and retrieving data/information

‘Flat file’ databases

One long text/csv file, has data redundancies and inconsistencies

Entity

Category of objects about which data will be stored

Attribute

Characteristic about an entity, which would be stored as a field

Record

All characteristics of a specific object. They are the rows in a table

Primary key

Must be a unique characteristic for that specific record, is a unique identifier for a record

Composite Key

When the primary key of a relation is made of two or more attributes

Foreign Key

Attribute that links two tables. When two tables are in a one-to-many relationship, that relationship is modelled with an attribute

select specifics (SQL)

SELECT…FROM…WHERE…ORDER BY…

select and group (SQL)

SELECT…FROM…GROUP BY…

Data Definition Language (DDL)

Defines how the data is stored, organised and managed in the database

Data Manipulation Language (DML)

Manipulates the data, updates/changes it, selects subsets

CHAR(n)

Char string of fixed length n

VARCHAR(n)

Character string variable length max n

BOOLEAN

TRUE or FALSE

INTEGER, INT

Integer

FLOAT

Number with a floating decimal point

DATE

Stores Day, Month, Year values

TIME

Stores Hour, Minute, Second

MONEY, SMALLMONEY

Formats numbers in the currency used in your region. MONEY uses 8 bytes, SMALLMONEY uses 4 bytes

Deleting a column

ALTER TABLE...DROP…

Changing data type of a column

ALTER TABLE…MODIFY COLUMN…(VARCHAR(n))NOT NULL

Inserting a new record

INSERT INTO…VALUES…

Updating data

UPDATE…SET…WHERE…

Deleting a record

DELETE FROM…WHERE…

Client-Server Databases

Multiple users need to be able to access the database and use it

Concurrent Access

When multiple users try to access the database at the same time

Record Locks

When a user starts to access a record, an exclusive lock is set on the record. Other users can’t edit the records until the lock is released (when the first transaction is completed)

Serialiation

Transactions are allowed to take place one at a time in serial format. Process is managed by DBMS. Ensures each transaction is carried out in the correct sequence to avoid compromising the integrity of data.

Timestamp Ordering

• Timestamps are generated for each transaction, indicates the order that transaction occurs in

• Database records timestamp of the last read/write transaction for each record

• Database server applies rules to determine if processing a transaction will result in loss of data integrity/inconsistency

  • if a transaction tries to write to a record then the transaction should be aborted if the read/write timestamp on the record is greater than the timestamp at which the transaction has started

Commitment Ordering

Look at each command it has been asked to execute on the database in terms of when it was made and also in terms of whether it should take precedence over other commands. Depends on nature of the command and the impact it has on the database