CS630 - DATABASE Systems-Final Project Group Project: 2 students

The following is an example of the business case similar to what you must create:(You can still select other domains like library, university, airport, retail store.)

The Video Library Business Case

You have been hired by the general manager of The Video Library to develop a working database system to handle their video inventory, customers and rental transactions. Their specialty is older classic movies that may be hard to find. They have many titles but only a few of each. Their store is too small to display them all, so they want to allow their customers to be able to browse for movies using a computer. Customers tend to look for movies based on their favorite actors or directors, its genre (comedy, western etc) and even the country where it was filmed. Some customers also like to choose their movies based upon whether it received awards. They basically want to browse to see what is available and decide if it might be a movie they would enjoy. Each rental item has a unique stock code that identifies the item.

The Video Library database must also store data about its customers, in particular, a customers name, address, city, state, zip code, and email. A customer may rent any movie in inventory, if it is available.

You can NOT use any of the stored database templates to create your database. Your case must be rigorous enough to need at least 5 related tables.

Requirements.

Instructions: The following is the minimal list of requirements your database application must contain.

I. Create a written report of your business case. Include the following:

1. What is the purpose of the database?

2. What do you want the database to accomplish?

3. Who are your intended users?

4. What are some of the main pieces of data that should be collected for your database and why?

II. Using the SQL developer, create your database It must include at least the following:

1. At least Five related tables. (For example: Customer, Inventory, Rentals, Rental Details.)

2. Each table must have the proper Primary and Foreign keys defined to create the relationship.

a. Each table must include:

i. At least 4 fields.

ii. If applicable each field must have:

1. Proper data types

2. Validation rules and messages

3 . Format masks

4. Proper captions

5. Logical field sizes

3. Queries build at least 5 queries of your choice

a. The Queries must be based on different tables

b. The queries should be useful and they should include some of those:

i. A calculated field

ii. sort criteria

iii. conditional statement

iv. Range Criteria

v. AND and OR criteria

vi. NOT criteria

4. Use sequences and indexes

DELIVERABLES:

1. Your final Database written Report: Word or PDF File.

2. Structured Query Language instructions: sql file