Practical No: 03

Practical Title: Introduction to SQL, DDL, DML, DCL, database and table creation,
alteration, defining constraints, primary key, foreign key, unique, not null, check.

1. Database schema diagram.

2. SQL Scripts used

CREATE DATABASE StudentManagementDB;
USE StudentManagementDB;
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE Department_Master(

Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(50) NOT NULL UNIQUE,
HOD_Name VARCHAR(50) NOT NULL);

CREATE TABLE Student_Master(

Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Age INT CHECK (Age>=18),
Department_ID INT,
Admission_Date DATE NOT NULL,
Phone BIGINT NOT NULL,
FOREIGN KEY (Department_ID) REFERENCES Department_Master(Department_ID));
CREATE TABLE Faculty_Master(
Faculty_ID INT PRIMARY KEY,
Faculty_Name VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Department_Master(Department_ID));

CREATE TABLE Course_Master(

Course_ID VARCHAR(10) PRIMARY KEY,
Course_Name VARCHAR(50) NOT NULL,
Credits INT CHECK(Credits BETWEEN 1 AND 6),
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Department_Master(Department_ID));

CREATE TABLE Enrollment_Master(

Enrollment_ID INT PRIMARY KEY,
Student_ID INT,
Course_ID VARCHAR(10),
Enrollment_Date DATE NOT NULL,
Grade CHAR(1),
FOREIGN KEY(Student_ID) REFERENCES Student_Master(Student_ID),
FOREIGN KEY(Course_ID) REFERENCES Course_Master(Course_ID),
CHECK(Grade IN('A','B','C','D','F')));

INSERT INTO Department_Master VALUES

