“Heaven’s Light is Our Guide”

Rajshahi University of
Engineering & Technology
Department of Electrical & Computer
Engineering
ASSIGNMENT
Course Code: ECE-2215

Numerical Methods and Discrete

Course Name:
Mathematics

Date of
20-12-25
Submission:

SUBMITTED BY

Sanumong Marma,
Roll:2210061
Reg no:1115
Department of Electrical &
Computer Engineering,
22 Series

SUBMITTED TO
 Oishi Jyoti,
Assistant Professor,
Department of Electrical &
Computer Engineering,
RUET,Rajshahi

1. ABSTRACT

This laboratory report details the comprehensive design and implementation of a

relational database system for a University Management System. The project
encompasses the complete database development lifecycle, beginning with
conceptual modeling using Entity-Relationship (ER) diagrams, followed by logical
schema design, and culminating in physical implementation using SQL Data
Definition Language (DDL). The design emphasizes data integrity through
systematic constraint implementation and normalization principles. The resulting
schema supports core academic operations including student enrollment, course
management, instructor assignment, and departmental administration.

2. INTRODUCTION

2.1 Background

Modern educational institutions require robust database systems to manage

complex academic data. A University Management System must efficiently handle
interrelated entities including students, courses, instructors, and departments while
maintaining data consistency across numerous transactions.

2.2 Objectives

1. Design a conceptual model using Entity-Relationship diagrams

2. Transform the ER model into a normalized relational schema
3. Implement the schema using SQL with appropriate constraints
4. Ensure data integrity through referential and domain constraints
5. Demonstrate understanding of database design principles

2.3 Scope
The system encompasses:

a. Student information management

b. Course catalog and scheduling
c. Instructor assignment and tracking
d. Departmental organization
e. Enrollment and grade management

3. METHODOLOGY

3.1 Conceptual Design: ER Modeling

The design process commenced with requirement analysis to identify core entities
and relationships. Chen's ER notation was employed to create a conceptual model
capturing all business rules and constraints.

3.2 Logical Design: Relational Mapping

The ER model was transformed into relational schema following Codd's relational
model principles. Normalization to Third Normal Form (3NF) was applied to
eliminate data redundancy and anomalies.

3.3 Physical Design: SQL Implementation

The logical schema was implemented using SQL DDL statements with specific
considerations for:

a. Data type selection

b. Index creation for performance
c. Constraint definition for integrity
d. Referential action specification

4 DESIGN SPECIFICATION

4.1 Entity-Relationship

4.1.1 Entities
a. Student: Records academic and personal information
b. Course: Represents academic offerings with credit specifications
c. Instructor: Manages faculty information and assignments
d. Department: Organizes academic units and administration
e. Enrollment: Associative entity resolving M:N relationship (Student-Course)

4.1.2 Cardinalities

Relationships describe how entities in a database are connected, while cardinality

defines the number of entity instances that can participate in a relationship.

a. Belongs_to (Student–Department, M:1):

Each student is associated with one department, while a department can have
many students.
b. Offers (Department–Course, 1:M):
A department can offer multiple courses, but each course belongs to one
department.
c. Teaches (Instructor–Course, 1:M):
An instructor may teach several courses, whereas each course is taught by one
instructor.
d. Heads (Instructor–Department, 1:1):
One instructor heads one department, and each department has only one head.
e. Enrolls (Student–Course, M:N):
A student can enroll in multiple courses, and each course can have many
students.
4.1.3 ER Diagram Schema

Fig. University Management System

4.2 Relational Schema

4.2.1 Table Specifications

The database schema uses primary keys to uniquely identify records and foreign
keys to maintain relationships between tables. Constraints are applied to ensure
data accuracy and integrity.

a. Department: Uses dept_code as the primary key with a UNIQUE constraint on

department name.
b. Student: Identified by student_id; the major field references Department, with a
CHECK constraint on enrollment year.
c. Instructor: Uses instructor_id; foreign keys link to Department, with UNIQUE
constraints on email and department head assignment.
d. Course: Identified by course_code; linked to Department and Instructor, with a
CHECK constraint ensuring positive credits.
e. Enrollment: Uses a composite primary key (student_id, course_code); foreign
keys ensure valid student–course entries and grade validity.

4.2.2 Normalization Analysis

a. 1NF: All attributes are atomic

