Introduction to Database Design Principles

Course: MySQL Training

Topic: Introduction to Database Design Principles

Course Link: MySQL Training Course at JustAcademy

Demo Registration: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Database Design

Database design is the process of planning and organizing data into tables, columns, relationships, constraints, and indexes so that information can be stored, retrieved, updated, and maintained efficiently. A well-designed database helps reduce unnecessary duplication, improve data accuracy, and support the requirements of an application.

In MySQL, database design is especially important when building systems such as student management platforms, e-commerce websites, employee management systems, banking applications, hospital databases, and inventory management systems.

For example, an online shopping application may need to store customers, products, orders, payments, and delivery information. If all this information is placed in a single large table, customer details may be repeated for every order, product information may become duplicated, and updating records may become difficult.

A well-designed database separates information into related tables and connects them using primary keys and foreign keys.

1.1 Why Database Design Is Important

• Data accuracy: Helps prevent inconsistent and invalid information.
• Reduced duplication: Stores each type of information in an appropriate location.
• Data integrity: Uses constraints and relationships to protect data.
• Maintainability: Makes the database easier to modify and troubleshoot.
• Performance: Supports efficient queries through appropriate schema design and indexing.
• Scalability: Makes it easier to expand an application as requirements grow.
• Security: Supports controlled access to sensitive information.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the fundamentals of relational database design.
• Identify entities, attributes, records, and relationships.
• Design tables with suitable columns and data types.
• Select primary keys and foreign keys.
• Understand one-to-one, one-to-many, and many-to-many relationships.
• Apply normalization principles to reduce unnecessary duplication.
• Use constraints to maintain data integrity.
• Understand the importance of indexes and query performance.
• Recognize common database design mistakes.
• Create a practical database schema using MySQL.

3. Understanding the Basic Components of a Database

3.1 Database

A database is an organized collection of related information. MySQL allows developers to create databases and manage their contents using SQL statements.

CREATE DATABASE college_db;

USE college_db;

The first statement creates a database named college_db. The second statement selects it as the current database for subsequent operations.

3.2 Table

A table stores related information in rows and columns. Each table should represent a clearly defined entity or relationship.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

This table stores student information. Each row represents one student, and each column represents a specific attribute.

3.3 Row or Record

A row is one complete record in a table. For example, one row in the students table may contain a student's ID, name, and email address.

3.4 Column or Attribute

A column defines a particular property of an entity, such as a student's name, product price, or order date. Each column has a name and a data type.

3.5 Schema

A database schema describes the structure of the database, including its tables, columns, data types, keys, constraints, indexes, and relationships.

In MySQL, you can inspect a table's structure using:

DESCRIBE students;

SHOW CREATE TABLE students;

4. Understanding Entities and Attributes

An entity is a real-world object, person, place, event, or concept about which information must be stored. An attribute is a property that describes an entity.

4.1 Examples of Entities

4.2 Identifying Attributes

When designing a table, choose attributes that directly describe the entity represented by the table.

For a student entity, suitable attributes may include:

• student_id
• first_name
• last_name
• email
• date_of_birth

Avoid combining unrelated information into one column. For example, storing a student's full name, email, and phone number together in a single field makes filtering and updating the individual values difficult.

5. Choosing Appropriate Data Types

Every column should have a data type that matches the kind of information it stores. Choosing appropriate data types improves validation, storage efficiency, and query behavior.

5.1 Example of Suitable Data Types

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(120) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

In this example, the product name uses VARCHAR, the description uses TEXT, the price uses DECIMAL, and quantity uses INT. The created_at column stores a timestamp initialized by its default expression.

5.2 Avoid Using Incorrect Data Types

• Do not use VARCHAR for every type of information.
• Use DECIMAL rather than FLOAT or DOUBLE when exact decimal arithmetic is important, such as storing prices.
• Use DATE or DATETIME for date-related information instead of storing dates as ordinary text.
• Choose a suitable length for text fields.
• Ensure related foreign key columns have compatible data types and matching integer signedness where applicable.

6. Primary Keys in Database Design

A primary key uniquely identifies each row in a table. A primary key must contain unique, non-NULL values. A table has one primary key constraint, which may consist of one column or multiple columns.

6.1 Why Primary Keys Are Important

• Uniquely identify records.
• Prevent duplicate identifiers.
• Support relationships between tables.
• Make it easier to update or delete a specific record.
• Provide a stable reference for other tables.

