Sunway- Kuala Lumpar
Semester 1, 2011
The database assignment is divided into two major sections. The first requires you to develop a database in Access 2007. Imagine that this is a prototype that will have four tables and some basic features such as forms, reports and queries. The second part is documentation that is used to describe the database you have produced to the client who has requested the database. In this latter Microsoft Word 2007 document you will have a covering letter to the client and then approximately two or three pages of technical documentation that describes the basic features of the system you produced. Case Study
Code| Description| Price|
STA| Standard Kool Air Conditioner| $ 199.00 |
SUA| Super Kool Air Conditioner| $ 259.00 |
RAC| Really Kool Air Conditioner| $ 325.00 |
MAC| Basic Model Air Conditioner| $ 159.00 |
EAC| Executive Model Air Conditioner| $ 439.00 |
PAC| Lightweight Portable Model| $ 309.00 |
Imagine that you are a developer of small PC applications in products such as Microsoft Excel and Access. You have been approached by a business called Qwik Kool who manufacture air conditioning systems. Quik Kool only manufacture air-conditioning systems and all sales to retail customers are made through independently owned retail stores. There are (8) retail stores located in Australia and Malaysia. Qwik Kool sell six products as outlined in the table below. Qwik Kool have asked you to develop a database application using Microsoft Access 2007 to keep track of sales made by the retail stores. Qwik Kool has recorded sales, customer and store details in Excel. One of your first tasks is to transfer this data currently in an Excel spreadsheet into Access. Your second task is to create a table for Products in the Access database and to enter the data. The database you create will have four tables representing
Sales Table| | |
Field Name| Type| Size/Format| Description|
ReceiptNo| Number| Long Integer| Identifies transaction| Date| Date/Time| Med Date| Date of sale |
CustomerNo| Number| Integer| Customer ID Number|
StoreCode| Text| 2| Identifies Sales Agent|
ProductCode| Text| 3| Identifies the product|
Quantity| Number| Long Integer| Number of units sold|
PaymentType| Text| 1| Type of Payment|
four entities in the E-R Diagram of this system; Sales, Products, Store and Customers. The Sales table has data on ReceiptNo, Date, Store Code, Product Code, Quantity, CustomerNo and PaymentType (C=Cash, Q=Cheque, E=EFT). The Customer table includes CustomerID, Family Name, First Name, Address, Suburb, Postcode and Phone The Store table has only three fields: a Store Code, Store Name and Store Location. Part 1: Database Requirements (15 marks)
You are required to do the following tasks:
Build a database. To do this you will need to do the following: Create an Access database shell.
Import the data for three tables contained in the Excel spreadsheet into the Access database. Create a Products table and enter the products data.
All tables are to have fields with appropriate field names, types and sizes. Set up the relationships that join the tables. The joins should include all records from the Sales Table and any corresponding records in the other tables. You should enforce referential integrity. Create a columnar form, with the name “Sales”, which enables the operator to examine and edit all the information in the Sales table. A Columnar form shows the details of a single record at a time. Create a query called Sales Details which brings together the information in the three tables. The query should show the details listed below (Note: Total Value is a calculated field within the query and is equal to Total Value = (Quantity) * (Price)): Receipt No| Date| Store Code| Name...