Department: - INFORMATION TECHNOLOGY

Course Title: - Database System

Project Title: -Primary School Registrar System

Group Assignment

NAME ID

1. Tagel Shirenga------------------------------------------ CNCS/UE22191/13

2. Natnael Mola ------------------------------------------ CNCS/UE22170/13
3. Mahlet Lakew ------------------------------------------ CNCS/UE22155/13
4. Habtamu Shiferaw ------------------------------------ CNCS/UE22137/13
5. Abel Girma ---------------------------------------------- CNCS/UE22089/13
6. Kelem tibebe ------------------------------------------- CNCS/UE22 /13
Table of Contents
Table of Contents.................................................................................................................................... 0
1.1 Background Information ................................................................................................................. 2
1.2 Problems of the existing system ................................................................................................... 2
1.3 Scope of the project ...................................................................................................................... 3
1.4 Objective of the project ................................................................................................................ 3
1.5 Data Collection Methods .............................................................................................................. 3
1.6 Benefits and Beneficiaries of the new system .............................................................................. 4
2. Existing System Analysis ................................................................................................................... 5
2.1 Overview of purposes/services of the existing system ................................................................. 6
2.2 Business rules ............................................................................................................................... 7
2.3 Forms Used................................................................................................................................... 7
2.4 Reports generated ........................................................................................................................ 7
3. Database Design ................................................................................................................................. 7
3.1 Conceptual Database Design of the New System......................................................................... 7
3.2 Logical Database Design of the New System ............................................................................... 8
3.2.1 Relational Schema (before normalization) ............................................................................... 8
3.2.2 Normalization (1NF, 2NF, 3NF) (Normal Form) ..................................................................... 8
4 Implementation................................................................................................................................. 10
4.1 SQL Coding for:......................................................................................................................... 10
4.1.1 Database and table schemas.................................................................................................... 10
4.1.2 Inserting, updating and deletion of sample records ............................................................... 12
4.1.3 Sample Queries [related to users reports or query requirements identified] ........................ 13
1.1 Background Information
1.2 Problems of the existing system
The file-based registry system, also known as a paper-based registry system, refers
to a method of organizing and storing information using physical files and
documents. While this system was widely used in the past, it has several inherent
problems that have led to its gradual replacement by digital or electronic registry
systems. In this response, we will discuss some of the key problems associated with
file-based registry systems.
1. Limited Accessibility and Availability: One of the primary issues with file-based
registry systems is the limited accessibility and availability of information. Physical
files are typically stored in specific locations, such as filing cabinets or storage
rooms, making it difficult for multiple users to access them simultaneously. This can
lead to delays in retrieving information and hinder efficient decision-making
processes. Additionally, if a file is misplaced or lost, it can be challenging to locate
and retrieve the required information.
2. Space Constraints and Storage Issues: File-based registry systems require
significant physical space for storing paper documents. As organizations accumulate
more records over time, they face challenges related to storage capacity and
management. Filing cabinets and storage rooms can become overcrowded, making
it difficult to organize and locate specific files efficiently. Moreover, maintaining an
extensive collection of physical files requires regular maintenance, including
sorting, labeling, and archiving, which can be time-consuming and resource-
intensive.
3. Data Security and Integrity: File-based registry systems are inherently
vulnerable to various security risks. Physical files can be easily damaged due to
environmental factors like fire, water damage, or pests. Additionally, unauthorized
access to sensitive information is a concern since physical files can be stolen or
tampered with more easily compared to digital records protected by encryption and
access controls. Ensuring data integrity in a file-based system is also challenging
since there is no built-in mechanism for tracking changes or detecting alterations
made to the documents.
4. Limited Search ability and Retrieval Efficiency: Locating specific information
within a file-based registry system can be time-consuming and inefficient. Searching
for a particular document or piece of information requires manually going through
multiple files, which can be a tedious and error-prone process. Unlike digital systems
that offer advanced search functionalities, file-based systems rely on manual
indexing and categorization methods, making it difficult to quickly retrieve relevant
information.
5. Lack of Collaboration and Workflow Integration: File-based registry systems
often lack the ability to facilitate collaboration and integrate with other digital
workflows. Sharing physical files among multiple users can be cumbersome, leading
to delays in decision-making processes and hindering effective teamwork.
Additionally, integrating file-based systems with other digital tools or processes,
such as data analysis or automation, becomes challenging due to the lack of
interoperability.

1.3 Scope of the project

The scope of the project for a primary school registrar system encompasses various
aspects related to managing student records, enrollment and other administrative
tasks within a primary school setting. The primary goal of such a system is to
streamline and automate the processes involved in maintaining accurate and up-to-
date student and Employee information.

1.4 Objective of the project

