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.