Database Design of the movie rental management system based on oracle, SQL, and Mysql

In today's society, people's daily lives are getting richer and richer. Small audiovisual rental stores are gradually growing and their business volume is constantly expanding. Traditional inefficient manual management is out of date. An easy-to-access and easy-to-update audio/video rental database management system can be established to keep abreast of the situation of Video/disc rental, manage customers, and quickly and effectively query customers.

In today's society, people's daily lives are getting richer and richer. Small audiovisual rental stores are gradually growing and their business volume is constantly expanding. Traditional inefficient manual management is out of date. An easy-to-access and easy-to-update audio/video rental database management system can be established to keep abreast of the situation of Video/disc rental, manage customers, and quickly and effectively query customers.

In today's society, people's daily lives are getting richer and richer. Small audiovisual rental stores are gradually growing and their business volume is constantly expanding. Traditional inefficient manual management is out of date. You can establish an easy-to-access and easy-to-update audio/video rental database management system to keep abreast of the situation and manage customers, quickly and effectively query customer rental records and movie rental records.

1. System Requirement Analysis

1.1 business and tasks of the system

The audio/video rental store undertakes the leasing and return of audio and video disks. Its task is to provide quality services to all members, so as to conveniently and quickly complete the leasing business.

1.2 Information requirements

Audio/Video rental stores need to know the information of all their movies, including numbers, names, types, countries, directors, actors, etc. They can be stored in the DVD or VHS format, therefore, each movie has its corresponding DVD or VHS number. Each movie has multiple copies, and each movie has at least one DVD or VHS version. Each DVD or VHS is a copy of a specific movie, that is, a movie does not need multiple DVDs or VHS.

You also need to track the stars in each movie. The customer wants to know the information about each actor, including the artist's art name, real name, and birthday.

Audio/Video rental stores need to have detailed information about each customer who becomes a member, including their names and surnames, phone numbers, and current addresses.

The most important thing is that the audiovisual rental store should track the leasing situation of each member, including the current rental movies, rental dates and return dates of the member. Customers can rent multiple DVDs and tapes at any time. By understanding the leasing history, audiovisual rental stores can also analyze their leasing modes. For example, they can determine how many DVDs/tapes each member has rented and how many times each member has postponed the return, you can also know the number of times a specific DVD or tape is used to determine when it will be decommissioned. You can also analyze the overall movie preferences of audio/video rental store members.

1.3 business rules of the system

The store owner or administrator can provide services for any member. A member is a customer who joins the video club. The owner or administrator can enter, save, and query related information of each album and each member as needed.

Use Oracle SQL developer data modeler design to generate code.
Because images and other content cannot be inserted, only generated code is published first.
The first time the content was published, the Experts laughed.

Oracle SQL Developer Oracle SQLyog MySQL

