数据库系统概念(英文精编版.第六版)ch5 Advanced SQL

数据库系统概念英文精编版第六版教学设计介绍在本教学设计中，我们将介绍《Database System Concepts》（数据库系统概念）英文精编版第六版，旨在帮助学生掌握数据库系统的关键概念、基本结构以及数据库的设计和实现。

此教学设计将涵盖以下主题：•数据库基础知识•数据模型和ER图•SQL语言•数据库设计•事务管理和并发控制•数据库安全本教学设计旨在为初学者提供一个实践性的课程，旨在帮助学生掌握现代数据库系统的基础知识，并增强他们在实际生活和职业中利用数据库系统进行数据管理的能力。

教学内容数据库基础知识本节将首先介绍数据库系统的一些基础概念，如数据、数据库、数据库管理系统和关系数据库等。

学习者将学会如何使用SQL语句来完成基本的数据检索和修改。

数据模型和ER图本章将介绍抽象概念对于数据库设计的重要性。

学生们将学会如何使用实体关系（ER）图来表达数据库中各个实体之间的关系，从而方便设计和管理数据库。

SQL语言SQL是处理和查询关系数据的标准语言。

在本节中，我们将介绍SQL语言的不同方面，如数据定义、数据操作和嵌套查询等。

数据库设计数据库设计是一项关键性的工作，我们需要通过设计来确保数据库可以支持我们所需要的数据存储和管理。

本节将介绍关于设计和开发数据库的一些基本技术，如关系模式、规范化和冗余等。

事务管理和并发控制并发控制是数据库系统设计中最具挑战性的问题之一。

在本节中，我们将介绍什么是事务，事务管理和并发控制如何确保数据库在多个用户同时访问时仍然保持正确性。

数据库安全数据安全是建立在数据库的完整性和可用性之上的。

在本节中，我们将介绍一些关于数据库安全的基本概念，如授权和认证、数据加密等。

教学方法本教学设计采用电子教学的方式，其中，我们将提供录制的视频课程、示例问题、以及针对每个主题的在线测验。

本课程将使用不同的教学方法：•通过开放式问题和讨论区域，用以解释不同的概念。

•提供示例代码来增强实践性学习。

数据库系统概念第六版课程设计一、选题背景随着信息技术的发展，数据的数量和类型变得越来越复杂和庞大，需要有效地管理和处理。

数据库系统作为数据管理的关键技术之一，应用广泛。

通过学习数据库系统的概念、原理和实现方法，可以帮助学生深入理解数据管理、数据结构和数据操作等基本概念，并掌握常用数据库系统的设计和开发方法。

本课程设计旨在通过对数据库系统概念第六版的学习和实践，帮助学生全面了解数据库系统的基础知识，提高数据管理和处理能力。

二、选题内容本课程设计主要包括以下几个部分：1.数据库设计与实现：根据给出的实际场景，设计一个关系型数据库，并使用MySQL实现。

包括数据模型设计、表结构设计、数据类型定义、SQL语句编写等内容。

2.数据库应用开发：基于Java语言和JDBC技术，开发一个简单的图书管理系统，实现图书的查询、添加、修改和删除等功能。

包括前端UI设计、后端业务逻辑实现和数据库操作等内容。

3.数据库性能调优：分析数据库在不同负载条件下的性能表现，设计和实施调优策略。

包括SQL语句优化、索引优化、缓存策略、服务器参数优化等内容。

4.数据库备份与恢复：制定数据库备份和恢复策略，实现对数据库的定时备份和快速恢复。

包括备份方案设计、恢复操作测试、恢复时间评估等内容。

三、选题目的通过此次课程设计，旨在达到以下目标：1.学生能够全面了解数据库系统的原理、架构和应用场景，掌握常用的关系型数据库系统的设计和开发方法。

2.学生能够运用Java语言和JDBC技术，开发一个简单的图书管理系统，掌握前后端交互和数据库操作等基本技能。

3.学生能够分析数据库在不同负载条件下的性能表现，能够设计和实施调优策略，提高数据库系统的运行效率。

4.学生能够制定数据库备份和恢复策略，实现对数据库的高效备份和快速恢复，提高数据安全性和可靠性。

四、选题材料本课程设计所需的主要材料包括：1.《数据库系统概念第六版》一书作为课程教材。

2.Java语言和JDBC技术相关的书籍和资料，如《Java核心技术》、《Java编程思想》等。

