Designing a normalized schedule relational database for class
Hello folks, for this weeks class task, I have to create a database that is based on class scheduling... this is what my assignment calls for...
Turn in your tables and fields in each table for this database. Also show us what your primary keys would be.
"We should be about ready to build a relational database. Let’s see how well we can plan a normalized database. If you look in a course schedule, you will find classes, their times and the instructor. We want to create a database that will keep the courses, sections and instructors assigned to teach the classes. Let’s not worry about time. Each course may have different section and each instructor may teach different courses. Here is an example:"
Classroom - Section - Title - Instructor
Classroom 101 - 1011 - Computers - Palmer
Classroom 101 - 1022 - History - Jean
Classroom 103 - 1033 - Science - Roland
Classroom 103 - 1044 - Math - Fitz
I have all weekend for this one... I don't think it should take too long, now it's time to start reading the book!
same tables as i came up with are what you put in there... just wasn't sure about how to relate them to the other tables like you did... thanks for the help so far man, i'm just always wary about what i do on my own because i'm learning and always think im wrong...
you guys are way too advanced for me, psss, where did u learn these techniques? r937, i know ur an expert so i'm assuming you have a lot of experience with this... how would you guys suggest i learn this in detail, any books you'd recommend?
My analysis shows four entities (tables). They are classroom, course, instructor, and section. All of the entities are strong execpt for section (it is weak because it relies on all of the other entities for its natural key).
If I understand the problem correctly, you've got some work to do on your design/diagram.
First thing is that we need to be sure that I understand what you're trying to model. Let's start with the strong entities, since they don't depend on anything else to exist. My definitions are:
A classroom is a place where teaching/learning can be done. These are usually grouped into buildings, although a few classrooms may be "stand alone" and be an entire building such as a theater or an athletic venue. The natural key for a classroom is a building and an optional room number.
An instructor is a person that teaches. The natural key for an instructor is more challenging because people change names, etc. For the sake of discussion, we'll assume that whoever employs the instructors has solved this problem for us and provided some kind of an employee identifier.
A course describes a unit of subject matter to be taught. It could be a specific topic within the areas of math, literature, foreign language, accounting, engineering, etc. Different institutions use different schemes for identifying courses, but again we'll assume that the institution has given us some kind of unique identifier for each course.
A section is a specific instance of all of these. It is usually assigned a surrogate key (a section number) for ease of use by the faculty and students, but a section is really defined by a natural key which is a combination of classroom, instructor, course, and time.
There is no point in proceeding until we agree on what we are modeling. Are these definitions correct based on how you understand the problem?