Databases

Database definition sheet

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;