CS计算机代考程序代写 python Java SQL database SQL:

SQL:
Data Definition Language
Author: Diane Horton

Types

Table attributes have types
• When creating a table, you must define the type of each attribute.
• Analogous to declaring a variable’s type in a program. Eg, “int num;” in Java or C.
• Some programming languages don’t require type declarations. Eg, Python.
• Pros and cons?
• Why are type declarations required in SQL?
3

Built-in types
•CHAR(n): fixed-length string of n characters. Padded with blanks if necessary.
•VARCHAR(n): variable-length string of up to n characters.
•TEXT: variable-length, unlimited. Not in the SQL standard, but psql and others support it.
•INT =INTEGER •FLOAT =REAL
• BOOLEAN
• DATE; TIME; TIMESTAMP (date plus time)
4

Values for these types
• Strings: ‘Shakespeare’’s Sonnets’ Must surround with single quotes.
•INT: 37
•FLOAT: 1.49, 37.96e2
•BOOLEAN: TRUE, FALSE
•DATE: ‘2011-09-22’
• TIME: ’15:00:02’, ’15:00:02.5’
•TIMESTAMP: ‘Jan-12-2011 10:25’
5

And much more
• These are all defined in the SQL standard. • There is much more, e.g.,
• specifying the precision of numeric types • other formats for data values
• more types
• For what psql supports, see chapter 8 of the documentation.
6

