Project 1. Student-Classes database.
In this project you need to design a relational database that contains information about
university courses, classes, students, and professors.
This database should contain the following information:
- 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 (future semesters)
- has a list of section taught (current and previous semesters)
- number of weeks in the semester
- Course
- has a title and description
- has a number of credit hours
- 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 (location)
- has a max number of students allowed
- has a number of students taking this section
- has an average grade for the course
- has a nnumber of students dropped
- 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
- has a list of courses he/she has taught
- has a list of courses he/she can teach
- Student
- has ID, name, and GPA
- can have a major (but not more than one)
- can have a minor
- 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 20 credit hours per semester
- students can register only for the courses offered during future 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
- Pretend you are keeping all data in one huge relation. Write down all functional
dependencies for the attributes of this relation.
- Normalize the relation. Show all your work brigning the relation
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.
- Design a Entity-Relationship model corresponding the requirements above
- Database design should be visualised with MS Visio.
Your project paper should include:
- all functional dependencies and your assumptions based on which you believe these
dependencies are correct
- all the steps of the normalization process you performed to decompose the initial
relation into the Boyce-Codd Normal Form.
- Definitions of the all Normal Forms and violators of this forms for each relation
you are about to modify.
- Final E-R model in MS Visio.
-