[SOLVED] movie database

$25

File Name: movie_database.zip
File Size: 131.88 KB

5/5 - (1 vote)

Design

Database Design

ER Diagram

Database management system

Data Schema Design

Database management system is developed with the help of MySQL, which interference with the web application to give a more comprehensive and lively online HKIFF system.

The tables of the databases are designed as follows:

Movie
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
Edition Varchar(10) Edition of the film
Format enum (DCP, XXX, XXXX, XXX) Format of the film
Year Varchar (5) Year of the HKIFF
Duration int(3) Duration of the film
Premier varchar(20) Premier of the film
Date_Received Datetime Date of the film received
Film_Type enum (animation, XXX, XXXX, XXX) Type of the film
Remarks varchar(100) Remarks of the film
Synopsis varchar(500) Summary and description of the film
Projects varchar(30)

null

Project of the film belonging to

MovieMakers
FIELDS DATA TYPE DESCRIPTION
MMID (PK) int(3) Unique identifier of film maker
Chinese_Last_Name varchar(2) Last name of the film maker
Chinese_First_Name varchar(4) First name of the film maker
English_Last_Name varchar(50) Last name of the film maker
English_First_Name varchar(50) First name of the film maker
Chinese_Biography varchar(256) Chinese Biography of the film maker
English_Biography varchar(256) English Biography of the film maker

Title
FIELDS DATA TYPE DESCRIPTION
Title_ID (PK) int(3) Unique identifier of film maker
Original_Title varchar(256) Original title of the film
Chinese_Title (FK) varchar(256) Chinese title of the film
Country
FIELDS DATA TYPE DESCRIPTION
Country_ID (PK) int(3) Unique identifier of customers to login the system
English_List enum (China, XXX, XXXX, XXX)

*can be more than 1

The English list of countries
Chinese_List enum (, XXX, XXXX, XXX)

*can be more than 1

The Chinese list of countries

Section
FIELDS DATA TYPE DESCRIPTION
Section_ID (PK) int(3) Unique identifier of section
Chinese_Section enum (, XXX, XXXX, XXX) The Chinese section of the film
English_Section enum ( Global Vision, XXX, XXXX, XXX) The English section of the film

Honors
FIELDS DATA TYPE DESCRIPTION
Honors_ID (PK) int(3) Unique identifier of the honor
Festivals Varchar(50) The title of the festival
Awards Varchar(50) The title of the award

MakerList
FIELDS DATA TYPE DESCRIPTION
MakerList_ID (PK) int(3) Unique identifier of the marker list
MMID (FK) int(3) Unique identifier of film maker
Occupation enum (director, XXX, XXXX, XXX)

*can be more than 1

The occupation of the film maker
Distributor
FIELDS DATA TYPE DESCRIPTION
Distributor_ID (PK) int(3) Unique identifier of distributor
Chinese_Distributor Varchar(20)

null

The Chinese title of the distributor
English_Distributor Varchar(40)

null

The English title of the distributor

Language
FIELDS DATA TYPE DESCRIPTION
Language_ID (PK) int(3) Unique identifier of customers to login the system
Chinese_Language enum (, XXX, XXXX, XXX)

*can be more than 1

Language of the film in Chinese
English_Language enum (Chinese, XXX, XXXX, XXX)

*can be more than 1

Language of the film in English

Subtitle?

Film Company
FIELDS DATA TYPE DESCRIPTION
Film Company_ID (PK) int(3) Unique identifier of customers to login the system
Chinese_FilmCompany Varchar(20)

null

The Chinese title of the film company
English_FilmCompany Varchar(50)

null

The English title of the film company

Screener
Screener _ID (PK) int(3) Unique identifier of screener
Access_Information Varchar(10)
Screening_Location enum (cannes, XXX, XXXX, XXX)

Location of the screener
HyperLink Varchar(256) Hyperlink of the screener

Staff
FIELDS DATA TYPE DESCRIPTION
Staff_ID (PK) int(3) Unique identifier of the staff
User_name Varchar(10) User name of the staff account
Staff_Name Varchar(10) staff name
Password Varchar(10) Password for the account to login
Establish_Date Daytime Creation date of the account
AccessRight enum (administrator, XX, XXXX, XXX) Access right
Last_access Datetime Date and time of the last access to the account
Last_edit Datetime Date and time of the last edit to the account

Review
FIELDS DATA TYPE DESCRIPTION
Review_ID (PK) int(3) Unique identifier of the review
Reviewer Varchar(10) The name of the reviewer
Comment Varchar(256) The comment of the review
Movie Honors
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
Honors_ID (PK) int(3) Unique identifier of the honor
Movie Country
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
Country_ID (PK) int(3) Unique identifier of customers to login the system

