Relational Model and Database Design

TL;DR

The relational model organizes data into tables (relations) with rows (tuples) and columns (attributes), forming the foundation of most modern databases. Database design involves creating a schema that accurately represents real-world information and ensures data integrity. This process helps you store, retrieve, and manage data efficiently and reliably.

1. The Mental Model

Think of a relational database as a collection of interconnected spreadsheets. Each spreadsheet (table) holds a specific type of information, and the connections between them allow you to link related pieces of data.

2. The Core Material

The relational model is a way to structure and manage data using relations, which are essentially tables. Each table has a defined set of columns (attributes) and stores data in rows (tuples).

Relations, Tuples, and Attributes

Photo by Pixabay on Pexels

• Relation (Table): A set of tuples (rows). Each relation represents an entity type, like Students or Courses.
• Tuple (Row): A single record in a relation, representing one instance of the entity. For example, one specific student.
• Attribute (Column): A named property that describes an aspect of the entity. For example, StudentID, StudentName, CourseTitle.
• Domain: The set of all possible values for an attribute. For Age, the domain might be positive integers.
• Schema: The logical design of the database, defining the tables, attributes, and relationships.

Keys

Keys are crucial for uniquely identifying rows and establishing relationships between tables.

• Primary Key (PK): An attribute or set of attributes that uniquely identifies each tuple in a relation. It cannot contain NULL values.
  • Example: StudentID in a Students table.
• Candidate Key: Any attribute or set of attributes that can serve as a primary key (unique and minimal).
• Foreign Key (FK): An attribute in one relation that refers to the primary key of another relation. It establishes a link between tables.
  • Example: StudentID in an Enrollments table, referring to StudentID in the Students table.

Relational Algebra (Briefly)

Photo by Sergey Meshkov on Pexels

Relational algebra is a procedural query language that defines how data is retrieved from relations. It uses operations like:

• SELECT (σ): Filters rows based on a condition.
• PROJECT (π): Selects specific columns.
• JOIN (⋈): Combines rows from two relations based on a common attribute.
• UNION (∪), INTERSECTION (∩), DIFFERENCE (-): Set operations on relations.

Database Design Process

Photo by panumas nikhomkhai on Pexels

Designing a database involves several steps to ensure it's robust, efficient, and accurate.

1. Requirements Analysis: Understand what data needs to be stored and how it will be used. Talk to stakeholders!
2. Conceptual Design (ER Model): Create a high-level representation of the data using Entity-Relationship (ER) diagrams.
   • Entities: Real-world objects (e.g., Student, Course).
   • Attributes: Properties of entities (e.g., StudentName, CourseTitle).
   • Relationships: Associations between entities (e.g., Students enroll in Courses).
3. Logical Design (Relational Model Mapping): Translate the ER model into a relational schema (tables, attributes, keys).
4. Physical Design: Define how the database will be stored on disk (indexes, file organization – often handled by the DBMS).

Here's how entities and relationships map to tables and keys:

graph TD
    A["Entity (e.g., Student)"] --> B["Table (e.g., Students)"]
    B --> C["Attributes (Columns)"]
    C --> D["Primary Key (Unique ID)"]
    E["Relationship (e.g., Enroll)"] --> F["Foreign Key (Link to another table)"]
    B -- "Linked by" --> F

Normalization

Normalization is a systematic process of restructuring a relational database to reduce data redundancy and improve data integrity. It involves a series of forms (1NF, 2NF, 3NF, BCNF, etc.), with each form addressing specific types of data anomalies.

• 1st Normal Form (1NF): Each column contains atomic (indivisible) values, and there are no repeating groups of columns.
• 2nd Normal Form (2NF): Is in 1NF and all non-key attributes are fully functionally dependent on the primary key (no partial dependencies).
• 3rd Normal Form (3NF): Is in 2NF and all non-key attributes are non-transitively dependent on the primary key (no transitive dependencies).

3. Worked Example

Let's design a simple database for a library to track books and borrowers.

Requirements:
* Each book has a unique ISBN, title, author.
* Each borrower has a unique ID, name, and address.
* We need to record who borrowed which book and when.

Conceptual Design (ER Sketch):
* Entities: Book, Borrower
* Attributes:
* Book: ISBN (PK), Title, Author
* Borrower: BorrowerID (PK), Name, Address
* Relationship: Borrower borrows Book (many-to-many relationship, as one borrower can borrow many books, and one book can be borrowed by many different borrowers over time).

Logical Design (Relational Schema):

Since "Borrower borrows Book" is a many-to-many relationship, we need an intermediary table.

1. Books Table:

   • ISBN (Primary Key)
   • Title
   • Author

2. Borrowers Table:

   • BorrowerID (Primary Key)
   • Name
   • Address

3. Loans Table (Junction Table for the many-to-many relationship):

   • LoanID (Primary Key, or a composite key of ISBN and BorrowerID)
   • ISBN (Foreign Key referencing Books.ISBN)
   • BorrowerID (Foreign Key referencing Borrowers.BorrowerID)
   • LoanDate
   • ReturnDate

This structure prevents redundancy (e.g., storing borrower details with each book they borrow) and ensures data integrity through foreign keys.

4. Key Takeaways

• The relational model structures data into tables with rows and columns.
• Primary keys uniquely identify rows within a table.
• Foreign keys link related data across different tables.
• Database design starts with understanding requirements and progresses through conceptual, logical, and physical stages.
• Normalization helps reduce data redundancy and improve data integrity by following a set of rules.
• ER diagrams are a visual tool for conceptual database design, representing entities, attributes, and relationships.
• Many-to-many relationships require an intermediary (junction) table to be properly represented in the relational model.

Common Mistakes to Avoid:
* Using too few tables: Trying to cram all data into one giant table leads to redundancy and update anomalies.
* Not defining proper keys: Missing primary or foreign keys makes it hard to uniquely identify records or link data.
* Ignoring normalization: Storing redundant data can lead to inconsistencies when updating or deleting information.
* Confusing attributes with entities: Don't make an entity out of something that's just a property of another entity.

5. Now Try It

You're designing a database for a small online shop. You need to store information about Customers, Products, and Orders. Each order can contain multiple products, and each product can be part of many orders.

Your task:
1. Identify the entities and their main attributes.
2. Determine the relationships between these entities (one-to-many, many-to-many).
3. Design the relational schema (list the tables, their columns, primary keys, and foreign keys).

What success looks like: You should have at least three tables, one for Customers, one for Products, and at least one more table to handle the relationship between Orders and Products. Your tables should have clearly defined primary and foreign keys.