problem with entity relationship diagrams....

[nikeloeleven]

Hi there ,
I have 3 tables
"PUPIL"
"SUBJECT TARGET"
"REVIEW"

Basically, each pupil has a target that he sets himself/herself.
Those targets are reviewed, hence the 3 tables.

Here is my entity relationship diagram to show how they are linked:

PUPIL -------- 1 to many ------------ SUBJECT TARGET ---------1 to many -------- REVIEW


PUPIL has a key field called "Admission Number". It is posted to SUBJECT TARGET as a foreign key....

REVIEW also needs the "Admission Number" field but it's not actually linked to "PUPIL" so I can't figure out a way for this to be possible...
Can I just post Admission Number from SUBJECT TARGET to REVIEW even though I know that "Admission Number" is just a foreign key in SUBJECT TARGET...?
Please help me on how to allow REVIEW to have "Admission Number" field aswell please.... thanks so much.
I'd be greatful for replies as soon as possiblebecause I've been stuck on this for so long now.. Thanks a lot

 

[lespaul]

Why do you think that review needs Admission Number?

A pupil creates Subject Targets the Targets are reviewed, if you need to get the pupil information then you can just join into the pupil table through the subject targets table.

Is Admission Number the PK of Pupil? What's the PK of the other tables? Could you describe them in a little more detail?

leslie

 

[nikeloeleven]

is there a way to post images... i have an image of the whole Entity Relationship diagram with all the tables etc...

 

[nikeloeleven]

and yes... Admission Number is the primary key of pupil...

and don't Review need "Admission Number" so that u know which review corresponds to which pupil... otherwise we'd have loads of reviews but wouldn't know which review is meant for which pupil... therefore, i thought adding the "admission number" field would be a good way of knowing who the review belongs to

 

[lespaul]

don't you review the subject targets which contains the admission id?

you may want to review The Fundamentals of Relational Database Design

say you have:

Pupils
Admission ID (PK)
other fields

Subject Target
Subject Target ID(PK)
Admission ID (FK)

Reviews
Review ID (PK)
Subject Target ID (FK)

Now you just use joins in the query:

SELECT *
FROM Reviews
INNER JOIN [Subject Target] on Reviews.[Subject Target ID] = [Subject Target].[Subject Target ID]
INNER JOIN Pupils on [Subject Target].[Admission ID] = Pupils.[Admission ID]


Leslie

 

[nikeloeleven]

thanks leslie... the problem would be solved if i used a subject target id for the SUBJECT TARGET field as u said .... but i already have 2 fields that are joint keys for the SUBJECT TARGET field.... they are "ADMISSION NUMBER" and "TARGET NUMBER"....

so if i used a subject target id then what do i do with them 2

 

[lespaul]

I was just using Subject Target ID as an example, you can have whatever keys you want.

Do you have the target number in the review table?

Leslie

 

[willir]

nikeloeleven

I will take a backseat on this one. Your design makes a lot of sense.

Pupil -> several objectives (targets)
Each objective / target -> several reviews

You may want to look at renaming your table and field names. Spaces and special characters can make the SQL statements and coding a real pain later...
Avoid space characters in any 'Name' - Why ?

nikeloeleven, you are in good hands with Leslie...

 

[ceh4702]

What happend if a student applies one year is turned down and then applies again and is approved? The student then may have multiple applications. Application number does not describe the student it describes the application. Might want to try a 3 way join table that joins 3 tables.

Stud_ID, Application_Nbr, REview/er.

I didnt quite get about the subject being reviewed. Is that the program they are in or is that for each class they take? Is this for Grade School, High School, or Higher Education.

Here are some fine points. Normally a Class has a key 0f Year, semester, subject_area, course_nbr, Section_Nbr,Student_ID. A student record norally contains just one key for a student ID.

Courses are taught. Each course has a class called a section. The class has a record for each student to store the grade for each student in the class.

Students may be enrolled in Degree Programs. Degree programs have degree specific courese, general education courses in various areas and electives. Degree programs must be approved by the state.

In a grade school or a high school there may be differences in what makes up a class and how subjects are taught. I work in IT at a community college and have been doing this for a while. sometimes instructors purposely simplify things to make it easier.

If you do not like my post feel free to point out your opinion or my errors.