[Year 12 IT Apps] Database relationships

[Mark Kelly]

Hi Lisa. Firstly please don't be afraid to challenge me.  I'm far from 
infallible. In fact I remember  making a mistake at some time.  It was 
in 1983, I believe...

I agree with you: the example I gave for 1:1 was not good.  I should 
have used a real-life example like ones below, but I was trying to keep 
a certain similarity between the examples in the handout.

Anyway, I see a one-to-one relationship basically like VLOOKUP in Excel. 
They can be vital, and don't, I believe, they necessarily point to 
structural deficiency.

Fregzample... in my reporting database ('Rupert') the teacher selects 
the subject to be reported on in one table (where the reports are 
created).  There is a one-to-one relationship between the subject 
selected in that table (rupert.fp7) and the same subject ID in the 
subjects table (subjects.fp7) from which all the subject information 
(full name, assessment tasks, task weightings, VELS dimensions, 
outcomes) are stored.

Similarly, when a student is selected in rupert.fp7, his/her info (full 
name, form, registration #, sex etc) is drawn from students.fp7 into the 
report.

The full relationship map of Rupert is attached.  I'm sure it could be 
more efficient, but every time I try radically rebuilding it, I spend a 
week re-learning why I did it that way, changing small things and end up 
breaking everything.  So I now tend to leave it alone :-)

Thanks for the heads-up, Lisa.  I'll amend my handout.

Cheers
Mark

Lisa Imms wrote:
> Hi Mark,
>  
> Firstly, thanks for the resource :-)
>  
> I hate to even contemplate disagreeing with you as my ITA class and I regard you as the ITA font-of-all-knowledge, but I will try this anyway.....
>  
> Your example for one-to-one: "E.g. an employee's department data in one table matches a corresponding department in another table, allowing data relating to that department to be fetched."
>  
> Wouldn't this be a one to many relationship? Even the line you have joining them has the 'many feathers' at the employees table? Although each employee can belong to only one department, each department can (and will) have multiple employees? So it is actually a one to many relationship? 
>  
> Am I correct in assuming that a well organised database would not have any one-to-one relationships? The two tables in a one-to-one relationship could/should just be merged into one table? 
>  
> Lisa Bairstow
> St Mary's Coptic Orthodox College
> 
> ________________________________
> 
> From: itapps-bounces at edulists.com.au on behalf of Mark Kelly
> Sent: Wed 7/23/2008 2:24 PM
> To: Year 12 IT Applications Teachers' Mailing List
> Subject: [Year 12 IT Apps] Database relationships
> 
> 
> 
> Hi all.  It's been mighty quiet here for a while.  To perk things up,
> here's a little revision for database relationships (1-1, 1-many,
> many-many) that may or may not be useful.
> 
> Cheers
> Mark
> 
> --
> Mark Kelly
> Manager - Information Systems
> McKinnon Secondary College
> McKinnon Rd McKinnon 3204, Victoria, Australia
> Direct line / Voicemail: 8520 9085
> School Phone +613 8520 9000
> School Fax +613 95789253
> kel AT mckinnonsc.vic.edu.au
> 
> Webmaster - http://www.mckinnonsc.vic.edu.au <http://www.mckinnonsc.vic.edu.au/> 
> IT Lecture notes: http://vceit.com <http://vceit.com/> 
> Moderator: IT Applications Mailing List
> 
> If you Declare War - is it integer or boolean?
> _______________________________________________
> http://www.edulists.com.au <http://www.edulists.com.au/>  - FAQ, resources, subscribe, unsubscribe
> IT Applications Mailing List kindly supported by
> http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html - Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au <http://www.vitta.org.au/>   - VITTA Victorian Information Technology Teachers Association Inc 
> 
> _______________________________________________
> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
> IT Applications Mailing List kindly supported by
> http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html - Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers Association Inc


-- 
Mark Kelly
Manager - Information Systems
McKinnon Secondary College
McKinnon Rd McKinnon 3204, Victoria, Australia
Direct line / Voicemail: 8520 9085
School Phone +613 8520 9000
School Fax +613 95789253
kel AT mckinnonsc.vic.edu.au

Webmaster - http://www.mckinnonsc.vic.edu.au
IT Lecture notes: http://vceit.com
Moderator: IT Applications Mailing List

A conclusion is the place where you got sick of thinking.
If you Declare War - is it integer or boolean?
-------------- next part --------------
A non-text attachment was scrubbed...
Name: rupertmap.jpg
Type: image/jpeg
Size: 47267 bytes
Desc: not available
Url : http://www.edulists.com.au/pipermail/itapps/attachments/20080724/70bc3750/rupertmap-0001.jpg