Karpova_bazy_dannyh

DDL

SQL

141

,

.

.

,

,

.

-

,

-

.

,

MS SQL Server 6.5

-

MS SQL Server 7.0

1024.

CREATE TABLE

,

.

,

:

-

,

,

-

.

SQL

,

-

,

USER,

,

(

),

NULL

-

.

-

-

NOT NULL (

-

.

NOT NULL

-

IS NOT NULL)" (

-

).

-

,

-

DEFAULT NULL.

-

-

,

-

DDL

SQL

143

PUBLICH

Varchar(20) NULL.

PAGES

smallint CHECK(PAGES > = 5 AND PAGES <= 1000 )

,

FIRST_NAME

char (30)

NOT HULL

LAST_NAME

char (40)

NOT NULL

ADRES

char (50)

HOME_PHON

char (12)

WQRK_PHON

char (12)

144

8

«

» (counter)

1

,

1,

1, -

,

.

MS SQL Server 7.0

IDENTITY,

-

:

-

,

.

,

.

EXEMPLAR

-

MS SQL Server 7.0.

CREATE TABLE EXEMPLAR

INT IDENTITY PRIMARY KEY

ISBN , Varchar(14) NOT NULL FOREIGN KEY references B00KS(I5BN)

Smallint(4)

NULL FOREIGN KEY references READERS

date

date

DDL

SQL

145

(

ID_EXEMPLAR

NOT NULL

ISBN varchar (14) NOT NULL FOREIGN KEY references BOQKS (ISBN)

READ R_I0

Smallint (4) NULL FOREIGN KEY references READERS (REAGERJD)

DATA_IN

DATA_OUT

EXIST Logical

PRIMARY KEY

(IDJXEHPLAR ISBN)

)

(F0RIGN KEY),

(PRIMARY KEY).

(PRIMARY KEY)

,

,

.

,

,

.

.

CREATE TABLE BOOKS

(

ISBN

varchar (14)

NOT NULL PRIHARY KEY

TITLE

varchar (120)

NOT NULL

AUTOR

varchar (30)

NULL

COAUTOR varchar (30)

NULL

YEAR_PUBL smallint DEFAULT rear (Setout ()/CHECK (YEAR_PUBL >= I960 AND

YEAR_PUBL <= YEAR (GetDateO))

PUBLICH

varchar (20)

NULL

PAGES

Smallint CHECKCPAGES > = 5 AND PAGES<=1000)

CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))

)

,

,

.

,

,

,

,

,

.

-

,

FK

,

,

U

,

DF

.

BOOKS

.

ISBN

varchar (14)

NOT NULL

AUTOR

varchar (30)

NULL

YEAR_PUBL smallint HOT NULL

PAGES

smallint NOT NULL

HULL))

)

CREATE TABLE READERS

READER_1D

Smallint

PRIMARY KEY

LAST_NAME

char {30)

NOT NULL

DDL

SQL

147

WORK_PHON

BIRTH_DAY

CHECK (Date D1ff f(year Get Date () BIRT_DAY>=17)

CONSTRAIN CK_READERS CHECK (HOME_PH0N IS NOT NULL OR WORK_PHON IS NOT

CREATE TABLE CATALOG

ID_CATALOG

Smallint PRIMARY KEY

KN0WELEDGE_AREA

varchar (150)

CREATE TABLE EXEMPLAR

ID_EXEMPLAR

int

NOT NULL

ISBN varchar (14)

NOT HULL FOREIGN KEY refererfces B0OCKS (ISBN)

READER_ID Smallint (4) NULL FOREIGN KEY references READERS

(READER_ID)

DATA_IN

DATA_0UT

Logical

PRIMARY KEY UDJXEMPLAR ISBN)

CREATE TASLE RELATIONS

varchar (14)

NOT HULL FOREIGN KEY references BOOKS (ISBN)

ID_CATALOG

NOT NULL FOREIGN KEY references CATALOG

148

8

,

5

«

»

,

-

:

CREATE TABLE BOOKS

(

TITLE

varchar (120)

NOT NULL

AUTOR

varchar (30)

COAUTOR

varchar (30)

NULL

VEAR_PUBL

Smallint

NOT NULL,

PUBLICH varchar (2O)

PAGES

CONSTRAINT PK_BOOKS PRIMARY'KEY (ISBN),

CONSTRAINT DF_ YER_PUBL DEFAULT (Year (Get Date ()).

CONSTRAINT CK_ YEAR_PUBL CHECK (YEAR_PUBL >- I960 AND

YEAR_PUBL_ YEAR (Get Date()).

CONSTRANT CK_PAGES CHECK (PAGES >=5 AND PAGES <=1000)

CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))

(

READERS

Smallint

PRIMARI_ KEY.

FIRST.NAME

char (30)

NOT NULL.

LAST_NAME

char (30)

NOT NULL.

ADRESS

char (50)

HOME_PHON

char (12).

WORK_PHON

char (12),

BIRTH_DAY date CHECKC DateD1ff (year Get Dafe().BIRTH_DAY) >=17 ).

CONSTRAINT CK_READERS CHECK ( M _ N IS NOT NULL OR WORK_PHON IS NOT NULL)

)

1D_CATALOG

Smallint

PRIMARY KEV.

KNOHELEDGE_AREA

varchar (150)

CREATE TABLE EXEMPLAR

ID_EXEMPLAR

1nt

NOT NULL

ISBN

varchar (14) NOT NULL FOREIGN

references BOOKS (ISBN),

READER_1D

Smallint (4) NULL FOREIGN KEY references READERS (READER_1D).

DATA_IN

date.

DATA_OUT

date.

EXIST

logical.

PRIMARY KEY (ID_EXEMPLAR. ISBN)

)

,

CREATE TABLE RELATION

(

ISBN

varchar (14) NOT BULL FOREIGN KEY references BOOKS(ISBN)

ID_CATALOG

smallint

NOT NULL FOREIGN KEY references CATAL0G (ID_CATALOG)

CONSTRAINT PK_RELATION_1 PRIMARY KEY (ISBN ID_CATALOG)

)

«

»

SQL1

,

,

.

,

ORACLE

150

8

(SQL Server),

-

.

.

MS SQL Server 7.0.

[ON [PRIMARY][<

>[ n]][ <

> [ n]]]

[

LOG ON { <

> [ n]} ][ FOR LOAD | FOR ATTAGH]

<

( [ NAME =

]FILENAME =

[SIZE =

UNLIMITED } ]

[FILEGR0WTH =

] ) [

n]

<

<

> [ n]

:

data base name

,

,

ON

,

-

,

,

PRIMARY

,

-

,

;

LOG ON

,

-

,

FOR LOAD

,

,

,

FOR ATTAGH

,

,

,

,

«

»

-

CREATE DATABASE Library

:

{ADD FILE <

> [

n] [TO FILEGRQUP filegrup_name]

| ADD LOG FILE

[

n]

| (REMOVE FILE

_

| ADD F1LEGROUP

_

| MODIFI File GRQUP ( _

__

_

__

)

:

READONLY

,

READWRITE

,

DEFAULT

,

,