Design a Database Schema for an Application Using ER Diagram from Problem Description

---

1. Aim

To analyze a problem description, identify entities and relationships, draw the Entity–Relationship (ER) diagram, and convert it into a relational database schema with appropriate constraints.

---

2. Theory

2.1 ER Model

The Entity–Relationship (ER) model is a conceptual data modeling technique used to represent real-world objects (entities) and the relationships among them. It provides a high-level view of a system and is typically the first step in database design.

2.2 Key Components

1. Entity:
   A real-world object that is distinguishable from other objects.
   Examples: Student, Course, Employee.

2. Attributes:
   Properties that describe an entity.
   Examples: StudentID, Name, DOB.

3. Relationship:
   Describes how two or more entities interact.
   Examples: A Student enrolls in a Course.

4. Cardinality Constraints:
   Define the number of instances participating in a relationship.

   • One-to-One (1:1)

   • One-to-Many (1:N)

   • Many-to-Many (M:N)

5. Keys:

   • Primary Key (PK): Uniquely identifies an entity.

   • Foreign Key (FK): Establishes relationship between tables.

2.3 ER to Relational Mapping

1. Entity → Table

2. Attributes → Columns

3. Primary key → Unique identifier

4. 1:N Relationship → FK on many-side table

5. M:N Relationship → Create a new associative table

---

3. Problem Description (Example)

A university wants to develop a database for managing student course registrations.

The system requires the following:

• The university maintains information about Students (StudentID, Name, Email, Department).

• It also stores information about Courses (CourseID, CourseName, Credits).

• Each student can register for multiple courses.

• Each course can have many students.

• For each registration, the system must record the Semester and Year.

---

4. Identification of Entities and Relationships

Entities

1. Student
   Attributes: StudentID (PK), Name, Email, Department

2. Course
   Attributes: CourseID (PK), CourseName, Credits

3. Registration (Associative Entity for M:N relationship)
   Attributes: RegID (PK), Semester, Year
   Relationships:

   • Student (1:N)

   • Course (1:N)

Relationships

• A Student registers for a Course. (M:N)

• Implemented via Registration table.

---

5. ER Diagram (Textual Representation)

If you want, I can generate this as an image or a canvas diagram.

Student (StudentID, Name, Email, Department)
        |
        | 1:N
        |
Registration (RegID, Semester, Year)
        |
        | N:1
        |
Course (CourseID, CourseName, Credits)

---

6. Relational Schema

STUDENT

Attribute	Type	Constraint
StudentID	INT	PRIMARY KEY
Name	VARCHAR(50)	NOT NULL
Email	VARCHAR(50)	UNIQUE
Department	VARCHAR(50)	NOT NULL

COURSE

Attribute	Type	Constraint
CourseID	INT	PRIMARY KEY
CourseName	VARCHAR(100)	NOT NULL
Credits	INT	CHECK(Credits > 0)

REGISTRATION

Attribute	Type	Constraint
RegID	INT	PRIMARY KEY
StudentID	INT	FOREIGN KEY REFERENCES Student
CourseID	INT	FOREIGN KEY REFERENCES Course
Semester	VARCHAR(10)	NOT NULL
Year	INT	CHECK (Year >= 2000)

---

7. SQL Implementation (Optional for Lab)

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(50) UNIQUE,
    Department VARCHAR(50) NOT NULL
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT CHECK (Credits > 0)
);

CREATE TABLE Registration (
    RegID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    Semester VARCHAR(10) NOT NULL,
    Year INT CHECK (Year >= 2000),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

---

8. Results

• Successfully analyzed the problem description.

• Identified entities, attributes, and relationships.

• Designed ER diagram and generated relational schema.

• Implemented the schema in SQL.

---

9. Conclusion

The ER model provides a systematic way to design a structured and efficient database from a real-world problem description. Converting the ER diagram into relational schema ensures the logical structure is preserved for implementation in DBMS.