Description
Project Purpose
In this project you will analyze, specify, design, implement, document and demonstrate an
online system. You are required to use the classical methodology for database development.
The system will be implemented using a relational DBMS that supports standard SQL queries.
You will use your localhost MySQL Server (Version 5.1 or above) to implement your database
and the application. You also cannot use any other software like Access or SQLite. Ask the
professors or TAs if you have questions.
Project Phases
The 4 phases (phase 4 is optional, see syllabus) of the project cover the following workprocesses from the Classical Methodology for Database Development (see notes on Canvas).
Slides on database design methodology will be useful for phases 1 and 2: All slides have been
posted on Canvas.
Re-grade Policy
Once graded phases and/or quizzes are returned, there is a one-week deadline during which
you can contest your grade with the TA who graded your assignment. You must first go to the
TA who graded your assignment before going to the Head TA, if the TA who graded your
assignment was unable to resolve the issue. This clock starts when the papers are returned to
the class, not when you personally get your returned paper.
Teams
(See announcement on Canvas)
Deliverables
Phase 1 – Submit to Canvas (one person submits for the group)
The deliverables include (in a single PDF file):
1. A cover page – you MUST include all information listed on the template (See Canvas
Project Folder).
2. Enhanced Entity Relationship (EER) Diagram
3. Information Flow Diagram (IFD)
a. You MUST include the screen numbers (see figure caption and figure header)
when making IFD. Failure to do so will result in point deduction (25% per screen)
b. A partial IFD will be provided to start you off. Please INCLUDE these start-up
screens in your final submission
4. A list of logical constraints. You are required to include at least 3 logical constraints,
although a fully specified system will probably have more than that.
5. Any assumptions made, with justification and explanation.
Additional Phase 1 Information:
1. The EER must capture the functionalities of the application system whenever applicable.
(e.g., total participation, superclasses/subclasses, weak entities)
2. The design of your system must include all functionalities as indicated by the application
description in this document. You are allowed to make up additional assumptions as
long as they do not conflict with the specified constraints and requirements. You must
list all your assumptions; otherwise, your EER diagram will lose points since the TA will
not understand certain parts of your design based on your assumptions. Also,
information extraneous to the required functionality may count against you
3. The logical constraints that you must list cannot be ones that can be specified using ER
notation, nor can they be related directly to data types or values
a. (i.e. “password must have at least 8 characters” is not a logical constraint)
Phase 2 – Submit to Canvas (one person submits for the group)
The deliverables include (in a single PDF file):
1. A cover page, same as Phase 1.
2. Copy of the EER diagram (either your phase 1 diagram, with any modifications, or the
provided solution)
a. Highly encourage to use the provided solution
3. Relational Schema Diagram – identify primary and foreign keys and show referential
integrity using arrows.
4. MySQL CREATE TABLE statements, including domain constraints, integrity constraints,
primary keys, foreign keys, & appropriate referential triggered action clause.
Phase 3 –Submit to Canvas (one person submits for the group)
More details about phase 3 will be given later during the semester.
Phase 4 – Submit to Canvas (one person submits for the group)
More details about phase 4 will be given later during the semester.
On Demo Day
More details about demos will be discussed later during the semester. All team members must
be present and on time. Missing/late team members will receive a -10 points penalty.
Grading
(See Canvas Syllabus)
Project
In this project, you and your teammates will design a movie system with the following
requirements using relational database concepts.
General Notes
1. User can be a
• Customer
• Employee
• Both
• Neither
2. Employee must be either a(n):
• Admin
• Manager
3. Admins are special employees and their accounts are pre-populated in the database (no
need to handle admin registration). They work for the entire system rather than any
specific entity in the system
4. Table columns are NOT sortable unless specified otherwise
5. Dropdown lists must be populated using existing data in the database unless specified
otherwise. Additionally, add the “ALL option” to the all dropdown list.
6. Regarding filtering fields, they are not all required unless specified otherwise
General Screens
Screen 1: Login
Screen 1 Login
Notes:
1. This is a login page that all users use to log into the app.
2. Upon successful login, the user should be taken to the appropriate functionality screen
(7-12).
3. Upon invalid login, the app should notify the user, and the user should be allowed to
retry.
Screen 2: Register Navigation
Screen 2 Register Navigation
Notes:
1. This allows the user to navigate to the registration screen (3-6) of their choosing.
Screens (3-6): Registration Screens
The following notes are common to all registration screens:
1. “Usernames” are unique among all users.
2. “Password” must have at least 8 characters.
3. “Password” and “confirm password” should match
4. Do not store the password as plain text in the database. Store the hashed password.
Screen 3: User Registration
Screen 3 User Registration
Notes:
1. All fields required.
Screen 4: Customer-Only Registration
Screen 4 Customer-Only Registration
Notes:
1. All fields are required
2. “Credit card #” are unique for all users
3. A customer must enter at least 1 credit card and at most 5 credit cards
4. “Credit card #” has 16 digits
Screen 5: Manager-Only Registration
Screen 5 Manager-Only Registration
Notes:
1. All fields are required
2. “Address” consists of street address, city, state, and zip code.
3. “Address” is unique among all managers
4. “State” is a dropdown list that can be hardcoded (2-letter abbreviation for each states)
5. “Company” is a dropdown list
6. Manager must work for a specific company
7. “Zipcode” has 5 digits
Screen 6: Manager-Customer Registration
Screen 6 Manager-Customer Registration
Notes:
1. See screen 5 & 6
Screen 7-12: Functionality Screens
Screen 7 Admin-Only Functionality
Screen 8 Admin-Customer Functionality
Screen 9 Manager-Only Functionality
Screen 10 Manager-Customer Registration
Screen 11 Customer Functionality Screen 12 User Functionality
Notes:
1. Only an Admin can manage user, manage company, and create movie; other users
cannot
2. As a customer, an Admin (in addition to exclusive activities) can also explore movie and
explore theater.
3. Manager can overview the theater they manage as well as add movie to the theater
they manage.
4. As a customer, a Manager (in addition to exclusive activities) can also explore movie and
explore theater
5. A non-Manager and non-Admin Customer can only explore movies and theater
6. Any user (Admin, Manager, Customer, etc. ) can explore theaters.
Admin
Screen 13: Admin Manage User
Screen 13 Admin Manage User
Notes:
1. All columns in this table are sortable columns
2. Admin can approve “pending” or “declined” the selected users
3. Admin can decline “pending” the selected users
4. Admin cannot decline “approved” the selected users
5. “Status” is a dropdown list with contents (“All”, “Pending”, “Declined”, “Approved”).
You may hardcode this field.
Screen 14: Admin Manage Company
Screen 14 Admin Manage Company
Notes:
1. All columns in this table are sortable columns
2. “#CityCovered” is the total number of UNIQUE cities in which the company has theaters.
(Unique city: unique city-state combination)
3. “#Theater” is the number of different theaters owned by the company
4. “#Employee” is the number of managers working for the company
5. “Name” is a dropdown list
6. All filtered values are inclusive
7. Admin can create theater or view company details of the selected company
Screen 15: Admin Create Theater
Screen 15 Admin Create Theater
Notes:
1. All fields are required
2. Theater must belong to a company
3. “Address” consists of street address, city, state, and zip code
4. “Name” is unique for theaters within a company
5. “Company” is a dropdown list
6. “State” is a dropdown list that can be hardcoded (2-letter abbreviation for each states)
7. “Capacity” of a theater is the maximum number of movies it can play for the same date
8. Theater must be managed by an existing and non-assigned manager (i.e. manager who
does not manage other theaters). Dropdown list should only show eligible managers.
9. Manager can at most manage one theater within their company
Screen 16: Admin Company Detail
xxx
Screen 16 Admin Company Detail
Notes:
1. “Employees” lists all the managers who work for the company
2. “Theaters” lists the theater details for all theaters owned by the company
Screen 17: Admin Create Movie
Screen 17 Admin Create Movie
Notes:
1. All fields are required
2. “Duration” entered is in minutes
3. The combination of “Name” and “Release Date” is unique for all movies
Manager
Screen 18: Manager Theater Overview
Screen 18 Manager Theater Overview
Notes:
1. Manager can see and search the overview of the theater they manage
2. “Date” is inclusive
3. “Duration” is inclusive
4. “Only Include Not Played Movies”
a. If selects, display all of the movies that have not yet been played (i.e. do not
have a “Play Date”) at that theater scheduled yet
b. If not selected, display all movies (played nor not played yet)
Screen 19: Manager Schedule Movie
Screen 19 Manager Schedule Movie
Notes:
1. All fields are required
2. The combination of “Name” and “Release Date” is unique for all movies and it must
have been created first
3. “Name” is a dropdown list
4. Cannot schedule a movie before its release date (i.e. “release date” must be before
“play date”)
Customer
Screen 20: Customer Explore Movie
Screen 20 Customer Explore Movie
Notes:
1. “Card Number” is a dropdown list
2. “Company Name” is a dropdown list
3. “Movie Name” is a dropdown list
4. Customer must select one of their card numbers in order to view a movie
5. Customer can at most watch 3 movies per day
6. “Movie Play Date” is inclusive
Screen 21: Customer View History
Screen 21 Customer View History
Notes:
1. Customer are able to see all the movies they have viewed
User
Screen 22: User Explore Theater
Screen 22 User Explore Theater
Notes:
1. User must enter a “visit date” before logging visit to the selected theater.
2. “Theater Name” is a dropdown list
3. “Company Name” is a dropdown list
4. “State” is a dropdown list that can be hardcoded (2-letter abbreviation)
5. Users can visit any theater as many times as they want every day
Screen 23: User Visit History
Screen 23 User Visit History
Notes:
1. Users can see their visit histories based on the filtered information
2. “Company Name” is a dropdown list
3. “Visit Date” should be inclusive
