HW3 Solutions Page

Return to homework page.

1. Retrieve all EMPLOYEEs who earn as much as some EMPLOYEE in the Research DEPARTMENT.

a.

RES_SAL(sal) <-- π_Salary(EMPLOYEE JOIN_DNO=DNUMBER σ_{DNAME='Research'}(DEPARTMENT))

RESULT <-- π_{all attributes of EMPLOYEE}(EMPLOYEE JOIN_{salary>= sal} RES_SAL)

b. {e | EMPLOYEE(e) AND (∃ e2)(∃ d)[EMPLOYEE(e2) AND DEPARTMENT(d) AND d.DNUMBER= e2.DNO AND e2.SALARY <= e.SALARY]}

c. {abcdefghijk | EMPLOYEE(abcdefghijk) AND (∃ k1)(∃ b2)[EMPLOYEE(a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1) AND DEPARTMENT(a2 b2 c2 d2) AND b2= k1 AND i1 <= i]}

2. For each BDATE value of some EMPLOYEE count the number of EMPLOYEEs with that BDATE.

BDATE F_{COUNT SSN}EMPLOYEE

3. Retrieve all the DEPENDANTs of EMPLOYEEs who supervise at least two other EMPLOYEEs

a.

EMP1(SSN1, SUPERSSN1) <-- π_SSN,SUPERSSN(EMPLOYEE)

EMP2(SSN2, SUPERSSN2) <-- π_SSN,SUPERSSN(EMPLOYEE)

SUPERS(SSN) <-- π_SSN(σ_{NOT(SSN1=SSN2)}(EMPLOYEE JOIN_{SSN=SUPERSSN1}EMP1 JOIN_{SSN=SUPERSSN2}EMP2))

RESULT <-- DEPENDENT JOIN_ESSN=SSNSUPERS(SSN)

b. {d | DEPENDANT(d) AND (∃ e)[EMPLOYEE(e) AND e.SSN = d.ESSN AND (∃ e1)(∃ e2)[EMPLOYEE(e1) AND EMPLOYEE(e2) AND NOT(e1 = e2) AND e.SSN=e1.SUPERSSN AND e.SSN=e2.SUPERSSN] ]}

c. {abcde | DEPENDANT(abcde) AND (∃ d1)[EMPLOYEE(a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1) AND d1 = a AND (∃ j2)(∃ j3)(∃ d2)(∃ d3)[EMPLOYEE(a2 b2 c2 d2 e2 f2 g2 h2 i2 j2 k2) AND EMPLOYEE(a3 b3 c3 d3 e3 f3 g3 h3 i3 j3 k3) AND NOT(d2 = d3) AND d1=j2 AND d1=j3] ]}

4. Retrieve all the EMPLOYEEs who work on all of the PROJECTs located in the Engineering DEPARTMENT.

a.

ENG_PROJ <--π_PNUMBER (PROJECT JOIN_{DNUM=DNUMBER AND DNAME='ENGINEERING'}DEPARTMENT)

EMP_SSN <-- &pi_{ESSN, PNO}(WORKS_ON) ÷ ENG_PROJ

RESULT <-- EMPLOYEE * EMP_SSN

b. { e | EMPLOYEE(e) AND (∃ d)[DEPARTMENT(d) AND d.DNAME='ENGINEERING' AND (∀ p) [NOT PROJECT(p) OR NOT(p.DNUM = d.DNUMBER) OR (∃ w)[WORKS_ON(w) AND w.PNO =p.PNUMBER AND e.SSN = w.ESSN] ] ] }

c. { abcdefghijk | EMPLOYEE(abcdefghijk) AND (∃ b1)[DEPARTMENT('ENGINEERING' b1 c2 d1) AND (∀ b2)(∀ d2) [NOT PROJECT(a2 b2 c2 d2) OR NOT(d2 = b1) OR (∃ a3)(∃ b3) [WORKS_ON(a3 b3 c3) AND b3=b2 AND d=a3] ] ] }

5. Retrieve the first name and last names of EMPLOYEEs who either have the highest or the lowest salaries in their DEPARTMENTs.

a.

EMP1(SSN1, SAL1, DNO1) <-- π_{SSN, Salary, DNO}(EMPLOYEE)

EMP2(SSN2, SAL2, DNO2) <-- π_{SSN, Salary, DNO}(EMPLOYEE)

HI(SSN) <-- π_SSN2(EMP2 - π_{SSN2, SAL2, DNO2}( EMP1 JOIN_{DNO1=DNO2 AND SAL1> SAL2} EMP2))

LO(SSN) <-- π_SSN2(EMP2 - π_{SSN2, SAL2, DNO2}( EMP1 JOIN_{DNO1=DNO2 AND SAL1< SAL2} EMP2))

