Database 作業二 資訊四 朱民晃 B92902025




Дата канвертавання16.04.2016
Памер19.07 Kb.
Database 作業二

資訊四 朱民晃 B92902025


1.

Relation schema:


Employees(ssn: string, union_mem_no: integer)

Traffic_control(ssn: string, exam_date: string)

Technician(ssn: string, name: string , address: string, phone_num: string, salary: integer)

Model(model_no: string, capacity: integer, weight: integer)

Expert(ssn: string, model_no: string)

Test(FAA_no:string, name:string, score: integer)

Type_Plane(model_no: string, reg_no:string)

Test_info(ssn: string, model_no: string, reg_no:string, FAA_no: string, hours: integer, date: string, score: integer)


SQL:
CREATE TABLE Employees( ssn CHAR(20),

union_mem_no INTEGER,

PRIMARY KEY (ssn))

CREATE TABLE Traffic_control( ssn CHAR(20),

exam_date CHAR(20),

PRIMARY KEY (ssn),

FOREIGN KEY (ssn) REFERENCES Employees

ON DELETE CASCADE)

CREATE TABLE Technician( ssn CHAR(20),

name CHAR(20),

address CHAR(60),

phone_num CHAR(20),

salary INTEGER,

PRIMARY KEY (ssn),

FOREIGN KEY (ssn) REFERENCES Employees

ON DELETE CASCADE)

CREATE TABLE Model( model_no CHAR(20),

capacity INTEGER,

weight INTEGER,

PRIMARY KEY (mode_no))

CREATE TABLE Expert( ssn CHAR(20),

model_no CHAR(20),

PRIMARY KEY (ssn, model_no),

FOREIGN KEY (ssn) REFERENCES Employees

ON DELETE CASCADE,

FOREIGN KEY (model_no) REFERENCES Model)

CREATE TABLE Test( FAA_no CHAR(20),

name CHAR(20),

score INTEGER,

PRIMARY KEY (FAA_no))

CREATE TABLE Type_Plane( model_no CHAR(20),

reg_no CHAR(20) NOT NULL,

PRIMARY KEY (model_no , reg_no),

FOREIGN KEY (model_no) REFERENCES Model,

FOREIGN KEY (reg_no) REFERENCES Plane)

CREATE TABLE Test_info( ssn CHAR(20),

model_no CHAR(20),

reg_no CHAR(20),

FAA_no CHAR(20),

hours INTEGER,

date CHAR(20),

score INTEGER,

PRIMARY KEY (ssn, model_no , reg_no, FAA_no),

FOREIGN KEY (ssn) REFERENCES Employees

ON DELETE CASCADE,

FOREIGN KEY (model_no) REFERENCES Model,

FOREIGN KEY (reg_no) REFERENCES Plane,

FOREIGN KEY (FAA_no) REFERENCES Test)


在All participation(Expert和Technician的連線),沒有辦法用Relation schema來表示,因為在Relation schema之中,並沒有可以用來表示All participation的寫法。

3. (Exercise 5.4)


(1)

假設這兩個department分別叫作”Hardware”和”Software”

SELECT E.ename E.age

FROM Emp E, Works W1, Works W2, Dept D1, Dept D2

WHERE E.eid = W1.eid AND E.eid = W2.eid AND W1.did = D1.did AND W2.did = D2.did AND D1.dname = “Hardware” AND D2.dname = “Software”
(2)

SELECT W2.did, COUNT (W2.eid)

FROM Works W2

GROUP BY W2.did

HAVING 2000 < (SELECT SUM (W1.pct_time)

FROM Works W1

HERE W1.did = W2.did)

(3)


SELECT E.ename

FROM Emp E

WHERE E.salary > ALL (SELECT D.budget

FROM Dept D, Works W

WHERE E.eid = W.eid AND D.did = W.did)

(4)


SELECT DISTINCT D.managerid

FROM Dept D

WHERE 1000000 < ALL (SELECT D2.budget

FROM Dept D2

WHERE D2.managerid = D.managerid)
(5)

SELECT E.ename

FROM Emp E

WHERE E.eid IN (SELECT D.managerid

FROM Dept D

WHERE D.budget = (SELECT MAX (D2.budget)

FROM Dept D2))

(6)


SELECT D.managerid

FROM Dept D

WHERE 5000000 < (SELECT SUM (D2.budget)

FROM Dept D2

WHERE D2.managerid = D.managerid)

(7)


SELECT DISTINCT D.managerid

FROM (SELECT DISTINCT D.managerid, SUM(D.budget) AS tmp_Budget

FROM Dept D

GROUP BY D.managerid) AS DD

WHERE DD.tmp_budget = (SELECT MAX(DD.tmp_budget)

FROM Dept DD)

(8)

SELECT E.ename



FROM Emp E, Dept D

WHERE E.eid = D.managerid



GROUP BY E.Eid, E.ename

HAVING EVERY (D.budget > 1000000) AND ANY (D.budget < 5000000)


База данных защищена авторским правом ©shkola.of.by 2016
звярнуцца да адміністрацыі

    Галоўная старонка