MIS 2

Management Information Systems

Reporting the Access Control List.

Suppose that you have been asked to develop a database to facilitate the creation of access control lists for your organization’s 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?

A) 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)

B) 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)

C) 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?

D) 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.

a. Then add the following entries

1/1/2020	144.23.65.87	incoming	[email protected]
1/4/2020	144.25.68.89	both	[email protected]
2/13/2020	144.34.34.43	Outgoing	[email protected]
2/14/2020	144.23.67.33	Incoming	[email protected]
1/1/2020	144.23.33.37	Incoming	[email protected]

E) 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.)

a. 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 4th request, and decide to pending that request

· The 3 and 5th request stays unreviewed

F) 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)

G) Generate three reports, one for each query created in step F.

H) 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	[email protected]
John	Adams	Sales	[email protected]
Barack	Obama	Design	[email protected]
Abraham	Linkon	Outsourcing	[email protected]
Bill	Clinton	SupplyChain	[email protected]
George	Bush	R&D	[email protected]
Donald	Trump	CustomerService	[email protected]
Ronald	Reagan	Facility	[email protected]

Table 2: SpecialistInfo

Specialists
FirstName	LastName	Email	Specialty
Fang	Fang	[email protected]	MIS
Bennett	Cherry	[email protected]	Management
Yi	Sun	[email protected]	Multimedia
Kevin	Hee	[email protected]	Accounting
Wayne	Neu	[email protected]	Marketing
Robert	Aboolian	[email protected]	Operations