RESULT <-- π_{FNAME, LNAME}(EMPLOYEE * (HI ∪ LO))

b. { e.FNAME, e.LNAME | EMPLOYEE(e) AND [(∀ e1)(NOT EMPLOYEE(e1) OR NOT(e1.DNO = e.DNO) OR e1.Salary =< e.Salary) OR (∀ e2)(NOT EMPLOYEE(e2) OR NOT(e2.DNO = e.DNO) OR e2.Salary >= e.Salary)] }

c. { a, c | EMPLOYEE(abcdefghijk) AND [(∀ a1, b1, c1, d1, e1, f1, g1, h1, i1, j1, k1)(NOT EMPLOYEE(a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1) OR NOT(k1 = k) OR i1 =< i) OR (∀ a2, b2, c2, d2, e2, f2, g2, h2, i2, j2, k2)(NOT EMPLOYEE(a2 b2 c2 d2 e2 f2 g2 h2 i2 j2 k2) OR NOT(k2 = k) OR i2 <= i)] }

Dungeon ER diagram

Dungeon Relational Mapping

Dungeon Access Database

[Dungeon-MDB]

Dungeon.sql

/*
   Dungeon.sql
   HW3 Fall 2005
   
   Chris Pollett
*/

/*

	DDL --- CREATE ALL THE TABLES
	
*/
DROP TABLE PARTICIPANT CASCADE CONSTRAINTS;
CREATE TABLE PARTICIPANT
(
   PlayerID NUMBER(5) NOT NULL,
   FName VARCHAR(20) NOT NULL,
   LName VARCHAR(20) NOT NULL,
   Address VARCHAR(50),
   Phone NUMBER(10),
   --
   PRIMARY KEY(PlayerID)	
);

DROP TABLE GAME CASCADE CONSTRAINTS;
CREATE TABLE GAME
(
   GID NUMBER(5) NOT NULL,
   SettingOfGame VARCHAR(50),
   StartTime DATE NOT NULL,
   LocationHeld VARCHAR(50) NOT NULL,
   PID NUMBER(5),
   --
   PRIMARY KEY(GID),
   FOREIGN KEY (PID) REFERENCES PARTICIPANT(PlayerID)
);

DROP TABLE AWARD CASCADE CONSTRAINTS;
CREATE TABLE AWARD
(
   AName VARCHAR(20) NOT NULL,
   Value INTEGER,
   Description VARCHAR(50),
   VID NUMBER(5),
   --
   PRIMARY KEY(AName),
   FOREIGN KEY (VID) REFERENCES VENDOR(VID)
      INITIALLY DEFERRED DEFERRABLE
);

DROP TABLE VENDOR CASCADE CONSTRAINTS;
CREATE TABLE VENDOR
(
   VID NUMBER(5) NOT NULL,
   VName VARCHAR(20) NOT NULL,
   Address VARCHAR(50),
   Phone NUMBER(10),
   BID NUMBER(5),
   -- 
   PRIMARY KEY(VID),
   FOREIGN KEY (BID) REFERENCES BOOTH(BID)
      INITIALLY DEFERRED DEFERRABLE	
);

DROP TABLE MERCHANDISE CASCADE CONSTRAINTS;
CREATE TABLE MERCHANDISE
(
   MID NUMBER(5) NOT NULL,
   MName VARCHAR(20) NOT NULL,
   Cost INTEGER,
   Description VARCHAR(50),
   VID NUMBER(5),
   --
   PRIMARY KEY(MID),
   FOREIGN KEY (VID) REFERENCES VENDOR(VID)	
);

DROP TABLE BOOTH CASCADE CONSTRAINTS;
CREATE TABLE BOOTH
(
   BID NUMBER(5) NOT NULL,
   Location VARCHAR(50) NOT NULL,
   Price INTEGER,
   BSize INTEGER NOT NULL,
   --
   PRIMARY KEY(BID)
);

DROP TABLE SPECIAL_GAME CASCADE CONSTRAINTS;
CREATE TABLE SPECIAL_GAME
(
   GID NUMBER(5) NOT NULL,
   GName VARCHAR(20) NOT NULL,
   AName VARCHAR(20),
   --
   PRIMARY KEY(GID, GName),
   FOREIGN KEY (AName) REFERENCES AWARD(AName)	
);

DROP TABLE CHARACTER CASCADE CONSTRAINTS;
CREATE TABLE CHARACTER
(
   PlayerID NUMBER(5) NOT NULL,
   CName VARCHAR(20) NOT NULL,
   Type VARCHAR(20),
   CLevel INTEGER,
   HitPoints INTEGER,
   --
   PRIMARY KEY(PlayerID, CName),
   FOREIGN KEY (PlayerID) REFERENCES PARTICIPANT(PlayerID)
);

