Home » Other » General » ER Diagram (11g)
ER Diagram [message #554468] Mon, 14 May 2012 13:20 Go to next message
GaneshAnanth
Messages: 34
Registered: January 2011
Location: Thanjavur
Member
Could any of you please explain the HR diagram in the attachment.

I know the key highlighted in blue color are Primary Key.
I do want to understand which one is parent table and which one is the child table.

For example:

1. Region table needs to be created first and region_id needs to be set as primary key.
2 Countries table needs to be created second and region_id as foreign key and country_id as primary key.

Please explain - I need to implement this table to practice

Thanks in advance.
Ganesh

/forum/fa/10153/0/

[mod-edit: jpg attachment inserted into message body by bb]
  • Attachment: HR.JPG
    (Size: 37.93KB, Downloaded 14118 times)

[Updated on: Tue, 15 May 2012 17:06] by Moderator

Report message to a moderator

Re: ER Diagram [message #554480 is a reply to message #554468] Mon, 14 May 2012 14:21 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Region is parent to country.
A line between tables that splits into three indicates a one to many relationship. The three being the many obviously.
In a one to many relationship the one is always the parent.

Report message to a moderator

Re: ER Diagram [message #554527 is a reply to message #554480] Tue, 15 May 2012 03:32 Go to previous messageGo to next message
GaneshAnanth
Messages: 34
Registered: January 2011
Location: Thanjavur
Member
Hi Cookiemonster,

I know Region is parent and country in child.
There are Tables like Region, Country, Lacation, Departments, Employees, Jobs and Job History.

I am explaining here. Please correct me if I am wrong

Order in which Table and constraints to be created

1. Region Table, region_id - primary key
2. Country Table, country_id - primary key & region_id - foreign key references region_id in Region Table
3.Lacations Tbale, location_id - primary key & country_id - foreign key references country_id in Countries Table.
4. Department Table, department_id - Primary key & location_id - foreign key references location_id in Locations Table.

Confirm these are correct and explain for other tables Employees, Jobs and Job History.

Thanks,
Ganesh

Report message to a moderator

Re: ER Diagram [message #554546 is a reply to message #554527] Tue, 15 May 2012 07:17 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks fine to me. Now just expand that logic to the other tables.

Report message to a moderator

Re: ER Diagram [message #554549 is a reply to message #554546] Tue, 15 May 2012 08:49 Go to previous messageGo to next message
GaneshAnanth
Messages: 34
Registered: January 2011
Location: Thanjavur
Member
Please clear on these

1. Department Table and Employee Table both has manager_id - But they are not referencing to each other
2. Employee table has many to one reference for the table itself - see the image employee table's right side
3. In Job History Table Start_date is set to as primary key which is not referencing to any table.
4. Please explain which are the keys set to as primary and which are the key set to as foreign.

Report message to a moderator

Re: ER Diagram [message #554574 is a reply to message #554549] Tue, 15 May 2012 12:07 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator

  1. What is not referencing each other? These tables? According to the image you attached, there are one-to-many relationships both way.
  2. That might be because of parent-child relationship between employees and their managers (so EMPLOYEE_ID references MANAGER_ID).
  3. Not every primary key is referenced by another foreign key. Primary key in JOB_HISTORY consists of EMPLOYEE_ID and START_DATE because every employee may have more than just one record in that table (i.e. works in different departments). So the author chose to set primary key to these two columns, as you can't start working for different departments the same day.

    Could EMPLOYEE_ID and DEPARTMENT_ID make a primary key here? No, because an employee might have worked in department 1, move to department 2, and then return to department 1.
  4. That is not visible here. Primary keys are indicated, but foreign keys are not so all you can do is guess. On such a simple model, that shouldn't be too difficult

Report message to a moderator

Re: ER Diagram [message #554669 is a reply to message #554574] Wed, 16 May 2012 06:25 Go to previous message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) departments.manager_id references employees.employee_id. Employees.department_id references departments.department_id. You'll need to create the fks after both tables have been created.
3) job_history isn't parent to anything. So it's pk shouldn't be referenced by anything.

Report message to a moderator

Previous Topic: patch 10.2.0.4 on linux
Next Topic: ORA-01110: data file 2 error in database
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 27 04:56:13 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.