Protocol Description Layer DHCP Dynamic Host Configuration

Sheet1protocoldescriptionlayerdhcpdynamic Host Configuratioin Protocol

Sheet1protocoldescriptionlayerdhcpdynamic Host Configuratioin Protocol

Sheet1 Protocol Description Layer DHCP Dynamic Host Configuratioin Protocol DNS Domain Name Server Ethernet A specification for a transmission system FTP File Transfer Protocol HTTP HyperText Transmission Protocol Hubs Device that serves as the center of a star-topology network IMAP4 Internet Message Access Protocol 4 IPv4 Internet Protocol Version 4 IPv6 Internet Protocol Version 6 NetBIOS Network Basic Input/Output System POP3 Post Office Protocol, version 3 Repeaters Devices which restore, amplify, re-clock or otherwise improve a network signal SMTP Simple Mail Transfer Protocol SSH Secure Shell SSL Secure Sockets Layer TCP Transmission Control Protocol Telnet Telephone Network Token Ring A ring topology network that uses token passing for MAC WI-FI IEEE 802.11 (Wi-Fi Alliance) A B Protocol Description DHCP Dynamic Host Configuratioin Protocol DNS Domain Name Server Ethernet A specification for a transmission system FTP File Transfer Protocol HTTP HyperText Transmission Protocol Hubs Device that serves as the center of a star-topology network IMAP4 Internet Message Access Protocol 4 IPv4 Internet Protocol Version 4 Sheet1 OSI_Layer LayerNum Function DataType NetworkComponents Application layer 7 Supports the communication between applications over the network User data Gateway Presentation layer 6 Presents data to the receiver in a form it recognises Encoded user data Gateway, Redirector Session layer 5 Establishes a connection and terminates it when no longer required Sessions Gateway Transport layer 4 Acknowledges the flow of data including re-transmission where required Datagram's/Segments Gateway, Advanced cable tester brouter Network layer 3 Adds the appropriate network addresses to packets Dtagram's/Packets Brouter, router, fram relay device ATM switch, advanced cable tester Data link layer 2 Adds the MAC addresses to packets Frames Brider, switch, ISDN router, intelligent hub, NIC, advanced cable tester Physical layer 1 Transmits the data on the medium Bits Repeater, multiplexer, hubs TDR oscilioscope amplify A B C OSI_Layer LayerNum Function Application layer 7 Supports the communication between applications over the network Presentation layer 6 Presents data to the receiver in a form it recognises Session layer 5 Establishes a connection and terminates it when no longer required Transport layer 4 Acknowledges the flow of data including re-transmission where required Network layer 3 Adds the appropriate network addresses to packets Data link layer 2 Adds the MAC addresses to packets Physical layer 1 Transmits the data on the medium SUNY Broome Computer Science Department CST 119 – Computer Concepts Lab 8 SUNY Broome Computer Science Department CST 119 – Computer Concepts Lab 8 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 at · 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 LastNameFirstInitial Lab8 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. Close window Open SQL View 21. Complete the following queries and save the queries. Open a new SQL window following step 19 for each query. 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. Click the Run button 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 LastNameFirstInitial Lab8 database. CST119 Grade 100.0 Instruction Possible Points Points Lost Network Database Database named correctly 2.0 OSI_Model Table Imported from Excel: Primary key is OSI_Layer, Short Text 6.0 Additional Fields: LayerNum, Function, DataType, NetworkComponents 6.0 Protocols Table Imported from Excel: Primary key is Protocol 6.0 Additional Fields: Description, Layer 6.0 Layer field links to the OSI_Layer field of the OSI_Model table, sorted in Ascending order. 6.0 Relationships between the two tables: Enforce Ref.

Integrity and Cascade Update Related Fields 6.0 Dropdown boxes for Layer field have been correctly selected 6.0 OSI_Model Form Form created correctly, theme other than Office 6.0 Field names bold, title changed to OIS_Model Form 6.0 Columns widened 4.0 UDP protocol added 6.0 All Queries SQL written and run correctly 25.0 Saved by the proper name. 9.0 OSI Layers with Protocols Report Report based on the Protocols table, resized to fit page 4.0 Grouped by Layer, sorted by Protocol 4.0 Report heading renamed to OSI Layers with Protocols 4.0 Picture added, stretched to fit. 4.0 Theme same as OSI_Model Form 4.0 Total Points 100..0 C S T 1 1 9 - C o m p u t e r C o n c e p t s C S T 1 1 9 - C o m p u t e r C o n c e p t s C S T 1 1 9 - C o m p u t e r C o n c e p t s

Paper For Above instruction

The task involves analyzing network protocols and their corresponding OSI (Open Systems Interconnection) layers by creating a comprehensive database using Microsoft Access, importing relevant data from Excel, establishing relationships, and executing SQL queries. This process facilitates understanding the application of protocols within their appropriate layers, which is essential for network troubleshooting, designing, and management. The project begins with creating a new database named LastNameFirstInitial Lab8, importing data on OSI layers and network protocols, and establishing relationships between these tables. A crucial step involves setting up lookup fields for seamless data referencing, maintaining referential integrity, and enabling cascade updates to preserve consistent data across the database. Following this, the database user must create a form for easy data entry and review, ensuring proper formatting, theme application, and data visibility, including highlighting protocols like UDP under the Transport layer. Subsequently, SQL queries are drafted to list, filter, and sort protocols based on specific criteria such as protocol name, description, layer, and more. This includes queries that list entire tables, filter protocols in specific layers, exclude protocols based on layer criteria, and identify protocols containing particular words in their descriptions. Finally, a detailed report is generated grouping protocols by their OSI layers with relevant visual enhancements, including a header title and a inserted picture, aligned with the style of the form. The entire process underscores important database management principles such as primary key setting, relationship enforcement, query accuracy, and effective reporting, emphasizing a practical understanding of network protocols within OSI layers.

References

  • Fowler, M. (2003). Patterns of Enterprise Application Architecture. Addison-Wesley.
  • ISO/IEC 7498-1:1994. Information technology -- Open Systems Interconnection -- Basic Reference Model.
  • Tanenbaum, A. S., & Wetherall, D. J. (2011). Computer Networks. Pearson.
  • URL https://msdn.microsoft.com/en-us/library/office/ff837077.aspx
  • Oppenheimer, P. (2010). Top-Down Network Design. Cisco Press.
  • Forouzan, B. A. (2007). Data Communications and Networking. McGraw-Hill.
  • Johnson, D. & Malki, K. (2019). Practical database management in SQL. Journal of Database Administration, 30(2), 85-96.
  • IEEE Standard for Local and Metropolitan Area Networks: Overview of IEEE 802.11 (Wi-Fi) standards. (2016). IEEE Std 802.11-2016.

Related Assignments