Database and Management Systems (DBMS)

Backup vs Archive

	Backup	Archive
Purpose	Rapid recovery of live, changing data	Stores unchanging data no longer in use but must be retained
No. of Copies	Multiple copies	One copy
Speed	Restore speed is crucial	Retrieval speed not crucial
Retention time	Short term retention	Long term retention
Modifying data	Duplicate copies are periodically overwritten	Data cannot be altered or deleted

Database Management System

A **database **is an organised collection of structured information, typically stored **electronically **in a computer system
A **database **is usually **controlled **by a database management system (DBMS)
A DBMS serves as an **interface **between the **database **and its end users or programs, allowing users to retrieve, update, and manage the information
A DBMS facilitates additional administrative operations such as performance monitoring, tuning, and **backup **and recovery
Three-Level System

Objectives

Allows independent customised user views
- Each user should be able to access the same data
- Views should be** independent**
- Changes to one view should not affect others
Hides the physical storage details from users
- Users should not have to deal with physical database storage details
Database administrators should be able to change storage structures without affecting the users’ views
Internal structure of the database should be unaffected by changes to the physical aspects of the storage
- When shifting a database to a disk, its structure should not be changed

External Level

A user’s view of the database
Describes a part of the database that is relevant to a particular user
Excludes irrelevant data / data user is authorised to access

Conceptual Level

A way of describing what data is stored and how the data is inter-related
Does not specify how the data is physically stored
Controlled by the **database administrator (DBA) **who has access to the DBMS

Internal Level

Involves how the database is physically represented on the computer system
Controlled by the database management system (DBMS) software

ACID (Properties of DBMS)

Atomicity

All changes to data are performed as if they are a single operation
Either all changes are performed, or none are (preventing partial operations)
Eg. Transfer of funds from one account to another – it ensures that if a debit is made successfully from one account, credit is made to the other account.

Consistency

Data is in a consistent state when a transaction starts and when it ends
Eg. Transfer of funds from one account to another – it ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation

The** intermediate state** of a transaction is invisible to other transactions
As a result, transactions that are run concurrently appear to be serialised
Eg. Transfer of funds from one account to another – it ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither

Durability

After a transaction is completed, changes to data persist and are not undone
This is **regardless of **whether the event of a system failure occurs or not
Eg. Transfer of funds from one account to another – it ensures that the changes made to each account will not be reversed.

Pros and Cons

Pros	Cons
- Controlling redundancy - Restricting unauthorised access - Providing persistent storage - Efficient query processing - Reliable recovery systems - Providing multiple interfaces to different classes of users - Representing complex relationships - Enforcing integrity constraints - Permitting inference and actions using rules - Potential for enforcing standards. - Reduced development time - Flexibility to change data structures - Availability of up-to-date information - Sharing of data among users	- High initial investment and possible need for additional hardware - Overhead for providing generality, security, recovery, integrity, etc. - Not required when database is well defined and not expected to change - Not required when access to data by multiple users is not required

Pros

Cons

- Controlling redundancy
- Restricting unauthorised access
- Providing persistent storage
- Efficient query processing
- Reliable recovery systems
- Providing multiple interfaces to different classes of users
- Representing complex relationships
- **Enforcing integrity constraints **
- Permitting inference and actions using rules
- Potential for enforcing standards.
- Reduced development time
- Flexibility to change data structures
- Availability of up-to-date information
- Sharing of data among users

- High initial investment and possible need for additional hardware
- Overhead for providing generality, security, recovery, integrity, etc.
- Not required when database is well defined and not expected to change
- Not required when access to data by multiple users is not required

Relational Database

Definitions

A collection of data organised into a table structure
Allows users to** identify and access data** in relation to another piece of data in the table, or other tables within the database
Tables can be modified, or rows and columns can be added or removed without affecting the rest of the database
Terminology
- A **relation **is a table with rows and columns
- A **tuple **is a row or record of a relation
- An **attribute **is a named column or field of a relation
- A domain is a set of allowable values for each attribute

Properties of Relation

Relation name is distinct from all other relation names in the relational schema
Each attribute has a** distinct name **(from all other attributes in a relation)
Each cell of relation contains exactly one atomic (single) value
Values of an attribute are from the same domain
Each** tuple is distinct**, there are no duplicate tuples
Order of the attributes has no significance
Order of tuples has no significance

Relational Keys

