database integrity n.
Download
Skip this Video
Download Presentation
Database Integrity

Loading in 2 Seconds...

play fullscreen
1 / 19

Database Integrity - PowerPoint PPT Presentation


  • 308 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


Download Now 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 );