Data Definition Creating a Relation数据定义创建一个关系.pptVIP

Data Definition, Relational Manipulation and Data Control Using SQL Languages of DBMS Data Definition Language DDL define the logical schema (relations, views etc) and storage schema stored in a Data Dictionary Data Manipulation Language DML Manipulative populate schema, update database Retrieval querying content of a database Data Control Language DCL permissions, access control etc... Data Definition:Creating tables create table accountants as(select studno, name, tutor, year from student where hons = ‘ca’); Can specify column names, default values and integrity constraints (except referential) Datatypes and lengths derived from query Not null constraints passed on from query tables Defining a Relation create table student (studentno number(8) primary key, givenname char(20), surname char(20), hons char(3) check (hons in (cis,cs,ca,pc,cm,mcs)), tutorid number(4), yearno number(1) not null, constraint year_fk foreign key (yearno) references year(yearno), constraint super_fk foreign key (tutorid) references staff(staffid)); Data Definition: Create Table create table enrol(studno number(8),courseno char(5),primary key (studno, courseno),cluster (studno),labmark number(3)  check (labmark between 0 and 100),exammark number(3)  check (exammark between 0 and 100),constraint stud_fk  foreign key (studno) references student,constraint course_fk  foreign key (courseno) references course); Data Definition: Altering Relations alter table student  add (address char(20), default null);alter table student  modify (name not null); this won’t work if there are any nulls in the name column Data Manipulation: Insert Operator Inserting Tuples into a Relation insert into weak_students (studno,name,courseno,exammark) where (select s.studno,name,courseno,exammark from enrol, student s where exammark = 40 and enrol.studno = s.studno ); Insertion Anomalies An insert operation might voliate the uniqueness and minimality properties of the primary key