CREATE DATABASE IF NOT EXISTS `university` /*!40100 DEFAULT CHARACTER SET gbk */;USE `university`;-- MySQL dump 10.13 Distrib 5.6.13, for Win32 (x86)---- Host: localhost Database: university-- -------------------------------------------------------- Server version 5.7.4-m14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `advisor`--DROP TABLE IF EXISTS `advisor`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `advisor` (`s_ID` varchar(5) NOT NULL,`i_ID` varchar(5) DEFAULT NULL,PRIMARY KEY (`s_ID`),KEY `i_ID` (`i_ID`),CONSTRAINT `advisor_ibfk_1` FOREIGN KEY (`i_ID`) REFERENCES `instructor` (`ID`) ON DELETE SET NULL,CONSTRAINT `advisor_ibfk_2` FOREIGN KEY (`s_ID`) REFERENCES `student` (`ID`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `advisor`--LOCK TABLES `advisor` WRITE;/*!40000 ALTER TABLE `advisor` DISABLE KEYS */;INSERT INTO `advisor` VALUES ('12345','10101'),('44553','22222'),('45678','22222'),('76543','45565'),('23121','76543'),('98988','7 6766'),('76653','98345'),('98765','98345');/*!40000 ALTER TABLE `advisor` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `classroom`--DROP TABLE IF EXISTS `classroom`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `classroom` (`building` varchar(15) NOT NULL,`room_number` varchar(7) NOT NULL,`capacity` decimal(4,0) DEFAULT NULL,PRIMARY KEY (`building`,`room_number`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `classroom`--LOCK TABLES `classroom` WRITE;/*!40000 ALTER TABLE `classroom` DISABLE KEYS */;INSERT INTO `classroom` VALUES ('Packard','101',500),('Painter','514',10),('Taylor','3128',70),('Watson','100',30),('Watson','120',50) ;/*!40000 ALTER TABLE `classroom` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `course`--DROP TABLE IF EXISTS `course`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `course` (`course_id` varchar(7) NOT NULL,`title` varchar(50) DEFAULT NULL,`dept_name` varchar(20) DEFAULT NULL,`credits` decimal(2,0) DEFAULT NULL,PRIMARY KEY (`course_id`),KEY `dept_name` (`dept_name`),CONSTRAINT `course_ibfk_2` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `course`--LOCK TABLES `course` WRITE;/*!40000 ALTER TABLE `course` DISABLE KEYS */;INSERT INTO `course` VALUES ('BIO-101','Intro.to Biology','Biology',4),('BIO-301','Genetics','Biology',4),('BIO-399','ComputationalBiology','Biology',3),('CS-101','Intro.to Computer Science','Comp.Sci',4),('CS-190','Game Design','Comp.Sci',4),('CS-315','Robotics','Comp.Sci',3),('CS-319','ImageProcessing','Comp.Sci',3),('CS-347','Database System Concepts','Comp.Sci',3),('EE-181','Intro.to Digital Systems','Elec.Eng',3),('FIN-201','Investment Banking','Finance',3),('HIS-351','World Hisyory','History',3),('MU-199','Music Video Production','Music',3),('PHY-101','Physical Principles','Physics',4);/*!40000 ALTER TABLE `course` ENABLE KEYS */;UNLOCK TABLES;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = utf8 */ ;/*!50003 SET character_set_results = utf8 */ ;/*!50003 SET collation_connection = utf8_general_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger delete_course before delete on coursefor each rowbeginif(old.course_id in (select course_id from takes))theninsert into course(course_id,title,dept_name,credits)values(null,null,null,null);end if;end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = utf8 */ ;/*!50003 SET character_set_results = utf8 */ ;/*!50003 SET collation_connection = utf8_general_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger delete_courses before delete on coursefor each rowbeginif(old.course_id in (select course_id from takes))theninsert into instructor(ID,name,dept_name,salary)values('10101','null','null','null');end if;end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;---- Table structure for table `department`--DROP TABLE IF EXISTS `department`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `department` (`dept_name` varchar(20) NOT NULL,`building` varchar(15) DEFAULT NULL,`budget` decimal(12,2) DEFAULT NULL,PRIMARY KEY (`dept_name`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `department`--LOCK TABLES `department` WRITE;/*!40000 ALTER TABLE `department` DISABLE KEYS */;INSERT INTO `department` VALUES ('Biology','Watson',90000.00),('Comp.Sci','Taylor',100000.00),('Elec.Eng','Taylor',85000.00),('Finan ce','Painter',120000.00),('History','Painter',50000.00),('Music','Packard',80000.00),('Physics','Wats on',70000.00);/*!40000 ALTER TABLE `department` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `instructor`--DROP TABLE IF EXISTS `instructor`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `instructor` (`ID` varchar(5) NOT NULL,`name` varchar(20) NOT NULL,`dept_name` varchar(20) DEFAULT NULL,`salary` decimal(8,2) DEFAULT NULL,PRIMARY KEY (`ID`),KEY `dept_name` (`dept_name`),CONSTRAINT `instructor_ibfk_2` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `instructor`--LOCK TABLES `instructor` WRITE;/*!40000 ALTER TABLE `instructor` DISABLE KEYS */;INSERT INTO `instructor` VALUES ('10101','Srinivasan','Comp.Sci',65000.00),('121','aa','Comp.Sci',12300.00),('12111','aa','Music',12 300.00),('12121','Wu','Finance',90000.00),('15151','Mozart','Music',40000.00),('22222','Einstein','Physics',95000.00),('32343','ElSaid','History',60000.00),('33456','Gold','Physics',87000.00),('45565','Katz','Comp.Sci',75000.00),(' 58583','Califieri','History',62000.00),('76543','Singh','Finance',80000.00),('76766','Crick','Biology', 72000.00),('83821','Brandt','Comp.Sci',92000.00),('98345','Kim','Elec.Eng',80000.00);/*!40000 ALTER TABLE `instructor` ENABLE KEYS */;UNLOCK TABLES;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = utf8 */ ;/*!50003 SET character_set_results = utf8 */ ;/*!50003 SET collation_connection = utf8_general_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger insert_instructorinformationbefore insert on instructorfor each rowbeginif(new.dept_name not in(select dept_name from department))then set new.ID=null;end if;end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;---- Table structure for table `prereq`--DROP TABLE IF EXISTS `prereq`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `prereq` (`course_id` varchar(8) NOT NULL,`prereq_id` varchar(8) NOT NULL,PRIMARY KEY (`course_id`,`prereq_id`),KEY `prereq_id` (`prereq_id`),CONSTRAINT `prereq_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ONDELETE CASCADE,CONSTRAINT `prereq_ibfk_2` FOREIGN KEY (`prereq_id`) REFERENCES `course` (`course_id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `prereq`--LOCK TABLES `prereq` WRITE;/*!40000 ALTER TABLE `prereq` DISABLE KEYS */;INSERT INTO `prereq` VALUES ('BIO-301','BIO-101'),('BIO-399','BIO-101'),('CS-190','CS-101'),('CS-315','CS-101'),('CS-319','CS-101' ),('CS-347','CS-101'),('EE-181','PHY-101');/*!40000 ALTER TABLE `prereq` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `section`--DROP TABLE IF EXISTS `section`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `section` (`course_id` varchar(8) NOT NULL,`sec_id` varchar(8) NOT NULL,`semester` varchar(6) NOT NULL,`year` decimal(4,0) NOT NULL,`building` varchar(15) DEFAULT NULL,`room_number` varchar(7) DEFAULT NULL,`time_slot_id` varchar(4) DEFAULT NULL,PRIMARY KEY (`course_id`,`sec_id`,`semester`,`year`),KEY `building` (`building`,`room_number`),CONSTRAINT `section_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE,CONSTRAINT `section_ibfk_3` FOREIGN KEY (`building`, `room_number`) REFERENCES `classroom` (`building`, `room_number`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `section`--LOCK TABLES `section` WRITE;/*!40000 ALTER TABLE `section` DISABLE KEYS */;INSERT INTO `section` VALUES ('BIO-101','1','Summer',2009,'Painter','514','B'),('BIO-301','1','Summer',2010,'Painter','514','A'),('C S-101','1','Fall',2009,'Packard','101','H'),('CS-101','1','Spring',2010,'Packard','101','F'),('CS-190','1',' Spring',2009,'Taylor','3128','E'),('CS-190','2','Spring',2009,'Taylor','3128','A'),('CS-315','1','Spring',2 010,'Watson','120','D'),('CS-319','1','Spring',2010,'Watson','100','B'),('CS-319','2','Spring',2010,'Tay lor','3128','C'),('CS-347','1','Fall',2009,'Taylor','3128','A'),('EE-181','1','Spring',2009,'Taylor','3128',' C'),('FIN-201','1','Spring',2010,'Packard','101','B'),('HIS-351','1','Spring',2010,'Painter','514','C'),('M U-199','1','Spring',2010,'Packard','101','D'),('PHY-101','1','Fall',2009,'Watson','100','A');/*!40000 ALTER TABLE `section` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `student`--DROP TABLE IF EXISTS `student`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `student` (`ID` varchar(5) NOT NULL,`name` varchar(20) DEFAULT NULL,`dept_name` varchar(20) DEFAULT NULL,`tot_cred` decimal(3,0) DEFAULT NULL,PRIMARY KEY (`ID`),KEY `dept_name` (`dept_name`),CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `student`--LOCK TABLES `student` WRITE;/*!40000 ALTER TABLE `student` DISABLE KEYS */;INSERT INTO `student` VALUES ('12345','Shankar','Comp.Sci',32),('19991','Brandt','History',80),('23121','Chavez','Finance',110),(' 44553','Peltier','Physics',56),('45678','Levy','Physics',46),('54321','Williams','Comp.Sci',54),('55739 ','Sanchez','Music',38),('70557','Snow','Physics',0),('76543','Brown','Comp.Sci',58),('76653','Aoi','E lec.Eng',60),('98765','Bourikas','Elec.Eng',98),('98988','Tanaka','Biology',120);/*!40000 ALTER TABLE `student` ENABLE KEYS */;UNLOCK TABLES;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = utf8 */ ;/*!50003 SET character_set_results = utf8 */ ;/*!50003 SET collation_connection = utf8_general_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger delete_student after delete on studentfor each rowbeginif(old.ID in(select ID from takes))then delete from takes where ID=old.ID;delete from advisor where s_ID=old.ID;end if;end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;---- Table structure for table `takes`--DROP TABLE IF EXISTS `takes`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `takes` (`ID` varchar(5) NOT NULL,`course_id` varchar(8) NOT NULL,`sec_id` varchar(8) NOT NULL,`semester` varchar(6) NOT NULL,`year` decimal(4,0) NOT NULL,`grade` varchar(2) DEFAULT NULL,PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),KEY `course_id` (`course_id`,`sec_id`,`semester`,`year`),CONSTRAINT `takes_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `student` (`ID`) ON DELETECASCADE,CONSTRAINT `takes_ibfk_2` FOREIGN KEY (`course_id`, `sec_id`, `semester`, `year`) REFERENCES `section` (`course_id`, `sec_id`, `semester`, `year`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `takes`--LOCK TABLES `takes` WRITE;/*!40000 ALTER TABLE `takes` DISABLE KEYS */;INSERT INTO `takes` VALUES ('12345','CS-101','1','Fall',2009,'C'),('12345','CS-190','2','Spring',2009,'A'),('12345','CS-315','1','Spri ng',2010,'A'),('12345','CS-347','1','Fall',2009,'A'),('19991','HIS-351','1','Spring',2010,'B'),('23121','F IN-201','1','Spring',2010,'C+'),('44553','PHY-101','1','Fall',2009,'B-'),('45678','CS-101','1','Fall',2009 ,'F'),('45678','CS-101','1','Spring',2010,'B+'),('45678','CS-319','1','Spring',2010,'B'),('54321','CS-101 ','1','Fall',2009,'A-'),('54321','CS-190','2','Spring',2009,'B+'),('55739','MU-199','1','Spring',2010,'A-' ),('76543','CS-101','1','Spring',2010,'A'),('76653','EE-181','1','Spring',2009,'C'),('98765','CS-101','1', 'Spring',2010,'B'),('98988','BIO-101','1','Summer',2009,'A');/*!40000 ALTER TABLE `takes` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `teaches`--DROP TABLE IF EXISTS `teaches`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `teaches` (`ID` varchar(5) NOT NULL,`course_id` varchar(8) NOT NULL,`sec_id` varchar(8) NOT NULL,`semester` varchar(6) NOT NULL,`year` decimal(4,0) NOT NULL,PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),KEY `course_id` (`course_id`,`sec_id`,`semester`,`year`),CONSTRAINT `teaches_ibfk_3` FOREIGN KEY (`course_id`, `sec_id`, `semester`, `year`) REFERENCES `section` (`course_id`, `sec_id`, `semester`, `year`) ON DELETE CASCADE, CONSTRAINT `teaches_ibfk_4` FOREIGN KEY (`ID`) REFERENCES `instructor` (`ID`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `teaches`--LOCK TABLES `teaches` WRITE;/*!40000 ALTER TABLE `teaches` DISABLE KEYS */;INSERT INTO `teaches` VALUES ('76766','BIO-101','1','Summer',2009),('76766','BIO-301','1','Summer',2010),('10101','CS-101','1',' Fall',2009),('45565','CS-101','1','Spring',2010),('83821','CS-190','1','Spring',2009),('83821','CS-190' ,'2','Spring',2009),('10101','CS-315','1','Spring',2010),('45565','CS-319','1','Spring',2010),('83821',' CS-319','2','Spring',2010),('10101','CS-347','1','Fall',2009),('98345','EE-181','1','Spring',2009),('121 21','FIN-201','1','Spring',2010),('32343','HIS-351','1','Spring',2010),('15151','MU-199','1','Spring',2 010),('22222','PHY-101','1','Fall',2009);/*!40000 ALTER TABLE `teaches` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `time_slot`--DROP TABLE IF EXISTS `time_slot`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `time_slot` (`time_slot_id` varchar(4) NOT NULL,`day` varchar(1) NOT NULL,`start_time` time NOT NULL,`end_time` time DEFAULT NULL,PRIMARY KEY (`time_slot_id`,`day`,`start_time`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `time_slot`--LOCK TABLES `time_slot` WRITE;/*!40000 ALTER TABLE `time_slot` DISABLE KEYS */;INSERT INTO `time_slot` VALUES ('A','F','08:00:00','08:50:00'),('A','M','08:00:00','08:50:00'),('A','W','08:00:00','08:50:00'),('B','F','09 :00:00','09:50:00'),('B','M','09:00:00','09:50:00'),('B','W','09:00:00','09:50:00'),('C','F','11:00:00','1 1:50:00'),('C','M','11:00:00','11:50:00'),('C','W','11:00:00','11:50:00'),('D','F','13:00:00','13:50:00'), ('D','M','13:00:00','13:50:00'),('D','W','13:00:00','13:50:00'),('E','R','10:30:00','11:45:00'),('E','T','10:30:00','11:45:00'),('F','R','14:30:00','15:45:00'),('F','T','14:30:00','15:45:00'),('G','F','16:00:00','16: 50:00'),('G','M','16:00:00','16:50:00'),('G','W','16:00:00','16:50:00'),('H','W','10:00:00','12:30:00'); /*!40000 ALTER TABLE `time_slot` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2015-01-11 21:10:50。

数据库系统概念（databasesystemconcepts）英文第六版课后练习题答案第8章C H A P T E R8Relational Database DesignExercises8.1Suppose that we decompose the schema R=(A,B,C,D,E)into(A,B,C)(A,D,E).Show that this decomposition is a lossless-join decomposition if thefollowing set F of functional dependencies holds:A→BCCD→EB→DE→AAnswer:A decomposition{R1,R2}is a lossless-join decomposition ifR1∩R2→R1or R1∩R2→R2.Let R1=(A,B,C),R2=(A,D,E),and R1∩R2=A.Since A is a candidate key(see Practice Exercise8.6),Therefore R1∩R2→R1.8.2List all functional dependencies satis?ed by the relation of Figure8.17.Answer:The nontrivial functional dependencies are:A→B and C→B,and a dependency they logically imply:AC→B.There are 19trivial functional dependencies of the form?→?,where.C does not functionally determine A because the?rst and third tuples havethe same C but different A values.The same tuples also showB does notfunctionally determine A.Likewise,A does not functionally determineC because the?rst two tuples have the same A value and different Cvalues.The same tuples also show B does not functionally determine C.8.3Explain how functional dependencies can be used to indicate the fol-lowing:910Chapter8Relational Database DesignA one-to-one relationship set exists between entity sets student andinstructor.A many-to-one relationship set exists between entity sets studentand instructor.Answer:Let Pk(r)denote the primary key attribute of relation r.The functi onal dependencies Pk(student)→Pk(instructor)and Pk(instructor)→Pk(student)indicate a one-to-one relationshipbecause any two tuples with the same value for student must havethe same value for instructor,and any two tuples agreeing on instructor must have the same value for student.The functional dependency Pk(student)→Pk(instructor)indicates amany-to-one relationship since any student value which isrepeatedwill have the same instructor value,but many student values mayhave the same instructor value.8.4Use Armstrong’s axioms to prove the soundness of the union rule.(Hint:Use the augmentation rule to show that,if?→?,then?→??.Apply theaugmentation rule again,using?→?,and then apply the transitivityrule.)Answer:To prove that:if?→?and?→?then?→??Following the hint,we derive:→?given→??augmentation rule→??union of identical sets→?given→??augmentation rule→??transitivity rule and set union commutativity8.5Use Armstrong’s axioms to prove the soundness of the pseudotransitiv-ity rule.Answer:Pr oof using Armstrong’s axioms of the Pseudotransitivity Rule:if?→?and??→?,then??→?.→?given→??augmentation rule and set union commutativity→?given→?transitivity rule8.6Compute the closure of the following set F of functional dependenciesfor relation schema R=(A,B,C,D,E).Exercises 11A →BCCD →EB →DE →AList the candidate keys for R .Answer:Note:It is not reasonable to expect students to enumerate all of F +.Some shorthand representation of the result should be acceptable as long as the nontrivial members of F +are found.Starting with A →BC ,we can conclude:A →B and A →C .Since A →B and B →D ,A →D (decomposition,transitive)Since A →C D and C D →E ,A →E (union,decom-position,transi-tive)Since A →A ,we have (re?exive)A →ABC DE from the above steps (union)Since E →A ,E →ABC DE (transitive)Since C D →E ,C D →ABC DE (transitive)Since B →D and BC →C D ,BC →ABC DE (augmentative,transitive)Also,C →C ,D →D ,B D →D ,etc.Therefore,any functional dependency with A ,E ,BC ,or C D on the left hand side of the arrow is in F +,no matter which other attributes appear in the FD.Allow *to represent any set of attributes in R ,then F +is B D →B ,B D →D ,C →C ,D →D ,B D →B D ,B →D ,B →B ,B →B D ,and all FDs of the form A ?→?,BC ?→?,C D ?→?,E ?→?where ?is any subset of {A ,B ,C ,D ,E }.The candidate keys are A ,BC ,C D ,and E .8.7Using the functional dependencies of Practice Exercise8.6,compute thecanonical cover F c .Answer:The given set of FDs F is:-A →BCCD →EB →DE →AThe left side of each FD in F is unique.Also none of the attributes in the left side or right side of any of the FDs is extraneous.Therefore the canonical cover F c is equal to F .12Chapter8Relational Database Design8.8Consider the algorithm in Figure8.18to compute?+.Show that thisalgorithm is more ef?cient than the one presented in Figure8.8(Sec-tion8.4.2)and that it computes?+correctly.Answer:The algorithm is correct because:If A is added to result then there is a proof that?→A.T o see this,observe that?→?trivially so?is correctly part of result.IfA∈?is added to result there must be some FD?→?such that A∈?and?is already a subset of result.(Otherwise f dcountwould be nonzero and the if condition would be false.)A full proofcan be given by induction on the depth of recursion for an executionof addin,but such a proof can be expected only from students witha good mathematical background.If A∈?+,then A is eventually added to result.We prove this byinduction on the length of the proof of?→A using Armstrong’saxioms.First observe that if procedure addin is called with someargument?,all the attributes in?will be added to result.Also if aparticular FD’s fdcount becomes0,all the attributes in its tail willde?nitely be added to result.The base case of the proof,A∈??A∈?+,is obviously true b ecause the?rst call to addinhas the argument?.The inductive hypotheses is that if?→A canbe proved in n steps or less then A∈result.If there is a proof inn+1steps that?→A,then the last step was an application ofeither re?exivity,augmentation or transiti vity on a fact?→?proved in n or fewer steps.If re?exivity or augmentation was usedin the(n+1)st step,A must have been in result by the end of the n thstep itself.Otherwise,by the inductive hypothesis??result.Therefore the dependency used in proving?→?,A∈?willhave f dcount set to0by the end of the n th step.Hence A will beadded to result.To see that this algorithm is more ef?cient than the one presented inthe chapter note that we scan each FD once in the main program.Theresulting array a ppears has size proportional to the size ofthe givenFDs.The recursive calls to addin result in processing linear in the sizeof a ppears.Hence the algorithm has time complexity which is linear inthe size of the given FDs.On the other hand,the algorithm given in thetext has quadratic time complexity,as it may perform the loop as manytimes as the number of FDs,in each loop scanning all of them once.8.9Given the database schema R(a,b,c),and a relation r on the schema R,write an SQL query to test whether the functional dependency b→cholds on relation r.Also write an SQL assertion that enforces the func-tional dependency.Assume that no null values are present.(Althoughpart of the SQL standard,such assertions are not supported by anydatabase implementation currently.)Answer:Exercises13a.The query is given below.Its result is non-empty if and only ifb→c does not hold on r.select bfrom rgroup by bhaving count(distinct c)>1b.create assertion b to c check(not exists(select bfrom rgroup by bhaving count(distinct c)>1))8.10Our discussion of lossless-join decomposition implicitly assumed thatattributes on the left-hand side of a functional dependency cannot take on null values.What could go wrong on decomposition,if this property is violated?Answer:The natural join operator is de?ned in terms of the cartesian product and the selection operator.The selection operator,gives unknown for any query on a null value.Thus,the natural join excludes all tuples with null values on the common attributes from the?nal result.Thus, the decomposition would be lossy(in a manner different from the usual case of lossy decomposition),if null values occur in the left-hand side of the functional dependency used to decompose the relation.(Null values in attributes that occur only in the right-hand side of the functional dependency do not cause any problems.)8.11In the BCNF decomposition algorithm,suppose you usea functional de-pendency?→?to decompose a relation schema r(?,?,?)into r1(?,?) and r2(?,?).a.What primary and foreign-key constraint do you expect toholdon the decomposed relations?b.Give an example of an inconsistency that can arise due to anerroneous update,if the foreign-key constraint were not enforcedon the decomposed relations above.c.When a relation is decomposed into3NF using the algorithm inSection8.5.2,what primary and foreign key dependencies wouldyou expect will hold on the decomposed schema?14Chapter8Relational Database DesignAnswer:a.?should be a primary key for r1,and?should be the foreign keyfrom r2,referencing r1.b.If the foreign key constraint is not enforced,then a deletion of atuple from r1would not have a corresponding deletion from thereferencing tuples in r2.Instead of deleting a tuple from r,this would amount to simply setting the value of?to null in some tuples.c.For every schema r i(??)added to the schema because of a rule→?,?should be made the primary key.Also,a candidate key?for the original relation is located in some newly created relationr k,and is a primary key for that relation.Foreign key constraints are created as follows:for each relationr i created above,if the primary key attributes of r i also occur inany other relation r j,then a foreign key constraint is created fromthose attributes in r j,referencing(the primary key of)r i.8.12Let R1,R2,...,R n be a decomposition of schema U.Let u(U)be a rela-(u).Show thattion,and let r i= RIu?r11r21···1r nAnswer:Consider some tuple t in u.(u)implies that t[R i]∈r i,1≤i≤n.Thus,Note that r i= Rit[R1]1t[R2]1...1t[R n]∈r11r21...1r nBy the de?nition of natural join,t[R1]1t[R2]1...1t[R n]= ?(??(t[R1]×t[R2]×...×t[R n]))where the condition?is satis?ed if values of attributes with the samename in a tuple are equal and where?=U.The cartesian productof single tuples generates one tuple.The selection process is satis?edbecause all attributes with the same name must have the same valuesince they are projections from the same tuple.Finally,the projectionclause removes duplicate attribute names.By th e de?nition of decomposition,U=R1∪R2∪...∪R n,which meansthat all attributes of t are in t[R1]1t[R2]1...1t[R n].That is,t is equalto the result of this join.Since t is any arbitrary tuple in u,u?r11r21...1r n8.13Show that the decomposition in Practice Exercise8.1is not a dependency-preserving decomposition.Answer:The dependency B→D is not preserved.F1,the restrictionof F to(A,B,C)is A→ABC,A→AB,A→AC,A→BC,Exercises 15A →B ,A →C ,A →A ,B →B ,C →C ,AB →AC ,AB →ABC ,AB →BC ,AB →AB ,AB →A ,AB →B ,AB →C,AC (same as AB ),BC (same as AB ),ABC (same as AB ).F 2,the restriction of F to (C ,D ,E )is A →ADE ,A →AD ,A →AE ,A →DE ,A →A ,A →D ,A →E ,D →D ,E (same as A ),AD ,AE ,DE ,ADE (same as A ).(F 1∪F 2)+is easily seen not to contain B →D since the only F D in F 1∪F 2with B as the left side is B →B ,a trivial FD .We shall see in Practice Exercise 8.15that B →D is indeed in F +.Thus B →D is not preserved.Note that C D →ABC DE is also not preserved.A simpler argument is as follows:F 1contains no dependencies with D on the right side of the arrow.F 2contains no dependencies withB on the left side of the arrow.Therefore for B →D to be preserved theremustbe an FD B →?in F +1and ?→D in F +2(so B →D would follow by transitivity).Since the intersection of the two schemes isA ,?=A .Observe thatB →A is not in F +1since B +=B D .8.14Show that it is possible to ensure that a dependency-preserving decom-position into 3NF is a lossless-join decomposition by guaranteeing that at least one schema contains a candidate key for the schema being decom-posed.(Hint :Show that the join of all the projections onto the schemas of the decomposition cannot have more tuples than the original relation.)Answer:Let F be a set of functional dependencies that hold on a schema R .Let ?={R 1,R 2,...,R n }be a dependency-preserving 3NF decompo-sition of R .Let X be a candidate key for R .Consider a legal instance r of R .Let j = X (r )1 R 1(r )1 R 2(r ) (1)R n (r ).We want to prove that r =j .We claim that if t 1and t 2are two tuples in j such that t 1[X ]=t2[X ],then t 1=t 2.To prove this claim,we use the following inductive argument –Let F ′=F 1∪F 2∪...∪F n ,where each F i is the restriction of F to the schema R i in ?.Consider the use of the algorithm given in Figure 8.8to compute the closure of X under F ′.We use induction on the number of times that the f or loop in this algorithm is executed.Basis :In the ?rst step of the algorithm,result is assigned to X ,and hence given that t 1[X ]=t 2[X ],we know that t 1[result ]=t 2[result ]is true.?Induction Step :Let t 1[result ]=t 2[result ]be true at the end of thek th execution of the f or loop.Suppose the functionaldependency considered in the k +1th execution of the f or loop is ?→?,and that ??result .??result implies that t 1[?]=t 2[?]is true.The facts that ?→?holds for some attribute set Ri in ?,and that t 1[R i ]and t 2[R i ]are inR i (r )imply that t 1[?]=t 2[?]is also true.Since ?is now added to result by the algorithm,we know that t 1[result ]=t 2[result ]is true at theend of the k +1th execution of the f or loop.16Chapter8Relational Database DesignSince?is dependency-preserving and X is a key for R,all attributes in Rare in result when the algorithm terminates.Thus,t1[R]=t2[R]is true,that is,t1=t2–as claimed earlier.Our claim implies that the size of X(j)is equal to the size of j.Notealso that X(j)= X(r)=r(since X is a key for R).Thus we haveproved that the size of j equals that of /doc/826273026.htmling the result of PracticeExercise8.12,we know that r?j.Hence we conclude that r=j.Note that since X is trivially in3NF,?∪{X}is a dependency-preservinglossless-join decomposition into3NF.8.15Give an example of a relation schema R′and set F′of functional depen-dencies such that there are at least three distinct lossless-join decompo-sitions of R′into BCNF.Answer:Given the relation R′=(A,B,C,D)the set of functionaldependencies F′=A→B,C→D,B→C allows three distinctBCNF decompositions.R1={(A,B),(C,D),(B,C)}is in BCNF as isR2={(A,B),(C,D),(A,C)}R2={(A,B),(C,D),(A,C)}R3={(B,C),(A,D),(A,B)}8.16Let a prime attribute be one that appears in at least one candidate key.Let?and?be sets of attributes such that?→?holds,but?→?does not hold.Let A be an attribute that is not in?,is not in?,and forwhich?→A holds.We say that A is transitively dependent on?.Wecan restate our de?nition of3NF as follows:A relation schema R is in3NF with respect to a set F of functional dependencies if there are nononprime attributes A in R for which A is transitively dependent on akey for R.Show that this new de?nition is equivalent to the original one.Answer:Suppose R is in3NF according to the textbook de?nition.Weshow that it is in3NF according to the de?nition in the exercise.Let A bea nonprime attribute in R that is transitively dependent on a key?forR.Then there exists??R such that?→A,?→?,A∈?,A∈,and?→?does not hold.But then?→A violates the textbookde?nition of3NF sinceA∈?implies?→A is nontrivialSince?→?does not hold,?is not a superkeyA is not any candidate key,since A is nonprimeExercises17 Now we show that if R is in3NF according to the exercise de?nition,it is in3NF according to the textbook de?nition.Suppose R is not in3NF according the the textbook de?nition.Then there is an FD?→?that fails all three conditions.Thus→?is nontrivial.is not a superkey for R.Some A inis not in any candidate key.This implies that A is nonprime and?→A.Let?be a candidate key for R.Then?→?,?→?does not hold(since?is not a superkey), A∈?,and A∈?(since A is nonprime).Thus A is transitively dependent on?,violating the exercise de?nition.8.17A functional dependency?→?is called a partial dependency if thereis a proper subset?of?such that?→?.We say that?is partially dependent on?.A relation schema R is in second normal form(2NF)if each attribute A in R meets one of the following criteria:It appears in a candidate key.It is not partially dependent on a candidate key.Show that every3NF schema is in2NF.(Hint:Show that every partial dependency is a transitive dependency.)Answer:Referring to the de?nitions in Practice Exercise8.16,a relation schema R is said to be in3NF if there is no non-prime attribute A in R for which A is transitively dependent on a key forR.We can also rewrite the de?nition of2NF given here as:“A relation schema R is in2NF if no non-prime attribute A is partially dependent on any candidate key for R.”To prove that every3NF schema is in2NF,it suf?ces to show that if a non-prime attribute A is partially dependent on a candidate key?,thenA is also transitively dependent on the key?.Let A be a non-prime attribute in R.Let?be a candidate key for R.Suppose A is partially dependent on?.From the de?nition of a partial dependency,we know that for someproper subset?of?,?→A.Since,?→?.Also,?→?does not hold,sin ce?is acandidate key.Finally,since A is non-prime,it cannot be in either?or?.Thus we conclude that?→A is a transitive dependency.Hence we have proved that every3NF schema is also in2NF.8.18Give an example of a relation schema R and a set of dependencies suchthat R is in BCNF but is not in4NF.18Chapter8Relational Database DesignAnswer:R(A,B,C)A→→BExercises19result:=?;/*fdcount is an array whose i th element contains the number of attributes on the left side of the i th FD that arenot yet known to be in?+*/for i:=1to|F|dobeginlet?→?denote the i th FD;fdcount[i]:=|?|;end/*appears is an array with one entry for each attribute.The entry for attribute A is a list of integers.Each integeri on the list indicates that A appears on the left sideof the i th FD*/for each attribute A dobeginappears[A]:=NI L;for i:=1to|F|dobeginlet?→?denote the i th FD;if A∈?then add i to appears[A];endendaddin(?);return(result);procedure addin(?);for each attribute A in?dobeginif A∈result thenbeginresult:=result∪{A};for each element i of appears[A]dobeginfdcount[i]:=fdcount[i]?1;if fdcount[i]:=0thenbeginlet?→?denote the i th FD;addin(?);endendendendFigure8.18.An algorithm to compute?+.。

C H A P T E R22Object-Based DatabasesPractice Exercises22.1A car-rental company maintains a database for all vehicles in its cur-rentfleet.For all vehicles,it includes the vehicle identification number,license number,manufacturer,model,date of purchase,and color.Spe-cial data are included for certain types of vehicles:•Trucks:cargo capacity.•Sports cars:horsepower,renter age requirement.•Vans:number of passengers.•Off-road vehicles:ground clearance,drivetrain(four-or two-wheel drive).Construct an SQL schema definition for this e inheritancewhere appropriate.Answer:For this problem,we use table inheritance.We assume thatMyDate,Color and DriveTrainType are pre-defined types.create type Vehicle(vehicle id integer,license number char(15),manufacturer char(30),model char(30),purchase date MyDate,color Color)create table vehicle of type Vehiclecreate table truck(cargo capacity integer)under vehiclecreate table sportsCar12Chapter22Object-Based Databases(horsepower integerrenter age requirement integer)under vehiclecreate table van(num passengers integer)under vehiclecreate table offRoadVehicle(ground clearance realdriveTrain DriveTrainType)under vehicle22.2Consider a database schema with a relation Emp whose attributes areas shown below,with types specified for multivalued attributes.Emp=(ename,ChildrenSet multiset(Children),SkillSet multiset(Skills))Children=(name,birthday)Skills=(type,ExamSet setof(Exams))Exams=(year,city)a.Define the above schema in SQL,with appropriate types for eachattribute.ing the above schema,write the following queries in SQL.i.Find the names of all employees who have a child born on orafter January1,2000.ii.Find those employees who took an examination for the skilltype“typing”in the city“Dayton”.iii.List all skill types in the relation Emp.Answer:a.No Answer.b.Queries in SQL.i.Program:select enamefrom emp as e,e.ChildrenSet as cwhere’March’in(select birthday.monthfrom c)ii.Program:Practice Exercises3select e.enamefrom emp as e,e.SkillSet as s,s.ExamSet as xwhere s.type=’typing’and x.city=’Dayton’iii.Program:select distinct s.typefrom emp as e,e.SkillSet as s22.3Consider the E-R diagram in Figure22.5,which contains composite,multivalued,and derived attributes.a.Give an SQL schema definition corresponding to the E-R diagram.b.Give constructors for each of the structured types defined above.Answer:a.The corresponding SQL:1999schema definition is given below.Note that the derived attribute age has been translated into amethod.create type Name(first name varchar(15),middle initial char,last name varchar(15))create type Street(street name varchar(15),street number varchar(4),apartment number varchar(7))create type Address(street Street,city varchar(15),state varchar(15),zip code char(6))create table customer(name Name,customer id varchar(10),address Adress,phones char(7)array[10],dob date)method integer age()b.create function Name(f varchar(15),m char,l varchar(15))returns Namebeginsetfirst name=f;set middle initial=m;set last name=l;endcreate function Street(sname varchar(15),sno varchar(4),ano varchar(7))4Chapter22Object-Based Databasesreturns Streetbeginset street name=sname;set street number=sno;set apartment number=ano;endcreate function Address(s Street,c varchar(15),sta varchar(15),zip varchar(6))returns Addressbeginset street=s;set city=c;set state=sta;set zip code=zip;end22.4Consider the relational schema shown in Figure22.6.a.Give a schema definition in SQL corresponding to the relationalschema,but using references to express foreign-key relationships.b.Write each of the queries given in Exercise6.13on the aboveschema,using SQL.Answer:a.The schema definition is given below.Note that backward ref-erences can be addedbut they are not so important as in OODBSbecause queries can be written in SQL and joins can take care ofintegrity constraints.create type Employee(person name varchar(30),street varchar(15),city varchar(15))create type Company(company name varchar(15),(city varchar(15))create table employee of Employeecreate table company of Companycreate type Works(person ref(Employee)scope employee,comp ref(Company)scope company,salary int)create table works of Workscreate type Manages(person ref(Employee)scope employee,(manager ref(Employee)scope employee)create table manages of Managesb.i.select comp−>namePractice Exercises5from worksgroup by comphaving count(person)≥all(select count(person)from worksgroup by comp)ii.select comp−>namefrom worksgroup by comphaving sum(salary)≤all(select sum(salary)from worksgroup by comp)iii.select comp−>namefrom worksgroup by comphaving avg(salary)>(select avg(salary)from workswhere comp−>company name="First Bank Corporation")22.5Suppose that you have been hired as a consultant to choose a databasesystem for your client’s application.For each of the following appli-cations,state what type of database system(relational,persistent pro-gramming language–based OODB,object relational;do not specify acommercial product)you would recommend.Justify your recommen-dation.a.A computer-aided design system for a manufacturer of airplanes.b.A system to track contributions made to candidates for publicoffice.c.An information system to support the making of movies.Answer:a.A computer-aided design system for a manufacturer of airplanes:An OODB system would be suitable for this.That is because CADrequires complex data types,and being computation oriented,CAD tools are typically used in a programming language envi-ronment needing to access the database.b.A system to track contributions made to candidates for publicoffice:A relational system would be apt for this,as data types are ex-pected to be simple,and a powerful querying mechanism is es-sential.c.An information system to support the making of movies:Here there will be extensive use of multimedia and other complexdata types.But queries are probably simple,and thus an objectrelational system is suitable.6Chapter22Object-Based Databases22.6How does the concept of an object in the object-oriented model differfrom the concept of an entity in the entity-relationship model?Answer:An entity is simply a collection of variables or data items.An object is an encapsulation of data as well as the methods(code)tooperate on the data.The data members of an object are directly visibleonly to its methods.The outside world can gain access to the object’sdata only by passing pre-defined messages to it,and these messagesare implemented by the methods.。

C H A P T E R21Information RetrievalPractice Exercises21.1Compute the relevance(using appropriate definitions of term fre-quency and inverse document frequency)of each of the Practice Ex-ercises in this chapter to the query“SQL relation”.Answer:We do not consider the questions containing neither of thekeywords as their relevance to the keywords is zero.The number ofwords in a question include stop words.We use the equations givenin Section21.2to compute relevance;the log term in the equation isassumed to be to the base2.21.2Suppose you want tofind documents that contain at least k of a givenset of n keywords.Suppose also you have a keyword index that givesyou a(sorted)list of identifiers of documents that contain a specifiedkeyword.Give an efficient algorithm tofind the desired set of docu-ments.Answer:Let S be a set of n keywords.An algorithm tofind all docu-ments that contain at least k of these keywords is given below:12Chapter21Information RetrievalThis algorithm calculates a reference count for each document identi-fier.A reference count of i for a document identifier d means that atleast i of the keywords in S occur in the document identified by d.The algorithm maintains a list of records,each having twofields–adocument identifier,and the reference count for this identifier.This listis maintained sorted on the document identifierfield.initialize the list L to the empty list;for(each keyword c in S)dobeginD:=the list of documents identifiers corresponding to c;for(each document identifier d in D)doif(a record R with document identifier as d is on list L)thenR.re f erence count:=R.re f erence count+1;else beginmake a new record R;R.document id:=d;R.re f erence count:=1;add R to L;end;end;for(each record R in L)doif(R.re f erence count>=k)thenoutput R;Note that execution of the second for statement causes the list D to“merge”with the list L.Since the lists L and D are sorted,the timetaken for this merge is proportional to the sum of the lengths of the twolists.Thus the algorithm runs in time(at most)proportional to n timesthe sum total of the number of document identifiers corresponding toeach keyword in S.21.3Suggest how to implement the iterative technique for computing Page-Rank given that the T matrix(even in adjacency list representation)does notfit in memory.Answer:No answer21.4Suggest how a document containing a word(such as“leopard”)canbe indexed such that it is efficiently retrieved by queries using a moregeneral concept(such as“carnivore”or“mammal”).You can assumethat the concept hierarchy is not very deep,so each concept has onlya few generalizations(a concept can,however,have a large number ofspecializations).You can also assume that you are provided with a func-tion that returns the concept for each word in a document.Also suggesthow a query using a specialized concept can retrieve documents usinga more general concept.Answer:Add doc to index lists for more general concepts also.Practice Exercises3 21.5Suppose inverted lists are maintained in blocks,with each block not-ing the largest popularity rank and TF-IDF scores of documents in the remaining blocks in the list.Suggest how merging of inverted lists can stop early if the user wants only the top K answers.Answer:For all documents whose scores are not complete use upper bounds to compute best possible score.If K th largest completed score is greater than the largest upper bound among incomplete scores output the K top answers.No answer。

数据库系统概念第六版课后习题部分答案2sC H A P T E R2Introduction to the Relational ModelPractice Exercises2.1Consider the relational database of Figure??.What are the appropriateprimary keys?Answer:The answer is shown in Figure2.1,with primary keys under-lined.2.2Consider the foreign key constraint from the dept name attribute of in-structor to the department relation.Give examples of inserts and deletes tothese relations,which can cause a violation of the foreign key constraint.Answer:Inserting a tuple:(10111,Ostrom,Economics,110,000)into the instructor table,where the department table does not have thedepartment Economics,would violate the foreign key constraint.Deleting the tuple:(Biology,Watson,90000)from the department table,where at least one student or instructortuple has dept name as Biology,would violate the foreign key con-straint.employee(person name,street,city)works(person name company name,salary)company(company name,city)Figure2.1Relational database for Practice Exercise2.1.12Chapter2Introduction to the Relational Model2.3Consider the time slot relation.Given that a particular time slot can meetmore than once in a week,explain why day and start time are part of theprimary key of this relation,while end time is not.Answer:The attributes day and start time are part of the primary keysince a particular class will most likely meet on several different days,and may even meet more than once in a day.However,end time is notpart of the primary key since a particular class that starts at a particulartime on a particular day cannot end at more than one time.2.4In the instance of instructor shown in Figure??,no two instructors havethe same name.From this,can we conclude that name can be used as asuperkey(or primary key)of instructor?Answer:No.For this possible instance of the instructor table the namesare unique,but in general this may not be always the case(unless theuniversity has a rule that two instructors cannot have the same name,which is a rather unlikey scenario).2.5What is the result of?rst performing the cross product of student andadvisor,and then performing a selection operation on the result with thepredicate s id=ID?(Using the symbolic notation of relational algebra,this query can be written as?s id=I D(student×advisor).)Answer:The result attributes include all attribute values of studentfollowed by all attributes of advisor.The tuples in the result are asfollows.For each student who has an advisor,the result has a rowcontaining that students attributes,followed by an s id attribute identicalto the students ID attribute,followed by the i id attribute containing theID of the students advisor.Students who do not have an advisor will not appear in the result.Astudent who has more than one advisor will appear a correspondingnumber of times in the result.2.6Consider the following expressions,which use the result ofa relationalalgebra operation as the input to another operation.For each expression,explain in words what the expression does.a.?year≥2009(takes)1studentb.?year≥2009(takes1student)c. ID,name,course id(student1takes)Answer:a.For each student who takes at least one course in2009,displaythe students information along with the information about whatcourses the student took.The attributes in the result are:ID,name,dept name,tot cred,course id,section id,semester,year,gradeb.Same as(a);selection can be done before the join operation.c.Provide a list of consisting ofExercises3ID,name,course idof all students who took any course in the university.2.7Consider the relational database of Figure??.Give an expression in therelational algebra to express each of the following queries:a.Find the names of all employees who live in city“Miami”.b.Find the names of all employees whose salary is greater than$100,000.c.Find the names of all employees who live in“Miami”and whosesalary is greater than$100,000.Answer:a. name(?city=“Miami”(employee))b. name(?salary>100000(employee))c. name(?city=“Miami”∧salary>100000(employee))2.8Consider the bank database of Figure??.Give an expression in therelational algebra for each of the following queries.a.Find the names of all branches located in“Chicago”.b.Find the names of all borrowers who have a loan in branch“Down-town”.Answer:a. branch name(?branch city=“Chicago”(branch))b. customer name(?branch name=“Downtown”(borro w er1loan))。

数据库英文版第六版课后答案Chapter 1: IntroductionQuestions1.What is a database?A database is a collection of organized and structured data stored electronically in a computer system. It allows users to efficiently store, retrieve, and manipulate large amounts of data.2.What are the advantages of using a database system?–Data sharing and integration: A database system allows multiple users to access and share data simultaneously.–Data consistency and integrity: A database system enforces rules and constraints to maintain the accuracy and integrity of the data.–Data security: A database system provides access control mechanisms to ensure that data is accessed by authorized users only.–Data independence: A database system separates the data from the application programs that use it, allowing for easier applicationdevelopment and maintenance.Exercises1.Discuss the advantages and disadvantages of using a database system.Advantages:–Data sharing and integration–Data consistency and integrity–Data security–Data independenceDisadvantages:–Cost: Database systems can be expensive to set up and maintain.–Complexity: Database systems require a certain level of expertise to design, implement, and manage.–Performance overhead: Database systems may introduce some overhead in terms of storage and processing.Overall, the advantages of using a database system outweigh the disadvantages in most cases, especially for large-scale applications with multiple users and complex data requirements.Chapter 2: Relational Model and Relational Algebra Questions1.What is a relation? How is it represented in the relational model?A relation is a table-like structure that represents a set of related data. It is represented as a two-dimensional table with rows and columns, where each row corresponds to a record and each column corresponds to a attribute or field.2.What is the primary key of a relation?The primary key of a relation is a unique identifier for each record in the relation. It is used to ensure the uniqueness and integrity of the data.Exercises1.Consider the following relation:Employees (EmpID, Name, Age, Salary)–EmpID is the primary key of the Employees relation.–Name, Age, and Salary are attributes of the Employees relation.2.Write a relational algebra expression to retrieve the names of all employees whose age is greater than 30.π Name (σ Age > 30 (Employees))Chapter 3: SQLQuestions1.What is SQL?SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It provides a set of commands and statements that allow users to create, modify, and query databases.2.What are the main components of an SQL statement?An SQL statement consists of the following main components:–Keywords: SQL commands and instructions.–Clauses: Criteria and conditions that specify what data to retrieve or modify.–Expressions: Values, variables, or calculations used in SQL statements.–Operators: Symbols used to perform operations on data. Exercises1.Write an SQL statement to create a table called。

C H A P T E R17Database System ArchitecturesPractice Exercises17.1Instead of storing shared structures in shared memory,an alternativearchitecture would be to store them in the local memory of a specialprocess,and access the shared data by interprocess communicationwith the process.What would be the drawback of such an architecture?Answer:The drawbacks would be that two interprocess messageswould be required to acquire locks,one for the request and one toconfirm grant.Interprocess communication is much more expensivethan memory access,so the cost of locking would increase.The processstoring the shared structures could also become a bottleneck.The benefit of this alternative is that the lock table is protected betterfrom erroneous updates since only one process can access it.17.2In typical client–server systems the server machine is much more pow-erful than the clients;that is,its processor is faster,it may have multipleprocessors,and it has more memory and disk capacity.Consider in-stead a scenario where client and server machines have exactly thesame power.Would it make sense to build a client–server system insuch a scenario?Why?Which scenario would be better suited to adata-server architecture?Answer:With powerful clients,it still makes sense to have a client-server system,rather than a fully centralized system.If the data-serverarchitecture is used,the powerful clients can off-load all the long andcompute intensive transaction processing work from the server,freeingit to perform only the work of satisfying read-write requests.even ifthe transaction-server model is used,the clients still take care of theuser-interface work,which is typically very compute-intensive.A fully distributed system might seem attractive in the presence ofpowerful clients,but client-server systems still have the advantage ofsimpler concurrency control and recovery schemes to be implemented1718Chapter17Database System Architectureson the server alone,instead of having these actions distributed in allthe machines.17.3Consider a database system based on a client–server architecture,withthe server acting as a data server.a.What is the effect of the speed of the interconnection betweenthe client and the server on the choice between tuple and pageshipping?b.If page shipping is used,the cache of data at the client can beorganized either as a tuple cache or a page cache.The page cachestores data in units of a page,while the tuple cache stores data inunits of tuples.Assume tuples are smaller than pages.Describeone benefit of a tuple cache over a page cache.Answer:a.We assume that tuples are smaller than a page andfit in a page.If the interconnection link is slow it is better to choose tuple ship-ping,as in page shipping a lot of time will be wasted in shippingtuples that might never be needed.With a fast interconnectionthough,the communication overheads and latencies,not the ac-tual volume of data to be shipped,becomes the bottle neck.Inthis scenario page shipping would be preferable.b.Two benefits of an having a tuple-cache rather than a page-cache,even if page shipping is used,are:i.When a client runs out of cache space,it can replace objectswithout replacing entire pages.The reduced caching granu-larity might result in better cache-hit ratios.ii.It is possible for the server to ask clients to return some ofthe locks which they hold,but don’t need(lock de-escalation).Thus there is scope for greater concurrency.If page caching isused,this is not possible.17.4Suppose a transaction is written in C with embedded SQL,and about80percent of the time is spent in the SQL code,with the remaining20percent spent in C code.How much speedup can one hope to attain ifparallelism is used only for the SQL code?Explain.Answer:Since the part which cannot be parallelized takes20%of thetotal running time,the best speedup we can hope for has to be less than5.17.5Some database operations such as joins can see a significant differencein speed when data(for example,one of the relations involved in ajoin)fits in memory as compared to the situation where the data doesnotfit in memory.Show how this fact can explain the phenomenonof superlinear speedup,where an application sees a speedup greaterthan the amount of resources allocated to it.Answer:FILLPractice Exercises19 17.6Parallel systems often have a network structure where sets of n pro-cessors connect to a single Ethernet switch,and the Ethernet switches themselves connect to another Ethernet switch.Does this architecture correspond to a bus,mesh or hypercube architecture?If not,how would you describe this interconnection architecture?Answer:FILL。