b. 2NF: No partial dependencies (composite keys only in associative entity)
c. 3NF: No transitive dependencies (department separated from student/course)

5. IMPLEMENTATION

5.1 SQL Schema Definition

CREATE DATABASE UniversityDB;

USE UniversityDB;

CREATE TABLE Department (

dept_code VARCHAR(10) PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL UNIQUE,
office_location VARCHAR(100),
establishment_year INT CHECK (establishment_year > 1900),
budget DECIMAL(12,2) DEFAULT 0.00,
CONSTRAINT chk_budget_nonnegative CHECK (budget >= 0)
) ENGINE=InnoDB;

-- 5.1.2 Instructor Table

CREATE TABLE Instructor (
instructor_id INT AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
hire_date DATE NOT NULL,
department_code VARCHAR(10),
head_of_department VARCHAR(10) UNIQUE,
salary DECIMAL(10,2) CHECK (salary > 0),
CONSTRAINT fk_instructor_dept FOREIGN KEY (department_code)

REFERENCES Department(dept_code) ON DELETE SET NULL,

CONSTRAINT fk_head_of_dept FOREIGN KEY (head_of_department)
REFERENCES Department(dept_code) ON DELETE SET NULL,
INDEX idx_instructor_dept (department_code),
INDEX idx_instructor_name (last_name, first_name)
) ENGINE=InnoDB;

-- 5.1.3 Student Table

CREATE TABLE Student (
student_id INT AUTO_INCREMENT PRIMARY KEY,
ssn VARCHAR(11) UNIQUE NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
enrollment_year INT NOT NULL,
major_code VARCHAR(10) NOT NULL,
academic_status ENUM('Active', 'Inactive', 'Graduated',
'Suspended') DEFAULT 'Active',
CONSTRAINT chk_enrollment_year CHECK (enrollment_year >=
2000 AND enrollment_year <= YEAR(CURDATE())),
CONSTRAINT fk_student_major FOREIGN KEY (major_code)
REFERENCES Department(dept_code) ON DELETE RESTRICT,
INDEX idx_student_major (major_code),
INDEX idx_student_name (last_name, first_name)
) ENGINE=InnoDB;

-- 5.1.4 Course Table

CREATE TABLE Course (
course_code VARCHAR(20) PRIMARY KEY,
course_title VARCHAR(200) NOT NULL,
course_description TEXT,
credit_hours DECIMAL(3,1) NOT NULL,
department_code VARCHAR(10) NOT NULL,
instructor_id INT,
semester ENUM('Fall', 'Spring', 'Summer') NOT NULL,
 academic_year INT NOT NULL,
max_capacity INT DEFAULT 30,
current_enrollment INT DEFAULT 0,
CONSTRAINT chk_credit_hours CHECK (credit_hours > 0 AND
credit_hours <= 6),
CONSTRAINT chk_capacity CHECK (current_enrollment <=
max_capacity),
CONSTRAINT fk_course_dept FOREIGN KEY (department_code)
REFERENCES Department(dept_code) ON DELETE CASCADE,
CONSTRAINT fk_course_instructor FOREIGN KEY (instructor_id)
REFERENCES Instructor(instructor_id) ON DELETE SET
NULL,
INDEX idx_course_dept (department_code),
INDEX idx_course_instructor (instructor_id)
) ENGINE=InnoDB;

-- 5.1.5 Enrollment Table (Associative Entity)

CREATE TABLE Enrollment (
enrollment_id INT AUTO_INCREMENT,
student_id INT NOT NULL,
course_code VARCHAR(20) NOT NULL,
enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
enrollment_status ENUM('Registered', 'Dropped',
'Completed', 'Withdrawn') DEFAULT 'Registered',
grade DECIMAL(4,2) CHECK (grade >= 0 AND grade <= 4.0),
grade_letter CHAR(2),
attendance_percentage DECIMAL(5,2) DEFAULT 100.00,
CONSTRAINT pk_enrollment PRIMARY KEY (enrollment_id),
CONSTRAINT uk_student_course UNIQUE (student_id,
course_code),
CONSTRAINT chk_attendance CHECK (attendance_percentage >= 0
AND attendance_percentage <= 100),
CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id)
REFERENCES Student(student_id) ON DELETE CASCADE,
CONSTRAINT fk_enrollment_course FOREIGN KEY (course_code)
REFERENCES Course(course_code) ON DELETE CASCADE,
INDEX idx_enrollment_student (student_id),
INDEX idx_enrollment_course (course_code),
INDEX idx_enrollment_date (enrollment_date)
) ENGINE=InnoDB;

