EBA3420 Databases Term Paper Assignment
Spring 2022
Practical information
Hand-out date and time: 19.04.2022, 09:00
Hand-in date and time: 03.05.2022, 12:00
Group size: 1 - 3 persons
Weight: 70% of the total grade
Introduction
In the term project you are going to combine everything you have learned during the course: from
creating an ER model based on a textual specification of an information system, to converting it to a
relational model and implementing it in a relational database, populating it with data and writing a
web application for querying and modifying the database.
Customer specification and requirements
Oslo Cinematix AS is a small company that owns a movie theater in Oslo with several auditoria
(kinosaler). You are asked to develop a simple intranet for employees of the movie theater with the
functionality described below.
The homepage will list 5 upcoming viewings, sorted by their date and time, including the following
information for each of them:
• the title of the movie,
• the date and time,
• the auditorium,
• the ticket price (same for all tickets for a given viewing),
• a "More information" link or button.
Clicking on the "More information" link or button will take the user to a detail pagewith the following
extended information about the selected viewing:
• the title of the movie,
1
• the date and time,
• the auditorium,
• the ticket price,
• the capacity of the auditorium,
• the description of the movie,
• a table of ordered tickets for the viewing with the following information about the customers
(sorted by the last name):
– the last name,
– the first name,
– the email address,
– the phone number,
– the number of the tickets ordered.
• a form (below the table) to add a new order with the following fields:
– the email address of a customer,
– the number of tickets being ordered (1 by default).
You can assume that all customers are already registered in the system (and are uniquely identified
by their email addresses). When the form is submitted, the application must check that the customer
does exist and the number of the tickets does not exceeds the available capacity for the viewing. If
both conditions are met, the order is registered in the system, otherwise an error message is displayed
to the user. If the customer has already ordered some tickets for the viewing, the number of tickets
specified in the form will be added to their existing order.
Tasks
1. Make an ER-model of the database for the information system described above. This task
includes finding out what you need to store in the database. Make your own assumptions (and
write them down) if necessary. Think carefully about the design of your database in order to
avoid redundancy of data.
You can draw the diagram by hand, or create an account at and use ERDPlus.
2. Convert the ER model into a relational model. Write the SQL DDL statements to create the
tables. Make sure that every table in the database is in 3NF. In order to be sure that you include
all the details about the tables: correct data types, specification of primary and foreign keys,
etc., write the SQL statements yourself according to the ER diagram and use software tools
(ERDPlus, DB Browser for SQLite, or similar) only as supporting/checking tool.
3. Create an SQLite database and execute the SQL statements from the previous task.
4. Populate the database. Create at least 2 auditoria, 5 viewings, 2 movies and 3 customers. Use
SQL DML statements and data of your own choice to solve this task.
5. Implement the web application described above in Flask.
There are no requirements on the appearance of the web pages you create, i.e. as long as you
satisfy the content and functionality requirements, the graphical design of the pages does not
matter.
2
Deliverables and submission instructions
The final report in the PDF format must include the following parts:
A. an ER diagram,
B. a relational schema (optional),
C. all additional assumptions that you made,
D. all the SQL DDL and DML (i.e. CREATE TABLE and INSERT) statements (the whole content
of the SQL file in point E. below).
In addition you must upload a ZIP file of a folder including the following files:
E. an SQL file with all DDL and DML (i.e. CREATE TABLE and INSERT) statements (e.g. the
database.sql file that you create in VS Code or another editor of your choice).
F. the SQLite database (e.g., the database.db file that you can obtain, for example, by importing
your database.sql file into a database.db file).
G. the source code of the web application (the app.py file and a "templates" folder with all the
HTML files).
3