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