DOC

4 Seminarsample_solution.doc

By Bobby Allen,2014-11-18 08:36
10 views 0
4 Seminarsample_solution.doc

    Solutions for Seminar 4

    Queries in relational algebra notation

1. (EMP) EMP_NO

    2;; (EMP) SALARY > 15000

    3. (PROJ) ( (EMP) DEPT_NO DEPT_NO 4. (EMP) (PROJ) DEPT_NO DEPT_NO 5. (PROJ) - (EMP) DEPT_NO DEPT_NO 6. EMP DEPT

    7. EMP ? DEPT (DEPT_NO = DEPT_NO and EMP_NO MANAGER_NO)

or

    T1 ? EMP ? DEPT DEPT_NO = DEPT_NOResult ? (T1)EMP_NO MANAGER_NO

or

    T1 ? EMP ? DEPT DEPT_NO = DEPT_NOT2 ? EMP ? DEPT EMP_NO MANAGER_NO Result ? T1 T2

8. EMP * ALLOC

9. (DEPT PROJ) DEPT_NO=DEPT_NODEPT_NO, PROJ_NO

10. (DEPT PROJ) DEPT_NO=DEPT_NODEPT_NO, PROJ_NO

11. (DEPT PROJ) DEPT_NO=DEPT_NODEPT_NO, PROJ_NO

Company.SQL File Contents

SET VERIFY ON

Rem Setup File for SQL double unit

    Rem Running this file will empty the data base that is left over from other units Rem Invoke this file from the SQL window by typing @COMPANY

SET ECHO ON

    Rem Everything must first be dropped from the data base. If an object doesn't Rem exist, there will be an error message, but this can be ignored.

DROP TABLE EMP CASCADE CONSTRAINTS;

    DROP TABLE DEPT CASCADE CONSTRAINTS;

    DROP TABLE PROJ CASCADE CONSTRAINTS;

    DROP TABLE ALLOC CASCADE CONSTRAINTS;

    Rem CASCADE CONSTRAINTS is necessary to avoid violating any formal integrity Rem constraints on the tables.

    Rem Now we can re-create the data base:

CREATE TABLE EMP

     (EMP_NO CHAR(2) CONSTRAINT PKEY_EMP PRIMARY KEY ,

     EMP_NAME CHAR(10),

     DEPT_NO CHAR(2),

     SALARY INTEGER

     CONSTRAINT SAL_RANGE CHECK (SALARY BETWEEN 6000 AND 30000) ,

     MARITAL_STATUS CHAR(1)

     CONSTRAINT MAR_STATS CHECK (MARITAL_STATUS IN ('S', 'M', 'W', 'D')) );

CREATE TABLE DEPT

     (DEPT_NO CHAR(2) CONSTRAINT PKEY_DEPT PRIMARY KEY ,

     DEPT_NAME CHAR(10) CONSTRAINT UNIQ_NAME UNIQUE ,

     MANAGER_NO CHAR(2) CONSTRAINT UNIQ_MANAGER UNIQUE,

     BUDGET INTEGER CONSTRAINT MAX_BUDG CHECK (BUDGET <= 400000) );

CREATE TABLE PROJ

     (PROJ_NO CHAR(2) CONSTRAINT PKEY_PROJ PRIMARY KEY ,

     DEPT_NO CHAR(2),

     START_DATE DATE CONSTRAINT UNIQ_START UNIQUE ,

     DEADLINE DATE,

     CONSTRAINT DURATION CHECK (TO_DATE (START_DATE, 'DD-MON-YYYY') <

     TO_DATE (DEADLINE, 'DD-MON-YYYY')) );

CREATE TABLE ALLOC

     (EMP_NO CHAR(2),

     PROJ_NO CHAR(2),

     CONSTRAINT PKEY_ALLOC PRIMARY KEY (EMP_NO, PROJ_NO) );

    INSERT INTO EMP VALUES ('E1', 'Smith', 'D1', 9900, 'W'); INSERT INTO EMP VALUES ('E2', 'Jones', 'D2', 13200, 'M'); INSERT INTO EMP VALUES ('E3', 'Roberts', 'D2', 11000, 'M'); INSERT INTO EMP VALUES ('E4', 'Evans', 'D3', 16500, 'S'); INSERT INTO EMP VALUES ('E5', 'Brown', 'D3', 27500, 'S'); INSERT INTO EMP VALUES ('E6', 'Green', 'D3', 13200, 'M'); INSERT INTO EMP VALUES ('E7', 'McDougal', 'D4', 17600, 'D'); INSERT INTO EMP VALUES ('E8', 'McNally', 'D5', 12100, 'M'); INSERT INTO EMP VALUES ('E9', 'Fletcher', 'D5', 13200, 'S');

    INSERT INTO DEPT VALUES ('D1', 'Production', NULL, 100000); INSERT INTO DEPT VALUES ('D2', 'Sales', 'E5', 250000); INSERT INTO DEPT VALUES ('D3', 'Accounts', 'E9', 95000); INSERT INTO DEPT VALUES ('D4', 'Admin', 'E8', 156000); INSERT INTO DEPT VALUES ('D5', 'Personnel', 'E7', 196000);

    INSERT INTO PROJ VALUES ('P1', 'D1', '20-APR-1994', '23-FEB-1998'); INSERT INTO PROJ VALUES ('P2', 'D1', '21-JAN-1995', '14-MAY-1997'); INSERT INTO PROJ VALUES ('P3', 'D2', '02-FEB-1996', '03-MAR-1999'); INSERT INTO PROJ VALUES ('P4', 'D3', '11-DEC-1995', '01-JAN-1999'); INSERT INTO PROJ VALUES ('P5', 'D4', '08-OCT-1995', '31-DEC-1999'); INSERT INTO PROJ VALUES ('P6', NULL, '09-OCT-1995', '30-DEC-1999');

INSERT INTO ALLOC VALUES ('E1', 'P1');

    INSERT INTO ALLOC VALUES ('E1', 'P2');

    INSERT INTO ALLOC VALUES ('E2', 'P1');

    INSERT INTO ALLOC VALUES ('E2', 'P5');

    INSERT INTO ALLOC VALUES ('E4', 'P4');

    INSERT INTO ALLOC VALUES ('E5', 'P4');

    INSERT INTO ALLOC VALUES ('E6', 'P4');

    INSERT INTO ALLOC VALUES ('E9', 'P4');

    INSERT INTO ALLOC VALUES ('E5', 'P3');

    INSERT INTO ALLOC VALUES ('E7', 'P3');

    ALTER TABLE EMP ADD CONSTRAINT FKEY_DEPT FOREIGN KEY (DEPT_NO) REFERENCES DEPT (DEPT_NO) DEFERRABLE;

    ALTER TABLE DEPT ADD CONSTRAINT FKEY_EMP FOREIGN KEY (MANAGER_NO) REFERENCES EMP (EMP_NO) DEFERRABLE;

    ALTER TABLE PROJ ADD CONSTRAINT FKEY_PROJ FOREIGN KEY (DEPT_NO) REFERENCES DEPT (DEPT_NO) DEFERRABLE;

    ALTER TABLE ALLOC ADD CONSTRAINT ALLOC_EMP FOREIGN KEY (EMP_NO) REFERENCES EMP (EMP_NO) DEFERRABLE;

    ALTER TABLE ALLOC ADD CONSTRAINT ALLOC_PROJ FOREIGN KEY (PROJ_NO) REFERENCES PROJ (PROJ_NO) DEFERRABLE;

COMMIT;

SET ECHO OFF

PROMPT

    PROMPT You are now ready to proceed with the SQL exercises. PROMPT

Queries in SQL

REM ****** SQL USED TO EXPRESS RELATIONAL ALGEBRA OPERATORS ******

    REM ***** EXAMPLES *****

REM 1. Algebra - PROJECT

    REM (Get a list of employee numbers from EMP).

SELECT DISTINCT EMP_NO

    FROM EMP;

REM 2. Algebra - RESTRICT

    REM (Get details of employees with salaries in excess of 15,000

    REM pounds).

SELECT *

    FROM EMP

    WHERE SALARY > 15000;

REM 3. Algebra - UNION

    REM (Get a list of department numbers that occur in PROJ or EMP).

SELECT DEPT_NO

    FROM PROJ

     UNION

    SELECT DEPT_NO

    FROM EMP;

REM 4. Algebra - INTERSECT

    REM (Get a list of department numbers that occur in both PROJ and

    REM EMP).

SELECT DEPT_NO

    FROM PROJ

     INTERSECT

    SELECT DEPT_NO

    FROM EMP;

REM 5. Algebra - DIFFERENCE

    REM (Get a list of department numbers that occur in PROJ but not in

    EMP).

SELECT DEPT_NO

    FROM PROJ

     MINUS

    SELECT DEPT_NO FROM EMP;

REM 6. Algebra - CARTESIAN PRODUCT

    REM (Get information on all possible combinations of employees and

    REM departments).

SELECT *

    FROM EMP,DEPT;

REM 7a. Algebra - THETA JOIN

    REM (Find information on those employees (and their departments) who REM are working for departments but are not the managers of those REM departments).

SELECT *

    FROM EMP,DEPT

    WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP.EMP_NO <> DEPT.MANAGER_NO;

REM 7b. Algebra - EQUI JOIN FOLLOWED BY SELECT

    REM (Find information on those employees (and their departments) who REM are working for departments but are not the managers of those REM departments).

SELECT *

    FROM

    (SELECT EMP_NO ENUM, EMP_NAME ENAME, DEPT_NAME DNAME, DEPT.DEPT_NO DNUM, MANAGER_NO MNUM

    FROM EMP,DEPT

    WHERE EMP.DEPT_NO = DEPT.DEPT_NO)

    WHERE ENUM <> MNUM;

    REM 7c. Algebra - EQUI JOIN, INTERSECT, THETA JOIN REM (Find information on those employees (and their departments)who REM are working for departments but are not the managers of those REM departments).

SELECT *

    FROM EMP,DEPT

    WHERE EMP.DEPT_NO = DEPT.DEPT_NO

     INTERSECT

    SELECT *

    FROM EMP,DEPT

    WHERE EMP.EMP_NO <> DEPT.MANAGER_NO;

    REM What effect has the NULL manager of department D1 had on the result ?

    REM Is it a reasonable effect ?

REM 8a. Algebra - EQUIJOIN

    REM (Find information about employees working on projects).

SELECT *

    FROM EMP,ALLOC

    WHERE EMP.EMP_NO = ALLOC.EMP_NO;

REM 8b. Algebra - NATURAL JOIN

    REM (Find information about employees working on projects).

    SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.SALARY, EMP.MARITAL_STATUS,

     EMP.DEPT_NO, ALLOC.PROJ_NO

    FROM EMP,ALLOC

    WHERE EMP.EMP_NO = ALLOC.EMP_NO;

REM 9. Algebra - RIGHT OUTER JOIN

REM (Find all departments working on projects, including departments

    without projects).

SELECT DISTINCT DEPT.DEPT_NO, PROJ.PROJ_NO

    FROM DEPT, PROJ

    WHERE DEPT.DEPT_NO = PROJ.DEPT_NO (+);

REM 10. Algebra - LEFT OUTER JOIN

    REM (Find all departments working on projects, including projects REM wich are not assigned to any department)

SELECT DISTINCT DEPT.DEPT_NO, PROJ.PROJ_NO

    FROM DEPT, PROJ

    WHERE DEPT.DEPT_NO(+) = PROJ.DEPT_NO;

REM 11. Algebra - FULL OUTER JOIN

    REM (Find all departments working on projects, including departments

    without projects

    REM and projects which are not assigned to any department).

SELECT DISTINCT DEPT.DEPT_NO, PROJ.PROJ_NO

    FROM DEPT, PROJ

    WHERE DEPT.DEPT_NO = PROJ.DEPT_NO (+)

     UNION

    SELECT DISTINCT DEPT.DEPT_NO, PROJ.PROJ_NO

    FROM DEPT, PROJ

    WHERE DEPT.DEPT_NO(+) = PROJ.DEPT_NO;

Report this document

For any questions or suggestions please email
cust-service@docsford.com