Hi all,

Sorry about the large post, I think I have completed my metadata, and have
implemented most of the suggestions received. I would appreciate any further
comments, as I will begin connecting my application to the database, and
filling it. Hence I would like to be satisfied of its correctness prior to
this.

Thanks Cassandra

NB. As noted by Matijn earlier, I know this is a large post, could you
please ensure you delete this original message when replying.



//***********************************************************
CREATE DATABASE 'se_gdb_0_1_0.gdb' PAGE_SIZE 2048 USER 'SYSDBA' PASSWORD
'masterkey';
COMMIT;

CREATE DOMAIN D_Dummy as CHAR(4);
CREATE DOMAIN D_Address AS VARCHAR(38);
CREATE DOMAIN D_ABN AS VARCHAR(11);
CREATE DOMAIN D_Branch AS INTEGER DEFAULT 1;
CREATE DOMAIN D_Name AS VARCHAR(200);
CREATE DOMAIN D_Town AS VARCHAR(27);
CREATE DOMAIN D_State AS CHAR(3)
CHECK (VALUE IN ('ACT', 'NSW', 'NT', 'QLD', 'SA', 'VIC', 'WA', 'TAS',
'OTH'));
CREATE DOMAIN D_OthState AS VARCHAR(20);
CREATE DOMAIN D_Postcode AS VARCHAR(20);
CREATE DOMAIN D_Country AS VARCHAR(20);
CREATE DOMAIN D_Contact AS VARCHAR(20);
CREATE DOMAIN D_Phone AS VARCHAR(15);
CREATE DOMAIN D_TFN AS VARCHAR(9);
CREATE DOMAIN D_Date AS DATE;
CREATE DOMAIN D_Surname AS VARCHAR(30);
CREATE DOMAIN D_Given AS VARCHAR(15);
CREATE DOMAIN D_Memo AS BLOB SUB_TYPE 1;
CREATE DOMAIN D_Monetary AS DECIMAL(12,2);
CREATE DOMAIN D_ID AS INTEGER;
CREATE DOMAIN D_Number AS VARCHAR(20);
CREATE DOMAIN D_Hour AS DECIMAL(4,2);
CREATE DOMAIN D_Bool AS INTEGER
CHECK ((VALUE = 0) OR (VALUE = 1));

CREATE TABLE T_State
(C_State_Init D_State NOT NULL,
C_StateName D_Country,
PRIMARY KEY (C_State_Init));

CREATE TABLE T_Employer
(C_Employer_ID D_ID NOT NULL,
C_ABN D_ABN NOT NULL,
C_Branch D_Branch NOT NULL,
CONSTRAINT U_ABN UNIQUE (C_ABN, C_Branch),
C_Name D_Name,
C_Trading D_Name,
C_Address1 D_Address,
C_Address2 D_Address,
C_Town D_Town,
C_State D_State,
C_Oth_State D_OthState,
C_Postcode D_Postcode,
C_Country D_Country,
C_Contact D_Contact,
C_ContactPhone D_Phone,
C_ContactFax D_Phone,
PRIMARY KEY (C_Employer_ID),
FOREIGN KEY (C_State) REFERENCES T_State (C_State_Init)
);
COMMIT;

CREATE TABLE T_Owner
(C_Owner_ID D_ID NOT NULL,
C_ABN D_ABN,
C_Branch D_Branch,
C_Trading D_Name,
C_Address1 D_Address,
C_Address2 D_Address,
C_Town D_Town,
C_State D_State,
C_Oth_State D_OthState,
C_Postcode D_Postcode,
C_Contact D_Contact,
C_ContactPhone D_Phone,
C_ContactFax D_Phone,
C_Memo D_Memo,
PRIMARY KEY (C_Owner_ID),
FOREIGN KEY (C_State) REFERENCES T_State (C_State_Init)
ON UPDATE CASCADE
ON DELETE CASCADE);
COMMIT;

CREATE TABLE T_Field
(C_Field_ID D_ID NOT NULL,
C_Owner_ID D_ID NOT NULL,
C_Name D_Name,
C_Location1 D_Name,
C_Location2 D_Name,
C_Memo D_Memo,
PRIMARY KEY (C_Field_ID),
FOREIGN KEY (C_Owner_ID) REFERENCES T_Owner (C_Owner_ID)
ON UPDATE CASCADE
ON DELETE CASCADE);
COMMIT;

