Database integrity
Download
1 / 19

Database Integrity - PowerPoint PPT Presentation


  • 305 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Database Integrity ' - suzuki


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
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?


Followup from tues
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


Traditional security concerns
Traditional Security Concerns

  • Confidentiality

  • Integrity

  • Availability

  • Accountability


Traditional security concerns1
Traditional Security Concerns

  • Confidentiality

  • Integrity

  • Availability

  • Accountability


Integrity
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


Integrity goals
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.


Integrity rules
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.


Application specific integrity checks
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


Integrity in postresql
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.


Thinking about foreign keys
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)


Other constraints
Other constraints

  • “Table constraints”

  • Field contraints

  • Can be built into the CREATE TABLE OR

  • Can be added using the ALTER TABLE


Other constraints1
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,


Field vs table level from postgresql reference
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) );


Check constraints
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) );


Named constraints
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) );


Adding constraints
Adding constraints

  • ALTER TABLE

  • Can add table level constraints.


Default values
Default values

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


ad