База данных велосипедного магазина

1.5 Фирмы

1.6 Технические характеристики

1.7 Свойства

1.8 Профессии

1.9 Типы фирм

1.10 Типы операций

123 – Primarykey (первичный ключ)

123 – Foreign key (связь)

123 – Uniqe (уникальный)

2. Примеры создания таблиц

create table Professii (KOD NUMBER(5) PRIMARY KEY,

NAZVANIE VARCHAR2(30) UNIQUE

);

create table Personal (KOD NUMBER(5) PRIMARY KEY,

FIO VARCHAR2(50),

TELEFON VARCHAR2(15),

BIRTHSDAY DATE,

ADRES VARCHAR2(30),

POL NUMBER(1),

KOD_PROFESSII NUMBER(5),

UNIQUE (FIO,BIRTHSDAY),

FOREIGN KEY KOD_PROFESSII REFERENCES PROFESSII KOD

);

create table Svoistva (KOD NUMBER(5) PRIMARY KEY,

NAZVANIE VARCHAR2(30),

EDINIZI IZMERENIA VARCHAR2(10)

);

create table Texnicheskie_xarakteristiki (

KOD_TOVARA NUMBER(5),

KOD_SVOISTVA NUMBER(5),

ZNACHENIE NUMBER(5),

FOREIGN KEY KOD_TOVARA REFERENCES TOVARI KOD,

FOREIGN KEY KOD_SVOISTVA REFERENCES SVOISTVA KOD

);

create table Tipi_firm (KOD NUMBER(5) PRIMARY KEY,

NAZVANIE VARCHAR2(30) UNIQUE

);

create table Tipi_operazii (KOD NUMBER(5) PRIMARY KEY,

NAZVANIE VARCHAR2(30) UNIQUE

);

create table Firmi (KOD NUMBER(5) PRIMARY KEY,

NAZVANIE VARCHAR2(30) UNIQUE,

ADRES VARCHAR2(30),

TELEFON VARCHAR2(15),

KOD_TIPA_FIRMI NUMBER(5),

FOREIGN KEY KOD_TIPA_FIRMI REFERENCES TIPI_FIRM KOD

);

create table Tipi_tovarov (KOD NUMBER(5) PRIMARY KEY,

NAZVANIE VARCHAR2(30) UNIQUE,

KOD_FIRMI NUMBER(5),

KOD_TIPA_TOVARA NUMBER(5),

FOREIGN KEY KOD_FIRMI REFERENCES FIRMI KOD,

FOREIGN KEY KOD_TIPA_TOVARA REFERENCES TIPI_TOVAROV KOD

);

create table Tovari (KOD NUMBER(5) PRIMARY KEY,

KOD_TIPA_TOVARA NUMBER(5),

NAZVANIE VARCHAR2(30),

UNIQUE (KOD_TIPA_TOVARA,NAZVANIE),

FOREIGN KEY KOD_TIPA_TOVARA REFERENCES TIPI_TOVAROV KOD

);

create table Sklad (

KOD_TOVARA NUMBER(5),

KOD_FIRMI NUMBER(5),

KOD_PERSON NUMBER(5),

KOD_TIPA_OPERAZII NUMBER(5),

ZENA NUMBER(5),

DATA DATE,

KOLICHESTVO NUMBER(3),

FOREIGN KEY KOD_TOVARA REFERENCES TOVARI KOD,

FOREIGN KEY KOD_FIRMI REFERENCES FIRMI KOD,

FOREIGN KEY KOD_PERSON REFERENCES PERSONAL KOD,

FOREIGN KEY KOD_TIPA_OPERAZII REFERENCES TIPI_OPERAZII KOD

);

3. Примеры создания последовательностей

CREATE SEQUENCE "SEQPERSONAL" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

CREATE SEQUENCE "SEQFIRMI" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

CREATE SEQUENCE "SEQTIPI_TOVAROV" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

CREATE SEQUENCE "SEQTOVARI" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

4. Примеры создания триггеров

CREATE OR REPLACE TRIGGER INSERT_IN_PERSONAL

BEFORE INSERT ON PERSONAL

FOR EACH ROW

BEGIN

SELECT SEQPERSONAL.NEXTVAL INTO :new.KOD FROM DUAL;

END;

/

CREATE OR REPLACE TRIGGER INSERT_IN_FIRMI

BEFORE INSERT ON FIRMI

FOR EACH ROW

BEGIN

SELECT SEQFIRMI.NEXTVAL INTO :new.KOD FROM DUAL;

END;

/

CREATE OR REPLACE TRIGGER INSERT_IN_TIPI_TOVAROV

BEFORE INSERT ON TIPI_TOVAROV

FOR EACH ROW

BEGIN

SELECT SEQTIPI_TOVAROV.NEXTVAL INTO :new.KOD FROM DUAL;

END;

/

CREATE OR REPLACE TRIGGER INSERT_IN_TOVARI

BEFORE INSERT ON TOVARI

FOR EACH ROW

BEGIN

SELECT SEQTOVARI.NEXTVAL INTO :new.KOD FROM DUAL;

END;

/

5. Создание процедуры

CREATE OR REPLACE FUNCTION

Updater

(NUMTOVARA Number)

IS

Result Number

BEGIN

RETURN

(SELECT SUM (KOLICHESTVO) FROM SKLAD WHERE KOD_TOVARA=

 Скачать реферат