Assignment #1     ComS 461/561      Due September 25, 2009

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 using the Entity-Relationship (ER) model for the following data and use create table to define the tables and use insert to load the data. List the entities and relationships and briefly explain why they are necessary. Sketch the ER model diagram. List the relations needed and briefly explain why they are required.
 
data:
 instructorid name  instructoradd  dept  building  courseId  courseName courseCredit
 0112  John Jones  Ames, IA ComS Atanasoff  ComS 101 Intro to computers 3
 0112  John Jones  Ames, IA ComS  Atanasoff  ComS 103 Programming II   4
 0118  Jill Jackson  Boone, IA CprE  Coover  Engg 110 Engineering I 3
 0118  Jill Jackson  Boone, IA CprE Coover  CprE 220 Computer Arch 3
 0128  Jane Johnson  Chicago, IL ComS Atanasoff  ComS 102 Programming I 3
 0136  Jim Jansen  Norman, OK CprE Coover CprE 330 Networks I  3
 0136  Jim Jansen  Norman, OK CprE Coover CprE 331 Networks 3
 
Use the resulting relations to answer the following query: You should be using the MySQL monitor to answer the query.
 
 1.List the instructor ids, instructor names, and the building they are associated with.
 2. List the instructors and the names of the courses they will teach.
 3. List the course Ids, the name of the courses, and the credit each course is worth.
 
Hand in a listing of each table and printout of both the query and the results
of the query.
 
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.