The objective of the primary school registrar database system project is to create an
efficient and reliable system for managing student records and information in a
primary school setting. This database system aims to streamline administrative tasks,
improve data accuracy, enhance communication between stakeholders, and provide
valuable insights for decision-making processes.
One of the main goals of the primary school registrar system database system is to
centralize student information in a secure and easily accessible manner. By storing
all relevant data in a single database, it becomes easier for school administrators,
teachers, and other authorized personnel to retrieve and update student records. This
eliminates the need for manual paperwork and reduces the chances of errors or data
duplication.

1.5 Data Collection Methods

When it comes to collecting data for a primary school registrar database project,
there are several methods that can be used. These methods aim to gather accurate
and relevant information about students, their personal details, and academic
records. The choice of data collection methods depends on various factors such as
the size of the school, available resources, and the specific requirements of the
project. Here are some commonly used data collection methods for a primary school
registrar database project:
Interviews: Conducting interviews with students, registrar office and teachers can
provide valuable insights and additional information that may not be captured
through surveys alone. Interviews can be structured or unstructured depending on
the purpose of data collection. Structured interviews involve asking a set of
predetermined questions, while unstructured interviews allow for more open-ended
discussions. Interviews can be conducted in person or remotely through video
conferencing tools.
Observations: Observational methods involve directly observing students in their
natural environment within the school setting. This method can provide valuable
information about student behavior, interactions, and engagement in various
activities. Observations can be done by teachers or researchers who record their
observations using standardized protocols or checklists. The collected data can then
be entered into the registrar database.

1.6 Benefits and Beneficiaries of the new system

The new system primary school registrar database project brings numerous benefits
to both the educational institutions and the individuals involved. This comprehensive
database aims to streamline and improve the management of student records,
enrollment processes, and administrative tasks in primary schools. By centralizing
and digitizing these records.
The primary benefit of the new system primary school registrar database project is
the efficient management of student data. With a centralized database, schools can
easily store, update, and retrieve student information such as personal details and
academic records. This eliminates the need for manual paperwork and reduces the
chances of errors or data duplication. Administrators can quickly access relevant
information when needed, improving overall operational efficiency. Vantages for
various stakeholders.
2. Existing System Analysis
Existing System Analysis of Primary School File-Based Registrar System
The existing system of a primary school file-based registrar system refers to the
current method or process used by a primary school to manage and maintain student
and Employee information and other administrative tasks using physical files and
documents. This analysis aims to evaluate the strengths and weaknesses of such a
system and identify potential areas for improvement.
1. Data Management: One of the key aspects of the existing system is data
management. Student and Employee information, personal information, academic
performance are stored in physical files. This manual process requires significant
effort and time to create, update, and retrieve information. It also increases the risk
of data loss or damage due to mishandling or unforeseen events like fire or natural
disasters.
2. Accessibility and Retrieval: Another aspect to consider is the accessibility and
retrieval of information. In a file-based registrar system, finding specific records can
be time-consuming and prone to errors. Staff members need to physically search
through numerous files to locate the required information. This can lead to delays in
responding to inquiries or making informed decisions regarding students' academic
progress or disciplinary matters.
3. Data Security: The security of student data is crucial in any educational
institution. However, in a file-based registrar system, maintaining data security
becomes challenging. Physical files can be easily misplaced, lost, or accessed by
unauthorized individuals. Additionally, there is no backup mechanism in place to
protect against data loss or damage.
4. Reporting and Analysis: Generating reports and conducting data analysis is an
essential requirement for schools to monitor student performance, identify trends,
and make informed decisions. However, in a file-based system, generating
comprehensive reports can be time-consuming and error-prone. Analyzing data
across multiple files manually becomes a tedious task that may hinder effective
decision-making.
5. Collaboration and Communication: Effective collaboration among staff
members is vital for the smooth functioning of a primary school. However, in a file-
based registrar system, sharing and communicating information can be challenging.
Staff members may need to physically transfer files or rely on verbal
communication, increasing the chances of miscommunication or delays in accessing
critical information.
6. Scalability and Space Constraints: As a primary school grows, the volume of
student records also increases. Storing and managing physical files for an expanding
student population becomes increasingly difficult. The existing system may face
space constraints, requiring additional storage facilities or compromising the
organization and accessibility of records.

2.1 Overview of purposes/services of the existing system

