Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2003
    Posts
    7

    Exclamation Database Design - please help

    Hello all,

    I have no experience in databse design and I would need some help and advice.
    Attached is a GIF file that shows a screen capture from ERWin 3.5.2 which I'm using to design a database for keeping track of courses and students registration for the courses (each course can be scheduled few times a year, a student can attend the course only once). I don't know if the design is correct; I would really appreciate your adivce.
    As you can see, some one-to-many relationships I draw already. I was trying to create also a one-to-many relationship between ScheduledCourses and RegisteredStudents but then ERWin is inserting the LectorID field from ScheduledCourses as a FK in RegisteredStudents table; I'm sure ERWin knows more than I do so my question is what am I doing wrong?

    Is it possible that I have in one table a primary key composed from two columns which are primary key columns in two other different tables ? See RegisteredStudents table for example (I design it to have a primary key composed from StudentID and ScheduledCourseID which are primary keys in the Students and ScheduledCourses table).

    TIA,
    Pat
    Attached Thumbnails Attached Thumbnails course database schema.gif  
    Last edited by patm; 04-18-03 at 10:50.

  2. #2
    Join Date
    Apr 2003
    Posts
    13

    Re: Database Design - please help

    Ok,

    THis is a fast take, so take with a grain of salt.

    The course table should have only a single column primary key of COURSEID. This key should uniquely identify each row in the table.

    You will need a many to many relation between course and student. Ie, a student can take many courses and a course can be taken by many students. This will requiire a resolution table between course and student called COURSE_STUDENT, with a composite PK of courseid & studentid.

    The student entity should have PK of studentID.

    HTH,
    Jeff

  3. #3
    Join Date
    Apr 2003
    Posts
    7

    Re: Database Design - please help

    Originally posted by jlarimore
    Ok,

    THis is a fast take, so take with a grain of salt.

    The course table should have only a single column primary key of COURSEID. This key should uniquely identify each row in the table.

    You will need a many to many relation between course and student. Ie, a student can take many courses and a course can be taken by many students. This will requiire a resolution table between course and student called COURSE_STUDENT, with a composite PK of courseid & studentid.

    The student entity should have PK of studentID.

    HTH,
    Jeff
    The design I presented in the GIF file is already implemented your sugestions. Am I missing something ?
    Pat

  4. #4
    Join Date
    Apr 2003
    Posts
    13

    Re: Database Design - please help

    Nope, I missed it.


    Ok, The primary key for ScheduledCourses should be either, the surrogate key, ScheduledCourseID, or the composite key of CourseID&LectureID, but not both. You can move the courseID and LectureID to be attriutes of ScheduledCourses rather than primary keys.

    The RegisteredStudents table should use the primary key designed above.

    How bout now?

  5. #5
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Thumbs up Re: Database Design - please help

    Quote: "See RegisteredStudents table for example (I design it to have a primary key composed from StudentID and ScheduledCourseID which are primary keys in the Students and ScheduledCourses table)."

    I dont't see that ScheduledCourses table has ScheduledCourseID as its promarykey. I see that ScheduledCourses primarykey is composed of: ScheduledCourseID, LectorId and CourseId. If you want to make a one-to-many relationship with table RegisteredStudents than the primary key of table ScheduledCourses has to be a FK in table RegisteredStudents. So you have to have these fields in RegisteredStudents table: ScheduledCourseID, LectorId and CourseId, but you only have ScheduledCourseID. You also need LectorId (you said that it's automaticaly added) and CourseId (you didn't said but I think is't also added automaticly in RegisteredStudents table, as well, isn't it?)

    ionut

  6. #6
    Join Date
    Apr 2003
    Posts
    7

    Re: Database Design - please help

    Originally posted by jlarimore
    Nope, I missed it.


    Ok, The primary key for ScheduledCourses should be either, the surrogate key, ScheduledCourseID, or the composite key of CourseID&LectureID, but not both. You can move the courseID and LectureID to be attriutes of ScheduledCourses rather than primary keys.

    The RegisteredStudents table should use the primary key designed above.

    How bout now?
    Thanks Jeff...
    This is the implementation I was thinking of, this time in SQL Server. How does it looks ?
    Pat
    Attached Thumbnails Attached Thumbnails course registration db diagram sql server.gif  

  7. #7
    Join Date
    Apr 2003
    Posts
    13

    Re: Database Design - please help

    Looks real good...

    Jeff

  8. #8
    Join Date
    Apr 2003
    Posts
    7

    Re: Database Design - please help

    Originally posted by ionut calin
    Quote: "See RegisteredStudents table for example (I design it to have a primary key composed from StudentID and ScheduledCourseID which are primary keys in the Students and ScheduledCourses table)."

    I dont't see that ScheduledCourses table has ScheduledCourseID as its promarykey. I see that ScheduledCourses primarykey is composed of: ScheduledCourseID, LectorId and CourseId. If you want to make a one-to-many relationship with table RegisteredStudents than the primary key of table ScheduledCourses has to be a FK in table RegisteredStudents. So you have to have these fields in RegisteredStudents table: ScheduledCourseID, LectorId and CourseId, but you only have ScheduledCourseID. You also need LectorId (you said that it's automaticaly added) and CourseId (you didn't said but I think is't also added automaticly in RegisteredStudents table, as well, isn't it?)

    ionut
    Thanks Ionut...
    Actually, ScheduledCourses table has ScheduledCourseID as its only primary key; what are you seeing in the first picture is ERWin's way to present table relations (notice the FK beside CourseID and LectorID - it was ERWin that put them there, not me). What about my last posting with the picture from SQL Server ? It looks OK to me, but I would like to hear some opinions.
    Pat

  9. #9
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    The SQL diagram looks great to me.

    a student can attend the course only once
    Does this need to be enforced by the database? If so, you'll want to add courseID to RegisteredStudents. You could make it part of the primary key, or just create a unique index on it. You should also make it a foreign key pointing to Courses.
    Bradley

  10. #10
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    The diagram looks good, but you have to pay attention to some "little" problems, like:

    - as bcrockett said, a student can attend a course only once. This means the combination StudentId,CourseId has to be unique. You don't have this combination in one table in order to create an unique constraint index. So you have two choices:

    1) Create an trigger in table RegisteredStudents (for INSERTS and UPDATES) like:

    if exists
    (select * from
    (select i.StudentId,s.CourseId from inserted i join ScheduledCourse s
    on i.ScheduledCourse=s.ScheduledCourse) T1
    (select r.StudentId,s.CourseId from RegisteredStudents r join
    ScheduledCourse s on r.ScheduledCourse=s.ScheduledCourse where not exists (select * from inserted i where i.StudentId=r.StudentId and i.ScheduledCourseId=r.ScheduledCourseId) T2
    on T1.StudentId=T2.StudentId and T1.CourseId=T2.CourseId)
    BEGIN
    ROLLBACK TRAN
    END


    2) Modify tables design, so that you put CourseId field in table RegisteredStudents. You can simply add field CourseId in the table, but now your problem is to give it values automatically. Again the solution is a trigger for inserts and updates:

    update RegisteredStudents set RegisteredStudents.CourseId=A.CourseId
    from RegisteredStudents join (select i.StudentId,i.ScheduledCourseId,s.CourseId from inserted i join ScheduledCourse s on i.ScheduledCourseId=s.ScheduledCourseId) A
    on RegisteredStudents.ScheduledCourseId=A.ScheduledCo urseId

    Good luck
    ionut


    PS

    When I posted this replay I didn't have access to a SQLServer machine, so I couldn't test the above sql statements. So it is possible that some errors occured. If so, and you can not debug them, tell me and I will try to figure out what the problems are.

  11. #11
    Join Date
    Apr 2003
    Posts
    7
    Thanks a lot Ionut,

    I tried to create the trigger as you suggest but I get an error. See the picture below.
    Pat
    Attached Thumbnails Attached Thumbnails sql server trigger error.png  

  12. #12
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    I've missed an JOIN, see bellow:
    if exists
    (select * from
    (select i.StudentId,s.CourseId from inserted i join ScheduledCourse s
    on i.ScheduledCourse=s.ScheduledCourse) T1 JOIN
    (select r.StudentId,s.CourseId from RegisteredStudents r join
    ScheduledCourse s on r.ScheduledCourse=s.ScheduledCourse where not exists (select * from inserted i where i.StudentId=r.StudentId and i.ScheduledCourseId=r.ScheduledCourseId) T2
    on T1.StudentId=T2.StudentId and T1.CourseId=T2.CourseId)
    BEGIN
    ROLLBACK TRAN
    END

  13. #13
    Join Date
    Apr 2003
    Posts
    7
    Fast answer, thanks ..
    Sysntax check is OK now. A closing bracket was missing also prior to T2 but I fix it.
    Can you please tell me what exactly are you verifying with this :
    i.ScheduledCourse=s.ScheduledCourse ? Equality between tables ?
    TIA,
    Pat

  14. #14
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    That's another problem that I've missed. The correct form was i.ScheduledCourseId=s.ScheduledCourseId

Posting Permissions

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