数据库系统概念作业1答案

合集下载

数据库系统概论第五版课后习题答案

第1章绪论1 .试述数据、数据库、数据库系统、数据库管理系统的概念。

答:( l )数据( Data ) :描述事物的符号记录称为数据。

数据的种类有数字、文字、图形、图像、声音、正文等。

数据与其语义就是不可分的。

解析在现代计算机系统中数据的概念就是广义的。

早期的计算机系统主要用于科学计算,处理的数据就是整数、实数、浮点数等传统数学中的数据。

现代计算机能存储与处理的对象十分广泛,表示这些对象的数据也越来越复杂。

数据与其语义就是不可分的。

500这个数字可以表示一件物品的价格就是 500元,也可以表示一个学术会议参加的人数有 500人,还可以表示一袋奶粉重 500克。

( 2 )数据库( DataBase ,简称 DB ) :数据库就是长期储存在计算机内的、有组织的、可共享的数据集合。

数据库中的数据按一定的数据模型组织、描述与储存,具有较小的冗余度、较高的数据独立性与易扩展性,并可为各种用户共享。

( 3 )数据库系统( DataBas。

Sytem ,简称 DBS ) :数据库系统就是指在计算机系统中引入数据库后的系统构成,一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员构成。

解析数据库系统与数据库就是两个概念。

数据库系统就是一个人一机系统,数据库就是数据库系统的一个组成部分。

但就是在日常工作中人们常常把数据库系统简称为数据库。

希望读者能够从人们讲话或文章的上下文中区分“数据库系统”与“数据库”,不要引起混淆。

( 4 )数据库管理系统( DataBase Management sytem ,简称 DBMs ) :数据库管理系统就是位于用户与操作系统之间的一层数据管理软件,用于科学地组织与存储数据、高效地获取与维护数据。

DBMS的主要功能包括数据定义功能、数据操纵功能、数据库的运行管理功能、数据库的建立与维护功能。

解析 DBMS就是一个大型的复杂的软件系统,就是计算机中的基础软件。

目前,专门研制 DBMS的厂商及其研制的 DBMS产品很多。

数据库系统概论(第四版)习题和答案

说明：高等教育出版社的《数据库系统概论》第4版，简称《概论》；机械工业出版社的《数据库系统概念》第5版，简称《概念》。

1.试论述数据库系统主要动机、构成及DBMS主要功能特性。

说明数据库系统与文件系统的主要区别。

答：数据库设计的动机：数据库设计的目的是为了管理大量信息。

数据库系统一般由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员和用户构成。

DBMS主要功能：•数据库定义功能：DBMS提供数据定义语言（DDL）定义数据库的三级结构，包括外模式、概念模式、内模式及基相互之间的映象，定义数据的完整性、安全控制等约束。

因此，在DBMS中应包括DDL的编译程序。

•数据库的操纵功能：DBMS提供数据操纵语言（DML）实现对数据库中数据的操作。

基本的数据操作分成两类四种：检索（查询）、更新（插入、删除、修改）•数据库的保护功能：数据库中的数据是信息社会的战略资源，对数据的保护是至关重要的大事。

•数据库的存储管理：把各种DML语句转换成低层的文件系统命令，起到数据的存储、检索和更新的作用。

•数据库的维护功能：它有许多实用程序提供给数据库管理员：数据装载程序、备份程序、文件重组织程序、性能监控程序•数据字典：数据库系统中存放三级结构定义的数据库称为数据字典（DD）。

对数据库的操作都要通过访问DD才能实现，通常DD中还存放数据库运行时的统计信息。

文件系统和数据库系统主要区别：•在文件系统中，相互独立的文件的记录内部是有结构的，但记录之间没有联系。

数据库系统实现整体数据的结构化，是数据库的主要特征之一。

•在文件系统中，数据的最小存取单位是记录，粒度不能细到数据项。

而在数据库系统中，存取数据的方式也很灵活，可以存取数据库中的某一个数据项、一组数据项一个记录或或一组记录。

•文件系统中的文件是为某一特定应用服务的，文件的逻辑结构对该应用程序来说是优化的，因此要想对现有的数据再增加一些新的应用会很困难，系统不容易扩充。

数据库系统概念答案(第五版)

