Integrity and Constraints

1
Integrity and Constraints

• Integrityensuring that the database state
  satisfies all constraints after each update

2
Approaches

• One can place checks of integrity into each
  database application
• Usually better to have the DBMS make these checks
  internally, as it is generally
• safer
• easier to manage
• easier to modify
• Again, this is usually seen as a job of the DBA

3
Constraints

• SQL as a data definition language includes
  commands that declare constraints
• Current SQL standard dictates that integrity
  constraints are specified in the create table
  command
• Constraints may be assigned a name, which is used
  in error reporting when an update would
  contravene a constraint
• Constraints are specified in a non-procedural way
• Key constraints maintain referential integrity of
  a database

4
Key Constraint

• Declare that a column of a relation is not to
  have duplicate or null values
• create table Part (
• PartNo integer unique not null,Description char
  (20),Weight integer,Colour char(10),
• Instock integer
• )

5
Alternative Syntax

• create table Part (
• PartNo integer primary key,Description char(20)
  ,Weight integer,Colour char(10),
• Instock integer
• )
• Note that some systems do not enforce primary key
  unless an index is present on that attribute

6
Alternative Syntax

• create table Part (
• PartNo integer
• constraint pk_Part primary key,Description char(
  20),Weight integer,Colour char(10),
• Instock integer
• )
• In this case the constraint is assigned a name
  which becomes available in error reporting

7
Simple Constraints

• SQL92 allows flexible expression of a check
  constraint, a condition which must not be false
  for any row of a table
• create table Part (...,
• Instock integer,check (weight gt 0 and Instock
  gt0)
• )
• Not all DBMS enforce check conditions

8
Advanced Constraints

• Use a subquery in check to express more complex
  constraints
• check (
• Instock lt (
• select sum(Maxqty)
• from Availability
• where
• Availability.PartNo PartNo)
• )

9
Advanced Constraints

• Define a multi-attribute primary key
• create table Availability (PartNo integer,Supp
  lierId integer,Price integer,Maxqty integer
  ,
• constraint avail_pk
• primary key (PartNo, SupplierId)
• )

10
Advanced Constraints

• Instead of defining constraints in a table
  declaration, an assertion can be declared on its
  own
• create assertion no_overload_256
• check (
• (select sum (P.Weight A.Maxqty)
• from Part P, Availability A
• where A.PartNo P.PartNo
• and A.SupplierId 256) lt 1000
• )

11
Foreign Key Constraint

• An attribute is a foreign key for a relation if
  every value it assumes occurs in an attribute of
  another relation
• it usually has the same name in both relations
• it is usually a primary key in the other relation
• create table Availability (...constraint
  supp_fkforeign key (SupplierId)references
  Supplier(SupplierId)
• )

12
Triggers

• Describe an action to take when an event takes
  place in a database
• events like insert, delete, update
• more powerful than constraints
• procedural (triggered procedures)
• not in SQL92, but present in some systems
• will be standardised in SQL3

13
(No Transcript)
14
Database Design is Difficult

• Michael Stonebraker () presents 6 main reasons
  why people have trouble with database design
• difficulty in tuning the schema
• unwillingness to iterate the schema
• unrealistic application design
• failure to load and test a realistic database
  early
• difficulty in capturing the rest of the schema
• excessive focus on formal modelling
• ( Object-Relational DBMSs - The Next Great Wave
• - Stonebraker Moore, Morgan Kaufmann 1996)

15
Performance Tuning the Schema

• Find all the employees on the first floor
• Consider the normalised model where there is a
  separate employee and department relation, with
  the floor being an attribute of the department
  relation
• This form would require a join
• Alternative is to denormalise and include the
  department dependent floor attribute in the
  employee relation
• Deciding between the options requires expertise
  in the underlying DBMS and the applications

16
Iterate the Schema

• Database schema design is an iterative process
• Changes in the schema usually requires
  maintenance of applications
• It is usually a mistake to freeze the schema
  early in an attempt to save flow-on changes to
  applications
• Mistakes in the schema usually cost more in the
  long run
• Expect schema design to consume significant
  resources (10-20 of a projects budget)

17
Realistic Application Design

• Choose the most appropriate site for your data
  processing
• For example, input checking may best be done
  within the data entry application, rather than by
  the use of complex constraints or triggers
• DBMS features usually incur a cost which has to
  be weighed up against any potential advantage

18
Set Realistic Performance Goals

• A common mistake is to fail to test prototypes of
  the system with realistic data sets
• Load a large database early
• Test mock-ups of the transactions you expect to
  be most common and/or most critical
• Identifying performance problems early gives you
  some chance of rectifying the them

19
Capture the Business Rules

• An application must enforce a wide variety of
  business rules in most application domains
• Extracting the rules from the users can be
  difficult
• Knowing how to react to breaches of the rules can
  be difficult

20
Focus on Formal Modelling

• A large range of methodologies and accompanying
  tools exist for database design and database
  application analysis
• Finding the right tool for the job can be a
  difficult task in itself
• Dont let the search for a modelling tool or
  methodology distract you from design for too long

21
Evaluation of Relational Designs

• A good design of a database schema
• avoids anomalies, which are possibilities for
  inconsistent update
• avoids unnecessary redundancy, where information
  is repeated
• These concepts are formalised by requiring that a
  relational schema be in normal form
• Sensible Entity-Relationship design usually leads
  to a good relational design

22
A Bad Example

• Supplies(PartNo, SuppName, Address)
• redundancy address is repeated for each part
  supplied
• update anomaly an address can be changed for
  one part, but not for all from that supplier
• deletion anomaly if a supplier supplies no
  parts at the moment, the name and address are lost

23
A Bad Example
Supplies
24
A Better Example

• Associate the attributes which describe
  individual suppliers together into one relation
• Model the act of supply in a separate relation
• Supplier(SuppName, Address)
• Supplies(PartNo, SuppName)

25
A Better Example
Aside What Relational Algebraic
operation gets us from the bad example to the
better example?
Supplies
Supplier
26
Evaluation of Relational Designs

• A good design
• avoids anomalies, which are possibilities for
  inconsistent update
• avoids unnecessary redundancy, where information
  is repeated
• These concepts are formalised by requiring that a
  relational schema be in normal form
• Sensible E-R design usually leads to a good
  relational design

27
(No Transcript)
28
(No Transcript)