Data Definition and Integrity Constraints

1
Data Definitionand Integrity Constraints

• Reading CB, Chap 7

2
In this lecture you will learn

• the different SQL data types related scalar
  functions
• how to define new data types with DDL statements
• some of the integrity constraints used in DBMSs
• SQL's Integrity Enhancement Features (IEF)
• how integrity constraints can affect row
  operations
• the notion of schemas

3
SQL's Integrity EnhancementFeatures (IEF)

• So far, we have thought of databases as static
  repositories. In fact, real databases are often
  very active with 100's of users simultaneously
  querying and updating the DB.
• So database integrity is important
• IEFs allow the DB designer to specify enforce
• domain constraints
• required data
• entity integrity
• referential integrity
• enterprise constraints (business rules)

4
Creating Tables - Data Definition

• CREATE TABLE is used to define relational tables
• it defines the data type for each column
• defines rules for how data may be inserted and
  deleted
• CREATE TABLE Staff (StaffNo VARCHAR(5),
• Lname VARCHAR(20),
• Salary FLOAT,
• HireDate DATE)
• VARCHAR, FLOAT, and DATE are examples of domains
• Domains specify type range of allowed data
  values

5
Built-in Data Types (Domains) inANSI SQL

• ANSI SQL supports many data types (vendors often
  also have own dialects)
• CHARACTER (CHAR), CHARACTER VARYING (VARCHAR)
• NUMERIC, DECIMAL (DEC), INTEGER (INT), SMALLINT
• FLOAT, REAL, DOUBLE PRECISION
• DATE, TIME, TIMESTAMP
• BOOLEAN, BIT
• BINARY LARGE OBJECT, etc.
• Some types have an associated size . e.g. CHAR(5)

6
User-Defined Domains in ANSI SQL

• CREATE DOMAIN SexType AS CHAR(1)
• DEFAULT 'M'
• CHECK (VALUE IN ('M', 'F'))
• CREATE TABLE Staff (StaffNo VARCHAR(5),
• Lname VARCHAR(20), Salary FLOAT,
• HireDate DATE, Sex SexType)
• INSERT INTO Staff VALUES
• ('S0057', 'Smith', 12075.50, '12-JAN-1990', 'F')
  . . OK
• INSERT INTO Staff VALUES
• ('S0023', 'Jones', 14250.50, '14-FEB-1997', 'X')
  . . Fails
• SexType acts as a constraint on allowed range of
  values

7
Required Data More Domain Constraints

• Example
• CREATE TABLE Staff (
• StaffNo VARCHAR(5) NOT NULL,
• Lname VARCHAR(20) NOT NULL,
• Salary FLOAT CHECK (Salary BETWEEN 50 and 20000),
• HireDate DATE,
• Sex SexType)
• StaffNo Lname are required - may not be NULL
• The CHECK clause gives a domain constraint for
  Salary
• Updates insertions will fail if constraints
  not satisfied

8
Dynamic Domain Constraints

• Domains may be defined dynamically using values
  that already exist in the database
• CREATE DOMAIN StaffNoDomain AS VARCHAR(5)
• CHECK (VALUE IN (SELECT StaffNo FROM Staff))
• CREATE TABLE PropertyForRent
• (PropertyNo VARCHAR(5) NOT NULL,
• StaffNo StaffNoDomain)
• This could be used to ensure every StaffNo in
  PropertyForRent is valid
• Domains can be deleted
• DROP DOMAIN DomainName RESTRICT CASCADE

9
Scalar Functions

• Scalar functions may be used to
  convert/manipulate data values (remember
  aggregates MIN, MAX, etc?).
• Example
• SELECT SUBSTRING(Lname FROM 1 TO 3),
• CONVERT(INTEGER Salary),
• EXTRACT(YEAR FROM HireDate)
• FROM Staff
• ANSI SQL supports many scalar functions...
• See CB, Table 6.2, p163

Result Result Result
SMI 12075 1990
10
Entity Integrity - Primary Keys

• Reminder the primary key of each row in a table
  must be unique and non-null.
• Example The primary key of the Viewing table is
  composed of two attributes (composite key)
• CREATE TABLE Viewing (
• ClientNo VARCHAR(5) NOT NULL,
• PropertyNo VARCHAR(5) NOT NULL,
• PRIMARY KEY (ClientNo, PropertyNo))
• SQL will reject operations that would violate
  primary key uniqueness
• Can use UNIQUE(Colname) to enforce uniqueness of
  alternate keys

11
Referential Integrity - Foreign Keys

• Reminders
• A foreign key links a child table to its parent
  table.
• If a foreign key is non-null, it must match an
  existing row in the parent table.
• So... SQL has more keywords for this
• CREATE TABLE PropertyForRent (...
• StaffNo VARCHAR(5) NOT NULL,
• FOREIGN KEY (StaffNo) REFERENCES Staff)
• SQL will reject operations that would violate
  referential integrity

12
Referential Integrityand Referential Actions

• Question if a key attribute in the parent table
  is modified, what should happen in the child
  table ? - SQL provides 4 alternative referential
  actions
• FOREIGN KEY (Key) REFERENCES Table ON DELETE
  UPDATE Action
• CASCADE - apply changes to child rows
• SET NULL - set child keys to NULL
• SET DEFAULT - set child keys to DEFAULT value
• NO ACTION - reject the operation (default)
• Suppose a client is removed from the DreamHome
  DBMS. What's the most appropriate action to
  specify for ClientNo in the Viewing table?

13
Enterprise Constraints(Business Rules)

• Sometimes, real-world business rules involve
  constraints that refer to more than one table.
  Its useful to define enterprise constraints just
  once.
• Example A member of staff may manage no more
  than 100 properties
• CREATE ASSERTION StaffNotOverLoaded
• CHECK (NOT EXISTS
• (SELECT StaffNo FROM PropertyForRent
• GROUP BY StaffNo HAVING COUNT () gt 100))
• CREATE TABLE PropertyForRent ( ...
• CONSTRAINT StaffNotOverLoaded)

14
Triggers

• Often, real-world business rules cannot be
  implemented using constraints.
• Example The branch manager is notified by e-mail
  if a client views more than 10 properties.
• Different DBMSs often provide a trigger
  mechanism
• Triggers may contain procedural code
  (if/then/else, function calls)
• Triggers can implement complex database
  operations
• However, triggers can add to database complexity
  (hidden rules)
• Triggers are not ANSI standard - should they be?

15
Putting It All Together - Schemas

• A schema is a collection of named DBMS objects
• Tables, Domains, Constraints, Views (later),
  Triggers, and more ...
• A multi-user DMBS may contain multiple schemas
• Each schema is owned by a given user
• A Database Administrator (DBA) manages schemas
  (CREATE, DROP)
• Schemas are maintained in special system tables
• However, different DBMSs have different ways of
  managing schemas...

16
Simplified Data Model of a DBMS
17
Database Schemas Evolve Over Time

• Ideally, a database is created once and then used
  for many years ... BUT
• The data model may be improved (integrity,
  performance) ...
• New features may be added in new releases ...
• Enterprise rules may change ...
• Therefore, SQL provides many options for changing
  tables
• See ALTER TABLE, CB Ch. 6, p172

18
Summary So Far...

• DBs are active or alive - contents always
  changing
• The structure of a DB can also evolve over
  time...
• DB contents should always be consistent -
  integrity
• ANSI SQL provides several Integrity Enhancement
  Features (IEFs)
• IEF gt domain constraints, entity/referential
  integrity, business rules...
• IEFs imply additional design choices for new DBs
• One DBMS can manage multiple DBs - notion of
  schemas privileges