Relational Databases

First Normal Form

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…