Movie Makers
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
MMID (PK) int(3) Unique identifier of film maker
Movie Screener
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
Screener _ID (PK) int(3) Unique identifier of screener
Movie Film Company
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
Film Company_ID (PK) int(3) Unique identifier of customers to login the system
Movie Language
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
Language_ID (PK) int(3) Unique identifier of customers to login the system
Movie Distributor
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
Distributor_ID (PK) int(3) Unique identifier of distributor
MovieMakert List
FIELDS DATA TYPE DESCRIPTION
Movie_ID (PK) int(3) Unique identifier of the film
MakerList_ID (PK) int(3) Unique identifier of the marker list

Relational Schema Design

Normalization

Movie ( Movie_ID , Edition, Format, Year, Duration, Premier, Date_Received, Film_Type, Remarks, Synopsis, Projects, Title_ID, Section_ID)

MovieMakers ( MMID , Chinese_Last_Name, Chinese_First_Name, English_Last_Name, English_First_Name, Chinese_Biography, English_Biography)

Title ( Title_ID , Original_Title, Chinese_Title)

Country (Country_ID , English_List, Chinese_List)

Section ( Section_ID , Chinese_Section, English_Section)

Honors ( Honors_ID , Festivals, Awards)

Distributor ( Distributor_ID , Chinese_Distributor, English_Distributor)

Language ( Language_ID , Chinese_Language, English_Language)

Film Company ( Film Company_ID , Chinese_FilmCompany, English_FilmCompany)

Screener ( Screener _ID , Access_Information, Screening_Location, HyperLink)

Staff ( Staff_ID , User_name, Staff_Name, Password, Establish_Date, AccessRight, Last_access, Last_edit, Review_ID)

Review ( Review_ID , Reviewer, Comment)

Movie Honors ( Movie_ID , Honors_ID )

Movie Country ( Movie_ID , Country_ID )

Movie MovieMakers ( Movie_ID , MMID, Occupation)

Movie Screener ( Movie_ID , Screener ID )

Movie Film Company ( Movie_ID , Film Company_ID )

Movie Language ( Movie_ID , Language_ID )

Movie Distributor ( Movie_ID , Distributor_ID )

Denormalization

Movie ( Movie_ID , Edition, Format, Year, Duration, Premier, Date_Received, Film_Type, Remarks, Synopsis, Projects, Title_ID, Original_Title, Chinese_Title, Section_ID)

MovieMakers ( MMID , Chinese_Last_Name, Chinese_First_Name, English_Last_Name, English_First_Name, Chinese_Biography, English_Biography)

Title ( Title_ID , Original_Title, Chinese_Title)

Country (Country_ID , English_List, Chinese_List)

Section ( Section_ID , Chinese_Section, English_Section)

Honors ( Honors_ID , Festivals, Awards)

Distributor ( Distributor_ID , Chinese_Distributor, English_Distributor)

Language ( Language_ID , Chinese_Language, English_Language)

Film Company ( Film Company_ID , Chinese_FilmCompany, English_FilmCompany)

Screener ( Screener _ID , Access_Information, Screening_Location, HyperLink)

Staff ( Staff_ID , User_name, Staff_Name, Password, Establish_Date, AccessRight, Last_access, Last_edit, Review_ID)

Review ( Review_ID , Reviewer, Comment)

Movie Honors ( Movie_ID , Honors_ID )

Movie Country ( Movie_ID , Country_ID )

Movie MovieMakers ( Movie_ID , MMID, Occupation)

Movie Screener ( Movie_ID , Screener ID )

Movie Film Company ( Movie_ID , Film Company_ID )

Movie Language ( Movie_ID , Language_ID )

Movie Distributor ( Movie_ID , Distributor_ID )

Website Design

Landing Interface

External Movie Information Interface

Store edition, section, title, cast, release date, language, format, country, studio, synopsis and action still for each film

Bibliographic Information Interface

Store information of individuals, including name, filmography, position, biography and photo as they may overlap

Internal Movie Information Interface

External movie information interface plus the following

Store internal media server hyperlink information (text variable), internal review, and review date, reviewers name for internal system for staffs use

Distinguish Submitted film and Participating film

Movie Information Input Page

Input edition, section, title, director, cast, release date, language, format, country, studio, synopsis and action still for each film

Input internal media server hyperlink information (text variable), internal review, and review date for internal system for staffs use

Distinguish Submitted film and Participating film

Internal Bibliographic Information Interface

Input information of individuals, including name, position, biography and photo as they may overlap

Movie Entries Editing Page

Edit information input

Alternative Language Interface

Perform switch between bilingual Interfaces

Search Interface

Search across Traditional Chinese and English entries

Search by name, occupation, film, country, edition and section

Search result should be listed from recent to farthest or in alphabetical order

Individual result can display biography, filmography in chronological order (title and year), position and photo

Search Result Interface

Internal Search Result Interface

Login Interface

Internal interface for staff use and external interface for general audience

Access Control Interface

Flexible internal administrative system, enable administrator to create, remove or edit internal user account and its information

Reviewer Input Page

Only consist of functions

Input internal media server hyperlink information (text variable), internal review, and review date for internal system for staffs use

Reviews

There are no reviews yet.

Only logged in customers who have purchased this product may leave a review.

Shopping Cart
[SOLVED] movie database
$25