-- 5.1.6 Triggers for Business Logic

DELIMITER //

-- Trigger to update course enrollment count

CREATE TRIGGER update_enrollment_count_insert
AFTER INSERT ON Enrollment
FOR EACH ROW
BEGIN
UPDATE Course
SET current_enrollment = current_enrollment + 1
WHERE course_code = NEW.course_code;
END //

CREATE TRIGGER update_enrollment_count_delete

AFTER DELETE ON Enrollment
FOR EACH ROW
BEGIN
UPDATE Course
SET current_enrollment = current_enrollment - 1
WHERE course_code = OLD.course_code;
END //

-- Trigger to enforce grade letter assignment

CREATE TRIGGER assign_grade_letter
BEFORE UPDATE ON Enrollment
FOR EACH ROW
BEGIN
IF [Link] IS NOT NULL THEN
SET NEW.grade_letter = CASE
WHEN [Link] >= 3.7 THEN 'A'
WHEN [Link] >= 3.3 THEN 'A-'
WHEN [Link] >= 3.0 THEN 'B+'
WHEN [Link] >= 2.7 THEN 'B'
WHEN [Link] >= 2.3 THEN 'B-'
WHEN [Link] >= 2.0 THEN 'C+'
WHEN [Link] >= 1.7 THEN 'C'
WHEN [Link] >= 1.0 THEN 'D'
ELSE 'F'
END;
END IF;
END //

DELIMITER ;

5.2 Sample Data Population

INSERT INTO Department (dept_code, dept_name, office_location,

establishment_year) VALUES
('CSE', 'Computer Science', 'Engineering Building', 1995),
('ECE', 'Electrical Engineering', 'Tech Tower', 1985),
('MATH', 'Mathematics', 'Science Building', 1970);
 -- Insert sample instructors
INSERT INTO Instructor (first_name, last_name, email,
hire_date, department_code, salary) VALUES
('John', 'Smith', '[Link]@[Link]', '2010-08-15',
'CSE', 85000.00),
('Sarah', 'Johnson', 'sarah.j@[Link]', '2015-03-20',
'ECE', 92000.00);

-- Update head of department

UPDATE Instructor SET head_of_department = 'CSE' WHERE
instructor_id = 1;
UPDATE Instructor SET head_of_department = 'ECE' WHERE
instructor_id = 2;

-- Insert sample students

INSERT INTO Student (ssn, first_name, last_name, date_of_birth,
email, enrollment_year, major_code) VALUES
('123-45-6789', 'Alice', 'Brown', '2000-05-15',
'alice.b@[Link]', 2022, 'CSE'),
('987-65-4321', 'Bob', 'Wilson', '2001-02-20',
'bob.w@[Link]', 2023, 'ECE');

-- Insert sample courses

INSERT INTO Course (course_code, course_title, credit_hours,
department_code, instructor_id, semester, academic_year) VALUES
('CSE101', 'Introduction to Programming', 3.0, 'CSE', 1,
'Fall', 2024),
('ECE201', 'Circuit Analysis', 4.0, 'ECE', 2, 'Fall', 2024);

-- Insert sample enrollments

INSERT INTO Enrollment (student_id, course_code) VALUES
(1, 'CSE101'),
(2, 'ECE201');

6. RESULTS AND ANALYSIS

6.1 Constraint Verification

Test Case 1: Referential Integrity

-- Should fail: Non-existent department

INSERT INTO Student (ssn, first_name, last_name, date_of_birth,
email, enrollment_year, major_code)
VALUES ('111-22-3333', 'Test', 'Student', '2002-01-01',
'test@[Link]', 2024, 'INVALID');
-- Result: FOREIGN KEY constraint violation

Test Case 2: Domain Constraints

-- Should fail: Invalid credit hours
INSERT INTO Course (course_code, course_title, credit_hours,
department_code, semester, academic_year)
VALUES ('TEST101', 'Test Course', 0, 'CSE', 'Fall', 2024);
-- Result: CHECK constraint violation (credit_hours > 0)

Test Case 3: Uniqueness Constraints

