6 Replies - 1859 Views - Last Post: 12 April 2011 - 04:49 AM Rate Topic: -----

#1 xdeathcorex  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 20-March 10

ERD for my mini db project

Posted 08 April 2011 - 08:52 PM

I'm doing Oracle Database for my school mini project. It is due tomorrow! :P So, I had a lil bit confusion here. I don't know whether my mini database here will work or not. Take a look at my ERD here if you guys can understand it and please let me know what will cause the problem if I started to enter the data into the db.

Posted Image

A little bit about this database is I want to store trees and worker names and stuffs inside the database (I don't know how to say this). So each workers have their own orchard which they work on. and each orchards has their trees and species of the trees.

I don't know whether the company table is appropriate or not. My confusion here is actually at the workers, do I need to put orchard ID as foreign key so that when I query it, I can show which orchard they belong to?

tl;dr Database for mini project, does the ERD correct and relates to each other?

oh forget about the crow's foot notation. I'll do something for that.

Is This A Good Question/Topic? 0
  • +

Replies To: ERD for my mini db project

#2 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: ERD for my mini db project

Posted 09 April 2011 - 12:13 PM

Hello,

One thing I notice is that you have the trees foreign key in the orchard table while it should be the other way around. One orchard can have many trees.

Putting the orchard FK in the worker table is the correct way to do it. That is assuming that one worker only works at one orchard at a time. If you wanted the worker to be able to work at multiple orchards simultaneously you would need to create an associative table with foreign keys from the orchard and worker table.

Other than that everything looks ok to me. Hope this helps :)
Was This Post Helpful? 1
  • +
  • -

#3 xdeathcorex  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 20-March 10

Re: ERD for my mini db project

Posted 09 April 2011 - 03:32 PM

Hey Jstall thanks for your feedback, I really really appreciate it cuz I understand the concept now :D :D

Okay, I've put the data into the db. Here's how it looked for "species" table. Is this correct if I want to assign same fruit to different orchard?

Posted Image

I mean if I want to add another same fruit (Orange) and assign it to 'orchard_id 20' then I have to make a new species_id?
Right?? That's what made me confused though :P

This post has been edited by xdeathcorex: 09 April 2011 - 03:35 PM

Was This Post Helpful? 0
  • +
  • -

#4 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: ERD for my mini db project

Posted 09 April 2011 - 06:17 PM

Hi,

You wouldn't want an orchard id in the species table. You would want it in the tree table like you have in your ERD. A species can have many different trees, but a tree can only be one species. The tree has a orchard id because the tree is in the orchard. The orchard has many trees but the tree is only in one orchard.

Using this schema you will be able to query and find all the trees in a given orchard and what species each tree is. Hope this helps :)
Was This Post Helpful? 1
  • +
  • -

#5 xdeathcorex  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 20-March 10

Re: ERD for my mini db project

Posted 10 April 2011 - 04:39 AM

Hey,
I wish I could multi-karma you :D :D Thanks a lot man. It is now working perfectly. :)
Was This Post Helpful? 0
  • +
  • -

#6 xdeathcorex  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 55
  • Joined: 20-March 10

Re: ERD for my mini db project

Posted 12 April 2011 - 12:36 AM

Bump. Hello again.

So I wanted to add a bridge between Orchard and Trees and Tools, because I wanted to make many Trees in one Orchard . Is this how am I gonna do it? I mean I can't really see how it goes when querying the database. :confuse: What else do I might have to put inside the bridge entity besides the PK from Orchard and Tools and Trees? :confuse:

Posted Image

This post has been edited by xdeathcorex: 12 April 2011 - 12:39 AM

Was This Post Helpful? 0
  • +
  • -

#7 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: ERD for my mini db project

Posted 12 April 2011 - 04:49 AM

Hello,

Because your tree table has fields like DATE_PLANTED and FRUIT_PRODUCED it suggests that that each record represents a living physical tree in an orchard. In this case you would not need the associative table TREE_INFO. This is because each tree can only, presumably be in one orchard. That is why the FK in the tree table would be sufficient.

In the case of tools it looks like that table represents a type of tool. In this case the associative table TOOLS_INFO is appropriate since a type of tool can be on many orchards and an orchard can have many tools. You would want to remove the orchard_id from the tools table.

To use the associative table you use joins. For example to find every tool being used in orchard 3 you would use the query:
SELECT TOOLS.*
FROM TOOLS,
INNER JOIN TOOLS_INFO ON TOOLS.TOOL_ID = TOOLS_INFO.TOOL_ID
WHERE TOOLS_INFO.orchard_id = '3'



Hope that helps :)
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1