Deakin University SIT103 Assignment 2: Movie Lover Database Design

This document presents a comprehensive solution for SIT103 Assignment 2, focusing on database design for a movie lover company. The solution begins by identifying key business rules and then proceeds to create an Entity-Relationship (ER) diagram to visually represent the database structure. Following the ER diagram, the document provides relational schemas for the database tables, detailing attributes and data types. The solution then provides SQL commands to create the tables, populate them with sample data, and includes commands to alter the table, update and query the database. Specific SQL queries are developed to retrieve data based on various criteria, such as actor names, and rental histories. The assignment addresses the unit learning outcomes of evaluating data models and applying data modeling techniques to capture real-world data aspects, and designing and developing relational databases.

Am aware of penalties that are applied for plagiarism and I declare the work am submitting is my own
work.
SIGNED_________________________________ DATE__________________________________
STUDENT NUMBER: Enter your number
STUDENT NAME: fill in your name

Solution one
Identifying business rules
The business rules are important part of any business and below are the identified business rules.
1. The store should have one or more employees
2. The store must have movies to operate
3. The store can operate without a customer
4. Customer can only rent a one movie per order.
5. Store can have more movies.
6. Store must operate with a manger
7. Customer can only belong to one branch
8. Customer can rent movie in any store.
9. One movie title can appear to all branches.
Solution two
Drawing ER Diagram
Identifying Entities
 Staff entity
 Customer entity
 Branch entity
 Movie entity
 Movie title entity
 Actor entity
 Director entity
 Rental entity
The ER diagram

SOLUTION THREE
Relational schemas
Staff (staff_no:integer, branch_no:integer, name:string(50), email:varchar(50), position:varchar(30), salary:number)
Branch(branch_no:integer, branch_location:string(50))
Customer(id_number:number, branch_no:integer, name:varchar2(50), email:varchar2(50),
address:string(100), reg_date:varchar2(30))
Movie title(copy_no:integer, title:string(100), catalog_no:integer)
Movie (catalog_no:integer, genre_id:integer, actor_id:integer, release_date:varchar2(30), director_id:integer,
rental_price:integer)
Actor(actor_id:integer, firstname:varchar2(30), lastname:varchar2(30))
Genre(genre_id:integer, genre_name:varchar(30)
Rental(rental_id:integer, id_number:integer, copy_no:integer, rental_date:varchar2(30),
return_date(30), overdue:integer)
The primary key constraint are marked with an orange color while the foreign key constraint are marked
with blue color
Solution 4
Sql command
Part one
To create the tables it is recommended to begin with the table that does not have the foreign key
In this solution we begin by creating the branch table.
Branch table
CREATE TABLE branch(
Director(director_id:integer, firstname:varchar2(30) ,
lastname(30))

BrancH_no integer NOT NULL,
Branch_location varchar2(50) NOT NULL,
CONSTRAINT branch_pk FOREIGN_KEY(branch_no)
);
Staff table
The screen shot for creating the staff table.

The below figure show populated staff data
Actor table
Creating actor table which has actor_id as a primary key and two attributes firstname and lastname both
of which are of varchar holding a maximum of 30 characters each.
Create table actor(
Actor_id integer NOT NULL,
Firstname varchar2(30) NOT NULL,
Lastname varchar2(30) NOT NULL,
CONSTRAINT actor_pk PRIMARY KEY(actor_id)
);

Populated actor data
Director table
Director table has three columns only. This table is an example of composite entity.
The table has a primary key director_id.
Below figure show how the tabke was created

Genre table
This is another example of composite entity.
Creating genre table.
Genre table had only two columns genre id and genre name.
Create table genre(
Genre_id integer NOT NULL,
Genre_name varchar2(50) NOT NULL,
CONSTRAINT genre_pk PRIMARY KEY(genre_id)
);
We the populated the data using the select statement
SELECT * FROM genre;

Populated data for both director and genre table

Movie table
Movie table
Create table movie(
Catalog_no integer NOT NULL,
Actor_id integer NOT NULL,
Director_id integer NOT NULL,
Genre_id integer NOT NULL,
Release_date date NOT NULL,
Rental_price number NOT NULL,
CONSTRAINT movie_pk PRIMARY KEY(catalog_no),
CONSTRAINT FOREIGN KEY(actor_id) REFERENCES actor(actor_id),
CONSTRAINT FOREIGN KEY(director_id) REFERENCES director(director_id),
CONSTRAINT FOREIGN KEY(genre_id) REFERENCES genre(genre_id)
);

Movie title table
Movie title table has three columns; copy no which is a foreign key, catalog _no a foreign key and title.
Create table movie_title(
copy_no INTEGER NOT NULL,
title VARCHAR2(50) NOT NULL,
catalog_no INTEGER NOT NULL
CONSTRAINT title_pk PRIMARY KEY(copy_no),
CONSTRAINT FOREIGN KEY(catalog_no) REFERENCES MOVIE(catalog_no)
);
CUSTOMER TABLE
THE CUSTOMER TABLE display detail of customer and the branch they have registered at.

Rental table