Limitations

• difficult to search for data
• data redundancy: data is needlessly repeated
• no restriction of entered data; data may be incorrect
• lack of data privacy: confidential data will not be omitted in a file-based storage as all data is shown

Other

• does not support multiple users
• cannot store more than one table

Relational database

A database structured to recognise relations between stored items of information.

• assigns unique identifiers to each item (a key)
• contains more than one table
  • these tables can be linked together by a key field

Domain integrity

• maintained by selecting the correct data type for each field

Other methods

• setting up appropriate constraints and rules to define the data format
• restricting the range of possible values.

User-defined integrity

• allows users to apply rules to the database
  • e.g: business requirements

Data security

• handles password allocation and verification
• backs up database automatically
• controls what users see depending on their access rights

Database backup

• data records are copied to an offline storage
  • this copy of data can be used to restore and recover the data after a failure

Query processor

• query processing consists of 3 steps:
  • parsing
  • optimisation
  • execution

Parsing

• handles high-level queries
• translates them into low-level language that DBMS can understand

Optimisation

• optimises queries: determines the most efficient way of executing a query
  • this results in a query plan

Execution

• final step of processing
• executes the query plan and retrieves data from the database
  • returns the result

Developer interface

• allows the creation and manipulation of databases in code (SQL) rather than graphically

SQL

• an example of a high-level language (see 1.8.3 SQL)

1NF

Also known as First Normal Form.

• remove records (rows) that have the same data
• separate out fields (columns) that contain repeating data
• each record (row) must be unique

2NF

Also known as Second Normal Form.

• assign a primary key
• make every non-primary field fully dependent on the primary

3NF

Also known as Third Normal Form.

• ensures that there is no non-key attribute dependent on another non-key attribute
  • a new table must be defined if there is

DDL

Also known as Data Definition Language.

• part of SQL provided for creating and altering tables
• commands only create structure; cannot manipulate data

Commands

CREATE DATABASE BandBooking;

CREATE TABLE Band 

(

    BandName varchar2(25),

    NumberOfMembers number(1)

);

ALTER TABLE Band ADD PRIMARY KEY (BandName);

ALTER TABLE Band-Booking ADD FOREIGN KEY (BandName) REFERENCES Band(BandName);

CREATE

DATABASE - creates a database

TABLE - creates a table

ALTER

TABLE

• add primary keys
• add foreign keys
• add attributes

DML

Also known as Data Manipulation Language.

• used when a database is first created
• queries and management can be done using DML
• populates tables with data
  • adding (inserting)
  • deleting
  • modifying

Commands

Data maintenance

INSERT INTO Band ('ComputerKidz', 5);

INSERT INTO Band-Booking (BandName, BookingID)

    VALUES ('ComputerKidz', '2016/023');

UPDATE Band

    SET NumberOfMembers = 6;

DELETE FROM BandName

    WHERE BandName = 'ITWizz';

Queries

SELECT BandName

    FROM Band

        ORDER BY BandName;

SELECT BandName

    FROM Band-Booking

        WHERE Headlining = 'Y'

            GROUP BY BandName;

SELECT

selects an attribute

• a query always starts with SELECT

FROM

locates attribute's table: 'Band'

ORDER BY

produces an ordered list, sorted by 'BandName'

WHERE

identifies items in 'BandName' that have the Headlining attribute as 'Y'

GROUP BY

groups (puts together) these identified items by 'BandName'