(10,'Computer Science','Dr. Mehta'),
(20,'Information Technology','Dr. Shah'),
(30,'Cyber Security','Dr. Patel'),
(40,'Data Science','Dr. Joshi'),
(50,'Artificial Intelligence','Dr. Trivedi');
INSERT INTO Faculty_Master VALUES
(101,'Prof. Shah','shah@[Link]',10),
(102,'Prof. Patel','patel@[Link]',20),
(103,'Prof. Joshi','joshi@[Link]',30),
(104,'Prof. Mehta','mehta@[Link]',40),
(105,'Prof. Trivedi','trivedi@[Link]',50);
INSERT INTO Course_Master VALUES
('CS101','Database Systems',4,10),
('CS102','Data Structures',4,10),
('IT201','Cloud Computing',3,20),
('CY301','Ethical Hacking',4,30),
('DS401','Data Analytics',4,40),
('AI501','Machine Learning',5,50);
INSERT INTO Student_Master VALUES
(1001,'Aarav Sharma','[Link]@[Link]',18,10,'2024-07-
01',9876543210),
(1002,'Vivaan Patel','[Link]@[Link]',19,20,'2024-07-
01',9876543211),
(1003,'Aditya Singh','[Link]@[Link]',20,30,'2024-07-
01',9876543212),
(1004,'Krishna Verma','[Link]@[Link]',18,40,'2024-07-
01',9876543213),
(1005,'Arjun Gupta','[Link]@[Link]',21,50,'2024-07-01',9876543214),
(1006,'Rohan Mehta','[Link]@[Link]',19,10,'2024-07-01',9876543215),
(1007,'Kunal Shah','[Link]@[Link]',20,20,'2024-07-01',9876543216),
(1008,'Yash Joshi','[Link]@[Link]',18,30,'2024-07-01',9876543217),
(1009,'Dhruv Desai','[Link]@[Link]',22,40,'2024-07-01',9876543218),
(1010,'Harsh Trivedi','[Link]@[Link]',19,50,'2024-07-
01',9876543219),
(1011,'Priya Patel','[Link]@[Link]',18,10,'2024-07-01',9876543220),
(1012,'Ananya Shah','[Link]@[Link]',20,20,'2024-07-01',9876543221),
(1013,'Sneha Gupta','[Link]@[Link]',21,30,'2024-07-01',9876543222),
(1014,'Riya Mehta','[Link]@[Link]',19,40,'2024-07-01',9876543223),
(1015,'Kavya Joshi','[Link]@[Link]',18,50,'2024-07-01',9876543224),
(1016,'Pooja Verma','[Link]@[Link]',22,10,'2024-07-01',9876543225),
(1017,'Ishita Sharma','[Link]@[Link]',20,20,'2024-07-
01',9876543226),
(1018,'Neha Singh','[Link]@[Link]',19,30,'2024-07-01',9876543227),
(1019,'Aditi Desai','[Link]@[Link]',21,40,'2024-07-01',9876543228),
(1020,'Muskan Patel','[Link]@[Link]',18,50,'2024-07-
01',9876543229),
(1021,'Rahul Sharma','[Link]@[Link]',20,10,'2024-07-
01',9876543230),
(1022,'Siddharth Gupta','[Link]@[Link]',19,20,'2024-07-
01',9876543231),
(1023,'Manav Shah','[Link]@[Link]',21,30,'2024-07-01',9876543232),
(1024,'Tanish Patel','[Link]@[Link]',18,40,'2024-07-
01',9876543233),
(1025,'Nikhil Joshi','[Link]@[Link]',22,50,'2024-07-
01',9876543234),
(1026,'Bhavya Mehta','[Link]@[Link]',19,10,'2024-07-
01',9876543235),
(1027,'Riddhi Shah','[Link]@[Link]',20,20,'2024-07-01',9876543236),
(1028,'Khushi Verma','[Link]@[Link]',18,30,'2024-07-
01',9876543237),
(1029,'Tanvi Trivedi','[Link]@[Link]',21,40,'2024-07-
01',9876543238),
(1030,'Devansh Patel','[Link]@[Link]',19,50,'2024-07-
01',9876543239);
INSERT INTO Enrollment_Master VALUES
(5001,1001,'CS101','2024-08-01','A'),
(5002,1002,'IT201','2024-08-01','B'),
(5003,1003,'CY301','2024-08-01','A'),
(5004,1004,'DS401','2024-08-01','C'),
(5005,1005,'AI501','2024-08-01','B'),
(5006,1006,'CS101','2024-08-02','A'),
(5007,1007,'IT201','2024-08-02','B'),
(5008,1008,'CY301','2024-08-02','A'),
(5009,1009,'DS401','2024-08-02','D'),
(5010,1010,'AI501','2024-08-02','A'),
(5011,1011,'CS102','2024-08-03','B'),
(5012,1012,'IT201','2024-08-03','C'),
(5013,1013,'CY301','2024-08-03','A'),
(5014,1014,'DS401','2024-08-03','B'),
(5015,1015,'AI501','2024-08-03','A'),
(5016,1016,'CS102','2024-08-04','D'),
(5017,1017,'IT201','2024-08-04','B'),
(5018,1018,'CY301','2024-08-04','C'),
(5019,1019,'DS401','2024-08-04','A'),
(5020,1020,'AI501','2024-08-04','B'),
(5021,1021,'CS101','2024-08-05','A'),
(5022,1022,'IT201','2024-08-05','B'),
(5023,1023,'CY301','2024-08-05','A'),
(5024,1024,'DS401','2024-08-05','C'),
(5025,1025,'AI501','2024-08-05','B'),
(5026,1026,'CS101','2024-08-06','A'),
(5027,1027,'IT201','2024-08-06','A'),
(5028,1028,'CY301','2024-08-06','B'),
(5029,1029,'DS401','2024-08-06','A'),
(5030,1030,'AI501','2024-08-06','C'),
(5031,1001,'CS102','2024-08-07','B'),
(5032,1002,'CS101','2024-08-07','A'),
(5033,1003,'IT201','2024-08-07','B'),
(5034,1004,'CY301','2024-08-07','A'),
(5035,1005,'DS401','2024-08-07','B'),
(5036,1006,'AI501','2024-08-08','A'),
(5037,1007,'CS102','2024-08-08','C'),
(5038,1008,'CS101','2024-08-08','B'),
(5039,1009,'IT201','2024-08-08','A'),
(5040,1010,'CY301','2024-08-08','B'),
(5041,1011,'DS401','2024-08-09','A'),
(5042,1012,'AI501','2024-08-09','B'),
(5043,1013,'CS102','2024-08-09','A'),
(5044,1014,'CS101','2024-08-09','D'),
(5045,1015,'IT201','2024-08-09','B'),
(5046,1016,'CY301','2024-08-10','A'),
(5047,1017,'DS401','2024-08-10','B'),
(5048,1018,'AI501','2024-08-10','A'),
(5049,1019,'CS102','2024-08-10','C'),
(5050,1020,'CS101','2024-08-10','A');

UPDATE Department_Master SET HOD_Name='Dr. R. Mehta' WHERE Department_ID=10;

UPDATE Student_Master SET Email='newmail@[Link]' WHERE
Student_ID=1001;
DELETE FROM Enrollment_Master WHERE Student_ID=1005;
ALTER TABLE Student_Master ADD Mobile_Number VARCHAR(15);
ALTER TABLE Student_Master ADD Gender CHAR(1);
ALTER TABLE Student_Master MODIFY Student_Name VARCHAR(100);
ALTER TABLE Student_Master ADD CONSTRAINT chk_gender CHECK (Gender IN
('M','F'));
 DESCRIBE Student_Master;
SELECT * FROM Student_Master;
SELECT * FROM Student_Master WHERE Department_ID=10;
SELECT * FROM Student_Master WHERE Age>20;
SELECT * FROM Course_Master WHERE Credits>3;
SELECT * FROM Enrollment_Master WHERE Grade='A';
SELECT * FROM Student_Master WHERE Email LIKE '%@%';
-- DCL (run as root/admin)
CREATE USER'lab_user'@'localhost' IDENTIFIED BY 'lab123';
GRANT SELECT ON StudentManagementDB.Student_Master TO 'lab_user'@'localhost';
GRANT INSERT ON StudentManagementDB.Enrollment_Master TO
'lab_user'@'localhost';
REVOKE INSERT ON StudentManagementDB.Enrollment_Master FROM
'lab_user'@'localhost';
FLUSH PRIVILEGES;

3. Screenshots of execution
4. Constraint Validation Screenshots

5. ALTER TABLE Screenshots

6. DCL Commands

7. The Student Management Database was successfully implemented using

MySQL Workbench. The assignment demonstrated the use of DDL, DML, DCL,
constraints, table alteration, and querying. It provided practical experience in
designing and managing a relational database.