DROP TABLE RECEIVES CASCADE CONSTRAINTS;
CREATE TABLE RECEIVES
(
   PlayerID NUMBER(5) NOT NULL,
   AName VARCHAR(20) NOT NULL,
   --
   PRIMARY KEY(PlayerID, AName),
   FOREIGN KEY (PlayerID) REFERENCES PARTICIPANT(PlayerID),
   FOREIGN KEY (AName) REFERENCES AWARD(AName)	   
);

DROP TABLE BUYS CASCADE CONSTRAINTS;
CREATE TABLE BUYS
(
   PlayerID NUMBER(5) NOT NULL,
   MID NUMBER(5) NOT NULL,
   --
   PRIMARY KEY(PlayerID, MID),
   FOREIGN KEY (PlayerID) REFERENCES PARTICIPANT(PlayerID),   
   FOREIGN KEY (MID) REFERENCES MERCHANDISE(MID)     
);

DROP TABLE PLAYED_WITH CASCADE CONSTRAINTS;
CREATE TABLE PLAYED_WITH
(
   GID NUMBER(5) NOT NULL,
   PID NUMBER(5) NOT NULL,
   CName VARCHAR(20) NOT NULL,
   Score INTEGER,
   --
   PRIMARY KEY(GID, PID),
   FOREIGN KEY (PID,CName) REFERENCES CHARACTER(PlayerID,CName),   
   FOREIGN KEY (GID) REFERENCES GAME(GID)           
);

DROP TABLE INVENTORY CASCADE CONSTRAINTS;
CREATE TABLE INVENTORY
(
   PlayerID NUMBER(5) NOT NULL,
   CName VARCHAR(20) NOT NULL,
   Item VARCHAR(20) NOT NULL,
   --
   PRIMARY KEY(PlayerID, CName, Item),	
   FOREIGN KEY (PlayerID, CName) REFERENCES CHARACTER(PlayerID, CName)         
);

/*

	DML COMMANDS -- INSERT ROWS

*/

--PARTICIPANT
INSERT INTO PARTICIPANT VALUES
   (00000, 'Bob', 'Smith', 'Somewhere1', '4081234567');

INSERT INTO PARTICIPANT VALUES
   (00001, 'Sally', 'Smith', 'Somewhere2', '4082234567');

INSERT INTO PARTICIPANT VALUES
   (00002, 'Sam', 'Taylor', 'Somewhere3', '4083234567');
      
INSERT INTO PARTICIPANT VALUES
   (00003, 'Aaron', 'Song', 'Somewhere4', '4084234567');
   
INSERT INTO PARTICIPANT VALUES
   (00004, 'Josh', 'Stone', 'Somewhere5', '4085234567');  

--GAME
INSERT INTO GAME VALUES
   (00000, 'A far off place1', to_date('2005/11/08:12:00PM', 'yyyy/mm/dd:hh:miam'), 'Room1', 00000);

INSERT INTO GAME VALUES
   (00001, 'A far off place2', to_date('2005/11/08:12:00PM', 'yyyy/mm/dd:hh:miam'), 'Room2', 00001);

INSERT INTO GAME VALUES
   (00002, 'A far off place3', to_date('2005/11/08:01:00PM', 'yyyy/mm/dd:hh:miam'), 'Room1', 00000);   

INSERT INTO GAME VALUES
   (00003, 'A far off place4', to_date('2005/11/08:02:00PM', 'yyyy/mm/dd:hh:miam'), 'Room1', 00000);   

INSERT INTO GAME VALUES
   (00004, 'A far off place4', to_date('2005/11/08:03:00PM', 'yyyy/mm/dd:hh:miam'), 'Room1', 00000);   


--AWARD
INSERT INTO AWARD VALUES
   ('Golden Katana1', 100, 'Awarded to the winner of Special Game 1', 00000);  
   
INSERT INTO AWARD VALUES
   ('Golden Katana2', 150, 'Awarded to the winner of Special Game 2', 00001); 

INSERT INTO AWARD VALUES
   ('Golden Katana3', 500, 'Awarded to the winner of Special Game 3', 00001); 

INSERT INTO AWARD VALUES
   ('Golden Katana4', 1000, 'Awarded to the winner of Special Game 4', 00002); 

INSERT INTO AWARD VALUES
   ('Golden Katana5', 5000, 'Awarded to the winner of Special Game 5', 00003); 

--VENDOR
INSERT INTO VENDOR VALUES
   (00000, 'DDStore1', 'Elsewhere1', 4087654321,00000);

INSERT INTO VENDOR VALUES
   (00001, 'DDStore2', 'Elsewhere2', 4087654322, 00001);

INSERT INTO VENDOR VALUES
   (00002, 'DDStore3',  'Elsewhere3', 4087654323, 00002);
      
