Session 3: Schema Analysis

Exercise 1: Missing Primary Key EASY

Task: What's wrong with this table design?

Students Table

student_id, name, email, major

Hint: Think about what makes each student unique.

Problem

Missing PRIMARY KEY

The student_id should be marked as the PRIMARY KEY.

Why it matters: Without a primary key, you could have duplicate student_ids, and other tables can't reliably reference students.

Correct design:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    major VARCHAR(50)
);

Exercise 2: Wrong Data Type MEDIUM

Task: This Products table has a data type problem. What is it?

Products Table

product_id INT
product_name VARCHAR
price VARCHAR ← Stored as text!
stock INT

Example data: price = "99.99"

Hint: What happens when you try to calculate total prices?

Problem

Price is stored as VARCHAR (text) instead of DECIMAL

Why this is bad:

Can't do math: "99.99" + "50.00" won't work
No validation: someone could enter "abc" as a price
Sorting doesn't work right: "9.99" comes after "100.00" alphabetically

Correct design:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),  -- Fixed!
    stock INT
);

Exercise 3: Bad Relationship Design HARD

Task: This design is broken. How should it be fixed?

Students Table

student_id, name, course1, course2, course3

Example:
Alice: course1="Math", course2="Physics", course3=NULL

Think about: What if Alice wants to take 4 courses? What if you need to find all students in Math?

Problems

Limited to 3 courses - what if a student takes 4?
Hard to query "all students taking Math"
Wastes space if a student takes only 1 course

Correct design - Use separate tables:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Now: Students can take any number of courses, and it's easy to query who's in each course!