SQL

Question#1

Using the IT Trouble Ticket Table shown above Identify and describe at least three of the following issues:

1. Any problems with unnecessarily repeated (i.e. redundant) data.
2. Any problems with atomicity that is columns that store more than one piece of information. For example: multiple dates, multiple names, or multiple pieces of address information.
3. Any NULL values (if any).
4. Any groupings of columns that really belong in separate tables because they really describe different entities.

NOTE: having a day, month, and year in a single column is not a problem (it is still atomic for practical purposes).

Question#2

Try to separate out all of the information stored in the above IT Trouble Ticket Table into outlines for four new tables called: Task, Location, Appointment, and Technician. For each new table include only columns from the IT Trouble Ticket Table that really belong to that new table.

For example, the new Task table should only contain columns from the IT Trouble Ticket Table that describe Task specific attributes (e.g. Description). Do not be concerned if there does not seem to be too many columns from the original IT Trouble Ticket Table that pertain to each new table.

In your listing of the new tables you do not have to list all the data inside the rows of the IT Trouble Ticket Table only the column headings. Please make sure all the columns in your new tables are split up properly so they are atomic (i.e. store only one piece of information). NOTE: it is ok to rename or split up columns from the original IT Trouble Ticket Table but you cannot just throw away any of the original columns from the IT Trouble Ticket Table!

Sample New Table Listings:

Task(Description, etc)

Location (etc.)

Appointment( etc)

Technician(etc)

NOTE:

The etc here denotes the rest of the properly associated, atomic columns that should be grouped under their correct table. You should not have the same column in more than one table!

Question#3

Identify a unique lookup on each table (it is ok to make up a new column).

IT Trouble Ticket Table description location dateRequested dateCompleted techName Fix printer TC 116 2015-03-09 2015-03-10 B.Rubble Reformat hard drive on machine 10TC 116 2015-02-20 2015-02-22 W. Flintstone B. Rubble TC 116 2015-03-01 2015-03-09 Upgrade memory on machine 12 Reinstall Java SDK on machine 12 TC 118 2015-03-09 w.Flintstone TC 118 2015-03-09 Upgrade VMWare IT Trouble Ticket Table description location dateRequested dateCompleted techName Fix printer TC 116 2015-03-09 2015-03-10 B.Rubble Reformat hard drive on machine 10TC 116 2015-02-20 2015-02-22 W. Flintstone B. Rubble TC 116 2015-03-01 2015-03-09 Upgrade memory on machine 12 Reinstall Java SDK on machine 12 TC 118 2015-03-09 w.Flintstone TC 118 2015-03-09 Upgrade VMWare