Список  используемых источников

1. Эндрю Троелсен «Язык программирования С# 2005 и платформа .NET 2.0», 3-е издание. : Пер. с англ. –  М.: ООО «И.Д.Вильямс », 2007. – 1168 с.
2. Гамильтон Билл «ADO.NET Сборник рецептов. Для профессионалов» - СПб.: Питер, 2005 год. - 576 с.
3. Малик Сахил «Microsoft ADO.NET 2.0 для профессионалов» — М. : Издательский дом "Вильямс", 2006. - 560 с.
4. Шумаков П.В. «ADO.NET и создание приложений баз данных в среде Microsoft Visual Studio .NET» - М.: Диалог-Мифи, 2008. - 528 с
5. Сеппа Д. « Microsoft ADO.NET»/Пер. с англ. — М.: Издательско-торговый дом «Русская Редакция», 2003- — 640 стр.: ил.
6. Бори Х.,  «FIREBIRD. Руководство разработчика баз данных». –С-Пб.: «БХВ», 2006 г., 2-е издание.

7. http://www.ibase.ru

8. http://www.php.org
9. http://citforum.ru

 
 
 
 
 
 
 
 

 

   

Приложение 1. SQL код создания БД

 
 

ДОМЕНЫ

CREATE DOMAIN D_DATE AS

DATE

NOT NULL

CHECK (value<='TODAY'); 

CREATE DOMAIN D_FIO AS

VARCHAR(25) CHARACTER SET WIN1251

NOT NULL

COLLATE WIN1251; 

CREATE DOMAIN D_INF AS

VARCHAR(40) CHARACTER SET WIN1251

NOT NULL

COLLATE WIN1251; 

CREATE DOMAIN D_STATNUM AS

VARCHAR(7) CHARACTER SET WIN1251

NOT NULL

COLLATE WIN1251; 

CREATE DOMAIN D_TIME AS

TIME

NOT NULL; 

ТАБЛИЦЫ 

CREATE TABLE EMPLOYERS (

    RN_EMPLOYERS  D_INDEX NOT NULL /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */,

    SURNAME       D_FIO /* D_FIO = VARCHAR(25) NOT NULL */,

    NAME          D_FIO /* D_FIO = VARCHAR(25) NOT NULL */,

    SECOND_NAME   D_FIO /* D_FIO = VARCHAR(25) NOT NULL */,

    POLNOMOCHIE   D_INF /* D_INF = VARCHAR(40) NOT NULL */

); 

CREATE TABLE MAGAZINE_EVENTS (

    RN_EVENTS     D_INDEX NOT NULL /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */,

    DATE_SOB      D_DATE /* D_DATE = DATE NOT NULL CHECK (value<='TODAY') */,

    TIME_V_EZD    D_TIME /* D_TIME = TIME NOT NULL */,

    TIME_VYEZD    D_TIME /* D_TIME = TIME NOT NULL */,

    RN_EMPLOYERS  D_INDEX /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */,

    RN_TRANSPORT  D_INDEX /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */,

    RN_MESTO      D_INDEX /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */

); 

CREATE TABLE MESTO_PARKING (

    RN_MESTO  D_INDEX NOT NULL /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */,

    CLASS     D_INF /* D_INF = VARCHAR(40) NOT NULL */

); 

CREATE TABLE TRANSPORT (

    RN_TRANSPORT  D_INDEX NOT NULL /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */,

    MARK          D_INF /* D_INF = VARCHAR(40) NOT NULL */,

    COLOUR        D_INF /* D_INF = VARCHAR(40) NOT NULL */,

    STATE_NUM     D_STATNUM /* D_STATNUM = VARCHAR(7) NOT NULL */,

    RN_CLIENTS    D_INDEX /* D_INDEX = SMALLINT NOT NULL CHECK (value>0) */

);

Созданные первичные и вторичные  ключи 

    ALTER TABLE CLIENTS ADD CONSTRAINT RN_CLIENTS PRIMARY KEY (RN_CLIENTS); 

    ALTER TABLE EMPLOYERS ADD CONSTRAINT PK_EMPLOYERS PRIMARY KEY (RN_EMPLOYERS); 

    ALTER TABLE MAGAZINE_EVENTS ADD CONSTRAINT FK_MAGAZINE_EVENTS_1 FOREIGN KEY (RN_EMPLOYERS) REFERENCES EMPLOYERS (RN_EMPLOYERS) ON UPDATE CASCADE;

    ALTER TABLE MAGAZINE_EVENTS ADD CONSTRAINT FK_MAGAZINE_EVENTS_2 FOREIGN KEY (RN_TRANSPORT) REFERENCES TRANSPORT (RN_TRANSPORT) ON UPDATE CASCADE;

    ALTER TABLE MAGAZINE_EVENTS ADD CONSTRAINT FK_MAGAZINE_EVENTS_3 FOREIGN KEY (RN_MESTO) REFERENCES MESTO_PARKING (RN_MESTO) ON UPDATE CASCADE; 

    ALTER TABLE MESTO_PARKING ADD CONSTRAINT PK_MESTO_PARKING PRIMARY KEY (RN_MESTO); 

    ALTER TABLE TRANSPORT ADD CONSTRAINT PK_TRANSPORT PRIMARY KEY (RN_TRANSPORT);

    ALTER TABLE TRANSPORT ADD CONSTRAINT FK_TRANSPORT_1 FOREIGN KEY (RN_CLIENTS) REFERENCES CLIENTS (RN_CLIENTS) ON UPDATE CASCADE; 

