how to Design this ERD right?

[54uydf]

I have 2 places I'm stuck on with my DB design..
1**************************************************************************
how to translate ERD that looks like this into real tables?
http://schemabank.com/sites/default/files/images/notation_self_relationship.gif
my entity is "Course", if before student wants to take some course he must take few pre-courses then how should I model this?
tableCourse- id, name
tablePreCourses- courseId, preCourseId

and the other way around, if those are the tables, is it correct to draw the ERD like in that picture? or it should be 2 entities with 1-M connection?

2******************************************************************************
so, I have Students, which my program would categorize and give them a Status (good, has a warning, expeled..etc)
every student has only 1 status at any given moment, but they change them and can return to past statuses..like- good, bad, expelled, bad (return to school), good...etc
so I want to keep the hystory of their statuses.
so I have few options here and I don't know which is the right one!!
Hystory-studId,date,status
Status-statusName
Student-Id, name..

so I can make Hystory be the weak entity to Student cuz there's no hystory without the student, but I don't know if I even need the Status table, but it's a big deal in my program, so if I want to make a report of all the expelled students I'll have to do some crazy SQL with the Hystory table to get every student's current status, so maybe I should add a "current status" field to Student..so then I DO need the Status table or not? also don't know if I should make the "status" in Hystory a foreign key, or one of the primary keys..

another idea is to make the Hystory the relation table between Student and Status, cuz every student has many statuses, every status has many students..but the thing here is that, the same student can have the same status more than once, so I'll need 1 more key in Hystory (date). if I do it like this then I can't really add the "current status" to Student, can I?

buttom line I'm stuck in a maze!! any ideas to would be appreciated!

[tharkun]

Hi,

I think you're right that the Pre-requesite is a 1 to many relationship on the Course table, but it needs to be implemented by another table as you specified:

tablePreCourses- courseId, preCourseId

As the history problem, I'd separate out the Status, as you can set up a Referential Integrity (RI) rule where the status is a foreign key to the Status table & MUST exist. The Status_History table then holds the Student_Id, Status & a column for however the history is discriminated (year, date, semester_id, etc). I would also use Status_History to hold the current status of the student too.

I hope this helps.

[54uydf]

As the history problem, I'd separate out the Status, as you can set up a Referential Integrity (RI) rule where the status is a foreign key to the Status table & MUST exist. The Status_History table then holds the Student_Id, Status & a column for however the history is discriminated (year, date, semester_id, etc). I would also use Status_History to hold the current status of the student too.

I hope this helps.

WOW I didn't think of putting a current)status in the Hystory_Status table!!! thank u for the idea..I was thinking for so long about this that I can't get out of the box anymore lol..that's a great idea..I'll add it like a bool field current (yes /no)..should work I think..

alright..so 1 last thing tht bothers me now..is the Status table..I don't have any fields besides the name there..so it seems kinda silly to make a table with 1 field and 4 elements lol..but I guess it'll make things more simple..

btw which software do u use to draw the ERD? looks simple..unlike others..

[tharkun]

I know the Status table looks simple. but you can use the table to enforce Referential Integrity [RI} (i.e. make the rule that the database will ONLY allow a Status_History row to be inserted if the status_id corresponds to an entry in the Status table). You would similarly put RI between the Status_History & the Student table, so rows can't be inserted UNLESS they correspond to an existing student.
I used a drawing package to draw the ERD (All those notations are my own).

[BitBlt]

I would add one thing to the course/pre-req table structures...a second identifying relationship between Course and Pre-req. Here's the rationale: If you think about the "pre-req" as defining a relationship between two courses, one course might have many pre-requisites, and one course might be a pre-requisite for multiple courses. So if you break it down completely, you'd have:

Course---->PreReq<-----Course

So when the primary key migrates (assuming CourseId) then the primary key of PreReq after unifying and rolenaming becomes CourseId(ParentCourse), CourseId(ChildCourse). Then when you put it back in shape you wind up with

CREATE TABLE [Course] 
(
[CourseId] [int] NOT NULL ,
CONSTRAINT [PK_Course] PRIMARY KEY  CLUSTERED 
([CourseId])
) 

CREATE TABLE [PreReq] 
(
[ParentCourseId] [int] NOT NULL ,
[ChildCourseId] [int] NOT NULL ,
CONSTRAINT [PK_PreReq] PRIMARY KEY  CLUSTERED 
([ParentCourseId], [ChildCourseId]),
CONSTRAINT [FK_PreReq_CourseChild] FOREIGN KEY 
([ChildCourseId]) REFERENCES [Course] ([CourseId]),
CONSTRAINT [FK_PreReq_CourseParent] FOREIGN KEY 
([ParentCourseId]) REFERENCES [Course] ([CourseId])
)
GO

This gives you referential integrity, enforcement of the relationship and flexibility to have any course be either role in different situations.

Again, sorry for not having a picture...if this isn't clear, let me know and I'll put together a prettier one when I get to a machine with my tools.

[54uydf]

I would add one thing to the course/pre-req table structures...a second identifying relationship between Course and Pre-req. Here's the rationale: If you think about the "pre-req" as defining a relationship between two courses, one course might have many pre-requisites, and one course might be a pre-requisite for multiple courses. So if you break it down completely, you'd have:

Course---->PreReq<-----Course

So when the primary key migrates (assuming CourseId) then the primary key of PreReq after unifying and rolenaming becomes CourseId(ParentCourse), CourseId(ChildCourse). Then when you put it back in shape you wind up with
Course PreReq
(--------) (-----------------------)
(CourseId)--+--->(CourseId(Parentcourse) )
( ) +--->(CourseId(ChildCourse) )
(--------) (-----------------------)

This gives you referential integrity, enforcement of the relationship and flexibility to have any course be either role in different situations.

Again, sorry for the ASCII model...if this isn't clear, let me know and I'll put together a prettier one when I get to a machine with my tools.

hahaha yes..the ASCII is awesome but hard to understand lol
after somethinking I came back to the decision to draw it like this http://schemabank.com/sites/default/...lationship.gif
since you are right, every course has 1 to many precourses and every course can be a precourse to 1..m courses..
so it's like M to M relationship between the same table..so I think that drawing represents it right.
what I did with the DB tables was
tblCourse: courseId, name...
tblePreCourses: courseId, preCourseId
so both of the courseId fields in tblPreCourse are foreign keys to the tblCourse.courseId

[BitBlt]

Yes, you got it exactly. Sorry it took me so long to edit/correct my post above...you beat me to it! Good job!

[54uydf]

Yes, you got it exactly. Sorry it took me so long to edit/correct my post above...you beat me to it! Good job!

BitBlt you are great!! thank you for the help!