Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: implementing supertype-subtype

    my project needs an implementation of supertype-subtype, however, we haven't tackled that topic yet at school. i know the concept of supertype-subtype, but i don't know how to write it out on mssql. i even don't know how to insert data using supertype-subtype.

    here's the reqs's:
    * there is a supertype named DOCUMENT. it has attributes such as DATE ISSUED, NAME OF REQUESTOR..
    * one subtype is clearance
    * another subtype is business clearance

    now my question:
    * how would i code this in mssql?
    * how would i enter data if, for example, i will get a business clearance document?
    * what if the document to be issued is neither clearance nor business clearance?


    please help me out. i'm completely lost.

    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I thought exam time was over. read your book. come back we you have a specific question you need help with.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2009
    Posts
    7
    no, this isn't a acad related project. this is a real project, i'll be doing an document issuance system for our municipality. i need an urgent help about this supertype subtype thing.

    this is the erd i created for my project.

    http://i49.tinypic.com/qq4qog.gif

  4. #4
    Join Date
    Dec 2009
    Posts
    7
    this is my code, i just don't know how the supertype subtype be written correctly in ms sql.

    i hope my guess is correct. help me

    Code:
    CREATE TABLE Person (
       PersonID INT
          CONSTRAINT PK_Person_PersonID PRIMARY KEY,
       LName VARCHAR(25) NOT NULL,
       GName VARCHAR(30) NOT NULL,
       MName VARCHAR(25) NULL,
       Status VARCHAR(10) NULL,
       Address VARCHAR(50) NULL,
       Age INT NULL,
       Citizenship VARCHAR(15) NULL );
    
    CREATE TABLE Receipt (
       ORNo INT
          CONSTRAINT PK_Receipt_ORNo PRIMARY KEY,
       Nature VARCHAR(100) NOT NULL,
       FACode INT NULL,
       Amount SMALLMONEY,
       IssuePlace VARCHAR(20) NULL,
       PaymentType VARCHAR(10) NULL );
    
    CREATE TABLE CTC (
       CTCNo INT
          CONSTRAINT PK_CTC_CTCNo PRIMARY KEY,
       IssueDay SMALLINT NOT NULL,
       IssueMonth VARCHAR(9) NOT NULL,
       IssueYear VARCHAR(4) NOT NULL );
    
    CREATE TABLE Officer (
       OfficerID INT IDENTITY(1,1)
          CONSTRAINT PK_Officer_OfficerID PRIMARY KEY,
       Name VARCHAR(30) NOT NULL );
    
    CREATE TABLE Document (
       DocID INT
          CONSTRAINT PK_Document_DocID PRIMARY KEY,
       Requestor INT NOT NULL
          CONSTRAINT FK_Document_Requestor FOREIGN KEY REFERENCES Person(PersonID),
       Type VARCHAR(10) NOT NULL,
       OfficerID INT NULL
          CONSTRAINT FK_Document_OfficerID FOREIGN KEY(OfficerID) REFERENCES Officer,
       CTCNo INT NULL
          CONSTRAINT FK_Document_CTCNo FOREIGN KEY(CTCNo) REFERENCES CTC,
       ORNo INT NULL
          CONSTRAINT FK_Document_ORNo FOREIGN KEY(ORNo) REFERENCES Receipt,
       IssueDay SMALLINT NOT NULL,
       IssueMonth VARCHAR(9) NOT NULL );
    
    CREATE TABLE Clearance (
       DocID INT
          CONSTRAINT PK_Clearance_DocID PRIMARY KEY,
       Finding VARCHAR(100) NULL,
       Purpose VARCHAR(200) NULL,
       Type VARCHAR(9) DEFAULT 'Clearance' NOT NULL
          CONSTRAINT CK_Clearance_Type CHECK(Type = 'Clearance'),
       CONSTRAINT FK_Clearance_DocID FOREIGN KEY(DocID) REFERENCES Document(DocID) );
    
    CREATE TABLE BClearance (
       DocID INT
          CONSTRAINT PK_BClearance_DocID PRIMARY KEY,
       Position VARCHAR(35) NOT NULL,
       Address VARCHAR(50) NOT NULL,
       Type VARCHAR(10) DEFAULT 'Clearance' NOT NULL
          CONSTRAINT CK_BClearance_Type CHECK(Type = 'BClearance'),
       CONSTRAINT FK_BClearance_DocID FOREIGN KEY(DocID) REFERENCES Document(DocID) );
    
    CREATE TABLE AppBClearance (
       DocID INT
          CONSTRAINT PK_AppBClearance_DocID PRIMARY KEY,
       AppNo VARCHAR(10),
       BName VARCHAR(60) NOT NULL,
       Address VARCHAR(50) NOT NULL,
       Capital MONEY NULL,
       Sales MONEY NULL,
       AppState BIT NOT NULL,
       Type VARCHAR(13) DEFAULT 'AppBClearance' NOT NULL
          CONSTRAINT CK_AppBClearance_Type CHECK(Type = 'AppBClearance'),
       CONSTRAINT FK_AppBClearance_DocID FOREIGN KEY(DocID) REFERENCES Document(DocID) );
    
    CREATE TABLE OtherDoc (
       DocID INT
          CONSTRAINT PK_OtherDoc_DocID PRIMARY KEY,
       Message TEXT NOT NULL,
       Type VARCHAR(5) DEFAULT 'Other' NOT NULL
          CONSTRAINT CK_OtherDoc_Type CHECK(Type = 'Other'), 
       CONSTRAINT FK_OtherDoc_DocID FOREIGN KEY(DocID) REFERENCES Document(DocID)  );
    Last edited by scias23; 12-18-09 at 12:37.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah. that does not look quite right.

    have a look at http://technet.microsoft.com/en-us/l.../cc505839.aspx
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Dec 2009
    Posts
    7
    what does PK,FK1 means? is the field a primary and the same time a foreign key?

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your suggestion looks good to me. (I see you've edited it, it may be different now from what Thrasy saw).

    Document is the supertype with PK DocID. You provide an extra column Type as a redundant field.

    Clearance, BClearance, AppBClearance, OtherDoc all reference Document through DocID. DocID is both their PK and the FK to the supertype Document. But I would drop the Type coulumn. A record in the OtherDoc is .. an Other document. There is no need to repeat that again.
    now my question:
    * how would i code this in mssql?
    Like you already did.
    * how would i enter data if, for example, i will get a business clearance document?
    (*)You INSERT a Document with a certain value for DocId. Then you create a BClearance record with teh same value for DocId.
    * what if the document to be issued is neither clearance nor business clearance?
    Then you do the same as (*) but you INSERT INTO OtherDoc instead of into BClearance.
    what does PK,FK1 means? is the field a primary and the same time a foreign key?
    Yes.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Dec 2009
    Posts
    7
    hey Wim thanks! your post is a great help! another function that i will do is printing of all the details of a document. what if i want to display all the details of, for example, business clearance document for printing? is JOIN appropriate for this (we haven't tackled joins yet)? how would i accomplish it in mssql?

    thanks

  9. #9
    Join Date
    Dec 2009
    Posts
    7
    another thing, how does the discriminator column works? if i assign A for clearance, B for business clearance, and C for other document, how would the database know if an instance of the document is either clearance, business clearance, or other? is that still sql or is that a programming thing (i'll be building the application uising VB.net)?

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    what if i want to display all the details of, for example, business clearance document for printing? is JOIN appropriate for this (we haven't tackled joins yet)? how would i accomplish it in mssql?
    Code:
    SELECT Document.DocId, 
         Person.LName, 
         BClearance.Position,
         ... 
    FROM Document 
         INNER JOIN BClearance ON
              Document.DocId = BClearance.DocId
         INNER JOIN Person ON
              Document.Requestor = Person.PersonID 
    WHERE Document.DocId = 12345
    how does the discriminator column works? if i assign A for clearance, B for business clearance, and C for other document, how would the database know if an instance of the document is either clearance, business clearance, or other?
    The database only stores data for you. It doesn't know anything.
    It is up to you to INSERT and SELECT the data in a way that makes sense.

    When you INSERT a Clearance record, you will have to assign an 'A' to the Document.Type. It is redundant, in the sense that you could query the database and find out that to that particular Document record, only one record from the Clearance table is related.

    The caveat of redundancy is that you have to make sure that the result from querying the database to see to what table a record from Document is related, e.g. Clearance table, is conform the code found in Type, e.g. 'A'.

    You should write an SQL query to assert that this condition is true. You should run this query periodically, especially after working on the code that INSERTS or UPDATES data in those tables.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Dec 2009
    Posts
    7
    hey wim, thanks for your post. such a great help! but still im confused about the discriminator column and stuffs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •