DBI202: Database Systems

Assignment

1. Description:

As you know, databases today are essential to every business. Whenever you visit a major Web site Google, Yahoo!, Amazon.com, or thousands of smaller sites that provide information there is a database behind the scenes serving up the information you request. Corporations maintain all their important records in databases.

The objective of this project is to learn how to design, implement and manipulate a database in SQL Server corresponding to the requirements of a system that you choose.

2. Requirements:

You should work in groups on this project. The idea is to choose a subject (among the list of subjects provided by the teacher) for your group. You have to study how the system works, then design and implement a database for this system.

In order to do this project, you should do the following steps :

1. Choose a system to study

Try to study how the system works and describe in details the system : the entities and their functions in the system, the information needed for each entity, the work flows of the system.

2. Propose an Entity / Relationship model for the system

3. Convert the ER model to relational model with corresponding relations and functional dependencies.

4. Create in SQL Server a database corresponding to your relational model. You should create your database and the corresponding tables by using SQL statements. The statements for the creation of the database and the corresponding tables should be saved in a createDB.sql file.

5. Create at least 3 constraints by using alter table statements and save these statement in a constraints.sql file.

6. Insert data in your database (at least 5 records for each table). The statements for the insertion should be saved in a insert.sql file.

7. Write some at least 1 query for each of the following requirements. All queries should be saved in the queries.sql file.

   1. Query using inner join.

   2. Query using outer join.

   3. Using subquery in where.

   4. Using subquery in from.

   5. Query using group by and aggregate functions.

8. Write at least one transaction using rollback and save into the transaction.sql file.

9. Write at least one trigger and save into trigger.sql file.

10. Write at least one procedure and save into procedure.sql file.

3. Submission:

You should zip all files of your project into DBI202Project_CC_NNN_RN.zip file and submit on the CMS system where CC is your class, NNN is your fullname and RN is your roll number. In your submission, you should have :

• A final report in the format .docx or .pdf which describes in detail all your steps.

• All .sql files of your project.