Student: Having Trouble with ERDs

[Ludovik]

Hi all,


I'm a student in my first year of an IT Program and one of the courses I've been struggling to understand is Introduction to Databases. We've moved from manipulating data/generating reports/making queries in Access to designing ERDs. What I'm having trouble with is deciding what should be a P.Key and a F.Key as well as what should be its own entity.

I'll post the database description as well as the ERD I've come up with. What I'm hoping is that someone could have a look at it and let me know if my logic is flawed. Additionally, if anyone has any recommended web resources for understanding/developing ERDs I'd be much obliged.

Anyways, here is the description:

Scenario 1: Soapscum Window Washing wants to keep track of its customers, employees and the projects to which they are assigned. They need to keep track of some basic employee contact information, such as their first name, last name, street address, city, province, postal code, phone number, email address, and emergency contact phone number. They use job classifications to group employees and determine salary. A classification has a code, description, and a salary. Each employee is assigned to a single classification, and there can be multiple employees within the company assigned to a classification. In addition, they would like to keep track of all the projects to which an employee is assigned. For each project, there is an id number, a start date, an end date, and a cost. Each project can have multiple employees assigned to it, and an employee can be assigned to multiple projects. They would like to track their customer’s contact information such as the company name, street address, city, province, postal code, contact person, and phone number. Each project is for only a single customer, but a customer will have multiple projects over time.

And this is the ERD I've come up with:



If anyone could give some advice, I'd be extremely grateful.

- Greg

[June7]

There will be many employees for each project? If so, should not be EmployeeID in Projects. If not, there is no need for EmployeeProject junction table.

There will be many customers for each project? Can each customer have many projects?

[Ludovik]

Yes, there are many employees for each project. So, I've removed EmployeeID in Projects. A customer can have many projects over time, so I think that relationship is OK.

[orange]

Since

Each project can have multiple employees assigned to it, and an employee can be assigned to multiple projects.

, you have a Many to Many situation, and to resolve this you make two One to Many relationships using a junction table.

For more info on junction tables and many to many see http://www.youtube.com/watch?v=7XstSSyG8fw

A Project is a Project regardless of Employee, so EmployeeID should not be in Project. The Employee/Project relationship is handled by the Junction table.
A Customer is a Customer regardless of ProjectID, so Project (I think you meant ProjectID) should not be in Customer table.

Since

Each project is for only a single customer,

you can accommodate this by placing the CustomerID (Fk) in Project (as you have done).

In practice with Access you should not have spaces in field (attribute) names.

Good luck with your project.

For a number of videos related to ERDs see my post
https://www.accessforums.net/forms/e...-35220.html#13

[June7]

Was that fine print there when I first read OP?

Agree with Orange.

But Orange - (FH)?

[orange]

June7,

I caught the FH -->>and changed it/edit FK. I'm on a6 year old laptop with some sticky keys -- sometimes there are no f's, some times no r's. But this one was a clear case of fat fingers being a little too quick.

[Ludovik]

Attachment 14406

---

Yeah the fine text was there all along but don't worry. I've since done a little bit of reading and I think I have a better version, if one you nice people would glance at it. I also just noticed an extra space, will fix the formatting soon.

[Ludovik]

I had one other ERD to make and I think I have it sorted but there's one thing I'm not sure of: Is the relationship between books and orders a many to many relationship and should I have a junction table there? I'll post the description along with the ERD I made.

DESCRIPTION

Scenario 2: We want to design a database for the Acme online used textbook store. The website
will allow customers to register, search for books, and place orders for one or more books. We
want to maintain name, address, and contact (i.e. phone/email) information on our customers
and we will identify them by their login name. They will also be able to search for textbooks by
author or publisher. Our clients will want a fair bit of information about a textbook before
buying: title, edition, type of cover, number of pages, original price, used price, etc. Textbooks
will be tracked by their ISBN-13 number. For publishers, they want to track the company name,
address information, as well as a contact name and phone number. For authors, they will have
information on their title (e.g. “Professor”, ‘Doctor”), full name, and primary field of expertise
(e.g. “Marine Biology”). They also want to track the dates that orders are placed and the
quantity of each book ordered. We need a database designed to support such a web application

---

My ERD:

[June7]

Should have an Orders table and OrderDetails, assuming each order can have more than one book title.

Order table is missing CustomerID

Orders
OrderID
CustomerID
DateOrder

OrderDetails
OrderID
ISBN13
Quantity


BTW, advise no spaces or special characters/punctuation (underscore is exception) in names. Better would be ISBN13 or ISBN_13.

[orange]

I agree with June's comments. I would also include AgreedToPrice in OrderDetails. If you don't include that Price in that table, all records (history) will change when you decide to change the UsedPrice of a Book.
Also, as your model stands, I think you allow for only 1 copy of each Book. (BOOK PK is ISBN13)

[June7]

orange, I don't think as stands, the db cares how many copies of book are on hand or ordered. The same ISBN could be referenced in as many orders as desired.

This does present the question of whether or not inventory accounting needs to be incorporated.

[orange]

True, but the PK of the Book is ISBN13 --- doesn't sound like it would uniquely identify a Book record to me.
Perhaps if there was a CopyNo or similar...

[June7]

True, the ISBN does not uniquely identify a copy of book but nothing prevents referencing it for multiple orders. The question is should there be inventory accounting? Should each book copy be accounted for or should they be treated like a bunch of pencils in a bin and each ISBN balance on hand quantity calculated from transactions of stock received and stock sold?

[orange]

I think your statement implies that if a Customer Orders ISBN13, they buy all copies you have -- which I doubt is realistic.

Consider renting out DVDs. If you have 5 copies of a DVD title, you rent out 1. CopyNo is key to unique, not Title (or ISBN13).
I can't think of designing a DVD rental firm, where a Customer always rents all copies of the DVD. Possible yes, but highly unlikely.

Perhaps the OP can claify.

[Ludovik]

Hey there. Sorry I've been away. The ISBN does unique identify the book for our purposes but as my database stood, above, I had it so a customer was unable to order more than one copy of a book at any given time. I've since fixed it and have made some progress in my understanding of ERDs. I have another problem I'm working on if anyone in this thread is interested; it's much more complicated and uses self-referencing tables, sub-types and super-types. I've worked hard on it but I could use another set of eyes. Also, I've got to mark someone as the one who helped me most here.