Автор: Пользователь скрыл имя, 06 Июня 2013 в 19:54, курсовая работа
Реляционная СУБД (Система Управления Базами Данных) - СУБД, управляющая реляционными базами данных. Понятие реляционный (англ. relation - отношение) связано с разработками известного английского специалиста в области cиcтем баз данных Эдгара Кодда.
Эти модели характеризуются простотой структуры данных, удобным для пользователя табличным представлением и возможностью использования формального аппарата алгебры отношений и реляционного иcчиcления для обработки данных.
Введение 3
1.Предметная область 4
2.Проектирование 5
2.1 Физическая модель 8
2.2 Логическая модель 9
2.3 Инфологическая модель 10
2.4 Даталогическая модель 11
3. Разработка БД 13
4. Заключение 30
5. Список литературы…………………………………………………………...31
CREATE TABLE AZS
(
id_azs int IDENTITY (1,1) ,
Name_directora char(30) NULL ,
Surname_directora char(30) NULL ,
Lastname_directora char(30) NULL ,
Cena_95 money NULL ,
Cena_92 money NULL ,
Cena_76 money NULL ,
Cena_DT money NULL ,
Cena_Sug money NULL ,
id_sotrudniki int NULL ,
id_neftebaza int NULL ,
id_vip int NULL
)
go
ALTER TABLE AZS
ADD CONSTRAINT XPKAZS PRIMARY KEY CLUSTERED (id_azs ASC)
go
CREATE TABLE Dolzhnost
(
id_dolzh int IDENTITY (1,1) ,
Dolzhnost char(30) NULL ,
Oklad int NULL ,
Zarplata int NULL ,
id_sotrudniki int NULL
)
go
ALTER TABLE Dolzhnost
ADD CONSTRAINT XPKDolzhnost PRIMARY KEY CLUSTERED (id_dolzh ASC)
go
CREATE TABLE Neftebaza
(
id_neftebaza int IDENTITY (1,1) ,
Kol_vo_95 int NULL ,
Kol_vo_92 int NULL ,
Kol_vo_76 int NULL ,
Kol_vo_DT int NULL ,
Kol_vo_Sug int NULL
)
go
ALTER TABLE Neftebaza
ADD CONSTRAINT XPKNeftebaza PRIMARY KEY CLUSTERED (id_neftebaza ASC)
go
CREATE TABLE Sotrudniki
(
id_sotrudniki int IDENTITY (1,1) ,
Name char(30) NULL ,
Surname char(30) NULL ,
Lastname char(30) NULL ,
Pol char(10) NULL ,
Adress char(50) NULL ,
Phone char(12) NULL
)
go
ALTER TABLE Sotrudniki
ADD CONSTRAINT XPKSotrudniki PRIMARY KEY CLUSTERED (id_sotrudniki ASC)
go
CREATE TABLE Vip_Client
(
id_vip int IDENTITY (1,1) ,
Name char(30) NULL ,
Surname char(30) NULL ,
Lastname char(30) NULL ,
Kol_vo_TS int NULL ,
Raschetni`_schet int NULL
)
go
ALTER TABLE Vip_Client
ADD CONSTRAINT XPKVip_Client PRIMARY KEY CLUSTERED (id_vip ASC)
go
ALTER TABLE AZS
ADD CONSTRAINT R_3 FOREIGN KEY (id_sotrudniki) REFERENCES Sotrudniki(id_sotrudniki)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
ALTER TABLE AZS
ADD CONSTRAINT R_4 FOREIGN KEY (id_neftebaza) REFERENCES Neftebaza(id_neftebaza)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
ALTER TABLE AZS
ADD CONSTRAINT R_5 FOREIGN KEY (id_vip) REFERENCES Vip_Client(id_vip)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
ALTER TABLE Dolzhnost
ADD CONSTRAINT R_6 FOREIGN KEY (id_sotrudniki) REFERENCES Sotrudniki(id_sotrudniki)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
CREATE TRIGGER tD_AZS ON AZS FOR DELETE AS
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* DELETE trigger on AZS */
BEGIN
DECLARE @errno int,
@errmsg varchar(255)
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Sotrudniki R/3 AZS on child delete no action */
/*
ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="AZS"
P2C_VERB_PHRASE="R/3", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_sotrudniki" */
IF EXISTS (SELECT * FROM deleted,Sotrudniki
WHERE
/* %JoinFKPK(deleted,Sotrudniki," = "," AND") */
deleted.id_sotrudniki = Sotrudniki.id_sotrudniki AND
NOT EXISTS (
SELECT * FROM AZS
WHERE
/* %JoinFKPK(AZS,Sotrudniki," = "," AND") */
AZS.id_sotrudniki = Sotrudniki.id_sotrudniki
)
)
BEGIN
SELECT @errno = 30010,
@errmsg = 'Cannot delete last AZS because Sotrudniki exists.'
GOTO ERROR
END
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Neftebaza R/4 AZS on child delete no action */
/*
ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="AZS"
P2C_VERB_PHRASE="R/4", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_neftebaza" */
IF EXISTS (SELECT * FROM deleted,Neftebaza
WHERE
/* %JoinFKPK(deleted,Neftebaza," = "," AND") */
deleted.id_neftebaza = Neftebaza.id_neftebaza AND
NOT EXISTS (
SELECT * FROM AZS
WHERE
/* %JoinFKPK(AZS,Neftebaza," = "," AND") */
AZS.id_neftebaza = Neftebaza.id_neftebaza
)
)
BEGIN
SELECT @errno = 30010,
@errmsg = 'Cannot delete last AZS because Neftebaza exists.'
GOTO ERROR
END
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Vip_Client R/5 AZS on child delete no action */
/*
ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="AZS"
P2C_VERB_PHRASE="R/5", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_vip" */
IF EXISTS (SELECT * FROM deleted,Vip_Client
WHERE
/* %JoinFKPK(deleted,Vip_Client," = "," AND") */
deleted.id_vip = Vip_Client.id_vip AND
NOT EXISTS (
SELECT * FROM AZS
WHERE
/* %JoinFKPK(AZS,Vip_Client," = "," AND") */
AZS.id_vip = Vip_Client.id_vip
)
)
BEGIN
SELECT @errno = 30010,
@errmsg = 'Cannot delete last AZS because Vip_Client exists.'
GOTO ERROR
END
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
RETURN
ERROR:
raiserror @errno @errmsg
rollback transaction
END
go
CREATE TRIGGER tU_AZS ON AZS FOR UPDATE AS
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* UPDATE trigger on AZS */
BEGIN
DECLARE @NUMROWS int,
@nullcnt int,
@validcnt int,
@insid_azs int,
@errno int,
@errmsg varchar(255)
SELECT @NUMROWS = @@rowcount
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Sotrudniki R/3 AZS on child update no action */
/* ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="AZS"
P2C_VERB_PHRASE="R/3", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_sotrudniki" */
IF
/* %ChildFK(" OR",UPDATE) */
UPDATE(id_sotrudniki)
BEGIN
SELECT @nullcnt = 0
SELECT @validcnt = count(*)
FROM inserted,Sotrudniki
WHERE
/* %JoinFKPK(inserted,Sotrudniki) */
inserted.id_sotrudniki = Sotrudniki.id_sotrudniki
/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */
select @nullcnt = count(*) from inserted where
inserted.id_sotrudniki IS NULL
IF @validcnt + @nullcnt != @NUMROWS
BEGIN
SELECT @errno = 30007,
@errmsg = 'Cannot update AZS because Sotrudniki does not exist.'
GOTO ERROR
END
END
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Neftebaza R/4 AZS on child update no action */
/* ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="AZS"
P2C_VERB_PHRASE="R/4", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_neftebaza" */
IF
/* %ChildFK(" OR",UPDATE) */
UPDATE(id_neftebaza)
BEGIN
SELECT @nullcnt = 0
SELECT @validcnt = count(*)
FROM inserted,Neftebaza
WHERE
/* %JoinFKPK(inserted,Neftebaza) */
inserted.id_neftebaza = Neftebaza.id_neftebaza
/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */
select @nullcnt = count(*) from inserted where
inserted.id_neftebaza IS NULL
IF @validcnt + @nullcnt != @NUMROWS
BEGIN
SELECT @errno = 30007,
@errmsg = 'Cannot update AZS because Neftebaza does not exist.'
GOTO ERROR
END
END
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Vip_Client R/5 AZS on child update no action */
/* ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="AZS"
P2C_VERB_PHRASE="R/5", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_vip" */
IF
/* %ChildFK(" OR",UPDATE) */
UPDATE(id_vip)
BEGIN
SELECT @nullcnt = 0
SELECT @validcnt = count(*)
FROM inserted,Vip_Client
WHERE
/* %JoinFKPK(inserted,Vip_Client) */
inserted.id_vip = Vip_Client.id_vip
/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */
select @nullcnt = count(*) from inserted where
inserted.id_vip IS NULL
IF @validcnt + @nullcnt != @NUMROWS
BEGIN
SELECT @errno = 30007,
@errmsg = 'Cannot update AZS because Vip_Client does not exist.'
GOTO ERROR
END
END
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
RETURN
ERROR:
raiserror @errno @errmsg
rollback transaction
END
go
CREATE TRIGGER tD_Dolzhnost ON Dolzhnost FOR DELETE AS
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* DELETE trigger on Dolzhnost */
BEGIN
DECLARE @errno int,
@errmsg varchar(255)
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Sotrudniki R/6 Dolzhnost on child delete no action */
/*
ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="Dolzhnost"
P2C_VERB_PHRASE="R/6", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_sotrudniki" */
IF EXISTS (SELECT * FROM deleted,Sotrudniki
WHERE
/* %JoinFKPK(deleted,Sotrudniki," = "," AND") */
deleted.id_sotrudniki = Sotrudniki.id_sotrudniki AND
NOT EXISTS (
SELECT * FROM Dolzhnost
WHERE
/* %JoinFKPK(Dolzhnost,
Dolzhnost.id_sotrudniki = Sotrudniki.id_sotrudniki
)
)
BEGIN
SELECT @errno = 30010,
@errmsg = 'Cannot delete last Dolzhnost because Sotrudniki exists.'
GOTO ERROR
END
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
RETURN
ERROR:
raiserror @errno @errmsg
rollback transaction
END
go
CREATE TRIGGER tU_Dolzhnost ON Dolzhnost FOR UPDATE AS
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* UPDATE trigger on Dolzhnost */
BEGIN
DECLARE @NUMROWS int,
@nullcnt int,
@validcnt int,
@insid_dolzh int,
@errno int,
@errmsg varchar(255)
SELECT @NUMROWS = @@rowcount
/* ERwin Builtin 24 мая 2013 г. 15:39:01 */
/* Sotrudniki R/6 Dolzhnost on child update no action */
/* ERWIN_RELATION:CHECKSUM="
CHILD_OWNER="", CHILD_TABLE="Dolzhnost"
P2C_VERB_PHRASE="R/6", C2P_VERB_PHRASE="",
FK_CONSTRAINT="", FK_COLUMNS="id_sotrudniki" */
IF
/* %ChildFK(" OR",UPDATE) */
UPDATE(id_sotrudniki)
BEGIN
SELECT @nullcnt = 0
SELECT @validcnt = count(*)
FROM inserted,Sotrudniki
WHERE
/* %JoinFKPK(inserted,Sotrudniki) */
inserted.id_sotrudniki = Sotrudniki.id_sotrudniki
/* %NotnullFK(inserted," IS NULL","select @nullcnt = count(*) from inserted where"," AND") */
select @nullcnt = count(*) from inserted where
inserted.id_sotrudniki IS NULL
Информация о работе Разработка автоматизированной информационной системы Автозаправочная станция