Web Database Project
are required to create a project and present this project to the class
at the end of the semester. You may work on teams (size to be
determined in class).
project may be any type of web
database project, or some type of project requiring complex querying.
Possible project topics are a
shopping cart system, a help-desk type of system, an email notification
system, or practically any kind of system that would require some kind
of server-side connectivity. There are many sample projects, some
in your texts, and many available elsewhere. You are not required
to build the project from scratch. You may use one of the
projects that were demonstrated in class, or build upon examples that
were used in labs. However, you must modify the
code to your specifications.
The application requirements of the project:
- Assume that this is a database for an organization/business. What is the title of this application?
- What is the organization/business for whom this application is being developed?
- What is the mission of this organization?
- What is the purpose of this application?
- Why will this application support the mission of the organization?
- Provide an ER diagram of the database, before any structural changes are made.
- Walk through how this application is used, both as a user and as an adminstrator.
- During your demonstration, be sure to point out the technical
specifications below. But make sure that the technical features make
sense in the context of your application. For example, if you are
demonstrating an aggregate function, you might say, "This is a query
that tells us how many orders each customer has placed. This is
useful if we are trying to analyze repeat business."
- You will proabably find it easier to take screen shots of your
data entry, validation and queries, and paste them into PPT
slides. In this way, your presentation will be smoother, you
won't rely upon scrolling through results or navigating through your
application during your presentation. In addition, you won't have
any unpleasant surprises if your server goes down or any other
unanticipated issues arise.
- Provide an ER Diagram of the database, after any structural changes that you have made.
Some of the technical specifications/components of the project:
The more complicated your queries, the more points you will recieve.
- The project should be written using PHP and MySQL.
at least one structural change to the database using DDL.
(Create, Alter, Drop). Clearly, your application will be written on one
version of the design. So you may satisfy this requirement by
discussing what changes in the business requirements might precipitate
a change in the design, and how would you implement that? An
example would be: the design is based on the reality that every
employee is assigned to exactly one department. But the company
has changed its management structure to a more project-based structure,
and now, employees can be assigned to multiple departments. This
necessitates a change in the cardinaly of department:employee from 1:n
to m:n. This now requires a new relationship entity. Other
examples might include changing the format of a zip code from XXXXX to
- Provide the INSERT SQL for initially populating the database.
at least one INSERT that will occur during the execution
of the application. This will most likely be as the result of a
transaction or user that should be added to the database. Unless
there is a good reason not to do so, use a "prepared" statement for
your query, using variable parameters and bindvalues, as discussed in
class and lab.
- Include at least one DELETE.
- Include at least one simple SELECT SQL statement.
- Inlcude at least one SELECT using an aggregate function.
- Include at least one SELECT using a compound condition.
- Include at least ond JOIN query. This can be an inner or outer join. It will be probably be an inner join.
- Excellence points: use a correlated subquery.
- Include at least one subquery.
- Use a form to collect user data. Pass variables to the next page (or postback) using POST.
- Use one GET to pass data to another page.
- Populate a field on a form or table from the database.
- Login and password using bcrypt. (Password hash and password verify currently in PHP.)
- Implement one trigger. Demonstrate what happens when it is implemented.
referential intergrity. Demonstrate what happens when it is
violated. Or, if you constructed your program so that it can't be
violated, demonstrate how it references a primary key and prevents a
vioaltion of referential intergrity. For example, if employee has
a foreign key deptNo that references the primary key deptNo in the
relation Department. If you populate a drop-down box with
existing department numbers, this will prevent the user from entering
an invalid department number, thereby enforcing referential integrity.
If you just have a text box, the user can enter a department
number that doesn't exist, and you can demonstrate that this will cause
a referential integrity error. Either method is fine, but be
clear on what you are trying to achieve and demonstrate.
- Implement one transaction. Show the impact on the DB or the application.
Emphases will be on understanding
the business value of the system and knowing how to apply the
appropriate technologies to the business needs as well as on
demonstrating proficiency in the ability to model a database and query
What to submit: (See Presentation Guidelines for more detail)
- Your PPT presentation.
- A zip file with your entire application. This can be saved
on our server, or on box.com, or any other method that I can
access. If it's too large to save/upload, then supplement with
additional screen shots, and meet with me so that I can review it.
- A short report, listing the points listed above, with a short explanation of how you have included each one.