Database Systems & ApplicationsCourse
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
Database Systems & Applications
Course Project
1. Goal
⚫ Build a database driven web application from the ground up.
⚫ To develop the project, you are encouraged to use the Docker platform.
⚫ If you wish, you may use any languages, tools, microservice architecture,
or web application frameworks, and run servers on your own machines or
cloud servers.
⚫ Submit a report, together with your data and code.
2. Project Requirements
The project is to build a database driven web application. Specifically, you will
need to complete the following tasks over the course of this semester.
1) Pick your favorite data management application. It should be
relatively substantial, but not too enormous. Several project ideas are
described at the end of this document, but you are encouraged to come
up with your own. When picking an application, keep the following
questions in mind:
⚫ How do you plan to acquire the data to populate your database?
Use of real datasets is highly recommended. You may use program
generated “fake” datasets if real ones are too difficult to obtain.
⚫ How are you going to use the data? What kind of queries do you
want to ask?
⚫ How is the data updated? Your application should support both
queries and updates
2) Design the database schema. Start with an E/R diagram and convert
it to a relational schema. Identify any constraints that hold in your
application domain, and code them as database constraints. If you plan
to work with real datasets, it is important to go over some samples of
real data to validate your design (in fact, you should start Task 7 below
as early as possible, in parallel to Tasks 3~6). Do not forget to apply
database design theory and check for redundancies.
3) Create a sample database using a small dataset. You may generate
this small dataset by hand. You will find this sample database very
useful in testing, because large datasets make debugging difficult. It is
a good idea to write some scripts to create/load/destroy the sample
database automatically; they will save you lots of typing when
debugging.
4) Design a web-based user interface for your application. Think
about how a typical user would use your application. Optionally, it might
be useful to build a “canned” demo version of the site first (i.e., with
hardcoded rather than dynamically generated responses), while you
brush up your application design skills at the same time. Do not spend
too much time on refining the look of your interface; you just need to
understand the basic “flow” in order to figure out what database
operations are needed in each step of the user interaction.
5) Write SQL queries that will supply dynamic contents for the web pages
you designed for Task 4. Also write SQL code that modifies the
database on behalf of the user. You may hardcode the query and
update parameters. Test these SQL statements in the sample database.
6) Choose an appropriate platform for your application. Python or
PHP? JavaScript or plain HTML? Start by implementing a “hello world”
type of simple database driven web application, deploy it in your
development environment, and make sure that all parts are working
together correctly.
7) Acquire the large “production” dataset, either by downloading it
from a real data source or by generating it using a program. Make sure
the dataset fits your schema. For real datasets, you might need to write
programs/scripts to transform them into a form that is appropriate for
loading into a database. For program generated datasets, make sure
they contain enough interesting “links” across rows of different tables,
or else all your join queries may return non-empty results.
8) Test the SQL statements you developed for Task 5 in the large
database. Do you run into any performance problems? Try creating
some additional indexes to improve performance.
9) Implement and debug the application and the web interface. Test
your application with the smaller sample database first. You may need
to iterate the design and implementation several times in order to
correct any unforeseen problems.
10) Test your application with the production dataset. Resolve any
performance problems.
11) Polish the web interface. You may add as many bells and whistles as
you like, though they are optional because they are not the main focus
of this course.
3. Milestone 1
You should have completed Tasks 1~5 and have started thinking about 6 and
7. If you plan to work with real data, you should also have made significant
progress on Task 7 (you should at least ensure that it is feasible to obtain the
real dataset, transform it, and load it into your database).
4. Milestone 2
You should have completed Tasks 1~8 and have made good progress on 9.
5. Project Demo
At the end of the semester, you will need to present a working demo of your
system. Prior to your demo, submit the following:
1) A final project report, including
⚫ A brief description of your application.
⚫ A plan for getting the data to populate your database, as well as
some sample data.