Question

An IT support organization within a large company wants to develop a relational database to keep track of its work. We will call users of the organization’s service CLIENTs. CLIENTs, as needed, submit requests for help called TICKETs. Each TICKET, in turn, can have multiple PROBLEM_ITEMs. There are many SUPPORT_SPECIALISTs in the organization. Eventually each PROBLEM_ITEM is assigned to a unique SUPPORT_SPECIALIST. Each SUPPORT_SPECIALIST can have multiple PROBLEM_ITEMs assigned to him or her. Since PROBLEM_ITEMs may not be assigned to a human SUPPORT_SPECIALIST immediately upon arrival, some PROBLEM_ITEMs can be assigned to the SUPPORT_SPECIALIST named “Unassigned.”

1.Draw an Entity-Relationship diagram for this situation. Be sure to indicate many to one relationships and one to one relationships correctly.

2.For each of the following attributes, say which entity they most logically should be associated with. Also mention if each attribute is likely to be a Key field, a Primary Key, and/or used as a Foreign Key in the table of other entity(s) to establish a relationship. If the attribute is not likely to be any of these things, mention that.

Support_Specialist_ID_Number

Text_Description_of_Problem

Specialist_First_Name

Ticket_ID_Number

Answer 1

The flow in the ER dia is as following

- Client lodged a compliant/ request ( client as entity)

- A ticket is generated with a code . ( ticket as entity/ code as action ) Problem code will work as primary key.

- The code then works as a primary key for the differentiation process. This branch will have it's end at unassigned (entity) and distribution 2 ( action ) .

- One to many relationship in distribution to specialist, and many to one relationship in specialist to resolved status.

- For specialist (entity) the problem code will work as foriegn key and the ticket would be distributed on the basis of code. (One specialist might handle various codes)

- Last entity would be resolved with ticket being closed as action.