Case Study48 Information System for a Movie Theater

Information System for a Movie Theater

Problem Description

A local movie theater is interested in building a database information system. The database will keep the information about the customers, sales, etc. The system will enable the management to analyze the progress of the business, create financial reports, etc.

An important aspect of this project is to develop a web application that will allow the customers to check the schedule of movies and purchase tickets on-line.

Database Design

We present the main entity types of this database. For each entity type, we provide some of the corresponding attributes. Use this information in order to: (a) Build an Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database. Identify the primary key(s) and the foreign key(s) for each relation. Draw the relational integrality constraints; (c) For each of the relations created, indicate its normal form. If the relation is not in the 3NF, decompose it into 3NF relations.

  1. Customer: The main attributes are identification number, name, address, telephone number, e-mail, etc.
  2. Employee: The main attributes are identification number, name, address, date of hire, employment history, salary, etc.
  3. Movie: The main attributes are identification number, title, year of production, awards won, description, actors, location, etc.
  4. Showroom: The main attributes are location, name, capacity, etc.
  5. Producer: The main attributes are identification number, name, address, contact information, current balance, etc.

Note the following: (a) Awards won and Actors are multi-value attributes of the entity type Movie. (b) Employment history is a multi-value attribute of the entity type Employee. (c) A movie is played in at least one showroom and it is played at least once a day. When a movie is assigned to a showroom, the following information is recorded: the timetable and the total number of tickets available per show. (d) When a customer purchases tickets for a movie, the following is recorded: ticket number, date, unit price, amount paid, and number of tickets purchased. The number of tickets purchased reduces the total number of tickets available. (e) Some of the customers choose to become members in order to buy tickets on-line. For members, the following additional information is required: login name and password. (f) When the movie theater purchases movies from a producer, the following information is recorded: transaction number, purchase price, purchase date, payment due date, and amount due.

Access Application Development

The following are some of the queries, forms, and reports one can create in order to increase the functionality of the database:

Queries:

  1. The following set of queries helps with financial analysis:
  2. Create a query that presents the monthly revenues from ticket sales, the monthly expenses from salaries, the monthly expenses from purchasing new movies, and the monthly earnings.
  3. Create a query that presents the total revenues, total expenses, and earnings during the current year.
  1. Create a query that lists the five best movies of the current year. This classification is based on the number of awards won.
  2. List the five most expensive movies of the current year.
  3. Create a query that presents the total number of tickets sold per movie. Sort the information in descending order of the total number of tickets.
  4. Create a query that presents the average capacity usage of a showroom during the current year.
  5. Create a query that lists the one hundred most preferable customers.
  6. Create a query that presents detailed information about the producer with whom the theater did the most business during the current year.
  7. Create a query that prompts for a date and returns the movie schedule for the selected date and the total number of available tickets per show.
  8. Create a query that prompts for the name of a movie and returns the weekly schedule of the selected movie and the total number of available tickets per show.
  9. Create a query that prompts for the name of a customer and returns details about the ticket purchases made by the selected customer during the current month.

Forms:

  1. Create a user sign-in form together with a registration form for new users.
  2. Create the following data entry forms that are used for database administrative functions: employees, movies, customers, movie timetables, etc. These forms allow the user to add, update, and delete information about employees, movies, customers, movie timetables, etc.
  3. Create a form that enables the user to browse through the financial reports created.
  4. Create a form that would enable the user to select a movie title from a combo box. Insert a subform that presents the weekly timetable of the selected movie and the total number of available tickets per show. Insert textboxes to present the following information about the selected movie: production year, name of the producer, and a description of the movie. Insert a command button that, when clicked-on, returns a list with details about the awards won by the selected movie. Insert a command button that, when clicked-on, returns details about the actors who perform in the movie. Insert a command button that allows the user to purchase a ticket. When the user clicks-on this button (a) the user sign-in form opens. If the user is not a member, the user registration form opens. (b) Next, the data entry form opens to enable the user to purchase a ticket.
  5. Create a form that allows the user to browse through the information saved in this database about actors. Insert a subform that presents details about the movies performed in by the selected actor. Insert a subform that presents details about the awards won by the selected actor.
  6. Create a form that allows the user to browse through the information saved in this database about producers. Insert a subform that presents details about the movies purchased during the current year from the selected producer.
  7. Create a form that allows the user to browse through the information saved in the database about customers. Insert a subform that presents details about the ticket purchases of the selected customer during the last month. Insert textboxes that present the following information: the total amount of money the selected customer has spent during the current month; the total revenues generated from ticket salesduring the current month; and the total revenues from ticket sales during the current year. Insert a command button that, when clicked-on, returns details about the most preferred customers.

Design a logo for this database. Insert this logo in the forms created above. Pick a background color for the forms and colors for the borders of the titles. Include the following in the forms created: record navigation command buttons, record operations command buttons, and form operations command buttons as needed.

Reports

  1. Create the following financial reports:
  2. Report details about monthly expenses of the movie theater.
  3. Report details about monthly revenues of the movie theater.
  4. Create a summary report of annual expenses, revenues, and earnings of the movie theater.
  5. Report details about the transactions performed during the current year with the movie producers. Group the information by producer name.
  6. Use the chart wizard to plot the following:
  7. The total number of tickets sold by movie.
  8. The average monthly use of each showroom.
  9. The total number of tickets sold per month during the last twelve months.
  10. The total number of movies purchased from each producer.
  11. The total number of new movies per month during the last twelve months.
  12. The total number of new members per month during the last twelve months.
  13. The monthly earnings during the last twelve months.
  14. The annual earnings during the last five years.
  15. The annual expenses from purchasing movies during the last five years.
  16. Report details about the five producers with whom the theater did most of its business during the current year.
  17. Report details about the one hundred most preferred customers.
  18. Report details about the movies that won an award in the current year.
  19. Report details about the actors who won an award in the current year.

Visual Basic.NET Application Development

This database application can be used by the employees and the managers of the movie theater, the database administrator, etc. In the following figure we present a tentative layout of the system.

In the welcome screen, the user can choose one of the five options presented. We give details about the forms or set of forms to be included in each option; however, you are encouraged to add other forms you find relevant. We suggest that the queries, forms, and reports already created in the Access Application Development section be included in here.

Movies: This part of the database keeps information about movies shown in the theater. The customers browse the database to learn about the movies schedule, to learn about new movies, etc.

Producers: This part of the database presents information about the producers.

Customers: This part of the database presents information about the customers.

Statistics, Graphs & Data Analysis: Users browse this part of the database to identify trends in the annual expenses, the annual revenues, the total number of new members, etc.

Update: This form allows the user to add/delete/update the information kept in this database about movies, producers, customers, ticket sales, etc.

Web Extension

A user may access this database from personal computers at home or in the office. The user could be a customer, an employee or a manager, the database administrator, etc. The user should have a login name and a password to be able to access the system. The customers access the database to learn about the movie schedule and new movies, to purchase tickets, or to update their personal information. The managers use the system to identify total expenses, total revenues, employee schedules, etc. The database administrator can have access to the update forms.

Develop an ASP.NET web application that will enable the users to access the database and perform the activities described above. Your application will have forms similar to the ones described in the VB.NET Application section.