Developing the Greenwood Academy Database: A Comprehensive Guide
In this article we will talk about the creation of a database that is made for Greenwood Academy. We will cover parts of Structured Query Language including database design, data manipulation, querying and techniques to make things work better. This project is meant to manage student records, courses that are available and exam results in a way. After reading this article you will have an idea of how to set up and work with a database that is based on relations through SQL.
Project Overview
The database for Greenwood Academy will have important parts:
Students: Information about the students who're in the academy.
Subjects: Details about the subjects that are taught at the academy.
Exam Results: Records of how students did in their exams.
Step 1: Database Design
Before writing any SQL commands we need to create the structure of the database. This means finding the tables what they hold and how they are connected to each other.
. Attributes
- Students Table:
Student_id (Primary Key)
First_name
Last_name
Gender
Date_of_birth
Class
City
- Subjects Table:
Subject_id (Primary Key)
Subject_name
Department
Teacher_name
Credits
- Exam Results Table:
Result_id (Primary Key)
Student_id (Foreign Key that connects to Students)
Subject_id (Foreign Key that connects to Subjects)
Marks
Exam_date
Grade
Entity-Relationship Diagram (ERD)
The connections can be shown like this:
Each student can have exam results.
Each exam result is connected to one subject.
Step 2: Building the Database (DDL)
Using SQL Data Definition Language we can create our tables:
SQL Code for Creating Tables
-- Create schema
SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;
-- Create students table
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHAR(1)
date_of_birth DATE,
class VARCHAR(10)
city VARCHAR(50)
);
-- Create subjects table
CREATE TABLE subjects (
subject_id INT PRIMARY KEY
subject_name VARCHAR(100) NOT UNIQUE
department VARCHAR(50)
teacher_name VARCHAR(100)
credits INT
);
-- Create exam_results table
CREATE TABLE exam_results (
result_id INT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL
marks INT NOT NULL
exam_date DATE,
grade VARCHAR(2)
FOREIGN KEY (student_id) REFERENCES students(student_id)
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);
Step 3: Adding Information to the Database (DML)
After we create the tables we need to add some sample information. This part is very important to show how things work in the world.
SQL Code for Adding Data
-- Add students information
INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city) VALUES
(1, 'Amina' 'Wanjiku' 'F' '2008-03-12' 'Form 3' 'Nairobi')
(2, 'Brian' 'Ochieng' 'M' '2007-07-25' 'Form 4' 'Mombasa')
(3, 'Cynthia' 'Mutua' 'F' '2008-11-05' 'Form 3' 'Kisumu');
-- Add subjects information
INSERT INTO subjects (subject_id, subject_name, department, teacher_name, credits) VALUES
(1, 'Mathematics 'Sciences 'Mr. Njoroge' 4)
(2, 'English' 'Languages 'Ms. Adhiambo' 3)
(3, 'Biology' 'Sciences 'Ms. Otieno' 4);
-- Add exam results information
INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date, grade) VALUES
(1, 1 1 78 '2024-03-15' 'B')
(2, 2 1 92 '2024-03-15' 'A')
(3, 3 2 49 '2024-03-16' 'D');
Step 4: Getting Information from the Database
Now that the database has data we can ask for information. Using the features of SQL we can find, sort and change the data as needed.
Some Common SQL Queries
- Find all students in Form 4:
SELECT * FROM students WHERE class = 'Form 4';
- Get all subjects in the Sciences department:
SELECT * FROM subjects WHERE department = 'Sciences;
- Get all exam results that have marks of 70 or more:
SELECT * FROM exam_results WHERE marks >= 70;
Step 5: Using Operators to Get Specific Data
SQL has operators that let us make more detailed queries.
Examples of Using Range and Membership Operators
- Find exam results where marksre between 50 and 80:
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;
- Find all subjects that have the word 'Studies in their name:
SELECT * FROM subjects WHERE subject_name LIKE '%Studies%';
Step 6: Using COUNT to Summarize Data
The COUNT() function is a tool that helps us summarize data.
Example of Counting Entries
- Count how many studentsre in Form 3 right now:
SELECT COUNT(*) FROM students WHERE class = 'Form 3';
Step 7: Using Conditional Logic with CASE WHEN
The CASE statement helps us use logic in our SQL queries.
Examples of CASE WHEN
- Give a description of the performance for each exam result:
SELECT result_id, student_id, subject_id, marks,
CASE
WHEN marks >= 80 THEN 'Distinction'
WHEN marks >= 60 THEN 'Merit'
WHEN marks >= 40 THEN 'Pass
ELSE 'Fail'
END AS performance
FROM exam_results;
- Give a level to each student based on their class:
SELECT first_name, last_name, class,
CASE
WHEN class IN ('Form 3' 'Form 4') THEN 'Senior'
WHEN class IN ('Form 1' 'Form 2') THEN 'Junior'
END student_level
FROM students;
Creating the Greenwood Academy database shows the main steps of database design, building adding data and getting data. We looked at how to manage student, subject and exam result data using SQL. As shown SQL has abilities to store, change and get data making it a very useful tool in the tech world.
By using the ideas and ways shown in this article you will be ready, for database projects making sure that data is handled and found easily.
Top comments (0)