Candidate key: Minimal set of attributes that uniquely identify each tuple
Primary key: Candidate key used to identify tuples which should not change
Alternate key: Candidate key not selected to be primary key
**Foreign **key: Attribute used to reference the primary key of another table

Structured Query Language (SQL)

Standard computer language for the management of relational databases
There are 4 categories of SQL commands
- Data Definition Language (DDL)
  - Defines database schema
  - Creates database, table structure and objects
- Data Manipulation Language (DML)
  - Use to retrieve and modify data
- Data Control Language and Transaction Control Language

Data Types

Storage Class	Meaning
NULL	Missing information
INTEGER	Whole numbers (positive or negative)
REAL	Decimal numbers
TEXT	Character data
BLOB	Binary large object

Data Definition Language

Create Table

CREATE TABLE IF NOT EXISTS table_name(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    money REAL NOT NULL,
    percent REAL DEFAULT 1.0 CHECK(percent > 0 AND percent < 1),
    fkey INTEGER NOT NULL,
    FOREIGN KEY (fkey) REFERENCES ftable(id)
);

Alter Table

ALTER TABLE table_name
RENAME TO new_table_name;

ALTER TABLE table_name
DROP COLUMN money;

Drop Table

DROP TABLE IF EXISTS table_name;

Data Manipulation Language

Select Statement

SELECT * FROM table_name
WHERE text = 'something'
AND percent = 1.0
ORDER BY fkey ASC;

For **LIKE **statements, percentage signs represent where extra characters are valid
- For ‘%TAN’, something like ‘TANAF’ will not be valid
- For ‘%TAN%’, everything containing ‘TAN’ will be valid

SELECT * FROM table_name
WHERE text LIKE 'TAN%';

For **DISTINCT **statements, they are placed before the column names

SELECT DISTINCT class_name
FROM table_name;

For **IN **conditions, they accept a list in the form of a tuple

SELECT * FROM table_name
WHERE id IN (1, 2, 3, 4);

For **COUNT **statements, they return the number of rows in a group

SELECT COUNT(*) FROM table_name
WHERE text = 'test';

For **AVG **statements, they return the average of non-null values in a group

SELECT AVG(column_name) FROM table_name
WHERE text = 'test';

For **MAX **/ **MIN **statements, they return the minimum or maximum of non-null values in a group

SELECT MIN(column_name) FROM table_name
WHERE text = 'test';

For **GROUP BY **statements, they separate the outputs by a specified column (should be placed after WHERE)

SELECT COUNT(*) FROM table_name
WHERE text = 'test'
GROUP BY class_name;

Join Statement

SQL allows a selection of data to return a combination of multiple tables
These include
- Cross Join
- Inner Join
- Left Outer Join

Cross Join

Cross join combines each row in the first table with each row in the second table (both are correct)

SELECT * FROM table1_name, table2_name;

SELECT * FROM table1_name
CROSS JOIN table2_name;

Inner Join

Inner join returns only rows where the join condition is met in both tables.

SELECT * FROM table1_name
INNER JOIN table2_name
ON table1_name.fkey = table2_name.pkey;

Left Outer Join

Left outer join returns all records from the left table and the matching records from the right table

SELECT * FROM table1_name
LEFT OUTER JOIN table2_name
ON table1_name.fkey = table2_name.pkey;

Insert Statement

Inserts a set of data values into the specified table
Columns should be specified unless inserting into all columns in order

INSERT INTO table_name (id, text, money)
VALUES (10, 'test', 123.45);

INSERT INTO table_name
VALUES (10, 'test', 123.45, 0.5, 2);

Update Statement

Update a set of data in a specified row in a specified table

UPDATE table_name
SET money = 10
WHERE id = 1;

Delete Statement

Delete a specified row in a specified table

DELETE FROM table_name
WHERE id = 1;

Normalisation

Data Redundancy

Data Redundancy refers to the **same data are being stored more than once **

Data Integrity

Data redundancy will cause** issues when inserting, updating and deleting** data
Data redundancy causes data anomalies
Data anomaly develops when not all** of the required changes in the redundant data are made successfully**

Types of Data Anomalies

Insertion Anomalies

New data cannot be inserted into a table without the presence of other data
Due to redundant storage of information, inserting a new student record may require dummy values for class if no class data exists yet
Leads to inconsistent or incomplete data when placeholder values are used

