Re: How do I add constraints to my DB

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