If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > General > Database Concepts & Design > Designing a normalized schedule relational database for class

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: beantown
Posts: 25
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!

Any input is really appreciated.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 38
Can anyone please tell if my model meets the requirement and how to normalise it?
Attached Thumbnails
Designing a normalized schedule relational database for class-example-12.jpg  
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: beantown
Posts: 25
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...
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
the easiest way to tell if a table design works is to create some sample rows

psss, your design has a few problems in regards to the PK

show some sample data for your instructors table, and i'll show you where the problems are
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 38
Ins_num Ins_name Class Sec Course
1 sss 100 A 10001
2 ppp 100 B 10001
3 jjjjjj 101 A 10002
1 sss 101 A 10004
2 ppp 100 A 10005

I think Instructor_number repeats and it should not be assigned PK.
Am I right!!
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
yes, that's right -- for the instructors table, you want only one row per instructor
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: beantown
Posts: 25
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?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 38
Hi r937,

So the instructor table has the Primary Key as the collection of the foreign keys?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 38
Quote:
Originally Posted by r937
the easiest way to tell if a table design works is to create some sample rows

psss, your design has a few problems in regards to the PK

show some sample data for your instructors table, and i'll show you where the problems are
I learnt this technique form above quote by r937,
or else Iam just a beginner like u, started learning data modelling a month back and Iam struggling to model the requirements properly.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: beantown
Posts: 25
are you just learning by trial and error or are you having some type of guide to help you along the way?
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 38
I went for a datamodelling training institute which teaches u basics in 40 hr training period.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 38
Can anyone let me know if this meets the requirements and also how to normalise?
Attached Thumbnails
Designing a normalized schedule relational database for class-example-12.jpg  
Reply With Quote
  #13 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,728
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.

-PatP
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 38
Thanks Pat.

But Iam unable to proceed further, can U help me please!!
Reply With Quote
  #15 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,728
Sure, I'll help.

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?

-PatP
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On