Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 4th, 2014, 11:30 AM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default Exercise 2 and 3 Chapter 5

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.
Reply With Quote
  #2 (permalink)  
Old May 4th, 2014, 01:06 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #3 (permalink)  
Old May 4th, 2014, 01:44 PM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old May 4th, 2014, 02:04 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.)
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
frdata (May 8th, 2014)
  #5 (permalink)  
Old May 4th, 2014, 02:23 PM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #6 (permalink)  
Old May 4th, 2014, 09:37 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #7 (permalink)  
Old May 5th, 2014, 03:42 AM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old May 5th, 2014, 12:05 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
frdata (May 8th, 2014)
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
chapter 3 exercise Q 3 muhammadanzar BOOK: Beginning PHP 6, Apache, MySQL 6 Web Development ISBN: 9780470391143 3 July 15th, 2016 04:40 AM
Chapter 4 exercise 4 earlmw BOOK: Ivor Horton's Beginning Visual C++ 2008 ISBN: 978-0-470-22590-5 0 March 3rd, 2012 02:05 PM
Chapter 7 - Exercise rickymartini76@yahoo.it BOOK: Beginning PHP 6, Apache, MySQL 6 Web Development ISBN: 9780470391143 2 March 16th, 2010 07:32 PM
Chapter 5 exercise 3 Will BOOK: Beginning Microsoft Visual C# 2008 ISBN: 978-0-470-19135-4 2 September 27th, 2009 02:41 PM
Chapter 8, Exercise 4 cjo BOOK: Beginning ASP.NET 1.0 0 November 3rd, 2003 01:26 PM



All times are GMT -4. The time now is 12:23 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.