Нормализация таблиц до 3НФ. Создание новых таблиц для каждого поля. Результат нормализации. SQL сценарий для создания спроектированной базы данных

3.1 Нормализация таблиц до 3НФ.

На Рис.3.1. показана ненормализованая таблица 

ID_PHIS_LICA

SNAME

NAME

PNAME

ADRES

TELEPHON

AGE

ID_AVTO

KILOMETRAZ

MARKA

ID_SOTRUDNIK

ZARPLATA

OTCHISLENIE

ID_COMPANY

COMPANYNAME

PAY

CITY

PHONE

REZ

ID_DISPETCHER

DOLZNOST

ID_TEXOBCLUZ

ID_VODITEL

ID_ZAKAZ

TIP_ZAKAZA

NOMER_ZAKAZA

DATA_ZAKAZ

ZAKAZCHIK

Рис.3.1. Ненормализованная таблица «Таблица 1»

Для нормализации до 3НФ Таблица 1 были проведены следующие действия:

1.Определены все поля , от которых зависят другие поля.

2. Созданы новые таблицы для каждого такого поля  и группы зависящих от него полей.

3. Удалены перемещенные поля из исходной таблицы, остались только те поля которые станут внешними ключами.

Результат нормализации приведен на нижеследующих рисунках:

Рис.3.2. Третья НФ таблицы «Заказы»

Рис.3.3. Третья НФ таблицы «Физические лица»

Рис.3.4. Третья НФ таблицы «Сотрудники»

Рис.3.5. Третья НФ таблицы «Водители»

Рис.3.6. Третья НФ таблицы «авто»

Рис.3.6. Третья НФ таблицы «Диспетчеры»

4.Инфологическая модель разрабатываемой базы данных

SQL сценарий, для создания спроектированной базы данных

/*Создадим  таблицу заказы*/

CREATE TABLE ZAKAZ (

ID_ZAKAZ       INTEGER NOT NULL,

TIP_ZAKAZA     D_TIPI /* D_TIPI = CHAR(20) DEFAULT 'грузовой' CHECK (VALUE IN ('грузовой','пассажирский')) */,

NOMER_ZAKAZA   VARCHAR(20) NOT NULL,

DATA_ZAKAZ     CHAR(8) DEFAULT '00.00.00',

ID_DISPETCHER  INTEGER NOT NULL,

ZAKAZCHIK      INTEGER,

ID_COMPANY     INTEGER

);

/*Создадим таблицу физические лица*/

CREATE TABLE PHISICHESKIE_LICA (

ID_PHIS_LICA  INTEGER NOT NULL,

SNAME         VARCHAR(30) NOT NULL,

NAME          VARCHAR(30) NOT NULL,

PNAME         VARCHAR(30) NOT NULL,

ADRES         CHAR(50) NOT NULL,

TELEPHON      CHAR(11) DEFAULT '000-00-00',

AGE           INTEGER NOT NULL

);

/*Создадим таблицу сотрудники*/

CREATE TABLE SOTRUDNIKI (

ID_SOTRUDNIK  INTEGER NOT NULL,

ID_PHIS_LICA  INTEGER NOT NULL,

DOLZNOST      VARCHAR(20) NOT NULL

);

/*Создадим таблицу водители*/

CREATE TABLE VODITELI (

ID_SOTRUDNIK  INTEGER NOT NULL,

ID_AVTO       INTEGER NOT NULL,

ID_VODITEL    INTEGER NOT NULL

);

/*Создадим таблицу авто*/

CREATE TABLE AVTO (

ID_AVTO     INTEGER NOT NULL,

KILOMETRAZ  CHAR(20) NOT NULL,

MARKA       VARCHAR(20) NOT NULL

);

/*Создадим таблицу Диспетчеры*/

CREATE TABLE DISPETCHERS (

ID_DISPETCHER  INTEGER NOT NULL,

ID_SOTRUDNIK   INTEGER NOT NULL

);

