10 Fundamentals of Databases

A property of an entity equivalent to a field in a table

Any item in the system about which data is stored

An attribute that uniquely identifies each record in a table

A primary key that involves more than one attribute

A primary key in another table to create a relationship

• No repeating attributes or groups of attributes • all attributes must be atomic (cannot consist of two data item e.g. first name and surname • this would make it difficult to order by surname

• In first normal form • With no partial dependencies • (this only occurs if primary key is a composite key) • the value of the non-key attribute can be determined by a subset of the primary key but not the entire key

• Second normal form • No non-key dependencies

SELECT attribute FROM table

SELECT attributes FROM tables JOIN table2 ON table1.primarykey = table2.foreignkey

SELECT attribute FROM table WHERE attribute2 = 10 ORDER BY attribute ASC

CREATE TABLE Customers ( CustomerID CHAR(4) NOT NULL PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, Surname VARCHAR (50) NOT NULL, PricePaid CURRENCY Height FLOAT (5, 2) #float with 5 digits and max of two after decimal )

ALTER TABLE Customers ADD Age INT

ALTER TABLE Customers DROP Height

ALTER TABLE Customers MODIFY COLUMN Surname VARCHAR(40) NOT NULL

INSERT INTO Customers(CustomerID, Firstname, Surname) VALUES (“8472”, “Alice”, “Wonderland”)

DELETE FROM Customers WHERE CustomerID = “2342”

UPDATE Customers SET Surname = “Borderland” WHERE CustomerID = “8462”

• Easier to maintain and update a normalised database • No unnecessary duplication of data • Data integrity is maintained e.g. if a person changes their address, the update only needs to be made to one table • Smaller tables and fewer fields means faster searches and saves storage

A system that provides simultaneous access to the database for multiple clients

The formal process of optimally designing data tables by reducing data redundancy and repetition by converting them into normal forms.

• Consistency of database is maintained as only one copy is held • Backup and recovery - as the database is centralised, a server admin can create backups so that if there’s a failure or loss of data the database can be recovered with little or no consequences • Centralised security - limiting and controlling access levels can preserve the integrity of the data in the database

• Record locking • Timestamp ordering • Commitment ordering

Prevents simultaneous access to objects in a database to prevent updates being lost or inconsistencies in the data arising • Record is locked when a user retrieves it for editing or updating • Anyone else attempting to retrieve it is denied access until the transaction is completed