locked
Data Modeling - implementing subtyping RRS feed

  • Question

  • I am fairly new to SQL and database creation. So far things are progressing nicely but I think I have hit a stumbling block with a super-subtype relationship btween one super type and 2 subtypes.

    http://img52.imageshack.us/img52/1433/subtyping.jpg

    That is how i have modeled the relationships. I need to satisfy both sub-typing and a one-many relationship. All seemed to be going well but after reading http://blogs.msdn.com/b/dfurman/archive/2009/08/31/disjoint-subtyping-in-sql.aspx this article I am certain I have not implimented the relationship correctly in my code.

     

    Create table tblStaff(
    /*table attributes */
    staffNo char(4) not null,
    sFName varchar(30) not null,
    sSName varchar(30) not null,
    sAddress varchar(30) not null,
    sPostcode char(8) not null,
    sTelNo varchar(11) not null,
    sEmail varchar(30),
    sDOB varchar(10) not null,
    /*This attribute is set to allow a recursive relationship between Staff and Staff*/
    sSupervisorNo char(4),
    /*A subtype discrimination, stored as a column in the supertype (tblStaff) as a single character*/
    sPosition char(1) not null,
    
    /*keys*/
    constraint staff_Pkey primary key(staffNo),
    
    /*Constraints*/
    constraint staffNo_format check (staffNo between '0001' and '9999'),
    constraint staffNo_Uniquie UNIQUIE(staffNo),
    constraint staff_Postcodes check ((sPostcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (sPost_code like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')),
    constraint staff_sTelNo check (sTelNo like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 
    constraint staff_DOB check(sDOB like '[0-9][0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]'),
    constraint sPosition_check check((sPosition = I) OR (sPosition = M)),
    ); /*End tblStaff*/
    
    Create table tblInstructor(
    /*table attributes */
    instructorNo char(4) not null,
    iLevel char(3) not null,
    
    /*keys*/
    constraint instructor_Pkey primary key(instructorNo),
    /* primry key is foreign key which references the supertable tblStaff. This child table is the subtable of the super-subtype relationship
     between tblStaff and tblInstructor*/
    constraint ikey_fkey foreign key(instructorNo) references tblStaff(staffNo) ON UPDATE CASCADE ON DELETE NO ACTION,
    /*Constraints*/
    constraint instructorNo_format check (instructorNo between '0001' and '9999'),
    constraint sPosition_check check(iLevel = HGV OR iLevel = C or iLevel = B4 or iLevel = A1),
    );/*End tblInstructor */
    
    Create table tblMechanic(
    /*table attributes */
    mechanicNo char(4) not null,
    bike int(1) not null,
    car int (1) not null,
    /*keys*/
    constraint mechanicNo_Pkey primary key(mechanicNo),
    /* primry key is foreign key which references the supertable tblStaff. This child table is the subtable of the super-subtype relationship
     between tblStaff and tblInstructor.*/
    constraint mechanicNo_Fkey foreign key(mechanicNo) references tblStaff(staffNo) ON UPDATE CASCADE ON DELETE NO ACTION,
    
    /*Constraints*/
    constraint mechanicNo_format check (mechanicNo between '0001' and '9999'),
    );/*End tblMechanic*/
    

    From what I have read in the books I have a subtype relationship is achieved when the foreign key of the subtable is the primary key for that entity this creating a relational dependence.

     

    I understand that this may be a very common issue but I am begining to panic that I have progressed with the design and now it is faulty.

     

    please help

    Monday, August 16, 2010 2:39 PM

Answers

  • Ok, Here is what I was referring

    Your Staff table IsPosition column accepts only two values either I or M,  Now modify the Instructor table like below.

     CREATE TABLE tblInstructor
     ( 
     instructorNo char(4) not null,
     iPosition char(1) ISNULL(CAST('I' AS CHAR(1)),'') PERSISTED 
     iLevel char(3) not null,
    
     /*keys*/
      constraint instructor_Pkey primary key(instructorNo),
      
      /* primry key is foreign key which references the supertable tblStaff. This child table is the subtable of the super-subtype relationship
      between tblStaff and tblInstructor*/
      constraint ikey_fkey foreign key(instructorNo, iPosition) references tblStaff(staffNo, sPosition) ON UPDATE CASCADE ON DELETE NO ACTION,
     
      constraint instructorNo_format check (instructorNo between '0001' and '9999'),
      constraint sPosition_check check(iLevel = HGV OR iLevel = C or iLevel = B4 or iLevel = A1),
     ); 
    

    Now the iKey_fkey references to StaffId and the sPosition so the constraint of staff can be either an instructor or a mechanic is enforced.

    You need to do similar changes to Mechanic table. Let me know if you have more questions.

     


    Ranjith | My Blog
    • Marked as answer by Kent Waldrop Wednesday, December 22, 2010 3:43 PM
    Tuesday, August 17, 2010 7:11 AM
  • Yes you did it right and you dont need CAST as explained in post but you have achieved the purpose with check constraints. 

    >>Also one more thing. How does my code look for format?

    You need to follow proper indentation for writing good SQL code or any language code for that matter. And you also do not need comments to explain the code as the statement itself is self descriptive. Bing for some sample written SQL code by good developers and you can go from there.


    Ranjith | My Blog
    Tuesday, August 17, 2010 12:15 PM
  • catscratched,

    The final implementation looks ok. One more thing you can do is to add a default constraint for column [?Position] in each subtype table, so you do not need to provide values for this column, or be able to create a view and hide this column from the users since you do not want this column to be updated even though you have a check constraint.

    So, for instructor "add constraint DF_Instructor_Position default('I')" and for mechanic "add constraint DF_Mechanic_Position default('M')". I would suggest to name the column as [position]. Same suggestion for Staff columns, get rid of the "s" prefix and "tbl" too.

    AMB

    • Marked as answer by Kent Waldrop Wednesday, December 22, 2010 3:43 PM
    Tuesday, August 17, 2010 3:05 PM

All replies

  • No need to panic, you might have to correct some data where the staff is a member of both Instructor and Mechanic tables and write a script to force the relationship as explained in article.

    The design is not entirely faulty but it does not create a disjoint sub type relationship (i.e. Entity of super type is atmost related to one subtype) rather here the staff entity could be in both Mechanic and an Instructor tables.

    You already have a column to discriminate the sub types (isPosition), So you need to add that column to subtype tables and ALTER the foreign keys as explained in article.


    Ranjith | My Blog
    Monday, August 16, 2010 6:00 PM
  • The idea was that members of each subtype can not be members of the other, so mechanics cant be instructors and vice versa.

    When you say entity is at most related to one subtype is that refering to the above where the supertype could have members of both?

    I am a little confused by your final paragraph. I have read that the discriminator is added to the super table only. Honestly I dont understand what it is actually used for aside from its usefulness in querying however surely the atributes of the subtables would be left out of any query on the super table. I havent quite made it that far yet.

     

    Thanks for the reply

     

    Monday, August 16, 2010 9:15 PM
  • Ok, Here is what I was referring

    Your Staff table IsPosition column accepts only two values either I or M,  Now modify the Instructor table like below.

     CREATE TABLE tblInstructor
     ( 
     instructorNo char(4) not null,
     iPosition char(1) ISNULL(CAST('I' AS CHAR(1)),'') PERSISTED 
     iLevel char(3) not null,
    
     /*keys*/
      constraint instructor_Pkey primary key(instructorNo),
      
      /* primry key is foreign key which references the supertable tblStaff. This child table is the subtable of the super-subtype relationship
      between tblStaff and tblInstructor*/
      constraint ikey_fkey foreign key(instructorNo, iPosition) references tblStaff(staffNo, sPosition) ON UPDATE CASCADE ON DELETE NO ACTION,
     
      constraint instructorNo_format check (instructorNo between '0001' and '9999'),
      constraint sPosition_check check(iLevel = HGV OR iLevel = C or iLevel = B4 or iLevel = A1),
     ); 
    

    Now the iKey_fkey references to StaffId and the sPosition so the constraint of staff can be either an instructor or a mechanic is enforced.

    You need to do similar changes to Mechanic table. Let me know if you have more questions.

     


    Ranjith | My Blog
    • Marked as answer by Kent Waldrop Wednesday, December 22, 2010 3:43 PM
    Tuesday, August 17, 2010 7:11 AM
  • Thank you for your reply this is most helpful. I didnt quite understand the example in the blog post so i had a stab at doing it myself. If you would be so kind as to just review my attempt to see if it is correct it would be a great help. I think it is correct although more primitive, i havent dealt with casting in sql but where I am familiar with it in java i was advised against it. I see in this situation it is basically doing the same thing as the checks I have enlisted.

    Also one more thing. How does my code look for format?

     

    Thanks in advance, you have been a great help.

     

    Create table tblStaff(
    /*table attributes */
    staffNo char(4) not null,
    sFName varchar(30) not null,
    sSName varchar(30) not null,
    sAddress varchar(30) not null,
    sPostcode char(8) not null,
    sTelNo varchar(11) not null,
    sEmail varchar(30),
    sDOB varchar(10) not null,
    /*This attribute is set to allow a recursive relationship between Staff and Staff*/
    sSupervisorNo char(4),
    /*A subtype discrimination, stored as a column in the supertype (tblStaff) as a single character*/
    sPosition char(1) not null,
    sDSchoolNo char(4) not null,
    
    /*keys*/
    constraint staff_Pkey primary key(staffNo),
    /*This constraint enforces the one-many relationship between Driving school and staff*/
    constraint staffSchoolNo_fKey foreign key(sDSchoolNo) references tblDrivingSchool(schoolNo),
    
    /*Constraints*/
    constraint staffNo_format check (staffNo between 'S001' and 'S999'),
    constraint staff_Postcodes check ((sPostcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (sPost_code like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')),
    constraint staff_sTelNo check (sTelNo like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 
    constraint staff_DOB check(sDOB like '[0-9][0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]'),
    /*constraint check that input character matches I or M */
    constraint sPosition_check check (sPosition IN ('I','M' /* Instructor or Mechanic */)),
    constraint sStaff_PositionUniquie UNIQUE (staffNo, sPosition),
    ); /*End tblStaff*/
    
    Create table tblInstructor(
    /*table attributes */
    instructorNo char(4) not null,
    iPosition char(1) not null,
    iLevel char(3) not null,
    
    /*keys*/
    constraint instructor_Pkey primary key(instructorNo),
    /* primry key is foreign key which references the supertable tblStaff. This child table is the subtable of the super-subtype relationship
     between tblStaff and tblInstructor*/
    constraint ikey_fkey foreign key(instructorNo, iPosition) references tblStaff(staffNo, sPosition) ON UPDATE CASCADE ON DELETE NO ACTION,
    /*Constraints*/
    constraint instructorNo_format check (instructorNo between 'S001' and 'S999'),
    constraint iPositionCheckInstructor check (iPosition ='I'),
    constraint iPosition_check check(iLevel IN ('HGV', 'A1', 'B4', 'C' )),
    );/*End tblInstructor */
    
    Create table tblMechanic(
    /*table attributes */
    mechanicNo char(4) not null,
    mPosition char(1) not null,
    bike int(1) not null,
    car int (1) not null,
    /*keys*/
    constraint mechanicNo_Pkey primary key(mechanicNo),
    /* primry key is foreign key which references the supertable tblStaff. This child table is the subtable of the super-subtype relationship
     between tblStaff and tblInstructor.*/
    constraint mechanicNo_Fkey foreign key(mechanicNo, mPosition) references tblStaff(staffNo, mPosition) ON UPDATE CASCADE ON DELETE NO ACTION,
    
    /*Constraints*/
    constraint mechanicNo_format check (mechanicNo between 'S001' and 'S999'),
    constraint mechanicCheckPositionM check (mPosition ='M'),
    );/*End tblMechanic*/
    

    Tuesday, August 17, 2010 11:40 AM
  • Yes you did it right and you dont need CAST as explained in post but you have achieved the purpose with check constraints. 

    >>Also one more thing. How does my code look for format?

    You need to follow proper indentation for writing good SQL code or any language code for that matter. And you also do not need comments to explain the code as the statement itself is self descriptive. Bing for some sample written SQL code by good developers and you can go from there.


    Ranjith | My Blog
    Tuesday, August 17, 2010 12:15 PM
  • catscratched,

    The final implementation looks ok. One more thing you can do is to add a default constraint for column [?Position] in each subtype table, so you do not need to provide values for this column, or be able to create a view and hide this column from the users since you do not want this column to be updated even though you have a check constraint.

    So, for instructor "add constraint DF_Instructor_Position default('I')" and for mechanic "add constraint DF_Mechanic_Position default('M')". I would suggest to name the column as [position]. Same suggestion for Staff columns, get rid of the "s" prefix and "tbl" too.

    AMB

    • Marked as answer by Kent Waldrop Wednesday, December 22, 2010 3:43 PM
    Tuesday, August 17, 2010 3:05 PM