C H A P T E R2Exercises2.4Describe the differences in meaning between the terms relation and relation schema.Answer:A relation schema is a type deﬁnition,and a relation is an instance of that schema.For example,student(ss#,name)is a relation schema andis a relation based on that schema.2.5Consider the relational database of Figure2.35,where the primary keys are un-derlined.Give an expression in the relational algebra to express each of the fol-lowing queries:a.Find the names of all employees who work for First Bank Corporation.b.Find the names and cities of residence of all employees who work for FirstBank Corporation.c.Find the names,street address,and cities of residence of all employees whowork for First Bank Corporation and earn more than$10,000per annum.d.Find the names of all employees in this database who live in the same cityas the company for which they work.e.Assume the companies may be located in several cities.Find all companieslocated in every city in which Small Bank Corporation is located.Answer:a.Πperson-name(σcompany-name=“First Bank Corporation”(works))78Chapter2Relational Modelemployee(person-name,street,city)works(person-name,company-name,salary)company(company-name,city)manages(person-name,manager-name)Figure2.35.Relational database for Exercises2.1,2.3and2.9.b.Πperson-name,city(employee1(σcompany-name=“First Bank Corporation”(works)))c.Πperson-name,street,city(σ(company-name=“First Bank Corporation”∧salary>10000)works1employee)d.Πperson-name(employee1works1company)e.Note:Small Bank Corporation will be included in each answer.Πcompany-name(company÷(Πcity(σcompany-name=“Small Bank Corporation”(company))))2.6Consider the relation of Figure2.20,which shows the result of the query“Findthe names of all customers who have a loan at the bank.”Rewrite the query to include not only the name,but also the city of residence for each customer.Observe that now customer Jackson no longer appears in the result,even though Jackson does in fact have a loan from the bank.a.Explain why Jackson does not appear in the result.b.Suppose that you want Jackson to appear in the result.How would youmodify the database to achieve this effect?c.Again,suppose that you want Jackson to appear in the result.Write a queryusing an outer join that accomplishes this desire without your having to modify the database.Answer:The rewritten query isΠcustomer-name,customer-city,amount(borrower1loan1customer)a.Although Jackson does have a loan,no address is given for Jackson in thecustomer relation.Since no tuple in customer joins with the Jackson tuple of borrower,Jackson does not appear in the result.b.The best solution is to insert Jackson’s address into the customer relation.Ifthe address is unknown,null values may be used.If the database system does not support nulls,a special value may be used(such as unknown)for Jackson’s street and city.The special value chosen must not be a plausible name for an actual city or street.c.Πcustomer-name,customer-city,amount((borrower1loan)1customer)2.7Consider the relational database of Figure2.35.Give an expression in the rela-tional algebra for each request:a.Give all employees of First Bank Corporation a10percent salary raise.Exercises 9b.Give all managers in this database a 10percent salary raise,unless the salary would be greater than $100,000.In such cases,give only a 3percent raise.c.Delete all tuples in the works relation for employees of Small Bank Corpora-tion.Answer:a.works ←Πperson -name,company -name,1.1∗salary (σ(company -name =“First Bank Corporation”)(works ))∪(works −σcompany -name =“First Bank Corporation”(works ))b.The same situation arises here.As before,t 1,holds the tuples to be updated and t 2holds these tuples in their updated form.t 1←Πworks.person -name,company -name,salary (σworks.person -name =manager -name (works ×manages ))t 2←Πworks.person -name,company -name,salary ∗1.03(σt 1.salary ∗1.1>100000(t 1))t 2←t 2∪(Πworks.person -name,company -name,salary ∗1.1(σt 1.salary ∗1.1≤100000(t 1)))works ←(works −t 1)∪t 2c.works ←works −σcompany −name =“Small Bank Corporation”(works )2.8Using the bank example,write relational-algebra queries to ﬁnd the accountsheld by more than two customers in the following ways:ing an aggregate function.b.Without using any aggregate functions.Answer:a.t 1←account -number G count customer -name (depositor )Πaccount -number σnum -holders>2 ρaccount -holders (account -number,num -holders )(t 1)b.t 1←(ρd 1(depositor )×ρd 2(depositor )×ρd 3(depositor ))t 2←σ(d 1.account -number =d 2.account -number =d 3.account -number )(t 1)Πd 1.account -number (σ(d 1.customer -name =d 2.customer -name ∧d 2.customer -name =d 3.customer -name ∧d 3.customer -name =d 1.customer -name )(t 2))2.9Consider the relational database of Figure 2.35.Give a relational-algebra expres-sion for each of the following queries:a.Find the company with the most employees.b.Find the company with the smallest payroll.c.Find those companies whose employees earn a higher salary,on average,than the average salary at First Bank Corporation.Answer:10Chapter 2Relational Modela.t 1←company -name G count-distinct person -name (works )t 2←max num -employees (ρcompany -strength (company -name,num -employees )(t 1))Πcompany -name (ρt 3(company -name,num -employees )(t 1)1ρt 4(num -employees )(t 2))b.t 1←company -name G sum salary (works )t 2←min payroll (ρcompany -payroll (company -name,payroll )(t 1))Πcompany -name (ρt 3(company -name,payroll )(t 1)1ρt 4(payroll )(t 2))c.t 1←company -name G avg salary (works )t 2←σcompany -name =“First Bank Corporation”(t 1)Πt pany -name ((ρt 3(company -name,avg -salary )(t 1))1t 3.avg -salary >first -bank.avg -salary (ρfirst -bank (company -name,avg -salary )(t 2)))2.10List two reasons why null values might be introduced into the database.Answer:Nulls may be introduced into the database because the actual value is either unknown or does not exist.For example,an employee whose address has changed and whose new address is not yet known should be retained with a null address.If employee tuples have a composite attribute dependents ,and a particular employee has no dependents,then that tuple’s dependents attribute should be given a null value.2.11Consider the following relational schemaemployee (empno ,name ,ofﬁce ,age )books (isbn ,title ,authors ,publishe r )loan (empno ,isbn ,date )Write the following queries in relational algebra.a.Find the names of employees who have borrowed a book published by McGraw-Hill.b.Find the names of employees who have borrowed all books published byMcGraw-Hill.c.Find the names of employees who have borrowed more than ﬁve different books published by McGraw-Hill.d.For each publisher,ﬁnd the names of employees who have borrowed morethan ﬁve books of that publisher.Answer:No answerExercises3.8Consider the insurance database of Figure 3.11,where the primary keys are un-derlined.Construct the following SQL queries for this relational database.a.Find the number of accidents in which the cars belonging to “John Smith ”were involved.b.Update the damage amount for the car with license number “AABB2000”in the accident with report number “AR2197”to $3000.Answer:Note:The participated relation relates drivers,cars,and accidents.a.SQL query:selectcount (distinct *)fromaccident where exists(select *from participated,personwhere participated.driver id =person.driver idand =’John Smith’and accident.report number =participated.report number )b.SQL query:update participatedset damage amount =3000where report number =“AR2197”and driver id in(select driver idfrom ownswhere license =“AABB2000”)11C H A P T E R312Chapter3SQLperson(driver id,name,address)car(license,model,year)accident(report number,date,location)owns(driver id,license)participated(driver id,car,report number,damage amount)Figure3.11.Insurance database.employee(employee name,street,city)works(employee name,company name,salary)company(company name,city)manages(employee name,manager name)Figure3.12.Employee database.3.9Consider the employee database of Figure3.12,where the primary keys are un-derlined.Give an expression in SQL for each of the following queries.a.Find the names of all employees who work for First Bank Corporation.b.Find all employees in the database who live in the same cities as the com-panies for which they work.c.Find all employees in the database who live in the same cities and on thesame streets as do their managers.d.Find all employees who earn more than the average salary of all employeesof their company.e.Find the company that has the smallest payroll.Answer:a.Find the names of all employees who work for First Bank Corporation.select employee namefrom workswhere company name=’First Bank Corporation’b.Find all employees in the database who live in the same cities as the com-panies for which they work.select e.employee namefrom employee e,works w,company cwhere e.employee name=w.employee name and e.city=c.city andpany name=pany namec.Find all employees in the database who live in the same cities and on thesame streets as do their managers.select P.employee namefrom employee P,employee R,manages Mwhere P.employee name=M.employee name andM.manager name=R.employee name andP.street=R.street and P.city=R.cityExercises13d.Find all employees who earn more than the average salary of all employeesof their company.The following solution assumes that all people work for at most one com-pany.select employee namefrom works Twhere salary>(select avg(salary)from works Swhere pany name=pany name)e.Find the company that has the smallest payroll.select company namefrom worksgroup by company namehaving sum(salary)<=all(select sum(salary)from worksgroup by company name)3.10Consider the relational database of Figure3.12.Give an expression in SQL foreach of the following queries.a.Give all employees of First Bank Corporation a10percent raise.b.Give all managers of First Bank Corporation a10percent raise.c.Delete all tuples in the works relation for employees of Small Bank Corpora-tion.Answer:a.Give all employees of First Bank Corporation a10-percent raise.(the solu-tion assumes that each person works for at most one company.)update worksset salary=salary*1.1where company name=’First Bank Corporation’b.Give all managers of First Bank Corporation a10-percent raise.update worksset salary=salary*1.1where employee name in(select manager namefrom manages)and company name=’First Bank Corporation’c.Delete all tuples in the works relation for employees of Small Bank Corpora-tion.delete workswhere company name=’Small Bank Corporation’3.11Let the following relation schemas be given:14Chapter3SQLR=(A,B,C)S=(D,E,F)Let relations r(R)and s(S)be given.Give an expression in SQL that is equivalentto each of the following queries.a.ΠA(r)b.σB=17(r)c.r×sd.ΠA,F(σC=D(r×s))Answer:a.ΠA(r)select distinct Afrom rb.σB=17(r)select*from rwhere B=17c.r×sselect distinct*from r,sd.ΠA,F(σC=D(r×s))select distinct A,Ffrom r,swhere C=D3.12Let R=(A,B,C),and let r1and r2both be relations on schema R.Give anexpression in SQL that is equivalent to each of the following queries.a.r1∪r2b.r1∩r2c.r1−r2d.ΠAB(r1)1ΠBC(r2)Answer:a.r1∪r2(select*from r1)union(select*from r2)b.r1∩r2We can write this using the intersect operation,which is the preferred approach,but for variety we present an solution using a nested subquery.Exercises15select*from r1where(A,B,C)in(select*from r2)c.r1−r2select∗from r1where(A,B,C)not in(select∗from r2)This can also be solved using the except clause.d.ΠAB(r1)1ΠBC(r2)select r1.A,r2.B,r3.Cfrom r1,r2where r1.B=r2.B3.13Show that,in SQL,<>all is identical to not in.Answer:Let the set S denote the result of an SQL subquery.We compare(x<>all S)with(x not in S).If a particular value x1satisﬁes(x1<>all S)then for all elements y of S x1=y.Thus x1is not a member of S and must satisfy(x1not in S).Similarly,suppose there is a particular value x2which satisﬁes(x2not inS).It cannot be equal to any element w belonging to S,and hence(x2<>all S) will be satisﬁed.Therefore the two expressions are equivalent.3.14Consider the relational database of ing SQL,deﬁne a view con-sisting of manager name and the average salary of all employees who work for that manager.Explain why the database system should not allow updates to be expressed in terms of this view.Answer:create view salinfo asselect manager name,avg(salary)from manages m,works wwhere m.employee name=w.employee namegroup by manager nameUpdates should not be allowed in this view because there is no way to de-termine how to change the underlying data.For example,suppose the request is“change the average salary of employees working for Smith to$200”.Should everybody who works for Smith have their salary changed to$200?Or should theﬁrst(or more,if necessary)employee found who works for Smith have their salary adjusted so that the average is$200?Neither approach really makes sense.3.15Write an SQL query,without using a with clause,toﬁnd all branches wherethe total account deposit is less than the average total account deposit at allbranches,16Chapter3SQLing a nested query in the from clauser.ing a nested query in a having clause.Answer:We output the branch names along with the total account deposit atthe branch.ing a nested query in the from clauser.select branch name,tot balancefrom(select branch name,sum(balance)from accountgroup by branch name)as branch total(branch name,tot balance)where tot balance¡(select avg(tot balance)from(select branch name,sum(balance)from accountgroup by branch name)as branch total(branch name,tot balance))ing a nested query in a having clause.select branch name,sum(balance)from accountgroup by branch namehaving sum(balance)¡(select avg(tot balance)from(select branch name,sum(balance)from accountgroup by branch name)as branch total(branch name,tot balance))3.16List two reasons why null values might be introduced into the database.Answer:No Answer3.17Show how to express the coalesce operation from Exercise3.4using the caseoperation.Answer:No Answer.3.18Give an SQL schema deﬁnition for the employee database of Figure3.12.Choosean appropriate domain for each attribute and an appropriate primary key foreach relation schema.Answer:create domain company names char(20)create domain city names char(30)create domain person names char(20)create table employeeExercises17 (employee name person names,street char(30),city city names,primary key(employee name))create table works(employee name person names,company name company names,salary numeric(8,2),primary key(employee name))create table company(company name company names,city city names,primary key(company name))create table manages(employee name person names,manager name person names,primary key(employee name))3.19Using the relations of our sample bank database,write SQL expressions to deﬁnethe following views:a.A view containing the account numbers and customer names(but not thebalances)for all accounts at the Deer Park branch.b.A view containing the names and addresses of all customers who have anaccount with the bank,but do not have a loan.c.A view containing the name and average account balance of every customerof the Rock Ridge branch.Answer:No Answer.3.20For each of the views that you deﬁned in Exercise3.19,explain how updateswould be performed(if they should be allowed at all).Answer:No Answer.3.21Consider the following relational schemaemployee(empno,name,ofﬁce,age)books(isbn,title,authors,publisher)loan(empno,isbn,date)Write the following queries in SQL.a.Print the names of employees who have borrowed any book published byMcGraw-Hill.18Chapter3SQLb.Print the names of employees who have borrowed all books published byMcGraw-Hill.c.For each publisher,print the names of employees who have borrowed morethanﬁve books of that publisher.Answer:No Answer.3.22Consider the relational schemastudent(student id,student name)registered(student id,course id)Write an SQL query to list the student-id and name of each student along withthe total number of courses that the student is registered for.Students who arenot registered for any course must also be listed,with the number of registeredcourses shown as0.Answer:No Answer.3.23Suppose that we have a relation marks(student id,score).Write an SQL query toﬁnd the dense rank of each student.That is,all students with the top mark get arank of1,those with the next highest mark get a rank of2,and so on.Hint:Splitthe task into parts,using the with clause.Answer:No Answer.C H A P T E R4Exercises4.7Referential-integrity constraints as deﬁned in this chapter involve exactly tworelations.Consider a database that includes the following relations:salaried-worker(name,ofﬁce,phone,salary)hourly-worker(name,hourly-wage)address(name,street,city)Suppose that we wish to require that every name that appears in address appear in either salaried-worker or hourly-worker,but not necessarily in both.a.Propose a syntax for expressing such constraints.b.Discuss the actions that the system must take to enforce a constraint of thisform.Answer:a.For simplicity,we present a variant of the SQL syntax.As part of the createtable expression for address we includeforeign key(name)references salaried-worker or hourly-workerb.To enforce this constraint,whenever a tuple is inserted into the address rela-tion,a lookup on the name value must be made on the salaried-worker relationand(if that lookup failed)on the hourly-worker relation(or vice-versa).4.8Write a Java function using JDBC metadata features that takes a ResultSet asan input parameter,and prints out the result in tabular form,with appropriate names as column headings.Answer:No Answer.1920Chapter4Advanced SQL4.9Write a Java function using JDBC metadata features that prints a list of all re-lations in the database,displaying for each relation the names and types of itsattributes.Answer:No Answer.4.10Consider an employee database with two relationsemployee(employee-name,street,city)works(employee-name,company-name,salary)where the primary keys are underlined.Write a query toﬁnd companies whoseemployees earn a higher salary,on average,than the average salary at First BankCorporation.ing SQL functions as appropriate.b.Without using SQL functions.Answer:a.create function avg-salary(cname varchar(15))returns integerdeclare result integer;select avg(salary)into resultfrom workswhere pany-name=cnamereturn result;endselect company-namefrom workswhere avg-salary(company-name)>avg-salary(”First Bank Corporation”)b.select company-namefrom worksgroup by company-namehaving avg(salary)>(select avg(salary)from workswhere company-name=”First Bank Corporation”)4.11Rewrite the query in Section4.6.1that returns the name,street and city of allcustomers with more than one account,using the with clause instead of using afunction call.Answer:No Answer.4.12Compare the use of embedded SQL with the use in SQL of functions deﬁned ina general-purpose programming language.Under what circumstances wouldyou use each of these features?Answer:SQL functions are primarily a mechanism for extending the powerof SQL to handle attributes of complex data types(like images),or to performcomplex and non-standard operations.Embedded SQL is useful when imper-ative actions like displaying results and interacting with the user are needed.Exercises21 These cannot be done conveniently in an SQL only environment.Embedded SQL can be used instead of SQL functions by retrieving data and then perform-ing the function’s operations on the SQL result.However a drawback is that a lot of query-evaluation functionality may end up getting repeated in the host language code.4.13Modify the recursive query in Figure4.14to deﬁne a relationempl depth(employee name,manager name,depth)where the attribute depth indicates how many levels of intermediate managers are there between the employee and the manager.Employees who are directly under a manager would have a depth of0.Answer:No Answer.4.14Consider the relational schemapart(part id,name,cost)subpart(part id,subpart id,count)A tuple(p1,p2,3)in the subpart relation denotes that the part with part-id p2is adirect subpart of the part with part-id p1,and p1has3copies of p2Note that p2 may itself have further subparts.Write a recursive SQL query that outputs the names of all subparts of the part with part-id“P-100”.Answer:No Answer.4.15Consider again the relational schema from Exercise4.14.Write a JDBC functionusing non-recursive SQL toﬁnd the total cost of part“P-100”,including the costs of all its subparts.Be sure to take into account the fact that a part may have multiple occurrences of a subpart.You may use recursion in Java if you wish.Answer:No Answer.7.22Using the functional dependencies of Practice Exercise7.6,compute B+.Answer:Computing B+by the algorithm in Figure7.9we start with result= {B}.Considering FDs of the formβ→γin F,weﬁnd that the only depen-dencies satisfyingβ⊆result are B→B and B→D.Therefore result= {B,D}.No more dependencies in F apply now.Therefore B+={B,D}7.23Show that the following decomposition of the schema R of Practice Exercise7.1is not a lossless-join decomposition:(A,B,C)(C,D,E).Hint:Give an example of a relation r on schema R such thatΠA,B,C(r)1ΠC,D,E(r)=rAnswer:Following the hint,use the following example of r:A B C D Ea1b1c1d1e1a2b2c1d2e2With R1=(A,B,C),R2=(C,D,E):a.ΠR1(r)would be:A B Ca1b1c1a2b2c1b.ΠR2(r)would be:C D Ec1d1e1c1d2e2c.ΠR1(r)1ΠR2(r)would be: A B C D Ea1b1c1d1e1a1b1c1d2e2a2b2c1d1e1a2b2c1d2e2Clearly,ΠR1(r)1ΠR2(r)=r.Therefore,this is a lossy join.Exercises6.14Explain the distinctions among the terms primary key,candidate key,and su-perkey.Answer:A superkey is a set of one or more attributes that,taken collectively,al-lows us to identify uniquely an entity in the entity set.A superkey may contain extraneous attributes.If K is a superkey,then so is any superset of K.A superkey for which no proper subset is also a superkey is called a candidate key.It is pos-sible that several distinct sets of attributes could serve as candidate keys.The primary key is one of the candidate keys that is chosen by the database designer as the principal means of identifying entities within an entity set.6.15Construct an E-R diagram for a hospital with a set of patients and a set of medi-cal doctors.Associate with each patient a log of the various tests and examina-tions conducted.Answer:See Figure6.16.16Construct appropriate tables for each of the E-R diagrams in Practice Exercises6.1and6.2.Answer:a.Car insurance tables:person(driver-id,name,address)car(license,year,model)accident(report-number,date,location)participated(driver-id,license,report-number,damage-amount)b.Hospital tables:33Figure6.1E-R diagram for a hospital.patients(patient-id,name,insurance,date-admitted,date-checked-out)doctors(doctor-id,name,specialization)test(testid,testname,date,time,result)doctor-patient(patient-id,doctor-id)test-log(testid,patient-id)performed-by(testid,doctor-id)c.University registrar’s tables:student(student-id,name,program)course(courseno,title,syllabus,credits)course-offering(courseno,secno,year,semester,time,room)instructor(instructor-id,name,dept,title)enrols(student-id,courseno,secno,semester,year,grade)teaches(courseno,secno,semester,year,instructor-id)requires(maincourse,prerequisite)。

数据库系统概论及习题及答案

数据库系统概论复习资料：第一章：一选择题：1．在数据管理技术的发展过程中，经历了人工管理阶段、文件系统阶段和数据库系统阶段。

在这几个阶段中，数据独立性最高的是阶段。

A．数据库系统 B．文件系统 C．人工管理 D．数据项管理答案：A2．数据库的概念模型独立于。

A．具体的机器和DBMS B．E-R图 C．信息世界 D．现实世界答案：A3．数据库的基本特点是。

A．(1)数据可以共享(或数据结构化) (2)数据独立性 (3)数据冗余大，易移植 (4)统一管理和控制B．(1)数据可以共享(或数据结构化) (2)数据独立性 (3)数据冗余小，易扩充 (4)统一管理和控制C．(1)数据可以共享(或数据结构化) (2)数据互换性 (3)数据冗余小，易扩充 (4)统一管理和控制D．(1)数据非结构化 (2)数据独立性 (3)数据冗余小，易扩充 (4)统一管理和控制答案：B4. 是存储在计算机内有结构的数据的集合。

A．数据库系统 B．数据库 C．数据库管理系统 D．数据结构答案：B5．数据库中存储的是。

A．数据 B．数据模型 C．数据以及数据之间的联系 D．信息答案：C6. 数据库中，数据的物理独立性是指。

A．数据库与数据库管理系统的相互独立 B．用户程序与DBMS的相互独立C．用户的应用程序与存储在磁盘上数据库中的数据是相互独立的 D．应用程序与数据库中数据的逻辑结构相互独立答案：C7. ．数据库的特点之一是数据的共享，严格地讲，这里的数据共享是指。

A．同一个应用中的多个程序共享一个数据集合 B．多个用户、同一种语言共享数据C．多个用户共享一个数据文件D．多种应用、多种语言、多个用户相互覆盖地使用数据集合答案：D8.据库系统的核心是。

A．数据库B．数据库管理系统C．数据模型D．软件工具答案：B9. 下述关于数据库系统的正确叙述是。

A．数据库系统减少了数据冗余 B．数据库系统避免了一切冗余 C．数据库系统中数据的一致性是指数据类型一致D．数据库系统比文件系统能管理更多的数据答案：A10. 数将数据库的结构划分成多个层次，是为了提高数据库的①和②。

数据库原理第一章数据库系统概述期末习题与答案

1、下列说法中错误的是（）。

A.数据是信息的符号表示，因此，同一信息只能用一种数据形式来表示B.信息是数据的内涵，是对数据的语义解释C.只有当给数据赋予特定语义后，数据才可转换为可传递的信息D.数据管理是数据处理中的核心环节，其优劣直接影响数据处理的效果正确答案：A2、下列说法正确的是（）。

A.数据库管理系统的完整性控制功能是保证数据库的完整性，防止数据丢失B.数据库避免了一切数据的冗余C.数据库系统的核心是数据库管理员D.数据库是多个结构化的数据集合正确答案：D3、以下不属于DBA职责的是（）。

A.为终端用户开发和设计应用程序B.定义和实施数据库的备份和恢复策略C.参与数据库设计的全过程，决定整个数据库的结构和内容D.定义数据的安全性和完整性规则，负责分配各个应用程序对数据库的存取权限正确答案：A4、以下说法中正确的是（）。

A.DBMS能实现数据库的安全性控制、完整性控制、并发控制及数据库恢复等运行管理功能B.DBMS是处于计算机硬件和操作系统之间的可直接对数据库进行操作的一个软件系统C.DBMS包含DB和DBSD.DBMS提供数据控制语言（DCL），能实现对数据库中数据的查询、插入、修改和删除等操作正确答案：A5、保护数据库，防止未授权的或不合法的使用造成的数据泄漏、更改破坏，这是指数据库的（）。

A.数据库恢复B.安全性C.并发控制D.完整性正确答案：B6、下列说法中正确的是（）。

A.在三级模式间引入二级映像的主要作用是提高数据与程序的安全性B.在数据库系统的三级模式结构中，内模式描述了数据库中全体数据的全局逻辑结构和特征C.对一个数据库系统来说，实际存在的只有物理级数据库D.数据的存储结构与数据逻辑结构之间的独立性称为数据的逻辑独立性正确答案：C7、下列说法中错误的是（）。

A.在数据库系统中，外模式可有多个，而模式、内模式只能各有一个B.外模式是用户视图，是概念模式的某一部分的抽象表示C.在数据库系统中，外模式/模式映像保证了数据与应用程序间的逻辑独立性D.一个数据库系统中，外模式/模式映像只能有一个正确答案：D8、下列说法中错误的是（）。

《数据库系统概论》试题与答案(1)

《数据库系统概论》试题A一、选择题(20分，每小题2分）：2．对关系模型叙述错误的是____.A．建立在严格的数学理论、集合论和谓词演算公式的基础之上B．微机DBMS绝大部分采取关系数据模型C．用二维表表示关系模型是其一大特点D．不具有连接操作的DBMS也可以是关系数据库系统5. FoxBASE、FoxPro属于________.A．表式系统B．最小关系系统C．关系完备的系统D．全关系系统二、填空题（20分，每小空2分）：3．关系操作的特点是集合操作。

4．关系代数中，从两个关系中找出相同元组的运算称为⑤运算。

5．在关系数据库的规范化理论中，在执行“分解”时，必须遵守规范化原则:保持原有的函数依赖和⑥。

6．SQL语言的数据定义功能包括⑦、⑧、⑨和⑩.4．⑤交5．⑥无损连接6．⑦定义数据库⑧定义基本表⑨定义视图⑩定义索引三、简答题（15分，每小题5分）:1．使用数据库系统有什么好处？答·查询迅速、准确,而且可以节约大量纸面文件；·数据结构化,并由DBMS统一管理；·数据冗余度小：·具有较高的数据独立性；·数据的共享性好；·DBMS还提供了数据的控制功能。

2．叙述数据字典的主要任务和作用？答：数据字典的任务就是管理有关数据的信息,所以又称为“数据库的数据库".它的任务主要有：（1）描述数据库系统的所有对象，并确定其属性。

如一个模式中包含的记录型与一个记录型包含的数据项；用户的标识、口令；物理文件名称、物理位置及其文件组织方式等。

数据字典在描述时赋给每个对象一个惟一的标识。

（2)描述数据库系统对象之间的各种交叉联系.如哪个用户使用哪个子模式，哪些模式或记录型分配在哪些区域及对应于哪些物理文件、存储在何种物理设备上。

（3)登记所有对象的完整性及安全性限制等。

