Suppose that you have been asked to develop a database to facilitate the creation of access control lists for your organizations firewall Assume that managers submit all blocking requests and that each request is reviewed by a data communications specialist Your database is to keep track of managers, their requests for blocking IP addresses, and each specialist review of the request Question How many tables should we create For the managers, create a table with the following fields (note that it is not recommended to leave space between words for Access Sometimes it might be fine, like we did in the last lab But it may cause trouble when you add more complicated functionalities ) FirstName LastName Department EmailAddress Question Which field should be the primary key of this table Put some records in the table (see info from table 1 attached) For the specialists, create a table with the following fields FirstName LastName EmailAddress Specialty (long text type) Question Which field should be the primary key of this table Put some records in the table (see info from table 2 attached) For each request, create a table with the following fields RequestID (autonumber) DateOfRequest IPAddresses (to block) (Text or short text) BlockType incoming outgoing both SubmittedBy (email of manager) ReviewedBy (email of specialist) Status (Default value unreviewed, other values block, refuse, pending) Question Which field should be the primary key of this table Assume that your database is to track the name and email of the manager who made each request Assume that a manager can make many requests, but that a particular blocking request is made by one manager Finally assume that all requests are reviewed by data communications specialists After a review, the specialist grants the block, refuses the block or places the block into a pending status (waiting for more information) Your database is to track the name and email of the specialist who made the review A specialist can review many requests, but each request is reviewed by at most one specialist Question What should be the relationship among the three tables Create a data entry form to enter request Only show the fields needed to be filled by the manager Change the type of entry for BlockType and SubmittedBy Then add the following entries 1 1 2020 144 23 65 87 incoming gwashington company com 1 4 2020 144 25 68 89 both gwashington company com 2 13 2020 144 34 34 43 Outgoing bclinton company com 2 14 2020 144 23 67 33 Incoming dtrump company com 1 1 2020 144 23 33 37 Incoming bclinton company com Create a secondary data entry form to enter review by specialists (Lock the fields which should not be entered Change the entry type for Status ) Then add the following entry Fang Fang reviewed the first two requests, and decide to grant block to the first request but pending the second one Yi Sun reviewed the 4 th request, and decide to pending that request The 3 and 5 th request stays unreviewed Generate the following query tables All data for all requests sorted by request date, showing the date of request, names of the mangers who submitted requests, the IP addresses, and type of blocking, status of request, and the names of the specialists who reviewed the request All pending requests, showing the date of request, names of the mangers who submitted the requests, the IP addresses, and type of blocking, and the names of the specialists who reviewed the requests (Note the field status should be included in the table and criteria set as pending, uncheck the show box so that this field does not show in the query table ) All requests reviewed by Fang Fang, showing the date of request, names of the mangers who submitted the requests, the IP addresses, and type of blocking, and the status of request) Generate three reports, one for each query created in step F Paste the screen shots of the two forms and three reports to the submission doc Table 1 ManagerInfo Manager FirstName LastName Department EmailAddresses George Washington ProductDemo gwashington company com John Adams Sales jadams company com Barack Obama Design bobama company com Abraham Linkon Outsourcing alinkon company com Bill Clinton SupplyChain bclinton company com George Bush R D rgiuliani company com Donald Trump CustomerService dtrump company com Ronald Reagan Facility rregan company com Table 2 SpecialistInfo Specialists FirstName LastName Email Specialty Fang Fang fangfang company com MIS Bennett Cherry bcherry company com Management Yi Sun ysun company com Multimedia Kevin Hee khee company com Accounting Wayne Neu wneu company com Marketing Robert Aboolian raboolian company com Operations

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on May 31, 2024

Suppose that you have been asked to develop a database to facilitate the creation of access control lists for your organizations firewall. Assume that managers

Suppose that you have been asked to develop a database to facilitate the creation of access control lists for your organizations firewall. Assume that managers submit all blocking requests and that each request is reviewed by a data communications specialist.

