Re: How do I add constraints to my DB

From: Frank <fbortel_at_nescape.net>
Date: Sat, 29 Nov 2003 16:56:07 +0100
Message-ID: <bqaf2p$ksg$1@news2.tilbu1.nb.home.nl>

Greg wrote:
> Hi, I am using Oracle 9i on Win XP
> I am to create a simple object-relational DB based on an air traffic control
> system that stores information on runways, flights, planes, landing times
> and allocations. To get an improved grade, I can add constraints but am
> unsure what form these would take. I haven't posted my tables as I haven't
> decided on the design of the DB yet, so any tips on how I could design it
> would be great, too.
> Greg
>
>

Column constraints:
  Business Rule: Airplane_Code is required translates into
  Column CODE in table AIRPLANES constraint NOT NULL   Business Rule: Gender of a passenger is (not only required)

                either Male, or Female
translates into
  Column GENDER in table PASSENGERS has CHECK constraint (GENDER IN ('M','F'));

Table constraints:
  Business Rule: if a flight is recorded from outside the

      		US of A with lands on US territory, certain
		countries need visa

translates into
  a Table trigger on PLANNED_LANDINGS, columns CODE_COO and CODE_COD,   which checks the business rule
  (If PLANNED_LANDINGS.CODE_COO is not usa AND PLANNED_LANDINGS.CODE_COD    is usa AND PLANNED_LANDINGS.CODE_COO is on the visa list, then    PLANNED_LANDINGS.VISA may not be NULL else RAISE an error)

Referential constraints:

   Business Rule: Any recorded country must exist in the list of

                    IAA approved countries.
translates into

   table constraints (constraint Foreign Key ([columns]) references

      lookuptable([columns])

-- 
Regards, Frank van Bortel

Received on Sat Nov 29 2003 - 09:56:07 CST