Computer Science (9210) | Database design

Welcome to Database Design! Building a Strong Foundation

Hello future Computer Scientists! This chapter is incredibly important. Designing a database is like planning the structure of a sturdy skyscraper—if the foundation is weak, the whole building will eventually collapse!

Here, we will learn how to organise data efficiently using tables, fields, and special "keys," making sure our data is accurate and easy to manage. Don't worry if terms like 'keys' and 'relationships' sound complicated; we will break them down using simple, everyday examples.

Why Good Design Matters

A well-designed database saves space, ensures accuracy, and makes it fast to find the information you need. Our main goal in design is to eliminate **Data Redundancy**.

Key Takeaway: Good design = Efficiency + Accuracy.

1. The Building Blocks: Tables and Fields

Relational databases store information in Tables (sometimes called Entities). Think of a table as a specific category of thing, like *Students*, *Books*, or *Orders*.

Fields (Attributes)

Each column in a table is called a Field (or an Attribute). Fields define the specific pieces of information we want to store about that entity.

• Example Table: Students
• Example Fields: StudentID, FirstName, LastName, DateOfBirth, HomeAddress

Records (Tuples)

Each row in the table is a complete set of data about one specific item or person. This is called a Record or a Tuple.

2. The Essential Security System: Keys

To link tables together and ensure every record is unique, we use special fields called Keys. These are arguably the most important concept in database design!

A. The Primary Key (PK)

The Primary Key (PK) is a field (or sometimes a combination of fields) that uniquely identifies every single record in a table. No two records can ever have the same Primary Key value, and it cannot be empty (it must be NOT NULL).

• Analogy: The Primary Key is like your passport number or unique student ID. No one else has it, and you need it to identify yourself.
• Example: In a "Products" table, the PK might be ProductID. In a "Students" table, it would be StudentID.

Did you know? A PK that is made up of two or more fields combined is called a Composite Key.

B. The Foreign Key (FK)

A Foreign Key (FK) is a field in one table that refers to (or "borrows") the Primary Key of another table. The Foreign Key is the essential link that connects different tables together, creating relationships.

Don't worry if this sounds tricky! Think of it this way:

1. We have a Classes table (PK is ClassID).
2. We have a Students table (PK is StudentID).
3. To show which class a student belongs to, we must put the ClassID into the Students table.
4. In the Students table, ClassID is now the Foreign Key.

Memory Trick:
Primary Key = Personal, Keeps unique.
Foreign Key = For Konnecting (connecting) to another table.

3. Making Connections: Database Relationships

The whole point of using Foreign Keys is to define how the data in different tables relate to each other. This is crucial for avoiding redundancy.

A. One-to-One (1:1)

A record in Table A relates to exactly one record in Table B, and vice versa.

• Example: One employee is assigned exactly one company car.
• How to create: The Primary Key of one table is included as the Foreign Key in the other. (This relationship is rare because if the data is truly 1:1, you often just put it all in one table.)

B. One-to-Many (1:M)

This is the most common and important relationship. A single record in Table A can relate to many records in Table B.

• Example: One customer can place many orders. (The Customer is the 'One' side; Orders are the 'Many' side).
• How to create: The PK from the 'One' side (e.g., CustomerID) is placed as the FK in the 'Many' side table (e.g., the Orders table).

C. Many-to-Many (M:M)

A record in Table A can relate to many records in Table B, and a record in Table B can relate to many records in Table A.

• Example: Many students can enrol in many courses, and many courses contain many students.
• The Problem: You cannot directly link two tables in an M:M relationship, because that would lead to massive data duplication.

Step-by-Step Solution: The Linking Table

To solve the M:M problem, we must introduce a third table, often called a Linking Table (or Junction Table). This table breaks the M:M relationship into two separate 1:M relationships.

1. Create Table A (Students) and Table B (Courses).
2. Create a new Linking Table (e.g., Enrolment).
3. The Linking Table must contain two fields: StudentID (FK from Students table) and CourseID (FK from Courses table).
4. The Primary Key of the Linking Table is the combination of these two Foreign Keys (a Composite Key).

Result: Students (1) relates to Enrolment (M). Courses (1) relates to Enrolment (M). Problem solved!

4. Dealing with Poor Design: Data Redundancy

The primary purpose of designing a database with linked tables is to avoid **Data Redundancy**.

What is Data Redundancy?

Data Redundancy means storing the same piece of data multiple times within the database.

• Bad Example: If you have an Orders table and you put the Customer’s full name and address on every single order record.

Problems Caused by Redundancy

If you allow redundancy, your database will suffer from several critical problems, collectively known as Data Anomalies:

1. Wasted Space:

Storing the same address 500 times for one customer takes up much more storage than storing the address once in a Customer table and just using a Foreign Key (CustomerID) 500 times in the Orders table.

2. Update Anomalies (Inconsistency):

This is the most dangerous problem. If a customer moves house, you have to update their address in hundreds of places (all their orders). If you miss just one update, the data becomes inconsistent—different records show different addresses for the same person.

When tables are linked correctly (using Foreign Keys), you only update the address in one place (the Customer table), and every linked order record automatically uses the correct, updated information.

3. Insertion Anomalies:

Sometimes you cannot add new, important data until you have another related piece of data.

Key Takeaway: Good database design uses relationships (PK/FK) to ensure data is stored only once, preventing inconsistency. This is the definition of good **Data Integrity**.

Chapter Summary: Database Design Checklist

You are now equipped with the fundamental knowledge of database design! Always remember these core concepts when structuring your tables:

1. Identify your entities (Tables).
2. Determine the specific information needed for each entity (Fields).
3. Ensure every table has a unique Primary Key (PK).
4. Use Foreign Keys (FK) to link tables, ensuring the FK always points back to a PK in another table.
5. Structure relationships as One-to-Many (1:M) whenever possible, placing the FK on the 'Many' side.
6. Use a Linking Table to handle Many-to-Many (M:M) relationships.
7. The goal is to eliminate Data Redundancy and ensure high Data Integrity.

You've got this! Move on to the next chapter knowing you understand the structure needed for powerful databases.