Purpose: Database Design.
General Description:
================
Design a MS Access Database along with Entry Forms, SQL Queries, Reports, and Entity Relations Diagram for an Inventory Purchasing System to be used by a Retail Distributor Company.
The company buys products from several suppliers. An inventory item can be supplied by more than one supplier. A product can be offered by several suppliers at different costs.
The Retail Distributor owns several distribution centers on which inventory is stocked. An inventory item stocked at a distribution center has a current on hand balance and quantity on order from suppliers expected to arrive at the particular location.
Suppliers can deliver their products to any of the distribution centers. The cost of product varies depending on the location where it needs to be delivered.
To order products from a supplier, a buyer creates a Purchase Order on which he/she specifies when the product must be delivered, which distribution center to deliver them to, the list of products ordered and required quantity for each, the total cost of each product, and total value of products ordered. The quantity of product ordered on PO is registered as product On Order in the respective distribution center.
When the products ordered on a Purchase Order arrive at the specified distribution center, a warehouse person retrieves the purchase order being delivered from the Database and proceeds to receive the products into inventory, therefore increasing the current on hand balance of a product, and reducing the quantity on order for the products received. Note that a vendor can short-ship or over-ship a product.
There are three principal user roles in this system: Administrator, Buyer, and Warehouse Receiver.
The administratror maintains master file information such as Company Information, Distribution Center Information, Supplier Account, Inventory Items, Supplier Products and costs, System Users, etc.
The buyer creates purchase orders and also received products into inventory.
The warehouse Receiver retrieves an existing Purchase Order from the Database, receives products into inventory, and can also add lines to an existing Purchase Order.
Each team is to implement a solution for the software requirements described above. Your solution must be object-oriented and component-oriented.
The system will store master and transactional data in a MS Access Database. The name of the MS Access Database is “Purveyors_g#”
where g# corresponds to a student group number implementing the solution. You will create several tables in your group’s database to store and maintain your application’s data.
You will demonstrate your solution to the rest of the class on 11/27/2007 during class.
What to turn in:
- Design Document (Entity Relationship Diagram In MS Visio)
Identify the various entities in the above requirements that can be expressed as Database Entities, and identify the proper type of MS Access Objects to model your entities. You may list any others that you can think of are present in a Warehouse and Distribution Center and in Product Purchasing/Replenishment Activities.
Draw an entity diagram listing your database entities and the proper relationships between them. A Screenshot of the Access Relations Diagram would suffice. Make sure to show ALL tables from your database in this diagram.
Identify relationships between the tables and establish proper primary and foreign keys.
Identify proper attributes(columns) for each of your database entities(tables) and choose proper data types for these.
- Your Implementation in an Access Database File
Your MS Access Database will include forms for all the user groups, along with any queries, and reports that you think are necessary.
- Power Point Presentation
Compress all files in a (ZIP) Archive File and turn in a CD-Rom.
Print Reports and submit everything in a paper folder.
Make sure to list complete name of Members that form your Project Team.
The following are screen shots of Forms and reports from a Medical Practice Project Implemented by students of a previous term of your CGS2100 Class. You may use them to get ideas on how to layout the forms and reports for your project this term.
To create this type of form with drop down combo boxes, you will need to read ahead
Access Tutorials on Advanced Form Design. The following is a screen shot illustrating
The set up of a combo box with hard-coded list values.
The above screen shot illustrates the set up of a combobox bound to a table.
Good Luck.
General Description:
================
Design a MS Access Database along with Entry Forms, SQL Queries, Reports, and Entity Relations Diagram for an Inventory Purchasing System to be used by a Retail Distributor Company.
The company buys products from several suppliers. An inventory item can be supplied by more than one supplier. A product can be offered by several suppliers at different costs.
The Retail Distributor owns several distribution centers on which inventory is stocked. An inventory item stocked at a distribution center has a current on hand balance and quantity on order from suppliers expected to arrive at the particular location.
Suppliers can deliver their products to any of the distribution centers. The cost of product varies depending on the location where it needs to be delivered.
To order products from a supplier, a buyer creates a Purchase Order on which he/she specifies when the product must be delivered, which distribution center to deliver them to, the list of products ordered and required quantity for each, the total cost of each product, and total value of products ordered. The quantity of product ordered on PO is registered as product On Order in the respective distribution center.
When the products ordered on a Purchase Order arrive at the specified distribution center, a warehouse person retrieves the purchase order being delivered from the Database and proceeds to receive the products into inventory, therefore increasing the current on hand balance of a product, and reducing the quantity on order for the products received. Note that a vendor can short-ship or over-ship a product.
There are three principal user roles in this system: Administrator, Buyer, and Warehouse Receiver.
The administratror maintains master file information such as Company Information, Distribution Center Information, Supplier Account, Inventory Items, Supplier Products and costs, System Users, etc.
The buyer creates purchase orders and also received products into inventory.
The warehouse Receiver retrieves an existing Purchase Order from the Database, receives products into inventory, and can also add lines to an existing Purchase Order.
Each team is to implement a solution for the software requirements described above. Your solution must be object-oriented and component-oriented.
The system will store master and transactional data in a MS Access Database. The name of the MS Access Database is “Purveyors_g#”
where g# corresponds to a student group number implementing the solution. You will create several tables in your group’s database to store and maintain your application’s data.
You will demonstrate your solution to the rest of the class on 11/27/2007 during class.
What to turn in:
- Design Document (Entity Relationship Diagram In MS Visio)
Identify the various entities in the above requirements that can be expressed as Database Entities, and identify the proper type of MS Access Objects to model your entities. You may list any others that you can think of are present in a Warehouse and Distribution Center and in Product Purchasing/Replenishment Activities.
Draw an entity diagram listing your database entities and the proper relationships between them. A Screenshot of the Access Relations Diagram would suffice. Make sure to show ALL tables from your database in this diagram.
Identify relationships between the tables and establish proper primary and foreign keys.
Identify proper attributes(columns) for each of your database entities(tables) and choose proper data types for these.
- Your Implementation in an Access Database File
Your MS Access Database will include forms for all the user groups, along with any queries, and reports that you think are necessary.
- Power Point Presentation
Compress all files in a (ZIP) Archive File and turn in a CD-Rom.
Print Reports and submit everything in a paper folder.
Make sure to list complete name of Members that form your Project Team.
The following are screen shots of Forms and reports from a Medical Practice Project Implemented by students of a previous term of your CGS2100 Class. You may use them to get ideas on how to layout the forms and reports for your project this term.
To create this type of form with drop down combo boxes, you will need to read ahead
Access Tutorials on Advanced Form Design. The following is a screen shot illustrating
The set up of a combo box with hard-coded list values.
The above screen shot illustrates the set up of a combobox bound to a table.
Good Luck.
but uh.. good luck on the project? 
