Tr.

Ni Htwe CIE-CS

Chapter - 9

Single Table Database

• A database is a structured collection of data so it can be searched, sorted,

filtered and analysed quickly.
o Data in a database can be any type of data including text, images, videos,
sound
• Databases use tables to store data.
• Tables have records of data represented by one row.
o In the example below, each row represents the data stored about a single
customer (the customer’s record).
o In the customer table, there are 3 records.
o Each record is divided into fields (CustomerID, FirstName, LastName, DOB
and Phone Number).
• Fields are represented by the columns in a table
o There are 5 fields in the customer table
o The first row in a table contains the field names which is the heading for the
data stored in that field
o Each field in a table has a data type which defines what data can be entered
into that field

Data Types
• Each field in a table, has a data type
o Common data types include text/alphanumeric, character, Boolean,
integer, real and date/time
o Phone numbers have to be assigned the text/alphanumeric data type
because they begin with a 0 and not used for calculation. It may contain some
symbol such as space, +, -
 If you assigned the data type Integer to a phone number it would
remove the initial 0.

1
Tr. Ni Htwe CIE-CS

Primary Keys
• Each table has a primary key field which acts as a unique identifier
o Each item of data in this field is unique
o Duplicate data items would be blocked if they were entered into the primary
key field
• Because the items of data are unique within the primary key field they can be used
to identify individual records

• In the example customer table, the primary key would be the CustomerID because
each customer’s ID is unique.

SQL
• Records in a database can be searched and data can be manipulated using
Structured Query Language (SQL)
• SQL statements can be written to query the data in the database and extract useful
information
• SQL statements follow this structure:
o SELECT the fields you want to display
o FROM the table/tables containing the data you wish to search
o WHERE the search criteria

2
Tr. Ni Htwe CIE-CS

Example

SELECT Name, Rating

FROM Movie
WHERE Rating>8.4;

The results of this query would be:

• There are several other comparison operators which can be used to create
the filter criteria in the WHERE line of a SQL query

Example

SELECT Name,Rating
FROM Movie
WHERE Genre= “Family” AND Certificate= “U”;

The results of this query would be:

3
Tr. Ni Htwe CIE-CS

• This query uses the AND logical operator to include multiple criteria in the
WHERE line of the SQL query
• Another logical operator which can be used in the WHERE statement is OR
o For example, WHERE Genre= “Comedy” OR Genre= “Family”.

ORDER BY
• Data can be displayed in order using the ORDER BY command, followed by ASC or
DESC
o If you enter ASC the results of the query will be sorted in ascending order
o If you enter DESC the results of the query are sorted in descending order

Example

SELECT Name,Genre, Certificate, Rating

FROM Movie
ORDER BY Name ASC

• The results of this query would be:

• The records are sorted by Name alphabetically

SUM and COUNT

• The SUM command can be used to add numerical data
• The COUNT command can be used to count items of
data

4
Tr. Ni Htwe CIE-CS

Example

SELECT SUM(QuantityInStock)
FROM ProductTable;

• This query will add up all of the numbers in the QuantityInStock field
o The result of this query would be 25

Example

SELECT COUNT(*)
FROM ProductTable
WHERE Price>2;

• This query will count all the records with a price greater than 2
o The result of this query would be 3
o This is because there are three products with a price greater than £2 (Chips,
Beans, Bananas)

Validation and Verification

• Verification is used to check whether the data that has been entered is the correct
data and is reasonable.
o This is often completed by getting data entered by one person is then
checked by another person.
• When a table is created, validation rules can be assigned to the different fields
o There are different types of validation checks used to limit what data can be
entered into each field

Different types of validation check