Update Anomalies

When data is duplicated, a single logical change requires multiple physical updates
The same piece of information is stored in multiple rows
If not all instances are updated, this leads to inconsistent or outdated data

Deletion Anomalies

Deleting a record inadvertently removes additional useful information
Data that should be stored independently is grouped together
Deleting all students from a class also deletes the only record of that class

Preventing Data Redundancy

Normalise tables to avoid anomalies by analysing functional dependencies
Functional dependencies help ensure that every attribute in a table is non-redundant
If B depends on A, it means the value of B is determined by A — this helps in organising data efficiently

Dependencies

Functional Dependency

Functional dependency (FD) in a database enforces constraints between attributes
This occurs when attribute X in a relation uniquely determines attribute Y
This can be written as X → Y , which means Y is functionally dependent on X
For any relation R, attribute Y is functionally dependent on attribute X, if for every valid instance of X, that value of X uniquely determines the value of Y

Transitive Dependency

X → Z is a transitive dependency if the following dependencies hold true:
- X determines Y (X → Y)
- Y does not determine X (Y not → X)
- Y determines Z ( Y → Z)
In a database, this means that a column’s value relies upon another column through a second intermediate column
A transitive dependency can only occur in a relation of three or more attributes

Normalisation

Normalisation is the process of organising the tables in a database to reduce data redundancy and prevent inconsistent data
The goals of normalisation are to provide mechanisms for transforming schemas in order to remove redundancy
Each normal form involve dependency properties that a schema must satisfy
Higher normal forms have less redundancy and fewer update problems

First Normal Form (1NF)

1NF states that
- The domain of an attribute must include only atomic values
- The value of any attribute in a record must be a single value
Relations with relations OR relations as attribute values within tuples are not allowed
The only attribute values permitted by 1NF are single atomic or indivisible values

Second Normal Form (2NF)

For 2NF, the relation must first be in 1NF
2NF states that :
- All non-key attributes are functionally dependent on the primary key
- If the relation has a composite PK, then each non-key attribute must be fully dependent on the entire composite PK and not on a subset of the PK
Partial functional dependencies are NOT allowed

Third Normal Form (3NF)

For 3NF, the relation must first be in 2NF
3NF states that:
- All transitive dependencies must be removed
- The table will contain only columns that are non-transitively dependent on the primary key
A non-key attribute may not be functionally dependent on another non-key attribute

Entity Relationship Diagrams

ER Data Model

The entity relationship data model is is easy to discuss and explain
ER models are readily translated to relations/tables.
ER models are represented by ER Diagrams

Components of ERDs

Entity

An entity is a specific object of interest
Nouns are usually used to name entities
Entities are represented by rectangles in ERD

Relationship

A relationship is an association between two entities
Relationships between entities always operate in both directions

Types of Relationship

Type	Definition	Diagram
One-to-One	One instance of X is linked to one instance of Y	XY X Y
One-to-Many	One instance of X can be linked to many instances of Y One instance of Y is linked to one instance of X	XY X Y
Many-to-Many	Many instances of X can be linked to many instances of Y	XY X Y

Steps to Creating ERDs

Identify the entities
Identify the relationships between entities
Decide the connectivity of the relationships
Refine Many-to-Many Relationships

Quartz 5

Explorer

01 Database and Management Systems (DBMS)

Database and Management Systems (DBMS)

Backup vs Archive

Database Management System

Objectives

External Level

Conceptual Level

Internal Level

ACID (Properties of DBMS)

Atomicity

Consistency

Isolation

Durability

Pros and Cons

Relational Database

Definitions

Properties of Relation

Relational Keys

Structured Query Language (SQL)

Data Types

Data Definition Language

Create Table

Alter Table

Drop Table

Data Manipulation Language

Select Statement

Join Statement

Cross Join

Inner Join

Left Outer Join

Insert Statement

Update Statement

Delete Statement

Normalisation

Data Redundancy

Data Integrity

Types of Data Anomalies

Insertion Anomalies

Update Anomalies

Deletion Anomalies

Preventing Data Redundancy

Dependencies

Functional Dependency

Transitive Dependency

Normalisation

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Entity Relationship Diagrams

ER Data Model

Components of ERDs

Entity

Relationship

Types of Relationship

Steps to Creating ERDs

Refinement of Many-to-Many relationships

Graph View

Table of Contents

Backlinks