Your database is to keep track of managers, their requests for blocking IP addresses, and each specialist review of the request.

Question: How many tables should we create?

For the managers, create a table with the following fields: (note that it is not recommended to leave space between words for Access. Sometimes it might be fine, like we did in the last lab. But it may cause trouble when you add more complicated functionalities.)

FirstName
LastName
Department
EmailAddress

Question: Which field should be the primary key of this table?

Put some records in the table (see info from table 1 attached)

For the specialists, create a table with the following fields:

FirstName
LastName
EmailAddress
Specialty (long text type)

Question: Which field should be the primary key of this table?

Put some records in the table (see info from table 2 attached)

For each request, create a table with the following fields:

RequestID (autonumber)
DateOfRequest
IPAddresses (to block) (Text or short text)
BlockType: incoming/outgoing/both?
SubmittedBy (email of manager)
ReviewedBy (email of specialist)
Status: (Default value: unreviewed, other values: block, refuse, pending)

Question: Which field should be the primary key of this table?

Assume that your database is to track the name and email of the manager who made each request. Assume that a manager can make many requests, but that a particular blocking request is made by one manager. Finally assume that all requests are reviewed by data communications specialists. After a review, the specialist grants the block, refuses the block or places the block into a pending status (waiting for more information). Your database is to track the name and email of the specialist who made the review. A specialist can review many requests, but each request is reviewed by at most one specialist.

Question: What should be the relationship among the three tables?

Create a data entry form to enter request: Only show the fields needed to be filled by the manager. Change the type of entry for BlockType and SubmittedBy.
1. Then add the following entries

1/1/2020	144.23.65.87	incoming	gwashington@company.com
1/4/2020	144.25.68.89	both	gwashington@company.com
2/13/2020	144.34.34.43	Outgoing	bclinton@company.com
2/14/2020	144.23.67.33	Incoming	dtrump@company.com
1/1/2020	144.23.33.37	Incoming	bclinton@company.com

Create a secondary data entry form to enter review by specialists. (Lock the fields which should not be entered. Change the entry type for Status.)
1. Then add the following entry:

Fang Fang reviewed the first two requests, and decide to grant block to the first request but pending the second one.
Yi Sun reviewed the 4^th request, and decide to pending that request
The 3 and 5^th request stays unreviewed

Generate the following query tables:

All data for all requests sorted by request date, showing the date of request, names of the mangers who submitted requests, the IP addresses, and type of blocking, status of request, and the names of the specialists who reviewed the request.
All pending requests, showing the date of request, names of the mangers who submitted the requests, the IP addresses, and type of blocking, and the names of the specialists who reviewed the requests. (Note: the field status should be included in the table and criteria set as pending, uncheck the show box so that this field does not show in the query table.)
All requests reviewed by Fang Fang, showing the date of request, names of the mangers who submitted the requests, the IP addresses, and type of blocking, and the status of request)

Generate three reports, one for each query created in step F.
Paste the screen shots of the two forms and three reports to the submission doc.

Table 1: ManagerInfo.

Manager
FirstName	LastName	Department	EmailAddresses
George	Washington	ProductDemo	gwashington@company.com
John	Adams	Sales	jadams@company.com
Barack	Obama	Design	bobama@company.com
Abraham	Linkon	Outsourcing	alinkon@company.com
Bill	Clinton	SupplyChain	bclinton@company.com
George	Bush	R&D	rgiuliani@company.com
Donald	Trump	CustomerService	dtrump@company.com
Ronald	Reagan	Facility	rregan@company.com

Table 2: SpecialistInfo

Specialists
FirstName	LastName	Email	Specialty
Fang	Fang	fangfang@company.com	MIS
Bennett	Cherry	bcherry@company.com	Management
Yi	Sun	ysun@company.com	Multimedia
Kevin	Hee	khee@company.com	Accounting
Wayne	Neu	wneu@company.com	Marketing
Robert	Aboolian	raboolian@company.com	Operations