SUBJECT DATABASE ADMINISTRATION AND SECURITY a) What is a database trigger, and how can database triggers be

SUBJECT DATABASE ADMINISTRATION AND SECURITY

 

Transcribed Image Text:

a) What is a database trigger, and how can database triggers be used to enhance data integrity. b) Based on the Dreamome database (refer appendix), implement the following data integrity rules: 1. A property must be assigned to a staff working at the branch that controls the property. In addition, the property and the branch that controls it must be in the same city. ii. The number of properties assigned to each staff must be maintained at all times. In the event of a new assignment or re-distribution of the assignment, the number must be immediately updated for the staff involved. In addition, the number of properties assigned to a staff cannot exceed 10 units of property. Appendix Branch branchNo street city B005 22 Deer Rd postcode London SW1 4EH B007 16 Argyll St Aberdeen AB2 3SU B003 163 Main St Glasgow G119QX B004 32 Manse Rd Bristol B599 INZ B002 56 Clover Dr London NW10 6EU Staff staffNo Name IName position sex DOB salary branchNo SGS SL21 John White Manager M 1-Oct-45 30000 BOOS SG37 Ann Beech Assistant F 10-Nov-60 12000 B003 - SG14 David Ford Supervisor M 24-Mar-58 18000 B003 SA9 Mary Howe Assistant F 19-Feb-70 9000 B007 Susan Brand SL41 Julie Lee Manager F 3-Jun-40 24000 B003 Assistant F 13-Jun-65 9000 B005 PropertyForRent propertyNo street city postcode type rooms rent ownerNo staffNo branchNo PA14 16 Holhead PL94 6 Argyll St PG4 6 Lawrence St PG36 2 Manor Rd PG21 PG16 Aberdeen AB7 5SU House 6 London NW2 Flat 4 Glasgow G11 9QX Flat 3 Flat Glasgow G32 4QX 3 18 Dale Rd Glasgow G12 House 5 5 Novar Dr Flat Glasgow G12 9AX 4 650 CO46 400 CO87 350 CO40 375 CO93 600 CO87 450 CO93 SA9 B007 SL41 5005 B003 SG37 SG37 B003 B003 SG14 B003 Client clientNo Name IName telNo prefType maxRent eMail CR76 John Kay 0207-774-5632 Flat CR56 Aline Stewart 0141-848-1825 Flat CR74 Mike Ritchie 01475-392178 House CR62 Mary Tregear 01224-196720 Flat 425 john.kay@gmail.com 350 astewart@hotmail.com 750 mritchie01@yahoo.co.uk 600 maryt@hotmail.co.uk PrivateOwner ownerNo fName IName address teiNo eMail CO46 CO87 CO40 CO93 Joe Keogh Carol Tina Murphy 63 Well St, Glasgow G12 Tony Shaw 12 Park Pi, Glasgow G4 0QR 2 Fergus Dr, Aberdeen AB2 7SX 01224-861212 jkeogh@lhh.com Farrel 6 Achray St, Glasgow G32 9DX 0141-357-7419 cfarrel@gmail.com 0141-943-1728 tinam@hotmail.com 0141-225-7025 tony.shaw@ark.com password Viewing clientNo propertyNo viewDate comment Registration clientNo branchNo staffNo date Joined CR56 PA14 24-May-08 too small CR76 B005 $1.41 2-Jan-08 CR76 PG4 20-Apr-08 too remote CR56 B003 SG37 11-Apr-07 CR56 PG4 26-May-08 CR74 8003 SG37 16-Nov-06 CR62 PA14 14-May-08 no dining room CR62 B007 SA9 7-Mar-07 CR56 PG36. 28-Apr-08 a) What is a database trigger, and how can database triggers be used to enhance data integrity. b) Based on the Dreamome database (refer appendix), implement the following data integrity rules: 1. A property must be assigned to a staff working at the branch that controls the property. In addition, the property and the branch that controls it must be in the same city. ii. The number of properties assigned to each staff must be maintained at all times. In the event of a new assignment or re-distribution of the assignment, the number must be immediately updated for the staff involved. In addition, the number of properties assigned to a staff cannot exceed 10 units of property. Appendix Branch branchNo street city B005 22 Deer Rd postcode London SW1 4EH B007 16 Argyll St Aberdeen AB2 3SU B003 163 Main St Glasgow G119QX B004 32 Manse Rd Bristol B599 INZ B002 56 Clover Dr London NW10 6EU Staff staffNo Name IName position sex DOB salary branchNo SGS SL21 John White Manager M 1-Oct-45 30000 BOOS SG37 Ann Beech Assistant F 10-Nov-60 12000 B003 - SG14 David Ford Supervisor M 24-Mar-58 18000 B003 SA9 Mary Howe Assistant F 19-Feb-70 9000 B007 Susan Brand SL41 Julie Lee Manager F 3-Jun-40 24000 B003 Assistant F 13-Jun-65 9000 B005 PropertyForRent propertyNo street city postcode type rooms rent ownerNo staffNo branchNo PA14 16 Holhead PL94 6 Argyll St PG4 6 Lawrence St PG36 2 Manor Rd PG21 PG16 Aberdeen AB7 5SU House 6 London NW2 Flat 4 Glasgow G11 9QX Flat 3 Flat Glasgow G32 4QX 3 18 Dale Rd Glasgow G12 House 5 5 Novar Dr Flat Glasgow G12 9AX 4 650 CO46 400 CO87 350 CO40 375 CO93 600 CO87 450 CO93 SA9 B007 SL41 5005 B003 SG37 SG37 B003 B003 SG14 B003 Client clientNo Name IName telNo prefType maxRent eMail CR76 John Kay 0207-774-5632 Flat CR56 Aline Stewart 0141-848-1825 Flat CR74 Mike Ritchie 01475-392178 House CR62 Mary Tregear 01224-196720 Flat 425 john.kay@gmail.com 350 astewart@hotmail.com 750 mritchie01@yahoo.co.uk 600 maryt@hotmail.co.uk PrivateOwner ownerNo fName IName address teiNo eMail CO46 CO87 CO40 CO93 Joe Keogh Carol Tina Murphy 63 Well St, Glasgow G12 Tony Shaw 12 Park Pi, Glasgow G4 0QR 2 Fergus Dr, Aberdeen AB2 7SX 01224-861212 jkeogh@lhh.com Farrel 6 Achray St, Glasgow G32 9DX 0141-357-7419 cfarrel@gmail.com 0141-943-1728 tinam@hotmail.com 0141-225-7025 tony.shaw@ark.com password Viewing clientNo propertyNo viewDate comment Registration clientNo branchNo staffNo date Joined CR56 PA14 24-May-08 too small CR76 B005 $1.41 2-Jan-08 CR76 PG4 20-Apr-08 too remote CR56 B003 SG37 11-Apr-07 CR56 PG4 26-May-08 CR74 8003 SG37 16-Nov-06 CR62 PA14 14-May-08 no dining room CR62 B007 SA9 7-Mar-07 CR56 PG36. 28-Apr-08