1 / 19

Database Integrity

Database Integrity. DBMS Spring 2014. Sources: Security in Computing, Pfleeger and Pfleeger, Prentice Hall, 2003 Lecture Slides, CSE6243, MSU, Rayford B. Vaughn, 2005 http://www.postgresql.org/docs/9.0/static/ddl-constraints.html. First, a followup from Tues. R(A, B, C, D, E) with FDs

suzuki
Download Presentation

Database Integrity

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Integrity DBMS Spring 2014 Sources: Security in Computing, Pfleeger and Pfleeger, Prentice Hall, 2003 Lecture Slides, CSE6243, MSU, Rayford B. Vaughn, 2005 http://www.postgresql.org/docs/9.0/static/ddl-constraints.html

  2. First, a followup from Tues • R(A, B, C, D, E) with FDs • AB -> C • C -> D • A -> E • What violations of 3nf are found? • How would you decompose this?

  3. Followup from Tues • R(A, B, C, D) with FDs • AB -> C • C -> D • D -> A • What are the possible keys to this relation? • What violations of 3nf are found? • How would you decompose this? Book, page 92, 3.3.5.1

  4. Traditional Security Concerns • Confidentiality • Integrity • Availability • Accountability

  5. Traditional Security Concerns • Confidentiality • Integrity • Availability • Accountability

  6. Integrity • DB integrity types • Physical Database Integrity • Data in Database is safe • Data can be reconstructed • Logical Database Integrity • Structure of Database is preserved • No inconsistency • Element Integrity • Correctness/accuracy of data is maintained

  7. Integrity Goals • Database integrity protection must achieve: • Internal consistency: database entries must obey prescribed rules; • External consistency: database entries are correct - combine checking data entered with auditing to check consistent state.

  8. Integrity Rules • Most rules are application specific, but two rules inherent to relational database model: • Entity integrity rule: • no component of primary key can accept nulls. • Referential integrity rule: • database must not contain unmatched foreign key values.

  9. Application Specific Integrity Checks • field checks: check that entries are valid elements of domain • consistency checks: check that entries in different relations do not conflict • scope checks: checks that query results are not computed for too small a sample (for specialized DB) • change logs: check that all changes are recorded with original and modified entry values

  10. Integrity in postresql • Primary keys • Must be unique • Must be non-null • Foreign keys - http://www.postgresql.org/docs/8.1/static/tutorial-fk.html • Field can be null or not-null • If null, we can have a row without the foreign key attribute present, but if the attribute is filled in, it must match a row in the referenced table.

  11. Thinking about foreign keys • If a Foreign Key references another table and the other table row is deleted, what should happen to the referencing row. • For example, dlangs references languages. What if we decide that a language really isn’t a language and so we remove it. ie. PigLatin. What should happen? • No Action / Restrict • On Delete Cascade • On Update Cascade (what happens if we change)

  12. Other constraints • “Table constraints” • Field contraints • Can be built into the CREATE TABLE OR • Can be added using the ALTER TABLE

  13. Other constraints • Null is assumed for a non-key attribute added to a table. • NOT NULL requires a value to be filled in. • SSN int NOT NULL, • UNIQUE requires that no other row can have the same value. • SSN int UNIQUE NOT NULL,

  14. Field vs Table levelfrom Postgresql reference CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric ); CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) ); CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) );

  15. Check Constraints • Allow us to more finely define field values beyond the overall domain (data type) • For example, can a driver have a vehicle with -5 seats? Can a driver have a vehicle with 192 seats? What is the restriction to insure that we have a reasonable number. CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );

  16. Named constraints CREATE TABLE products ( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price > 0) ); Table level CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );

  17. Adding constraints • ALTER TABLE • Can add table level constraints.

  18. Default values CREATE TABLE distributors ( name varchar(40) DEFAULT 'Luso Films', did integer DEFAULT nextval('distributors_serial'), modtime timestamp DEFAULT current_timestamp );

More Related