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

The database script looks like this (using 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,
     CITY CITY_NAME NOT NULL,
     DESCRIPTION DESCRIPTION NOT NULL,
     PRIMARY KEY (APPRENTICESHIP_CODE)
  );
  CREATE TABLE APPRENTICESHIP_PREFERENCE (
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     NUMBER NUMBER NOT NULL,
     APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
     UNIQUE (FIRST_NAME, SURNAME, NUMBER),
     UNIQUE (FIRST_NAME, SURNAME, APPRENTICESHIP)
  );
  CREATE TABLE ASSIGNED_APPRENTICESHIP (
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
     UNIQUE (FIRST_NAME, SURNAME),
     UNIQUE (APPRENTICESHIP)
  );
  CREATE TABLE STUDENT (
     FIRST_NAME FIRST_NAME NOT NULL,
     SURNAME SURNAME NOT NULL,
     CITY1 CITY_NAME NOT NULL,
     CITY2 CITY_NAME NOT NULL,
     PRIMARY KEY (FIRST_NAME, SURNAME)
  );
  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);

 



 

Add comment


Security code
Refresh

Casetalk Logo

CaseTalk Sponsors

dmz

16-18 Oct, DMZ US

22-23 Oct, DMZ EU

Read More...

Share