Lab 3: DDL and DML Commands with Key Constraints

In this lab, you will learn to:

• Design and create database tables using DDL commands
• Insert, update, and delete data using DML commands
• Implement and test various database constraints (Primary Key, Foreign Key, UNIQUE, CHECK)
• Understand referential integrity and cascading operations
• Write and execute SQL queries to retrieve and analyze data

DDL (Data Definition Language) Commands

DDL commands are used to define and modify database structure:

• CREATE: Creates database objects (tables, indexes, views)
• ALTER: Modifies existing database objects
• DROP: Deletes database objects
• TRUNCATE: Removes all data from a table

DML (Data Manipulation Language) Commands

DML commands are used to manipulate data within database objects:

• INSERT: Adds new records to a table
• UPDATE: Modifies existing records
• DELETE: Removes records from a table
• SELECT: Retrieves data from tables

Key Constraints

• Primary Key: Uniquely identifies each record in a table
• Foreign Key: Establishes relationships between tables and maintains referential integrity
• UNIQUE: Ensures all values in a column are different
• CHECK: Ensures values in a column meet specific conditions
• NOT NULL: Ensures a column cannot have NULL values

Step 1: Database Schema Design

Design a simple university management system with the following entities:

• Students: Information about students
• Courses: Information about courses
• Enrollments: Student course registrations

Step 2: Create Database and Tables (DDL Commands)

-- Create database
CREATE DATABASE university_db;
USE university_db;

-- Create Students table
CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    date_of_birth DATE,
    enrollment_date DATE DEFAULT (CURDATE())
);

-- Create Courses table
CREATE TABLE Courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(10) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credits INT CHECK (credits > 0),
    department VARCHAR(50)
);

-- Create Enrollments table with foreign key constraints
CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    enrollment_date DATE DEFAULT (CURDATE()),
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES Students(student_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE(student_id, course_id)
);

Step 3: Insert Sample Data

-- Insert data into Students table
INSERT INTO Students (first_name, last_name, email, date_of_birth) VALUES
('John', 'Doe', 'john.doe@university.edu', '2000-05-15'),
('Jane', 'Smith', 'jane.smith@university.edu', '2001-03-22'),
('Mike', 'Johnson', 'mike.johnson@university.edu', '1999-11-08'),
('Sarah', 'Williams', 'sarah.williams@university.edu', '2000-09-12');

-- Insert data into Courses table
INSERT INTO Courses (course_code, course_name, credits, department) VALUES
('CS101', 'Introduction to Computer Science', 3, 'Computer Science'),
('MATH201', 'Calculus II', 4, 'Mathematics'),
('ENG102', 'English Composition', 3, 'English'),
('PHYS101', 'General Physics', 4, 'Physics');

-- Insert data into Enrollments table
INSERT INTO Enrollments (student_id, course_id, grade) VALUES
(1, 1, 'A'),
(1, 2, 'B+'),
(2, 1, 'A-'),
(2, 3, 'B'),
(3, 2, 'C+'),
(3, 4, 'B-'),
(4, 1, 'A'),
(4, 3, 'A-');

Step 4: Test Constraint Violations

Test the following constraint violations in your database:

-- This should fail - duplicate primary key
INSERT INTO Students (student_id, first_name, last_name, email) 
VALUES (1, 'Test', 'User', 'test@university.edu');

-- This should fail - invalid student_id reference
INSERT INTO Enrollments (student_id, course_id) VALUES (999, 1);

-- This should fail - duplicate email
INSERT INTO Students (first_name, last_name, email, date_of_birth) 
VALUES ('Another', 'User', 'john.doe@university.edu', '2000-01-01');

Basic Data Retrieval

-- Get all students
SELECT * FROM Students;

-- Get all courses
SELECT * FROM Courses;

-- Get all enrollments
SELECT * FROM Enrollments;

JOIN Queries

-- Get student names with their enrolled courses and grades
SELECT 
    s.first_name, 
    s.last_name, 
    c.course_name, 
    e.grade
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id
ORDER BY s.last_name, s.first_name;

Aggregate Queries

-- Count enrollments per student
SELECT 
    s.first_name, 
    s.last_name, 
    COUNT(e.enrollment_id) as total_courses
FROM Students s
LEFT JOIN Enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name, s.last_name;

In this lab, you have learned how to:

• Create and modify database tables using DDL commands
• Insert, update, and delete data using DML commands
• Implement various constraints to ensure data integrity
• Query data using SELECT statements with various clauses
• Understand and test referential integrity constraints