-- Should fail: Duplicate email
INSERT INTO Student (ssn, first_name, last_name, date_of_birth,
email, enrollment_year, major_code)
VALUES ('444-55-6666', 'Duplicate', 'Email', '2001-01-01',
'alice.b@[Link]', 2024, 'CSE');
-- Result: UNIQUE constraint violation

6.2 Query Performance Analysis

-- Index utilization verification

EXPLAIN SELECT * FROM Student WHERE last_name = 'Brown' AND
first_name = 'Alice';
-- Shows index usage: idx_student_name

EXPLAIN SELECT s.*, [Link]

FROM Student s
JOIN Enrollment e ON s.student_id = e.student_id
WHERE s.major_code = 'CSE';
-- Shows index usage: idx_student_major and
idx_enrollment_student

6.3 Transaction Consistency

-- Atomic transaction example

START TRANSACTION;

INSERT INTO Enrollment (student_id, course_code) VALUES (1,

'ECE201');
UPDATE Course SET current_enrollment = current_enrollment + 1
WHERE course_code = 'ECE201';

COMMIT;
-- Both operations succeed or fail together
7. DISCUSSION

7.1 Design Decisions Justification

7.1.1 Primary Key Selection

a. Surrogate keys (student_id, instructor_id) provide stability and performance

b. Natural keys (course_code, dept_code) maintain business meaning
c. Composite keys in Enrollment ensure uniqueness of student-course pairs

7.1.2 Referential Actions

a. ON DELETE CASCADE: Courses deleted when department removed (logical

dependency)
b. ON DELETE RESTRICT: Prevent department deletion with enrolled students
(business rule)
c. ON DELETE SET NULL: Maintain course when instructor leaves (data
preservation)

7.1.3 Index Strategy

a. B-tree indexes on foreign keys for join optimization

b. Composite indexes on frequently queried name combinations
c. Selective indexing based on query patterns

7.2 Normalization Trade-offs

Normalization is the process of organizing database tables to reduce redundancy

and ensure data integrity. Achieving Third Normal Form (3NF) ensures that each
non-key attribute depends only on the primary key and eliminates transitive
dependencies.

Denormalization is sometimes applied deliberately to improve performance. For

example:

A. Storing derived attributes like current_enrollment in the Course table speeds up

queries.
B. Keeping grade_letter alongside numeric grades allows faster reporting without
repeated calculations.

7.3 Scalability Considerations

Scalability ensures the database can handle growing data volumes and user loads
efficiently. Table partitioning, such as dividing the Student table by enrollment year,
improves query performance and maintenance. Sharding, based on departments,
enables distributed deployment and load balancing. Caching frequently accessed
data like course catalogs reduces database load and response time. Archiving
historical enrollment records helps manage storage growth while maintaining
system performance.

7.4 Security Implications

Database security is essential to protect sensitive information and ensure controlled

data access. Sensitive data such as SSN should be encrypted at the application layer
to prevent unauthorized exposure. Role-based access control (RBAC) is required to
restrict database operations based on user roles and responsibilities. An audit trail,
maintained through enrollment timestamps, supports accountability and activity
tracking. Additionally, data masking techniques should be applied to reporting
interfaces to prevent disclosure of confidential information while allowing analytical
use.

8. CONCLUSION

The University Management System database design effectively fulfills all specified
requirements using a structured three-tier design approach. The conceptual design
employs ER modeling to accurately represent academic entities and relationships.
The logical design applies normalization principles to reduce redundancy while
preserving efficiency, and the physical design uses SQL constraints to ensure
reliable data storage. Data integrity is maintained through primary keys (entity
integrity), foreign keys (referential integrity), and CHECK constraints with
appropriate data types (domain integrity). Business rules are enforced using
triggers and stored procedures. The design successfully meets CO3 objectives by
demonstrating practical database design skills and PO1 objectives by applying
engineering principles to solve complex data management problems. Future
enhancements may include advanced indexing, optimized reporting mechanisms,
and API-based system integration.

9. REFERENCES

[1] R. Elmasri and S. B. Navathe, Fundamentals of Database Systems,

7th ed. Pearson, 2016.

[2] A. Silberschatz, H. F. Korth, and S. Sudarshan, Database System

Concepts, 7th ed. McGraw-Hill, 2020.

[3] MySQL 8.0 Reference Manual, Oracle Corporation, 2024. [Online].

Available: [Link]
[4] C. J. Date, An Introduction to Database Systems, 8th ed. Addison-
Wesley, 2003.