Dept. of Computer Science
Database Management Systems (CSC570.001)
Database Project (40 Points)
Consider an organization of your choice (e.g., manufacturing company,
bank, acounting system, video rental, medical applications, retail store,
Online_shoping,..) for which you are assigned to design and develop a
database. Note that this is a real world database and therefore al entities,
attributes, relationships, and assumptions must be reasonable.
1) - Define the information content of your database.
a)-Define a set of entities and apropriate atributes for each entity.
Minimum 10 entities.
b)-Define a set of relationships that might exist between/among entities
and attributes. Such relationships may include one-to-one, one-to-many
and many-to-many asociations.
c)-Define a set of constraints that may be imposed on data.
2) - Define an E-R Diagram for your database design.
3) - Define a relational schema for your database design.
Make sure that you have both one-to-many and many-to-many
associations.
a)-Define one or more realistic key(s) for every relation
scheme. Use both simple and composite keys.
b)-Define a realistic set of Functional / Multi-Valued Dependencies
(when apropriate) for every relation scheme.
C-Check whether your relational schema is in 2NF, 3NF, BCNF, 4NF.
d)-Put your relational schema in the highest normal form. that is posible.
Note that, every relation scheme should be in a specific normal form. in
order to have the relational schema in that normal form.
NOTE: Please provide a detailed explanation for every question when
appropriate.
4) Implementation: Create your database using Oracle, or MySQL,
or.. to Perform. the following operations.
A) You are required to execute SQL queries that include the folowing
operations. For each query, provide the SQL statements along with the
output. For each of the following, try diferent SQL statements (i.e.,
using one relation, more than one relations,..).
- Create tables:
- Select:
select involving one/more conditions in Where Clause
select with agregate functions (i.e., SUM,IN,MAX,AVG,COUNT)
select with Having, Group By, Order By clause
Nested Select
select involving the Union operation
- Insert:
insert one tuple into a table
insert a set of tuples (by using another select statement)
insert involving two tables
- Delete:
delete one tuple or a set of tuples: from one table, from multiple tables.
- Update:
update one tuple or a set of tuples: from one table, from multiple tables.
- Create View:
based on one relation and more than one relation:
operate on View (i.e., select, insert, delete, update,.)
B) Also, create at least 4 diferent practical/useful trigers (writen in
MySQL) for your database to perform. the folowing tasks:
- enforcing referential integrity
- enforcing atribute domain constraints
- creating database log
- gathering statistics
C) Use MySQL and ColdFusion to create a Web-based application to
enable the user to do the folowing operations:
Add a record
Delete a record
Update a record
Query (at least 3 select statements on one relation)
---------------------------------------------------------------------------------------
NOTE: - You can define your own hypothetical organization, provided
that you give enough information as to what this organization does.
- You can make any asumptions about your database, provided that you
define them and give reasons as why these asumptions are being made.
- Please let me know if you have any trouble in finding a suitable
practical/operational organization.
- Attach any documents that you use to acquire the data for your
database.
- This Project is graded based on acuracy and completenes
and its practicality to real world problem.
--------------------------------------------------------------------------------------
You ned to provide a complete copy of the entire
project:
Al the inputs, outputs, sql statements, sql results,
codes for triggers, codes for htl/ColdFusion, on-
line operations scren shots, etc.
Create a zip file for your project and put it on the
blackboard.