Spring 1997 Tsai
Spring 2007 Tsai
CALIFORNIA STATE UNIVERSITY, SACRAMENTO
School of Business Administration
MIS 101 – Computer Information System Management
Project 1: Database and Information
Saratoga Chip Corporation (SCC) is a wholesaler of silicon integrated circuits (ICs) that are commonly found in all modern electronic equipment. SCC buys ICs in large quantities from manufacturers, warehouses them, and sells them, usually in smaller batches, to manufacturers of computers, cell phones, and other electronic equipment.
SCC has kept its customer, sales, and product data in three Excel worksheets of one Excel workbook named SaratogaChip.xls. It is hard to generate useful information from these three different worksheets even they are stored in one Excel file. Therefore, SCC has hired your team from MIS101 Consulting, Inc., of Sacramento to (1) convert the data from these three workbooks of a Excel file into one integrated database using a database management system named Microsoft Access and (2) generate several analytical reports to help the management to understand its sales performance related to its customers, sales, and products for future business planning and decision making.
SCC requirements are:
1. Create a new Microsoft Access database named MIS101XX.MDB, where XX is your team number.
2. Within the database MIS101XX.MDB, create a table named Customer by importing the data from the worksheet named Customer in the Excel workbook file named dataaccess07s.xls. Create a second table named Sales by importing the data from the worksheet named Sales in the Excel workbook file named dataaccess07s.xls. Create a third table named IC by importing the data from the worksheet named IC in the Excel workbook file named dataaccess07s.xls.
3. Create a one-to-many relationship between Customer table and Sales table and a one-to-many relationship between IC table and Sales table. Save the relationship diagram as a report named CustomerSalesIcRelationship.
4. Create a query named SalesInfo having the following information: SalesNo, SalesDate, CustomerNo, CustomerName, ICNo, ICName, Quantity, UnitPrice and TotalAmount. The TotalAmount is equal to UnitPrice times Quantity. Sort records according to the CustomerNo.
5. Create a query named SalesInfoICdvd having the following information: SalesDate, CustomerName, ICNo, and TotalAmount. The TotalAmount is equal to the UnitPrice times Quantity. The SalesInfoICdvd query contains the sales information of DVD during 2006. Sort records according to the SalesDate.
6. Create a query named SalesInfoCustIBM having the following information: SalesDate, CustomerNo, ICName, and TotalAmount. The TotalAmount is equal to the UnitPrice times Quantity. The SalesInfoCustIBM query is the sales information of customer IBM during 2006. Sort records according to the SalesDate.
7. Create a report named SalesInfoICdvdRep that is based on the data in the SalesInfoICdvd query. The report header should have a title as “2006 DVD Sales Analysis”, the date in the upper left corner, and your group number in the right upper corner. The detail section contains SalesDate, CustomerName, ICNo, and TotalAmount. Sort records according to the SalesDate. The report footer contains the average and sum of the TotalAmount.
8. Create a report named SalesInfoCustIBMRep that is based on the data in the SalesInfoCustIBM query. The report header should have a title as “2006 IBM Sales Analysis”, the date in the upper left corner, and your group number in the right upper corner. The detail section contains SalesDate, CustomerNo, ICName, and TotalAmount. Sort records according to the SalesDate. The report footer contains the minimum and maximum of the TotalAmount.
9. Create a report named SalesInfoICRep that is based on the data in the SalesInfo query. The report should have a title as “Sales Analysis for Every IC” the date in the upper left corner, and your group number in the right upper corner. The group header of each group contains ICName. The detail section for each group contains SalesNo, SalesDate, CustomerName, and TotalAmount. Sort records within each group according to the SalesDate. The group footer of each group contains the average, sum, minimum, and maximum of the TotalAmount.
10. Create a report named SalesInfoCustRep that is based on the data in the SalesInfo query. The report should have a title as “Sales Analysis of All Customers” the date in the upper left corner, and your group number in the right upper corner. The group header of each group contains CustomerName. The detail section for each group contains SalesNo, SalesDate, ICName, and TotalAmount. Sort records with each group according to the SalesDate. The group footer of each group contains the average, sum, minimum, and maximum of the TotalAmount.
To be turned in: a storage media (such as CD) contains MIS101XX.MDB with tables, queries, and reports.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.