精华内容
下载资源
问答
  • 数据库系统概念 课后习题答案pdf 实践习题 影印版
  • 数据库系统概念课后Practice Exercises答案
  • 数据库系统概念课后习题答案,很全哦。
  • 数据库系统概念课后系统答案 史上最全说明 每道让你明明白白解答
  • 华南理工大学数据库系统概念试卷 往年 从2005-2013年都有,有答案,详细,需要速度看过来
  • 数据库系统概念袁术第六版,英文版每章前半部分习题答案,很全,很实用
  • 数据库系统概念第六版对应练习以及试卷,包含了各个章节内容,题目经典且详细,包含完整答案,还有两套期中考试卷
  • 该文档为数据库系统概念第6版习题参考答案,里面基本上包含所有习题,是英文版,但是不影响解读。
  • 数据库系统概念第六版英文版习题答案,学习数据库还是读读英文版不错.
  • 这是数据库系统概念的实践习题的答案是原书英文第五版的。
  • 由Abraham Slilberschatz,Henry F.Korth,S.Sudarshan所著,高等教育出版社出版数据库系统概念 第五版》练习答案。
  • 数据库系统概念第六版课后习题答案, 是PDF格式,包含一到二十六章内容
  • 数据库系统概念第五版课后答案,英文版
  • 数据库系统概念复习资料 复习 期末复习 习题集 期中考试卷 资料整理 期末复习时候,整理了较多材料,包括了各个章节习题以及知识点整理,还有几套期中、期末考试卷,内容较多,分享给大家 word文档内容...

    数据库系统概念复习资料 复习题 期末复习 习题集 期中考试卷 资料整理

    展开全文
  • 数据库系统概念原书第六版课后习题答案 免费哦。
  • 数据库系统概念第四章习题答案 4.1 a.显示所有教师列表,列出他们ID、姓名以及所讲授课程段编号。对于没有讲授任何课程教师,确保将课程段编号显示为0.在你查询之中应该使用外连接,不能使用标量子查询。 ...

    数据库系统概念第四章习题答案

    在这里插入图片描述
    在这里插入图片描述

    4.1

    a.显示所有教师列表,列出他们的ID、姓名以及所讲授课程段的编号。对于没有讲授任何课程的教师,确保将课程段编号显示为0.在你的查询之中应该使用外连接,不能使用标量子查询。
    select ID, name, sec_id
    set sec_id = case
    			when sec_id is null then 0
    			end
    from instructor natural left outer join teaches
    group by ID,name;
    

    上面的set语句不知道对不对!oracle没有跑成功

    标答

    select ID, name,
    	count(course_id, sec_id, year,semester) as 'Number of sections'
    from instructor natural left outer join teaches
    group by ID, name
    

    这题有点不太理解题目意思,哭~~~~标答在oracle上显示count()参数错误,我也觉得神奇!

    函数nvl(p,q)可以实现null值赋值:如果p是null,则赋予q。如下

    select ID,name,nvl(sec_id,0)
    from instructor natural left outer join teaches;
    

    oracle结果正确,但是不能使用group by.

    b.使用标量子查询,不使用外连接写出上述查询。

    直接上标答

    select ID, name,
    	(select count(*) as 'Number of sections'
    	 from teaches T 
    	 where T.id = I.id)
    from instructor I
    
    c. 显示2010年春季开设的所有课程的列表,包括讲授课程段的教师姓名。如果一个课程段有不止一位教师讲授,那么有多少位教师,此课程段在结果中就出现多少次。如果一个课程段没有任何教师,它也要出现在结果中,相应教师名设置为“—”.
    select course_id, sec_id, ID,
    	decode(name, NULL, '-', name)
    from (section natural left outer join teaches)
    			natural left outer join instructor
    where semester='Spring' and year= 2010
    

    decode()函数,检测控制并赋值。
    decode函数详细介绍
    section和teaches左外连接给出了对应课程上老师的ID,再与instructor左外连接得到老师的name.

    d. 显示所有系的列表,包括每个系中教师的总人数,不能使用标量子查询。确保正确处理没有教师的系。
    select distinct dept_name, count(ID)
    from department natural left outer join instructor
    group by dept_name;
    

    4.2 不使用外连接重写查询

    a.

    select* from student natural left outer join takes
    

    解答:

    select * from student natural join takes
    union
    select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL
    from student S1 where not exists
    (select ID from takes T1 where T1.id = S1.id)
    
    b.
    select* from student natural full outer join takes
    

    解答

    select* from student natural join takes
    union
    select ID, name,dept_name,tot_cred, NULL,NULL,NULL,NULL,NULL
    from student S where not exists
    	(select ID from takes T where S.ID = T.ID)
    union
    select ID, NULL,NULL,NULL,course_id, sec_id,semester,yesr,grade
    from takes T where not exists 
    	(select ID from student S where T.ID =S.ID)
    

    4.7 关系数据库如下

    在这里插入图片描述
    Answer:

    create table employee
    (person name char(20),
    street char(30),
    city char(30),
    primary key (person_name))
    
    create table works
    (person name char(20),
    company name char(15),
    salary integer,
    primary key (person_name),
    foreign key (person_name) references employee,
    foreign key (company_name) references company)
    
    create table company
    (company_name char(15),
    city char(30),
    primary key (company_name))
    
    create table manages
    (person_name char(20),
    manager_name char(20),
    primary key (person_name),
    foreign key (person_name) references employee,
    foreign key (manager_name) references employee)
    

    雇员数据库模式图

    4.12 查找那些没有经理的雇员。注意一个雇员可能只是没有列出其经理,或者有null经理。

    使用外连接

    select employee_name
    from employee natural left outer join manages
    where manager_name is null
    

    不使用外连接

    (1)not exists版本

    select employee_name as d
    from employee as E
    where not exists
    	(select employee_name
    	from manages as M
    	where E.employee_name = M.employee_name 
    		and manager_name is not null
    

    (2)not in版本

    select employee_name as d
    from employee as E
    where not in
    	(select employee_name
    	from manages as M
    	where E.employee_name = M.employee_name 
    		and manager_name is not null
    

    (3)except版本

    select employee_name
    from employee
    except
    select employee_name
    from employee natural join manages
    where manager_name is not null 
    
    展开全文
  • ——数据库系统概念(第六版)第三章习题 数据库版本:oracle 11g; 工具:PL/ SQL 3.11 使用大学模式,用SQL写出如下查询 a.超出所有选修了至少一门Comp. SCI.课程学生姓名,保证结果中没有重复姓名。 ...

    小白做题

    ——数据库系统概念(第六版)第三章习题

    数据库版本:oracle 11g; 工具:PL/ SQL

    有问题下面评论处提

    3.11 使用大学模式,用SQL写出如下查询

    a.超出所有选修了至少一门Comp. SCI.课程的学生姓名,保证结果中没有重复的姓名。

    SELECT DISTINCT NAME FROM student NATURAL JOIN takes NATURAL JOIN course WHERE dept_name = 'Comp. Sci.'
    

    b.找出所有没有选修在2009年春季之前开设的任何课程的学生的ID和姓名

    SELECT ID,NAME FROM student WHERE ID NOT IN (
        SELECT ID FROM takes WHERE YEAR < 2009 )
    

    c.找出每个系教师的最高工资。可以假设每个系至少有一位教师。

    SELECT dept_name,MAX(salary) FROM instructor GROUP BY dept_name 
    

    d.从前述查询所计算出的每个系最高工资中选出最低值

    WITH max_salary ( dept_name,max_salary) AS (
        SELECT  dept_name,MAX(salary) FROM instructor GROUP BY dept_name) 
      SELECT MIN(max_salary) FROM max_salary      
    

    3.12使用大学模式用SQL写出如下查询

    a.创建一门课程“CS-001”,其名称为“Weekly Seminar”,学分为0

    INSERT INTO course VALUES ('CS-001','Weekly Seminar','Comp. Sci.',0);
    

    与之前类似的,网站的DDL对course表的credits项有检查不能为0,故需用去掉该项检查。

    b.创建该课程在2009年秋季的一个课程段,sec_id为1.

    INSERT INTO SECTION(course_id,sec_id,Semester,Year) VALUES('CS-001','1','Fall','2009');
    

    c.让Comp. Sci.系的每个学生都选修上述课程段

    INSERT INTO takes (ID,course_id,sec_id,semester,year) SELECT ID,'CS-001','1','Fall','2009'FROM student WHERE dept_name='Comp. Sci.' ;
    

    d.删除名为Chavez的学生选修上述课程段的信息

    DELETE FROM takes WHERE ID=(
        SELECT ID FROM student WHERE NAME='Chavez'
        )
        AND course_id ='CS-001'
    

    e.删除课程CS-001。如果在运行此语句之前没有先删除这门课程授课信息(课程段),会发生什么事情。

    DELETE FROM course WHERE course_id = 'CS-001' 
    // 由于DDL 中设定的course_id外健为级联删除,故section表中对应的CS-001元组会一同被删除 
    

    f.删除课程名中包含“database”的任意课程的任意课程段所对应的所有takes元组,在课程名的匹配中忽略大小写

    DELETE FROM takes WHERE course_id =(
        SELECT course_id FROM course WHERE LOWER(title) LIKE '%database%'
        )   
    

    3.13 写出对应于图3-18中模式的SQL DDL。在数据类型上做合理的假设,确保声明主码和外码。

    CREATE TABLE person 
      (driver_id  NUMBER(13,0),
       NAME VARCHAR2(20) NOT NULL,
       address VARCHAR2(100), 
       PRIMARY KEY (driver_id)
       );
       
    CREATE TABLE car
      (license VARCHAR2(8),
       MODEL VARCHAR2(10),
       YEAR  NUMBER(4,0),
       PRIMARY KEY (license)
      );
      
    CREATE TABLE accident
      (report_number NUMBER(6,0),
       datetime DATE,
       LOCATION VARCHAR2(100),
       PRIMARY KEY (report_number)
       );
            
    CREATE TABLE owns
      (driver_id  NUMBER(13,0),
       license VARCHAR2(8),
       PRIMARY KEY (driver_id),
       FOREIGN KEY (driver_id) REFERENCES person 
         on delete CASCADE,
       FOREIGN KEY (license) REFERENCES car
         on delete set NULL   
       );
    
    CREATE TABLE participated 
      (report_number NUMBER(6,0),
       license VARCHAR2(8),
       driver_id  NUMBER(13,0),
       damage_amount NUMBER(15,0),
       PRIMARY KEY (report_number ,license),
       FOREIGN KEY (report_number) REFERENCES accident
         ON DELETE CASCADE,
       FOREIGN KEY (license) REFERENCES car
         ON DELETE SET NULL
      )
    

    3.14考虑图3-18中的保险公司数据库,其中加下划线的是主码。对这个关系数据库构造如下的SQL查询。

    a.找出和“John Smith”的车有关的交通事故数量

    select count(report_number) from participated natural join person 
      where name = 'John Smith'
    

    b.对事故报告编号为“AR2197”中的车牌是“AABB2000”的车辆损坏保险费用更新到3000美元

    update participated set damage_amount = '3000' 
      where report_number='AR2197' and license='AABB2000'
    

    3.15考虑图3-19中的银行数据库,其中加下划线的是主码。为这个关系数据库构造出如下SQL查询:

    a.找出在“Brooklyn”的所有支行都有账户的所有客户。

    WITH s_br(num1) AS (
        SELECT COUNT(1) FROM branch WHERE branch_city='Brooklyn'
        )
      (SELECT customer_name FROM customer a 
        WHERE (
            SELECT COUNT(DISTINCT branch_name) FROM 
                (SELECT * from  customer NATURAL JOIN depositor NATURAL JOIN ACCOUNT NATURAL JOIN branch
                 ) b
                 WHERE a.customer_name=b.customer_name AND branch_city='Brooklyn'
         )=(
         SELECT num1 FROM s_br))
    

    b.找出银行的所有贷款额的总和

    select sum(amount) from loan
    

    c.找出总资产至少比位于Brooklyn的某一家支行要多的所有支行名字

    select customer_name from depositor a
      where Brooklyn_count =(
          select count(distinct branch_name) from (SELECT * FROM customer natural join depositor natrual join account natural join branch)  b
              where a.name=b.name)
    

    3.16考虑图3-20中的雇员数据库,其中加下划线的是主码。给出下面每个查询对应的SQL语句

    c.找错所有为“First Bank Corporation”工作的雇员的名字

    select employee_name from works where company_name='First Bank Corporation'
    

    b.找出数据库中所有居住城市和公司所在城市相同的雇员

    with em_c(name,city) as 
        (select employee_name,city from works natural join company)
      select employee_name from employee,em_c 
          where employee.employee_name=em_c.name and employee.city=em_c.city
    

    c.找出数据库中所有居住的街道和城市与其经理相同的雇员

    select a.employee_name from employee a,employee b,managers 
      where a.employee_name=managers.employee_name 
      and managers.manager_name = b.managers
      and a.city=b.city 
      and a.street =b.street
    

    d.找出工资高于其所在公司雇员平均工资工资的所有雇员

    select employee_name from works w1
      where salary > 
        (select avg(salary) from works group by company_name 
           having works.company_name =w1.company_name
           )
    

    e.找出工资总和最小的公司

    with salary_sum (name,numb) as
      (select company_name,sum(salary) from works group by company_name )
    select name from salary_sum where numb = 
      ( select min(numb) from salary_sum )
    

    3.17 考虑图3-20中的关系数据库。给出下面每个查询对应的SQL表达式。

    a.为“First Bank Corporation”的所有雇员增长10%的工资

    update works set salary=salary*1.1 
      where company_name = 'First Bank Corporation'
    

    b.为“First Bank Corporation”的所有经理增长10%的工资

    update works set salary=salary*1.1
      where company_name = 'First Bank Corporation'
      and employee_name in (select manager_name from managers )
    

    c.删除“Small Bank Corporation”的雇员在works关系中的所有元组

    delete from works where company_name = 'Small Bank Corporation' 
    

    3.18 列出两个原因,说明为什么空值可以被引入数据库

    客观世界中,有时描述事物的信息并不全面,引入空值就可以表示记录的这个属性缺乏,便于数据库的维护和管理,那么引入空值就有两个原因:属性未知,属性不存在

    3.19 证明在SQL中,<>all 等价于not in

    在SQL中,<>all 即表示与全部元组均不相符,not in表示在全部元组中没有相匹配的,故<>all与not in 等价

    3.20 给出图3-20中雇员数据库的SQL模式定义。为每个属性选择合适的域,并为每个关系模式选择合适的主码。

    CREATE TABLE employee 
      (employee_name VARCHAR2(20),
       street VARCHAR2(20),
       city VARCHAR2(20),
       PRIMARY KEY (employee_name)
      );
    CREATE TABLE company
      (company_name VARCHAR2(20),
       city VARCHAR2(20),
       PRIMARY KEY (company_name)
      );
    CREATE TABLE works
      (employee_name VARCHAR2(20),
       company_name VARCHAR2(20),
       salary NUMBER(8,2),
       PRIMARY KEY (employee_name),
       FOREIGN KEY (employee_name) REFERENCES employee ON DELETE CASCADE,
       FOREIGN KEY (company_name) REFERENCES company ON DELETE CASCADE
       );
    CREATE TABLE managers 
      (employee_name VARCHAR2(20),
       manager_name VARCHAR2(20),
       PRIMARY KEY (employee_name),
       FOREIGN KEY (employee_name) REFERENCES employee ON DELETE CASCADE,
       FOREIGN KEY (manager_name) REFERENCES employee ON DELETE SET NULL
       )
    

    3.21考虑图3-21中图书馆数据库。用SQL写出如下查询

    a.打印借阅了任意由“McGraw-Hill”出版的书的会员名字

    select distinct name from member natural join borrowed natural join book 
      where publisher ='McGraw-Hill'
    

    b.打印借阅了所有由“McGraw-Hill”出版的书的会员名字

    with Mc_num(m_no,is) as 
        (select memb_no,isbn from borrowed natural join 
          where publisher ='McGraw-Hill')
       select name from member where memb_no in 
           (select m_no from Mc_num 
              group by m_no having count(is) = 
                  (select count(isbn) from book where publisher= 'McGraw-Hill')
           )               
    

    c.对于每个出版商,打印借阅了多于5本由该出版商出版的书的会员名字

    select name,publisher from member natural join book natural join borrowed 
      group by name,publisher having count(isbn)>5
    

    d.打印每位会员借阅书籍数量的平均值。考虑这样的情况,如果某会员没有借阅任何书籍,那么该会员根本不会出现在borrowed关系中

    select(select count(*) from borrowed)/ (select count(*)from member) FROM dual
    

    3.22不使用unique结构,重写下面的where子句

    where unique (select title from course)
    
    where 1>=(select count(title) from course)
    

    3.23考虑查询:

    select course_id,semeter,year,sec_id,avg(tot_cred)
    from takes natural join student
    where year=2000
    group by course_id,semester,year,sec_id
    having count(ID) >=2
    

    解释为什么在from子句中还加上与section的连接不会改变查询结果

    takes 和 section 是通过一些共同的外键相连系,每一个takes的某一个元组不会因为增加额外的元祖。

    3.24 考虑查询:

    with dept_total (dept_name,value) as
        (select  dept_name,sum(salary)
        from instructor
        group by dept_name),
    dept_total_avg (value) as
        (select avg(value)
        from dept_total)
    select  dept_name
    from dept_total,dept_total_avg
    where dept_total.value>=dept_total_avg.value
    

    不使用with结构,重写此查询

    SELECT dept_name 
      FROM (
          SELECT dept_name,SUM(salary) sa_sum
          FROM instructor
          GROUP BY dept_name
          ) 
      WHERE sa_sum>=(
          SELECT AVG(SUM(salary)) 
          FROM instructor 
          GROUP BY dept_name
          )
    

    终于码完了。。。。

    展开全文
  • 数据库系统概念 英文精编版 第6版 [美]Abraham 课后答案描述 我从学院网站上下载,据说是代课老师做答案,貌似版本有点低,但是大部分答案都有,要仔细找一下,顺序是乱。 上学期买答案书,用扫描仪一页...
  • 数据库系统的第五版答案,可以好好学习数据库
  • 数据库系统概念+第五版+答案+习题部分(即练习后半部分) 如果有谁有提供IT项目 第四版KS练习答案就好了
  • 截图来自数据库系统概念中文第六版 想要中文版和英文版请留言!!!我会尽量及时回复!!! 数据库系统体系结构 实践习题和答案 1.2 1.3 1.6 习题 1.13 Answer: A general purpose ...

    截图来自数据库系统概念中文第六版

    想要中文版和英文版的请留言!!!我会尽量及时回复!!!

    数据库系统体系结构

    实践习题和答案

    1.2

    1.3

    1.6

    习题

    1.13

    Answer: A general purpose database-management system (DBMS) has five responsibilities:
    a. interaction with the file manager.
    b. integrity enforcement.
    c. security enforcement.
    d. backup and recovery.
    e. concurrency control.
    If these responsibilities were not met by a given DBMS (and the text points out that sometimes a responsibility is omitted by design, such as concurrency control on a single-user DBMS for a micro computer) the following problems can occur, respectively:
    a. No DBMS can do without this, if there is no file manager interaction
    then nothing stored in the files can be retrieved.
    b. Consistency constraints may not be satisfied, for example an instructor may belong to a non-existent department, two students may have
    the same ID, account balances could go below the minimum allowed,
    and so on.
    c. Unauthorized users may access the database, or users authorized to
    access part of the database may be able to access parts of the database
    for which they lack authority. For example, a low-level user could get
    access to national defense secret codes, or employees could find out
    what their supervisors earn (which is presumably a secret).
    d. Data could be lost permanently, rather than at least being available
    in a consistent state that existed prior to a failure.
    e. Consistency constraints may be violated despite proper integrity enforcement in each transaction. For example, incorrect bank balances
    might be reflected due to simultaneous withdrawals and deposits on
    the same account, and so on.

    补充思考

    1.在文件处理系统中存储组织信息的主要弊端有哪些???

    Data redundancy and inconsistency
    Difficulty in accessing data.
    Data isolation.
    Integrity problems.
    Atomicity problems.
    Concurrent-access anomalies.
    Security problems.

    2.为了使得存储数据值满足一致性约束,数据库系统以哪些方式实现完整性约束?

    (我没搞懂一致性约束和完整性约束的区别)

    Domain Constraints
    Referential Integrity
    Assertions.
    Authorization.

    以下资料来源:

    http://www.zybang.com/question/e23cf724ee5a0d00d2cba4f9ccb2d23b.html

    https://zhidao.baidu.com/question/53409759.html

    https://blog.csdn.net/luke_wang/article/details/53240215

    一致性约束和完整性约束的区别

    3.数据抽象的三层

    4.实例和模式

    5.数据模型

    6.元数据和数据字典

    7.存储管理器

    8.查询处理器

    9.事务和事务管理器

    展开全文
  • 数据库系统概念(第6版)是一本经典计算机著作,在此提供与其配套实践习题答案以及大学模式样本数据
  • 这是经典数据库教材——Abraham Silberchatz等人数据库系统概念》(第五版)exercise练习答案。希望对你有用!
  • 该文档包含了完整的数据库系统概念第六版习题答案和实践习题答案,希望可以帮到大家

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,286
精华内容 514
关键字:

数据库系统概念的概念题