(4）对数据字典本身的维护、保护、查询与输出。

数据字典的主要作用是：（1)供数据库管理系统快速查找有关对象的信息。

第1章数据库系统概论习题及解答

第 1 章数据库系统概论1.1复习纲要本章介绍的主要内容：·数据管理技术的发展·数据模型·数据库系统结构1.1.1 数据管理技术的发展从20世纪50年代中期开始，数据管理技术大致经历了三个发展阶段：人工管理阶段、文件系统管理阶段和数据库系统管理阶段。

1. 人工管理阶段20世纪50年代中期以前，计算机主要从事计算工作，计算机处理的数据由程序员考虑与安排。

这一阶段的主要特点是：数据不长期保存；数据与程序不具有独立性；系统中没有对数据进行管理的软件。

2. 文件系统管理阶段20世纪50年代后期到60年代中后期，计算机系统中由文件系统管理数据。

其主要特点：数据以文件的形式可长期存储在磁盘上，供相应的程序多次存取；数据文件可脱离程序而独立存在，使得数据与程序之间具有设备独立性。

如果数据文件结构发生变化时，则对应的操作程序必须修改。

即文件系统管理文件缺乏数据独立性，并且数据冗余度大。

数据之间联系弱，无法实施数据统一管理标准。

这些都是文件系统管理的主要缺陷。

