Exercise 2 and 3 Chapter 5

frdata · May 4th, 2014, 11:30 AM

Hi Rod,

I'm trying to understand the solution given for exercise 2 and 3 in chapter 5. In Figure A-5 on page 418, it is written 1.1 to the left of the Course entity. I think it should be 0.N. There's a many to many to relationship between Student and Course. But then, you are showing the weak entity CourseResult but not StudentCourse in the figure. Am I missing something. I have not come across the "double line" from other books. Is it crucial in this representation. Hope you could enlighten me on this. Thank you.

Rod Stephens · May 4th, 2014, 01:06 PM

Hi Frdata,

I think the diagram is correct.

The double link is a participation constraint. It says a Course object must participate in a Takes relationship or it isn't allowed to exist. Basically that ensures that the Course has students.

In contrast, the Student doesn't have a participation constraint, so a Student can exist without a Course. (The whole issue is sort of saying Courses serve Students and wouldn't exist without them.)

Anyway, I think the 1.1 notation on Course makes sense if you think about the relationships rather than the objects as being the central part of the diagram. The goal is to describe the relationships not the objects.

So what does the Takes relationship need? It needs 1 Course and at least 5 Students.

Similarly what does the WorksOn relationship need? It needs 1 Project and between 1 and 5 Students.

As is mentioned in the Special Notes on page 419, I don't think this diagram enures that the Student is involved with at least 1 Course or Project. I'm not sure there's a good way to represent that fact in this kind of diagram. (And at that point it would be easier to understand if you just make a note of the fact.)

I hope that helps. Let me know if that doesn't make sense or if I've got my reasoning wrong.

frdata · May 4th, 2014, 01:44 PM

Thanks for the prompt reply.

I guess its ok if you look it that way.

Now, for figure A-6 on page 420, should the primary key of Courses be Courses(CourseID, InstructorID). Projects(ProjectId, InstructorID). StudentCourse(StudentID, CourseID). CourseResults(StudentID, CourseID). StudentProjects(StudentID, ProjectID).

Lastly pls confirm that Students to StudentProjects is 1 to 1 and Students to ProjectResults is 1 to many.

Rod Stephens · May 4th, 2014, 02:04 PM

Quote:

I guess its ok if you look it that way.

ER diagrams are kind of like that, I think. They're really focused on relationships.

Quote:

Now, for figure A-6 on page 420, should the primary key of Courses be Courses(CourseID, InstructorID). Projects(ProjectId, InstructorID).

I think they only need the CourseId and ProjectId, but it will depend to an extent on the business model. For example, you probably don't need the InstructorId to to identify a course. Math 101 will have an associated instructor. Another link to the Instructors table should tell you who.

But you may need to modify that if, for example, there is more than one Math 101 course being run at the same time. Then you'd probably want to have class or section numbers or something to tell them apart, You probably still wouldn't want to use InstructorId because the same instructor could teach multiple Math 101 classes.

Or if you want to track them across multiple years. Then you would probably want to add year and semester.

Quote:

StudentCourse(StudentID, CourseID). CourseResults(StudentID, CourseID). StudentProjects(StudentID, ProjectID).

Yes for these. I'm not sure why I didn't include them. It may have just been an oversight.

Technically I think they aren't required to have primary keys and you wouldn't usually look up their records by those keys. For example, you would probably want to find all StudentCource records for a particular Student not a record for a specific CourseId.

But in practice most relational databases require a primary key and you would want them anyway to guarantee uniqueness.

Quote:

Lastly pls confirm that Students to StudentProjects is 1 to 1 and Students to ProjectResults is 1 to many.

Hmm... It's been a while since I built this model, but I think the idea is that StudentProjects represents current projects and ProjectResults represents past projects.

In that case, a student can be working on at most one project so StudentProjects would be 1 to 1. A student might have worked on several past projects so Students-ProjectResults would be 1 to many.

These thing do get confusing, even to me, so let me know if you think I've gotten it wrong. I know I have missed a few of the cardinality values in models.

And many of them depend on your interpretation of the business rules. For example, does the Projects table represent projects over multiple years or only current projects? The diagram in the book assumes multiple years. (Otherwise you probably don't need the link to ProjectsResults, which does record across multiple years.)

frdata · May 4th, 2014, 02:23 PM

Thanks for all the explanation. I'm trying to get slowly to Figure B-16 on page 478 which I think is a very useful design. Your explanation in the other thread about figure B-16 helped a lot and gave me new ideas. The figure we are discussing it just some part of the whole design. Thank you sir.

Rod Stephens · May 4th, 2014, 09:37 PM

I'm glad that helped. Please let me know if you have other questions.

frdata · May 5th, 2014, 03:42 AM

Hi again,

To test my understanding, can I do the following :

First, I think StudentCourses could be removed because it is redundantly represented with CourseResults. Then, the unique key would be formed from CourseResults(StudentID,CourseID, Semester, Year). I'm replacing Date with Semester and Year here.

With the same reasoning, the StudentProjects table could be removed and placing unique constraint on ProjectResults(StudentID, ProjectID, Semester, Year)

The limitation of one project per student could be specified as a business rule.

By doing this, I think this would better represent figure A-5. What do you think

Rod Stephens · May 5th, 2014, 12:05 PM

Quote:

First, I think StudentCourses could be removed because it is redundantly represented with CourseResults. Then, the unique key would be formed from CourseResults(StudentID,CourseID, Semester, Year). I'm replacing Date with Semester and Year here.

They're not redundant if StudentCourses represents current enrollment and CourseResults represents past courses. But you could merge them as you say. (There are some differences between current and past classes--for example, you probably don't need to store quiz scores for past classes--but that's outside the scope of this model anyway.)

Quote:

With the same reasoning, the StudentProjects table could be removed and placing unique constraint on ProjectResults(StudentID, ProjectID, Semester, Year)

You're right, that's basically the same arrangement.

Quote:

By doing this, I think this would better represent figure A-5. What do you think

Figure A-5 has past course and project results represented separately so I'm not sure the new design would fit that diagram more closely, but I think your new design is probably better than the old one in any case.