The existing system of a primary school file-based registrar serves several purposes
and provides various services to efficiently manage and maintain student records.
This system is designed to handle the administrative tasks related to student
enrollment, Employee information and other essential information. The following is
an overview of the purposes and services offered by the primary school file-based
registrar system:
1. Student Enrollment: One of the primary purposes of the file-based registrar
system is to facilitate the enrollment process for new students. It allows school
administrators to collect and store necessary information such as student
demographics, contact details, previous educational history, and any special needs
or requirements. This information helps in creating student profiles and ensuring
accurate record-keeping.
2. Data Reporting and Analysis: The file-based registrar system generates various
reports and analytics to provide insights into student performance, attendance trends,
and other relevant data. These reports help administrators, teachers, and parents in
making informed decisions regarding student progress, curriculum planning,
resource allocation, and identifying areas for improvement.
3. Record Maintenance and Security: The file-based registrar system ensures the
secure storage and maintenance of student records. It allows authorized personnel to
access and update student information while maintaining data privacy and security
protocols. This includes implementing measures to protect sensitive information
from unauthorized access or data breaches.
4. Transcript Generation: The file-based registrar system assists in generating
official transcripts for students who are transitioning to higher grade levels or
transferring to other schools. It compiles academic records, grades, attendance
history, and other relevant information into a standardized format that can be shared
with educational institutions or employers.

2.2 Business rules

