Проектирование базы данных для торговца подержанными автомобилями

"justify">            №_Avto               INTEGER,

            Data_prodagi         DATE,

            Cena                 CHAR(18)

     ); 

     CREATE UNIQUE INDEX XPKPRODAGI ON PRODAGI

     (

            №_Sotrudnika,

            №_Agenta,

            №_Prodagi

     ); 
 

     ALTER TABLE PRODAGI

            ADD PRIMARY KEY (№_Sotrudnika, №_Agenta, №_Prodagi);

     CREATE TABLE AVTO (

            №_Avto               INTEGER NOT NULL,

            №_Agenta             INTEGER NOT NULL,

            Color                CHAR(18),

            Marka                CHAR(18),

            Model                CHAR(18)

     );

     CREATE UNIQUE INDEX XPKAVTO ON AVTO

     (

            №_Avto

     );

     ALTER TABLE AVTO

            ADD PRIMARY KEY (№_Avto);

     CREATE TABLE AVTO_PRODAGI (

            №_Sotrudnika         INTEGER,

            №_Agenta             INTEGER,

            №_Avto               INTEGER,

            №_Prodagi            INTEGER

     );

     CREATE TABLE MEXAN (

            №_Mexanika           INTEGER NOT NULL,

            №_Sotrudnika         INTEGER,

            Oklad                CHAR(18)

     );

     CREATE UNIQUE INDEX XPKMEXAN ON MEXAN

     (

            №_Mexanika,

            №_Sotrudnika

     );

     ALTER TABLE MEXAN

           ADD PRIMARY KEY (№_Mexanika, №_Sotrudnika);

     CREATE TABLE MEXAN_AVTO (

            №_Remonta            INTEGER NOT NULL,

            №_Sotrudnika         INTEGER,

            №_Mexanika           INTEGER,

            №_Avto               INTEGER,

            Data                 DATE,

            Clock                CHAR(20)

     );

     CREATE TABLE SECRETAR (

            №_Secretar           INTEGER NOT NULL,

            №_Sotrudnika         INTEGER,

            Oklad                CHAR(18)

     );

     CREATE UNIQUE INDEX XPKSECRETAR ON SECRETAR

     (

            №_Secretar,

            №_Sotrudnika

     );

     ALTER TABLE SECRETAR

            ADD PRIMARY KEY (№_Secretar, №_Sotrudnika);

     ALTER TABLE AGENT

            ADD FOREIGN KEY (№_Sotrudnika)

                                  REFERENCES SCHTAT;

     ALTER TABLE PRODAGI

            ADD FOREIGN KEY (№_Agenta, №_Sotrudnika)

                                  REFERENCES AGENT;

     ALTER TABLE AVTO_PRODAGI

            ADD FOREIGN KEY (№_Sotrudnika, №_Agenta, №_Prodagi)

                                  REFERENCES PRODAGI;

     ALTER TABLE AVTO_PRODAGI

            ADD FOREIGN KEY (№_Avto)

                                  REFERENCES AVTO;

     ALTER TABLE MEXAN

            ADD FOREIGN KEY (№_Sotrudnika)

                                  REFERENCES SCHTAT;

     ALTER TABLE MEXAN_AVTO

            ADD FOREIGN KEY (№_Avto)

                                  REFERENCES AVTO;

     ALTER TABLE MEXAN_AVTO

            ADD FOREIGN KEY (№_Mexanika, №_Sotrudnika)

                                  REFERENCES MEXAN;

     ALTER TABLE SECRETAR

            ADD FOREIGN KEY (№_Sotrudnika)

                                  REFERENCES SCHTAT; 

     CREATE EXCEPTION ERWIN_PARENT_INSERT_RESTRICT "Cannot INSERT Parent table because Child table exists.";

     CREATE EXCEPTION ERWIN_PARENT_UPDATE_RESTRICT "Cannot UPDATE Parent table because Child table exists.";

     CREATE EXCEPTION ERWIN_PARENT_DELETE_RESTRICT "Cannot DELETE Parent table because Child table exists.";

     CREATE EXCEPTION ERWIN_CHILD_INSERT_RESTRICT "Cannot INSERT Child table because Parent table does not exist.";

     CREATE EXCEPTION ERWIN_CHILD_UPDATE_RESTRICT "Cannot UPDATE Child table because Parent table does not exist.";

     CREATE EXCEPTION ERWIN_CHILD_DELETE_RESTRICT "Cannot DELETE Child table because Parent table does not exist."; 

     CREATE TRIGGER tD_SCHTAT FOR SCHTAT AFTER DELETE AS

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

       /* DELETE trigger on SCHTAT */

     DECLARE VARIABLE numrows INTEGER;

     BEGIN

         /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

         /* SCHTAT R/25 SECRETAR ON PARENT DELETE RESTRICT */

         select count(*)

           from SECRETAR

           where

             /*  %JoinFKPK(SECRETAR,OLD," = "," and") */

             SECRETAR.№_Sotrudnika = OLD.№_Sotrudnika into numrows;

         IF (numrows > 0) THEN

         BEGIN

           EXCEPTION ERWIN_PARENT_DELETE_RESTRICT;

         END 

         /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

         /* SCHTAT R/10 AGENT ON PARENT DELETE RESTRICT */

         select count(*)

           from AGENT

           where

             /*  %JoinFKPK(AGENT,OLD," = "," and") */

             AGENT.№_Sotrudnika = OLD.№_Sotrudnika into numrows;

         IF (numrows > 0) THEN

         BEGIN

           EXCEPTION ERWIN_PARENT_DELETE_RESTRICT;

         END 

         /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

         /* SCHTAT R/2 MEXAN ON PARENT DELETE RESTRICT */

         select count(*)

           from MEXAN

           where

             /*  %JoinFKPK(MEXAN,OLD," = "," and") */

             MEXAN.№_Sotrudnika = OLD.№_Sotrudnika into numrows;

         IF (numrows > 0) THEN

         BEGIN

           EXCEPTION ERWIN_PARENT_DELETE_RESTRICT;

         END 
 

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

     END !! 

     CREATE TRIGGER tU_SCHTAT FOR SCHTAT AFTER UPDATE AS

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

       /* UPDATE trigger on SCHTAT */

     DECLARE VARIABLE numrows INTEGER;

     BEGIN

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

       /* SCHTAT R/25 SECRETAR ON PARENT UPDATE RESTRICT */

       IF

         /* %JoinPKPK(OLD,NEW," <> "," or ") */

         (OLD.№_Sotrudnika <> NEW.№_Sotrudnika) THEN

       BEGIN

         select count(*)

           from SECRETAR

           where

             /*  %JoinFKPK(SECRETAR,OLD," = "," and") */

             SECRETAR.№_Sotrudnika = OLD.№_Sotrudnika into numrows;

         IF (numrows > 0) THEN

         BEGIN

           EXCEPTION ERWIN_PARENT_UPDATE_RESTRICT;

         END

       END 

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

       /* SCHTAT R/10 AGENT ON PARENT UPDATE RESTRICT */

       IF

         /* %JoinPKPK(OLD,NEW," <> "," or ") */

         (OLD.№_Sotrudnika <> NEW.№_Sotrudnika) THEN

       BEGIN

         select count(*)

           from AGENT

           where

             /*  %JoinFKPK(AGENT,OLD," = "," and") */

             AGENT.№_Sotrudnika = OLD.№_Sotrudnika into numrows;

         IF (numrows > 0) THEN

         BEGIN

           EXCEPTION ERWIN_PARENT_UPDATE_RESTRICT;

         END

       END 

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

       /* SCHTAT R/2 MEXAN ON PARENT UPDATE RESTRICT */

       IF

         /* %JoinPKPK(OLD,NEW," <> "," or ") */

         (OLD.№_Sotrudnika <> NEW.№_Sotrudnika) THEN

       BEGIN

         select count(*)

           from MEXAN

           where

             /*  %JoinFKPK(MEXAN,OLD," = "," and") */

             MEXAN.№_Sotrudnika = OLD.№_Sotrudnika into numrows;

         IF (numrows > 0) THEN

         BEGIN

           EXCEPTION ERWIN_PARENT_UPDATE_RESTRICT;

         END

       END 
 

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

     END !! 

     CREATE TRIGGER tD_AGENT FOR AGENT AFTER DELETE AS

       /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

       /* DELETE trigger on AGENT */

     DECLARE VARIABLE numrows INTEGER;

     BEGIN

         /* ERwin Builtin Fri Dec 11 20:50:53 2009 */

         /* AGENT R/24 PRODAGI ON PARENT DELETE RESTRICT */

         select count(*)

           from PRODAGI

           where

             /*  %JoinFKPK(PRODAGI,OLD," = "," and") */

             PRODAGI.№_Agenta = OLD.№_Agenta and

             PRODAGI.№_Sotrudnika = OLD.№_Sotrudnika into numrows;

         IF (numrows > 0) THEN

         BEGIN

           EXCEPTION ERWIN_PARENT_DELETE_RESTRICT;