-- Generated by Oracle SQL Developer Data Modeler 3.0.0.653--   at:        2012-09-06 12:18:46 CST--   site:      Oracle Database 10g--   type:      Oracle Database 10gCREATE TABLE actor     (      actor_id INTEGER  NOT NULL ,      actor_name VARCHAR2 (20 CHAR)  NOT NULL ,      actor_first_name VARCHAR2 (20 CHAR)  NOT NULL ,      actor_last_name VARCHAR2 (20 CHAR)  NOT NULL ,      actor_birthday DATE     ) ;ALTER TABLE actor     ADD CONSTRAINT actor_PK PRIMARY KEY ( actor_id ) ;CREATE TABLE actorlist     (      actorlist_datetime DATE  NOT NULL ,      actor_actor_id INTEGER  NOT NULL ,      cinema_cinema_is INTEGER  NOT NULL     ) ;CREATE TABLE cinema     (      cinema_is INTEGER  NOT NULL ,      cinema_name VARCHAR2 (10 CHAR)  NOT NULL ,      cinema_type VARCHAR2 (10 CHAR)  NOT NULL ,      cinema_country VARCHAR2 (20 CHAR)  NOT NULL ,      type_type_id INTEGER  NOT NULL     ) ;ALTER TABLE cinema     ADD CONSTRAINT cinema_PK PRIMARY KEY ( cinema_is ) ;CREATE TABLE customer     (      customer_id INTEGER  NOT NULL ,      customer_first_name VARCHAR2 (20 CHAR)  NOT NULL ,      customer_last_name VARCHAR2 (20 CHAR)  NOT NULL ,      customer_phone_number VARCHAR2 (20 CHAR)  NOT NULL ,      customer_address VARCHAR2 (40 CHAR)  NOT NULL     ) ;ALTER TABLE customer     ADD CONSTRAINT customer_PK PRIMARY KEY ( customer_id ) ;CREATE TABLE rental     (      rental_datetime DATE  NOT NULL ,      return_datetime DATE ,      customer_customer_id INTEGER  NOT NULL ,      video_video_id INTEGER  NOT NULL     ) ;ALTER TABLE rental     ADD CONSTRAINT rental_PK PRIMARY KEY ( rental_datetime ) ;CREATE TABLE type     (      type_id INTEGER  NOT NULL ,      type_description VARCHAR2 (40 CHAR)     ) ;ALTER TABLE type     ADD CONSTRAINT type_PK PRIMARY KEY ( type_id ) ;CREATE TABLE video     (      video_id INTEGER  NOT NULL ,      video_format VARCHAR2 (10 CHAR)  NOT NULL ,      video_deadline DATE  NOT NULL ,      cinema_cinema_is INTEGER  NOT NULL     ) ;ALTER TABLE video     ADD CONSTRAINT video_PK PRIMARY KEY ( video_id ) ;ALTER TABLE actorlist     ADD CONSTRAINT actorlist_actor_FK FOREIGN KEY     (      actor_actor_id    )     REFERENCES actor     (      actor_id    ) ;ALTER TABLE actorlist     ADD CONSTRAINT actorlist_cinema_FK FOREIGN KEY     (      cinema_cinema_is    )     REFERENCES cinema     (      cinema_is    ) ;ALTER TABLE cinema     ADD CONSTRAINT cinema_type_FK FOREIGN KEY     (      type_type_id    )     REFERENCES type     (      type_id    ) ;ALTER TABLE rental     ADD CONSTRAINT rental_customer_FK FOREIGN KEY     (      customer_customer_id    )     REFERENCES customer     (      customer_id    ) ;ALTER TABLE rental     ADD CONSTRAINT rental_video_FK FOREIGN KEY     (      video_video_id    )     REFERENCES video     (      video_id    ) ;ALTER TABLE video     ADD CONSTRAINT video_cinema_FK FOREIGN KEY     (      cinema_cinema_is    )     REFERENCES cinema     (      cinema_is    ) ;                            -- Oracle SQL Developer Data Modeler Summary Report: -- -- CREATE TABLE                             7-- CREATE INDEX                             0-- ALTER TABLE                             12-- CREATE VIEW                              0-- CREATE PACKAGE                           0-- CREATE PACKAGE BODY                      0-- CREATE PROCEDURE                         0-- CREATE FUNCTION                          0-- CREATE TRIGGER                           0-- CREATE STRUCTURED TYPE                   0-- CREATE COLLECTION TYPE                   0-- CREATE CLUSTER                           0-- CREATE CONTEXT                           0-- CREATE DATABASE                          0-- CREATE DIMENSION                         0-- CREATE DIRECTORY                         0-- CREATE DISK GROUP                        0-- CREATE ROLE                              0-- CREATE ROLLBACK SEGMENT                  0-- CREATE SEQUENCE                          0-- CREATE MATERIALIZED VIEW                 0-- CREATE SYNONYM                           0-- CREATE TABLESPACE                        0-- CREATE USER                              0-- -- DROP TABLESPACE                          0-- DROP DATABASE                            0-- -- ERRORS                                   0-- WARNINGS                                 0