[Year 12 IT Apps] IARTV October test

[Timmer-Arends]

Hello Mark

Basically, yes. When you create a relationship the idea idea of enforcing RI 
hits you right in the face but nothing actually forces you to tick the box. 
I must admit I've always thought this odd since I would have thought that RI 
was an integral part of the RDB model, one which would be defaulted to 
'enforce' so that it was only turned off if you knew what you were doing.

Apparently Codd (creator of RDB model) defined two rules: entity integrity 
(primary key field cannot contain null) and referential integrity. 
Interestingly, Access enforces the first but not the second (at least by 
default).

Regards
Robert T-A


----- Original Message ----- 
From: "Mark Kelly" <kel at mckinnonsc.vic.edu.au>
To: "Year 12 IT Applications Teachers' Mailing List" 
<itapps at edulists.com.au>
Sent: Thursday, September 03, 2009 7:11 PM
Subject: Re: [Year 12 IT Apps] IARTV October test


> Thanks, Robert.  So the RI option is turned off by default when creating a 
> relationship?
>
> Timmer-Arends wrote:
>> Hello Mark
>>
>> Access does not automatically enforce referential integrity. Its an 
>> option presented to you when you create a relationship, and you can 
>> totally ignore it. If you do you can delete records in the 'one-table' 
>> quite happily without the 'many-table' being at all affected.
>>
>> Regards
>> Robert T-A
>> Brighton SC
>>
>> ----- Original Message ----- From: "Mark Kelly" 
>> <kel at mckinnonsc.vic.edu.au>
>> To: "Year 12 IT Applications Teachers' Mailing List" 
>> <itapps at edulists.com.au>
>> Sent: Thursday, September 03, 2009 2:23 PM
>> Subject: [Year 12 IT Apps] IARTV October test
>>
>>
>>> Hi all.
>>>
>>> Anyone else using the CSE's IARTV October test for ITA?
>>>
>>> I'm having issues with one of its database questions (4c):
>>>
>>> "A one-to-many relationship is established between the Employees Table 
>>> and the Locations table.
>>>
>>> Explain why setting this relationship would prevent a row in the 
>>> location table being deleted if some employees were still allocated to 
>>> that location."
>>>
>>> The suggested answer is "Setting a relationship will prevent deletion of 
>>> any row that is used as a key in an existing relationship - referential 
>>> integrity."
>>>
>>> This is just not true, at least in Filemaker.  You certainly can delete 
>>> a record in a related table when local records still reference it. Do 
>>> other databases behave differently?
>>>
>>> Secondly, as I understand it, referential integrity (at least in 
>>> Filemaker) is not automatically created when a relationship is set up. 
>>> It's a deliberate strategy to ensure that values can only selected from 
>>> an existing list of options.
>>>
>>> e.g. a person can only be allocated to a location that exists in the 
>>> location table.  This can be done by forcing employees' locations be 
>>> selected from a drop-down list whose contents are populated 
>>> automatically with values from the locations list.  You can also set up 
>>> a validation rule stating that the value in the location
>>>
>>> Again, do other databases have referential integrity enforced by the 
>>> RDBMS - and how do they do it?
>>>
>>> -- 
>>> Mark Kelly
>>> Manager - Information Systems
>>> McKinnon Secondary College
>>> kel AT mckinnonsc.vic.edu.au
>>> McKinnon Rd, McKinnon 3204, Victoria, Australia
>>> Direct line / Voicemail: 8520 9085 Fax +613 9578 9253
>>>
>>> Webmaster - http://www.mckinnonsc.vic.edu.au
>>> IT Lecture notes: http://vceit.com
>>> Moderator: IT Applications Mailing List
>>>
>>> Why do people mis-spell 'grammar' and mispronounce 'pronuniciation'?
>>> _______________________________________________
>>> 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
>>
>> _______________________________________________
>> 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
> kel AT mckinnonsc.vic.edu.au
> McKinnon Rd, McKinnon 3204, Victoria, Australia
> Direct line / Voicemail: 8520 9085 Fax +613 9578 9253
>
> Webmaster - http://www.mckinnonsc.vic.edu.au
> IT Lecture notes: http://vceit.com
> Moderator: IT Applications Mailing List
>
> Why do people mis-spell 'grammar' and mispronounce 'pronuniciation'?
> _______________________________________________
> 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