home *** CD-ROM | disk | FTP | other *** search
/ Java 1.2 How-To / JavaHowTo.iso / 3rdParty / jbuilder / TRIAL / INTRBASE / DATA.Z / TABLES.SQL < prev    next >
Encoding:
Text File  |  1998-03-15  |  8.9 KB  |  305 lines
  1. /*
  2.     THIS FILE IS FOR USE WITH THE TUTORIAL IN "GETTING STARTED",
  3.     IT IS NOT MEANT TO BE USED WITHOUT REFERRING TO THE MANUAL.
  4.  
  5. *   You must change the parameters below to match your
  6. *   server name, database name , username, and password.
  7. *   This file defines tables for the EMPLOYEE database.
  8. */
  9.  
  10. CONNECT "server:\dir\mydb.gdb"
  11. USER "USERNAME" PASSWORD "password";
  12.  
  13. /*
  14. * You are supposed to create the COUNTRY table by hand.
  15. * 
  16. * CREATE TABLE country
  17. * (
  18. *     country COUNTRYNAME NOT NULL PRIMARY KEY,
  19. *     currency VARCHAR(10) NOT NULL)
  20. * 
  21. */
  22.  
  23. CREATE TABLE department
  24. (
  25.     dept_no         DEPTNO NOT NULL,
  26.     department      VARCHAR(25) NOT NULL UNIQUE,
  27.     head_dept       DEPTNO,
  28.     mngr_no         EMPNO,
  29.     budget          BUDGET,
  30.     location        VARCHAR(15),
  31.     phone_no        PHONENUMBER DEFAULT '555-1234',
  32.  
  33.     PRIMARY KEY (dept_no),
  34.     FOREIGN KEY (head_dept) REFERENCES department (dept_no)
  35. );
  36. COMMIT;
  37.  
  38. CREATE DESCENDING INDEX budgetx ON department (budget);
  39.  
  40. /*  Create JOB Table.
  41.  *  Job id, job title, minimum and maximum salary, job description,
  42.  *  and required languages.
  43.  *
  44.  *  A job is defined by a multiple key, consisting of a job_code
  45.  *  (a 5-letter job abbreviation), a job grade, and a country name
  46.  *  indicating the salary currency type.
  47.  *
  48.  *  The salary range is expressed in the appropriate country's currency.
  49.  *
  50.  *  The job requirement is a text blob.
  51.  *
  52.  *  The job may also require some knowledge of foreign languages,
  53.  *  stored in a character array.
  54.  */
  55.  
  56. CREATE TABLE JOB (JOB_CODE JOBCODE NOT NULL,
  57.     JOB_GRADE JOBGRADE NOT NULL,
  58.     JOB_COUNTRY COUNTRYNAME NOT NULL,
  59.     JOB_TITLE VARCHAR(25) NOT NULL,
  60.     MIN_SALARY SALARY NOT NULL,
  61.     MAX_SALARY SALARY NOT NULL,
  62.     JOB_REQUIREMENT BLOB SUB_TYPE TEXT SEGMENT SIZE 400,
  63.     LANGUAGE_REQ VARCHAR(15)[1:5] ,
  64.     PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),
  65.     FOREIGN KEY (job_country) REFERENCES country (country),
  66.     CHECK (min_salary < max_salary) );
  67. COMMIT;
  68.  
  69. /*  Create EMPLOYEE Table
  70.  *  Employee id, name, phone extension, date of hire, department id,
  71.  *  job and salary information.
  72.  *
  73.  *  Salary can be entered in any country's currency.
  74.  *  Therefore, some of the salaries can appear magnitudes larger than others,
  75.  *  depending on the currency type.  Ex. Italian lira vs. U.K. pound.
  76.  *  The currency type is determined by the country code.
  77.  *
  78.  *  job_code, job_grade, and job_country reference employee's job information,
  79.  *  illustrating two tables related by referential constraints on multiple
  80.  *  columns.
  81.  *
  82.  *  The employee salary is verified to be in the correct salary range
  83.  *  for the given job title.
  84.  */
  85.  
  86. CREATE TABLE employee
  87. (
  88.     emp_no          EMPNO NOT NULL PRIMARY KEY,
  89.     first_name      FIRSTNAME NOT NULL,
  90.     last_name       LASTNAME NOT NULL,
  91.     phone_ext       VARCHAR(4),
  92.     hire_date       DATE DEFAULT 'NOW' NOT NULL,
  93.     dept_no         DEPTNO NOT NULL,
  94.     job_code        JOBCODE NOT NULL,
  95.     job_grade       JOBGRADE NOT NULL,
  96.     job_country     COUNTRYNAME NOT NULL,
  97.     salary          SALARY NOT NULL,
  98.     full_name       COMPUTED BY (last_name || ', ' || first_name),
  99.  
  100.     FOREIGN KEY (dept_no) 
  101.         REFERENCES department (dept_no),
  102.     FOREIGN KEY (job_code, job_grade, job_country) 
  103.         REFERENCES job (job_code, job_grade, job_country),
  104.  
  105.     CHECK ( salary >= (SELECT min_salary FROM job WHERE
  106.                         job.job_code = employee.job_code AND
  107.                         job.job_grade = employee.job_grade AND
  108.                         job.job_country = employee.job_country) AND
  109.             salary <= (SELECT max_salary FROM job WHERE
  110.                         job.job_code = employee.job_code AND
  111.                         job.job_grade = employee.job_grade AND
  112.                         job.job_country = employee.job_country))
  113. );
  114.  
  115. CREATE INDEX namex ON employee (last_name, first_name);
  116. COMMIT;
  117.  
  118. /*
  119.  *  Project id, project name, description, project team leader,
  120.  *  and product type.
  121.  *
  122.  *  Project description is a text blob.
  123.  */
  124.  
  125. CREATE TABLE project
  126. (
  127.     proj_id         PROJNO NOT NULL,
  128.     proj_name       VARCHAR(20) NOT NULL UNIQUE,
  129.     proj_desc       BLOB(800,1),
  130.     team_leader     EMPNO,
  131.     product         PRODTYPE,
  132.  
  133.     PRIMARY KEY (proj_id),
  134.     FOREIGN KEY (team_leader) REFERENCES employee (emp_no)
  135. );
  136. COMMIT;
  137.  
  138. /*
  139.  *  Employee id, project id, employee's project duties.
  140.  *
  141.  *  Employee duties is a text blob.
  142.  */
  143.  
  144. CREATE TABLE employee_project
  145. (
  146.     emp_no          EMPNO NOT NULL,
  147.     proj_id         PROJNO NOT NULL,
  148.  
  149.     PRIMARY KEY (emp_no, proj_id),
  150.     FOREIGN KEY (emp_no) REFERENCES employee (emp_no),
  151.     FOREIGN KEY (proj_id) REFERENCES project (proj_id)
  152. );
  153. COMMIT;
  154.  
  155. /*
  156.  *  Fiscal year, project id, department id, projected head count by
  157.  *  fiscal quarter, projected budget.
  158.  *
  159.  *  Tracks head count and budget planning by project by department.
  160.  *
  161.  *  Quarterly head count is an array of integers.
  162.  */
  163.  
  164. CREATE TABLE proj_dept_budget
  165. (
  166.     year                INTEGER NOT NULL CHECK (YEAR >= 1993),
  167.     proj_id             PROJNO NOT NULL,
  168.     dept_no             DEPTNO NOT NULL,
  169.     quart_head_cnt      INTEGER [4],
  170.     projected_budget    BUDGET,
  171.  
  172.     PRIMARY KEY (year, proj_id, dept_no),
  173.     FOREIGN KEY (dept_no) REFERENCES department (dept_no),
  174.     FOREIGN KEY (proj_id) REFERENCES project (proj_id)
  175. );
  176. COMMIT;
  177.  
  178. /*
  179.  *  Employee number, salary change date, updater's user id, old salary,
  180.  *  and percent change between old and new salary.
  181.  */
  182.  
  183. CREATE TABLE salary_history
  184. (
  185.     emp_no              EMPNO NOT NULL,
  186.     change_date         DATE DEFAULT 'NOW' NOT NULL,
  187.     updater_id          VARCHAR(20) NOT NULL,
  188.     old_salary          SALARY NOT NULL,
  189.     percent_change      DOUBLE PRECISION
  190.                             DEFAULT 0
  191.                             NOT NULL
  192.                             CHECK (percent_change between -50 and 50),
  193.     new_salary          COMPUTED BY
  194.                             (old_salary + old_salary * percent_change / 100),
  195.  
  196.     PRIMARY KEY (emp_no, change_date, updater_id),
  197.     FOREIGN KEY (emp_no) REFERENCES employee (emp_no)
  198. );
  199.  
  200. CREATE INDEX updaterx ON salary_history (updater_id);
  201. CREATE DESCENDING INDEX changex ON salary_history (change_date);
  202. COMMIT;
  203.  
  204. /*
  205.  *  Customer id, customer name, contact first and last names,
  206.  *  phone number, address lines, city, state or province, country,
  207.  *  postal code or zip code, and customer status.
  208.  */
  209.  
  210. CREATE TABLE customer
  211. (
  212.     cust_no             CUSTNO NOT NULL,
  213.     customer            VARCHAR(25) NOT NULL,
  214.     contact_first       FIRSTNAME,
  215.     contact_last        LASTNAME,
  216.     phone_no            PHONENUMBER,
  217.     address_line1       ADDRESSLINE,
  218.     address_line2       ADDRESSLINE,
  219.     city                VARCHAR(25),
  220.     state_province      VARCHAR(15),
  221.     country             COUNTRYNAME,
  222.     postal_code         VARCHAR(12),
  223.     on_hold             CHAR DEFAULT NULL
  224.         CHECK (on_hold IS NULL OR on_hold = '*'),
  225.     PRIMARY KEY (cust_no),
  226.         FOREIGN KEY (country) 
  227.     REFERENCES country (country)
  228. );
  229.  
  230. CREATE INDEX custnamex ON customer (customer);
  231. CREATE INDEX custregion ON customer (country, city);
  232. COMMIT;
  233.  
  234. /*
  235.  *  Purchase order number, customer id, sales representative, order status,
  236.  *  order date, date shipped, date need to ship by, payment received flag,
  237.  *  quantity ordered, total order value, type of product ordered,
  238.  *  any percent discount offered.
  239.  *
  240.  *  Tracks customer orders.
  241.  *
  242.  *  sales_rep is the ID of the employee handling the sale.
  243.  *
  244.  *  Number of days passed since the order date is a computed field.
  245.  *
  246.  *  Several checks are performed on this table, among them:
  247.  *      - A sale order must have a status: open, shipped, waiting.
  248.  *      - The ship date must be entered, if order status is 'shipped'.
  249.  *      - New orders can't be shipped to customers with 'on_hold' status.
  250.  *      - Sales rep
  251.  */
  252.  
  253. CREATE TABLE sales
  254. (
  255.     po_number       PONUMBER NOT NULL,
  256.     cust_no         CUSTNO NOT NULL,
  257.     sales_rep       EMPNO,
  258.     order_status    VARCHAR(7)
  259.                         DEFAULT 'new'
  260.                         NOT NULL
  261.                         CHECK (order_status in
  262.                             ('new', 'open', 'shipped', 'waiting')),
  263.     order_date      DATE
  264.                         DEFAULT 'now'
  265.                         NOT NULL,
  266.     ship_date       DATE
  267.                         CHECK (ship_date >= order_date OR ship_date IS NULL),
  268.     date_needed     DATE
  269.                         CHECK (date_needed > order_date OR date_needed IS NULL),
  270.     paid            CHAR
  271.                         DEFAULT 'n'
  272.                         CHECK (paid in ('y', 'n')),
  273.     qty_ordered     INTEGER
  274.                         DEFAULT 1
  275.                         NOT NULL
  276.                         CHECK (qty_ordered >= 1),
  277.     total_value     DECIMAL(9,2)
  278.                         NOT NULL
  279.                         CHECK (total_value >= 0),
  280.     discount        FLOAT
  281.                         DEFAULT 0
  282.                         NOT NULL
  283.                         CHECK (discount >= 0 AND discount <= 1),
  284.     item_type       PRODTYPE,
  285.     aged            COMPUTED BY
  286.                         (ship_date - order_date),
  287.  
  288.     PRIMARY KEY (po_number),
  289.     FOREIGN KEY (cust_no) REFERENCES customer (cust_no),
  290.     FOREIGN KEY (sales_rep) REFERENCES employee (emp_no),
  291.  
  292.     CHECK (NOT (order_status = 'shipped' AND ship_date IS NULL)),
  293.  
  294.     CHECK (NOT (order_status = 'shipped' AND
  295.             EXISTS (SELECT on_hold FROM customer
  296.                     WHERE customer.cust_no = sales.cust_no
  297.                     AND customer.on_hold = '*')))
  298. );
  299.  
  300. CREATE INDEX needx ON sales (date_needed);
  301. CREATE INDEX salestatx ON sales (order_status, paid);
  302. CREATE DESCENDING INDEX qtyx ON sales (item_type, qty_ordered);
  303. COMMIT;
  304. EXIT;
  305.