Implementation of Database Design in MS Access 2010 - Assignment Example

The database design and the respective relationships are depicted in Figure 1.1. This image shows the implemented entity relationship diagram. The referential integrity is enforced with cascade update and cascade delete. All the relationships are of one – many types.In the second normal form, the tables are decomposed to curb redundancy. Like the table, “ProductTypes” was extracted out from the table “Products” to maintain the storage space and avoid redundancy in data.

This query uses 4 tables to retrieve data and to produce results. It returns the sale details with Customer names, Product Names, Product Type, and Date of Sale. Figure 1.2 highlights the design of this query.This query works similarly to the earlier one with the exception that it particularly focuses on individual customers. Figure 1.3 highlights the design of this query. At execution, the query would require the customer ID as input (example C-101) and would display its respective details as a result.

This query uses 3 tables to retrieve data and produce results. It returns the sales volume details with Product Names, respective Price, Sum of the quantity of each product sold, and the sum of sales amount for each product (calculated by retrieving values from two different tables). Figure 1.4 highlights the design of this query.This query uses 2 tables to retrieve data and produce results. It is designed to produce product records for which the stock quantity values are below certain thresholds.

Like in this query the threshold value is taken as 15. Figure 1.5 highlights the design of this query.The queries are really helpful in assessing the sale volume and the records of purchases done by the customers. This database can be useful for futuristic evaluation of business and for launching promotions by figuring out the sales volume and regular customers.The respective forms are created for each table to facilitate the data entry. The rest of the forms highlights the results of 4 queries.