程序代写代做代考 database SQL Overview

Overview

Recall, SQL provides:
Data Manipulation Language (DML)
Data Definition Language (DDL)
Data Control Language (DCL)

In this lecture, we will learn DDL – how to add, update, delete
database objects.
And constraints that enforce database integrity

SQL 4 : Table Definition and Modification

Table Creation

create table [table name] (

[attribute definition], …, [attribute definition],

[primary key definition],

[candidate key definition], …, [candidate key definition],

[foreign key definition], …, [foreign key definition])

Table Definition and Modification

Attribute Definition

[attribute name] [attribute type]

where the attribute type can be:
integer 32bit integer 2147483648 to 2147483647

double double precision number, e.g., 3.14159

char(n) where n is an integer of your choice. This defines a string
with at most n characters long.

many other types depend on the concrete database system.
In this course, we will mostly work with the above types only.

SQL 4 : Table Definition and Modification

create table PROF (

pid char(20),

name char(20),

dept char(20),

rank char(20),

sal integer)

Table Definition and Modification

Tuple Insertion

insert into [table name] values ([value 1], [value 2], · · · )

Example
insert into PROF values (’p1’, ’Adam’, ’CS’, ’asst’, ’6000’)

PROF

pid name dept rank sal
p1 Adam CS asst 6000

SQL 4 : Table Definition and Modification

Tuple Deletion

delete from T where P
T is a table name
P is a predicate (same as predicate in the where clause of an
SQL statement)

The statement removes all the tuples of T that satisfy P.

