Experiment No.
9
Objective: Design and implementation of Library
Information System
Creating Database for library_db :-
CREATE DATABASE LibraryDB;
USE LibraryDB;
Adding Necessary tables :-
CREATE TABLE Authors (
author_id INT PRIMARY KEY,
author_name VARCHAR(100),
country VARCHAR(50)
);
CREATE TABLE Publishers (
publisher_id INT PRIMARY KEY,
publisher_name VARCHAR(100),
address VARCHAR(150)
);
CREATE TABLE Books (
book_id INT PRIMARY KEY,
title VARCHAR(150),
author_id INT,
publisher_id INT,
category VARCHAR(50),
price DECIMAL(6,2),
total_copies INT,
available_copies INT,
FOREIGN KEY (author_id) REFERENCES Authors(author_id),
FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id)
);
CREATE TABLE Members (
member_id INT PRIMARY KEY,
member_name VARCHAR(100),
phone VARCHAR(15),
address VARCHAR(150),
membership_date DATE
);
CREATE TABLE Staff (
staff_id INT PRIMARY KEY,
staff_name VARCHAR(100),
role VARCHAR(50),
phone VARCHAR(15)
);
CREATE TABLE Issued_Books (
issue_id INT PRIMARY KEY,
book_id INT,
member_id INT,
staff_id INT,
issue_date DATE,
due_date DATE,
FOREIGN KEY (book_id) REFERENCES Books(book_id),
FOREIGN KEY (member_id) REFERENCES Members(member_id),
FOREIGN KEY (staff_id) REFERENCES Staff(staff_id)
);
CREATE TABLE Returned_Books (
return_id INT PRIMARY KEY,
issue_id INT,
return_date DATE,
fine_amount DECIMAL(6,2),
FOREIGN KEY (issue_id) REFERENCES Issued_Books(issue_id)
);
Inserting Sample Values for the Database :-
INSERT INTO Authors VALUES
(1,'J.K. Rowling','UK'),
(2,'Chetan Bhagat','India'),
(3,'George Orwell','UK');
INSERT INTO Publishers VALUES
(1,'Penguin Books','Delhi'),
(2,'HarperCollins','Mumbai'),
(3,'Oxford Press','London');
INSERT INTO Books VALUES
(101,'Harry Potter',1,1,'Fantasy',499,10,10),
(102,'Five Point Someone',2,2,'Fiction',299,5,5),
(103,'1984',3,3,'Dystopian',399,6,6);
INSERT INTO Members VALUES
(1,'Ravi Kumar','9876543210','Delhi','2023-01-10'),
(2,'Priya Sharma','7894561230','Mumbai','2023-04-18');
INSERT INTO Staff VALUES
(1,'Arun Verma','Librarian','9988776655'),
(2,'Meera Singh','Assistant','8877665544');
INSERT INTO Issued_Books VALUES
(1,101,1,1,'2025-11-01','2025-11-15'),
(2,103,2,2,'2025-11-05','2025-11-20');
INSERT INTO Returned_Books VALUES
(1,1,'2025-11-14',0),
(2,2,'2025-11-25',50);
Implementing queries on Database :-
SELECT * FROM Books;
SELECT I.issue_id, [Link], M.member_name, S.staff_name, I.issue_date,
I.due_date
FROM Issued_Books I
JOIN Books B ON I.book_id = B.book_id
JOIN Members M ON I.member_id = M.member_id
JOIN Staff S ON I.staff_id = S.staff_id;
SELECT title FROM Books
WHERE author_id = 1;
SELECT M.member_name, [Link], I.due_date
FROM Issued_Books I
JOIN Members M ON I.member_id = M.member_id
JOIN Books B ON I.book_id = B.book_id
WHERE I.due_date < CURDATE();
SELECT category, SUM(available_copies) AS total_available
FROM Books
GROUP BY category;
SELECT R.return_id, [Link], M.member_name, R.fine_amount
FROM Returned_Books R
JOIN Issued_Books I ON R.issue_id = I.issue_id
JOIN Books B ON I.book_id = B.book_id
JOIN Members M ON I.member_id = M.member_id;
UPDATE Books
SET available_copies = available_copies - 1
WHERE book_id = 101;
UPDATE Books
SET available_copies = available_copies + 1
WHERE book_id = 101;
Experimant no. – 10
Objective: Design and implementation of Student Information
System
1. Creating sample Database for the studentDB :-
CREATE DATABASE StudentDB;
USE StudentDB;
CREATE TABLE Departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);
CREATE TABLE Students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
dob DATE,
gender VARCHAR(10),
phone VARCHAR(15),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);
CREATE TABLE Faculty (
faculty_id INT PRIMARY KEY,
faculty_name VARCHAR(100),
dept_id INT,
phone VARCHAR(15),
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);
CREATE TABLE Subjects (
subject_id INT PRIMARY KEY,
subject_name VARCHAR(100),
course_id INT,
faculty_id INT,
FOREIGN KEY (course_id) REFERENCES Courses(course_id),
FOREIGN KEY (faculty_id) REFERENCES Faculty(faculty_id)
);
CREATE TABLE Enrollments (
enroll_id INT PRIMARY KEY,
student_id INT,
course_id INT,
enroll_date DATE,
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
CREATE TABLE Marks (
mark_id INT PRIMARY KEY,
enroll_id INT,
subject_id INT,
marks_scored INT,
FOREIGN KEY (enroll_id) REFERENCES Enrollments(enroll_id),
FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id)
);
2. Inserting Sample Values for the Databse :-
INSERT INTO Departments VALUES
(1, 'Computer Science'),
(2, 'Electrical'),
(3, 'Mechanical'),
(4, 'Civil'),
(5, 'Electronics');
INSERT INTO Courses VALUES
(101, '[Link] CSE', 1),
(102, '[Link] EEE', 2),
(103, '[Link] ME', 3),
(104, '[Link] Civil', 4),
(105, '[Link] ECE', 5);
INSERT INTO Students VALUES
(1,'Ravi Kumar','2003-03-15','Male','9876543210',1),
(2,'Priya Sharma','2004-05-22','Female','7894561230',1),
(3,'Aman Verma','2003-08-10','Male','9988776655',2),
(4,'Kiran Patel','2004-02-05','Female','8877665544',3),
(5,'Neha Singh','2003-11-01','Female','7788996655',5);
INSERT INTO Faculty VALUES
(11,'Dr. Mehta',1,'9991112222'),
(12,'Prof. Sharma',2,'8884445555'),
(13,'Dr. Gupta',1,'7772223333'),
(14,'Prof. Reddy',3,'6663339999'),
(15,'Dr. Bose',5,'5558889999');
INSERT INTO Subjects VALUES
(201,'Data Structures',101,11),
(202,'Operating Systems',101,13),
(203,'Electrical Machines',102,12),
(204,'Thermodynamics',103,14),
(205,'Digital Electronics',105,15);
INSERT INTO Enrollments VALUES
(1,1,101,'2024-08-01'),
(2,2,101,'2024-08-01'),
(3,3,102,'2024-08-01'),
(4,4,103,'2024-08-01'),
(5,5,105,'2024-08-01');
INSERT INTO Marks VALUES
(1,1,201,85),
(2,1,202,78),
(3,2,201,90),
(4,2,202,88),
(5,5,205,92);
3. Applying Queries in Database:-
SELECT * FROM Students;
SELECT S.student_name, D.dept_name
FROM Students S
JOIN Departments D ON S.dept_id = D.dept_id;
SELECT F.faculty_name, S.subject_name
FROM Subjects S
JOIN Faculty F ON S.faculty_id = F.faculty_id;
SELECT E.enroll_id, St.student_name, C.course_name, E.enroll_date
FROM Enrollments E
JOIN Students St ON E.student_id = St.student_id
JOIN Courses C ON E.course_id = C.course_id;
SELECT St.student_name, Su.subject_name, M.marks_scored
FROM Marks M
JOIN Enrollments E ON M.enroll_id = E.enroll_id
JOIN Students St ON E.student_id = St.student_id
JOIN Subjects Su ON M.subject_id = Su.subject_id;
SELECT student_name
FROM Students
WHERE dept_id = 1;
UPDATE Students
SET phone='9990001111'
WHERE student_id=1;