User-defined types
• Defined in terms of a built-in type.
• You make it more specific by defining constraints (and perhaps a default value).
• Example:
create domain Grade as int
default null
check (value>=0 and value <=100); create domain Campus as varchar(4) default ‘StG’ check (value in (‘StG','UTM','UTSC')); 7 Semantics of type constraints • Constraints on a type are checked every time a value is assigned to an attribute of that type. • You can use these to create a powerful type system. 8 Semantics of default values • The default value for a type is used when no value has been specified. • Useful! You can run a query and insert the resulting tuples into a relation -- even if the query does not give values for all attributes. • Table attributes can also have default values. • The difference: • attribute default: for that one attribute in that one table • type default: for every attribute defined to be of that type 9 Keys and Foreign Keys Key constraints • Declaring that a set of one or more attributes are the PRIMARY KEY for a relation means: • they form a key (unique and no subset is) • their values will never be null (you don’t need to separately declare that) • Big hint to the DBMS: optimize for searches by this set of attributes! • Every table must have 0 or 1 primary key. • A table can have no primary key, but in practise, every table should have one. Why? • You cannot declare more than one primary key. 11 Declaring primary keys • For a single-attribute key, can be part of the attribute definition. create table Blah ( ID integer primary key, name varchar(25)); • Or can be at the end of the table definition. (This is the only way for multi-attribute keys.) The brackets are required. create table Blah ( ID integer, name varchar(25), primary key (ID)); 12 Uniqueness constraints • Declaring that a set of one or more attributes is UNIQUE for a relation means: • they form a key (unique and no subset is) • their values can be null; if they mustn’t, you need to separately declare that • You can declare more than one set of attributes to be UNIQUE. 13 Declaring UNIQUE • If only one attribute is involved, can be part of the attribute definition. create table Blah ( ID integer unique, name varchar(25)); • Or can be at the end of the table definition. (This is the only way if multiple attributes are involved.) The brackets are required. create table Blah ( ID integer, name varchar(25), unique (ID)); 14 We saw earlier how nulls affect “unique” • For uniqueness constraints, no two nulls are considered equal. • E.g., consider: create table Testunique ( first varchar(25), last varchar(25), unique(first, last)) • This would prevent two insertions of ('Diane', 'Horton') • But it would allow two insertions of (null, 'Schoeler') This can’t occur with a primary key. Why not? 15 Foreign key contraints • Eg in table Took: foreign key (sID) references Student • Means that attribute sID in this table is a foreign key that references the primary key of table Student. • Every value for sID in this table must actually occur in the Student table. • Requirements: • Must be declared either primary key or unique in the “home” table. 16 Declaring foreign keys • Again, declare with the attribute (only possible if just a single attribute is involved) or as a separate table element. • Can reference attribute(s) that are not the primary key as long as they are unique; just name them. create table People ( SIN integer primary key, name text, OHIP text unique); create table Volunteers ( email text primary key, OHIPnum text references People(OHIP)); 17 Enforcing foreign-key constraints • Suppose there is a foreign-key constraint from relation R to relation S. • When must the DBMS ensure that: • the referenced attributes are PRIMARY KEY or UNIQUE? • the values actually exist? • What could cause a violation? • You get to define what the DBMS should do. • This is called specifying a “reaction policy.” 18 SQL DDL Worksheet Q1-4 Other Constraints and Assertions “check” constraints • We’ve seen a check clause on a user-defined domain: create domain Grade as smallint default null check (value>=0 and value <=100); • You can also define a check constraint: 1. On a column 2. On the rows of a table 3. Across tables 21 1. Column-based “check” constraints • Defined with a single column and constrain its value (in every row). • Can only refer to that column. • Example: create table Application ( sID integer, previousAppointments integer check (previousAppointments >= 0)
);
22

Column-based “check” constraints
• Condition can be anything that could go in a WHERE clause.
• Can include a subquery.
Example:
create table Student (
sID integer,
program varchar(5) check
program in (select post from P),
firstName varchar(15) not null, …);
• Use of a subquery is not supported by psql.
23

When they are checked
• Only when a tuple is inserted into that relation, or its value for that attribute is updated.
• If a change somewhere else violates the constraint, the DBMS will not notice. E.g.,
• If a student’s program changes to something not in table P, we get an error.
• But if table P drops a program that some student has, there is no error.
• This is only an issue if the constraint involves a subquery.
24

“not null” constraints
• You can declare that an attribute of a table is NOT
NULL.
create table Course(
cNum integer,
name varchar(40) not null,
dept Department,
wr boolean,
primary key (cNum, dept));
• In practise, many attributes should be not null.
• This is a very specific kind of attribute-based constraint.
25

2. Row-based “check” constraints
• Defined as a separate element of the table schema,
so can refer to any column of the table.
• Again, condition can be anything that could go in a
WHERE clause, and can include a subquery.
• Example:
create table Student (
sID integer,
age integer, year integer,
college varchar(4),
check (year = age – 18),
check college in
(select name from Colleges));
26

When they are checked
• Only when a tuple is inserted into that relation, or updated.
• Again, if a change somewhere else violates the constraint, the DBMS will not notice.
27

How nulls affect “check” constraints
• A check constraint only fails if it evaluates to false. • It is not picky like a WHERE condition.
• E.g.: check (age > 0)
age
Value of condition
CHECK outcome
WHERE outcome
19
TRUE
pass
pass
-5
FALSE
fail
fail
NULL
unknown
pass
fail
28

Example
• Suppose you created this table:
create table Frequencies(
word varchar(10),
num integer,
check (num > 5));
• It would allow you to insert (‘hello’, null) since null passes the constraint check (num > 5)
• If you need to prevent that, use a “not null”
constraint.
create table Frequencies(
word varchar(10),
num integer not null,
check (num > 5));
29

Naming your constraints
• If you name your constraint, you will get more helpful error messages.
• This can be done with any of the types of constraint we’ve seen.
• Add
constraint «name» before the
check («condition»)
30

Examples
create domain Grade as smallint
default null
constraint gradeInRange
check (value>=0 and value <=100)); create domain Campus as varchar(4) not null constraint validCampus check (value in ('StG', 'UTM', 'UTSC')); create table Offering(... constraint validCourseReference foreign key (cNum, dept) references Course); 31 3. Cross-table constraints: Assertions • Check constraints can’t express complex constraints across tables, e.g., • Every loan has at least one customer, who has an account with at least $1,000. • For each branch, the sum of all loan amounts < the sum of all account balances. • Assertions are schema elements at the top level, so can express cross-table constraints: create assertion () check ();
32

