2nd

Dbms

Here is the solution for the Sailors database schema:

a) Create the tables by specifying primary keys and foreign keys:

CREATE TABLE Sailors (
    sid INTEGER PRIMARY KEY,
    sname VARCHAR(50),
    rating INTEGER,
    age REAL
);

CREATE TABLE Boats (
    bid INTEGER PRIMARY KEY,
    bname VARCHAR(50),
    color VARCHAR(20)
);

CREATE TABLE Reserves (
    sid INTEGER,
    bid INTEGER,
    day DATE,
    PRIMARY KEY (sid, bid, day),
    FOREIGN KEY (sid) REFERENCES Sailors(sid),
    FOREIGN KEY (bid) REFERENCES Boats(bid)
);

b) Insert around 10 records into each table:

-- Insert records into Sailors
INSERT INTO Sailors VALUES 
(1, 'John', 5, 25.5),
(2, 'Alice', 3, 22.0),
(3, 'Bob', 7, 30.0),
(4, 'Charlie', 5, 28.0),
(5, 'Diana', 4, 19.5),
(6, 'Eve', 3, 24.0),
(7, 'Frank', 6, 26.5),
(8, 'Grace', 4, 27.0),
(9, 'Hank', 5, 21.5),
(10, 'Ivy', 2, 23.0);

-- Insert records into Boats
INSERT INTO Boats VALUES 
(101, 'Sailboat 1', 'red'),
(102, 'Speedboat 2', 'blue'),
(103, 'Yacht 3', 'green'),
(104, 'Canoe 4', 'red'),
(105, 'Rowboat 5', 'yellow'),
(106, 'Kayak 6', 'red'),
(107, 'Motorboat 7', 'blue'),
(108, 'Fishing Boat 8', 'green'),
(109, 'Luxury Boat 9', 'white'),
(110, 'Cruiser 10', 'yellow');

-- Insert records into Reserves
INSERT INTO Reserves VALUES 
(1, 101, '2024-06-01'),
(2, 103, '2024-06-02'),
(3, 104, '2024-06-03'),
(4, 105, '2024-06-04'),
(5, 101, '2024-06-05'),
(6, 106, '2024-06-06'),
(7, 102, '2024-06-07'),
(8, 103, '2024-06-08'),
(9, 106, '2024-06-09'),
(10, 104, '2024-06-10');

c) Find the names of sailors who have reserved a red boat and list them in order of age:

SELECT DISTINCT s.sname
FROM Sailors s
JOIN Reserves r ON s.sid = r.sid
JOIN Boats b ON r.bid = b.bid
WHERE b.color = 'red'
ORDER BY s.age;

d) Find the names of sailors who have reserved boat 103:

SELECT s.sname
FROM Sailors s
JOIN Reserves r ON s.sid = r.sid
WHERE r.bid = 103;

e) Find the name and age of the youngest sailor:

SELECT sname, age
FROM Sailors
WHERE age = (SELECT MIN(age) FROM Sailors);

f) Find the average age of sailors for each rating level that has at least two sailors:

SELECT rating, AVG(age) AS average_age
FROM Sailors
GROUP BY rating
HAVING COUNT(sid) >= 2;

g) Create a stored procedure to retrieve details of sailors for a specified color of a boat:

DELIMITER //

CREATE PROCEDURE GetSailorsByBoatColor(IN boat_color VARCHAR(20))
BEGIN
    SELECT DISTINCT s.sid, s.sname, s.rating, s.age
    FROM Sailors s
    JOIN Reserves r ON s.sid = r.sid
    JOIN Boats b ON r.bid = b.bid
    WHERE b.color = boat_color;
END //

DELIMITER ;

To call the stored procedure for a specific boat color, use:

CALL GetSailorsByBoatColor('red');

Explanation:
	1.	Tables: Created Sailors, Boats, and Reserves with appropriate primary and foreign keys.
	2.	Inserts: Added 10 records each into Sailors, Boats, and Reserves.
	3.	Queries:
	•	(c) Retrieves sailor names for red boats, ordered by age.
	•	(d) Fetches sailor names who reserved boat 103.
	•	(e) Finds the youngest sailor by age.
	•	(f) Groups sailors by rating and calculates average age, filtering ratings with at least two sailors.
	•	(g) Creates a stored procedure to fetch sailor details based on boat color.

Let me know if further explanations or refinements are needed!