INFM 603 - Information Technology and Organizational Context

Fall 2016 – Assignment H5

 

 

Consider the following sample entry in a flat-file database table.

            Field                                               Sample Value

               Project ID Number                                          26

               Project Name                                                   Web-based Review System Development

               Project Due Date                                             03/15/2004

               Project Supervisor First Name                      Laura

               Project Supervisor Last Name                       Reyes

               Project Supervisor Title                                 Junior Line Manager

               Project Supervisor Phone Extension            5234

               Employee 1 First Name                                  Carrie

               Employee 1 Last Name                                  Dorman

               Employee 1 Title                                             System Analyst

               Employee 1 Task in Project                           UML modeling

               Employee 1 Department Name                     Organizational Development Department

               Employee 1 Department Location                Hudson Building, 1st Floor

               Employee 1 Department Phone Extension  2597

               Employee 2 First Name                                  Mark

               Employee 2 Last Name                                  Peterson

               Employee 2 Title                                             Information Specialist

               Employee 2 Task in Project                           Database design

               Employee 2 Department Name                     Organizational Development Department

               Employee 2 Department Location                Hudson Building, 1st Floor

               Employee 2 Department Phone Extension  2597

               Employee 3 First Name                                  Alice

               Employee 3 Last Name                                  McManus

               Employee 3 Title                                             Programmer

               Employee 3 Task in Project                           Web development

               Employee 3 Department Name                     IT Support Department

               Employee 3 Department Location                Lewis Building, 3rd Floor

               Employee 3 Department Phone Extension  1523

 

Assume that one employee can work on many projects simultaneously. Also, one supervisor can supervise more than one project simultaneously.

 

1)      Normalize the table through the first, second, and third normal forms. Show all the tables for each form, identifying the primary and foreign keys (if any) of each table and the relationships between the tables in each form.

2)      Build all of your new tables in your personal database at psjconsulting.com/phpmyadmin and enter all of the data given above.  Show the SQL for a database query that retrieves from the database just the first and last names of Alice’s supervisor on any project (obviously there can be only one answer with this data since there is only one project, but the SQL query should be written so that it would work for any database and possibly with multiple projects).

 

 

 

 

 

 

 

 

 

 


Last Updated: September 26, 2016