Data Table Relations

One-to-One Relationship

primary key = Student ID
secondary key = Student Code

Destination Table

1 : 1

(Outside) Source Table

Table 1: Student Contact Data

Student ID	Name	Phone	Program
39	John	321-1234	Music
45	Paul	321-2345	Music
56	George	321-3456	Music
88	Ringo	321-4567	Music
89	Jerry	555-1111	Fine Arts
99 ½	Kramer	555-1111	Space
123	George	555-5959	Undeclared
119	Elaine	555-9876	Business
200	Doug	111-2231	Forestry

Table 2: Student Finances

Student Code	Name	Tuition	Amount Paid	Owing
39	John L	3000	3000	0
45	Paul M	3000	3000	0
56	George H	3000	3000	0
88	Ringo S	3000	3000	0
89	Jerry S	2500	2500	0
99 ½	Cosmo Kramer	3600	5.75	3594.25
123	George C	3000	0	3000
119	Elaine B	2800	2000	800
200	Doug C	4000	2200	1800

After joining it looks like this...

Student ID	Name	Phone	Program	Student Code	Name	Tuition	Amount Paid	Owing
39	John	321.1234	Music	39	John L	3000	3000	0
45	Paul	321.2345	Music	45	Paul M	3000	3000	0
56	George	321.3456	Music	56	George.H	3000	3000	0
88	Ringo	321.4567	Music	88	Ringo.S	3000	3000	0
89	Jerry	555.1111	Fine Arts	89	Jerry.S	2500	2500	0
99 ½	Kramer	555.1111	Space	99 ½	Cosmo.Kramer	3600	5.75	3594.25
123	George	555.5959	Undeclared	123	George.C	3000	0	3000
119	Elaine	555.9876	Business	119	Elaine.B	2800	2000	800
200	Doug	111.2231	Forestry	200	Doug.C	4000	2200	1800

Many-to-One

primary key = Program
secondary key = Department

Destination Table

M : 1

(Outside) Source Table

Table 1: Student Contact Data

Student ID	Name	Phone	Program
39	John	321-1234	Music
45	Paul	321-2345	Music
56	George	321-3456	Music
88	Ringo	321-4567	Music
89	Jerry	555-1111	Fine Arts
99 ½	Kramer	555-1111	Space
123	George	555-5959	Undeclared
119	Elaine	555-9876	Business
200	Doug	111-2231	Forestry

Table 3: Departments

Department	# of Faculty	Student Capacity	Students Enrolled
Music	7	122	120
Arts	39	2800	2654
Business	8	120	109
Forestry	6	46	39

After joining it looks like this...

Student ID	Name	Phone	Program	Department	# of Faculty	Student Capacity	Students Enrolled
39	John	321.1234	Music	Music	7	122	120
45	Paul	321.2345	Music	Music	7	122	120
56	George	321.3456	Music	Music	7	122	120
88	Ringo	321.4567	Music	Music	7	122	120
89	Jerry	555.1111	Arts	Arts	39	2800	2654
99 ½	Kramer	555.1111	Space
123	George	555.5959	Undeclared
119	Elaine	555.9876	Business	Business	8	120	109
200	Doug	111.2231	Forestry	Forestry	6	46	39

One-to-Many

primary key = Name

secondary key = Winner

Destination Table

1 : M

(Outside) Source Table

Table 1: Student Contact Data

Student ID	Name	Phone	Program
39	John	321-1234	Music
45	Paul	321-2345	Music
56	George	321-3456	Music
88	Ringo	321-4567	Music
89	Jerry	555-1111	Fine Arts
99 ½	Kramer	555-1111	Space
123	George	555-5959	Undeclared
119	Elaine	555-9876	Business
200	Doug	111-2231	Forestry

Table 4: Scholarships

Scholarship ID	Name	Winner
A1	MCA	Paul
A2	MM$	John
A3	Sony	Paul
B1	Time-Warner	Elaine

After relating (and selecting Paul) it looks like this...

Destination Table

1 : M

(Outside) Source Table

Table 1: Student Contact Data

Student ID	Name	Phone	Program
39	John	321-1234	Music
45	Paul	321-2345	Music
56	George	321-3456	Music
88	Ringo	321-4567	Music
89	Jerry	555-1111	Fine Arts
99 ½	Kramer	555-1111	Space
123	George	555-5959	Undeclared
119	Elaine	555-9876	Business
200	Doug	111-2231	Forestry

Table 4: Scholarships

Scholarship ID	Name	Winner
A1	MCA	Paul
A2	MM$	John
A3	Sony	Paul
B1	Time-Warner	Elaine