6.2 Single-Column Primary Key

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Here, each department must have a unique department ID.

6.3 AUTO_INCREMENT Primary Key

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100) NOT NULL,
    job_title VARCHAR(100)
);

AUTO_INCREMENT allows MySQL to generate a new numeric identifier when a row is inserted without an explicit employee ID.

6.4 Composite Primary Key

A composite primary key consists of two or more columns. It is useful when the combination of values uniquely identifies a record.

CREATE TABLE course_enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

The combination of student_id and course_id prevents the same student from being enrolled in the same course more than once. If multiple enrollments per student and course are allowed, the key design must be adjusted to represent each enrollment separately.

7. Foreign Keys and Relationships

A foreign key establishes a relationship between a child table and a referenced table. It helps ensure that child records refer to valid parent records.

7.1 Example of a Foreign Key

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

The employees table references departments through department_id. When a department ID is supplied for an employee, it must match a valid referenced department unless the foreign key is NULL and the column allows NULL.

7.2 Why Foreign Keys Matter

• Prevent invalid references to nonexistent parent records.
• Help maintain relationships between tables.
• Support referential integrity.
• Allow controlled deletion and update behavior through referential actions.

7.3 Referential Actions

Foreign keys can define ON DELETE and ON UPDATE actions. For example, RESTRICT prevents certain parent changes when related child records exist, while CASCADE propagates supported changes to related records.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

The customers table must exist before this example's orders table is created. The referenced customer_id column must have a suitable key or index, and the corresponding columns must have compatible definitions.

8. Types of Relationships in Database Design

Relationships describe how records in one table are associated with records in another table.

8.1 One-to-One Relationship

In a one-to-one relationship, one record in a table is associated with at most one record in another table.

Example: A user account and its optional profile details.

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL
);

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,
    biography TEXT,
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

The UNIQUE constraint on user_id prevents more than one profile from referencing the same user. Because user_id is NOT NULL, every profile must reference a user.

8.2 One-to-Many Relationship

In a one-to-many relationship, one parent record can be associated with multiple child records.

Example: One customer can place multiple orders.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The foreign key is placed in the many-side table, which is orders in this example.

8.3 Many-to-Many Relationship

In a many-to-many relationship, multiple records in one table can be associated with multiple records in another table. This relationship is usually implemented with a junction table, also called a bridge or associative table.

Example: Students can enroll in multiple courses, and each course can have multiple students.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

The enrollments table connects students and courses. It also stores enrollment_date, an attribute of the relationship itself.

9. Introduction to Database Normalization

Normalization is a process of organizing relational tables to reduce unnecessary data duplication and prevent data anomalies. It involves dividing data into appropriate tables and defining relationships between them.

Normalization helps avoid three common problems:

• Insertion anomaly: Difficulty adding information because unrelated information must also be provided.
• Update anomaly: The same information must be changed in multiple rows, creating a risk of inconsistency.
• Deletion anomaly: Deleting one record unintentionally removes information that should have been retained.

9.1 Example of Poor Database Design

Consider the following table:

The customer name and city are repeated for every order. If Aarav moves to another city, several rows may need to be updated. If one row is missed, the database becomes inconsistent.

9.2 Improved Database Structure

Separate the information into customers, orders, products, and order_items tables.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)
        REFERENCES orders(order_id),
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);

This structure stores customer details once, product details separately, and each order's product lines in order_items. The unit_price column can preserve the price charged at the time of the order, even if the current product price changes later.

10. First Normal Form (1NF)

First Normal Form requires each column value to be atomic for the purposes of the relational design, with no repeating groups stored as multiple values in a single field. Each row should be identifiable, and columns should represent consistent attributes.

10.1 Example That Violates 1NF

The phone numbers column stores multiple values in one field, making it difficult to search, validate, or update each number independently.

10.2 Improved 1NF Design

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE student_phones (
    student_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    PRIMARY KEY (student_id, phone_number),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id)
);

Each phone number is stored as a separate row in student_phones. VARCHAR is used because phone numbers may contain leading zeros, plus signs, or formatting characters and are not values for arithmetic.

11. Second Normal Form (2NF)

Second Normal Form requires a table to satisfy 1NF and every non-key attribute to depend on the whole of each candidate key, not just part of a composite candidate key.

Partial dependencies can occur when a table uses a composite key.

