1.1 Data Representation 1.2 Multimedia 1.3 Compression
2.1 Networks And The Internet
3.1 Computers And Their Components 3.2 Logic Gates
4.1 Central Processing Unit (CPU) Architecture 4.2 Assembly Language 4.3 Bitwise Manipulation
5.1 Operating Systems 5.2 Language Translators
6.1 Data Security 6.2 Data Integrity
7.1 Ethics and Ownership
8.1 Database Concepts 8.2 Database Management Systems (DBMS) 8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)
9.1 Computational Thinking 9.2 Algorithms
10.1 Data Types and Records 10.2 Arrays 10.3 Files 10.4 Introduction to Abstract Data Types (ADT)
11.1 Programming Basics 11.2 Constructs 11.3 Structured Programming
12.1 Program Development Life Cycle 12.2 Program Design 12.3 Program Testing and Maintenance

Chapter 8 | Databases

8.1 Database concepts

Data Terms

Data security - protecting data against loss
Data privacy - protects data from unauthorized access i.e. keeps data confidential
Data integrity - ensures data is correct, consistent, and accurate
Data redundancy - repeated data

Relational vs. File-Based Approach

Relational database - a database that consists of multiple linked tables

Advantages of relational databases:

File-based database - all the data is stored on one table

Limitations of a file-based approach:

Database Key Terms

Term Definition
Entity A specific object or concept that is represented
and stored in a database, and that can be
identified by a unique set of attributes or
properties
Any object, person or thing about which it is
possible to store data about
Table Stores data about a particular subject, with
rows and columns
Atomic A field containing only one item of data in its
simplest form
Record/tuple/row All the information about an item/entity
Attribute/field/column A characteristic or item of data about an entity
Primary key An attribute/group of attributes used to
uniquely identify a tuple/row
Composite key A key made up of more than one field
Candidate key An attribute that could be used as a primary key
(uniquely identifies a tuple/row)
Secondary key An attribute that is indexed for faster searching
Foreign key Attribute(s) in a table that link to a primary key
in another table to form a relationship
Index A small secondary table used for rapid searching
which contains one attribute from the table being
searched and pointers to the rows in that table

Entity-Relationship Modelling

Entity-relationship diagrams are used to break down the relationships between the tables
in a database. Used in place of stepwise refinement.

placeholder image

Normalisation

First Normal Form (1NF):

Second Normal Form (2NF):

Third Normal Form (3NF):

In order to normalise tables you must change all many-to-many relationships and turn them into one-to-many
relationships using an intermediary table.

A database is fully normalised if:

8.2 Database Management Systems (DBMS)

placeholder

Key DBMS Terms

Term Definition
Referential integrity Prevents the database from referencing data
that does not exist (more information later)
Data management Metadata on the database - only viewed by the DBA
Data modelling A database model is a type of data model that
determines the logical structure of a database and
fundamentally determines in which manner data can
be stored, organized and manipulated
Data dictionary Stores all the information about the database and
metadata about the data. Includes:
  • Fields
  • Table name
  • Rows
  • Data types
  • Validation rules
  • Primary keys and foreign keys
  • Relationships
dbms terms table here **include DBMS and DBA definitions in table**

Entity integrity - ensures that the primary key in a table is unique and the value is not set to null
Referential integrity - Making sure tables do not try to reference data that does not exist.

These are the main software tools that are provided by the DBMS:

Query processor

Developer interface

The Schema

The four parts of a schema are:

A logical schema:

placeholder image placeholder image

Uses of the DBA & DBMS

Ways in which the Database Administrator (DBA) could use the DBMS software to ensure the security:

Security features provided by the DBMS:

These are some non-physical methods used to improve the security of computer systems and databases:

Access rights/usernames and passwords

Firewalls

Auditing

Anti-malware

Application Security (or equivalent)

Physical methods are also often used. These include:

8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

SQL Data Types →

Data Definition Language (DDL) →

Allows you to create the database, its entities (i.e. tables) and alter them:

CREATE TABLE TABLENAME(
      <fieldname> <datatype> (X) NOT NULL,
      ...
      PRIMARY KEY(<primaryID>)
);

Example →

CREATE TABLE STUDENTS(
      <StudentID> <CHARACTER> (4) NOT NULL,
      <Name> <VARCHARACTER> (20) NOT NULL,
      <Age> <INTEGER> NOT NULL,
      <Grade> <INTEGER> NOT NULL,
      <FeePaid> <BOOLEAN> NOT NULL,
      PRIMARY KEY(<StudentID>)
);

Alter Table →

To change the data type of a column →

ALTER TABLE <tablename>
ALTER COLUMN <fieldname>DATATYPE;

To add a new column to an existing table →

ALTER TABLE <tablename>
ADD <fieldname>DATATYPE;

To remove a column →

ALTER TABLE <tablename>
DROP COLUMN <fieldname>DATATYPE;

To add a primary key to a table →

