Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    2

    Db Design and Table Relationship

    I am trying to accomplish the following:

    I am trying to design a database for the computer dept for a college. I am designing the database so that it could be used by an administrator in the computer department for the college. I would like the administrator to be able to find out how many students are registered for credit as well as non-credit courses. If a student is registered for a non-credit courses if it is for a certificate how many classes for the certificate has the person completed (when the person has completed all the classes a certificate would be mailed to them by the department) and did the student pass or fail the class. The administrator should also have the cability to bring up all students that have completed a course and what there grades were (especially important if the students are taking classes for credit. Another function of the database would be to find out what instructor is teaching what class and what student is enrolled in what classes. The administrator should be able to tell by the courseid if a class is being taught on a MAC or a PC and whether the class is being taught online or in person. I just need some input if the tables I have created are sufficient and should I add more fields and what the relationship should be between the tables. Below is the tables I have created so far (I have no idea how or why I set up the lab table and how I can use it). It was suggested that I should set up a table for historical information about courses and a table that has information for only one course but I don't know how I should set this up. Any suggestions any table(s) that can be addes as well as how to better set up table relationship is appreciated.

    Student table
    StudentID (int) primary key not null
    SFirstName (nvarchar)
    SLastName (nvarchar)
    SContactNumber (varchar)
    SAddress (nvarchar)
    SCity (nvarchar)
    SState (varchar)
    SZipCode (varchar)

    Coursehistory table
    CourseID (int) primary key not null
    CourseTitle (nvarchar)
    StartDate (datetime)
    EndDate (datetime)
    DateWkHeld (datetime)
    LabLocationID (nvarchar) foreign key
    Credits (smallint)

    CourseUnique table?

    Enrollment Table
    EnrollmentID (int) prmary key not null
    StudentID (int) foreign key not null
    InstructorID (int) foreign key not null
    CourseID (int) foreign key not null
    EnrollmentDate (datetime)
    NumGrade (smallint)
    Pass/Fail (bit)
    CourseLoad(FT/PT) (bit)
    TypeClassID (int) key would have one of 2 values: 1 or 2 1=MAC 2=PC)
    LabLocationID foreign key
    OnlineClass/InPerson (bit)
    CreditClass/NonCredit (bit)


    Instructor table
    InstructorID (int) primary key not null
    IFirstName (nvarchar)
    ILastName (nvarchar)
    IContactNumber (varchar)
    IAddress (nvarchar)
    ICity (nvarchar)
    IState (varchar)
    IZipCode (varchar)


    Lab table
    LabLocationID (nvarchar) primary key
    CourseID (int) foreign key

  2. #2
    Join Date
    May 2003
    Posts
    2

    Re: Db Design and Table Relationship

    I have made some changes to the tables. I was wondering if having a CourseHistory tables that holds present and past classes would causes a performance problem. Any input as to tables to add or how to build relationship between tables would be appreciated.

    Student table
    StudentID (int) primary key not null
    SFirstName (nvarchar)
    SLastName (nvarchar)
    SContactNumber (varchar)
    SAddress (nvarchar)
    SCity (nvarchar)
    SState (varchar)
    SZipCode (varchar)

    Course table
    CourseID (int) primary key not null
    CourseTitle (nvarchar)
    StartDate (datetime)
    EndDate (datetime)
    DateWkHeld (datetime)
    LabNumber (smallint) null (online courses have no labnumber)
    CreditValue (smallint)
    CourseDuration
    CourseFee

    CourseHistory table
    CourseHistoryID (int) primary key not null
    CourseID (int) foreign ksey
    StartDate (datetime)
    EndDate (datetime)
    DateWkHeld (datetime)
    LabNumber (smallint) null where should this be?
    CourseFee denomalization

    Enrollment Table
    EnrollmentID (int) prmary key not null
    StudentID (int) foreign key not null
    CourseHistoryID (int) foreign key not null
    Instrutor D (int) foreign key
    EnrollmentDate (datetime)
    NumGrade (smallint)
    Pass/Fail (bit)
    CourseLoad(FT/PT) (bit)
    TypeClassID (int) key would have one of 2 values: 1 or 2 1=MAC 2=PC)
    LabNumber (smallint) foreign key
    OnlineClass/InPerson (bit)
    CreditClass/NonCreditClass (bit)
    CourseFeePayDate

    Instructor table
    InstructorID (int) primary key not null
    EnrollmentID (int) foreign key
    IFirstName (nvarchar)
    ILastName (nvarchar)
    IContactNumber (varchar)
    IAddress (nvarchar)
    ICity (nvarchar)
    IState (varchar)
    IZipCode (varchar)


    Originally posted by bahmaine
    I am trying to accomplish the following:

    I am trying to design a database for the computer dept for a college. I am designing the database so that it could be used by an administrator in the computer department for the college. I would like the administrator to be able to find out how many students are registered for credit as well as non-credit courses. If a student is registered for a non-credit courses if it is for a certificate how many classes for the certificate has the person completed (when the person has completed all the classes a certificate would be mailed to them by the department) and did the student pass or fail the class. The administrator should also have the cability to bring up all students that have completed a course and what there grades were (especially important if the students are taking classes for credit. Another function of the database would be to find out what instructor is teaching what class and what student is enrolled in what classes. The administrator should be able to tell by the courseid if a class is being taught on a MAC or a PC and whether the class is being taught online or in person. I just need some input if the tables I have created are sufficient and should I add more fields and what the relationship should be between the tables. Below is the tables I have created so far (I have no idea how or why I set up the lab table and how I can use it). It was suggested that I should set up a table for historical information about courses and a table that has information for only one course but I don't know how I should set this up. Any suggestions any table(s) that can be addes as well as how to better set up table relationship is appreciated.

    Student table
    StudentID (int) primary key not null
    SFirstName (nvarchar)
    SLastName (nvarchar)
    SContactNumber (varchar)
    SAddress (nvarchar)
    SCity (nvarchar)
    SState (varchar)
    SZipCode (varchar)

    Coursehistory table
    CourseID (int) primary key not null
    CourseTitle (nvarchar)
    StartDate (datetime)
    EndDate (datetime)
    DateWkHeld (datetime)
    LabLocationID (nvarchar) foreign key
    Credits (smallint)

    CourseUnique table?

    Enrollment Table
    EnrollmentID (int) prmary key not null
    StudentID (int) foreign key not null
    InstructorID (int) foreign key not null
    CourseID (int) foreign key not null
    EnrollmentDate (datetime)
    NumGrade (smallint)
    Pass/Fail (bit)
    CourseLoad(FT/PT) (bit)
    TypeClassID (int) key would have one of 2 values: 1 or 2 1=MAC 2=PC)
    LabLocationID foreign key
    OnlineClass/InPerson (bit)
    CreditClass/NonCredit (bit)


    Instructor table
    InstructorID (int) primary key not null
    IFirstName (nvarchar)
    ILastName (nvarchar)
    IContactNumber (varchar)
    IAddress (nvarchar)
    ICity (nvarchar)
    IState (varchar)
    IZipCode (varchar)


    Lab table
    LabLocationID (nvarchar) primary key
    CourseID (int) foreign key

Posting Permissions

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