11.1 Example of a 2NF Problem

Suppose an enrollment table has a composite key consisting of student_id and course_id, but also stores student_name and course_name.

Student name depends on student_id alone, while course name depends on course_id alone. They do not depend on the complete composite key.

11.2 Improved 2NF Design

• Store student information in students.
• Store course information in courses.
• Store enrollment information and grade in enrollments.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    grade VARCHAR(5),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

12. Third Normal Form (3NF)

Third Normal Form requires a table to satisfy 2NF and generally ensures that non-key attributes depend on the key, the whole key, and nothing but the key. It helps remove transitive dependencies among non-key attributes.

12.1 Example of a 3NF Problem

Consider an employee table that stores employee_id, employee_name, department_id, and department_name. If department_name is determined by department_id rather than directly by employee_id, the department name is repeated for every employee in the department.

12.2 Improved 3NF Design

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Department details are stored in one table, and employees reference their department. This reduces repeated department information and simplifies updates.

13. Understanding Denormalization

Denormalization is the deliberate introduction of some redundant or precomputed data to support specific performance or reporting requirements. It should be considered only after understanding the application's workload and measuring performance.

13.1 Example

An order_items table may store unit_price at the time an order is placed, even though the current product price is also available in products. This is not necessarily an error because the order's historical price has a different business meaning from the current catalog price.

13.2 Benefits

• Can reduce the number of joins required for certain queries.
• Can improve reporting performance for selected workloads.
• Can preserve historical values that should not change with current reference data.

13.3 Risks

• May introduce redundant information.
• Can create update inconsistencies if duplicated values are not managed correctly.
• May increase storage and maintenance requirements.

Denormalization should be based on documented requirements and testing rather than applied automatically.

14. Database Constraints and Data Integrity

Constraints are rules that help ensure database values satisfy specified requirements. MySQL supports several important constraints that are useful during schema design.

14.1 Example of Multiple Constraints

CREATE TABLE inventory (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    quantity INT NOT NULL DEFAULT 0 CHECK (quantity >= 0)
);

This table requires a product name and SKU, prevents duplicate SKU values, and requires nonnegative prices and quantities. MySQL enforces CHECK constraints in supported modern versions, beginning with MySQL 8.0.16.

15. Choosing Appropriate NULL and DEFAULT Rules

Good database design distinguishes between required values, optional values, and values that should be initialized automatically.

• Use NOT NULL when a value is mandatory.
• Allow NULL when the information may legitimately be unknown, unavailable, or inapplicable.
• Use DEFAULT when there is a meaningful standard value.
• Do not use a placeholder such as zero or 'Unknown' simply to avoid NULL unless that value has a defined business meaning.

15.1 Example

CREATE TABLE tasks (
    task_id INT PRIMARY KEY AUTO_INCREMENT,
    task_title VARCHAR(150) NOT NULL,
    task_description TEXT,
    task_status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    priority INT NOT NULL DEFAULT 3
);

The task title is mandatory. The description is optional. New tasks receive a default status and priority.

16. Choosing Meaningful Table and Column Names

Clear naming makes a database easier to understand and maintain. Names should describe the information being stored and follow a consistent convention.

16.1 Naming Best Practices

• Use descriptive table names such as customers, orders, and products.
• Use meaningful column names such as customer_id and order_date.
• Choose a consistent naming style, such as lowercase snake_case.
• Avoid vague names such as data1, value2, or field_a.
• Avoid reserved SQL keywords as identifiers when possible.
• Use consistent names for related keys, such as customer_id in both customers and orders.
• Keep names readable and avoid unnecessary abbreviations.

16.2 Example

CREATE TABLE customer_addresses (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    street_address VARCHAR(200) NOT NULL,
    city VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The table name and column names communicate their purposes clearly. The referenced customers table must already exist with a compatible customer_id key.

17. Identifying Functional Dependencies

A functional dependency describes a relationship in which one attribute or set of attributes determines another attribute. Functional dependencies help identify candidate keys and decide how information should be divided among tables.

For example, if each employee_id identifies exactly one employee, then employee_id determines employee_name and the employee's other single-valued attributes.

This can be represented as:

employee_id -> employee_name
employee_id -> department_id

If department_id determines department_name, department_name belongs naturally to the department entity rather than being repeated in every employee record.

17.1 Why Functional Dependencies Matter

• Help identify primary and candidate keys.
• Reveal repeated information.
• Support normalization.
• Help prevent update anomalies.
• Clarify the meaning of each table's attributes.

18. Choosing Primary Keys and Natural Keys

A natural key is an identifier that has meaning outside the database, such as a carefully governed registration number. A surrogate key is an artificial identifier created primarily to identify records, such as an auto-incrementing integer.

Choose keys according to the data's meaning and lifecycle. A surrogate key does not remove the need for business constraints. For example, an email address that must be unique may still need a UNIQUE constraint even when a user_id primary key exists.

19. Indexing Principles in Database Design

An index is a data structure that helps MySQL locate rows efficiently. Indexes can improve query performance, but they also consume storage and add overhead to inserts, updates, and deletes.

19.1 Primary Key Index

MySQL automatically creates an index for a primary key.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL
);

19.2 Creating a Secondary Index

If the application frequently searches customers by city, an index on city may help.

CREATE INDEX idx_customers_city
ON customers(city);

19.3 Composite Index

A composite index contains multiple columns. Column order matters because MySQL can generally use the index most effectively when query conditions use its leading columns.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This index may help queries that filter by customer_id or by customer_id together with order_date. It is not automatically the best choice for every query that filters only by order_date.

19.4 Indexing Best Practices

• Index columns used frequently in selective searches and joins.
• Consider indexes for columns used in sorting and grouping when appropriate.
• Review existing indexes before adding duplicates.
• Avoid creating an index for every column.
• Measure query performance with realistic data.
• Use EXPLAIN to understand how MySQL plans a query.

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 10;

20. Planning a Database Before Creating Tables

Good database design starts with understanding requirements rather than immediately writing CREATE TABLE statements.

20.1 Step 1: Gather Requirements

Determine what information the application needs to store, who will use it, what operations users will perform, and which business rules must be enforced.

20.2 Step 2: Identify Entities

List the major objects or concepts, such as customers, products, orders, employees, or departments.

20.3 Step 3: Identify Attributes

Determine the fields required for each entity. Define which attributes are mandatory, optional, unique, or calculated.

20.4 Step 4: Identify Relationships

Determine whether relationships are one-to-one, one-to-many, or many-to-many. Create junction tables where needed.

20.5 Step 5: Choose Keys

Select primary keys and identify foreign keys that connect related tables.

20.6 Step 6: Normalize the Design

Review the tables for repeating groups, partial dependencies, and transitive dependencies. Normalize the design as appropriate for the requirements.

20.7 Step 7: Define Constraints

Add NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, and FOREIGN KEY constraints where they reflect genuine business rules.

20.8 Step 8: Plan Indexes

Identify likely query patterns and add indexes based on expected access paths and measured performance.

20.9 Step 9: Test with Sample Data

Insert realistic records, test joins and constraints, and check whether the schema supports expected operations.

20.10 Step 10: Document the Schema

Document table purposes, key relationships, important constraints, assumptions, and business rules so future developers can maintain the database.

21. Common Database Design Mistakes

22. Complete Practical Project: College Management Database

Project Objective: Design a relational database for a college that manages students, courses, instructors, and enrollments.

22.1 Project Requirements

• Store student information.
• Store course information.
• Store instructor information.
• Allow students to enroll in multiple courses.
• Allow each course to have multiple students.
• Record the date of enrollment.
• Prevent duplicate enrollment for the same student and course.
• Use primary keys and foreign keys to protect relationships.

22.2 Create the Database

CREATE DATABASE college_management;

USE college_management;

22.3 Create the Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(60) NOT NULL,
    last_name VARCHAR(60) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    date_of_birth DATE
) ENGINE=InnoDB;

22.4 Create the Instructors Table

CREATE TABLE instructors (
    instructor_id INT PRIMARY KEY AUTO_INCREMENT,
    instructor_name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
) ENGINE=InnoDB;

