PROGRAM 1 Database Development Life cycle: Problem definition and Requirement analysis
Scope and Constraints
PROGRAM
-- Creating Books Table
CREATE TABLE Books (
Book_ID NUMBER PRIMARY KEY,
Title VARCHAR2(255) NOT NULL,
Author VARCHAR2(100) NOT NULL,
Genre VARCHAR2(50),
Availability_Status VARCHAR2(10) CHECK (Availability_Status IN ('Available', 'Borrowed'))
);
-- Creating Members Table
CREATE TABLE Members (
Member_ID NUMBER PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Email VARCHAR2(255) UNIQUE NOT NULL,
Membership_Date DATE );
-- Creating Borrowing Table
CREATE TABLE Borrowing (
Transaction_ID NUMBER PRIMARY KEY,
Member_ID NUMBER,
Book_ID NUMBER,
Borrow_Date DATE DEFAULT SYSDATE,
Return_Date DATE,
CONSTRAINT fk_member FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID),
CONSTRAINT fk_book FOREIGN KEY (Book_ID) REFERENCES Books1(Book_ID),
CONSTRAINT chk_return_date CHECK (Return_Date > Borrow_Date) );
-- Inserting Sample Data
INSERT INTO Books1 (Book_ID, Title, Author, Genre, Availability_Status)
VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 'Available');
INSERT INTO Members (Member_ID, Name, Email)
VALUES (101, 'John Doe', 'john.doe@example.com');
INSERT INTO Borrowing (Transaction_ID, Member_ID, Book_ID, Borrow_Date, Return_Date) VALUES
(1001, 101, 1, SYSDATE, SYSDATE + 7);
-- Querying Data
SELECT * FROM Books1;
SELECT * FROM Members;
SELECT * FROM Borrowing;
OUTPUT
Books Table Output
Book_ID Title Author Genre Availability_Status
1 The Great Gatsby F. Scott Fitzgerald Fiction Available
Members Table Output
Member_ID Name Email Membership_Date
101 John Doe john.doe@example.com (Current Date)
Borrowing Table Output
Transaction_ID Member_ID Book_I Borrow_Date Return_Date
D
1001 101 1 (Current Date) (Current Date + 7 Days)
PROGRAM 2:
CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR2(100) NOT
NULL);
CREATE TABLE Course ( CourseID INT PRIMARY KEY, CourseName VARCHAR2(100) NOT
NULL);
CREATE TABLE Enrollment ( StudentID INT, CourseID INT, PRIMARY KEY (StudentID,
CourseID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY
(CourseID) REFERENCES Course(CourseID));
-- Insert sample data into the Student table
INSERT INTO Student (StudentID, Name) VALUES (1, 'Alice');
INSERT INTO Student (StudentID, Name) VALUES (2, 'Bob');
-- Insert sample data into the Course table
INSERT INTO Course (CourseID, CourseName) VALUES (101, 'Database');
INSERT INTO Course (CourseID, CourseName) VALUES (102, 'Software Engineering');
-- Insert sample data into the Enrollment table (Student enrollments)
INSERT INTO Enrollment (StudentID, CourseID) VALUES (1, 101);
INSERT INTO Enrollment (StudentID, CourseID) VALUES (1, 102);
INSERT INTO Enrollment (StudentID, CourseID) VALUES (2, 101);
-- Query to check the data
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Enrollment;
-- Query to show which students are enrolled in which courses
SELECT s.Name AS StudentName, c.CourseName
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID;
Output of SELECT * FROM Student;
StudentI Name
D
1 Alice
2 Bob
2. Output of SELECT * FROM Course;
CourseID CourseName
101 Database
102 Software Engineering
3. Output of SELECT * FROM Enrollment;
StudentI CourseID
D
1 101
1 102
2 101
PROGRAM 3:
-- Create Author table
CREATE TABLE Author (
AuthorID INT PRIMARY KEY,
AuthorName VARCHAR2(100) NOT NULL
);
-- Create Book table
CREATE TABLE Book ( BookID INT PRIMARY KEY, Title VARCHAR2(200) NOT NULL,
ISBN VARCHAR2(13) UNIQUE);
-- Create Loan table to track borrowed books
CREATE TABLE Loan ( LoanID INT PRIMARY KEY, BookID INT, BorrowerName
VARCHAR2(100), LoanDate DATE DEFAULT SYSDATE, FOREIGN KEY (BookID)
REFERENCES Book(BookID) ON DELETE CASCADE );
-- Insert sample data into Author table
INSERT INTO Author (AuthorID, AuthorName) VALUES (1, 'J.K. Rowling');
INSERT INTO Author (AuthorID, AuthorName) VALUES (2, 'George Orwell');
-- Insert sample data into Book table
INSERT INTO Book (BookID, Title, ISBN)
VALUES (1, 'Harry Potter', '9780747532743');
INSERT INTO Book (BookID, Title, ISBN)
VALUES (2, '1984', '9780451524935');
-- Insert sample data into Loan table
INSERT INTO Loan (LoanID, BookID, BorrowerName, LoanDate)
VALUES (1, 1, 'Alice Johnson', TO_DATE('2025-01-10', 'YYYY-MM-DD'));
INSERT INTO Loan (LoanID, BookID, BorrowerName, LoanDate)
VALUES (2, 2, 'Bob Smith', TO_DATE('2025-01-12', 'YYYY-MM-DD'));
-- Create a simple View to see borrowed books and borrowers
CREATE VIEW BorrowedBooks AS
SELECT b.Title, l.BorrowerName, l.LoanDate
FROM Book b
JOIN Loan l ON b.BookID = l.BookID;
-- Query to view all authors
SELECT * FROM Author;
-- Query to view all books
SELECT * FROM Book;
-- Query to view all loans (borrowed books)
SELECT * FROM Loan;
-- Query to view borrowed books using the View
SELECT * FROM BorrowedBooks;
Output:
1)Authors:
AUTHORID AUTHORNAME
-------- ------------------------
1 J.K. Rowling
2 George Orwell
2)Books:
BOOKID TITLE ISBN
------ ---------------------------- -------------
1 Harry Potter 9780747532743
2 1984 9780451524935
3)Loans:
LOANID BOOKID BORROWERNAME LOANDATE
------ ------ -------------- --------
1 1 Alice Johnson 2025-01-10
2 2 Bob Smith 2025-01-12
4)BorrowedBooks View:
TITLE BORROWERNAME LOANDATE
--------------- ------------- ----------
Harry Potter Alice Johnson 2025-01-10
1984 Bob Smith 2025-01-12
Program 4:
-- 1. Create Patients table
CREATE TABLE Patients ( PatientID INT PRIMARY KEY, PatientName VARCHAR(100), Age INT, Gender
VARCHAR(10));
-- 2. Create Doctors table
CREATE TABLE Doctors ( DoctorID INT PRIMARY KEY, DoctorName VARCHAR(100), Specialty
VARCHAR(50));
-- 3. Create Appointments table
CREATE TABLE Appointments (AppointmentID INT PRIMARY KEY,PatientID INT,DoctorID
INT,AppointmentDate DATE,FOREIGN KEY (PatientID) REFERENCES Patients(PatientID), FOREIGN KEY
(DoctorID) REFERENCES Doctors(DoctorID));
-- Inserting sample data into Patients table
INSERT INTO Patients (PatientID, PatientName, Age, Gender)
VALUES (1, 'Alice', 30, 'Female');
INSERT INTO Patients (PatientID, PatientName, Age, Gender)
VALUES (2, 'Bob', 40, 'Male');
-- Inserting sample data into Doctors table
INSERT INTO Doctors (DoctorID, DoctorName, Specialty)
VALUES (1, 'Dr. John Smith', 'Cardiology');
INSERT INTO Doctors (DoctorID, DoctorName, Specialty)
VALUES (2, 'Dr. Michael Lee', 'Neurology');
-- Inserting sample data into Appointments table
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
INSERT INTO Appointments (AppointmentID, PatientID, DoctorID, AppointmentDate)
VALUES (1, 1, 2, '2025-01-15');
INSERT INTO Appointments (AppointmentID, PatientID, DoctorID, AppointmentDate)
VALUES (2, 2, 1, '2025-01-20');
ALTER TABLE Appointments MODIFY AppointmentDate DATE;
-- 1. SELECT Query: Retrieve all patients
SELECT * FROM Patients;
-- 2. SELECT Query: Retrieve all doctors for a specific specialty
SELECT DoctorName
FROM Doctors
WHERE Specialty = 'Cardiology';
-- 3. INSERT Query: Add a new patient
INSERT INTO Patients (PatientID, PatientName, Age, Gender)
VALUES (3, 'John Doe', 45, 'Male');
-- 4. INSERT Query: Add a new doctor
INSERT INTO Doctors (DoctorID, DoctorName, Specialty)
VALUES (3, 'Dr. Sarah Brown', 'Neurology');
-- 5. UPDATE Query: Update a patient's age
UPDATE Patients
SET Age = 46
WHERE PatientID = 1;
-- 6. DELETE Query: Remove a doctor from the system
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'DOCTORS' AND CONSTRAINT_TYPE =
'R';
SELECT * FROM Appointments WHERE DoctorID = 2;
DELETE FROM Appointments WHERE DoctorID = 2;
DELETE FROM Doctors WHERE DoctorID = 2;
-- 7. SELECT Query: Get the most recent appointment (latest appointment)
SELECT * FROM Appointments
WHERE AppointmentDate = (SELECT MAX(AppointmentDate) FROM Appointments);
-- 8. SELECT Query: Get all appointments for a specific patient (e.g., 'John Doe')
SELECT a.AppointmentDate, d.DoctorName
FROM Appointments a
JOIN Doctors d ON a.DoctorID = d.DoctorID
JOIN Patients p ON a.PatientID = p.PatientID
WHERE p.PatientName = 'John Doe';
Output
1)SELECT Query: Retrieve all patients
PATIENTID PATIENTNAME AGE GENDER
---------- ------------ ---- -------
1 Alice 30 Female
2 Bob 40 Male
2) SELECT Query: Retrieve all doctors for a specific specialty
DOCTORNAME
---------------
Dr. John Smith
Dr. Michael Lee
3) Output: No output unless an error occurs, but 'John Doe' will be added to the Patients table.
4) Output: No output unless an error occurs, but 'Dr. Sarah Brown' will be added to the Doctors
table.
5) Output: No output unless an error occurs, but Alice's age will be updated in the Patients table.
6) Output: No output unless an error occurs, but the doctor with DoctorID = 2 will be deleted
from the Doctors table.
7) SELECT Query: Get the most recent appointment
APPOINTMENTID PATIENTID DOCTORID APPOINTMENTDATE
-------------- ---------- -------- -----------------
5 1 3 2025-01-22
8) SELECT Query: Get all appointments for a specific patient ('John Doe')
APPOINTMENTDATE DOCTORNAME
---------------- -------------
2025-01-10 Dr. Michael Lee
Program 5:
-- Step 1: Create Tables
-- Create Guests table
CREATE TABLE Guests ( GuestID INT PRIMARY KEY,GuestName VARCHAR(100), Email
VARCHAR(100));
-- Create Rooms table
CREATE TABLE Rooms (
RoomID INT PRIMARY KEY,
RoomType VARCHAR(50),
MaxCapacity INT,
PricePerNight NUMBER(10,2)
);
-- Create Reservations table
CREATE TABLE Reservations (
ReservationID INT PRIMARY KEY,
GuestID INT,
RoomID INT,
CheckInDate DATE,
CheckOutDate DATE,
FOREIGN KEY (GuestID) REFERENCES Guests(GuestID),
FOREIGN KEY (RoomID) REFERENCES Rooms(RoomID)
);
-- Step 2: Create Stored Procedure to Make a Reservation
CREATE OR REPLACE PROCEDURE Make_Reservation (
p_guest_id IN INT,
p_room_id IN INT,
p_check_in_date IN DATE,
p_check_out_date IN DATE
) AS
v_room_available INT;
BEGIN
SELECT COUNT(*) INTO v_room_available
FROM Reservations
WHERE RoomID = p_room_id
AND ((p_check_in_date BETWEEN CheckInDate AND CheckOutDate)
OR (p_check_out_date BETWEEN CheckInDate AND CheckOutDate));
IF v_room_available = 0 THEN
INSERT INTO Reservations (ReservationID, GuestID, RoomID, CheckInDate, CheckOutDate)
VALUES (Reservations_SEQ.NEXTVAL, p_guest_id, p_room_id, p_check_in_date,
p_check_out_date);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Reservation successful!');
ELSE
DBMS_OUTPUT.PUT_LINE('Room is not available.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
ROLLBACK;
END Make_Reservation;
/
CREATE SEQUENCE Reservations_SEQ START WITH 1 INCREMENT BY 1;
SET SERVEROUTPUT ON;
BEGIN Make_Reservation(1, 101, TO_DATE('2025-02-10', 'YYYY-MM-DD'), TO_DATE('2025-02-15',
'YYYY-MM-DD')); END;
-- Step 3: Create Stored Function to Check Room Availability
CREATE OR REPLACE FUNCTION Check_Room_Availability ( p_room_id IN INT,p_check_in_date IN
DATE,p_check_out_date IN DATE) RETURN VARCHAR AS v_room_available INT;
BEGIN
-- Check room availability
SELECT COUNT(*) INTO v_room_available
FROM Reservations
WHERE RoomID = p_room_id
AND ((p_check_in_date BETWEEN CheckInDate AND CheckOutDate)
OR (p_check_out_date BETWEEN CheckInDate AND CheckOutDate));
-- Return availability status
IF v_room_available = 0 THEN
RETURN 'Room Available';
ELSE
RETURN 'Room Not Available';
END IF;
END Check_Room_Availability;
-- Step 4: Create Trigger to Prevent Double Booking
CREATE OR REPLACE TRIGGER Prevent_Double_Booking
BEFORE INSERT ON Reservations
FOR EACH ROW
DECLARE
v_room_count INT;
BEGIN
-- Check if the room is already booked for the requested dates
SELECT COUNT(*) INTO v_room_count
FROM Reservations
WHERE RoomID = :NEW.RoomID
AND ((:NEW.CheckInDate BETWEEN CheckInDate AND CheckOutDate)
OR (:NEW.CheckOutDate BETWEEN CheckInDate AND CheckOutDate));
-- Prevent booking if room is already reserved
IF v_room_count > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Room is already booked for these dates.');
END IF;
END Prevent_Double_Booking;
-- Step 5: Test the Program
-- Test: Make a reservation
EXEC Make_Reservation(1, 101, TO_DATE('2025-02-01', 'YYYY-MM-DD'), TO_DATE('2025-02-05',
'YYYY-MM-DD'));
-- Test: Check room availability
SELECT Check_Room_Availability(101, TO_DATE('2025-02-01', 'YYYY-MM-DD'), TO_DATE('2025-02-
05', 'YYYY-MM-DD')) FROM dual;
PROGRM 6
-- Creating Students Table (1NF)
CREATE TABLE Students2 (
StudentID INT PRIMARY KEY,
StudentName VARCHAR2(100) NOT NULL,
Phone VARCHAR2(20) UNIQUE NOT NULL
);
-- Creating Courses Table (1NF)
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR2(100) NOT NULL,
Instructor VARCHAR2(100) NOT NULL
);
-- Creating Enrollment Table (1NF)
CREATE TABLE Enrollment1 (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students2(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
-- Creating Instructors Table (2NF)
CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR2(100) NOT NULL
);
-- Modify Courses Table to reference Instructors
ALTER TABLE Courses ADD InstructorID INT;
ALTER TABLE Courses ADD CONSTRAINT fk_instructor FOREIGN KEY (InstructorID) REFERENCES
Instructors(InstructorID);
-- Creating StudentContact Table (3NF)
CREATE TABLE StudentContact (
ContactID INT PRIMARY KEY,
StudentID INT,
Phone VARCHAR2(20) NOT NULL,
FOREIGN KEY (StudentID) REFERENCES Students2(StudentID)
);
ALTER TABLE Students2 MODIFY Phone NULL;
-- Insert Students
INSERT INTO Students2 (StudentID, StudentName) VALUES (1, 'Alice');
INSERT INTO Students2 (StudentID, StudentName) VALUES (2, 'Bob');
-- Insert Instructors
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES (102, 'Networks', 'Dr. Brown');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES (103, 'Networks', 'Dr. Brown');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES (112, 'graphics', 'Dr. henz’);
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES (101, 'graphics', 'Dr. henz’);
-- Insert Courses
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES (104, 'Networks', 'Dr. Brown');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES (105, 'Networks', 'Dr. Brown');
-- Insert Enrollment Data
INSERT INTO Enrollment1 (EnrollmentID, StudentID, CourseID) VALUES (2, 1, 102);
INSERT INTO Enrollment1 (EnrollmentID, StudentID, CourseID) VALUES (3, 1, 103);
INSERT INTO Enrollment1 (EnrollmentID, StudentID, CourseID) VALUES (3, 2, 101);
-- Retrieve Student Enrollment Details
SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollment e
JOIN Students2 s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;
OUTPUT
+-----------+------------+
| StudentID | Name |
+-----------+------------+
|1 | Alice |
|2 | Bob |
+-----------+------------+
+----------+-------------+
| CourseID | CourseName |
+----------+-------------+
| 101 | Database |
+----------+-------------+
+-------------+-----------+----------+
| EnrollmentID | StudentID | CourseID |
+-------------+-----------+----------+
|1 |1 | 101 |
|2 |1 | 102 |
|3 |2 | 101 |
+-------------+-----------+----------+