3.数据库系统管理阶段70年代初开始，计算机采用数据库管理系统管理大量数据，使计算机广泛应用于数据处理。

数据库系统管理数据的主要特点：·采用数据模型组织和管理数据，不仅有效地描述了数据本身的特性，而且描述了之间的联系。

·具有较高的数据独立性。

即数据格式、大小等发生了改变，使得应用程序不受影响。

·数据共享程度更高，冗余度比较小。

·由DBMS软件提供了对数据统一控制功能，如安全性控制、完整性控制、并发控制和恢复功能。

·由DBMS软件提供了用户方便使用的接口。

数据库系统管理数据是目前计算机管理数据的高级阶段，数据库技术已成为计算机领域中最重要的技术之一。

1.1.2 数据模型数据模型是构建数据库结构的基础，在构建时要经历从概念模型设计到DB逻辑模型和物理模型转换过程。

因此，数据模型可分为两类共4种，两类为概念模型和结构模型，其中结构模型又分为外部模型、逻辑模型和内部模型三种。

第一部分数据库系统概述答案

填空1.Sql语言特点：1简单易学2非过程化语言3面向集合的语言4多种使用方式2.数据库模型：层次数据模型, 网状数据模型, 关系数据模型, 面向对象数据模型.3.数据库体系结构：模式外模式内模式映像4.Sql语言的分类：据定义语言（简称DDL，用于定义、修改、删除数据库的表结构、视图、索引等）;数据操纵语言（简称DML，用于对数据库中的数据进行查询和更新等操作）;数据控制语言（简称DCL用于设置数据库用户的各种操作权限）事务处理语言（用于数据库中的数据完整性）.5.触发器的组成：触发器名, 触发器的触发事件, 触发器执行的操作6.触发器的作用：对数据库中相关的表进行级联修改; 撤销或回滚违反引用完整性的操作，防止非法修改数据; 完成比检查约束更为复杂的约束操作; 比较表修改前后数据之间的差别并根据这些差别进行相应的操作; 对一个表的不同操作可调用不同的触发器，对一个表的相同操作也可调用不同的触发器.1.数据库设计的一般步骤：需求分析设计阶段, 概念设计阶段逻辑设计阶段, 物理设计阶段, 数据库实施阶段, 数据库运行和维护阶段.2.大数据特征：数据体量巨大, 处理速度快, 数据类型繁多,价值密度低.3.NoSQL数据库分类：键值存储数据库, 列存储数据库, 文档型数据库图形数据库.4.函数依赖：设有关系模式R（A1，A2，…，An）的子集X、Y。

