DBMS Practical Assignment: Comprehensive Solutions
This document provides detailed answers for SYBCA DBMS
practical assignments, suitable for direct use in any PDF
converter.
1. ER Diagram of College Management System (Explanation)
The Entity-Relationship (ER) Diagram for a College Management
System models the key entities within a college environment and
their interconnections.
Entities:
Student
`StudentID` (Primary Key - PK)
`Name`
`DOB` (Date of Birth)
`Gender`
`Email`
`Phone`
`Address`
Department
`DeptID` (Primary Key - PK)
`DeptName`
`HOD` (Head of Department)
Course
`CourseID` (Primary Key - PK)
`CourseName`
`Credits`
`DeptID` (Foreign Key - FK, referencing Department)
Instructor
`InstructorID` (Primary Key - PK)
`Name`
`Email`
`DeptID` (Foreign Key - FK, referencing Department)
Class
`ClassID` (Primary Key - PK)
`CourseID` (Foreign Key - FK, referencing Course)
`InstructorID` (Foreign Key - FK, referencing Instructor)
`Semester`
`Year`
`Room`
Enrollment
`EnrollID` (Primary Key - PK)
`StudentID` (Foreign Key - FK, referencing Student)
`ClassID` (Foreign Key - FK, referencing Class)
`EnrollmentDate`
`Grade`
Relationships:
Department offers Course:
A `Department` can offer one or many `Courses` (1-M).
Instructor belongs to Department:
An `Instructor` belongs to one `Department`, but a `Department`
can have many `Instructors` (M-1).
Instructor teaches Class:
An `Instructor` can teach one or many `Classes` (1-M).
Student enrolls in Class:
A `Student` can enroll in many `Classes`, and a `Class` can have
many `Students`. This is a Many-to-Many (M-M) relationship,
resolved through the `Enrollment` entity.
2. DDL for Supplier–Part–Supplies Database
This section provides the Data Definition Language (DDL)
statements to create the tables for a Supplier-Part-Supplies
database.
Create Supplier table:
```sql
CREATE TABLE Supplier (
SID INT PRIMARY KEY,
Sname VARCHAR(100),
branch VARCHAR(50),
city VARCHAR(50),
phone VARCHAR(20)
);
```
`SID`: Supplier ID, an integer, serving as the primary key to
uniquely identify each supplier.
`Sname`: Supplier Name, a string of up to 100 characters.
`branch`: Supplier's branch location, a string of up to 50
characters.
`city`: City where the supplier is located, a string of up to 50
characters.
`phone`: Supplier's phone number, a string of up to 20
characters.
Create Part table:
```sql
CREATE TABLE Part (
PID INT PRIMARY KEY,
Pname VARCHAR(100),
color VARCHAR(30),
price DECIMAL(10,2)
);
```
`PID`: Part ID, an integer, serving as the primary key to uniquely
identify each part.
`Pname`: Part Name, a string of up to 100 characters.
`color`: Color of the part, a string of up to 30 characters.
`price`: Price of the part, a decimal number with 10 total digits
and 2 digits after the decimal point.
Create Supplies table:
```sql
CREATE TABLE Supplies (
SID INT,
PID INT,
qty INT,
date_supplied DATE,
PRIMARY KEY (SID, PID, date_supplied),
FOREIGN KEY (SID) REFERENCES Supplier(SID),
FOREIGN KEY (PID) REFERENCES Part(PID)
);
```
`SID`: Supplier ID, an integer, acting as a foreign key referencing
the `Supplier` table.
`PID`: Part ID, an integer, acting as a foreign key referencing the
`Part` table.
`qty`: Quantity of the part supplied, an integer.
`date_supplied`: Date when the part was supplied.
`PRIMARY KEY (SID, PID, date_supplied)`: A composite primary
key ensuring uniqueness for each specific part supplied by a
specific supplier on a specific date.
`FOREIGN KEY (SID) REFERENCES Supplier(SID)`: Establishes a
link to the `Supplier` table, ensuring data integrity.
`FOREIGN KEY (PID) REFERENCES Part(PID)`: Establishes a link to
the `Part` table, ensuring data integrity.
3. ALTER Commands (Add/Remove/Modify Columns)
This section demonstrates how to use `ALTER TABLE` commands
to modify the structure of existing tables.
Add an attribute (column):
To add a new column named `state` with a `VARCHAR(30)` data
type to the `Supplier` table:
```sql
ALTER TABLE Supplier ADD state VARCHAR(30);
```
Remove a column:
To remove the `city` column from the `Supplier` table:
```sql
ALTER TABLE Supplier DROP COLUMN city;
```
Modify data type of a column:
The syntax for modifying a column's data type can vary slightly
between different SQL database systems.
For MySQL:
To change the `phone` column's data type in the `Supplier` table
to `BIGINT`:
```sql
ALTER TABLE Supplier MODIFY phone BIGINT;
```
For PostgreSQL:
To change the `phone` column's data type in the `Supplier` table
to `BIGINT`:
```sql
ALTER TABLE Supplier ALTER COLUMN phone TYPE BIGINT;
```
4. Sample DML (Data Manipulation Language) - 10 Records Each
This section provides `INSERT` statements to populate the
`Supplier` and `Part` tables with sample data.
Supplier Inserts:
```sql
INSERT INTO Supplier VALUES
(1,'Mike','local','Delhi','9876543210');
INSERT INTO Supplier VALUES
(2,'Rita','global','Mumbai','9876501234');
INSERT INTO Supplier VALUES
(3,'Arun','local','Delhi','9876512345');
INSERT INTO Supplier VALUES
(4,'Sunita','regional','Pune','9876523456');
INSERT INTO Supplier VALUES
(5,'John','global','Chennai','9876534567');
INSERT INTO Supplier VALUES
(6,'Asha','local','Delhi','9876539876');
INSERT INTO Supplier VALUES
(7,'Vikas','regional','Bengaluru','9876540001');
INSERT INTO Supplier VALUES
(8,'Rahul','global','Hyderabad','9876541112');
INSERT INTO Supplier VALUES
(9,'Priya','local','Delhi','9876542223');
INSERT INTO Supplier VALUES
(10,'Sameer','regional','Kolkata','9876543334');
```
Part Inserts:
```sql
INSERT INTO Part VALUES (101,'Bolt','Silver',150);
INSERT INTO Part VALUES (102,'Nut','Silver',20);
INSERT INTO Part VALUES (103,'Washer','Black',5);
INSERT INTO Part VALUES (104,'Gear','Grey',500);
INSERT INTO Part VALUES (105,'Screw','Silver',10);
INSERT INTO Part VALUES (106,'Bearing','Grey',350);
INSERT INTO Part VALUES (107,'Valve','Black',750);
INSERT INTO Part VALUES (108,'Pipe','Silver',250);
INSERT INTO Part VALUES (109,'Gasket','Red',45);
INSERT INTO Part VALUES (110,'Spring','Metallic',80);
☐ ```