Example
delete from PROF where sal <= 8000 PROF pid name dept rank sal p1 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorpthy EE asst 5000 p5 Emily EE asso 8500 PROF pid name dept rank sal p3 Calvin CS full 10000 p5 Emily EE asso 8500 SQL 4 : Table Definition and Modification Tuple Update update T set A = v where P T is a table name A is an attribute and v is the new value of the attribute P is a predicate The statement updates the A values to v for all the tuples of T that satisfy P. Example: update PROF set salary = 6000 where salary = 5000 update PROF set salary = salary * 1.05 where salary <= 6000 Table Definition and Modification Updating the Table Alter: alter a table alter table PROF add column room integer; alter table PROF drop column room; Drop: remove a whole table drop table PROF; How is that different from? delete from PROF; SQL 4 : Table Definition and Modification Integrity and Validation Entity Integrity Contraint : Primary key Referential Integrity Constraint : Foreign key Validation SQL 4 : Table Definition and Modification Integrity and Validation Entity Integrity Contraint : Primary key Referential Integrity Constraint : Foreign key Validation SQL 4 : Table Definition and Modification Entity Integrity Constraint No component of the primary key of a base relation is allowed to accept nulls. NULL: information is missing for some reasons. a base relation: one which permanently exists in the database. a transient relation: temporary results (which is also a relation) of a query. every relation entities in the database should be uniquely identifiable (you do not want to be confused with someone else, do you?) SQL 4 : Table Definition and Modification Key constraint - revisit An attribute (or set of attributes, i.e. composite) K of a relation R is a candidate key for R if and only if it satisfies the following two time-independent properties: Uniqueness: At any given time, no two rows of R have the same value for K. Minimality: If K is composite, then no component of K can be eliminated without destroying the uniqueness property. (Otherwise, it is a superkey) Primary Key is chosen from candidate keys, rest are known as alternate keys. SQL 4 : Table Definition and Modification Primary Key in SQL A primary key for each table is defined through a constraint Primary Key also automatically adds UNIQUE and NOT NULL to the relevant column definition. Big hint to the DBMS: optimize for searches by this set of attributes! SQL 4 : Table Definition and Modification primary key([attribute list]) every table should have exactly one primary key create table PROF ( pid char(20), name char(20), dept char(20), rank char(20), sal integer, primary key (pid) ) SQL 4 : Table Definition and Modification Candidate Key Definition unique ([attribute list]) You can define as many candidate keys as you want. create table PROF ( pid char(20), name char(20), dept char(20), rank char(20), sal integer, primary key (pid), unique (name), unique (dept, rank)) Table Definition and Modification Integrity and Validation Entity Integrity Contraint : Primary key Referential Integrity Constraint : Foreign key Validation SQL 4 : Table Definition and Modification Recap PROF pid name dept rank sal p1 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 TEACH pid cid year p1 c1 2011 p2 c2 2012 p1 c2 2012 Natural Join ./ pid name dept rank sal cid year p1 Adam CS asst 6000 c1 2011 p2 Bob EE asso 8000 c2 2012 p1 Adam CS asst 6000 c2 2012 SQL 4 : Table Definition and Modification Delete Problem What if I delete the first tuple in PROF? PROF pid name dept rank sal p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 TEACH pid cid year p1 c1 2011 p2 c2 2012 p1 c2 2012 Natural Join ./ pid name dept rank sal cid year p2 Bob EE asso 8000 c2 2012 Information Loss What is p1 in TEACH refers to now? SQL 4 : Table Definition and Modification Update Problem What if I update the first tuple in PROF? PROF pid name dept rank sal p6 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 TEACH pid cid year p1 c1 2011 p2 c2 2012 p1 c2 2012 Natural Join ./ pid name dept rank sal cid year p2 Bob EE asso 8000 c2 2012 Information Loss What is p1 in TEACH refers to now? SQL 4 : Table Definition and Modification Referential Integrity Constraint The database must not contain any unmatched foreign key values. For every non-null value for a foreign key, there is a matching primary key SQL 4 : Table Definition and Modification Foreign Key Definition 1 foreign key (attribute list) references tablename (attribute list) The attributes in the attribute list must have the same types as those in the primary key in the table referenced. create table prof( pid char(20), name char(20), dept char(20), rank char(20), sal integer, primary key (pid) ); create table teach ( pid char(20), cid char(20), year integer, primary key (pid, cid), foreign key (pid) references prof (pid) ); SQL 4 : Table Definition and Modification The statements in the previous slide requires pid be declared either primary key or unique in PROF. does not allow the update/deletion of a tuple in PROF if it is referenced by a tuple in TEACH. Example: PROF pid name dept rank sal p1 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 TEACH pid cid year p1 c1 2011 p2 c2 2012 p1 c3 2012 The first two tuples of PROF cannot be deleted. ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails SQL 4 : Table Definition and Modification Another example employee’s DeptNo references to dept table. create table dept ( DeptNo integer not null, Name char(20) not null, Budget integer not null, primary key (DeptNo) }; create table employee ( EmpNo integer not null, LastName char(20) not null, FirstName char(20) not null, DeptNo integer not null, MgrNo integer, primary key ( EmpNo ), foreign key (DeptNo) references dept (DeptNo) ); DeptNo need not be a primary key in employee SQL 4 : Table Definition and Modification Self Referential Integrity create table employee ( EmpNo integer not null, LastName char(20) not null, FirstName char(20) not null, DeptNo integer not null, MgrNo integer, primary key (EmpNo), foreign key (MgrNo) references employee (EmpNo) ); Manager is also an employee SQL 4 : Table Definition and Modification Self Referential Integrity employee manager is an employee. What about managing director? null allowed in MgrNo foreign in employee. Employee EmpNo FirstName . . . MgrNo p5 Arnold (Head Of Department) . . . NULL p2 Stephen . . . p5 p7 Gary . . . p5 Find all who are being supervised. T 1← ρe1(employee)× ρe2(employee) Πe1.EmpNo,e1.FirstName,e2.FirstName(σe1.MgrNo=e2.EmpNo(T 1)) EmpNo e1.FirstName e2.FirstName p2 Stephen Arnold (Head Of Department) p7 Gary Arnold (Head Of Department) SQL 4 : Table Definition and Modification Reaction Policy What if we really want to modify a tuple in PROF which is referenced by some tuple in TEACH? We need to provide reaction policies for update/delete: Restrict Restrict to the case where there are no such matching entities (otherwise it is not carried out) [default]. Cascades The delete/update operation cascades to delete/update those matching entities. Nullifies The foreign key is set to null in all such matching entities and the item is then deleted/updated (should not apply if the foreign key cannot accept nulls in the first place – big problem) SQL 4 : Table Definition and Modification Foreign Key Definition 2 foreign key ([attribute list]) references [table name] on delete cascade If a referenced tuple is deleted, so are all the referencing tuples. create table PROF ( pid char(20), name char(20), dept char(20), rank char(20), sal integer, primary key (pid)) create table TEACH ( pid char(20), cid char(20), year integer primary key (pid, cid), foreign key (pid) references PROF on delete cascade) Table Definition and Modification Delete example foreign key (attribute list) references tablename (attribute list) on delete cascade PROF pid name dept rank sal p1 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 TEACH pid cid year p1 c1 2011 p2 c2 2012 p1 c2 2012 No inconsistency If the first tuple of PROF is deleted, so are the first and third tuples of TEACH. pid name dept rank sal p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 pid cid year p2 c2 2012 SQL 4 : Table Definition and Modification Similarly... for update foreign key (attribute list) references tablename (attribute list) on update cascade PROF pid name dept rank sal p1 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 TEACH pid cid year p1 c1 2011 p2 c2 2012 p1 c2 2012 No inconsistency If the first tuple of PROF is updated, so are the first and third tuples of TEACH. pid name dept rank sal p6 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 pid cid year p6 c1 2011 p2 c2 2012 p6 c2 2012 SQL 4 : Table Definition and Modification Note the asymmetry PROF pid name dept rank sal p1 Adam CS asst 6000 p2 Bob EE asso 8000 p3 Calvin CS full 10000 p4 Dorothy EE asst 5000 p5 Emily EE asso 8500 TEACH pid cid year p1 c1 2011 p2 c2 2012 p3 c3 2012 Suppose table R (e.g. TEACH) references table S (PROF). 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. SQL 4 : Table Definition and Modification Integrity and Validation Entity Integrity Contraint : Primary key Referential Integrity Constraint : Foreign key Validation SQL 4 : Table Definition and Modification Validation May involve extra constraints, for examples, Supplier numbers must be of the form Snnnn (where nnnn stands for up to four decimal digits); Part numbers must be of the form Pnnnnn (5 digits); Supplier status values must be in the range 1-100; Supplier and part cities must be drawn from a certain list; Part colours must be drawn from a certain list; Part weights must be greater than zero; Shipment quantities must be a multiple of 100; If the supplier city is London, then the status must be 20; implementable but database specific: check, trigger, procedure etc. http://www.w3schools.com/sql/sql_check.asp these commands are beyond the scope of this course SQL 4 : Table Definition and Modification http://www.w3schools.com/sql/sql_check.asp