Physical Implementation:

1
Physical Implementation

• Objectives
• Role, structure of SQL data definition language
• Database enforcement of the data model
  (constraints)
• culminating in cascade delete.

2
Data Modeling In the Context of Database Design

• 1. planning and analysis
• 2. conceptual design
• 3. logical design
• 4. physical design
• 5. implementation

3
SQL

• Structured Query Language is composed of three
  parts
• DDL Data Definition Language
• DML Data Manipulation Language
• DQL Data Query Language
• ANSI-SQL Standard keeps evolving
• SQL89 (SQL1)
• SQL92 (SQL2)
• SQL99 (SQL3)

4
Data Query Language (DQL)

• used to retrieve data from the database.
• Select
• From
• Where

5
Data Manipulation Language (DML)

• the language used to change the contents of a
  database.
• Insert http//www.w3schools.com/SQL/sql_insert.a
  sp
• Delete http//www.w3schools.com/SQL/sql_delete.asp
  
• Update http//www.w3schools.com/SQL/sql_update.asp

6
Data Definition Language (DDL)

• the language used to specify the physical
  structuring of a database.

7
SQL DDL

• Define tables
• attribute/column names
• data types
• Define indexes (// well come back to this)
• primary
• secondary
• index (search) keys
• Constraints
• enable the DBMS itself to keep the data in the
  database correct

8
Tables

• CREATE TABLE lttable namegt (
• ltattribute name 1gt ltdata type 1gt,
• ...
• ltattribute name ngt ltdata type ngt)

CREATE TABLE customers ( cfirstname
VARCHAR(20), clastname
VARCHAR(20), cphone
VARCHAR(20), cstreet
VARCHAR(50), czipcode
VARCHAR(5))
9
Attribute data types

• See handout

10
NULL

• SQL reserved word // never use reserved
• // words for names
• meaning no data value

11
Specifying the Primary Index

• If primary key is one attribute
• CREATE TABLE t_test
• (a INTEGER PRIMARY KEY,
• b VARCHAR(10)
• )
• If primary key is a number of attributes
  (composite key).
• CREATE TABLE track(
• album CHAR(10),
• dsk INTEGER,
• posn INTEGER,
• song VARCHAR(255),
• PRIMARY KEY (album, dsk, posn)
• )

12
Specifying Secondary Indexes

• CREATE INDEX ltindexnamegt ON lttablenamegt
  (ltcolumngt, ltcolumngt...)

13
Constraints

• Declare certain properties of the data (in the
  model)
• DBMS disallows ill-formed data
• An example where data model properties turn into
  executable code.
• When does it execute?

14
Not Null Primary Key Constraints

• Null value ---gt empty
• CREATE TABLE track(
• album CHAR(10) NOT NULL,
• dsk INTEGER NOT NULL,
• posn INTEGER NOT NULL,
• song VARCHAR(255),
• PRIMARY KEY (album, dsk, posn)
• )
• Primary key ---gt NOT NULL usually unique

15
Not Null

• Null value ---gt empty
• Domain semantics
• E.g. Date of birth, gender, zip-code
• Function of the database, keys
• What if a foreign key is undefined?

16
Primary Key Constraint

• ALTER TABLE lttable namegt
• ADD CONSTRAINT ltconstraint namegt PRIMARY
  KEY (ltattribute listgt)
• Alter - update a table after it is defined
• Primary key constraint
• Attributes cant be null
• Value is unique

17
Foreign Key Constraint References

• In relation R
• "attribute A references S ( B )"
• values in the A column of R must
• uniquely appear
• in the B column of relation S .
• (Oracle) B must be declared the primary key for S
  .

18
Why declare this to the database?

• declare (inform and enforce)
• performance
• join example - on the board
• correctness

19
Example

• CREATE TABLE Beers ( name CHAR(20) PRIMARY
  KEY, manf CHAR(20))CREATE TABLE Sells (
  bar CHAR(20), beer CHAR(20) REFERENCES
  Beers(name), price REAL)

20
Alternative

• Add another element declaring the foreign key,
  asCREATE TABLE Sells ( bar CHAR(20),
  beer CHAR(20), price REAL, FOREIGN KEY
  beer REFERENCES Beers(name))
• Extra element essential if the foreign key is
  more than one attribute.

21
Foreign Key Constraint

• ALTER TABLE lttable namegt
• ADD CONSTRAINT ltconstraint namegt FOREIGN
  KEY (ltattribute listgt)
• REFERENCES ltparent table namegt
  (ltattribute listgt)
• Make primary key constraints ltparent table
  namegt
• When a row is added to lttable namegt
• Check ltparent table namegt for the key value
• If value is missing, disallow the insert.
• What happens to children if a parent row is
  deleted?
• Cascade delete

22
Exceptions

• Two ways
• 1. Insert a Sells tuple referring to a
  nonexistent beer.
• Always rejected.
• 2. Delete or update a Beers tuple that has a beer
  value some Sells tuples refer to.
• a) Default reject.
• b) Cascading delete Ripple changes to referring
  Sells tuple.

