Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Deliverable 1 : Database system using Oracle DBMS Study the scenario and complete the questions that follow: Video Reservation Database System A local video rental

Deliverable 1: Database system using Oracle DBMS
Study the scenario and complete the questions that follow:
Video Reservation Database System
A local video rental service has contacted you to develop a database to electronically facilitate a streamlined reservation and rental process. The database assist in managing member, title, copy of title, and rental information to provide users with an efficient and enjoyable video rental and reservation experience. The database will store detailed member information, including attributes such as identity (a unique identifier for each member), last name, first name, address, city, phone number, and join date, which records when a member became part of the platform. Each member can reserve multiple titles associated with their identity. At the same time, each title can be reserved by many members, allowing for multiple users to access the same content. The title keeps the catalog of video titles available in the reservation system including identity for each title, title name, description, rating, category, and release date. These attributes help users browse and select right titles for reservation.
A copy of title representing instances of a title and comprising of a unique identifier for each copy and status (indicating whether the copy is available for reservation or not) is being kept. Each title can have many copies associated with it and thus, ensuring that multiple physical copies of the same title are available for members to reserve.
A rental records transaction when members rent titles. Each rental transaction is associated with a specific copy of a title, ensuring that only one member can rent a particular copy at a time. Specific information such as book date (when the rental was initiated), account rental date (when the rental was added to the member's account), and expiry rental date (when the rental period expires) are kept for the rental
transaction. Each rental transaction is associated with a specific member, indicating the user who rented the copy. The complete Entity Relationship Diagram (ERD) for the proposed database model comprising of all entities involved and their respective attributes are presented in the Figure 1 as shown below.
RESERVATION
#* resDate
TITLE
#* title_Id * title * description o rating o category o releaseDate o price
Figure 1: Entity Relationship Diagram (ERD)
Implementation of the above database model will facilitate effective reservation and rental management processes.
Source: Obaro Adewale (2024)
Questions for Consideration Under Deliverable 1
Use the above scenario to answer the questions that follows:
1.1 Data Definition Language operations
a. Using SQL queries, create the five (5) identified tables from the scenario including ALL related Primary and Foreign constraints where appropriate.
(15 Marks)
b. Store at least five (5) records into each table created in 1.1. a using INSERT statement.
(10 Marks)
1.2. Data Selection and Manipulation Operations
a. Write a query to change the price of the one of the movies in the TITLE table to 200 using an UPDATE statement.
(4 Marks)
b. Display the result of the query after performing the update task in 1.2. a.
(1 Marks)
c. Create a report that contains each customer's history of renting videos using the SELECT statement. Be sure to include the customer's name, title of unavailable movie (Rented), book_date of the rental, and duration of rentals (calculated as account_rental_date minus book_date) for the reporting period.
(4 Marks)
1.3. Views, Index and Function (Stored Procedure)
a. Create a view named "MemberInfo" that contains all member's information.
(2 Marks)
b. Obtain and display the result of the created view descending order using member identity.
(2 Marks)
c. Create a view named MOVIE_VIEW to show the list of movie titles, the availability of each copy, and its expected return date if unavailable (rented out).
(3 Marks)
d. Display all the result of the created view and order the output by the title.
(3 Marks)
e. Create an index named "title_description" on the description column in the title table.
(1 Mark)
f. As a way of giving back to its customers (members), the management of the rental service has decided to give 40% discount on all movie's title selected (UNAVAILABLE due to being rented out) by its customers. Create a function named "MovieTitleDiscount" that calculates 40% discount for each price in the TITLE table.
(3 Marks)
g. Use the created function to calculate the new price. Display the title_id, title, current price, and discounted price of the movies.
(2 Marks)
image text in transcribed

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Time Series Databases New Ways To Store And Access Data

Authors: Ted Dunning, Ellen Friedman

1st Edition

1491914726, 978-1491914724

Students also viewed these Databases questions

Question

explain the need for human resource strategies in organisations

Answered: 1 week ago

Question

describe the stages involved in human resource planning

Answered: 1 week ago