Databases

An organised collection of data enabling efficient: - Adding -Modification -Deleting -Searching of data

Field that holds unique value for each record in a database.

A field that links to tables together.

A unique identifier which is indexed to allow fast searching

2 or more attributes uniquely identifying a record.

Contains one table. Easy to set up and maintain, but can be inefficient and have repeated data

Ensures all foreign keys represent a valid and existing primary key in parent table

SELECT … FROM … WHERE… ORDER BY…. ASC/DESC WILDCARD (*) = ALL FIELDS

JOIN table2 ON table1.attribute = table2.attribute

CREATE TABLE table1 ( Attribute 1 [Datatype, Null/not null, Primary key] … )

CHAR(n) VARCHAR(n) BOOLEAN INTEGER FLOAT DATE TIME CURRENCY

ALTER TABLE table1 ADD attribute and data types

ALTER TABLE table1 DROP Attribute

ALTER TABLE table1 MODIFY COLUMN Attribute NEW DATATYPE

INSERT INTO COLUMN 1, COLUMN 2,… VALUES(Value 1, Value2…)

UPDATE table1 SET column1 = value1,…. WHERE columnX = value

DELETE FROM table1 WHERE columnX = value

Coming up with the best possible layout for a relational database.

No redundancy Consistent data Records added/removed without issue Complex queries can be carried out

No attributes that contain more than 1 value. Fixed by making more records but causes redundancy

Must be in 1st Normal Form Must be no partial dependencies (no attributes must depend on part of a composite key.) Fix partial dependency by creating another table with field which has the partial dependency and field it is dependent on.

Must be in 2nd Normal Form Contains no non key dependencies (All attributes must depend on only the primary key) Fixed by splitting table until each attribute in a table is only dependent on primary key

A single logical operation performed on data (Create, Read, Update, Delete)

A set of rules that ensure database validation rules are met. Atomicity, Durability, Isolation, Consistency