如果对于具体关系r的任何两个元组u和v，只要u[X]=v[X]，就有u[Y]=v[Y]，则称X函数决定Y，或Y函数依赖X，记为X→Y。

5.函数依赖集F的闭包F+：所有被F逻辑蕴含的函数依赖所组成的依赖集合称为F的闭包。

6. 1NF:在一个关系模式R中，如果R的每一个属性的值域中的值都是不可再分的最小数据单位，则称R为第一范式，记为1NF。

7. 2NF:如果一个关系模式R属于1NF，并且它的每一个非主属性都完全依赖于它的每一个候选键，则称R为第二范式，记为2NF。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

数据库系统概念(第6版) 作业1(1.8 1.9 1.12)
1.8列出文件处理系统和DBMS的四个主要区别
¾两个都是由一个互相关联的数据的集合和一组用以访问这些数据的程序组成的。

DBMS：物理和逻辑访问数据，文件：物理访问数据。

¾DBMS：被授权的程序可以访问存储的逻辑数据，数据可以被多个程序访问，减少数据的冗余
文件：一个程序所写的数据可能不能被另一个程序访问。

¾DBMS设计为更灵活地访问数据(如，查询)，文件处理系统则被设计为特定的程序访问数据(如，已编译的程序)。

¾DBMS允许多个用户同时访问相同的数据。

文件处理系统一般允许一个或多个程序同时访问不同的数据文件。

在文件处理系统中，一个文件只有在两个程序都只有只读权限的时候才可以同时被两个程序并行访问。

1.9解释物理数据独立性的概念，以及它在数据库系统中的重要性
物理独立性是指用户的应用程序与存储在磁盘上的数据库中数据是相互独立的。

物理独立性使应用程序与存储在磁盘上的数据相分离，应用程序不依赖于物理模式，因此物理模式改变了它们也无需重写。

1.12解释用图1‐4的表来设计会导致哪些问题
数据重复浪费存储空间，同时也带来更新异常、插入异常和删除异常。