Assignment #1 ComS 561 Due February 11, 2008

A. Install MySQL on a machine outside of the department. Information on MySQL

and its installation can be found at the MySQL Site.

B. Using your newly installed MySQL create a database design in third normal 
form (3nf) for the following data and use create table to define the tables and 
use insert to load the data. The design should preserve dependencies and provide 
a lossless join.  Prove your design is going to have a lossless join.
 
Functional dependencies
 
 employeeid --> name
 employeeid --> employeeadd
 employeeid --> projectgroup
 projectgroup --> dept
 
data:
 
employeeid name employeeadd projectgroup dept
 
 0112 John Jones Ames, IA sales sales 
 0118 Jill Jackson Boone, IA design engg
 0128 Jane Johnson Chicago, IL mang mang 
 0136 Jim Jansen  Norman, OK design engg 
 
 
Use the resulting two relations to answer the following query:
 
 List the names of the employees and the dept they are associated with.
 
Hand in a listing of each table and printout of both the query and the results
of the query (see note at bottom). 
 
A written on question: if the relation scheme is R(employeeid,
name, employeeadd, projectgroup, dept), what is the key? Prove that it
is a key!

Note: If you are working on a PC you can use screen print to capture the screen, take it to Paint, cut out the query result, and invert the colors. You can print the resulting image.