create table faculty
(
facpk integer primary key,
name varchar2(50) UNIQUE NOT NULL,
deanfk integer,
building char(2)
check (building in ('1','2','3','4','5','6','7','8','9','10')),
fund number(7,2) check(fund>100000)
);
create table department
(
deppk integer primary key,
facfk integer,
name varchar2(50) not null,
headfk integer,
building char(2)
check (building in ('1','2','3','4','5','6','7','8','9','10')),
fund number(7,2) check (fund between 20000 and 100000),
constraints dpe_fk_on_fac foreign key(facfk) references faculty(facpk),
constraints uni_facfk_name unique(facfk,name)
);
create table teacher
(
tchpk integer primary key,
depfk integer,
name varchar2(50) not null,
post varchar2(20) check(post in ('assistant', 'counselor', 'assistant professor', 'professor')),
tel char(7),
hiredate date constraints teach_constr not null check (hiredate >= TO_DATE('01.01.1950', 'DD.MM.YYYY')),
salary number(6,2) constraints teach_sal_constr not null check(salary > 1000),
commission number (6,2) default 0 check(commission >= 0),
chieffk integer,
constraint teach_fk_on_dep foreign key(depfk) references department(deppk) on delete set null,
constraint teach_fk_on_teach foreign key(chieffk) references teacher(tchpk) on delete set null,
constraint commi_constr check (commission <= salary/2),
constraints com_sal_constr check (commission + salary between 1000 and 3000),
constraint chief_constr check(chieffk<>tchpk)
);
create table sgroup
(
grppk integer primary key,
depfk integer,
course number(1) check(course in (1,2,3,4,5,6)),
num number(3) check (num between 1 and 700),
quantity number(2) check (quantity between 1 and 50),
curator integer,
rating number(3) default 0 check(rating between 0 and 100),
constraints conn1 foreign key(depfk) references department(deppk) on delete set null,
constraints conn2 foreign key(curator) references teacher (tchpk) on delete set null,
constraints conn3 unique(depfk,num),
constraints conn654 unique(depfk,curator)
);
create table subject
(
sbjpk integer primary key,
name varchar2(50) not null
);
create table room
(
rompk integer primary key,
num number(4) not null,
seats number(3) check (seats between 1 and 300),
floor number(2) check(floor between 1 and 16),
building char(5) not null check (building in('1','2','3','4','5','6','7','8','9','10')),
constraints room_constr unique(num,building)
);
create table lecture
(
tchfk integer,
grpfk integer,
sbjfk integer,
romfk integer,
type varchar2(25) not null check (type in('lecture', 'lab', 'seminar', 'practice')),
day char(3) not null check(day in ('sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat')),
week number(1) not null check(week = 1 or week = 2),
lesson number(1) not null check(lesson between 1 and 8),
constraints conn14 foreign key(tchfk) references teacher(tchpk) on delete set null,
|
|
|
constraints conn15 foreign key(grpfk) references sgroup(grppk) on delete cascade,
constraints conn16 foreign key(sbjfk) references subject(sbjpk),
constraints conn17 foreign key(romfk) references room(rompk) on delete set null,
constraints conn18 unique(Grpfk, Day, Week, Lesson),
constraints conn19 unique (tchfk, Day, Week, Lesson)
);
?
, . , , . , Oracle , . , .
?
SQL Oracle :
[NOT] NULL .
UNIQUE , ( ) . .
PRIMARY KEY , , .
FOREIGN KEY , , ( ) . .
, , , , . , , :
CHECK .
3) ? ?
: .
, . .
UNIQUE? ? ( )? UNIQUE NULL? UNIQUE PRIMARY KEY ( )?
UNIQUE , UNIQUE. UNIQUE , () . , , NULL .
|
|
|
. table_constraint, column_constraint. , , NULL , . , NULL , , .
( ) .
5) PRIMARY KEY? ? ( )? ? PRIMARY KEY NULL?
PRIMARY KEY , . . table_constraint, column_constraint.
.
.
NULL .
( ) .
6) NULL (NOT NULL)? , ? ?
NULL NOT NULL , . NULL NOT NULL column_constraint.
NULL , . NOT NULL NULL, .
NOT NULL , . .
7) FOREIGN KEY ( )? : , , , . ( ), ? ? ? ( )? ? ( ) ? ( ) ? ? NULL ( )? CREATE TABLE, AS subquery? ? ON DELETE ? ON DELETE ?
FOREIGN KEY , , ( ) . .
|
|
|
| , , , . , , , , , . , , . . foreign_key_clause table_constraint. , . REFERENCES column_constraint. ( ) , . . . : UNIQUE PRIMARY, , . . CREATE TABLE, AS subquery. , ALTER TABLE. | |
| foreign_key_ clause | . . , , , NULL. . |
| REFERENCES | , , , ( ), , . , . , , . |
| ON DELETE | , Oracle , , . , Oracle , . CASCADE , Oracle , . SET NULL , NULL. |
| CHECK | . , TRUE UNKNOWN. Oracle CHECK , . CHECK , . Oracle CHECK . CHECK , . |
|
|
|
8) CHECK? CHECK ()? CHECK ? CHECK ? Oracle , CHECK, , ? CHECK ?
CHECK . , TRUE UNKNOWN. Oracle CHECK , .
CHECK , . Oracle CHECK .
CHECK , .
