Flexible Models - Database SCRIPT FLEXIBLE
Article Index
Flexible Models
Conceptual Model
Optimal Normal Form
Database Script
Flexible Database Model
Database SCRIPT FLEXIBLE
Conclusion
All Pages

The script for a flexible model looks like this (again sql92 notation):

  CREATE SCHEMA LESSON
  CREATE DOMAIN APPRENTICESHIP_CODE AS VARCHAR(4);
  CREATE DOMAIN CITY_NAME AS VARCHAR(10);
  CREATE DOMAIN DESCRIPTION AS VARCHAR(48);
  CREATE DOMAIN FIRST_NAME AS VARCHAR(5);
  CREATE DOMAIN NUMBER AS INTEGER
     CHECK (VALUE IN (1, 2, 3));
  CREATE DOMAIN SURNAME AS VARCHAR(8);
  CREATE TABLE APPRENTICESHIP (
     APPRENTICESHIP_CODE APPRENTICESHIP_CODE NOT NULL,
     PRIMARY KEY (APPRENTICESHIP_CODE)
  );
  CREATE TABLE APPRENTICESHIP_CITY (
     APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
     CITY CITY_NAME NOT NULL,
     PRIMARY KEY (APPRENTICESHIP)
  );
  CREATE TABLE APPRENTICESHIP_DESCRIPTION (
     APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
     DESCRIPTION DESCRIPTION NOT NULL,
     PRIMARY KEY (APPRENTICESHIP)
  );
  CREATE TABLE APPRENTICESHIP_PREFERENCE (
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     NUMBER NUMBER NOT NULL,
     APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
     PRIMARY KEY (FIRST_NAME, SURNAME, NUMBER),
     UNIQUE (FIRST_NAME, SURNAME, APPRENTICESHIP)
  );
  CREATE TABLE ASSIGNED_APPRENTICESHIP (
     APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     PRIMARY KEY (APPRENTICESHIP),
     UNIQUE (FIRST_NAME, SURNAME)
  );
  CREATE TABLE CITY (
     CITY_NAME CITY_NAME NOT NULL,
     PRIMARY KEY (CITY_NAME)
  );
  CREATE TABLE CITY_OF_RESIDENCE (
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     CITY CITY_NAME NOT NULL,
     PRIMARY KEY (FIRST_NAME, SURNAME)
  );
  CREATE TABLE NATIVE_CITY (
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     CITY CITY_NAME NOT NULL,
     PRIMARY KEY (FIRST_NAME, SURNAME)
  );
  CREATE TABLE STUDENT (
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     PRIMARY KEY (FIRST_NAME, SURNAME)
  );
  ALTER TABLE CITY_OF_RESIDENCE
     ADD FOREIGN KEY (FIRST_NAME, SURNAME)
     REFERENCES STUDENT (FIRST_NAME, SURNAME);
  ALTER TABLE STUDENT
     ADD FOREIGN KEY (FIRST_NAME, SURNAME)
     REFERENCES CITY_OF_RESIDENCE (FIRST_NAME, SURNAME);
  ALTER TABLE CITY_OF_RESIDENCE
     ADD FOREIGN KEY (CITY)
     REFERENCES CITY (CITY_NAME);
  ALTER TABLE APPRENTICESHIP_CITY
     ADD FOREIGN KEY (APPRENTICESHIP)
     REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
  ALTER TABLE APPRENTICESHIP
     ADD FOREIGN KEY (APPRENTICESHIP_CODE)
     REFERENCES APPRENTICESHIP_CITY (APPRENTICESHIP);
  ALTER TABLE APPRENTICESHIP_CITY
     ADD FOREIGN KEY (CITY)
     REFERENCES CITY (CITY_NAME);
  ALTER TABLE APPRENTICESHIP_DESCRIPTION
     ADD FOREIGN KEY (APPRENTICESHIP)
     REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
  ALTER TABLE APPRENTICESHIP
     ADD FOREIGN KEY (APPRENTICESHIP_CODE)
     REFERENCES APPRENTICESHIP_DESCRIPTION (APPRENTICESHIP);
  ALTER TABLE APPRENTICESHIP_PREFERENCE
     ADD FOREIGN KEY (FIRST_NAME, SURNAME)
     REFERENCES STUDENT (FIRST_NAME, SURNAME);
  ALTER TABLE APPRENTICESHIP_PREFERENCE
     ADD FOREIGN KEY (APPRENTICESHIP)
     REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
  ALTER TABLE ASSIGNED_APPRENTICESHIP
     ADD FOREIGN KEY (FIRST_NAME, SURNAME)
     REFERENCES STUDENT (FIRST_NAME, SURNAME);
  ALTER TABLE ASSIGNED_APPRENTICESHIP
     ADD FOREIGN KEY (APPRENTICESHIP)
     REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
  ALTER TABLE NATIVE_CITY
     ADD FOREIGN KEY (FIRST_NAME, SURNAME)
     REFERENCES STUDENT (FIRST_NAME, SURNAME);
  ALTER TABLE STUDENT
     ADD FOREIGN KEY (FIRST_NAME, SURNAME)
     REFERENCES NATIVE_CITY (FIRST_NAME, SURNAME);
  ALTER TABLE NATIVE_CITY
     ADD FOREIGN KEY (CITY)
     REFERENCES CITY (CITY_NAME);

 



 

Add comment


Security code
Refresh

Casetalk Logo

CaseTalk Sponsors

dmz

16-18 Oct, DMZ US

22-23 Oct, DMZ EU

Read More...

Share