PART – B ANSWERS (DBMS)
1. Relational Model vs Hierarchical Model
Relational Model: Data stored in tables (relations) with rows and columns. Example: Student(StudentID, Name, Dept)
Hierarchical Model: Data stored in tree structure (parent-child). Example: Company → Department → Employee.
2. ER Diagram (Example: Hospital System)
Entities: Patient(PatientID, Name, Age), Doctor(DoctorID, Name, Specialization), Appointment(AppID, Date).
Generalization: Staff → Doctor, Nurse.
Aggregation: Appointment connects Patient and Doctor.
3. ER Diagram for Hospital
Entities: Patient, Doctor, Test(TestID, TestName, Result).
Relationship: Patient undergoes Test; Doctor conducts Test.
Patient(1) – (M) Test.
4. Architecture of DBMS
Components:
- Query Processor
- Storage Manager
- Database Engine
- Transaction Manager
Three-level architecture:
- External Level
- Conceptual Level
- Internal Level
5. Database Design: Student, Course, Faculty
Entities:
Student(SID, Name, Dept)
Course(CID, Title, Credits)
Faculty(FID, Name, Dept)
Relationships:
Enroll(Student-Course) M:N
Teaches(Faculty-Course) 1:M
Keys:
SID, CID, FID are Primary Keys.
Mapping Constraints:
One faculty teaches many courses.
One student enrolls in many courses.
6. Banking Management System Tables (SQL)
CREATE TABLE Customer(
CustID INT PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
);
CREATE TABLE Account(
AccNo INT PRIMARY KEY,
CustID INT,
Balance DECIMAL(10,2),
FOREIGN KEY (CustID) REFERENCES Customer(CustID)
);
CREATE TABLE Employee(
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10,2)
);
CREATE TABLE Transaction(
TransID INT PRIMARY KEY,
AccNo INT,
Amount DECIMAL(10,2),
FOREIGN KEY (AccNo) REFERENCES Account(AccNo)
);
7. Relational Algebra Queries
i) π empname (σ companyname='First Bank Corporation' (works))
ii) π empname,street,city (σ companyname='First Bank Corporation' AND salary>200000 (employee ■ works))
iii) π empname (σ [Link] = [Link] (employee ■ works ■ company))
8. SQL DDL with Constraints
CREATE TABLE Company(
CompanyName VARCHAR(50) PRIMARY KEY,
City VARCHAR(50)
);
CREATE TABLE Employee(
EmployeeName VARCHAR(50) PRIMARY KEY,
Street VARCHAR(50),
City VARCHAR(50)
);
CREATE TABLE Works(
EmployeeName VARCHAR(50),
CompanyName VARCHAR(50),
Salary DECIMAL(10,2),
PRIMARY KEY(EmployeeName),
FOREIGN KEY(EmployeeName) REFERENCES Employee(EmployeeName),
FOREIGN KEY(CompanyName) REFERENCES Company(CompanyName)
);
CREATE TABLE Manager(
EmployeeName VARCHAR(50),
ManagerName VARCHAR(50),
PRIMARY KEY(EmployeeName),
FOREIGN KEY(EmployeeName) REFERENCES Employee(EmployeeName),
FOREIGN KEY(ManagerName) REFERENCES Employee(EmployeeName)
);
9. Hospital SQL Queries
i) Join Query:
SELECT [Link], [Link], [Link]
FROM Patient p
JOIN Appointment a ON [Link] = [Link]
JOIN Doctor d ON [Link] = [Link];
ii) View for restricted data:
CREATE VIEW Patient_View AS
SELECT PatientID, Name FROM Patient;
iii) Daily Appointment Report:
SELECT Date, COUNT(*) AS Total_Appointments
FROM Appointment
GROUP BY Date;