data definition and integrity constraints l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Data Definition and Integrity Constraints PowerPoint Presentation
Download Presentation
Data Definition and Integrity Constraints

Loading in 2 Seconds...

play fullscreen
1 / 18

Data Definition and Integrity Constraints - PowerPoint PPT Presentation


  • 184 Views
  • Uploaded on

Data Definition and Integrity Constraints. Reading: C&B, Chap 6. 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

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 'Data Definition and Integrity Constraints' - urvi


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
in this lecture you will learn
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

Dept. of Computing Science, University of Aberdeen

sql s integrity enhancement features ief
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)

Dept. of Computing Science, University of Aberdeen

creating tables data definition
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

Dept. of Computing Science, University of Aberdeen

built in data types domains in ansi sql
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)

Dept. of Computing Science, University of Aberdeen

user defined domains in ansi sql
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

Dept. of Computing Science, University of Aberdeen

required data more domain constraints
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

Dept. of Computing Science, University of Aberdeen

dynamic domain constraints
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]

Dept. of Computing Science, University of Aberdeen

scalar functions
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

Dept. of Computing Science, University of Aberdeen

entity integrity primary keys
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

Dept. of Computing Science, University of Aberdeen

referential integrity foreign keys
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

Dept. of Computing Science, University of Aberdeen

referential integrity and referential actions
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?

Dept. of Computing Science, University of Aberdeen

enterprise constraints business rules
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 (*) > 100));

CREATE TABLE PropertyForRent ( ...

CONSTRAINT StaffNotOverLoaded);

Dept. of Computing Science, University of Aberdeen

triggers
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?

Dept. of Computing Science, University of Aberdeen

putting it all together schemas
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...

Dept. of Computing Science, University of Aberdeen

simplified data model of a dbms
Simplified Data Model of a DBMS

Dept. of Computing Science, University of Aberdeen

database schemas evolve over time
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

Dept. of Computing Science, University of Aberdeen

summary so far
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 => 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

Dept. of Computing Science, University of Aberdeen