Генераторы 

CREATE GENERATOR GEN_TRANSPORT_ID;

SET GENERATOR GEN_TRANSPORT_ID TO 20; 

CREATE GENERATOR GEN_MESTO_PARKING_ID;

SET GENERATOR GEN_MESTO_PARKING_ID TO 147; 

CREATE GENERATOR GEN_MAGAZINE_EVENTS_ID;

SET GENERATOR GEN_MAGAZINE_EVENTS_ID TO 19;

CREATE GENERATOR GEN_EMPLOYERS_ID;

SET GENERATOR GEN_EMPLOYERS_ID TO 5; 

CREATE GENERATOR GEN_CLIENTS_ID;

SET GENERATOR GEN_CLIENTS_ID TO 20; 

Триггеры 

CREATE OR ALTER TRIGGER CLIENTS_BI0 FOR CLIENTS

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

  IF (NEW.RN_CLIENTS IS NULL) THEN

    NEW.RN_CLIENTS = GEN_ID(gen_clients_id,1);

END 

CREATE OR ALTER TRIGGER EMPLOYERS_BI0 FOR EMPLOYERS

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

  IF (NEW.RN_EMPLOYERS IS NULL) THEN

    NEW.RN_EMPLOYERS = GEN_ID(gen_employers_id,1);

END 

CREATE OR ALTER TRIGGER MAGAZINE_EVENTS_BI0 FOR MAGAZINE_EVENTS

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

  IF (NEW.RN_EVENTS IS NULL) THEN

     NEW.RN_EVENTS = GEN_ID(gen_magazine_events_id,1);

END 

CREATE OR ALTER TRIGGER MESTO_PARKING_BI0 FOR MESTO_PARKING

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

  IF (NEW.RN_MESTO IS NULL) THEN

    NEW.RN_MESTO = GEN_ID(gen_mesto_parking_id,1);

END 

CREATE OR ALTER TRIGGER TRANSPORT_BI0 FOR TRANSPORT

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

  IF (NEW.RN_TRANSPORT IS NULL) THEN

    NEW.RN_TRANSPORT = GEN_ID(gen_transport_id,1);

END 

Представления 

CREATE VIEW VCLIENT(

    RN_CLIENTS,

    SURNAME,

    NAME,

   SECOND_NAME)

AS

select * from clients

; 

CREATE VIEW VCLIENTS(

    RN_CLIENTS,

    SURNAME,

    NAME)

AS

select clients.rn_clients, clients.surname, clients.name from clients

where clients.surname like 'В%'

; 

CREATE VIEW VMAGAZINE(

    RN_EVENTS,

    DATE_SOB,

    TIME_V_EZD,

    TIME_VYEZD,

    EMPLOYERS,

    TRANSPORT,

    CLASS)

AS

select  magazine_events.rn_events,magazine_events.date_sob, magazine_events.time_v_ezd, magazine_events.time_vyezd, employers.surname, transport.mark, mesto_parking.class  from magazine_events, employers, transport, mesto_parking

where magazine_events.rn_employers = employers.rn_employers and

        magazine_events.rn_transport = transport.rn_transport and

        magazine_events.rn_mesto = mesto_parking.rn_mesto

; 

CREATE VIEW VTIME(

    RN_EVENTS,

    DATE_SOB,

    TRANSPORT)

AS

select  magazine_events.rn_events,magazine_events.date_sob, transport.mark  from magazine_events, transport

where (magazine_events.rn_transport = transport.rn_transport) and (magazine_events.date_sob < '21.02.2010')

; 

CREATE VIEW VTRANSPORT(

    RN_TRANSPORT,

    MARC,

    COLOUR,

    STATE_NUM,

    CLIENTS)

AS

select  transport.rn_transport, transport.mark, transport.colour, transport.state_num, clients.surname from transport, clients

where transport.rn_clients = clients.rn_clients

; 

Процедуры 

CREATE OR ALTER PROCEDURE DEL_CLIENT (

    rn smallint)

as

begin

  begin

     delete from clients where rn_clients  = :rn;

  when sqlcode -530 do exception e_key;

  end

  suspend;

end 

CREATE OR ALTER PROCEDURE INS_CLIENTS (

    surname varchar(25),

    name varchar(25),

    second_name varchar(25))

as

begin

  begin

     insert into clients (surname,name,second_name) values (:surname,:name,:second_name);

     when sqlcode -530 do exception e_key;

  end

  suspend;

end 

CREATE OR ALTER PROCEDURE UPD_CLIENTS (

    rn smallint,

    surname varchar(25),

    name varchar(25),

    second_name varchar(25))

as

begin

     update  clients

     set surname=:surname, name=:name, second_name=:second_name

     where rn_clients = :rn;

  suspend;

end 
 

 

ПРИЛОЖЕНИЕ 2. Листинг клиентского  приложения

 

Form1.s 
 

using System;

using System.Data.OleDb;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;