Databases

Database (General Definition)

A collection of data that follows a set system structure to store data in a logical manner.

Common Fields using Databases

Social media, banking, retail, and medicine.

Table (Relation)

A structure of data for storing groups of similar relevant data, representing an Entity.

Entity

A thing that can have data stored about it (e.g., Cars, Aeroplanes).

Columns / Fields / Attributes

The specific characteristics or attributes to track within an entity (similar to attributes of a class).

Rows / Tuples

A single entry representing a specific record of an entity (similar to objects belonging to a class).

Primary Key

A column that serves as the unique identifier used to retrieve an entire record associated with it.

Foreign Key

A column in a table that originates from another table to create an association, serving as the Primary Key in its own table.

Composite Key

A unique identifier created by combining two or more columns, often used in bridging/linking tables.

Database Relationship

A logical association describing how two separate entities relate to each other to reduce redundancy, data duplication, and anomalies.

Referential Integrity Rule

A rule stating that any foreign key brought into a table must already exist as a Primary Key in its original table to protect data consistency.

Modality

Defines whether a relationship between tables is mandatory or optional in an Entity-Relationship Diagram (ERD).

Optional Modality

A relationship status where a record can be associated with other records from different tables, but does not have to be.

Mandatory Modality

A relationship status where a record must be associated with at least one other record from a different table.

Cardinality

Defines which relationship type fits into the database design structure (e.g., One-to-One, One-to-Many, Many-to-Many).

One-To-One Relationship

A relation where one record in Table 1 is associated with a maximum of one record in Table 2, sharing a linked key.

One-To-Many Relationship

A relation where one record in Table 1 is associated with any number of records in Table 2, managed by placing Table 1's Primary Key as a Foreign Key in Table 2.

Many-To-Many Relationship

A relation where multiple records in Table 1 link to multiple records in Table 2, requiring a new linking table (e.g., STUDENT_SUBJECT) with a composite key.

Database Schema

An architectural schematic showing how data is organized and how different entities relate to provide a view of the database.

Conceptual Schema

The highest abstraction view showing only what data to include and how they relate, excluding data types, keys, cardinality, and modality.

Logical Schema

The middle abstraction view defining structure, entities, data types, constraints, keys, and cardinality/modality, without considering DBMS-specific requirements.

Physical Schema

The lowest abstraction view containing storage specifics, access methods, indexing, views, and configurations tailored to a selected DBMS.

Entity-Relationship Diagram (ERD)

A visual representation of entities and their relationships, connecting them via lines representing modality and cardinality.

ERD Symbol: Double Line (||)

Represents "one and only one" (mandatory, single association).

ERD Symbol: Circle and Line (o|)

Represents "zero or one" (optional, single association).

ERD Symbol: Line and Crow's Foot (|<)

Represents "one or many" (mandatory, multiple association).

ERD Symbol: Circle and Crow's Foot (o<)

Represents "zero or many" (optional, multiple association).

CHARACTER(n) Data Type

A fixed-length string of size n

VARCHAR(n) Data Type

A variable-length string with a maximum limit of n

INTEGER Data Type

Stores whole numbers ranging from -2,147,483,648 to 2,147,483,647.

DECIMAL Data Type

Stores precise decimal numbers, used for exact financial or scientific calculations.

FLOAT Data Type

Stores approximate decimal numbers precise up to 23 digits

REAL Data Type

Equivalent to FLOAT(24), sharing the same memory efficiency and accuracy profile.

DATE Data Type

Stores date information in the format YYYY-MM-DD for straightforward sorting and filtering.

BOOLEAN Data Type

Stores binary values representing either True or False.

TEXT Data Type

A variable-length field for very long strings

Data Normalisation

The process of organizing database data according to specific rules to reduce redundancy, improve integrity, and prevent anomalies.

Normalisation Mnemonic

"The key [1NF], the whole key [2NF], and nothing but the key [3NF]."

Non-key Attribute

An attribute that is not part of the primary key or a composite key

Functional Dependency

A relationship where the value of one set of attributes (the dependent) is determined by another set (the determinant).

Full Functional Dependency

A state where dependent non-key attributes are fully determined by the entire primary key.

Partial Functional Dependency

An issue in composite-key tables where a non-key column relies on only one part of the composite key rather than the entire key.

Transitive Functional Dependency

An issue where a non-key attribute depends on another non-key attribute instead of depending directly on the primary key.

First Normal Form (1NF) Requirements

The database must contain atomic data (every cell cannot be reduced further) and must have a defined primary key.

Second Normal Form (2NF) Requirements

The database must be in 1NF and have zero partial functional dependencies (automatically met if there is no composite key).

Third Normal Form (3NF) Requirements

The database must be in 2NF and have zero transitive functional dependencies (all non-key attributes must rely solely on the primary key).

Benefit of Relational DB: Data Integrity

Constraints and referential integrity rules prevent data mismatches and keep relationships accurate.

Benefit of Relational DB: Reduced Redundancy

Normalization splits data into distinct tables, saving storage space and avoiding conflicting duplicate values.

Benefit of Relational DB: Security

Allows database administrators to grant detailed read/write permissions for specific tables and views.

Limitation of Relational DB: Scalability

Primarily relies on vertical scaling (adding hardware resources to a single server), making horizontal distribution across servers complex.

Limitation of Relational DB: Complexity

Requires careful initial design and normalization to prevent slow performance and design issues.

Limitation of Relational DB: Unstructured Data

Poorly suited for storing unstructured media formats, such as audio files, raw emails, or social media feeds.