Week 1 Project: Student Course Registration System

This project will introduce you to database creation, table design, and basic SQL queries using SQLite3 and SQLiteStudio. You will create a simple database to store student and course information, learning how to define tables, insert data, and retrieve information using SQL.

Objectives:

By the end of this project, you will:

✅ Understand how to create and structure a relational database using SQLite3.

✅ Learn to define tables, primary keys, and foreign keys.

✅ Insert data into tables and retrieve information using SELECT queries.

✅ Use basic SQL commands such as CREATE TABLE, INSERT, SELECT, WHERE, and JOIN.

✅ Gain hands-on experience with SQLiteStudio as a database management tool.

External resources

Step 0: Setting Up SQLiteStudio

Before you start, ensure that you have SQLiteStudio installed. Follow these steps:

Download SQLiteStudio from sqlitestudio.pl.
Install SQLiteStudio on your computer.
Open SQLiteStudio and click on Database -> Add a Database.
Choose “Create a new SQLite3 database”, and name the database university.db after clicking in the yellow folder button.
Click Save and ensure the database appears in the left panel.

Step 1. Creating Tables

Now, you will define three tables: students, courses, and enrollments.

Task: Create the students Table

This table stores information about students.

Open SQLiteStudio and select Tools -> Open SQL Editor.
Type the following SQL code:

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    birthdate DATE,
    major TEXT
);

Click Execute (the green play button).
Verify the table was created by clicking on Database → Refresh Structure.

Task: Create the courses Table

This table stores information about courses offered by the university.

In the SQL Editor, enter the following code:

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL
);

Click Execute and refresh the database structure.

Task: Create the enrollments Table

This table tracks which students are enrolled in which courses. It includes foreign keys linking to both the students and courses tables.

Enter the following SQL code:

CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Click Execute and refresh the database structure.

Warning

Checkpoint: Your database now contains three tables: students, courses, and enrollments.

Step 2. Inserting Data into Tables

Next, you will add sample data to test your database.

Task: Insert Students

Type and execute the following SQL commands to insert students:

INSERT INTO students (first_name, last_name, birthdate, major) VALUES
('Alice', 'Johnson', '2002-05-14', 'Computer Science'),
('Bob', 'Smith', '2001-09-20', 'Data Science'),
('Charlie', 'Davis', '2003-01-10', 'Business');

Warning

Checkpoint: Click on students table → View Data to confirm the data was inserted.

Task: Insert Courses

Now, insert some courses:

INSERT INTO courses (course_name, department) VALUES
('Database Management', 'Computer Science'),
('Machine Learning', 'Data Science'),
('Marketing Strategies', 'Business');

Warning

Checkpoint: Verify the courses table contains the inserted records.

Task: Enroll Students in Courses

Finally, insert sample enrollments:

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 3);

Warning

Checkpoint: Verify the enrollments table now contains data linking students to courses.

Step 3. Writing and Running SQL Queries

Now that your database is set up, you will write SQL queries to retrieve information.

Task 1: List All Courses in the Computer Science Department

Write a query to retrieve all courses offered in the Computer Science department.

-- This is a query to retrieve all courses from the Business department
SELECT * FROM courses WHERE department = 'Business';

✅ Expected Output: Should return the “Database Management” course.

Task 2: Retrieve All Students.

Write an SQL query to display all students in the database ordered by their last names.

-- This a query that returns all the students.
SELECT * FROM students;

✅ Expected Output: A list of all students with their student_id, first_name, last_name, birthdate, and major. They should be ordered in ascending order by ther last names.

Task 3: Find All Students Enrolled in “Database Management”

Retrieve the names of all students who are enrolled in the “Database Management” course.

SELECT s.first_name, s.last_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE c.course_name = 'Database Management';

✅ Expected Output:

Alice Johnson Bob Smith

Task 4: Count the Number of Students in Each Course

Write a query to count how many students are enrolled in each course.

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

✅ Expected Output: A table showing each course name and the number of enrolled students.

Final Deliverables

By the end of this project, you should have:

A SQLite3 database (university.db) with three tables:
- students, courses, and enrollments.
Successfully inserted and retrieved data using SQL.
Written four SQL queries to extract meaningful information from the database.
Verified all queries return the expected results on Moodle.

Bonus Challenge (Optional)

Modify the database by adding:

A new professors table and linking it to courses.
Additional constraints like UNIQUE or CHECK to enforce data integrity.

Grading Rubric

This project is worth 100 pts:

Grade will be given by the Quiz available on Moodle from Wednesday (3/19) until the following Wednesday (3/26).

