Database Implementation

Instructions:

You need to do the implementation of the BCC database. The REA diagram that will be used to design the BCC database is given in Figure 1.

A non-redundant relational database in access to include:

Table Structure consistent with the information needs and database design issues. Tables and fields defined as suggested below and populated with limited data (simple attributes and use only static derivable attributes. All employees information is maintained in one table. Field definitions should give evidence of appropriate choices for data types, length, formats, etc.

Helpful Hints:

All table names should begin with tbl followed by the name (i.e. tblcash)

All attribute names and table names should contain no spaces

Choose appropriate data types for each attribute. Only numbers used in calculations should be given a number data type.

If you choose a data type of text , you need to change the field size to an appropriate length

Every table needs a primary key

All foreign keys need to have the same data type as the primary key, with one exception. If a primary key has an a auto_number data type, the foreign key should have a data type of a number

Create the tables (and enter data into your tables) with no foreign keys first (data reduction tables, followed by resource and agent tables, followed by event tables and then relationship tables)

After you create your tables, draw the relationship lines and enforce referential integrity.

Finally, each table needs to have a minimum of 3 rows of data entered into each attribute. Make up appropriate information.

Resource

Table name	Attributes/fields
1.Warehouse,location table table-	*Warehouse area number, product quantity on hand at warehouse area, Warehouse description
2.Inventory Table	*Product code, Product standard selling price, product unit measurement, Product name, product description, product standard cost
3. Cash	cash account name

Events:

Table name	attributes
4.Order	*order number, Order date, Actual quantity of product code sold, order selling price of product code, Date order needed
5.Sale/ Shipment	*sale number, actual selling price of product code, sales date, Actual quantity of product code sold
6.Cash Receipts	date of cash receipt, *Remittance number cash account number Cash receipt amount, Customer check number,

Agents

Table name	attributes
7.Customer Table	Customer number, Customer name, customer street, Customer telephone Customer fax, Customer Account Receivable Balance
8.Employee	*employee code ID, Employee social security number, Employee first name, Employee first name Customer Account Receivable Balance

Relationship Table Many to Many relationships

Table name	attributes
9.Warehouse-inventory	*Warehouse area number, *Product code,
10.Inventory -order	*Product code, *order number
11.inventory-sale	*Product code *sale number
12.order-employee	*order number *employee code ID
13.sale-employee	*sale number *employee code ID

*Primary Key

Please use only the following attributes

Actual quantity of product code sold order selling price of product code

Actual order quantity of product code date of cash receipt

Warehouse description Employee social security number sales date

Product name product description

Remittance number cash account number

Cash receipt amount cash account name

Product standard selling price product standard cost

Customer name customer street

Date order needed Employee last name

Customer check number Customer fax

Customer telephone Employee first name

Customer Account Receivable Balance

Warehouse Order Salesperson Inventory Sale/ Shipment Customer Cash HCashier Cash Receipts Warehouse Order Salesperson Inventory Sale/ Shipment Customer Cash HCashier Cash Receipts