[go: up one dir, main page]
Scribd
Upload
21 views4 pages

Experiment-11-Video Library Management System

**Video Library Management System** A Video Library Management System is software designed to organize and manage video collections efficiently. It allows users to store, categorize, and retrieve videos based on titles, genres, or keywords. The system supports borrowing, returning, and tracking video records, reducing manual errors. It provides features like user registration, inventory management, and late-return penalties. Advanced systems include search functionality, recommendations, and

Uploaded by

instafill148
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
21 views4 pages

Experiment-11-Video Library Management System

**Video Library Management System** A Video Library Management System is software designed to organize and manage video collections efficiently. It allows users to store, categorize, and retrieve videos based on titles, genres, or keywords. The system supports borrowing, returning, and tracking video records, reducing manual errors. It provides features like user registration, inventory management, and late-return penalties. Advanced systems include search functionality, recommendations, and

Uploaded by

instafill148
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Experiment - 11

Video Library Management System


Step 1: Database Design

1. `customers`: Stores customer details who rent videos.


2. `videos`: Stores details about the videos in the library.
3. `rentals`: Links customers to the videos they rent.
4. `genres`: Stores the genre of the videos.
5. `staff`: Stores the information of library staff members.

Customers Table

CREATE TABLE customers (


customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY
PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
phone VARCHAR2(15),
address VARCHAR2(255)
);

Videos Table

CREATE TABLE videos (


video_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY
KEY,
title VARCHAR2(100),
genre_id NUMBER,
release_date DATE,
available BOOLEAN DEFAULT TRUE,
FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

Genres Table

CREATE TABLE genres (


genre_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY
KEY,
genre_name VARCHAR2(100)
);

Rentals Table

CREATE TABLE rentals (


rental_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY
KEY,
customer_id NUMBER,
video_id NUMBER,
rental_date DATE DEFAULT SYSDATE,
return_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (video_id) REFERENCES videos(video_id)
);

Staff Table

CREATE TABLE staff (


staff_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY
KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
position VARCHAR2(100),
phone VARCHAR2(15)
);

Step 2: Sample Data

-- Insert Genres
INSERT INTO genres (genre_name) VALUES ('Action');
INSERT INTO genres (genre_name) VALUES ('Comedy');
INSERT INTO genres (genre_name) VALUES ('Drama');

-- Insert Customers
INSERT INTO customers (first_name, last_name, email, phone, address)
VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '5551234567', '123
Main St');
INSERT INTO customers (first_name, last_name, email, phone, address)
VALUES ('Bob', 'Smith', 'bob.smith@example.com', '5557654321', '456 Oak
St');

-- Insert Videos
INSERT INTO videos (title, genre_id, release_date, available)
VALUES ('The Avengers', 1, DATE '2012-05-04', TRUE);
INSERT INTO videos (title, genre_id, release_date, available)
VALUES ('The Hangover', 2, DATE '2009-06-05', TRUE);

-- Insert Rentals
INSERT INTO rentals (customer_id, video_id, rental_date)
VALUES (1, 1, SYSDATE);
INSERT INTO rentals (customer_id, video_id, rental_date)
VALUES (2, 2, SYSDATE);

-- Insert Staff
INSERT INTO staff (first_name, last_name, position, phone)
VALUES ('Sarah', 'Lee', 'Manager', '5559876543');
INSERT INTO staff (first_name, last_name, position, phone)
VALUES ('James', 'Wright', 'Clerk', '5556543210');

Step 3: Queries
Retrieve Customer Details

SELECT customer_id, first_name, last_name, email, phone, address


FROM customers;

Get Video Details

SELECT v.video_id, v.title, g.genre_name, v.release_date, v.available


FROM videos v
JOIN genres g ON v.genre_id = g.genre_id;

Get Rentals Information

SELECT r.rental_id, c.first_name || ' ' || c.last_name AS customer_name,


v.title AS video_title,
r.rental_date, r.return_date
FROM rentals r
JOIN customers c ON r.customer_id = c.customer_id
JOIN videos v ON r.video_id = v.video_id;

Get Staff Information

SELECT staff_id, first_name, last_name, position, phone


FROM staff;

You might also like