Project 2. Student-Classes database.

• Semester
  • can be Fall, Spring, or Summer
  • is identified by year (ex: Spring 2002)
  • has the first and last days of classes
  • has a list of courses offered (next semester)
  • has a list of section taught (current and previous semesters)
• Course
  • has an ID (ex: IST366)
  • has a title and description
  • can have prerequisites
  • has at least one section (may have several)
  • has a number of students registered
  • has a number of students currently taking this class
• Section
  • has a number, syllabus, day and time of classes, final exam day and time
  • is taught by a professor
  • is taught in a specific room
  • has a max number of students allowed
  • has a number of students taking this section
• Professor
  • has name, phone number, office number, and a webpage
  • can teach zero, one, or several sections during a semester
  • must have office hours during a semester
• Student
  • has ID, name, and GPA
  • can have a major (but not more than one)
  • has a list of already taken courses/sections (with grades or drop mark)
  • has a list of courses he/she is currently taking
  • has a list of courses he/she is registered for the next semester
  • has a list of courses he/she is yet to take to complete the major
• Major
  • has title and description
  • has a list of courses a student has to take to complete the major
• Additional requirements:
  • a student can not take more than 10 classes per semester
  • students can register only for the courses offered during next semester
  • a student is not allowed to register for a course without having grades for all prerequisites (or he/she may currently be taking them) or a permission from a professor

Assignment

Part I

1. Design a Entity-Relationship model corresponding the requirements above
2. Write relations corresponding the E-R model and all functional dependencies that reflect all relashionship specified in the description
3. Normalize all the relations. Show all your work brigning the relations into Boyce-Codd Normal form. Each time you working on decomposing a relation into the N-th normal form write the definition of the form and the functional dependencies that violates that form.

Part II

1. Create MySQL tables corresponding your final relations.
2. Insert sample data into the tables. You can use real ISt courses and professor names. Use you school mate names for students. You need to enter at least 50 records in the tables. If you made a mistake entering information use UPDATE query to modify it.