Example

righteousgaggleΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 2 μήνες)

118 εμφανίσεις

2010
-
01
-
29

CO532 Database Systems

1

CO532 Database Systems



Lecture 4


SQL (3)

2010
-
01
-
29

CO532 Database Systems

2

SQL (3): this lecture


Data definition in SQL


CREATE TABLE


integrity constraints


mandatory data


domain constraints


entity integrity


referential integrity


enterprise constraints


Views

2010
-
01
-
29

CO532 Database Systems

3

Reminder: the example schema


Same schema as previous lectures: customers
-
products
-
orders




CUSTOMERS (
cid
, name, city, discount, contact)



PRODUCTS (
pid
, name, city, quantity, price)



ORDERS (
ordno
, month,
cid, pid,

qty)

2010
-
01
-
29

CO532 Database Systems

4









Data definition in SQL


Top level operation of creating a database depends
on the DBMS being used e.g. CREATE DATABASE in
PostgreSQL


Various database objects exist in a database
environment such as
tables, views, indexes,



created and destroyed by statements such as




CREATE TABLE
table_name
(
table_definition
)




DROP TABLE
table_name



the create table statement may include
integrity constraints

2010
-
01
-
29

CO532 Database Systems

5

CREATE TABLE


Basic syntax conforms to the ISO standard in all DBMS;
subclauses defining integrity constraints may vary



Example: To create the customers table, specify the




primary key , and apply not null constraints



CREATE TABLE customers (






cid


CHAR(4)



NOT NULL,



name

VARCHAR (20)

NOT NULL,



city


VARCHAR (10),



discount


REAL,




contact VARCHAR(24),





PRIMARY KEY (cid)



);





2010
-
01
-
29

CO532 Database Systems

6









Integrity constraints


Rules formulated by the database designer


May be specified as


Column constraints, as part of column specification


Table constraints, after all columns specified


Separate constraint statements


Can cover:


mandatory data


column value constraints


entity integrity


referential integrity


enterprise constraints


Some constraints may have to be specified using
database procedures and rules

2010
-
01
-
29

CO532 Database Systems

7


Constraints


Mandatory data


Specified using the NOT NULL clause in a column specification


Primary keys are, by definition, NOT NULL



CHECK constraint


used to constrain the domain (data type) values of columns



E
xample: To create a constraint on customer discount




discount REAL CHECK (discount
<= 15.00)




Example: To force column values to be from a specified set




college CHAR (1) CHECK (college IN(’D’,’E’,’K’,’R’))


2010
-
01
-
29

CO532 Database Systems

8


Constraints


DEFAULT constraint


Specified using the DEFAULT clause in a column specification



E
xample: To create a default value on customer discount




discount REAL
DEFAULT 0.0



UNIQUE constraint


used to ensure unique value in each row for non
-
primary keys



E
xample: To create a constraint on customer discount




note use of multiple constraints in this example




reg_no CHAR(7)
PRIMARY KEY,




chassis_no VARCHAR(32) NOT NULL UNIQUE





2010
-
01
-
29

CO532 Database Systems

9

Entity Integrity


Primary key: a unique identifier for each row


Entity integrity rule


no primary key (or part thereof) should have a null value



CREATE TABLE products (


pid

CHAR(3)

NOT NULL,


name

VARCHAR(10)

NOT NULL,


city

VARCHAR(10)

NOT NULL,


quantity

INTEGER,


price

REAL,


PRIMARY KEY (pid)


);


Primary keys can be composite



CREATE TABLE products (




pid


CHAR(3)

NOT NULL,



subprodno

CHAR(2)

NOT NULL,




name


VARCHAR(10),





:



PRIMARY KEY (prod, subprodno)



);



2010
-
01
-
29

CO532 Database Systems

10

Referential integrity (1)


Referential integrity rule


ensures integrity of primary and foreign keys used to
establish relationships between tables


the value of a foreign key must be either the value of an
existing primary key or null



REFERENCES clause in CREATE TABLE statement


Specifies foreign key columns in table and the related
(usually primary key) in the ‘parent’ table.


May specify action taken on changes to the ‘parent’ table


options are:



CASCADE


SET NULL


SET DEFAULT


NO ACTION

2010
-
01
-
29

CO532 Database Systems

11

Referential integrity (2)

CREATE TABLE orders


(ordno

INTEGER

NOT NULL,



month

CHAR (3)

NOT NULL,





cid

CHAR(4)

NOT NULL,







pid

CHAR(3)

NOT NULL,









PRIMARY KEY (ordno),




FOREIGN KEY (cid) REFERENCES customers (cid)








ON UPDATE CASCADE








ON DELETE CASCADE,







FOREIGN KEY (pid) REFERENCES products (pid)



);



By specifying NOT NULL for the foreign keys, we enforce a mandatory
relationship.

Exercise: what would be the result of:


deleting the record of the customer ‘c001’ in the customers table


2010
-
01
-
29

CO532 Database Systems

12

Enterprise Constraints


these are application dependent constraints that can
be implemented in different ways:



using
CHECK, DEFAULT, NOT NULL, UNIQUE …


clauses in the CREATE TABLE statement



using

rules, triggers



rules: enable actions when a table is accessed


triggers: enable actions to be performed when a table is
modified


more about rules later in the module

2010
-
01
-
29

CO532 Database Systems

13

VIEWS (1)


tables created with CREATE TABLE statements are
stored in the database and referred to as base tables



a view

is a
virtual
table


a view is a logical window on data from the base tables



the
SQL statements required to generate a view are
stored as a database object



no data is retrieved when a view is created, only when it is
used in a query statement



views


reduce query complexity


increase security by restricting access to parts of the
database


reflect changes to the base tables

2010
-
01
-
29

CO532 Database Systems

14

VIEWS (2)


To create a view of the agents in New York.




CREATE VIEW nycust AS




SELECT * FROM customers




WHERE city = ‘New York’;



When this view is used in a query, the system replaces
references to the view by the definition of the view.






SELECT *


FROM nycust


WHERE discount > 5.0;



2010
-
01
-
29

CO532 Database Systems

15

Summary, things to do …


Lectures 2
-
4 covered the basics of SQL



Read one of:


Connolly (4
th

edition):


chapter 5: SQL Data manipulation


chapter 6: SQL SQL Data definition


Date (8
th

edition):


Chapter 4: An introduction to SQL


Ryan & Smith


Chapter 14