Computer Science 455

Sixth Exercise Set

Due:  Friday, April 14 (or on the following Monday if Friday becomes a group work day)

Customer Name		Salesperson Name		Date
Customer #		Salesperson #		Order #
Address
Catalog #	Description	Unit Cost	Qty Ordered	Tot. Cost
C1379	Triming Tool	2.50	4	$10.00
	(etc.)
			Order Total:

Following the procedure outlined in class and in the notes, develop a relational schema from the form (including identification of keys and foreign keys). At each step, identify any violations of second and third normal form that you are trying to resolve in that step. At the final step, identify all (core) functional dependencies and verify that the resulting relations are in BCNF, and construct an entity-relational diagram.

Students at the University of Puget Sound declare at least one (but possibly more) majors and can declare one or more minor. Each major, and each minor, is administered by one department (though each department can administer more than one major and more than one minor). Each major/minor has a list of required courses that the student must take. Courses in the course bulletin give a department code for the department offering the course, a course number, and a description. For each student, we store the student number, student name, and advisor name. For each department, we store the department code, department name, the name of the department chair, and the chair's telephone number.

Using the above description, construct an entity-relationship diagram. After this is done, convert the entity-relationship diagram to a relational schema, identifying primary and foreign keys.