1 Replies - 222 Views - Last Post: 02 June 2014 - 10:33 AM Rate Topic: -----

#1 forev3r  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 24-May 14

Specialization Hierarchy SQL

Posted 26 May 2014 - 04:52 PM

Attached to this post I have my logical model for a mechanics company in visio. I am a little confused as to how to go about writing the sql code to invoke the specialization hierarchy. here is my sql code for the employee table (supertype to mechanic and administrator)

CREATE TABLE EMPLOYEE
(
	STAFF_ID			  INT				NOT NULL		CONSTRAINT PK_EMPLOYEE PRIMARY KEY, -- set as primary key
	STAFF_TYPE			  VARCHAR(10)		NOT NULL		CONSTRAINT CHECK_EMPLOYEE_EMP_TYPE CHECK (STAFF_TYPE IN('Mech', 'Admin')), -- check that employee is either a mechanic or administrator
	STAFF_BRANCH_ID		  INT				NOT NULL		CONSTRAINT FK1_EMPLOYEE_CENTRE FOREIGN KEY REFERENCES dbo.CENTRE(BRANCH_ID), --set as foreign key
	STAFF_MANAGER		  INT				NULL			CONSTRAINT FK2_EMPLOYEE_EMPLOYEE FOREIGN KEY REFERENCES dbo.EMPLOYEE(STAFF_ID),	--set as foreign key
	STAFF_FNAME			  CHAR(50)		    NOT NULL,
	STAFF_LNAME			  CHAR(50)		    NOT NULL,
	STAFF_STREET		  CHAR(50)		    NOT NULL,
	STAFF_SUBURB		  CHAR(50)		    NOT NULL,
	STAFF_CITY			  CHAR(50)		    NOT NULL,
	STAFF_DOB			  DATE				NOT NULL,
	STAFF_HIRE_DATE		  DATETIME		    NOT NULL,
	CONSTRAINT U1_EMPLOYEE_EMPID_EMPTYPE UNIQUE (STAFF_ID, STAFF_TYPE) --set as uniquness constraint
);

--create mechanic table
CREATE TABLE MECHANIC
(
	STAFF_ID				INT		     NOT NULL CONSTRAINT PK_MECHANIC PRIMARY KEY --set primary key
										          CONSTRAINT FK1_MECHANIC_EMPLOYEE FOREIGN KEY REFERENCES dbo.EMPLOYEE(STAFF_ID) --set as foreign key
												  CONSTRAINT FK2_MECHANIC_EMPLOYEE FOREIGN KEY REFERENCES dbo.EMPLOYEE(STAFF_ID),
	STAFF_TYPE				VARCHAR(10)  NOT NULL CONSTRAINT CHECK_MECHANIC_STAFF_TYPE CHECK (STAFF_TYPE = 'Mech'), --check to make sure that staff is a mechanic
	MECHANIC_TITLE			VARCHAR(50)  NOT NULL,
	MECHANIC_SPECALISATION	VARCHAR(50)  NOT NULL,
	MECHANIC_CHARGE_RATE	DECIMAL(3,2) NOT NULL
);

CREATE TABLE ADMINISTRATOR
(
	STAFF_ID						INT		 NOT NULL CONSTRAINT PK_ADMINISTRATOR PRIMARY KEY --set as primary key
													  CONSTRAINT FK1_ADMINISTRATOR FOREIGN KEY REFERENCES dbo.EMPLOYEE(STAFF_ID) --set as foreign key
													  CONSTRAINT FK2_ADMINISTRATOR FOREIGN KEY REFERENCES dbo.EMPLOYEE(STAFF_ID), --set as foreignkey 2
	STAFF_TYPE					VARCHAR(10)  NOT NULL CONSTRAINT FK3_ADMINISTRATOR FOREIGN KEY REFERENCES dbo.EMPLOYEE(STAFF_TYPE), --set as foreign key 3
	ADMINISTRATOR_CERTIFICATION VARCHAR(50)  NOT NULL,
	ADMINISTRATOR_BONUS		    DECIMAL(3,2) NOT NULL
);



Im just having trouble getting constraints to invoke properly etc... am i going in the right direction?

Cheers

Is This A Good Question/Topic? 0
  • +

Replies To: Specialization Hierarchy SQL

#2 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,383
  • Joined: 15-January 14

Re: Specialization Hierarchy SQL

Posted 02 June 2014 - 10:33 AM

What do you mean? The constraints aren't working?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1