2.3 Forms Used
The primary school registrar is responsible for maintaining accurate records of
students enrolled in the school. To fulfill this role, the registrar uses various forms
to collect and manage student information. These forms serve as essential tools for
gathering data and ensuring that all necessary details are recorded correctly.
One of the most commonly used forms by primary school registrars is the
enrollment form. This form is typically filled out by parents or guardians when
registering their child for admission to the school. It collects basic information about
the student, such as their name, date of birth, address, contact details, and emergency
contact information. Additionally, it may include sections for providing details about
the student's previous education.
In some cases, primary school registrars may also use forms related to student
transfers or withdrawals form. These forms document the process of transferring`
a student from one school to another or withdrawing them from enrollment
altogether. They typically require information about the new school (in case of
transfer) or reasons for withdrawal.

2.4 Reports generated

The file-based registrar system generates various reports and analytics to provide
insights into student performance, attendance trends, and other relevant data. These
reports help administrators, teachers, and parents in making informed decisions
regarding student progress, curriculum planning, resource allocation, and identifying
areas for improvement.

3. Database Design
3.1 Conceptual Database Design of the New System
3.2 Logical Database Design of the New System

Student (s_id (primary key), first name, last name, date of birth, grade level, t_id
(foreign key references Teacher (t_id)))
Teacher (t_id (primary key), first name, last name, email, phone number)
Employee (e_id (primary key), first name, last name, position, email, phone number,
salary)
Course (c_id (primary key), course name, room number, capacity, t_id (foreign key
references Teacher (t_id)))
Classroom (cl_id (primary key), room number, course_id (foreign key references
Course (c_id)), t_id (foreign key references Teacher (t_id)))
Grades (g_id (primary key), s_id (foreign key references Student (s_id)), c_id
(foreign key references Course (c_id)), grade)
Registrar’s office (s_id (foreign key references Student (s_id)), e_id (foreign key
references Employee (e_id)), t_id (foreign key references Teacher (t_id)))

3.2.1 Relational Schema (before normalization)

Student(s_id, First Name, Last Name, Date of Birth, Grade Level, T_id)
Teacher (T_id, First Name, Last Name, Email, Phone Number)
Employee (E_id, First Name, Last Name, Position, Email, Phone Number, Salary)
Classrooms (cl_id, Course Name, T_ID)
Courses (c_id, Room Number, Capacity, T_id)
Grades (G_id, S_ID, C_ID, Grade)
Registrar’s office (S_ID, E_id, T_id)

3.2.2 Normalization (1NF, 2NF, 3NF) (Normal Form)

1NF: Student(s_id, First Name, Last Name, Date of Birth, Grade Level, T_id)
Teacher (T_id, First Name, Last Name, Email, Phone Number)
Employee (E_id, First Name, Last Name, Position, Email, Phone Number, Salary)
Classrooms (cl_id, Course Name, T_ID)
Courses (c_id, Room Number, Capacity, T_id)
Grades (G_id, S_ID, C_ID, Grade)
Registrars office (S_ID, E_id, T_id)
2NF: Teacher (T_id, First Name, Last Name, Email, Phone Number)
Employee (E_id, First Name, Last Name, Position, Email, Phone Number, Salary)
3NF: Student(s_id, First Name, Last Name, Date of Birth, Grade Level, T_id)
Courses (c_id, Room Number, Capacity, T_id)
Grades (G_id, S_ID, C_ID, Grade)
Registrars office (S_ID, E_id, T_id)
3.2.3 Relational Schema (after normalization)
Teacher (T_id, First Name, Last Name, Email, Phone Number)
Employee (E_id, First Name, Last Name, Position, Email, Phone Number, Salary)
Student(s_id, First Name, Last Name, Date of Birth, Grade Level, T_id)
Course (c_id, Room Number, Capacity, T_id)
Grades (G_id, S_ID, C_ID, Grade)
Registrars office (S_ID, E_id, T_id)
Classrooms (cl_id, Course Name, T_ID)
4 Implementation
4.1 SQL Coding for:
4.1.1 Database and table schemas
CREATE DATABASE primary school registrar system;
CREATE TABLE Students (

S_id INT PRIMARY KEY,

S_first_name VARCHAR (50) NOT NULL,

S_last_name VARCHAR (50) NOT NULL,

S_birthdate DATE NOT NULL,
S_grade_level INT NOT NULL,
T_id INT NOT NULL,

FOREIGN KEY (T_id) REFERENCES Teachers (T_id)

);

CREATE TABLE Teachers (

T_id INT PRIMARY KEY,
T_first_name VARCHAR (50) NOT NULL,

T_last_name VARCHAR (50) NOT NULL,

T_email VARCHAR (50) NOT NULL,

T_phone VARCHAR (20) NOT NULL

);

CREATE TABLE Employees (

E_id VARCHAR PRIMARY KEY,

E_first_name VARCHAR (50) NOT NULL,

E_last_name VARCHAR (50) NOT NULL,
E_position VARCHAR (50) NOT NULL,

E_email VARCHAR (50) NOT NULL,

E_phone VARCHAR (20) NOT NULL,

E_ Salary INT NOT NULL

);
CREATE TABLE Classrooms (

Cl_id VARCHAR PRIMARY KEY,

Cl_course_name VARCHAR (50) NOT NULL,

T_id INT NOT NULL,

FOREIGN KEY (T_id) REFERENCES Teachers (T_id)

);
CREATE TABLE Courses (

C_id INT PRIMARY KEY,

C_room_number INT NOT NULL,

C_capacity INT NOT NULL,

T_id INT NOT NULL,
FOREIGN KEY (T_id) REFERENCES Teachers (T_id)

);
CREATE TABLE Grades (

G_id INT PRIMARY KEY,

S_id INT NOT NULL,

C_id INT NOT NULL,

G_grade INT NOT NULL,
FOREIGN KEY (S_id) REFERENCES Students (s_id),
FOREIGN KEY (C_id) REFERENCES Courses (c_id)

);
CREATE TABLE Registrars_office (
S_id INT NOT NULL,

E_id INT NOT NULL,

T_id INT NOT NULL,

PRIMARY KEY (S_id, E_id, T_id),

FOREIGN KEY (S_id) REFERENCES Students (s_id),
 FOREIGN KEY (E_id) REFERENCES Employees (E_id),
FOREIGN KEY (T_id) REFERENCES Teachers (T_id)

);

4.1.2 Inserting, updating and deletion of sample records

INSERT INTO Students (s_id, `First Name`, `Last Name`, `Date of Birth`, `Grade Level`, T_id)
VALUES (001, ' Mikiyas ', ' Getachew ', '1998-04-01', 10, 101);

INSERT INTO Teachers (T_id, `First Name`, `Last Name`, Email, `Phone Number`)

VALUES (101, ' Belay ', ' Abraham ', 'belayabraham@gmail.com ', '0911121918');

INSERT INTO Employees (E_id, `First Name`, `Last Name`, Position, Email, `Phone Number`,
Salary)
VALUES (E-1, ' Girma ', ' Kassa ', ' Manager ', 'girmakassa0012@gmail.com', '0985547895',
5800);

INSERT INTO Classrooms (cl_id, `Course Name`, T_id)

VALUES (10, 'Math', 101);

INSERT INTO Courses (c_id, `Room Number`, Capacity, T_id)

VALUES (Math G10, 10A, 45, 101);

INSERT INTO Grades (G_id, S_id, C_id, Grade)

VALUES (10-1semester, 001, Math G10, 85);

INSERT INTO Registrars_office (S_id, E_id, T_id)

VALUES (001, E-1, 101);

Update student Mikiyas Getachew grade level to 11

UPDATE Students
SET ‘Grade Level’ = 11

WHERE S_id = 001;

Update teacher Belay Abraham phone number

UPDATE Teachers
SET ‘Phone Number’ = ‘0915141232’

WHERE T_id = 101;

Update employee Girma Kassa’s salary

UPDATE Employees
SET Salary = 45000

WHERE E_id = E-1;

Deleting records
Delete the classroom for the Math course

DELETE FROM Classrooms

WHERE cl_id = 10;

Delete the course with Math G10 and all related grades
DELETE FROM Grades

WHERE C_id = Math G10;

DELETE FROM Courses

WHERE c_id = Math G10;

4.1.3 Sample Queries [related to users reports or query requirements

identified]
Find all students in grade level 10
SELECT *
FROM Students

WHERE `Grade Level` = 10;

Find all teachers and their respective courses

SELECT Teachers. ‘First Name`, Teachers. ‘Last Name`, Courses. `
References
https://www.wikipedia.org/
https://www.w3schools.com/
https://www.edrawmax.com/ for Conceptual Database Design