Need Feedback on this ER Diagram

[GeForceX]

I've been going in circles with this diagram for my project.

I'm implementing a rating systems where deaf students can rate the interpreters they've had in school. Similar to RateMyProfessors, students will be able to view ratings of all interpreters, search for their names, rate them in a list of questions, and et cetera.

Some rules:

A student can rate many interpreters, only those that they've had.
An interpreter can be rated by many students, only those that they've interpreted for.

However, to limit the ability for students to rate interpreters that they've had, we must incorporate the course section that both have participated in. So making some necessary changes:

A student can take many courses and a course can be taken by many students. Thus, an enrollment table.
An interpreter can interpret for 0 or more courses and a course can be interpreted by one or many interpreters. Thus, an assignment table.
And finally, a course section will need a term and course title to determine the specific course section that both the student(s) and interpreter(s) are in.

Does it make any sense to you guys or no?

Criticize the crap out of this diagram, if possible.

Edit: Sometimes this ER Diagram doesn't make sense to me. :/

 

[enlightenedby42]

I'm a smidge rusty on my diagrams, but overall I don't see any major shortcomings in your design and it's obvious you've considered your requirements thoroughly.

If this is going to be used in production stuff, you might break addresses out into a separate table to allow students and/or interpreters to have more than one address (unless you know this won't be an issue for you, then who cares). You might also put hearing level (and I don't know much about what this represents) as well as things like departments and majors into their own tables and then use foreign keys to represent them where you actually use them. There are probably a few other things that could potentially be off in lookup tables for similar reasons.

What if you want to do a "select * from students where major = 'foo'", but somebody spelled the criteria you're looking (busines vs business or something) for wrong so you miss a record? Doesn't happen if you give limited options and then use an int key, but then you have to restrict data entry somewhat.

Overall, I've seen WAY worse databases. You're definitely moving in the right direction IMO, speaking as a bit of a novice myself compared to some of the folks here.

 

[Pwyl_The_Destroyer]

Why do you have day and time in Course instead of Course Section?

(assuming a course would be something like Biology 101, which could be taught multiple times, and by different profs)

 

[jiminator]

Depending on the way you use the table that is A LOT of personal information that you are collecting. I'd drop that except what you need for your purposes. You also may have scenarios where there is activity that happens outside of your diagram. I'd consider allowing for this, however reducing the weighting it carries (ie: 25% vs 75% normal). That might allow your system to get more utilization, however with a risk of bias.

 

[GeForceX]

@enlightenedby42 Thanks for your thoughtful responses.

Students will have only one school address, at least for the purpose of this project.

Hearing level is for statistical purposes. Administrators will be able to see if there is any correlation between the level of ratings to the hearing level of the student. I did a little research of a hearing classification scale and it turns out to be this:

Normal hearing = -10 to 15 dB
Slight hearing loss = 16 to 25 dB
Mild hearing loss = 26 to 40 dB
Moderate hearing loss = 41 to 55 dB
Moderately severe loss = 56 to 70 dB
Severe hearing loss = 71 to 90 dB
Profound hearing loss = 91 to 120 dB
Deaf = +120 dB

You are correct that I should put major, department, hearing level, and such in their own tables. I feel it would be additional work but I will do that. I have modified the current version with additional tables.

I will be restricting data entry by utilizing Oracle forms. I will be designing the entire thing so I will make absolute sure that user error would be limited.

@Pwyl_The_Destroyer You are very correct. I just made a blunder. Thank you for pointing that one out! I moved the day and time to the course section table. And with that, I decided to change the naming of the course section table to rating. I do not know if this is appropriate naming but... I am open to other names, if anything.

@jiminator I definitely understand what you are saying. I should have mentioned this clearly: this is not an actual project expected to be implemented. This is just a database development class project, haha. If it were an actual project where we were expected to implement this, yes, we would definitely use outside systems to gather data such as student information. For the purposes of this project, three main groups of people will have access to this system: students, interpreters, and administrators. We know what the roles are of students and interpreters. However, for administrators, they want to gather as much information they can get from the system. Administrators are those who hire, assign, and assess interpreters. They would need all such statistical data to help them do that. This is why I added all of these attributes.

And so... here is the modified version according to the feedback gathered:

One area of concern is where I highlighted in red:

An interpreter has a specialty where he/she works under only one college (e.g. College of Science, College of Business, etc...). And of course, a college can have many interpreters. And finally, courses belong to one college whereas one college has many courses.

I wanted to use a simple College table where both interpreters and courses can have their identifier (clg_id). But I do not know if this is appropriate database design.

The reason why I consider this to be critical is because I want the user to have the ability to search an interpreter by the college they work in.

Any feedback in this area?

Thanks a lot, your feedback does help!

 

[GeForceX]

Or to make it simple... just do it this way:

Just limiting the concern of colleges to just the interpreter.

 

[GeForceX]

Any feedback? Would appreciate it. The college part is the only thing I'm stuck on.

 

[Sgraffite]

How are the interpreters assigned out? Are they connected to specific courses in any way or can any interpreter be chosen for any course?

What exactly is a course section? A single instance of a course in a specific time slot?

 

[GeForceX]

@Sgraffite

Interpreters have a specialized field they focus in. They are assigned to the courses that are under the college that they specialize in. For example, an interpreter may have background knowledge of science so they interpret for students attending courses in the College of Science. So they are not necessarily connected to specific courses.

An actual example: an interpreter is assigned to the College of Computing and Information Sciences (CCIS). There is a course named called Introduction to Programming which is available under the CCIS. The administrators try to find all the interpreters that are under the CCIS. Then the administrator assigns one or two of the interpreters to Introduction to Programming.

Course section is commonly used to specify the professor, time, date, and location of a course subject. Just as the other poster here explained, Biology 101 can be taught many times but in different locations, times, days, and with different professors. There is a need to specify which professor, time, and date you'd like to attend in such a class.

 

[GeForceX]

Separated Course Section from Ratings. Now it's better. All good?