8. Databases
1. Introduction to Databases
A database is a structured/organised way to store data so that it can be retrieved easily using queries
● A record/tuple is a single row/entry of data
in the table, about one instance of an object
● A field/attribute is a column of data or an
attribute of a record
● An entity is an object that can have data
stored about it e.g. an animal - a tuple is one
instance of an entity
Data types:
● CHAR(n) - fixed length text ofncharacters (or singlecharacter)
● VARCHAR(n) - variable length text uptoncharacters
● BOOLEAN - true or false (1 or 0)
● INTEGER - whole number
● REAL - number with decimal value
● DATE - date, typically formatted as YYYY-MM-DD
● TIME - time, typically formatted as HH:MM:SS
Flat-file database:
● A flat file database stores a single table of data inside a single text file
● Flat file databases are often stored using a .csv (comma separated values) format
○ Each record appears on a separate line
○ Each field is separated by a comma
● This format is very easy to set up, however is hard to manage for anything remotely complicated
● However, there are two problems with flat-file databases:
○ Data redundancy - the same data is repeated unnecessarily e.g. in a students database,
multiple will have the same form/house/form teacher/form room etc.
○ Data inconsistency - when redundant data causes errors in data such as update errors e.g.
one person has Dr Quirke as their form tutor, and another has Dr Qirke
Keys:
● K eys are pointers used to refer to specific records in database tables
● They minimise the number of copies of data needed and provide relationships between tables
● Candidate key - an attribute in a table where no tuple has the same value (unique), so could be
the primary key
● Primary key - the designated field that stores unique data for each record in a table to be able to
identity each record/tuple e.g. StudentID
● Secondary key - a candidate key field that could be a primary key (unique and not null) but has
not been chosen - may be used to locate specific data
● Foreign key - a field in one table that is a primary key of another table in the database, used to
form relationships between the tables (used for relational databases)
● An index is a data structure which uses one or more columns/fields (e.g. primary key/secondary
key) to speed up searching for records in a database table
, 2. Introduction to SQL
DBMSs (Database Management System) use a data definition language and data manipulation language:
● DDL (Data Definition Language) is used for working on the database structure (create/modify/
remove data structures that form a relational database) - DDL statements are written as scripts
● DML (Data Manipulation Language) is used to work with the data stored in the database
(add/modify/delete/retrieve data stored in a relational database)
● SQL is the industry standard commonly used for both
QL (Structured Query Language) is a language used to create, query, update and delete data to and
S
from databases
● SQL is used within most Database Management Systems e.g. MySQL, SQL Server and MS Access
● SQL queries can be used within high level programming languages e.g. Python, Visual Basic, C#
SELECT statement (DML query commands):
● The SELECT statement is used to extract fields from one or more tables
○ SELECT - list of fields to be displayed
○ FROM- list the table or tables the data will come from
○ WHERE - list of search criteria
■ Operators: = <> > < >= <= AND, OR, NOT
■ LIKE is used to search for a pattern
● % can represent 0, 1 or more characters
● _ represents one character
● E.g. WHERE FirstName LIKE “W%e_”
■ BETWEEN is for between an inclusive range e.g. WHERE ID BETWEEN 1 AND 5
■ IN used to specify multiple values
■ ORDER BY - list the fields that the data is to be sorted on
■ ASC/DESC e.g. ORDER BY ProductID DESC
■ Note: use ‘_’ for single chars and dates (or sometimes #), otherwise use “_"
● The wildcard (*) can be used to select all the fields
e.g. SELECT *
FROM Students
WHERE House IN (“Evans”, “Black Hawkins”) AND Form LIKE “T__3” AND
Teacher LIKE “Mrs%” AND Age BETWEEN 16 AND 17
ORDER BY Pupil ID DESC;
INSERT INTO statement (DML maintenance command):
● Used to insert a new record into a table
INSERT INTOTableName(FieldNames)
VALUES (Values)
e.g.
INSERT INTO Students (ID, FirstName, House)
VALUES (32, “William”, “Evans”);
UPDATE statement: DELETE statement:
● Used to update a record in a table ● Used to delete a record in a table
UPDATETableName DELETE FROMTableName
SETField1=Value1, Field2 = Value2 … WHEREFieldName=Value
WHEREFieldName=Value e.g.
DELETE FROM Students
e.g.UPDATE Students WHERE Name = “William”;
SET House = “Olders”
WHERE Name = “William”;
1. Introduction to Databases
A database is a structured/organised way to store data so that it can be retrieved easily using queries
● A record/tuple is a single row/entry of data
in the table, about one instance of an object
● A field/attribute is a column of data or an
attribute of a record
● An entity is an object that can have data
stored about it e.g. an animal - a tuple is one
instance of an entity
Data types:
● CHAR(n) - fixed length text ofncharacters (or singlecharacter)
● VARCHAR(n) - variable length text uptoncharacters
● BOOLEAN - true or false (1 or 0)
● INTEGER - whole number
● REAL - number with decimal value
● DATE - date, typically formatted as YYYY-MM-DD
● TIME - time, typically formatted as HH:MM:SS
Flat-file database:
● A flat file database stores a single table of data inside a single text file
● Flat file databases are often stored using a .csv (comma separated values) format
○ Each record appears on a separate line
○ Each field is separated by a comma
● This format is very easy to set up, however is hard to manage for anything remotely complicated
● However, there are two problems with flat-file databases:
○ Data redundancy - the same data is repeated unnecessarily e.g. in a students database,
multiple will have the same form/house/form teacher/form room etc.
○ Data inconsistency - when redundant data causes errors in data such as update errors e.g.
one person has Dr Quirke as their form tutor, and another has Dr Qirke
Keys:
● K eys are pointers used to refer to specific records in database tables
● They minimise the number of copies of data needed and provide relationships between tables
● Candidate key - an attribute in a table where no tuple has the same value (unique), so could be
the primary key
● Primary key - the designated field that stores unique data for each record in a table to be able to
identity each record/tuple e.g. StudentID
● Secondary key - a candidate key field that could be a primary key (unique and not null) but has
not been chosen - may be used to locate specific data
● Foreign key - a field in one table that is a primary key of another table in the database, used to
form relationships between the tables (used for relational databases)
● An index is a data structure which uses one or more columns/fields (e.g. primary key/secondary
key) to speed up searching for records in a database table
, 2. Introduction to SQL
DBMSs (Database Management System) use a data definition language and data manipulation language:
● DDL (Data Definition Language) is used for working on the database structure (create/modify/
remove data structures that form a relational database) - DDL statements are written as scripts
● DML (Data Manipulation Language) is used to work with the data stored in the database
(add/modify/delete/retrieve data stored in a relational database)
● SQL is the industry standard commonly used for both
QL (Structured Query Language) is a language used to create, query, update and delete data to and
S
from databases
● SQL is used within most Database Management Systems e.g. MySQL, SQL Server and MS Access
● SQL queries can be used within high level programming languages e.g. Python, Visual Basic, C#
SELECT statement (DML query commands):
● The SELECT statement is used to extract fields from one or more tables
○ SELECT - list of fields to be displayed
○ FROM- list the table or tables the data will come from
○ WHERE - list of search criteria
■ Operators: = <> > < >= <= AND, OR, NOT
■ LIKE is used to search for a pattern
● % can represent 0, 1 or more characters
● _ represents one character
● E.g. WHERE FirstName LIKE “W%e_”
■ BETWEEN is for between an inclusive range e.g. WHERE ID BETWEEN 1 AND 5
■ IN used to specify multiple values
■ ORDER BY - list the fields that the data is to be sorted on
■ ASC/DESC e.g. ORDER BY ProductID DESC
■ Note: use ‘_’ for single chars and dates (or sometimes #), otherwise use “_"
● The wildcard (*) can be used to select all the fields
e.g. SELECT *
FROM Students
WHERE House IN (“Evans”, “Black Hawkins”) AND Form LIKE “T__3” AND
Teacher LIKE “Mrs%” AND Age BETWEEN 16 AND 17
ORDER BY Pupil ID DESC;
INSERT INTO statement (DML maintenance command):
● Used to insert a new record into a table
INSERT INTOTableName(FieldNames)
VALUES (Values)
e.g.
INSERT INTO Students (ID, FirstName, House)
VALUES (32, “William”, “Evans”);
UPDATE statement: DELETE statement:
● Used to update a record in a table ● Used to delete a record in a table
UPDATETableName DELETE FROMTableName
SETField1=Value1, Field2 = Value2 … WHEREFieldName=Value
WHEREFieldName=Value e.g.
DELETE FROM Students
e.g.UPDATE Students WHERE Name = “William”;
SET House = “Olders”
WHERE Name = “William”;