22.5 Create the Courses Table

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(120) NOT NULL,
    instructor_id INT,
    course_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_course_instructor
        FOREIGN KEY (instructor_id)
        REFERENCES instructors(instructor_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB;

The instructor_id column permits NULL because a course may remain in the database if an instructor record is deleted. If that is not appropriate for the application, choose a different deletion policy.

22.6 Create the Enrollments Table

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    enrollment_status VARCHAR(20) NOT NULL DEFAULT 'Active',
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_enrollment_course
        FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

The enrollments table resolves the many-to-many relationship between students and courses. Its composite primary key prevents duplicate student-course pairs.

22.7 Insert Sample Data

INSERT INTO students
(first_name, last_name, email, date_of_birth)
VALUES
('Aarav', 'Sharma', '[email protected]', '2004-03-15'),
('Priya', 'Singh', '[email protected]', '2003-08-21'),
('Rahul', 'Mehta', '[email protected]', '2004-01-10');

INSERT INTO instructors
(instructor_name, email)
VALUES
('Neha Patel', '[email protected]'),
('Amit Verma', '[email protected]');

INSERT INTO courses
(course_name, instructor_id, course_fee)
VALUES
('MySQL Database Training', 1, 5000.00),
('Python Programming', 2, 6000.00),
('Web Development', 1, 5500.00);

INSERT INTO enrollments
(student_id, course_id, enrollment_date)
VALUES
(1, 1, '2026-01-10'),
(1, 2, '2026-01-12'),
(2, 1, '2026-01-13'),
(3, 3, '2026-01-15');

22.8 Display Students and Their Courses

SELECT
    s.student_id,
    s.first_name,
    s.last_name,
    c.course_name,
    e.enrollment_date,
    e.enrollment_status
FROM enrollments e
INNER JOIN students s
    ON e.student_id = s.student_id
INNER JOIN courses c
    ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_name;

This query combines data from three related tables to display students and their enrolled courses.

22.9 Display Courses and Instructor Names

SELECT
    c.course_id,
    c.course_name,
    i.instructor_name,
    c.course_fee
FROM courses c
LEFT JOIN instructors i
    ON c.instructor_id = i.instructor_id;

LEFT JOIN ensures that courses are displayed even when instructor_id is NULL.

22.10 Count Students in Each Course

SELECT
    c.course_name,
    COUNT(e.student_id) AS total_students
FROM courses c
LEFT JOIN enrollments e
    ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

22.11 Inspect the Database Structure

SHOW TABLES;

DESCRIBE students;
DESCRIBE courses;
DESCRIBE enrollments;

SHOW CREATE TABLE enrollments;

22.12 Project Tasks

1. Create the college_management database.
2. Create all four tables in the correct order.
3. Insert at least five students and three courses.
4. Enroll students in multiple courses.
5. Try inserting an enrollment with a nonexistent student ID.
6. Try inserting the same student-course combination twice.
7. Display students and their courses using JOIN.
8. Count enrollments for each course.
9. Review whether each referential action is appropriate for the college's business requirements.
10. Document the schema and explain why the enrollments table is necessary.

23. Database Design Testing and Validation

After designing the schema, test it with both valid and invalid data to confirm that it behaves as expected.

23.1 Test Required Values

INSERT INTO students (first_name, last_name, email)
VALUES (NULL, 'Sharma', '[email protected]');

This statement should fail because first_name is defined as NOT NULL.

23.2 Test Unique Constraints

INSERT INTO students
(first_name, last_name, email)
VALUES
('Aman', 'Kumar', '[email protected]');

This statement should fail if the email already exists because the email column has a UNIQUE constraint.

23.3 Test Foreign Key Integrity

INSERT INTO enrollments
(student_id, course_id, enrollment_date)
VALUES
(9999, 1, '2026-02-01');

This statement should fail when student ID 9999 does not exist in the students table.

23.4 Test Duplicate Enrollment

INSERT INTO enrollments
(student_id, course_id, enrollment_date)
VALUES
(1, 1, '2026-02-02');

If the student-course pair (1, 1) already exists, the insert fails because the composite primary key prevents duplicate pairs.

24. Database Security and Maintenance Principles

Database design should consider not only the structure of data but also how that data is accessed, protected, backed up, and maintained.

• Least privilege: Give each database account only the permissions it requires.
• Data protection: Protect sensitive information using suitable access controls and security measures.
• Backups: Establish and test a backup and recovery process.
• Change management: Plan and document schema changes before deploying them.
• Monitoring: Review slow queries, resource usage, and errors.
• Testing: Validate database changes in a development or staging environment.
• Data retention: Define how long business records should be retained and when they may be deleted.
• Documentation: Maintain an understandable record of relationships, constraints, and important design decisions.

25. Interview Questions and Answers

Q1. What is database design?

Answer: Database design is the process of organizing information into tables, columns, relationships, keys, constraints, and indexes to meet application requirements while maintaining data integrity.

Q2. What is a database schema?

Answer: A schema describes the structure of a database, including its tables, columns, data types, keys, constraints, and relationships.

Q3. What is an entity?

Answer: An entity is an object, person, event, or concept about which information is stored, such as a customer or product.

Q4. What is an attribute?

Answer: An attribute is a property of an entity, such as customer_name, product_price, or order_date.

Q5. What is a primary key?

Answer: A primary key uniquely identifies each row in a table and does not allow NULL values.

Q6. What is a foreign key?

Answer: A foreign key connects a child table to a referenced table and helps maintain referential integrity.

Q7. What is normalization?

Answer: Normalization organizes relational data into appropriate tables to reduce unnecessary redundancy and prevent insertion, update, and deletion anomalies.

Q8. What is First Normal Form?

Answer: First Normal Form requires atomic column values and prohibits repeating groups within a single field.

Q9. What is Second Normal Form?

Answer: Second Normal Form requires 1NF and ensures that non-key attributes depend on the whole of each candidate key, rather than only part of a composite key.

Q10. What is Third Normal Form?

Answer: Third Normal Form requires 2NF and removes relevant transitive dependencies so non-key attributes depend on keys rather than other non-key attributes.

Q11. What is a many-to-many relationship?

Answer: It is a relationship in which multiple records in each of two tables may be associated with multiple records in the other. It is usually implemented using a junction table.

Q12. What is a composite primary key?

Answer: A composite primary key uses two or more columns together to uniquely identify a row.

Q13. What is the difference between a natural key and a surrogate key?

Answer: A natural key has business meaning, while a surrogate key is created primarily as a database identifier.

Q14. What is an index?

Answer: An index is a data structure that can help MySQL locate rows efficiently. It improves some reads but adds storage and write overhead.

Q15. Why are foreign keys important?

Answer: Foreign keys help ensure that child records reference valid parent records, reducing inconsistent relationships.

Q16. What is denormalization?

Answer: Denormalization deliberately introduces selected redundancy or precomputed data to meet particular performance or reporting requirements.

Q17. Why should data types be chosen carefully?

Answer: Suitable data types improve data validation, represent values correctly, and can help manage storage and query performance.

Q18. What is a data anomaly?

Answer: A data anomaly is a problem that can arise when inserting, updating, or deleting records in a poorly structured database, often because information is unnecessarily repeated.

Q19. How do you inspect a table's design in MySQL?

Answer: Use DESCRIBE table_name or SHOW CREATE TABLE table_name to examine column definitions and constraints.

Q20. What are the main steps in database design?

Answer: Gather requirements, identify entities and attributes, define relationships, select keys, normalize the schema, apply constraints, plan indexes, test the design, and document it.

26. Quick Revision Checklist

• Database design organizes data into appropriate tables and relationships.
• Entities represent the objects or concepts being stored.
• Attributes describe entities.
• Rows represent records, while columns represent fields.
• Choose data types that match the meaning of each value.
• Primary keys uniquely identify records.
• Foreign keys help preserve referential integrity.
• One-to-many relationships place the foreign key on the many side.
• Many-to-many relationships generally require a junction table.
• Normalization helps reduce unnecessary duplication and data anomalies.
• Constraints enforce important business rules.
• Indexes can improve reads but add storage and write overhead.
• Use clear and consistent table and column names.
• Test the design with realistic data and invalid-input scenarios.
• Consider security, backups, maintenance, and future requirements.

27. Conclusion

Database design is a fundamental skill for anyone working with MySQL and relational database systems. A strong design starts with understanding application requirements, identifying entities and attributes, selecting appropriate data types, and defining relationships between tables.

Primary keys, foreign keys, constraints, normalization, and indexes each play an important role in building a reliable database. Applying these principles helps reduce unnecessary duplication, protect data integrity, and make the database easier to maintain and query.

Practice by designing small databases first, such as student management or inventory systems, and then expand to more complex applications involving customers, orders, payments, and reporting.

28. Additional Learning Resources

• Official MySQL 8.4 Reference Manual
• MySQL Documentation: Foreign Key Constraints
• MySQL Documentation: Primary Key Optimization
• Database Design Basics and Normalization
• Explore MySQL Training at JustAcademy
• Visit the JustAcademy Website
• Register for a JustAcademy Course Demo