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.
4. Database design should be visualised with MS Visio and submitted by email.

Part II

1. Create Oracle tables corresponding your final relations.
2. While creating make sure your tables sutisfy as many reqyurements as possible.
3. Please make each ID field in the table of char(..) type.
4. Specify all primary keys and foreign keys. While creating foreign keys do not forget to specify on delete constraint.
5. For each ID field create a separate sequence. Plase clearly state in comments for each table (I mean Oracle comments created with comment command) the format of the ID field. Also make sure that your sequence corresponds to the format. For example: if I state that mu SID field for Student table has format CCdddddd, where C stays for a capital character and d stays for a digit, then my sequence should return six-digit numbers only.
6. All your commands need to be stored in the project1.sql file stored in oracle subdirectory of your home directory.
7. Insert sample data into the tables. You can use real IST courses and professor names. Use you school mate names for students.