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