Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    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.

  2. #2
    Join Date
    Sep 2008
    Posts
    38
    Can anyone please tell if my model meets the requirement and how to normalise it?
    Attached Thumbnails Attached Thumbnails example 12.jpg  

  3. #3
    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...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    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!!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's right -- for the instructors table, you want only one row per instructor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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?

  8. #8
    Join Date
    Sep 2008
    Posts
    38
    Hi r937,

    So the instructor table has the Primary Key as the collection of the foreign keys?

  9. #9
    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.

  10. #10
    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?

  11. #11
    Join Date
    Sep 2008
    Posts
    38
    I went for a datamodelling training institute which teaches u basics in 40 hr training period.

  12. #12
    Join Date
    Sep 2008
    Posts
    38
    Can anyone let me know if this meets the requirements and also how to normalise?
    Attached Thumbnails Attached Thumbnails example 12.jpg  

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,923
    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

  14. #14
    Join Date
    Sep 2008
    Posts
    38
    Thanks Pat.

    But Iam unable to proceed further, can U help me please!!

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,923
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •