Databases

Database concept

• A database is a persistent organised store of data

• A data can be stored in flat files (filing cabinets, spreadsheets etc.) or in tables (aka entities) and connected by 'relationships' (relational database)

Problem with flat file databases:

• Flat files (filing cabinets, spreadsheets etc.) mean that we often have to repeat data in each record​

• This leads to inconsistencies in the data – these make it hard to search or sort the data​

• This also causes redundant data – so the database uses more memory or storage than it needs to, it may also take longer to search​

Data types

Each field can contain a type of data. The possible database data types to choose ​from are:​

• Integer (whole number)​

• Real, Float, Decimal (number with a decimal component)​

• Date, Time, Datetime (to store a dates and times)​

• Char (fixed length string up to 8,000 characters)​

• Varchar (variable length string up to 8,000 characters)​

• Text (variable length string up to 2 GB of data)

Key fields

• A primary key is a field that stores unique data for each record in a table to enable each record to be referenced

• A foreign key is a field that contains values from a primary key in another table

SQL (Structure Query Language)

SELECT CustomerName, City, Country

FROM Customers

WHERE Country = 'Germany'

ORDER BY CustomerName ASC

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

UPDATE Customers

SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'

WHERE CustomerID = 1;