Can you please check my ER Diagram?

[scottyscotty19]

Hi, I have produced an er diagram for an assignment which I have attached. Can somebody please tell me if it is correct and if not can you please let me know where I went wrong

Thanks

[timothybard]

Your current ER Diagram is a good start, but let me ask you some questions that may make you want to change your diagram a little:

1) How can you track if a student wants to take a module that is not within his course? ("a student may take one module at each level from outside their course")

2) How do you track which facilities are needed for each module? I see your diagram supports modules using facilities; however, how do you know if the facilities assigned to the module meet the needs for the module?

3) Can you explain the purpose of the studies entity? The reason I ask is because I was wondering how you are tracking which course each module belongs in.

4) I would recommend tracking credits as an attribute of one of the existing entities. can you identify the entity I would add it to?

5) Instead of having tutors directly joined to modules, I would recommend having a different entity directly joined to modules. Do you know which entity it is?

[scottyscotty19]

Thanks for your advice REALLY APPRECIATED

How can you track if a student wants to take a module that is not within his course?
Can you explain the purpose of the studies entity?

I thought that the studies entity would cover this I did need to name it better. Or Am I wrong?

How do you track which facilities are needed for each module?

I dont have a clue I have tired to include this in it just doesn't seem to work

I would recommend tracking credits as an attribute of one of the existing entities. can you identify the entity I would add it to?

I could put them on the Academic record

Instead of having tutors directly joined to modules, I would recommend having a different entity directly joined to modules. Do you know which entity it is?

A bit confused with this it looked right to me and everything else I try seems wrong. I now know that it is wrong I just cant figure out what entity I can use. Unless I put department then tutors.

When I was given this assignment I was told that there is around 18 entities can you see any that I might need that I have missed?

Thanks again for replying and taking some time to understand my problem

[timothybard]

1) The way you have your ER Diagram drawn, each studies record will have a course foreign key. From the course entity, you can find out which student is associated with the studies record. In other word, to get from the studies entity to the student entity, you have to go through the course entity. The problem with this is that if the student is taking a module that is not within his course, then there is no course record to go from the studies entity to the student entity.

2) To track the facilities needed for each module, there will need to be a many-to-many relationship from the module entity to the facilities entity.

3) I was thinking that credits can be recorded as an attribute in the studies entity. Since each module the student passes counts as one credit and since the studies entity tracks which modules the student is taking, I would add an attribute in the studies entity that tracks whether the student has passed the module or not.

4) My thinking with this point is that each module contains lectures and each lecture is taught by a tutor. Therefore, I would remove the relationship between the tutor and the module and replace it with a relationship between the module and the lecture. With the way you have things set up, I think there will be an issue if the tutor for a module changes sometime during the module.

Personally, 18 entities seems high; however, I will have to review the requirements again to see how many entities I would use. I'll have to get back to you on that.

[scottyscotty19]

I was told that if I had a many to many relationship then I had to create a new table is this not true?. I have attached a document that shows the format of the assignment.

Thanks again.

[scottyscotty19]

I see what you are saying now I have modules one to many to facilities and facilities many to one to room.

I still cant find any more entities that I need, if anybody sees any that I have missed, I would appreciate it if you could let me know.

Thanks

[scottyscotty19]

I have tried to do what you suggested timothybard my new er diagram is attached what do you think?

I wasn't sure if I should have the student entity to the modules entity 1 to many or student to studies 1 to many.

[timothybard]

What attributes will be in the academic record entity? Since you will have a credits earned attribute in the studies entity, I was thinking you could get rid of the academic records entity.

Secondly, I'm not sure what the head of office entity is for. My thought would be that the head of office would be an attribute in the department entity.

Also, instead of joining the room to the tutors entity, I would have it joined to the lectures entity because the lecture will be in a specific room.

Lastly, you need to review your relationships. There are a few that are going in the reverse direction and there are a few that need to be many-to-many relationships. If you identify the necessary many-to-many relationships, you may get to the 18 table count you need.

[scottyscotty19]

I will drop the academic table and the head of office.

Thanks

Lastly, you need to review your relationships.

I am having a massive problem finding theses many to many relationships, I have done nothing but review them and I cant seem to get them correct.

Are you saying that I shouldn't have a student and module link because that's going in reverse order? what about the module and facilities link should I drop that aswell?

[timothybard]

Okay, I read the original assignment and drew an ER Diagram based on the assignment. After drawing the ER Diagram, I counted the number of tables required and came up with 18 tables.

There are some key items that I had forgotten about and that are not included in your diagram:
1) Modules can be split up into sessions
2) Some lecture sessions can be associated with practical sessions
3) Some modules have prerequisite modules.
4) Modules are given either during the first semester, second semester or both.

Since the assignment does not mention departments or head of departments, I would leave those items out.

[scottyscotty19]

Each member of staff is only able to teach certain modules or even certain sessions of certain modules. Members of staff have line-mangers who report to the head of department

For some reason this was missing from the original I don't know why. Only just noticed that's the only thing missing.

[scottyscotty19]

I tired to take on board everything that you said and this is what I come up with.

[timothybard]

What is the difference between the lecture entity and the session entity; isn't a lecture just a type of session? Same with practical session and session.

I see you removed the relationship between student and module. What was your reason for removing it?

You have the lectures entity joined to the semester entity; for my diagram, I was assuming that each session/lecture was the same regardless of the semester it was in.

Is the head of the department one of the tutors? If so, you need a relationship from the head of department to directly to the tutors entity.

I would also recommend looking at the direction of some of your relationships again.

Lastly, you need a way to track prerequisites and the level of each course and module.

[scottyscotty19]

I am so confused now I don’t know why I removed it I guess I just kind of panicked and thought I didn’t need it thanks for pointing it out it will go back in.

It doesn’t state that a tutor is the head of department but it would make sense so I will add a link from head of dept. to tutors.

The directions of some of relationships I find really hard to figure out I have changed a few entities around but it again doesn’t seem right.

What is the difference between the lecture entity and the session entity; isn't a lecture just a type of session?

Again you are right I don’t need them I don’t have a clue why I thought I did I guess it’s because the deadline is approaching and I am struggling to get 18 tables. They will go. Thanks

[scottyscotty19]

I started from scratch and listed the basic tables that I could find. I then decided that between all the tables there are many to many relationships so I did this:

student
student/course
Course
Course/module
Module
module/staff
staff
staff/lecture
lecture
lecture/practicalsession
practicalsession
practical/room
room/facilities
facilities
facilities/module

It makes sense to me because I have broke up all the many to many relationships what do you think?

[scottyscotty19]

I give up thanks for your time timothybard your advice was really good I just struggled to get to terms with database design.

[timothybard]

You still need a Student/Module table and tables to deal with the prerequisites.

[scottyscotty19]

That's cleared everything up Thanks timothybard.