Basic Relationship Question

[RobM-C]

Hi first time poster here!!I really hope you guys can help me out because ive been trying this all week and it has to be submitted tomorrow and i just can't figure it out. This is part of a college question and i just cant figure out what way to establish the relationships. if someone could provide me with a step by step it would be great. my friend recommended me hear so i hope you guys can help. Here is the question (the relevent part :
The database must include the following information and may include any other information that you consider necessary for the queries or reports listed below. Enter information for at least ten job descriptions, ten universities and ten applicants to enable you to carry out queries and create reports.

Applicant details
Name, address, telephone number, reference number, skills (teaching, research, programming, administration, etc.).

Job description details
Reference number, university, type of job, and skills required.

University details
University name, reference number, contact person’s name, address and telephone number.

Interview details
You must decide what attributes will be contained in this table.

Relationships
One university can request many interviews from applicants for a job description.
One applicant can receive many interviews in relation to a job description.
One university can hire many applicants in relation to a job description.
Each applicant may only be hired by one university and in relation to only one job description.

You must create table(s) and relationships that will allow you to reflect that interviews occur, on particular dates, and that sometimes an applicant is hired for a job description after an interview.


I have set up the tables but ive no idea in what way to establis the relationships. i know how to establish relationships. but in this case i dont understand what order or what type. Please please can someone provide a decent answer.

Thanks in Advance

 

[Singh400]

I think the 'Relationships' section, pretty much spells it out...

Relationships

One university can request many interviews from applicants for a job description.
One applicant can receive many interviews in relation to a job description.
One university can hire many applicants in relation to a job description.
Each applicant may only be hired by one university and in relation to only one job description

 

[RobM-C]

Thanks i Understand that but i dont understand on what they are linked. i.e. on the first question should i link "university name" from the univerity table to "university" in the job description and then link the "skills" from job description table to "skills" of the applicants table!?

i really dont understand. or am i just overcomplicating it? if someone could take the time to give me an example of one it would be just great.

 

[Kiwiman]

Howzit

[BTW - welcome to the forum, and please do not post the same question twice]

In reagrds to relationships and linking to other tables, I always try to use number values.

All of my tables have a Id field generated automatically using the autonumber as the DataType and making it the Primary Key <PK>. When an record is created in one of the child tables, the <PK> field of the parent is entered (automatically) if the relationships are set up correctly, and is know as a Foreign Key <FK> in the child table.

As part of the relationship setup, you will need to enforce referential integrity, to ensure no record in the child table can be created without the parent id

As an example:

PARENT Table
tblCustomers

• CustID - Autonumber <PK>
• CoyName - Text
• ADDLine1 - Text
• blah blah blah

CHILD Table
tblInvoices

• InvID - Autonumber <PK>
• CustID - <FK> (from tblCustomers)
• InvDate - Date/Time
• blah blah blah

The 1:n relationship for the above will allow:

• 1 customer to have many invoices
• One invoice can relate to only the one customer

 

[RobM-C]

so could i use the reference numbers i had to put into each table as the primary key?
and am i only linking two tables per question or is there more than two?
thanks for your help. sorry if im not making myself clear

 

[Kiwiman]

Howzit

Some things for you to ponder...

In this link it says

The relational model dictates that each row in a table be unique

http://r937.com/relational.html

You may want to give this a read to get you started...

Also, there are probably more tables required - a couple to get you started...

• ApplicantSkills - list the skills (link to Applicants table)
• JobApplicants - to record what applicant applies for which job (link to jobdescription table (record FK of applicant here)

What does the reference number represent?

• if it is the unique university identifier - can the same university have multiple jobs they want to fill - how is this handled
• if it is the Job reference - is there a university table that holds there master data so repeat information does not need to be entered - can therefore report on all job requests by university

Also, can an applicant apply for more than one job?

• if yes, then why do we need the reference number in the applicant table?

 

[RobM-C]

I presumed the reference number was going to be used as the primary keys?!!

I understand, say for instance the third question "One university can hire many applicants in relation to a job description"

that this is a one to many relationship between some element of university table and the applicant table. What i dont understand is the "in relation to a job description"

 

[Kiwiman]

Howzit

In your example.

The one table will be the job descriptions (the job descriptions table could as well be the many table to the universities table (the one table)) with a many table called something like tblJobApplicants which will have at a minimum

• 
  
  jobappid
• jobid <FK> to job description table
• applicantid <FK> applicant table (i.e the applicant must already be in the applicant table)

in this table create a unique index based on both the jobid and the applcantid to prevent an applicant from applying twice for the same job.

The tblJobApplicants could in turn be the one table to the many tblInterviews table and so on

In most databases you will have many one tables and many many tables - the trick is to put the right one table with the correct many table.

And a table could be a one table in one relationship, but be the many table in another relationship