INSERT INTO VENDOR VALUES
   (00003, 'DDStore4', 'Elsewhere4', 4087654324, 00003);
   
INSERT INTO VENDOR VALUES
   (00004, 'DDStore5', 'Elsewhere5', 4087654325, 00004); 

--MERCHANDISE
INSERT INTO MERCHANDISE VALUES
   (00000, 'CoolDice1', 10, 'omega+1 sided dice', 00000);

INSERT INTO MERCHANDISE VALUES
   (00001, 'CoolDice2', 10, 'omega+2 sided dice', 00001);

INSERT INTO MERCHANDISE VALUES
   (00002, 'CoolDice3',  5, 'omega+3 sided dice', 00002);
      
INSERT INTO MERCHANDISE VALUES
   (00003, 'CoolDice4', 6, 'omega+4 sided dice', 00003);
   
INSERT INTO MERCHANDISE VALUES
   (00004, 'CoolDice5', 4, 'omega+5 sided dice', 00004);   
 
--BOOTH
INSERT INTO BOOTH VALUES
   (00000, 'Auditorium A', 1000, 200);  
   
INSERT INTO BOOTH VALUES
   (00001, 'Auditorium B', 500, 200);

INSERT INTO BOOTH VALUES
   (00002, 'Auditorium A', 2000, 300); 

INSERT INTO BOOTH VALUES
   (00003, 'Auditorium A', 2000, 400);

INSERT INTO BOOTH VALUES
   (00004, 'Auditorium A', 500, 50);
   
--SPECIAL_GAME
INSERT INTO SPECIAL_GAME VALUES
   (00000, 'Special Game1', 'Golden Katana1');

INSERT INTO SPECIAL_GAME VALUES
   (00001, 'Special Game2', 'Golden Katana2');

INSERT INTO SPECIAL_GAME VALUES
   (00002, 'Special Game3', 'Golden Katana3');

INSERT INTO SPECIAL_GAME VALUES
   (00003, 'Special Game4', 'Golden Katana4');

INSERT INTO SPECIAL_GAME VALUES
   (00004, 'Special Game4', 'Golden Katana4');
     
--CHARACTER
INSERT INTO CHARACTER VALUES
   (00001, 'MrWizard1', 'Wizard', 1, 5);

INSERT INTO CHARACTER VALUES
   (00002, 'MrWizard2', 'Paladin', 1, 10);

INSERT INTO CHARACTER VALUES
   (00003, 'MrWizard3', 'Wizard', 1, 6);

INSERT INTO CHARACTER VALUES
   (00003, 'MrWizard4', 'Wizard', 2, 12);

INSERT INTO CHARACTER VALUES
   (00004, 'MrWizard5', 'Wizard', 2, 11);
      
--RECEIVES
INSERT INTO RECEIVES VALUES
   (00001, 'Golden Katana1');

INSERT INTO RECEIVES VALUES
   (00002, 'Golden Katana2');

INSERT INTO RECEIVES VALUES
   (00001, 'Golden Katana3');

INSERT INTO RECEIVES VALUES
   (00001, 'Golden Katana4');

INSERT INTO RECEIVES VALUES
   (00003, 'Golden Katana5');

--BUYS
INSERT INTO BUYS VALUES
   (00000, 00000);

INSERT INTO BUYS VALUES
   (00000, 00001);

INSERT INTO BUYS VALUES
   (00001, 00000);  

INSERT INTO BUYS VALUES
   (00002, 00003);  

INSERT INTO BUYS VALUES
   (00000, 00004);
        
--PLAYED_WITH
INSERT INTO PLAYED_WITH VALUES
   (00000, 00001, 'MrWizard1', 50);

INSERT INTO PLAYED_WITH VALUES
   (00001, 00002, 'MrWizard2', 50);

INSERT INTO PLAYED_WITH VALUES
   (00002, 00001, 'MrWizard1', 50);

INSERT INTO PLAYED_WITH VALUES
   (00003, 00001, 'MrWizard1', 50);

INSERT INTO PLAYED_WITH VALUES
   (00004, 00003, 'MrWizard4', 50);
         
--INVENTORY
INSERT INTO  INVENTORY VALUES
   (00001, 'MrWizard1', 'Bow');

INSERT INTO  INVENTORY VALUES
   (00001, 'MrWizard1', 'Rainbow Wand');
   
INSERT INTO  INVENTORY VALUES
   (00001, 'MrWizard1', 'short sword');

INSERT INTO  INVENTORY VALUES
   (00001, 'MrWizard1', 'fedora');

INSERT INTO  INVENTORY VALUES
   (00001, 'MrWizard1', 'whip');

COMMIT;                             

Return to homework page.