--- title: "Week 1 Project: Student Course Registration System" format: html: css: /projects/proj-styles.css # engine: knitr eval: false --- <center> <figure> <img src="/images/projects/proj01-studog.gif" alt="Studog." width="500"/> <figcaption>Studoggy.</figcaption> </figure> </center> This project will introduce you to database creation, table design, and basic SQL queries using SQLite3 and SQLiteStudio. You will create a simple database to store student and course information, learning how to define tables, insert data, and retrieve information using SQL. ## Objectives: By the end of this project, you will: ✅ Understand how to create and structure a relational database using SQLite3. ✅ Learn to define tables, primary keys, and foreign keys. ✅ Insert data into tables and retrieve information using SELECT queries. ✅ Use basic SQL commands such as CREATE TABLE, INSERT, SELECT, WHERE, and JOIN. ✅ Gain hands-on experience with SQLiteStudio as a database management tool. ## External resources * [SQL Tutorial](https://www.w3schools.com/sql/){target="_blank"} * [SQLite Documentation](https://www.sqlite.org/docs.html){target="_blank"} ## Step 0: Setting Up SQLiteStudio Before you start, ensure that you have SQLiteStudio installed. Follow these steps: - Download SQLiteStudio from [sqlitestudio.pl](https://sqlitestudio.pl/){target="_blank"}. - Install SQLiteStudio on your computer. - Open SQLiteStudio and click on **Database -> Add a Database**. - Choose "Create a new SQLite3 database", and name the database **university.db** after clicking in the yellow folder button. - Click Save and ensure the database appears in the left panel. ## Step 1. Creating Tables Now, you will define three tables: `students`, `courses`, and `enrollments`. ### Task: Create the students Table This table stores information about students. 1. Open SQLiteStudio and select **Tools -> Open SQL Editor**. 2. Type the following SQL code: ```sql CREATE TABLE students ( student_id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT NOT NULL, last_name TEXT NOT NULL, birthdate DATE, major TEXT ); ``` 3. Click **Execute** (the green play button). 4. Verify the table was created by clicking on **Database → Refresh Structure**. ### Task: Create the courses Table This table stores information about courses offered by the university. 1. In the SQL Editor, enter the following code: ```sql CREATE TABLE courses ( course_id INTEGER PRIMARY KEY AUTOINCREMENT, course_name TEXT NOT NULL, department TEXT NOT NULL ); ``` 2. Click Execute and refresh the database structure. ## Task: Create the enrollments Table This table tracks which students are enrolled in which courses. It includes **foreign keys** linking to both the students and courses tables. 1. Enter the following SQL code: ```sql CREATE TABLE enrollments ( enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT, student_id INTEGER, course_id INTEGER, enrollment_date DATE DEFAULT CURRENT_DATE, FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); ``` 2. Click Execute and refresh the database structure. :::{.callout-warning} Checkpoint: Your database now contains three tables: `students`, `courses`, and `enrollments`. ::: ## Step 2. Inserting Data into Tables Next, you will add sample data to test your database. ### Task: Insert Students 1. Type and execute the following SQL commands to insert students: ```sql INSERT INTO students (first_name, last_name, birthdate, major) VALUES ('Alice', 'Johnson', '2002-05-14', 'Computer Science'), ('Bob', 'Smith', '2001-09-20', 'Data Science'), ('Charlie', 'Davis', '2003-01-10', 'Business'); ``` :::{.callout-warning} Checkpoint: Click on **students table → View Data** to confirm the data was inserted. ::: ### Task: Insert Courses Now, insert some courses: ```sql INSERT INTO courses (course_name, department) VALUES ('Database Management', 'Computer Science'), ('Machine Learning', 'Data Science'), ('Marketing Strategies', 'Business'); ``` :::{.callout-warning} Checkpoint: Verify the `courses` table contains the inserted records. ::: ### Task: Enroll Students in Courses Finally, insert sample enrollments: ```sql INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (2, 1), (2, 2), (3, 3); ``` :::{.callout-warning} Checkpoint: Verify the enrollments table now contains data linking students to courses. ::: ## Step 3. Writing and Running SQL Queries Now that your database is set up, you will write SQL queries to retrieve information. ### Task 1: List All Courses in the Computer Science Department Write a query to retrieve all courses offered in the Computer Science department. ```sql -- This is a query to retrieve all courses from the Business department SELECT * FROM courses WHERE department = 'Business'; ``` ✅ Expected Output: Should return the "Database Management" course. ### Task 2: Retrieve All Students. Write an SQL query to display all students in the database ordered by their last names. ```sql -- This a query that returns all the students. SELECT * FROM students; ``` ✅ Expected Output: A list of all students with their student_id, first_name, last_name, birthdate, and major. They should be ordered in ascending order by ther last names. ### Task 3: Find All Students Enrolled in "Database Management" Retrieve the names of all students who are enrolled in the "Database Management" course. ```sql SELECT s.first_name, s.last_name FROM students s JOIN enrollments e ON s.student_id = e.student_id JOIN courses c ON e.course_id = c.course_id WHERE c.course_name = 'Database Management'; ``` ✅ Expected Output: Alice Johnson Bob Smith ### Task 4: Count the Number of Students in Each Course Write a query to count how many students are enrolled in each course. ```sql SELECT c.course_name, COUNT(e.student_id) AS num_students FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id GROUP BY c.course_name; ``` ✅ Expected Output: A table showing each course name and the number of enrolled students. ## Final Deliverables By the end of this project, you should have: - A SQLite3 database (university.db) with three tables: - students, courses, and enrollments. - Successfully inserted and retrieved data using SQL. - Written four SQL queries to extract meaningful information from the database. - Verified all queries return the expected results on Moodle. ## Bonus Challenge (Optional) Modify the database by adding: - A new professors table and linking it to courses. - Additional constraints like UNIQUE or CHECK to enforce data integrity. ## Grading Rubric This project is worth 100 pts: Grade will be given by the Quiz available on Moodle from Wednesday (3/19) until the following Wednesday (3/26).