《Database System Concepts（数据库系统概念）》第六版——第四次作业（第四章）

4.5

CREATE VIEW student_grade(ID,GPA) AS
(
    ( SELECT ID,SUM(points*credits) / SUM(credits)
     FROM ((takes NATURAL JOIN course ) JOIN grade_points USING grade
    GROUP BY ID)
     UNION 
     (SELECT ID,NULL
     FROM student
     WHERE ID NOT IN (SELECT ID FROM takes)
)

4.6

CREATE TABLE student
	(ID			VARCHAR(5), 
	 NAME			VARCHAR(20) NOT NULL, 
	 dept_name		VARCHAR(20), 
	 tot_cred		NUMERIC(3,0) CHECK (tot_cred >= 0),
	 PRIMARY KEY (ID),
	 FOREIGN KEY (dept_name) REFERENCES department
		ON DELETE SET NULL
	);
	
CREATE TABLE takes
	(ID			VARCHAR(5), 
	 course_id		VARCHAR(8),
	 sec_id			VARCHAR(8), 
	 semester		VARCHAR(6),
	 YEAR			NUMERIC(4,0),
	 grade		        VARCHAR(2),
	 PRIMARY KEY (ID, course_id, sec_id, semester, YEAR),
	 FOREIGN KEY (course_id,sec_id, semester, YEAR) REFERENCES section
		ON DELETE CASCADE,
	 FOREIGN KEY (ID) REFERENCES student
		ON DELETE CASCADE
	);
	
CREATE TABLE advisor
	(s_ID			VARCHAR(5),
	 i_ID			VARCHAR(5),
	 PRIMARY KEY (s_ID),
	 FOREIGN KEY (i_ID) REFERENCES instructor (ID)
		ON DELETE SET NULL,
	 FOREIGN KEY (s_ID) REFERENCES student (ID)
		ON DELETE CASCADE
	);
	
CREATE TABLE time_slot
	(time_slot_id		VARCHAR(4),
	 DAY			VARCHAR(1),
	 start_time		DATETIME,
	 end_time		DATETIME,
	 PRIMARY KEY (time_slot_id, DAY, start_time)
    );
    
CREATE TABLE prereq
	(course_id		VARCHAR(8), 
	 prereq_id		VARCHAR(8),
	 PRIMARY KEY (course_id, prereq_id),
	 FOREIGN KEY (course_id) REFERENCES course(course_id)
		ON DELETE CASCADE,
	 FOREIGN KEY (prereq_id) REFERENCES course(course_id)
	)

4.7

CREATE TABLE employee
	(employee_name	VARCHAR(20) NOT NULL,
	 street			VARCHAR(20), 
	 city		    VARCHAR(20), 
	 PRIMARY KEY (employee_name)
	);
	
CREATE TABLE works
	(employee_name	VARCHAR(20) NOT NULL,
	 company_name	VARCHAR(20), 
	 salary         NUMERIC(8,2) CHECK(salary>0), 
	 PRIMARY KEY (employee_name),
     FOREIGN KEY (employee_name) REFERENCES employee
     ON DELETE CASCADE,
     FOREIGN KEY (company_name) REFERENCES company
     ON DELETE CASCADE
	);
	
CREATE TABLE works
	(company_name	VARCHAR(20) NOT NULL,
	 city           VARCHAR(20), 
	 PRIMARY KEY (company_name),
	);
	
CREATE TABLE managers
	(employee_name	VARCHAR(20) NOT NULL,
	 manager_name	VARCHAR(20), 
	 PRIMARY KEY (employee_name),
     FOREIGN KEY (employee_name) REFERENCES employee(employee_name)
     ON DELETE CASCADE,
     FOREIGN KEY (manager_name) REFERENCES employee(employee_name)
     ON DELETE SET NULL
	);

4.8

• a

  SELECT name,course_id,sec_id,semester,year
  FROM instructor NATURAL JOIN teaches NATURAL JOIN section NATURAL JOIN classroom
  GROUP BY name,course_id,sec_id,semester,year
  HAVING COUNT(building,room_no)>1
  

• b

  create assertion no_instructor_two_room check
  	(not exists (SELECT name,course_id,sec_id,semester,year
  					FROM instructor NATURAL JOIN teaches NATURAL JOIN section NATURAL JOIN classroom
  					GROUP BY name,course_id,sec_id,semester,year
  					HAVING COUNT(building,room_no)>1)
      )
  

4.10

SELECT COALESCE(a.name,b.name)AS NAME,COALESCE(a.address,b.address)AS address,title,salary
FROM a FULL outer_join b WHERE a.name=b.name AND a.address=b.address

4.12

//外连接
SELECT employee_name
FROM employee NATURAL RIGHT OUTER JOIN managers
WHERE manager_name IS NULL

//不用外连接
SELECT employee_name
FROM employee 
WHERE employee_name NOT EXISTS(
    SELECT employee_name
    FROM employee NATURAL JOIN managers
)

4.16

• a

  CREATE TABLE address
  	(NAME	VARCHAR(20) NOT NULL,
  	 ...
  	 PRIMARY KEY (NAME),
       FOREIGN KEY (NAME) REFERENCES hourly_worker OR salaried_worker
       ON DELETE CASCADE,
  	);
  

• b

  每当插入一个address关系，都要在salaried_worker和hourly_worker中查找与address相同的name。若找到则允许插入，否则不允许插入