CREATE TABLE T_Payslip
(C_Payslip_ID D_ID NOT NULL,
C_Tax_Withheld D_Monetary,
PRIMARY KEY (C_Payslip_ID));
COMMIT;

CREATE TABLE T_Superfund
(C_Superfund_ID D_ID NOT NULL,
C_FundName D_Name NOT NULL,
CONSTRAINT U_Fund UNIQUE (C_FundName),
C_Address1 D_Address,
C_Address2 D_Address,
C_Town D_Town,
C_State D_State,
C_Postcode D_Postcode,
PRIMARY KEY (C_Superfund_ID),
FOREIGN KEY (C_State) REFERENCES T_State (C_State_Init)
ON UPDATE CASCADE
ON DELETE CASCADE);

CREATE TABLE T_Employee
(C_Employee_ID D_ID NOT NULL,
C_Employer_ID D_ID NOT NULL,
C_TFN D_TFN,
C_DOB D_Date,
C_Surname D_Surname,
C_Firstname D_Given,
C_Secondname D_Given,
C_Address1 D_Address,
C_Address2 D_Address,
C_Town D_Town,
C_State D_State,
C_Oth_State D_OthState,
C_Postcode D_Postcode,
C_Country D_Country,
C_TaxFree_Threshold D_Bool,
C_Hecs_Debt D_Bool,
C_TFN_Super_Auth D_Bool,
C_Aus_Res D_Bool,
C_Current_Superfund_ID D_ID,
PRIMARY KEY (C_Employee_ID),
FOREIGN KEY (C_Employer_ID) REFERENCES T_Employer (C_Employer_ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (C_Current_Superfund_ID) REFERENCES T_Superfund
(C_Superfund_ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (C_State) REFERENCES T_State (C_State_Init)
ON UPDATE CASCADE
ON DELETE CASCADE);
COMMIT;
CREATE INDEX I_Employee_Surname ON T_Employee (C_Surname);
CREATE INDEX I_Employee_Firstname ON T_Employee (C_Firstname);
COMMIT;

CREATE TABLE T_Timesheet
(C_Timesheet_ID D_ID NOT NULL,
C_Field_ID D_ID NOT NULL,
C_Employee_ID D_ID NOT NULL,
C_Date D_Date NOT NULL,
CONSTRAINT U_Timesheet UNIQUE (C_Field_ID, C_Employee_ID, C_Date),
C_Hours D_Hour,
C_Rate D_Monetary,
C_Payslip_ID D_ID,
PRIMARY KEY (C_Timesheet_ID),
FOREIGN KEY (C_Field_ID) REFERENCES T_Field (C_Field_ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (C_Employee_ID) REFERENCES T_Employee (C_Employee_ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (C_Payslip_ID) REFERENCES T_Payslip (C_Payslip_ID)
ON UPDATE CASCADE
ON DELETE SET NULL);
COMMIT;
CREATE INDEX I_Timesheet_Field ON T_Timesheet (C_Field_ID);
CREATE INDEX I_Timesheet_Employee ON T_Timesheet (C_Employee_ID);
CREATE INDEX I_Timesheet_Date ON T_Timesheet (C_Date);
CREATE INDEX I_Timesheet_Payslip ON T_Timesheet (C_Payslip_ID);
COMMIT;

CREATE TABLE T_SuperSummary
(C_SuperSummary_ID D_ID NOT NULL,
C_SuperDue D_Monetary,
C_SuperFund_ID D_ID,
PRIMARY KEY (C_SuperSummary_ID),
FOREIGN KEY (C_Superfund_ID) REFERENCES T_Superfund (C_Superfund_ID)
);

CREATE TABLE T_Employee_Sup
(C_Employee_ID D_ID NOT NULL,
C_Superfund_ID D_ID NOT NULL,
C_Member_Number D_Number,
C_TFN_Provided D_Bool,
PRIMARY KEY (C_Employee_ID, C_Superfund_ID)
);

CREATE TABLE T_Employer_Sup
(C_Employer_ID D_ID NOT NULL,
C_Superfund_ID D_ID NOT NULL,
C_Member_Number D_Number,
PRIMARY KEY (C_Employer_ID, C_Superfund_ID)
);