Powerful but costly
• SQL has a fairly powerful syntax for expressing the predicates, including quantification.
• Assertions are costly because
• They have to be checked upon every database update (although a DBMS may be able to limit this).
• Each check can be expensive.
• Testing and maintenance are also difficult.
• Assertions are not supported by PostgreSQL
(or by most/all other DBMSs).
33

Triggers
• Assertions are powerful, but costly.
• Check constraints are less costly, but less powerful.
• Triggers are a compromise between these extremes:
• They are powerful.
• But you control the cost by having control over when they are applied.
34

The basic idea
• You specify a type of database event that you want to respond to, e.g.,
after delete on Courses or before update of grade on Took
• You specify the response, e.g.,
insert into Winners values (sID)
• The response is wrapped up in a function.
35

Example: the function
create function RecordWinner() returns trigger
as
$$ BEGIN
IF NEW.grade >= 85 THEN
INSERT INTO Winners VALUES (NEW.sid);
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
36

Example: the trigger
create trigger TookUpdate
before insert on Took
for each row
execute procedure RecordWinner();
37

Reaction Policies

Example
• Suppose R = Took and S = Student.
• What sorts of action must simply be rejected?
• But a deletion or update with an sID that occurs in Took could be allowed …
39

Possible policies
•cascade: propagate the change to the referring table
•set null: set the referring attribute(s) to null
• There are other options we won’t cover. Many DBMSs don’t support all of them.
• If you say nothing, the default is to forbid the change in the referred-to table.
40

Note the asymmetry
• Suppose table R refers to table S.
• You can define “fixes” that propogate changes backwards from S to R.
• (You define them in table R because it is the table that will be affected.)
• You cannot define fixes that propogate forward from R to S.
41

Syntax for specifying a reaction policy
• Add your reaction policy where you specify the foreign key constraint.
• Example:
create table Took (

foreign key (sID) references Student
… );
on delete cascade
42

What you can react to
• Your reaction policy can specify what to do either
•on delete, i.e., when a deletion creates a dangling reference,
•on update, i.e., when an update creates a dangling reference,
• or both. Just put them one after the other. Example:
on delete restrict on update cascade
43

What your reaction can be
• Your policy can specify one of these reactions (there are others):
•restrict: Don’t allow the deletion/update.
•cascade: Make the same deletion/update in the referring
tuple.
•set null: Set the corresponding value in the referring tuple to null.
44

Semantics of Deletion
• Suppose we are deleting a bunch of rows and: • Deleting one row violates a foreign key constraint. • But deleting others does not.
• The DBMS could.
• Halt at the error but keep any earlier deletes.
• Roll back the earlier deletes and make none at all. • Make all the deletes except the violating ones.
• This likely varies across DBMSs.
• If you assume a particular behaviour, you may lose portability.
45

Semantics of Deletion
• What if deleting one tuple affects the outcome for a tuple encountered later?
• To prevent such interactions, deletion proceeds in two stages:
• Mark all tuples for which the WHERE condition is satisfied.
• Go back and delete the marked tuples.
46

Reaction Policies Worksheet Parts c) and d)

DDL Wrap-up

Updating the schema itself
• Alter: alter a domain or table
alter table Course
add column numSections integer;
alter table Course
drop column breadth;
• Drop: remove a domain, table, or whole schema
drop table course;
• How is that different from this?
delete from course;
• If you drop a table that is referenced by another table, you must specify “cascade”.
• This removes all referring rows.
49

There’s more to DDL
• For example, you can also define:
• indices: for making search faster (we’ll discuss these later).
• privileges: who can do what with what parts of the database
• See csc443.
50