Objectives

Identify key entities relationships: Determine how entities interact with each other.

Incorporate inheritance: Utilize inheritance to represent common attributes and behaviors of entities.

Define constraints and additional attributes: Specify any constraints related to the entities, such as cardinalities and mandatory$/$optional attributes.

Facilitate future expansion: Design the EERD to allow for future additions of new entities or features.

Implementation. Implement database, add initial data, and run multiple test queries.

Project Phases

Requirement Analysis

Objective: Understand the data needs of the university and Identify the Stakeholders:

Key Entities

Student

Attributes: StudentID $($PK$),$ FirstName, LastName, Email, DateOfBirth, EnrollmentDate

Relationships: Enrolls in Courses, Has Grades

Course

Attributes: CourseID $($PK$),$ CourseName, Credits, Semester

Relationships: Has Students, Has Instructors

Instructor

Attributes: InstructorID $($PK$),$ FirstName, LastName, Email, HireDate

Relationships: Teaches Courses

Department

Attributes: DepartmentID $($PK$),$ DepartmentName, Budget

Relationships: Contains Courses, Has Instructors

Grade

Attributes: GradeID $($PK$),$ GradeValue, DateAssigned

Relationships: Belongs to Student, Related to Course

Inheritance Structure

Person $($Superclass$)$

Attributes: PersonID $($PK$),$ FirstName, LastName, Email

Subclasses: Student, Instructor

Relationships and Cardinalities

A Student can enroll in one or many Courses.

A Course can have zero or many Students enrolled.

An Instructor can teach one or many Courses.

A Department can have zero or $m$ Instructors and Courses.

Extended Attributes and Constraints

Course:

Attributes: Prerequisites $($optional$),$ MaxEnrollment $($mandatory$)$

Grade:

Constraints: GradeValue must be between $0$ and $100.$

Conceptual Design

Objective: Create a high$-$level model of the database.

Design an Extended$-$Entity$-$Relationship $($ERR$)$ Diagram

$3.$ Logical Design

Objective: Map the conceptual model to a logical schema.

Normalize the data to avoid redundancy and ensure data integrity:

First Normal Form $(1$NF$)$: Ensure each attribute has atomic values.

Second Normal Form $(2$NF$)$: Ensure no partial dependencies.

Third Normal Form $(3$NF$)$: Ensure no transitive dependencies.

Physical Design

Objective: Define the physical structure of the database for implementation.

Key Activities:

Define tables, columns, data types, and indexes:

Example Table for Student:

sql

Copy code

CREATE TABLE Student $($

StudentID INT PRIMARY KEY,

Name VARCHAR$(100),$

DOB DATE,

ContactInfo VARCHAR$(255)$

;

$5.$ Implementation

Objective: Build and populate the database.

Key Activities:

Create the database schema using SQL$.$

Populate the database with initial data.

Test the database with sample queries:

Retrieve all students enrolled in a specific course.

Find courses taught by a specific instructor.