Relational Schema Design for University and Hospital Systems

Posted on Dec 25, 2025 in Computers

ER Diagram Conversion to Relational Schema

The objective is to convert specified Entity-Relationship (ER) diagrams into a functional relational schema, defining appropriate primary and foreign keys.

A. University Database Relational Schema Mapping

Assuming the ER diagram for the University includes the following entities and relationships (based on typical university data models):

Entities Defined:

• Student: (sid, sname, age, major)
• Course: (cid, cname, credits)
• Professor: (pid, pname, department)
• Department: (dept_id, dept_name)
• Enrollment: (sid, cid, grade)
• Teaches: (pid, cid)

Relational Schema with Primary Keys (PK) and Foreign Keys (FK):

1. STUDENT

   sid INT PRIMARY KEY
   sname VARCHAR(50)
   age INT
   major VARCHAR(50)

2. COURSE

   cid INT PRIMARY KEY
   cname VARCHAR(100)
   credits INT

3. PROFESSOR

   pid INT PRIMARY KEY
   pname VARCHAR(50)
   department INT
   FOREIGN KEY (department) REFERENCES DEPARTMENT(dept_id)

4. DEPARTMENT

   dept_id INT PRIMARY KEY
   dept_name VARCHAR(50)

5. ENROLLMENT (Relationship)

   sid INT
   cid INT
   grade VARCHAR(2)
   PRIMARY KEY (sid, cid)
   FOREIGN KEY (sid) REFERENCES STUDENT(sid)
   FOREIGN KEY (cid) REFERENCES COURSE(cid)

6. TEACHES (Relationship)

   pid INT
   cid INT
   PRIMARY KEY (pid, cid)
   FOREIGN KEY (pid) REFERENCES PROFESSOR(pid)
   FOREIGN KEY (cid) REFERENCES COURSE(cid)

B. Hospital Management System Relational Schema

Assuming the Hospital ER diagram includes these entities and relationships:

Entities Defined:

• Patient: (pid, pname, age, gender, address)
• Doctor: (did, dname, specialization)
• Appointment: (aid, pid, did, appointment_date)
• Room: (room_no, type, charges_per_day)
• Admission: (admission_id, pid, room_no, admit_date, discharge_date)

Relational Schema with All Constraints:

1. PATIENT

   pid INT PRIMARY KEY
   pname VARCHAR(50)
   age INT
   gender VARCHAR(10)
   address VARCHAR(100)

2. DOCTOR

   did INT PRIMARY KEY
   dname VARCHAR(50)
   specialization VARCHAR(50)

3. APPOINTMENT

   aid INT PRIMARY KEY
   pid INT
   did INT
   appointment_date DATE
   FOREIGN KEY (pid) REFERENCES PATIENT(pid)
   FOREIGN KEY (did) REFERENCES DOCTOR(did)

4. ROOM

   room_no INT PRIMARY KEY
   type VARCHAR(20)
   charges_per_day DECIMAL(10,2)

5. ADMISSION

   admission_id INT PRIMARY KEY
   pid INT
   room_no INT
   admit_date DATE
   discharge_date DATE
   FOREIGN KEY (pid) REFERENCES PATIENT(pid)
   FOREIGN KEY (room_no) REFERENCES ROOM(room_no)

SQL Data Manipulation Language (DML) Commands

The objective is to practice the INSERT, UPDATE, and DELETE commands in SQL.

A. Inserting Records and Updating Data

Insert at least five sample records into the Student and Course tables. Then, update the contact number of one student.

Step 1: Create Tables

Note: The Student table structure here includes contact_no, differing slightly from the ER schema above.

CREATE TABLE Student (
    sid INT PRIMARY KEY,
    sname VARCHAR(50),
    age INT,
    contact_no VARCHAR(15)
);

CREATE TABLE Course (
    cid INT PRIMARY KEY,
    cname VARCHAR(50),
    credits INT
);

Step 2: Insert Sample Records

Inserting into Student table:

INSERT INTO Student VALUES (1, 'Ravi', 20, '9876543210');
INSERT INTO Student VALUES (2, 'Anita', 21, '9123456789');
INSERT INTO Student VALUES (3, 'Rahul', 19, '9001122334');
INSERT INTO Student VALUES (4, 'Meena', 22, '9898989898');
INSERT INTO Student VALUES (5, 'Karan', 20, '9812345678');

Inserting into Course table:

INSERT INTO Course VALUES (101, 'Mathematics', 4);
INSERT INTO Course VALUES (102, 'Physics', 3);
INSERT INTO Course VALUES (103, 'Chemistry', 3);
INSERT INTO Course VALUES (104, 'Biology', 4);
INSERT INTO Course VALUES (105, 'History', 2);

Step 3: Update the Contact Number of One Student

Updating contact number of student with sid = 3:

UPDATE Student
SET contact_no = '9999999999'
WHERE sid = 3;

B. Deleting Records with Conditions

Delete a course from the Course table that no student is enrolled in. This demonstrates the use of DELETE with a condition.

Prerequisite: Enrollment Table Setup

CREATE TABLE Enrollment (
    sid INT,
    cid INT,
    FOREIGN KEY (sid) REFERENCES Student(sid),
    FOREIGN KEY (cid) REFERENCES Course(cid)
);

Step: Delete Course Not Enrolled by Any Student

Delete course which has no entry in the Enrollment table:

DELETE FROM Course
WHERE cid NOT IN (
    SELECT DISTINCT cid FROM Enrollment
);

Conclusion

• Successfully inserted multiple records into Student and Course tables.
• Updated a student’s contact number using the UPDATE command.
• Deleted a course not enrolled by any student using a conditional DELETE statement with a subquery.