Assignment: USING DATABASE QUERIES TO MAKE DECISIONS
Objectives: Import data from a spreadsheet. Perform calculations and summarize data using SQL queries in Microsoft Access.
Research the following SQL commands using the tutorial on SQL.
• SQL Select
• SQL Where
• SQL And & Or
• SQL Order By
• SQL Wildcards
• SQL Between
Find examples in the Lecture Notes for this lesson.
Network Protocols and the OSI Layer
Problem: You import a list of network protocols and match them to the appropriate OSI Layer. Create Microsoft Access SQL queries to analyze the data.
Create a New Database
1. Create a new, blank database named LastNameFirstInitialLab8 and save it in the folder where you are storing your files.
2. Close the new blank table created automatically by Access without saving it.
Import Data from Excel
3. Click the External Data tab and click Excel in the Import & Link group.
4. Download and navigate to and select the OSI_Model workbook to be imported.
5. Import the source data into a new table in the current database.
6. Select the First Row Contains Column Headings option.
7. Set the OSI_Layer field Indexed option to Yes(No Duplicates).
8. Choose OSI_Layer as the primary key when prompted and create the table name OSI_Model.
9. Import the Protocols workbook into a new table in the current database. Set the Protocol as the primary key, and then change the indexing option to Yes(No Duplicates).
10. Accept the table name Protocols.
11. Change the ID field name in the Protocols table to Protocol.
12. Open each table in Datasheet view to examine the data. Widen the columns as necessary. Close the tables.
Create Relationships
13. Open the Protocols table in Design View.
14. Click on the field named Layer. For the Data Type, select Lookup Wizard. Keep the selection of I want the lookup field to get the values from another table or query.
Click Next. To answer Which table or query should provide the values for your lookup field? select the table OSI_Model. Click Next. Select the OSI_Layer field as the lookup field. Click Next. Sort the OSI_Layer field in Ascending order. Continue clicking Next and Finish. Save the table to create the relationships.
15. Close the table Protocols.
Finish Creating the Relationship
16. Click the Database Tools tab. Click on Relationships.
• Right click the line between the OSI_Model table and the Protocols table.
• Edit Relationships. Click Enforce Referential Integrity and Cascade Update Related Fields and OK. Close the Relationships window.
Complete the Protocols table data
17. Open the Protocols table in Datasheet view.
18. Click on the dropdown box in the Layer field for the first protocol. Find the appropriate layer for that protocol. For example, the first protocol is DHCP. By searching the following table under the Implementation & Protocols column, you will find that DHCP is in the Application Layer. Click on the dropdown box in the Layer field and select Application Layer for DHCP. Do the same for the remaining protocols.
Create a Form
19. Use the Form tool to create a form for the OSI_Model table.
a. Change the theme to one that is not Office.
b. Bold the field names. Change the title of the form to OSI_Model Form.
c. Widen columns so that all data is visible.
d. Scroll to the Transport Layer and add the protocol, UDP with the Description of User Datagram Protocol.
e. Save the form as OSI_Model Form, and then close it.
Create Queries using SQL
20. To access the SQL window, click on the Create tab, Query Design. Close the Show Table window. Click on the SQL View in the upper left ribbon.
21. Complete the following queries and save the queries. Open a new SQL window following step 19 for each query. The first one has been done for you. Type the
SELECT command in the QUERY window. Don't forget the semi-colon at the end!
a.List the complete Protocols table.
SELECT * from Protocols;
i. Click the Run button for the Query to run.
ii. Close and save the query as Protocol.
For each of the following queries, repeat step 20, then type the query in the SQL window. Close and save the query as noted. Find examples of how to write queries in the Lecture Notes for this lesson.
b. List the protocol and description of each protocol. Save the query as Protocol and Description.
c. List the complete OSI_Model table. Save the query as OSI Model.
d. List the name of each OSI_Layer that is between LayerNum 1 and 5, inclusively. Save the query as OSI Layers Between 1 and 5.
e. List the OSI_Layer, function, and datatype from the OSI_Model that are at LayerNum 4 and above. Save the query as OSI Layer 4 and Above.
f. List the name of each protocol in the physical layer. Save the query as Protocols in the Physical Layer.
g. List the name of each protocol NOT in the application layer. Save the query as Not Application Layer.
h. List the protocols in the Network layer OR in the Transport layer. Sort by Layer. Be careful! If all of the records show, your query is wrong. Try again. Save the query as Network or Transport Layer.
i. List all of the protocols that have the word "protocol" in the Description. Sort by Layer, then by Description. Save the query as Protocol in Description.
Create a Report
22. With the Protocols table selected, create a Report using the first Report icon on the menu.
• In Layout view, resize the fields so that all the fields (including the date and page number) are within the page area and all field values are fully displayed.
• Group the data by Layer. Sort by Protocol.
• Rename the report heading to OSI Layers with Protocols.
• From the Property sheet, add a picture to the report. Stretch the size to fit.
• Choose the same Theme as the OSI_Model Form.
• Save the report as OSI Layers with Protocols Report.
• Close the Report.
23. Save and close the LastNameFirstInitialLab8 database.
Attachment:- SQL-Assignment.rar