Lo schema relazionale DDL

Le definizioni che seguono sono state realizzate su un motore Oracle 7 installato sulla macchina chrome.sci.univr.it con sistema operativo HP-UX vers. 10.

Da notare i vincoli di integrità referenziale che sfruttano una caratteristica attiva di Oracle per la rimozione in cascata di tuple su più tabelle (ON DELETE CASCADE).

/**
 * Progetto: Gestione sito ftp - Corso di Telematica
 * Versione: consegnata
 */

-- Consente la vis. dei messaggi con DBMS_OUTPUT.PUTLINE
-- in un blocco PL/SQL
SET SERVEROUTPUT ON;

DROP TABLE lsm_general;
DROP TABLE author;
DROP TABLE primary_site;
DROP TABLE alternate_site;
DROP TABLE original_site;
DROP TABLE maintained_by;
DROP TABLE lsm_mandatory;

CREATE TABLE lsm_mandatory (
  X_Filename       CHAR(80),
  X_Os             CHAR(80),
                   PRIMARY KEY (X_Filename, X_Os),
  X_Directory      CHAR(255) NOT NULL,
  Title            CHAR(80) NOT NULL,
  Version          CHAR(10) NOT NULL,
  Entered_date     DATE NOT NULL,
  Description      CHAR(255) NOT NULL
);

INSERT INTO lsm_mandatory VALUES 
  ('zed-1.0.tgz', 'linux', 'software/linux/text','Zed', 
   '1.0', '10-JAN-98', 'powerful, multipurpose, 
    configurable Text Editor');

------------------------------------------------------------

CREATE TABLE lsm_general (
  X_Filename       CHAR(80),
  X_Os             CHAR(80),
	           FOREIGN KEY (X_Filename, X_Os) 
		     REFERENCES lsm_mandatory ON DELETE CASCADE,
                   PRIMARY KEY (X_Filename, X_Os),
  Keywords         CHAR(80),
  Platforms        CHAR(255),
  Copying_policy   CHAR(80)
);

INSERT INTO lsm_general VALUES
  ('zed-1.0.tgz', 'linux', 'editor', 'Unix and dos, tested on:
   Linux, HPUX, SunOS, AIX.', 'GPL');

------------------------------------------------------------

CREATE TABLE author (
  X_Filename       CHAR(80),
  X_Os             CHAR(80),
	           FOREIGN KEY (X_Filename, X_Os) 
		     REFERENCES lsm_mandatory ON DELETE CASCADE,
                   PRIMARY KEY (X_Filename, X_Os),
  X_Fullname       CHAR(80), -- formato RFC822: (Fullname)
  X_Email          CHAR(80)  -- formato RFC822: mailname@site.domain.top
);

INSERT INTO author VALUES
  ('zed-1.0.tgz', 'linux', 'Sandro Serafini', 
   'saseraf@tin.it');

------------------------------------------------------------

CREATE TABLE primary_site (
  X_Filename       CHAR(80),
  X_Os             CHAR(80),
	           FOREIGN KEY (X_Filename, X_Os) 
		     REFERENCES lsm_mandatory ON DELETE CASCADE,
                   PRIMARY KEY (X_Filename, X_Os),
  X_Site           CHAR(80),
  X_Size           FLOAT(1),
  X_Unit           CHAR(4),
  X_Basedirectory  CHAR(255),
  X_Check          CHAR(255)
);

INSERT INTO primary_site VALUES 
  ('zed-1.0.tgz', 'linux', 'sunsite.unc.edu', '167', 'KB',
   '/pub/Linux/apps/editors', 'MD5SUM');

------------------------------------------------------------

CREATE TABLE alternate_site (
  X_Filename       CHAR(80),
  X_Os             CHAR(80),
	           FOREIGN KEY (X_Filename, X_Os) 
		     REFERENCES lsm_mandatory ON DELETE CASCADE,
                   PRIMARY KEY (X_Filename, X_Os),
  X_Site           CHAR(80),
  X_Size           FLOAT(1),
  X_Unit           CHAR(4),
  X_Basedirectory  CHAR(255),
  X_Check          CHAR(255)
);

INSERT INTO alternate_site VALUES 
  ('zed-1.0.tgz', 'linux', '', '', '', '', 'MD5SUM');

------------------------------------------------------------

CREATE TABLE original_site (
  X_Filename       CHAR(80),
  X_Os             CHAR(80),
	           FOREIGN KEY (X_Filename, X_Os)
		     REFERENCES lsm_mandatory ON DELETE CASCADE,
                   PRIMARY KEY (X_Filename, X_Os),
  X_Site           CHAR(80),
  X_Size           FLOAT(1),
  X_Unit           CHAR(4),
  X_Basedirectory  CHAR(255),
  X_Check          CHAR(255)
);

INSERT INTO original_site VALUES 
  ('zed-1.0.tgz', 'linux', 'http://space.tin.it/io/saserafi/zed',
   '167', 'KB', '', 'MD5SUM');

------------------------------------------------------------

CREATE TABLE maintained_by (
  X_Filename       CHAR(80),
  X_Os             CHAR(80),
	           FOREIGN KEY (X_Filename, X_Os) 
		     REFERENCES lsm_mandatory ON DELETE CASCADE,
                   PRIMARY KEY (X_Filename, X_Os),
  X_Fullname       CHAR(80), -- formato RFC822: (Fullname)
  X_Email          CHAR(80)  -- formato RFC822: mailname@site.domain.top
);

INSERT INTO maintained_by VALUES
  ('zed-1.0.tgz', 'linux', 'Sandro Serafini', 'saseraf@tin.it');

------------------------------------------------------------