ch6-2 - Chapter 6: Integrity and Security ! Domain...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
1 ©Silberschatz, Korth and Sudarshan 6.1 Database System Concepts Chapter 6: Integrity and Security Chapter 6: Integrity and Security ! Domain Constraints ! Referential Integrity ! Assertions ! Triggers ! Security ! Authorization ! Authorization in SQL ©Silberschatz, Korth and Sudarshan 6.2 Database System Concepts Domain Constraints Domain Constraints ! Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. ! Domain constraints are the most elementary form of integrity constraint. ! They test values inserted in the database, and test queries to ensure that the comparisons make sense. ! New domains can be created from existing data types " E.g. create domain Dollars numeric (12, 2) create domain Pounds numeric (12,2) ! We cannot assign or compare a value of type Dollars to a value of type Pounds. " However, we can convert type as below ( cast r . A as Pounds ) (Should also multiply by the dollar-to-pound conversion-rate)
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 ©Silberschatz, Korth and Sudarshan 6.3 Database System Concepts Domain Constraints (Cont.) Domain Constraints (Cont.) ! The check clause in SQL-92 permits domains to be restricted: " Use check clause to ensure that an hourly-wage domain allows only values greater than a specified value. create domain hourly-wage numeric(5,2) constraint value-test check ( value > = 4.00) " The domain has a constraint that ensures that the hourly-wage is greater than 4.00 " The clause constraint value-test is optional; useful to indicate which constraint an update violated. ! Can have complex conditions in domain check " create domain AccountType char (10) constraint account - type - test check ( value in (‘Checking’, ‘Saving’)) " check ( branch - name in ( select branch - name from branch )) ©Silberschatz, Korth and Sudarshan 6.4 Database System Concepts Referential Integrity Referential Integrity ! Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. " Example: If “Perryridge” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”. ! Formal Definition " Let r 1 ( R 1 ) and r 2 ( R 2 ) be relations with primary keys K 1 and K 2 respectively. " The subset α of R 2 is a foreign key referencing K 1 in relation r 1 , if for every t 2 in r 2 there must be a tuple t 1 in r 1 such that t 1 [ K 1 ] = t 2 [ α ]. " Referential integrity constraint also called subset dependency since its can be written as α ( r 2 ) ⊆∏ K 1 ( r 1 )
Background image of page 2
3 ©Silberschatz, Korth and Sudarshan 6.5 Database System Concepts Referential Integrity in the E Referential Integrity in the E -R Model R Model ! Consider relationship set R between entity sets E 1 and E 2 . The relational schema for R includes the primary keys K 1 of E 1 and K 2 of E 2 . Then
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This document was uploaded on 01/10/2011.

Page1 / 29

ch6-2 - Chapter 6: Integrity and Security ! Domain...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online