Karpova_bazy_dannyh

ALTER TABLE

, , . -

, . -

.

. ALTER TABLE , .

«»).

ALTER TABLE READERS

ADD EDUCATION varchar (30) DEFAULT NULL CHECK (EDUCATION IS NULL OR EDUCATION="" OR

EDUCATION= "" OR EDUCATION='" OR EDUCATION = "")

READERS EDUCATION,

. -

.

EXEMPLAR. , , ,

. ,

2 . EXEMPLARE.

ALTER TABLE EXEMPLAR

ADD CONSTRAINT CK_ EXEMPLARE CHECK ((DATA_IN IS NULL AND DATA_0UT IS NULL) OR

{DATA_OUT >= DATA_IN +14))

153

, , , -

.

. -

, ,

-

, . -

-

, . , , . 8.1.

-

,

-

-

,

-

. 8.1.

ALTER TABLE S--

.

,

-

. -

CASE , -

, -

,

, , , .

(CREATE INDEX) -

, .

MS SQL Server 6.X,

, Transact SQL. USE <

> , go

.

/*________________________________________________*/

/*________________________________________________*/

/*___________________________________*/ /*Data base name LIBRARY /*_________________________________________________*/

Use LIBRARY /*_________________________________________________*/ /* Table BOOKS /*________________________________________________*/ create table BOOKS

PUBLICHER varchar (30) null

155

Constraint CKC_YEAR_IZD_B0OICS check (

YEAR_PUBL >= 1969 AND YEAR_PUBL <= YEAR (Get Date ())) PAGES smallint not null

Constraint CKC_PAGES_BOOKS check ( PAGES between 5 and 1000)

Constraint PK_BOOKS primary key (ISBN) Constraint CKI_BOOKS check (

(AUTOR IS NOT NULL OR (AUTOR IS NULL AND 0UTOR IS NULL)))

)

go /*________________________________________*/

/* Table READERS */

/*________________________________________*/

constraint CKC_BIRTH_DAY READERS check (

constraint PK_EADERS primary key (NUM READER) constraint CKC_READERS check (

(HOME_PHON IS NOT HULL OR WORK_PHON IS NOT NULL) )

)

90 /*___________________________________*/

/* Table CATALOG */

/* __________________________________*/

156 8

constraint PK_EXEMPLAR primary key (INV_NUMER)

)

90 /* ____________________________________________*/

/* Index RELATI0N_43_FK */ /*____________________________________________*/

create Index RELATI0N_43_FK on EXEMPLAR (ISBN) go /*____________________________________________*/ /* Index RELATI0N_44_FK */

create Index RELATION_44_FK on EXEMPLAR (NUM_READER) go

/*_____________________________________________*/

/* Table RELATI0N_67 */ /* _____________________________________________*/

create table RELATION_67

constraint PK_RELATI0N_67 primary key (ISBN KW_KOD)

)

go

157

/*_____________________________________________*/

/* Index [01] NEONY_E_IAEANOE_CIAKE_FK */

create index [01] NEONY_E_IAEANOE_CIAKE_FK on RELATION (ISBN) go

/*____________________________________________* / /* Index I_AANOAAEAIA_A_EIEAAO_FK */ /*____________________________________________*/

create index IjWNOAAEAIAjVJIEAAOJK on RELATION KW_K0D) go

alter table EXEMPLAR

add constraint FK_EXEMPLAR_RELATION_BOOKS foreign key (ISBN) references BOOKS (ISBN)

go

alter table EXEMPLAR

add constraint FK_EXEMPLAR_RELATION_READERS foreign key NUM_READER> references READERS (NUM_REABER)

go

alter table RELATI0N_67

add constraint FK_RELATION_[01] NEONY_BQOKS foreign key (ISBN) references BOOKS (ISBN)

go

alter table RELATI0N_67

add constraint rt_RELATIONJ_AAN0AAE_CATALOG foreign key (KW_COD) references CATALOG (KW_KOD)

go

SQL .

:

<> = DROP TABLE <> [CASCADE | RESTRICT]

, -.

, -

. , -

, .

DROP TABLE

. , -

, ,

, .

, , , -

BOOKS, READERS, CATALOG.

EXEMPLAR RELATION_67.

, -

, -

. -

:

DROP TABLE EXEMPLAR

DROP TABL RELATlON_67

DROP TABLE CATALOG

DROP TABLE READERS

DROP TABLE BOOKS

, .

.

, -

, , -

-

.

, .

. -

, -

. ,

, -

, ,

, ,

, . -

, .

:

<> » CREATE VIEW <> [ (<0>)] AS <SQL >

.

-

. , , ,

, -

, ,

.

, «» (EMPLOYEE) : «»(T_NUM), «»(NAME), «» (POSITION), «»(SALARY),

«»(PREMIUM), «» (DEPARTMENT).

, ,

:

CREATE VIEW SAL_DEPT

AS

SELECT *

FROM EMPLOYEE

WHERE DEPARTMENT= ""

-

.

, -

.

, , -

, .

:

CREATE VIEW TABEL

AS

SELECT T_NUM NAME POSITION DEPARTMENT

FROM EMPLOYEE

, . -

. -

,

,

, .

,

, -

, . -

-

,

.

CREATE VIEW RATE

DEPARTMENT COUNT (*) SUM (SALARY) SUM (PREMIUM) MAX (SALARY) MIN

MIN (SALARY) AVERAGE (SALARY) MAX (PREMIUH) MIN (PREMIUM) AVERAGE (PREMIUM)

FROM EMPLOYEE GROUP BY DEPARTMENT

. -

,

.

«» , -

-,

. -

,

,

.

CREATE VIEW DEBTORS

ISBN TITLE NUM_READER N ADRES HOME_PHON WORK_PHON DATA_OUT AS

SELECT ISBN TITLE NUM_READER NAME ADRES H0ME_PHON WORK_PHON DATA_OUT FROM BOOKS EXEMPLAR READERS

WHERE BOOKS ISBN = EXEMPLAR ISBN AND

EXEMPLAR NUM_READER = READERS NUM_READER AND EXEMPLAR PRESENT = FALSE AND

EXEMPLAR DATA_OUT < Get Date ()

159

SQL1

, -

, -

.

,

, -

.

. , , . -

,

.

, , -

:

DISTINKT,

.

FR0H , -

,

(),

.

, .

,

.

WHERE , -

.

GR0UP B

HAVING.

,

,