Data Base Project
Dept. of Computer Science

Database Management Systems (CSC570.001) Database Project (40 Points)


Consider an organization of your choice (e.g., manufacturing company, bank, accounting system, video rental, medical applications, retail store, Online_shopping,…) for which you are assigned to design and develop a database. Note that this is a real world database and therefore all entities, attributes, relationships, and assumptions must be reasonable.

1) – Define the information content of your database.
a)-Define a set of entities and appropriate attributes 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 associations.

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 appropriate) 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 possible. 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 following operations. For each query, provide the SQL statements along with the output. For each of the following, try different SQL statements (i.e., using one relation, more than one relations,…).

– Create tables:
– Select:


select involving one/more conditions in Where Clause
select with aggregate functions (i.e., SUM,MIN,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 different practical/useful triggers (written in MySQL) for your database to perform the following tasks:

–

enforcing referential integrity
- enforcing attribute 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 following 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 assumptions about your database, provided that you define them and give reasons as why these assumptions 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 accuracy and completeness and its practicality to real world problem.

—————————————————————————————

You need to provide a complete copy of the entire project:
All the inputs, outputs, sql statements, sql results, codes for triggers, codes for html/ColdFusion, on- line operations screen shots, etc.

Create a zip file for your project and put it on the blackboard.