Database (8)
Database Concepts -
File-based system; refers to any system that stores data in a separate file.
Limitations:

Relational Database; It is where data is stored and organised in different tables compared to flat file database.
prevents data redundancy (data is duplicated)
Is more efficient
easier to store and maintain.

Term | Definition |
Entity | A real-world object or concept that data is stored about(e.g. Student, Book) |
Table | A collection of data about an entity organised in rows and columns. |
Record (Tuple) | A single row in a table representing the instance of the entity. |
Field (Attribute) | A single column that stores one piece of data about the entity. |
Primary Key | A unique identifier for each record in the table. |
Candidate Key | a field/s that can be used as a primary key. |
Secondary key | A key for searching or sorting not really unique. |
Foreign key | A key that links to the primary key of another table to create a relationship. |
Relationship | The logical connection between two tables/entities |
One to One | One record in a table relates to a record in another table |
One to Many | One record in a table relates to many records in another table. |
Many to Many | Many records in one table relates to many records in another table. |
Referential Integrity | Ensures the foreign key matches the primary key in related table so there is no broken link. |
Indexing | A speed up technique to search a table by creating ordered list of key fields. |
Entity-Relationship Diagram
A diagram that represents entities in a database and their relationships.

Normalisation
organises data efficiently.
Prevents duplication
Ensure data integrity
Supports easy update and accurate relationships.
First Normal Form (1 NF) -
It is where columns contain single values and are separated
Must contain a primary key
Have unique column names
Have no repeating groups

Second Normal Form (2 NF) -
No partial dependencies
Full functional dependency
Only applied to table with a compound primary key.

Third Normal Form (3NF) -
There is no transitive dependencies (where non-prime attributes depend on other non-prime attributes.)
Each non-prime attributes depend on the primary key.
Have a separate table for attributes with transitive dependencies and linked using a foreign key.
The table is NOT in 3NF:
FilmID | Title | Certificate | Description |
12034 | SawIV | 18 | Eighteen and above |
12035 | Spiderman 2 | 12A | Age 12 and above |
12036 | Shrek | U | Universal |
This table is in 3NF:

Database Management System (DBMS) -
Area | Limitations in file-based system | How DBMS solves it |
Data management | Each applications stores and manages its own data | Has a data dictionary that defines data across the whole system |
Data modelling | No structured way to represent or link data | Uses data modelling tools that defines entities and relationships. |
Logical Schema | The overall logical design of the database | Shows how data is organised and used independently of how it is physically stored. |
Data integrity | Difficult to enforce validation rules across different files | Uses constraints to enforce intergrity |
Data security | Poor control on who can access and change data | Supports user accounts, access rights and permissions for individuals. |
Backup procedures | often manual and inconsistent | Automated backup and recovery tools to prevent loss of data |
DBMS Tools -
Developer interface
Allows a developer to write database queries using a Structured Query Language (SQL).
It allows to write more complex and precise queries to search, update or manage.
Query processor
Responsible for handling and executing SQL queries.
Data Definition Language (DDL) -
Used to build and manage structures of a relational database
Defines how data is stored by creating and modifying tables, fields, indexes and relationships.


Data Manipulation Language (DML) -
Used to manage data stored within the database structures made by the DDL.

