It’s written for a Database Management
System (DBMS) class using MySQL or SQLite.
🧪 Database Management System Lab Activity 01
Title: Basic SQL Operations and Relationships
Duration: 1.5 – 2 hours
Level: Beginner
Tools: MySQL Workbench / SQLite
🎯 Learning Objectives
After completing this lab, students should be able to:
1. Create databases and tables with primary and foreign keys.
2. Insert, update, delete, and query records using SQL commands.
3. Use JOIN and aggregate functions to combine and summarize data.
4. Understand how transactions, constraints, and backups work.
🧩 Scenario
You are part of the IT team tasked with building a College Enrollment Database.
The database should store students, courses, and enrollments.
Entity Relationship Diagram (ERD)
Code block
1 students (1) ────< enrollments >──── (1) courses
🧰 Lab Preparation
Option 1: SQLite (Recommended for Quick Setup)
1. Download the SQLite CLI tool: [Link]
2. Open a terminal in your working folder and type:
Code block
1 sqlite3 [Link]
Option 2: MySQL
1. Open MySQL Workbench or terminal.
2. Create a database:
Code block
1 CREATE DATABASE college_lab01;
2 USE college_lab01;
🧱 Part 1: Database and Table Creation
Step 1: Drop existing tables (for re-run)
Code block
1 DROP TABLE IF EXISTS enrollments;
2 DROP TABLE IF EXISTS students;
3 DROP TABLE IF EXISTS courses;
Step 2: Create students table
Code block
1 CREATE TABLE students (
2 student_id INT AUTO_INCREMENT PRIMARY KEY,
3 full_name VARCHAR(100) NOT NULL,
4 email VARCHAR(100) UNIQUE NOT NULL,
5 year_level TINYINT CHECK (year_level BETWEEN 1 AND 4)
6 );
Step 3: Create courses table
Code block
1 CREATE TABLE courses (
2 course_id INT AUTO_INCREMENT PRIMARY KEY,
3 code VARCHAR(20) UNIQUE NOT NULL,
4 title VARCHAR(120) NOT NULL,
5 units TINYINT NOT NULL CHECK (units BETWEEN 1 AND 5)
6 );
Step 4: Create enrollments table
Code block
1 CREATE TABLE enrollments (
2 enroll_id INT AUTO_INCREMENT PRIMARY KEY,
3 student_id INT NOT NULL,
4 course_id INT NOT NULL,
5 semester VARCHAR(10) NOT NULL,
6 grade DECIMAL(4,2),
7 UNIQUE(student_id, course_id, semester),
8 FOREIGN KEY (student_id) REFERENCES students(student_id),
9 FOREIGN KEY (course_id) REFERENCES courses(course_id)
10 );
✅ Checkpoint:
If all three tables were created successfully, type:
Code block
1 SHOW TABLES;
📥 Part 2: Insert Sample Data
Step 1: Insert students
Code block
1 INSERT INTO students (full_name, email, year_level) VALUES
2 ('Alex Cruz', '[Link]@[Link]', 1),
3 ('Bianca Reyes', '[Link]@[Link]', 2),
4 ('Carlos Santos', '[Link]@[Link]', 3),
5 ('Dina Flores', '[Link]@[Link]', 4);
Step 2: Insert courses
Code block
1 INSERT INTO courses (code, title, units) VALUES
2 ('DBMS101', 'Introduction to Databases', 3),
3 ('PRG201', 'Programming II', 4),
4 ('NET150', 'Computer Networks', 3);
Step 3: Insert enrollments
Code block
1 INSERT INTO enrollments (student_id, course_id, semester, grade) VALUES
2 (1, 1, '2025-1', NULL),
3 (1, 2, '2025-1', 2.75),
4 (2, 1, '2025-1', 1.50),
5 (2, 3, '2025-1', 2.00),
6 (3, 1, '2025-1', 1.75),
7 (4, 2, '2025-1', NULL);
🔍 Part 3: SQL Operations Guide
🧾 A. SELECT Queries
Show all students:
Code block
1 SELECT * FROM students;
Show all courses:
Code block
1 SELECT * FROM courses;
🔗 B. JOIN Tables
Show student names with the courses they enrolled in:
Code block
1 SELECT s.full_name, [Link], [Link], [Link], [Link]
2 FROM enrollments e
3 JOIN students s ON e.student_id = s.student_id
4 JOIN courses c ON e.course_id = c.course_id;
📊 C. Aggregate Functions
Count how many students are enrolled in each course:
Code block
1 SELECT [Link], COUNT(*) AS total_students
2 FROM enrollments e
3 JOIN courses c ON e.course_id = c.course_id
4 GROUP BY [Link];
Compute each student’s average grade:
Code block
1 SELECT s.full_name, ROUND(AVG([Link]), 2) AS average_grade
2 FROM enrollments e
3 JOIN students s ON e.student_id = s.student_id
4 WHERE [Link] IS NOT NULL
5 GROUP BY s.full_name;
✏️ D. INSERT and Constraints
Try adding Alex Cruz again in the same course ( DBMS101 , 2025-1 ):
Code block
1 INSERT INTO enrollments (student_id, course_id, semester, grade)
2 VALUES (1, 1, '2025-1', NULL);
➡️Observe: You should get an error because of the UNIQUE constraint.
🛠️ E. UPDATE
Update Dina Flores’s grade for PRG201 to 1.75:
Code block
1 UPDATE enrollments
2 SET grade = 1.75
3 WHERE student_id = 4 AND course_id = 2;
❌ F. DELETE
Remove Carlos Santos from DBMS101 :
Code block
1 DELETE FROM enrollments
2 WHERE student_id = 3 AND course_id = 1;
🔄 G. Transactions
Try this sequence:
Code block
1 START TRANSACTION;
2
3 INSERT INTO courses (code, title, units) VALUES ('SEC210', 'Intro to InfoSec',
3);
4 INSERT INTO enrollments (student_id, course_id, semester)
5 SELECT s.student_id, c.course_id, '2025-1'
6 FROM students s, courses c
7 WHERE s.full_name='Bianca Reyes' AND [Link]='SEC210';
8
9 ROLLBACK; -- cancel the changes
Now check:
CodeSELECT
1 block * FROM courses WHERE code='SEC210';
✅ You should see no result, meaning rollback worked.
Run again but this time commit:
Code block
1 START TRANSACTION;
2 INSERT INTO courses (code, title, units) VALUES ('SEC210', 'Intro to InfoSec',
3);
3 INSERT INTO enrollments (student_id, course_id, semester)
4 SELECT s.student_id, c.course_id, '2025-1'
5 FROM students s, courses c
6 WHERE s.full_name='Bianca Reyes' AND [Link]='SEC210';
7 COMMIT;
💾 Part 4: Backup / Export
• SQLite:
Code block
1 .output backup_lab01.sql
2 .dump
3 .output stdout
• MySQL:
Code block
1 mysqldump -u root -p college_lab01 > backup_lab01.sql
📤 Submission Requirements
Submit a ZIP file named:
Lastname_Firstname_Lab01.zip
It should contain:
1. [Link] – all your queries
2. output_screenshots folder
3. backup_lab01.sql
4. [Link] – short reflection (3–5 sentences)
🧾 Sample Reflection
This lab helped me understand how SQL commands interact with tables and how
relationships enforce data consistency. I learned that JOINs allow combining data from
multiple tables and that constraints prevent data duplication. Transactions were useful for
testing before committing changes.
🧮 Grading Rubric
Total: 10 Points