ALTER TABLE <tablename>
ADD PRIMARY KEY (<fieldname>);

To remove a primary key from a table →

ALTER TABLE <tablename>
DROP PRIMARY KEY;

To add a foreign key →

ALTER TABLE < other tablename>
ADD FOREIGN KEY<fieldname>DATATYPE;
REFERENCES<tablename>(<fieldname>);

Example →

ALTER TABLE BOOKING
ADD FOREIGN KEY (MovieID)
REFERENCES MOVIE (MovieID);

Data Manipulation Language (DML) →

allows you to insert data into the database, edit it and perform queries.

INSERT INTO →

Used to add data into a table

INSERT INTO DISCOUNT (AgeBand, Discount)
VALUES ("F", 50);

Deleting using DML →

DELETE FROM <tablename>

DELETE FROM <tablename>
WHERE <condition>

Updating a record →

UPDATE <tablename>
SET <field> = <value>
WHERE <condition>;

SELECT...FROM...WHERE →

Queries are used to get information from the database. They have a SELECT, FROM, WHERE system.

SELECT <field1>, <field2>
FROM <tablename>
WHERE <condition>;

Others may include the ORDER BY function which is used to sort the data in either ascending or descending order.

SELECT <field1>, <field2>
FROM <tablename>
WHERE <condition>;
ORDER BY <field> ASC/DESC

Or other more specific searches using the wildcard characters or keywords:

Character What it represents
% Represents zero or more characters in a query
E.g. LIKE “O%” would search for
anything beginning with an O.
_ Represents a single character
# Represents a single numeric character

Example →

SELECT MovieTitle
FROM MOVIE
WHERE MovieTitle LIKE "A%";

SELECT *
FROM <tablename>

The BETWEEN...AND operators can be used to select values within a specific range. The operators >= and <= can be used to achieve the same result.

WHERE Age BETWEEN 12 AND 24;
or WHERE Age >= 12 AND Age <= 24;

Used with the SELECT...FROM...WHERE operators

Null values can also be used when fields do not have a value (are null) →

SELECT *
FROM <tablename>
WHERE <field> is NULL;

Used to find fields where there is no value

The IN operator is used as multiple OR operators →

SELECT MovieTitle, MovieCategory
FROM MOVIE
WHERE MovieCategory IN ('Action', 'Adventure');

This searches for the Action OR Adventure titles. The IN operator is used as an OR operator.

INNER JOIN →

When joining two tables →

SELECT Table1.FieldName1, Table2.FieldName2
FROM Table1, Table2
WHERE Table 1.Common Field = Table 2.Common Field ;

The field names are joined to the table names.

Example →

SELECT Title, Rating
FROM MOVIE, MOVIESCHEDULE
WHERE MOVIE.MovieID = MOVIESCHEDULE.MovieID
AND MOVIESCHEDULE.ScreenNumber = 3;

When joining three tables →

SELECT Table1.FieldName1, Table2.FieldName2
FROM Table1, Table2, Table3
WHERE Table 1.Common Field = Table 2.Common Field
AND Table 2.Common Field = Table 3.Common Field;

Example →

SELECT BOOKING.CustomerName, MOVIE.MovieTitle, BOOKING.BookingDate
FROM BOOKING, MOVIE
WHERE MOVIE.MovieID = BOOKING.MovieID AND BOOKING.CustomerName = "Bob Smith";

This INNER JOIN query will search for all the bookings made by customer Bob Smith showing the movie title and booking date (with the data being retrieved from the BOOKING and MOVIE tables).

Aggregate Funtions →

Average: Finds the mean of all records

SELECT AVG(field1) AS <identifier>
FROM <tablename>

Sum: Finds the total of all records

SELECT SUM(field1) AS <identifier>
FROM <tablename>

Count: Finds the total of all records

SELECT COUNT(field1) AS <identifier>
FROM <tablename>

Group By →

SELECT CustomerName
FROM BOOKING
WHERE CustomerName LIKE '?o*'
GROUP BY CustomerName
ORDER BY CustomerName ASC;

Query Summary →

In SQL, a typical query would have this following order (sequence):

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...

1.1 Data Representation 1.2 Multimedia 1.3 Compression
2.1 Networks And The Internet
3.1 Computers And Their Components 3.2 Logic Gates
4.1 Central Processing Unit (CPU) Architecture 4.2 Assembly Language 4.3 Bitwise Manipulation
5.1 Operating Systems 5.2 Language Translators
6.1 Data Security 6.2 Data Integrity
7.1 Ethics and Ownership
8.1 Database Concepts 8.2 Database Management Systems (DBMS) 8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)
9.1 Computational Thinking 9.2 Algorithms
10.1 Data Types and Records 10.2 Arrays 10.3 Files 10.4 Introduction to Abstract Data Types (ADT)
11.1 Programming Basics 11.2 Constructs 11.3 Structured Programming
12.1 Program Development Life Cycle 12.2 Program Design 12.3 Program Testing and Maintenance