Таблица А.8 - Данные таблицы PaySumma

Приложение Б Скрипт для создания учебной базы данных

SET SQL DIALECT 3;

CREATE DATABASE C:\SQLLAB.FDB USER SYSDBA PASSWORD masterkey

PAGESIZE 4096 DEFAULT CHARACTER SET WIN1251;

/щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл щЯл I rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 I

/* Domains */

rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 I

CREATE DOMAIN BOOLEAN AS SMALLINT CHECK (VALUE IN (0, 1));

CREATE DOMAIN MONEY AS NUMERIC(15,2); CREATE DOMAIN TMONTH AS SMALLINT CHECK (VALUE BETWEEN 1 AND 12);

CREATE DOMAIN PKFIELD AS INTEGER; CREATE DOMAIN TYEAR AS SMALLINT CHECK (VALUE BETWEEN 1990 AND 2100);

rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 I

/* Exceptions */

CREATE EXCEPTION INSRESTRICT Ограничение добавления записи в дочернюю таблицу;

CREATE EXCEPTION DELRESTRICT Ограничение удаления записи из родительской таблицы;

CREATE EXCEPTION UPDRESTRICT Ограничение модификации записи в родительской таблице;

rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 I

/* Tables */

rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 rf9 I

CREATE TABLE STREET (

STREETCD PKFIELD NOT NULL PRIMARY KEY, STREETNM VARCHAR(30) );

CREATE TABLE SERVICES (

GAZSERVICECD PKFIELD NOT NULL PRIMARY KEY, GAZSERVICENM VARCHAR(30) );

CREATE TABLE DISREPAIR (

FAILURECD PKFIELD NOT NULL PRIMARY KEY, FAILURENM VARCHAR(50) );

CREATE TABLE EXECUTOR (

EXECUTORCD PKFIELD NOT NULL PRIMARY KEY, FIO VARCHAR(20) );

CREATE TABLE ABONENT (

ACCOUNTCD VARCHAR(6) NOT NULL PRIMARY KEY, STREETCD PKFIELD REFERENCES STREET

ON DELETE SET NULL ON UPDATE CASCADE, HOUSENO SMALLINT,

FLATNO SMALLINT, FIO VARCHAR(20), PHONE VARCHAR(15)); CREATE TABLE NACHISLSUMMA (

NACHISLFACTCD PKFIELD NOT NULL PRIMARY KEY, ACCOUNTCD VARCHAR(6) NOT NULL REFERENCES ABONENT

ON UPDATE CASCADE, GAZSERVICECD PKFIELD NOT NULL REFERENCES SERVICES

ON UPDATE CASCADE, NACHISLSUM MONEY,

NACHISLMONTH TMONTH, NACHISLYEAR TYEAR); CREATE TABLE PAYSUMMA ( PAYFACTCD PKFIELD NOT NULL PRIMARY KEY, ACCOUNTCD VARCHAR(6) NOT NULL REFERENCES ABONENT ON UPDATE CASCADE,

GAZSERVICECD PKFIELD NOT NULL REFERENCES SERVICES

ON UPDATE CASCADE,

PAYSUM MONEY,

PAYDATE DATE, PAYMONTH TMONTH, PAYYEAR TYEAR);

CREATE TABLE REQUEST (

REQUESTCD PKFIELD NOT NULL PRIMARY KEY,

ACCOUNTCD VARCHAR(6) REFERENCES ABONENT

ON DELETE SET NULL ON UPDATE CASCADE, EXECUTORCD PKFIELD REFERENCES EXECUTOR

ON DELETE SET NULL ON UPDATE CASCADE, FAILURECD PKFIELD REFERENCES DISREPAIR

ON DELETE SET NULL ON UPDATE CASCADE, INCOMINGDATE DATE,

EXECUTIONDATE DATE, EXECUTED BOOLEAN);

/i /

/* Insert STREET */

/i /

INSERT INTO STREET (STREETCD, STREETNM) VALUES (3, ВОЙКОВ ПЕРЕУЛОК);

INSERT INTO STREET (STREETCD, STREETNM) VALUES (7, КУТУЗОВА УЛИЦА);

INSERT INTO STREET (STREETCD, STREETNM) VALUES (6,

МОСКОВСКАЯ УЛИЦА);