SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Normalization

    Hey guys,

    I've got two questions dealing with database normalization.

    1. Isn't SET by it's nature bad database structure?

    2. Let me give an example and then I'll ask the question. Let's say I have a structure like this (ommitted unimportant parts):

    Code:
    Students
    SID // Student ID
    // primary key is SID
    
    Classes
    CID // Class ID
    // primary key is CID
    
    S2C (Student to class relationship)
    SID
    CID
    // primary key are those two combined
    
    Grades
    SID
    CID
    Grade
    // primary key is SID and CID
    For the last two tables, should I instead use:

    Code:
    S2C (Student to class relationship)
    SCID
    SID
    CID
    // primary key is SCID
    
    Grades
    SCID
    Grade
    // Primary key is SCID
    Is there any advantage to one or the other, or what?

    aDog
    Last edited by Arielladog; Jun 11, 2001 at 10:47.

  2. #2
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why can't you just do this?

    SC2
    ---------------
    SID
    CID
    Grade

    SID & CID are primary keys.

    The only restriction is that no student can take the same class more than once. If students are allowed to repeat classed then try:

    SC2
    ----------------
    SCID
    SID
    CID
    Grade

    SCID is now primary key

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, it might depend on all the data that make up the schema. But from my way of thinking the entities and relationships you have are these.

    You have a set of values called Period. Lets say the set of values that make up Period are {semester1, semester2}. So that is definitely a Relation (ie, database table).

    You have a set of Classes. Lets say they are {English, Maths, Biology}. So they are defninitely another Relation (ie, database table).

    You have a set of Students, identified by their student ID. Another table.

    But, to my way of thinking. A student doesn't take a class. A student enrols in a class for a certain period! The student is not enrolling in Maths, but is enrolling in Maths for semester2. So I see this as a seperate entity. Lets call it ClassPeriod. This entity becomes more relevent when you consider that the schema might want to model the following.

    - Lets say you have to assign teachers to teach classes (another table Teacher). The teacher is not assigned to teaching Maths for all time, but rather is assigned to teaching Maths, semester2. They may not be rostered to teach Maths, semester1. Thus you would need to assign the Teacher as an attribute (and foreign key) in the table ClassPeriod.

    A Student receives a grade for a Class during a certain period (ie, a ClassPeriod).

    Finally, a Student will recieve a grade for a certain ClassPeriod. The grade is predetermined from a set of grades. So even this could be a table. Grades {A, B, C, F}. However, that might be considered too trivial to represent in your schema. But, technically, if a grade is required to be constrained to a set of possible grades it is part of the schema.

    So I would represent the schema like so:

    Student
    -----
    SID (PK)

    Class
    ----
    CID (PK)

    Period
    -----
    PID (PK)

    ClassPeriod
    --------
    CPID (PK)
    CID (FK)
    PID (FK)

    StudentGrade
    ---------
    SID (PK, FK)
    CPID (PK, FK)
    Grade

    Now if Grade had its own table then StudentGrade.Grade would be a foreign key to Grade.

    Whether you design the database to use composite keys or an artificial primary key is more of an implimentation issue. If you are using MySQL and therefor have to maintain referential integrity yourself in your application logic, then it is easier to use an artificial primay key. For example

    StudentGrade
    ---------
    SGID (PK)
    SID (FK)
    CPID (FK)

    But in this case SGID is a purely artificial construct and does not represent any real attribute that you are trying to model.

  4. #4
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, you are saying it doesn't really matter? Also, just FYI, here's a basic representation of the full database:

    Code:
    Students
    SID (PK)
    Name
    Address (and other info)
    
    Teachers
    TID (PK)
    Name
    Address (and other info)
    
    Courses
    CourseID (PK)
    Description
    Weight (i.e.- AP, Honors, and regular have different weights)
    CreditID (where the credit counts towards...i.e-English or Math)
    
    Credits
    CreditID (PK)
    Name
    
    Location
    LocID (PK)
    Building
    Room
    
    GradingPeriod
    GPID (PK)
    GPName
    
    Time (may be ommitted depending...some places run off of time while others run on periods.  If periods, this is included)
    TimeID (PK)
    Name (i.e.-1st period)
    Time (time period 2:00-3:00)
    GPID (grading period ID)
    
    Classes
    ClassID (PK)
    CourseID
    LocID
    TimeID
    TID
    
    Stud2Class
    SID (PK)
    CID (PK)
    
    Grades
    SID (PK)
    CID (PK)
    Grade
    And I think that's the way I'll go. Thanks,
    aDog


Bookmarks

Posting Permissions

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