Name: Hapeya Permit

Course Code: CASB101

Course Name: Introduction to Databases Systems for Data Science
Assignment Title: University Student Information Management System and Company
Database (Case Study 1 and 2)
Semester: 01
Student ID: 202600845
Mode of Study: Distance
Phone Number: 0771177340
 Case Study 1: Entities and Attributes
In designing the University Student Information Management System, the following five key
entities and their attributes have been identified:
Student
 Student ID (Primary Key)
 Name
 Date Of Birth
 Gender
 Address
 Email
 Phone Number
Lecturer
 Lecturer ID (Primary Key)
 Name
 Department ID (Foreign Key)
 Email
 Phone Number
Course
 Course ID (Primary Key)
 Title
 Credits
 Department ID (Foreign Key)
Department
 Department ID (Primary Key)
 Name
 Faculty
 Location
Enrollment
 Enrollment ID (Primary Key)
 Student ID (Foreign Key)
 Course ID (Foreign Key)
 Semester
 Grade
 Explanation
The Student entity captures all personal and academic details of learners.
The Lecturer entity stores information about teaching staff and their departmental affiliation.
The Course entity defines academic offerings, linked to departments.
The Department entity organizes courses and lecturers under specific faculties.
The Enrollment entity connects students to courses, recording semester and grades.

Entity–Relationship Diagram

Figure X: ER diagram of the University Student Information Management System

The ER diagram above consists of five entities: Student, Course, Lecturer, Department, and
Enrollment. Each entity contains attributes relevant to the university’s operations.

Entities and Attributes

 Student → StudentID, Name, Email, Phone

 Course → CourseID, Title, Credits, DepartmentID

 Enrollment → EnrollmentID, StudentID, CourseID, Semester, Grade

 Lecturer → LecturerID, Name, DepartmentID, Email, Phone

 Department → DepartmentID, Name, Faculty, Location

 Relationships
 A Student can enroll in many courses (via Enrollment).
 A Course can have many students enrolled.
 A Department offers many courses.
 A Department has many lecturers.
Cardinalities and Participation Constraints
 Student–Enrollment → One student can have many enrollments; each enrollment must
belong to one student (mandatory).
 Course–Enrollment → One course can have many enrollments; each enrollment must
belong to one course (mandatory).
 Department–Course → One department offers many courses; each course must belong
to one department (mandatory).
 Department–Lecturer → One department has many lecturers; each lecturer must belong
to one department (mandatory).
The ER diagram clearly shows how the entities are connected. Cardinalities ensure that students
can register for multiple courses, courses can be taken by multiple students, and departments
manage both courses and lecturers. Participation constraints guarantee that no enrollment exists
without linking a student to a course, and no course or lecturer exists without belonging to a
department.
Transformation into Relational Tables
The ER diagram was transformed into relational tables in MySQL Workbench. Each entity
became a table, with primary keys defined to uniquely identify records and foreign keys to
enforce relationships.

Figure X: Student table populated with sample data.

Primary & Foreign Keys
 StudentID → Primary Key in Student
 CourseID → Primary Key in Course
 DepartmentID → Primary Key in Department
 LecturerID → Primary Key in Lecturer
 EnrollmentID → Primary Key in Enrollment
 StudentID & CourseID in Enrollment → Foreign Keys referencing Student and Course

Normalization (3NF)
Normalization was applied to ensure the database design is efficient, consistent, and free from
redundancy. The process followed the standard forms up to Third Normal Form (3NF):
 Unnormalized Form (UNF): Initially, all student, course, lecturer, department, and
enrollment details could be stored in a single large table. This design contained repeating
groups and redundant data.
  First Normal Form (1NF): The data was reorganized so that each attribute contains
atomic values only. Repeating groups were removed by separating information into
distinct tables such as Student, Course, Department, Lecturer, and Enrollment.
 Second Normal Form (2NF): Partial dependencies were eliminated. In the Enrollment
table, attributes like Semester and Grade depend on the full primary key (EnrollmentID
or the combination of StudentID and CourseID), not just part of it. This ensures that all
non-key attributes depend on the whole key.
 Third Normal Form (3NF): Transitive dependencies were removed. For example,
Department information was separated from Course and Lecturer tables instead of being
stored in Enrollment. This prevents duplication and ensures that non-key attributes
depend only on the primary key of their table.
Final Normalized Tables
 Student (StudentID, Name, DOB, Gender, Email, Phone)
 Course (CourseID, Title, Credits, DepartmentID)
 Department (DepartmentID, Name, Faculty, Location)
 Lecturer (LecturerID, Name, DepartmentID, Email, Phone)
 Enrollment (EnrollmentID, StudentID, CourseID, Semester, Grade)

By normalizing up to 3NF, the database design avoids redundancy, ensures data integrity, and
supports efficient querying. Each entity is stored in its own table, relationships are enforced
through foreign keys, and attributes depend only on their respective primary keys.
 SQL Statements to Create Database and Apply Constraints
Database Creation
CREATE DATABASE UniversityDB;

USE UniversityDB;

Tables Creation with Constraints

CREATE
THIS CODE TABLE
BELOW WAS USED: Student ( THIS CODE BELOW WAS USED:

StudentID
CREATE TABLE Student ( INT PRIMARY KEY, CREATE TABLE Enrollment (

StudentIDName
INT PRIMARY KEY, EnrollmentID INT PRIMARY KEY,
VARCHAR(45) NOT NULL,
StudentID INT NOT NULL,
Name VARCHAR(45) NOT NULL,
DOB DATE NOT NULL,
CourseID INT NOT NULL,
DOB DATE NOT NULL,
Gender VARCHAR(10) NOT NULL, Semester VARCHAR(45) NOT NULL,
Gender VARCHAR(10) NOT NULL,
Email VARCHAR(45) UNIQUE, Grade VARCHAR(5),
Email VARCHAR(45) UNIQUE,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
Phone VARCHAR(20)
Phone VARCHAR(20)
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
);

Student Table
Enrollment Table

 Primary Key: StudentID uniquely identifies each student.  Primary Key: EnrollmentID uniquely identifies each
 NOT NULL: Name, DOB, and Gender must always be provided. enrollment record.
 UNIQUE: Email prevents duplicate entries.  NOT NULL: StudentID, CourseID, and Semester must always
be provided.
 Foreign Keys: StudentID links to Student, CourseID links to
Course — enforcing relationships
This section presents the SQL statements used to create the UniversityDB schema. Primary keys
uniquely identify records, foreign keys enforce relationships between tables, NOT NULL ensures
required fields are always filled, and UNIQUE prevents duplicate values. Together, these
constraints guarantee data integrity and consistency in the database.

Conclusion on Case Study 1.

In this case study, a University Student Information Management System was successfully
designed and implemented using MySQL. The process began with identifying key entities and
attributes, followed by constructing an ER diagram to illustrate relationships and cardinalities.
The diagram was then transformed into relational tables with primary and foreign keys, ensuring
proper normalization up to Third Normal Form (3NF). Finally, SQL statements were used to
create the database and apply constraints such as PRIMARY KEY, FOREIGN KEY, NOT
NULL, and UNIQUE. This design minimizes redundancy, enforces data integrity, and supports
efficient querying of academic records. The completed schema provides a reliable foundation for
managing student registration, course enrollment, grade recording, and transcript generation in a
centralized and consistent manner.

Case Study 2: Company Database

This case study involves designing and implementing a company database. The company is
organized into Departments, each controlling multiple Projects. Employees work for one
department, may participate in several projects, and can have dependents. The database must
capture relationships between these entities, enforce referential integrity, and allow queries to
demonstrate functionality.
Conceptual Design (ER Diagram)
The conceptual design is represented using an Entity-Relationship (ER) diagram.
 Entities: Department, Employee, Project, Dependent, Works_On
 Relationships:
o Department ↔ Employee (one-to-many)

o Department ↔ Project (one-to-many)

o Employee ↔ Dependent (one-to-many)

o Employee ↔ Employee (supervisor relationship)

o Employee ↔ Project (many-to-many via Works_On)

Figure 1: ER diagram showing Department, Employee, Project, Dependent, and Works_On
relationships

Logical Design (Relational Schema)

The ER diagram was translated into relational tables with primary keys (PKs) and foreign keys
(FKs).
 Department (DeptID PK, Name, Number, ManagerID, StartDate, Location)
 Employee (EmpID PK, SSN, Name, Address, Salary, Sex, BirthDate, DeptID FK,
SupervisorID FK)
 Project (ProjectID PK, Name, Number, Location, DeptID FK)
 Dependent (DependentID PK, Name, Sex, BirthDate, Relationship, EmpID FK)
 Works_On (EmpID PK/FK, ProjectID PK/FK, HoursPerWeek)
 Physical Implementation (SQL Scripts)

The schema was implemented in MySQL Workbench using CREATE TABLE statements

Figure 2 shows action output for CREATE TABLE Department figure 3 shows action output for CREATE TABLE Employee

Figure 4 shows Action Output for CREATE TABLE Project figure 5 shows Action Output for CREATE TABLE Dependent
Figure 6 shows action output for CREATE TABLE Works_On

Verification

To confirm successful creation:

SHOW TABLES; was executed to list all tables.

Figure 7 SHOW TABLES result showing Department, Employee, Project, Dependent, Works_On
DESCRIBE TableName; was executed to display column definitions and constraints.

Figure 8 shows output on DESCRIBE Department

Figure 9 shows output on DESCRIBE Employee

Figure 10 shows output on DESCRIBE Project

Figure 11 shows output on DESCRIBE Dependent

 Figure 12 shows output on DESCRIBE Works_On

Sample Data (Optional)

To demonstrate functionality, sample records were inserted:

INSERT INTO Department VALUES (1, 'IT', 101, 2001, '2020-01-01', 'Lusaka');

INSERT INTO Employee VALUES (1, 'SSN001', 'John', 'Street 1', 5000, 'M', '1990-01-01', 1, NULL);

INSERT INTO Project VALUES (1, 'Payroll System', 1001, 'Lusaka', 1);

INSERT INTO Dependent VALUES (1, 'Mary', 'F', '2015-05-05', 'Daughter', 1);

INSERT INTO Works_On VALUES (1, 1, 20);

Figure 13 shows INSERT and SELECT*FROM TableName

Conclusion

The company database was successfully conceptualized, designed, and implemented. The ER
diagram illustrates the relationships, the relational schema enforces integrity, and the SQL
implementation confirms functionality.