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.

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 ,

CONSTRAINT DURATION CHECK (TO_DATE (START_DATE, '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