1.
a) Student Database Table with Constraints
DDL:
CREATE TABLE Student (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
marks INT CHECK (marks BETWEEN 0 AND 100)
);
DML:
-- Insert rows
INSERT INTO Student (student_id, name, marks) VALUES
(1, 'Alice', 85),
(2, 'Bob', 28),
(3, 'Charlie', 45);
-- Update a row
UPDATE Student SET marks = 35 WHERE student_id = 2;
-- Delete a row
DELETE FROM Student WHERE student_id = 3;
1. b) PL/SQL Program for Moderation Strategy
Note: MySQL does not support PL/SQL natively. However, similar functionality can be achieved using stored
procedures.
DELIMITER //
CREATE PROCEDURE ApplyModeration()
BEGIN
UPDATE Student
SET marks = marks + 5
WHERE marks > 30;
END //
DELIMITER ;
-- Execute the procedure
CALL ApplyModeration();
2. a) Bus Reservation Database with Foreign Key Constraints
DDL:
CREATE TABLE Bus (
bus_id INT PRIMARY KEY,
bus_number VARCHAR(20) NOT NULL,
capacity INT NOT NULL
);
CREATE TABLE Passenger (
passenger_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
contact_info VARCHAR(100)
);
CREATE TABLE Reservation (
reservation_id INT PRIMARY KEY,
bus_id INT,
passenger_id INT,
reservation_date DATE,
FOREIGN KEY (bus_id) REFERENCES Bus(bus_id),
FOREIGN KEY (passenger_id) REFERENCES Passenger(passenger_id)
);
2. b) Triggers for Reservation and Cancellation
DELIMITER //
CREATE TRIGGER AfterReservation
AFTER INSERT ON Reservation
FOR EACH ROW
BEGIN
INSERT INTO ReservationLog (reservation_id, action, action_date)
VALUES (NEW.reservation_id, 'Reservation Made', NOW());
END //
CREATE TRIGGER AfterCancellation
AFTER DELETE ON Reservation
FOR EACH ROW
BEGIN
INSERT INTO ReservationLog (reservation_id, action, action_date)
VALUES (OLD.reservation_id, 'Reservation Cancelled', NOW());
END //
DELIMITER ;
-- Ensure the ReservationLog table exists
CREATE TABLE ReservationLog (
log_id INT AUTO_INCREMENT PRIMARY KEY,
reservation_id INT,
action VARCHAR(50),
action_date DATETIME
);
3. a) Query for Books Related to "Machine Learning"
SELECT * FROM Books
WHERE title LIKE '%Machine Learning%';
3. b) Procedure to Update Book Details After Purchase
DELIMITER //
CREATE PROCEDURE UpdateBookDetails(
IN book_id INT,
IN additional_copies INT
BEGIN
UPDATE Books
SET copies_available = copies_available + additional_copies
WHERE id = book_id;
END //
DELIMITER ;
-- Execute the procedure
CALL UpdateBookDetails(1, 10);
4. a) Product, Sales, and Purchase Tables
DDL:
CREATE TABLE Product (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
);
CREATE TABLE Sales (
sale_id INT PRIMARY KEY,
product_id INT,
quantity INT,
sale_date DATE,
FOREIGN KEY (product_id) REFERENCES Product(product_id)
);
CREATE TABLE Purchase (
purchase_id INT PRIMARY KEY,
product_id INT,
quantity INT,
purchase_date DATE,
FOREIGN KEY (product_id) REFERENCES Product(product_id)
);
4. b) Procedure to Add 20 Records to Each Table
DELIMITER //
CREATE PROCEDURE AddSampleRecords()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 20 DO
INSERT INTO Product (product_id, name, price)
VALUES (i, CONCAT('Product', i), i * 10);
INSERT INTO Sales (sale_id, product_id, quantity, sale_date)
VALUES (i, i, i * 2, CURDATE());
INSERT INTO Purchase (purchase_id, product_id, quantity, purchase_date)
VALUES (i, i, i * 3, CURDATE());
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
-- Execute the procedure
CALL AddSampleRecords();
5. a) Employee, Salary, and Department Tables with Aggregations
DDL:
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);
CREATE TABLE Employee_Personal (
emp_id INT PRIMARY KEY,
name VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
CREATE TABLE Salary (
emp_id INT PRIMARY KEY,
salary DECIMAL(10,2),
FOREIGN KEY (emp_id) REFERENCES Employee_Personal(emp_id)
);
Aggregations:
SELECT
MIN(salary) AS MinSalary,
MAX(salary) AS MaxSalary,
SUM(salary) AS TotalSalary,
AVG(salary) AS AvgSalary
FROM Salary;
5. b) Function to Update Salary with Incentives
DELIMITER //
CREATE FUNCTION UpdateSalaryWithIncentive(empId INT, incentive DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE new_salary DECIMAL(10,2);
UPDATE Salary
SET salary = salary + incentive
WHERE emp_id = empId;
SELECT salary INTO new_salary FROM Salary WHERE emp_id = empId;
RETURN new_salary;
END //
DELIMITER ;
-- Use the function
SELECT UpdateSalaryWithIncentive(1, 500.00);
6. a) Online Shopping Database and Subqueries
DDL:
CREATE TABLE Items (
item_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
stock INT
);
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE Purchases (
purchase_id INT PRIMARY KEY,
customer_id INT,
item_id INT,
quantity INT,
purchase_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
FOREIGN KEY (item_id) REFERENCES Items(item_id)
);
Subquery to Display Purchased Items:
SELECT * FROM Items
WHERE item_id IN (
SELECT item_id FROM Purchases WHERE customer_id = 1
);
6. b) Triggers to Display Available Items Before and After Purchase
DELIMITER //
CREATE TRIGGER BeforePurchase
BEFORE INSERT ON Purchases
FOR EACH ROW
BEGIN
DECLARE available_stock INT;
SELECT stock INTO available_stock FROM Items WHERE item_id = NEW.item_id;
IF available_stock < NEW.quantity THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
END IF;
END //
CREATE TRIGGER AfterPurchase
AFTER INSERT ON Purchases
FOR EACH ROW
BEGIN
UPDATE Items
SET stock = stock - NEW.quantity
WHERE item_id = NEW.item_id;
END //
DELIMITER ;
7. a) Wedding Hall Reservation Database and Join Operation
DDL:
CREATE TABLE WeddingHall (
hall_id INT PRIMARY KEY,
hall_name VARCHAR(100),
location VARCHAR(100)
);
CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE Booking (
booking_id INT PRIMARY KEY,
hall_id INT,
customer_id INT,
booking_date DATE,
FOREIGN KEY (hall_id) REFERENCES WeddingHall(hall_id),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);
Join Query:
SELECT b.booking_id, c.name AS customer_name, w.hall_name, b.booking_date
FROM Booking b
JOIN Customer c ON b.customer_id = c.customer_id
JOIN WeddingHall w ON b.hall_id = w.hall_id;
7. b) Procedure to Apply Booking Reduction
DELIMITER //
CREATE PROCEDURE ApplyBookingDiscount(IN bookingId INT, IN discount DECIMAL(10,2))
BEGIN
UPDATE Booking
SET total_amount = total_amount - discount
WHERE booking_id = bookingId;
END //
DELIMITER ;
-- Ensure the Booking table has a 'total_amount' column
ALTER TABLE Booking ADD COLUMN total_amount DECIMAL(10,2);
-- Execute the procedure
CALL ApplyBookingDiscount(1, 500.00);
8. a) Car Manufacturing Database with HAVING Clause
DDL:
CREATE TABLE CarModel (
model_id INT PRIMARY KEY,
model_name VARCHAR(100),
price DECIMAL(10,2)
);
Query Using HAVING Clause:
SELECT model_name, COUNT(*) AS count
FROM CarModel
GROUP BY model_name
HAVING AVG(price) > 20000;
8. b) Procedure to Insert and Update Car Records
DELIMITER //
CREATE PROCEDURE InsertOrUpdateCar(
IN modelId INT,
IN modelName VARCHAR(100),
IN modelPrice DECIMAL(10,2)
BEGIN
IF EXISTS (SELECT * FROM CarModel WHERE model_id = modelId) THEN
UPDATE CarModel
SET model_name = modelName, price = modelPrice
WHERE model_id = modelId;
ELSE
INSERT INTO CarModel (model_id, model_name, price)
VALUES (modelId, modelName, modelPrice);
END IF;
END //
DELIMITER ;
-- Execute the procedure
CALL InsertOrUpdateCar(1, 'Sedan X', 25000.00);
9. a) Student and Course Tables with Join Operations
DDL:
CREATE TABLE Student (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE Course (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
CREATE TABLE Enrollment (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
**
9. b) Procedure to Display Day Name Based on Day Number
DELIMITER //
CREATE PROCEDURE GetDayName(IN day_num INT, OUT day_name VARCHAR(20))
BEGIN
CASE day_num
WHEN 1 THEN SET day_name = 'Sunday';
WHEN 2 THEN SET day_name = 'Monday';
WHEN 3 THEN SET day_name = 'Tuesday';
WHEN 4 THEN SET day_name = 'Wednesday';
WHEN 5 THEN SET day_name = 'Thursday';
WHEN 6 THEN SET day_name = 'Friday';
WHEN 7 THEN SET day_name = 'Saturday';
ELSE SET day_name = 'Invalid Day Number';
END CASE;
END //
DELIMITER ;
-- Example usage:
CALL GetDayName(3, @output);
SELECT @output;
10. a) DCL Commands for User Privileges
Granting privileges:
-- Allow user1 to create, insert, update, and delete
GRANT CREATE, INSERT, UPDATE, DELETE ON your_database.* TO 'user1'@'localhost' IDENTIFIED BY 'password1';
-- Disallow DELETE only for user2 (REVOKE)
REVOKE DELETE ON your_database.* FROM 'user2'@'localhost';
10. b) Create XML-like Structure for Online Quiz and Results
MySQL doesn't support native XML databases, but XML data can be stored in a column.
CREATE TABLE OnlineQuiz (
quiz_id INT PRIMARY KEY,
quiz_title VARCHAR(100),
quiz_content TEXT
);
INSERT INTO OnlineQuiz (quiz_id, quiz_title, quiz_content)
VALUES (
1,
'General Knowledge Quiz',
'<?xml version="1.0"?>
<quiz>
<question>
<text>What is the capital of France?</text>
<option>Paris</option>
<option>Berlin</option>
</question>
<result>Passed</result>
</quiz>'
);
To extract or manipulate XML, consider using application logic or MySQL JSON fields instead.
11. a) Complex Transaction and Use of TCL Commands
START TRANSACTION;
-- Sample complex transaction
INSERT INTO Customer (customer_id, name) VALUES (101, 'John Doe');
INSERT INTO Purchases (purchase_id, customer_id, item_id, quantity, purchase_date)
VALUES (201, 101, 1, 2, CURDATE());
-- Suppose something goes wrong here
-- ROLLBACK; -- if error
COMMIT; -- if everything works fine
TCL Commands in MySQL:
START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT (optional intermediate checkpoints)
11b) Create Document, column and graph based data using NOSQL database tools.
1. Document-Based Data (e.g., Product Catalog)
Each document in a collection can have a flexible schema.
use CompanyDB
db.ProductCatalog.insertOne({
_id: 1,
name: "Laptop",
brand: "Dell",
price: 65000,
specs: {
RAM: "16GB",
Storage: "512GB SSD",
Processor: "Intel i7"
},
tags: ["electronics", "portable", "new"]
});
2. Column-Based Data (Simulated in MongoDB)
db.Metrics.insertOne({
user_id: 1001,
metric_1: 10,
metric_2: 45,
metric_3: 78,
metric_4: 12,
metric_5: 99
});
3. Graph-Based Data (Using Adjacency List Model)
db.Graph.insertMany([
{ node: 1, connected_to: [2, 3] },
{ node: 2, connected_to: [4] },
{ node: 3, connected_to: [] },
{ node: 4, connected_to: [1] }
]);
To find connections:
db.Graph.find({ node: 1 })
12. Develop GUI based application software for Hostel Management
import tkinter as tk
from tkinter import messagebox
import mysql.connector
def connect_db():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password", # Replace with your MySQL password
database="management_db"
def add_hostel_student(name, room):
if not name or not room:
messagebox.showerror("Error", "All fields are required")
return
try:
conn = connect_db()
cursor = conn.cursor()
cursor.execute("INSERT INTO hostel_students (name, room_number) VALUES (%s, %s)", (name, room))
conn.commit()
conn.close()
messagebox.showinfo("Success", f"{name} assigned to Room {room}")
except Exception as e:
messagebox.showerror("Database Error", str(e))
def show_hostel_menu():
top = tk.Toplevel()
top.title("Hostel Management")
tk.Label(top, text="Student Name").grid(row=0, column=0, padx=10, pady=5)
name = tk.Entry(top)
name.grid(row=0, column=1, padx=10, pady=5)
tk.Label(top, text="Room Number").grid(row=1, column=0, padx=10, pady=5)
room = tk.Entry(top)
room.grid(row=1, column=1, padx=10, pady=5)
tk.Button(top, text="Register", command=lambda: add_hostel_student(name.get(), room.get())).grid(row=2,
column=0, columnspan=2, pady=10)
root = tk.Tk()
root.title("Hostel Management System")
tk.Button(root, text="Open Hostel Management", command=show_hostel_menu, width=30).pack(pady=20)
root.mainloop()
13. Develop the application for an EMart Grocery Shop.
import tkinter as tk
from tkinter import messagebox
import mysql.connector
def connect_db():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="management_db"
def add_grocery_item(item, qty):
if not item or not qty.isdigit():
messagebox.showerror("Error", "Please enter valid item and quantity")
return
try:
conn = connect_db()
cursor = conn.cursor()
cursor.execute("INSERT INTO grocery_cart (item, quantity) VALUES (%s, %s)", (item, int(qty)))
conn.commit()
conn.close()
messagebox.showinfo("Success", f"{qty} x {item} added to cart")
except Exception as e:
messagebox.showerror("Database Error", str(e))
def show_grocery_menu():
top = tk.Toplevel()
top.title("EMart Grocery Shop")
tk.Label(top, text="Item").grid(row=0, column=0, padx=10, pady=5)
item = tk.Entry(top)
item.grid(row=0, column=1, padx=10, pady=5)
tk.Label(top, text="Quantity").grid(row=1, column=0, padx=10, pady=5)
qty = tk.Entry(top)
qty.grid(row=1, column=1, padx=10, pady=5)
tk.Button(top, text="Add to Cart", command=lambda: add_grocery_item(item.get(), qty.get())).grid(row=2,
column=0, columnspan=2, pady=10)
root = tk.Tk()
root.title("EMart Grocery System")
tk.Button(root, text="Open Grocery Shop", command=show_grocery_menu, width=30).pack(pady=20)
root.mainloop()
14. Create software for property management in eMall.
import tkinter as tk
from tkinter import messagebox
import mysql.connector
def connect_db():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="management_db"
def add_property(name, price):
if not name or not price.isdigit():
messagebox.showerror("Error", "Enter valid property name and numeric price")
return
try:
conn = connect_db()
cursor = conn.cursor()
cursor.execute("INSERT INTO properties (property_name, price) VALUES (%s, %s)", (name, int(price)))
conn.commit()
conn.close()
messagebox.showinfo("Success", f"{name} listed for ₹{price}")
except Exception as e:
messagebox.showerror("Database Error", str(e))
def show_property_menu():
top = tk.Toplevel()
top.title("eMall Property Management")
tk.Label(top, text="Property Name").grid(row=0, column=0, padx=10, pady=5)
name = tk.Entry(top)
name.grid(row=0, column=1, padx=10, pady=5)
tk.Label(top, text="Price").grid(row=1, column=0, padx=10, pady=5)
price = tk.Entry(top)
price.grid(row=1, column=1, padx=10, pady=5)
tk.Button(top, text="List Property", command=lambda: add_property(name.get(), price.get())).grid(row=2,
column=0, columnspan=2, pady=10)
root = tk.Tk()
root.title("eMall Property System")
tk.Button(root, text="Open Property Management", command=show_property_menu, width=30).pack(pady=20)
root.mainloop()
15. Create software for tourism management systems.
import tkinter as tk
from tkinter import messagebox
import mysql.connector
def connect_db():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="management_db"
def book_trip(tourist, destination):
if not tourist or not destination:
messagebox.showerror("Error", "All fields are required")
return
try:
conn = connect_db()
cursor = conn.cursor()
cursor.execute("INSERT INTO bookings (tourist_name, destination) VALUES (%s, %s)", (tourist, destination))
conn.commit()
conn.close()
messagebox.showinfo("Booked", f"Trip booked for {tourist} to {destination}")
except Exception as e:
messagebox.showerror("Database Error", str(e))
def show_tourism_menu():
top = tk.Toplevel()
top.title("Tourism Management")
tk.Label(top, text="Tourist Name").grid(row=0, column=0, padx=10, pady=5)
tourist = tk.Entry(top)
tourist.grid(row=0, column=1, padx=10, pady=5)
tk.Label(top, text="Destination").grid(row=1, column=0, padx=10, pady=5)
destination = tk.Entry(top)
destination.grid(row=1, column=1, padx=10, pady=5)
tk.Button(top, text="Book Trip", command=lambda: book_trip(tourist.get(), destination.get())).grid(row=2,
column=0, columnspan=2, pady=10)
root = tk.Tk()
root.title("Tourism Management System")
tk.Button(root, text="Open Tourism Management", command=show_tourism_menu, width=30).pack(pady=20)
root.mainloop()
