CS630 - DATABASE Systems-Final Project Group Project: 2 students The following i
ID: 3914083 • Letter: C
Question
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 customer’s 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
Explanation / Answer
1. Database Written Report:-
Now I am going to write the business case and the database requirement for the retail store or super market.
Retail store wants to develop the database to manage their stock , inventory, sales and the employees information.
In this the database should store all the items and their quantity available in the store and when the purchase done by the customer the quantity should be decreased based on the quantity purchased by the customer and it shoud give the notification for the items which are below the specific limit in the store. Also it should store all the transaction information to get the complete idea of sales , profits and losses etc..
As this is the database deal with the retail store it will be used by the store manager to update the quantities and the items, and the employees while the time of billing and inventory manager to update the stock in the inventory or to create the order requests based on the stock available in the market.
Also this database will store the customer information like name and mobile number for subscriptions to give some special offers based on their purchases etc like that.Basically in this database each item has its own item id's and also for each transaction it creates one unique transaction id's and also will store unique employee id's for each employee working in that store etc.
2. Database table Creation Queries:-
Based on the above description now we are creating the 5 most important tables in the database.
3. Queries:-
4. Sequence is used in the database to automatically increase the number in a specified sequence.
The above is the sequence it will automatically increments the value by 1 for each call of sequence by starting with 1 and
it will increase upto 250, once it reach 250 it will automatically starts from 1 again because of cycle keyword.
We have to assign this sequence during the table creation time to the column and we have to alter the table to add this
saleseq to the column in particular table.
---> Indexes are the ones used to retrieve the data faster from the database.
The above is the index for the inventory table which makes the information retrieval faster when we create this in database.