Database Design

What is a Database?

A database is an organized collection of data stored electronically. A relational database stores data in tables that can be linked together.

Database Terminology

Term	Definition	Example
Table	A collection of related data organized in rows and columns	Students, Products, Orders
Record (Row)	A single entry in a table	One student's information
Field (Column)	A single piece of data about each record	StudentName, DateOfBirth
Primary Key	A unique identifier for each record	StudentID, ProductCode
Foreign Key	A field that links to another table's primary key	CustomerID in Orders table

Data Types

Each field must have a data type that defines what kind of data it can store:

Text Types

VARCHAR(n) - Variable text up to n characters
CHAR(n) - Fixed length text
TEXT - Long text (descriptions)

Number Types

INTEGER - Whole numbers
DECIMAL(p,s) - Numbers with decimals
FLOAT - Approximate decimals

Date/Time Types

DATE - Date only (YYYY-MM-DD)
TIME - Time only
DATETIME - Date and time together

Other Types

BOOLEAN - True/False
BLOB - Binary data (images)
ENUM - List of allowed values

Entity-Relationship Diagrams (ERDs)

An ERD is a visual representation of a database structure, showing:

Entities - Things we store data about (become tables)
Attributes - Properties of entities (become fields)
Relationships - How entities connect to each other

ERD Notation

Entity

Rectangle represents an entity (table)

Attribute

Oval represents an attribute (field)

Relates

Diamond represents a relationship

Cardinality

Cardinality describes how many records in one table relate to records in another:

One-to-One (1:1) - Each record in Table A relates to exactly one record in Table B
One-to-Many (1:M) - One record in Table A relates to many records in Table B
Many-to-Many (M:M) - Many records in Table A relate to many records in Table B

Common Examples:

1:1 - Person → Passport (each person has one passport)
1:M - Customer → Orders (one customer, many orders)
M:M - Students ↔ Classes (students take many classes, classes have many students)

Crow's Foot Notation

A popular way to show cardinality on ERD lines:

|| - Exactly one (mandatory)
O| - Zero or one (optional)
|< - One or many
O< - Zero or many

Keys

Primary Keys

Rules for primary keys:

Must be unique for every record
Cannot be null (empty)
Should not change over time
Often an auto-incrementing number (ID)

Foreign Keys

Foreign keys create relationships between tables:

A foreign key in one table references the primary key in another
Enforces referential integrity
Prevents orphan records

// Example: Orders table with foreign key

Orders Table
-----------
OrderID (PK)    CustomerID (FK)    OrderDate
1               101                2024-03-15
2               102                2024-03-16
3               101                2024-03-17

// CustomerID references Customers.CustomerID

Normalization

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

First Normal Form (1NF)

Each cell contains only one value (atomic)
No repeating groups of columns
Each row is unique (has a primary key)

Not 1NF (Bad)

StudentID | Subjects
1 | Math, Science, English

1NF (Good)

StudentID | Subject
1 | Math
1 | Science
1 | English

Second Normal Form (2NF)

Is in 1NF
All non-key fields depend on the entire primary key
Remove partial dependencies

Third Normal Form (3NF)

Is in 2NF
No transitive dependencies
Non-key fields depend only on the primary key, not other non-key fields

Database Design Process

Identify entities - What things do we need to store data about?
Identify attributes - What information do we need for each entity?
Choose primary keys - How will we uniquely identify each record?
Identify relationships - How do entities connect?
Draw ERD - Visualize the structure
Normalize - Remove redundancy
Verify - Check the design meets requirements

Key Terminology

Table - A collection of related records
Record - A single row in a table
Field - A single column in a table
Primary Key - Unique identifier for each record
Foreign Key - Links one table to another
ERD - Entity-Relationship Diagram
Cardinality - The number of related records
Normalization - Organizing data to reduce redundancy

Learning Objectives