please help

hi guys please help me with creating a database or whatever for this project on Program search analysis skill. the requirements are as follows please help on inventory control as follows :









INVENTORY CONTROL





Domino is a large departmental store, which caters to a wide range of requirements in Atlantis. The store is a major attraction to it’s customers because they can buy all that they need under one roof. Domino is going in for computerization of its inventory system and it wants you to develop it through SQL7.0.



The store has many suppliers supplying items to it. A supplier can supply one or more items. The store wants to record details of all available items in a database. The details to be stored are item code, item description, price, quantity on hand (currently available), recorder level and reorder quantity.



The new system will help in maintaining an optimum stock for each item. Whenever the quantity on hand goes below the reorder level, the store needs a system by which an order can be placed with the supplier through the purchase department for the reorder quantity.



Items sold in the store can be supplied by many suppliers and one supplier can supply many items. Each item is supplied at a particular price. The store needs to record details of each supplier in the database. Details are supplier identification number, supplier name, address, city and phone number. All transactions between the store and the supplier need to be recorded everyday. The system generates an order form, which contains order number, item code(s), supplier identification number and the quantity ordered.



Following list of activities to be performed:



1) Draw E/R diagrams showing all entities, relationships and attributes. The database needs to be in the third normal form.

2) Map E/R diagrams into tables.

3) Prepare a complete plan for table creation. This should include the following:

*Table name

*Field names, datatypes, size of fields. Field names should be meaningful and should follow certain convention.

*Defaults, rules and constrains for all fields.

*Indexes to be created

*Primary and foreign keys

4) Create tables according to the plan

5) Prepare a complete plan for implementing data integrity and referential integrity. This should include:

*Stored Procedures

*Triggers

6) Create stored procedures and triggers according to the integrity issues.

7) Create sample data. This should conform to the integrity issues.

8) Create stored procedures/ batches to insert data into tables.

9) Create queries to display the required information.



Also should be able to perform the tasks:

1) Add/Modify/ Delete records from any table. You need to demonstrate the implementation of data integrity and referential integrity. Appropriate messages should be displayed.

2) Display specified records based on the requirements.



Queries can be created to display the following:



1) All item details for a specified supplier.

2) Supplier details for a specified item.

3) Order details for a specific item.

4) All items for which the difference between quantity on hand and recorder level is 10.

5) Total sales for all items.

6) Number of items in the store.

VIDEO LIBRARY



Jurassic is a large video library located at Bombay, which rents laser discs to customers. The video library needs to maintain a record of its customers and the rental transactions in a database. Hence, the library wants you to develop an application using SQL 7.0 in order to computerize its rental system. The library lends out laser discs on a rental basis only to its members. Laser discs are hired out for a maximum of 3 days after which the penalty charges are included in the invoice. Hiring charges for each laser disc is Rs. 50/-.



The details about the customer that need to be stored include the customer identification number, customers first and last name, phone number and the address.



The details of all available laser discs are stored in the database. This database would contain a unique identification number for each movie, movie cast, number of copies and movie rating (example: PG-Parental Guidance, (12)-12 years and above, (18)-18 years and above and G-General). The library has only 3 copies of each movie.



As and when the discs are rented, they need to be recorded through an invoicing system. Every transaction records an invoice number, which is generated automatically, scheduled date of return, actual date of return and fine amount if the disc is not returned by the scheduled date then the fine is levied, which is Rs. 10/- per day of the delay per disc. Actual date of return is the date when the disc is returned to the library.



Following is the activities to be performed:



1) Draw E/R diagrams showing all entities, relationships and attributes. The database needs to be in the third normal form.

2) Map E/R diagrams into tables.

3) Prepare a complete plan for table creation. This should include the following:

*Table name

*Field names, datatypes, size of fields. Field names should be meaningful and should follow certain convention.

*Defaults, rules and constrains for all fields.

*Indexes to be created

*Primary and foreign keys

4) Create tables according to the plan

5) Prepare a complete plan for implementing data integrity and referential integrity. This should include:

*Stored Procedures

*Triggers

6) Create stored procedures and triggers according to the integrity issues.

7) Create sample data. This should conform to the integrity issues.

8) Create stored procedures/ batches to insert data into tables.

9) Create queries to display the required information.



Also should be able to perform the tasks:

1) Add/Modify/ Delete records from any table. You need to demonstrate the implementation of data integrity and referential integrity. Appropriate messages should be displayed.

2) Display specified records based on the requirements.



Queries can be created to display the following:



1) All transactions on a specified date.

2) All transactions for which the fine amount is >0.

3) All transactions for which the date of return is equal to the current date.

4) Movie details for a specified movie title or movie rating.

5) Movie details for which number of copies is <2<br>
6) For all movies, the total number of times the movie is rented and the total charges.




Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories