integrity & security 1

integrity & security 1 - Chapter 6: Integrity and...

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

View Full Document Right Arrow Icon
©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
Background image of page 1

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

View Full DocumentRight Arrow Icon
©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 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 ))
Background image of page 3

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

View Full DocumentRight Arrow Icon
©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 4
©Silberschatz, Korth and Sudarshan 6.5 Database System Concepts Referential Integrity in the E-R Model Referential Integrity in the E-R Model Consider relationship set R between entity sets E 1 and E 2 . The relational schema for R includes the primary keys
Background image of page 5

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

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

This note was uploaded on 10/25/2010 for the course CMPN SEM4 taught by Professor Thakur during the Spring '07 term at University of Mumbai.

Page1 / 60

integrity & security 1 - Chapter 6: Integrity and...

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

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