/*Создадим таблицу bux*/

CREATE TABLE BUX (

ID_SOTRUDNIK  INTEGER NOT NULL,

ZARPLATA      DECIMAL(15,2),

OTCHISLENIE   DECIMAL(15,2)

);

/*Создадим таблицу company*/

CREATE TABLE COMPANY (

ID_COMPANY   INTEGER NOT NULL,

COMPANYNAME  VARCHAR(30) NOT NULL,

PAY          INTEGER NOT NULL,

CITY         VARCHAR(30) DEFAULT 'Неизвестен',

PHONE        D_PHONE DEFAULT '000-00-00' /* D_PHONE = VARCHAR(9) CHECK (VALUE like '___-__-__') */,

REZ          VARCHAR(20)

);

/*Создадим таблицу TEXOBCLUZ */

CREATE TABLE TEXOBCLUZ (

ID_TEXOBCLUZ  INTEGER NOT NULL,

ID_AVTO       INTEGER NOT NULL,

DATA          D_DATA /* D_DATA = VARCHAR(9) CHECK (VALUE like '__.__.__') */

);

Виды !

1

/* View: BURBUR */

CREATE VIEW BURBUR(

NAME,

FAMILIYA,

MASINA,

ZARPLATA)

AS

select phisicheskie_lica.SNAME,phisicheskie_lica.NAME,AVTO.MARKA,bux.ZARPLATA

from phisicheskie_lica , avto, sotrudniki,voditeli,bux

where (phisicheskie_lica.id_phis_lica=sotrudniki.id_phis_lica)

and (sotrudniki.id_sotrudnik=voditeli.id_sotrudnik) and (voditeli.id_avto=avto.id_avto)

;

2

/* View: NEW_VIEW */

CREATE VIEW NEW_VIEW(

SNAME,

NAME,

PNAME)

AS

select SNAME,NAME,PNAME from PHISICHESKIE_LICA

where exists (select zakazchik

from zakaz

WHERE (zakaz.tip_zakaza='грузовой') and

zakaz.zakazchik=PHISICHESKIE_LICA.ID_PHIS_LICA)

;

3

/* View: NEW_VIEW1 */

CREATE VIEW NEW_VIEW1(

SNAME,

OTCHISLENIE)

AS

select SNAME,OTCHISLENIE from phisicheskie_lica,bux

where phisicheskie_lica.id_phis_lica=bux.id_sotrudnik

;

4

/* View: NEW_VIEW3 */

CREATE VIEW NEW_VIEW3(

MARKA,

DOLZNOST,

ZARPLATA,

SNAME,

NAME,

PNAME)

AS

select avto.marka,sotrudniki.dolznost,bux.zarplata,phisicheskie_lica.sname,

phisicheskie_lica.name,phisicheskie_lica.pname from avto,voditeli,sotrudniki,phisicheskie_lica,bux

where (voditeli.id_avto=avto.id_avto)and(voditeli.id_sotrudnik=sotrudniki.id_sotrudnik)

and(sotrudniki.id_phis_lica=phisicheskie_lica.id_phis_lica)

;

5

/* View: NEW_VIEW4 */

CREATE VIEW NEW_VIEW4(

MARKA,

DATA)

AS

select MARKA,DATA from avto,texobcluz

where avto.id_avto=texobcluz.id_avto

;

6

/* View: NEW_VIEW5 */

CREATE VIEW NEW_VIEW5(

DOLZNOST,

MARKA)

AS

select dolznost,marka from sotrudniki,avto,voditeli

where sotrudniki.id_sotrudnik=voditeli.id_sotrudnik

;

Триггеры

1

CREATE TRIGGER AVTO_BI0 FOR AVTO

ACTIVE BEFORE INSERT POSITION 0

AS

begin

New.id_avto=gen_id(gen_avto_id,1);

End