Introduction to Information Systems
Learning Outcomes
Lab 06: Database using Microsoft Access
1. Learn about the basic concepts in database.
Copyright By PowCoder代写 加微信 powcoder
2. Understand the concept of entity (table), entity instance (record), attribute (field) and identifier
(key field).
3. Experience how to create and modify tables for data storage.
4. Experience how to create and modify queries for data retrieval.
Introduction
Microsoft Access is a desktop application for creating and maintaining databases. A database is a collection of information that is related to a particular subject or purpose, e.g., a database that maintains patients’ records for a clinic. The purpose of using a database is to coordinate and organize the information in a way that a centralized repository is transparent to the end users even the physical data are spread over different locations.
Imagine that you store your friends’ phone numbers in a paper-based phone book as well as in your mobile phone’s contact list. If some of your friends change their phone numbers, you would have to update the information in both places. To avoid the trouble of updating information more than once, it is better to store data in a central location. All data and any changes made to the data are entered into this location only. This arrangement not only makes the maintenance of the database much easier, but also guarantees the integrity of the data (i.e. same data will not have two different versions). To retrieve the data, users must get access to the database. In practice, a computerized database management system allows users to retrieve the information that they want and presents it in a nice format.
In this laboratory, you will learn some basic functions of Microsoft Access and a few key concepts in database management systems.
Task 1: View table’s content in Datasheet View
1. Open the file labAccessDB.accdb which can be downloaded from CANVAS.
2. There is one table named Customer Information. Double click Customer Information to review
the table content.
This view is called Datasheet view in Access. Note that Access’ table looks very similar to Excel worksheet and data are stored in rows (also known as records) and columns (also known as fields). This table contains the information about the customers.
Introduction to Information Systems
Task 2: View table’s structure in Design View
Click ViewDesign View
Field Name: The column names in Datasheet View.
Data Type: A proper data type of each of the field should be set in order to maintain data integrity, i.e. to make sure the data entered is accurate. The table below lists the different data types used in Access 2016.
Primary Key: Customer ID is set as the primary key field in this table with an icon ( primary key denotes a field that can uniquely identify the records in a table.
Field Properties: List the properties of the corresponding field.
Short Text Long Text Number Date/Time Currency AutoNumber Yes/No
OLE Object
Hyperlink Lookup Wizard
Description
Use for text or combinations of text and numbers. 255 characters maximum Memo is used for larger amounts of text. Stores up to 64,000 characters. Allows numbers between -2,147,483,648 and 2,147,483,647
Use for dates and times
Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places.
Unique value generated by Access for each new record.
A logical field can be displayed as Yes/No, True/False, or On/Off.
Pictures, graphs, or other ActiveX objects from another Windows-based application.
Contain links to other files, including web pages.
Let you type a list of options, which can then be chosen from a drop-down list.
PRO_ID *(Primary Key)
PRO_Weight
Short Text
300018 Rainbow Cake
198 7 inches 1.3 lbs
300029 Tiramisu
298 7 inches 1.9 lbs
Introduction to Information Systems
Task 3: Design the table of “Product Information”
1. Click CreateTable Design, a new Table1 will appear.
2. Give each field a Name (PRO_ID, PRO_Name, PRO_Price, PRO_Size, PRO_Weight) and specify their Data Type (as listed in the following table).
Field Name PRO_Name PRO_Size
Short Text Short Text
3. Designate the Primary Key Field: Right-click, and select Primary Key on the short-cut menu.
4. Click Save and name this table as Product Information.
Task 4: Data Entry
1. Switch to Datasheet View.
2. In the Datasheet View, enter the following data.
PRO_Name PRO_Price
Rose’s Fruit Cake 138
3. Enter data as in EXCEL. Use the TAB key to move from field to another field.
4.5 inches
PRO_Weight
0.7 lbs 1.4 lbs
Introduction to Information Systems
4. To insert or delete a record, right-click on the record and then select New / Delete Record from the short-cut menu.
Task 5: Create a Single Table Query
Query 1: Which customers have a contact number starting with 9?
1. On the window toolbar, click the Create Tab. Choose Query Design.
2. Select “Customer Information” table and click Add.
3. Close the Show Table window.
4. To display CUST_Last_Name as the first column, CUST_First_Name as the second column and
his/her contact number as the third column. Double click the field name or simply drag it to the table.
Introduction to Information Systems
5. Type Like “9*” in the criteria for CUST_Contact_No.
6. To run your query, press the button ( ). (Note: Make sure you have closed all the other tables when perform the query.)
7. To modify your query, click the button ( ) to go back to the Design View of the query.
8. Save your query as “qryLike9”.
Task 6: Create a Multiple Table Query
Query 2: Retrieve Purchase Information (Customer Names, Product Name, Price and Quantity) of those who had purchased a product that is priced over 200.
1. On the database window toolbar, click Create, and then choose Query Design.
2. Add Customer Information, Product Information and Purchase Record.
Introduction to Information Systems
3. Link the CUST_ID field of Customer Information to the PUR_CUST_ID field of Purchase Record by dragging CUST_ID to PUR_CUST_ID.
Link the PRO_ID field of Product Information to the PUR_Item_ID field of Purchase Record by dragging PRO_ID to PUR_Item_ID.
4. Create a query that shows the following information ― i.e. CUST_Last_Name, CUST_First_Name, PRO_Name, PRO_Price and PUR_Quantity ―which the item is priced over 200.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com