A3.1.1 Features, Benefits, and Limitations of a Relational Database Notes

Features of a Relational Database

Tables: The Foundation of Relational Databases

Each table represents an entity, such as customers, products, or orders.

Primary Keys: Ensuring Uniqueness

It ensures that no two rows have the same value in the primary key column.

Foreign Keys: Creating Relationships

This creates a link between the two tables, enabling data to be related across entities.

Composite Keys: Combining Attributes for Uniqueness

It is used when a single column is not sufficient to uniquely identify a record.

Relationships: Connecting Data Across Tables

They are established through primary and foreign keys.

Types of Relationships

1. Relationships might be optional or mandatory (modality).
   1. Mandatory Relationship:
      1. A relationship where a record in one table must be linked to a record in another table. The foreign key cannot be NULL.
      2. Example: Every student must be enrolled in a school.
         Student(SchoolID) must have a valid SchoolID from the School table.
   2. Optional Relationship:
      1. A relationship where a record in one table may or may not be linked to a record in another table. The foreign key can be NULL.
      2. Example: A teacher might have an assigned mentor, but not all teachers do. Teacher(MentorID) can be NULL if no mentor is assigned.
2. One-to-One: Each record in one table is related to exactly one record in another table.
   1. Example: A person and their passport.
3. One-to-Many: A record in one table can be related to multiple records in another table.
   1. Example: A customer can have multiple orders.
4. Many-to-Many: Multiple records in one table can be related to multiple records in another table.
   1. Example: Students enrolled in multiple courses.

Benefits of Relational Databases

Data Consistency and Integrity

1. Relational databases ensure data consistency by eliminating redundancy through normalization.
2. Data integrity is maintained through constraints like primary and foreign keys.

Reduced Data Duplication and Redundancy

1. By organizing data into related tables, relational databases minimize data duplication.
2. This reduces storage requirements and prevents anomalies during data updates.

Efficient Data Retrieval

1. Structured Query Language ( SQL) allows for complex queries to retrieve and manipulate data efficiently.
2. Indexes and optimized query execution further enhance performance.

Scalability

1. Relational databases can handle large volumes of data and concurrent users.
2. They are designed to scale both vertically (adding more resources to a single server) and horizontally (distributing data across multiple servers).

Security Features

1. Relational databases offer robust security through user authentication, role-based access control, and encryption.
2. This ensures that sensitive data is protected from unauthorized access.

Reliable Transaction Processing

1. Relational databases support ACID properties (Atomicity, Consistency, Isolation, Durability), ensuring that transactions are processed reliably.
2. Atomicity: A transaction is all or nothing. Either every step completes, or none do. If one part fails, the entire transaction is rolled back.
3. Consistency: The database must always move from one valid state to another. Any transaction must preserve the rules and constraints of the database.
4. Isolation: Transactions happen independently. They appear to be executed one at a time, even if they’re run concurrently. This prevents interference between them.
5. Durability: Once a transaction is committed, it will not be lost, even if the system crashes. Changes are permanently saved to non-volatile storage.

Community Support

1. Relational databases like MySQL, PostgreSQL, and Oracle have large user communities and extensive documentation.
2. This provides valuable resources for troubleshooting and optimization.

Addresses Insert, Update, and Delete Anomalies

These are data integrity issues that arise in poorly designed (unnormalised) relational databases.

1. Insert Anomaly
   1. Occurs when you can’t add data to a table without adding unrelated data.
   2. Example: You can’t add a new course unless a student has enrolled in it.
2. Update Anomaly
   1. Occurs when the same piece of data is stored in multiple places, and not all copies are updated.
   2. Example: A teacher’s phone number is stored in multiple rows, updating one row but not the others causes inconsistency.
      1. How do you then know which phone number is correct
3. Delete Anomaly
   1. Occurs when deleting data unintentionally removes other important data often from a linked entity.
   2. Example: Deleting the last student on a course also deletes the course details.
4. How They Relate to ACID:
   1. These anomalies violate the principle of Consistency.
   2. They arise when the database design allows inconsistent, incomplete, or duplicated data.
   3. Normalisation helps eliminate these problems and supports Consistent, reliable transactions.

Limitations of Relational Databases

Big Data Scalability Issues

1. Relational databases may struggle with extremely large datasets or high-velocity data.
2. NoSQL databases are often preferred for big data applications due to their flexibility and scalability.

Design Complexity

1. Designing a relational database requires careful planning to define tables, relationships, and constraints.
2. Normalization can lead to complex schemas that are difficult to manage.

Rigid Schema

1. Relational databases have a fixed schema, making it challenging to adapt to changing data requirements.
2. Altering the schema can be time-consuming and may disrupt existing applications.

Hierarchical Data Handling

1. Relational databases are not optimized for hierarchical data structures, such as organizational charts or file systems.
2. Navigating deep hierarchies can be inefficient and complex.

Object-Relational Impedance Mismatch

1. Relational databases store data in tables, while object-oriented programming uses objects.
2. This mismatch can lead to complexity when mapping objects to database tables.

Unstructured Data Handling

1. Relational databases are not designed for unstructured data, such as text documents, images, or videos.
2. Storing and retrieving such data can be inefficient and cumbersome.