23
Example

• Delete "Bud." Cascade deletes all Sells tuples
  that mention Bud.
• Update "Bud" ? "Budweiser." Change all Sells
  tuples with "Bud" in beer column to be
  "Budweiser."

24
(beyond scope of course)What Happens On a
Foreign Key Exception

• c) Set Null Change referring tuples to have
  NULL in referring components.
• Example
• Delete "Bud." Set-null makes all Sells tuples
  with "Bud" in the beer component have NULL there.
• Update "Bud" ? "Budweiser." Same change.

25
(beyond scope of course) Selecting a Policy

• Add ON DELETE, UPDATE CASCADE, SET NULL to
  declaration of foreign key.

26
(beyond scope of course) Selecting a Policy (II)

• ExampleCREATE TABLE Sells (bar CHAR(20),beer
  CHAR(20),price REAL,FOREIGN KEY beer
  REFERENCESBeers(name)ON DELETE SET NULLON
  UPDATE CASCADE)

27
(beyond scope of course) Selecting a Policy (III)

• "Correct" policy is a design decision.
• E.g., what does it mean if a beer goes away? What
  if a beer changes its name?

28
beyond scope of course - but shouldnt be
Attribute-Based Checks

• Follow an attribute by a condition that must hold
  for that attribute in each tuple of its relation.
• Form CHECK (condition).
• Condition may involve the checked attribute.
• Other attributes and relations may be involved,
  but only in subqueries.
• Oracle 7.3.2 No subqueries allowed in condition.

29
Attribute-Based Checks

• Condition is checked only when the associated
  attribute changes (i.e., an insert or update
  occurs).

30
Example

• CREATE TABLE Sells ( bar CHAR(20), beer
  CHAR(20) CHECK( beer IN (SELECT name
  FROM Beers) // much like fk constraint
  ), price REAL CHECK( price lt 5.00
  ))

31
Attribute-Based Checks (III)

• Check on beer is like a foreign-key constraint,
  except
• The check occurs only when we add a tuple or
  change the beer in an existing tuple, not when we
  delete a tuple from Beers.

32
Tuple-Based Checks

• Separate element of table declaration.
• Form like attribute-based check.
• But condition can refer to any attribute of the
  relation.
• Or to other relations/attributes in subqueries.
• Again Oracle 7.3.2 forbids the use of subqueries.

33
Example

• Only Joe's Bar can sell beer for more than
  5.CREATE TABLE Sells ( bar CHAR(20),
  beer CHAR(20), price REAL, CHECK(bar
  'Joe''s Bar' OR price lt 5.00))

34
(beyond scope of course) Triggers

• Often called event-condition-action rules.
• Event a class of changes in the DB, e.g.,
  "insert into Beers."
• Condition a test as in a where-clause for
  whether or not the trigger applies.
• Action one or more SQL statements.

35
Other types of constraints

• simple numeric tests
• e.g. zip-codes 0 - 99999
• enumerated data types
• e.g. two letter state abbreviations TX, NY, CA
• minimal support in data modeling tools so far

36
Lets see what Rational Rose does