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. o Support_Specialist_ID_Number o Text_Description_of_Problem o Specialist First_Name o 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.

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. Crow s foot zero or one one and only one zero or many one or many

We have used Crows Foot notation: