#1
  1. Infernal Technomancer
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jun 2005
    Location
    Centrally located far from everywhere
    Posts
    334
    Rep Power
    182

    Question building a database (Open Office Base), table creation, etc.


    Okay, I am learning (again) databases, this time with ooBase, the database app in Open Office.

    I am currently building a UML-type design framework for a maintenance tracking database for a unit that I work on.

    Each aircraft has two of these units. They receive signals from the airport and use those signals to tell the aircrew the direct bearing to the airport, whether they are flying toward or away from it, and gives flight-path directions on approach.

    The units, when they fail, are sent to my shop for repair. I check the unit for revisions (one SB is now required to be installed, which is why I am going to track them all with this database), and run a diagnostic program on the unit. The diagnostic needs to know what SBs are installed so that it will test the unit properly.

    When it fails a step, I have to repair it, either by making adjustments or troubleshooting down to one or more bad components and replacing them.

    After replacing certain components, I may have to make adjustments or even align the entire unit.

    Sometimes a unit is pulled for troubleshooting purposes, or there was something else wrong with the aircraft and replacing the unit did not fix the plane. In those cases, I will either have no repair action, or I will have a service bulletin to install but that had nothing to do with the original discrepancy, and I would like to track those actions separately.

    Certain circuit card part numbers are installed or modified with certain service bulletins, so I want to be able to use conditional statements on my data entry to keep from misentering configuration data (or catch a unit that is misconfigured). Also, different cards require different calibration procedures (for instance, an A2 card P/N 662-6652-007 requires special equipment that a -004 doesn't need) so I might have a need to track those units which require special handling or perhaps arrange to get one-off units installed in a plane we are selling.

    In the initial setup, I had a single table (the UML design has exploded into ten tables with more to come) which tracks each unit by Part Number and Serial Number (same unit, but three different part numbers with minor differences).

    I though I should use a set of Boolean entities in a standalone table that lists each of 25 Service Bulletins then use the primary key from that table (one unique key for each unique combination of SBs installed) in a linking table to track what bulletins are on each unit (on average, each unit will have ten of the nineteen SBs currently out).

    Ultimately, through this database, I will be tracking more than 500 units and be able to identify which ones have what SBs on them, and the part numbers of each of the six sub-assemblies, as well as a history of repairs.

    I have already started writing an SQL script to build the appropriate databases, if that helps you understand what I am trying to do.

    My primary question is: How do I set up a form or whatever that will allow me to enter the 25 service bulletins for a unit and be able to generate (or identify as already existing in the SB table) a unique key for the main tracking table?

    In other words, I know a lot of the units will have SBs 1-9, 11-19 installed, and it would be simpler (in the long run) if I can punch in the checkboxes and the database will say, 'oh, that's unique key 001' and so when I run a query, theoretically, it could expand that one column entry '001' into a list of the SBs installed (ie 1-9,11-19).

    Does that make any sense? Anyway, if someone could point me in the right direction, I would appreciate the help.
    Attached Files
    Almost Ancient Folding Wizard


    For difficult hardware problems, go to the Hard Side™-- DevHardware Forums
  2. #2
  3. Infernal Technomancer
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jun 2005
    Location
    Centrally located far from everywhere
    Posts
    334
    Rep Power
    182
    Hrm... could I make all 25 of the boolean columns part of the primary key? It would solve my problem of making sure there were no duplicates, but it isn't exactly elegant.
  4. #3
  5. Infernal Technomancer
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jun 2005
    Location
    Centrally located far from everywhere
    Posts
    334
    Rep Power
    182
    Okay, I'm trying to create a composite primary key via SQL , but I'm getting an error when I execute it:

    Code:
    DROP TABLE "Unit Configuration" IF EXISTS
    DROP TABLE "Revisions" IF EXISTS
    DROP TABLE "Revision Tracking" IF EXISTS
    DROP TABLE "Maintenance Data" IF EXISTS
    DROP TABLE "Faults Detected" IF EXISTS
    DROP TABLE "Findings" IF EXISTS
    DROP TABLE "Adjustment" IF EXISTS
    DROP TABLE "Replacement" IF EXISTS
    DROP TABLE "Repair Actions" IF EXISTS
    DROP TABLE "Additional Work" IF EXISTS
    
    CREATE TABLE "Unit Configuration" (
    "UPN" VARCHAR(15) NOT NULL,
    "USN" VARCHAR(8) NOT NULL,
    "A1PN" VARCHAR(15),
    "A1REV" VARCHAR(2),
    "A2PN" VARCHAR(15),
    "A2REV" VARCHAR(2),
    "A3PN" VARCHAR(15),
    "A3REV" VARCHAR(2),
    "A4PN" VARCHAR(15),
    "A4REV" VARCHAR(2),
    "A5PN" VARCHAR(15),
    "A5REV" VARCHAR(2),
    "A6PN" VARCHAR(15),
    "A6REV" VARCHAR(2),
    "NOTES" VARCHAR(256),
    CONSTRAINT UPN_USN PRIMARY KEY ("UPN", "USN" )
    );
    
    CREATE TABLE "Revisions" (
    "SBK" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
    "SB1" BOOLEAN,
    "SB2" BOOLEAN,
    "SB3" BOOLEAN,
    "SB4" BOOLEAN,
    "SB5" BOOLEAN,
    "SB6" BOOLEAN,
    "SB7" BOOLEAN,
    "SB8" BOOLEAN,
    "SB9" BOOLEAN,
    "SB10" BOOLEAN,
    "SB11" BOOLEAN,
    "SB12" BOOLEAN,
    "SB13" BOOLEAN,
    "SB14" BOOLEAN,
    "SB15" BOOLEAN,
    "SB16" BOOLEAN,
    "SB17" BOOLEAN,
    "SB18" BOOLEAN,
    "SB19" BOOLEAN
    );
    
    CREATE TABLE "Revision Tracking" (
    "UPN" VARCHAR(15) NOT NULL,
    "USN" VARCHAR(8) NOT NULL,
    "SBK" INTEGER,
    CONSTRAINT FK_REV_UPN FOREIGN KEY ("UPN") REFERENCES "Unit Configuration" ("UPN"),
    CONSTRAINT FK_REV_USN FOREIGN KEY ("USN") REFERENCES "Unit Configuration" ("USN"),
    CONSTRAINT FK_REV_SBK FOREIGN KEY ("SBK") REFERENCES "Revisions" ("SBK")
    );
    
    CREATE TABLE "Maintenance Data" (
    "WO" VARCHAR(8) NOT NULL PRIMARY KEY,
    "UPN" VARCHAR(15) NOT NULL,
    "USN" VARCHAR(8) NOT NULL,
    "DIN" DATE NOT NULL
    "ACSN" VARCHAR(8)
    "SQWK" VARCHAR(256) NOT NULL
    "FIX" VARCHAR(256)
    "COST" INT(6,2)
    "DOUT" DATE
    CONSTRAINT FK_MAINT_UPN FOREIGN KEY ("UPN") REFERENCES "Unit Configuration" ("UPN"),
    CONSTRAINT FK_MAINT_USN FOREIGN KEY ("USN") REFERENCES "Unit Configuration" ("USN")
    );
    
    CREATE TABLE "Faults Detected" (
    "FDK" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
    "P13V" BOOLEAN,
    "N13V" BOOLEAN,
    "P5V" BOOLEAN,
    "AIFLT" BOOLEAN,
    "VOR108" BOOLEAN,
    "VOR116" BOOLEAN,
    "VORRCV" BOOLEAN,
    "VOROUT" BOOLEAN,
    "LOCFLT" BOOLEAN,
    "GSSEN" BOOLEAN,
    "GSFLT" BOOLEAN,
    "MBSEN" BOOLEAN,
    "MBFLT" BOOLEAN,
    "DIFLT" BOOLEAN,
    "PROC" BOOLEAN,
    "CHAS" BOOLEAN
    );
    
    CREATE TABLE "Findings" (
    "WO" VARCHAR(8),
    "FDK" INTEGER,
    CONSTRAINT FK_FIND_WO FOREIGN KEY ("WO") REFERENCES "Maintenance Data" ("WO"),
    CONSTRAINT FK_FIND_FDK FOREIGN KEY ("FDK") REFERENCES "Faults Detected" ("FDK")
    );
    
    CREATE TABLE "Adjustment" (
    "ADJK" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
    "LOCCTR" BOOLEAN,
    "30HZREF" BOOLEAN,
    "VORZERO" BOOLEAN,
    "GSCTR" BOOLEAN,
    "MBRF" BOOLEAN,
    "MBTHD" BOOLEAN,
    "MBAUD" BOOLEAN,
    "GSRF" BOOLEAN,
    "GSDET" BOOLEAN,
    "VORRF" BOOLEAN,
    "VORDET" BOOLEAN,
    "VORAUD" BOOLEAN,
    "LOCDVN" BOOLEAN,
    "GSDVN" BOOLEAN
    );
    
    CREATE TABLE "Replacement" (
    "WO" VARCHAR(8),
    "PART" VARCHAR (12),
    CONSTRAINT FK_FIND_WO FOREIGN KEY ("WO") REFERENCES "Maintenance Data" ("WO")
    );
    Error:

    Code:
    Primary or unique constraint required on main table: "Unit Configuration" in statement [
    
    CREATE TABLE "Revision Tracking" (
    "UPN" VARCHAR(15) NOT NULL,
    "USN" VARCHAR(8) NOT NULL,
    "SBK" INTEGER,
    CONSTRAINT FK_REV_UPN FOREIGN KEY ("UPN") REFERENCES "Unit Configuration" ("UPN"),
    CONSTRAINT FK_REV_USN FOREIGN KEY ("USN") REFERENCES "Unit Configuration" ("USN"),
    CONSTRAINT FK_REV_SBK FOREIGN KEY ("SBK") REFERENCES "Revisions" ("SBK")
    )]
  6. #4
  7. Infernal Technomancer
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jun 2005
    Location
    Centrally located far from everywhere
    Posts
    334
    Rep Power
    182
    Okay, so the problem was (with the composite key) that I needed to format the constraint for the foreign key the same way as the original composite primary key:

    Code:
    CONSTRAINT FK_REV_UPN FOREIGN KEY ("UPN", "USN") REFERENCES "Unit Configuration" ("UPN", "USN" ),
    After fixing all the syntax errors, I got my tables built, but I don't know... something isn't right-- my linking tables aren't working right.

Similar Threads

  1. The Zuzim Game - answer questions and earn zuzim!
    By Shadow Wizard in forum ASP Free Lounge
    Replies: 397
    Last Post: February 21st, 2007, 04:24 PM
  2. Open database in global.asa or on each seperate page..
    By roel018 in forum ASP Development
    Replies: 4
    Last Post: March 25th, 2006, 01:58 PM
  3. Newbie question on access database.
    By XandarX in forum Visual Basic Programming
    Replies: 6
    Last Post: June 6th, 2005, 05:59 PM
  4. create a table in already exsisting database with script?
    By ALEX-GRIM in forum .NET Development
    Replies: 10
    Last Post: February 11th, 2005, 02:46 PM
  5. Creating a database table using SQL CREATE TABLE
    By Steve Schofield in forum SQL Development
    Replies: 1
    Last Post: June 25th, 2001, 04:39 PM

IMN logo majestic logo threadwatch logo seochat tools logo