《数据库期末考试题.pdf》由会员分享，可在线阅读，更多相关《数据库期末考试题.pdf（50页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1.DBMS has many advantages.Then what are two disadvantages of database system?Answer:Two disadvantages associated with database systems are listedbelow.a.Setup of the database system requires more knowledge,money,skills,and time.b.b.The complexity of the database may result in poor performance.2List
2、 five responsibilities of a database management system.Ansers:a.interaction with the file manager.b.integrity enforcement.c.security enforcement.d.backup and recovery.e.concurrency control.3.What are five main functions of a database administrator?Answer:Five main functions of a database administrat
3、or are:,Tb create the scheme definition,To define the storage structure and access methods,Tb modify the scheme and/or physical organization when necessary,To grant authorization for data access,To specify integrity constraints4.Explain the distinctions among the terms primary key,candidate key,and
4、superkey.Answer:A superkey is a set of one or more attributes that,takencollectively,al-lows us to identify uniquely an entity in the entity set.Asuperkey may contain extraneous attributes.If K is a superkey,then so isany superset of K.Asuperkey for which no proper subset is also asuperkey is called
5、 a candidate key.It is possible that several distinct setsof attributes could serve as candidate keys.The primary key is one of thecandidate keys that is chosen by the database designer as the principalmeans of identifying entities within an entity set6.What is logical data independence and why is i
6、t important?Answer:Logical data independence means that users are shielded fromchanges in the logical structure of the data,i.e.,changes in the choice ofrelations to be stored.For example,if a relation Students(sid,sname,gpa)is replaced by Studentnames(sid,sname)and Studentgpas(sid,gpa)forsome reaso
7、n,application programs that operate on the Students relationcan be shielded from this change by defining a view Stu-dents(sid,sname,gpa)(as the natural join of Studentnames and Studentgpas).Thus,application programs that refer to Students need not be changed when therelation Students is replaced by
8、the other two relations.The only changeis that instead of storing Students tuples,these tuples are computed asneeded by using the view definition;this is transparent to the applicationprogram.7.Which of the following plays an important role in representing in formation about the real world in a data
9、base?Explain briefly.1.The data definition language.2.The data manipulation language.3.The buffer manager.4.The data model.8 Consider the following information about a university database:Professors have an SSN,a name,an age,a rank,and a research specialty.Projects have a project number,a sponsor na
10、me(e.g.,NSF),a startingdate,an ending date,and a budget.Graduate students have an SSN,a name,an age,and a degree program(e.g.,M.S.or Ph.D.).Each project is managed by one professor(known as the projectsprincipal investigator).Each project is worked on by one or more professors(known as theprojects c
11、o-investigators).Professors can manage and/or work on multiple projects.Each project is worked on by one or more graduate students(known asthe projects research assistants).When graduate students work on a project,a professor must supervisetheir work on the project.Graduate students can work on mult
12、ipleprojects,in which case they will have a(potentially different)supervisorfor each one.Departments have a department number,a department name,and a mainoffice.Departments have a professor(known as the chairman)who runs thedepartment.Professors work in one or more departments,and for each departmen
13、t thatthey work in,a time percentage is associated with their job.Graduate students have one major department in which they are workingon their degree.Each graduate student has another,more senior graduate student(knownas a student advisor)who advises him or her on what courses to take.Design and dr
14、aw an ER diagram that captures the information about theuniversity.Use only the basic ER model here;that is,entities,relationships,andattributes.Besure to indicate any key and participation constraints.F-x u rc2,7E RD i a g n u n f b rE x e rc ise2 W9.Consider the university database from Exercise 8
15、 and the ER diagramyou designed.Write SQL statements to create the corresponding relationsand capture as many of the constraints as possible.If you cannot capturesome constraints,explain why.Answer The following SQL statements create the correspondingrelations.1.CREATE TABLE Professors(profssn CHAR(
16、IO),name CHAR(64),age INTEGER,rank INTEGER,speciality CHAR(64),PRIMARY KEY(prof ssn)2.CREATE TABLE Depts(dno INTEGER,dname CHAR(64),office CHAR(IO),PRIMARY KEY(dno)3.CREATE TABLE Runs(dno INTEGER,prof ssn CHAR(IO),PRIMARY KEY(dno,prof ssn),FOREIGN KEY(prof ssn)REFERENCES Professors,FOREIGN KEY(dno)R
17、EFERENCES Depts)4.CREATE TABLE Work Dept(dno INTEGER,prof ssn CHAR(IO),pc time INTEGER,PRIMARY KEY(dno,prof ssn),FOREIGN KEY(prof ssn)REFERENCES Professors,FOREIGN KEY(dno)REFERENCES Depts)Observe that we would need check constraints or assertions in SQL toenforce the rule that Professors work in at
18、 least one department.5.CREATE TABLE Project(pid INTEGER,sponsor CHAR(32),start date DATE,end date DATE,budget FLOAT,PRIMARY KEY(pid)6.CREATE TABLE Graduates(grad ssn CHAR(IO),age INTEGER,name CHAR(64),deg prog CHAR(32),major INTEGER,PRIMARY KEY(grad ssn),FOREIGN KEY(major)REFERENCES Depts)Note that
19、 the Major table is not necessary since each Graduate has onlyone majorand so this can be an attribute in the Graduates table.7.CREATE TABLE Advisor(senior ssn CHAR(IO),grad ssn CHAR(IO),PRIMARY KEY(senior ssn,grad ssn),FOREIGN KEY(senior ssn)REFERENCES Graduates(grad ssn),FOREIGN KEY(grad ssn)REFER
20、ENCES Graduates)8.CREATE TABLE Manages(pid INTEGER,prof ssn CHAR(IO),PRIMARY KEY(pid,prof ssn),FOREIGN KEY(prof ssn)REFERENCES Professors,FOREIGN KEY(pid)REFERENCES Projects)9.CREATE TABLE Work In(pid INTEGER,profssn CHAR(IO),PRIMARY KEY(pid,profssn),FOREIGN KEY(profssn)REFERENCES Professors,FOREIGN
21、 KEY(pid)REFERENCES Projects)Observe that we cannot enforce the participation constraint for Projects intheWork In table without check constraints or assertions in SQL.10.CREATE TABLE Supervises(profssn CHAR(10),grad ssn CHAR(10),pid INTEGER,PRIMARY KEY(prof ssn,grad ssn,pid),FOREIGN KEY(profssn)REF
22、ERENCES Professors,FOREIGN KEY(grad ssn)REFERENCES Graduates,FOREIGN KEY(pid)REFERENCES Projects)Note that we do not need an explicit table for the Work Proj relation sinceeverytime a Graduate works on a Project,he or she must have aSupervisor.10.Consider the following relations:Student(snum:integer
23、,sname:string,major:string,level:string,age:integer)Class(name:string,meets at:string,room:string,fid:integer)Enrolled(snum:integer,cname:string)Faculty(fid:integer,fhame:string,deptid:integer)The meaning of these relations is straightforward;for example,Enrolledhas one record per student-class pair
24、 such that the student is enrolled inthe class.Write the following queries in SQL.No duplicates should be printed inany of the answers.1.Find the names of all Juniors(level=JR)who are enrolled in a classtaught by I.leach.2.Find the age of the oldest student who is either a History major orenrolled i
25、n a course taught by I.Teach.3.Find the names of all classes that either meet in room RI28 or havefive or more students enrolled.4.Find the names of all students who are enrolled in two classes thatmeet at the same time.5.Find the names of faculty members who teach in every room in whichsome class i
26、s taught.The answers are given below:1.SELECT DISTINCT S.SnameFROM Student S,Class C,Enrolled E,Faculty FWHERE S.snum=E.snum AND E.cname=C.name AND 6d=F.fidANDF.fname=I.Teach AND S.level=JR2.SELECT MAX(S.age)FROM Student SWHERE(S.major=History)OR S.snum IN(SELECT E.snumFROM Class C,Enrolled E,Facult
27、y FWHERE E.cname=C.name AND C.fid=F.fidAND F.fname=工Teach)3.SELECT C.nameFROM Class CWHEREC.room=R128OR C.name IN(SELECT E.cnameFROM Enrolled EGROUP BY E.cnameHAVING COUNT(*)=5)4.SELECT DISTINCT S.snameFROM Student SWHERE S.snum IN(SELECTEl.snumFROM Enrolled El,Enrolled E2,Class Cl,Class C2WHERE El.
28、snum=E2.snum AND Eame EameAND Eame=Cl.nameAND Eame=C2.name AND Cl.meets at=C2.meets at)5.SELECT DISTINCT EfnameFROM Faculty FWHERE NOT EXISTS(SELECT*FROM Class C)EXCEPT(SELECTCI.roomFROM Class ClWHERE C1.fid=F.fid)l.List four signficant differences between a file-processing system and a DBMS.Answer:
29、Some main differences between a database management system anda file-processing system are:Both systems contain a collection of data and a set of programswhich access that data.Adatabase management system coordinates both the physical and the logical access to the data,whereas afile-processing syste
30、m coordinates only the physical access.A databasemanagement system reduces the amount of data duplication byensuring that a physical piece of data is available to all programs authorizedto have access to it,whereas data written by one programinfiie-processingsystem may not be readable by another pro
31、gram.A database management system is designed to allowflexible access to data(i.e.,queries),whereas afile-processing system is designed to allow predetermined access to data(i.e.,compiled programs).A database management system is designed to coordinate multiple usersaccessing the same data at the sa
32、me time.Afile-processing systemis usuallydesigned to allow one or more programs to access different dafiies atthe same time.In afile-processing system,afile can be accessed by twoprograms concuiTently only if both programs have read-only access to thefile.2Why would you choose a database system inst
33、ead of simply storing datain operating systemfiles?When would it make sense not to use a database system?Answer 1.1 A database is an integrated collection of data,usually so large that ithas to be stored on secondary storage devices such as disks or tapes.This data canbe maintained as a collection o
34、f operating systeifiles,or stored in a DBMS(databasemanagement system).The advantages of using a DBMS are:Data independence and efficient access.Database application programs are independent of the details of data representation and storage.The conceptual andexternal schemas provide independence fro
35、m physical storage decisions and logicaldesign decisions respectively.In addition,a DBMS provides efficient storage andretrieval mechanisms,including support for very larries,index structures andquery optimization.Reduced application development time.Since the DBMS provides several important functio
36、ns required by applications,such as concurrency control and crashrecovery,high level query facilities,etc.,only application-specific code needs tobe written.Even this is facilitated by suites of application development toolsavailable from vendors for many database management systems.Data integrity a
37、nd security.The view mechanism and the authorization facilitiesof a DBMS provide a powerful access control mechanism.Further,updates to thedata that violate the semantics of the data can be detected and rejected by theDBMS if users specify the appropriate integrity constraints.Data administration.By
38、 providing a common umbrella for a large collection ofdata that is shared by several users,a DBMS facilitates maintenance and dataadministration tasks.A good DBA can effectively shield end-users from the choresof fine-tuning the data representation,periodic back-ups etc.Concurrent access and crash r
39、ecovery.ADBMS supports the notion of a transaction,which is conceptually a single users sequential program.Users can writetransactions as if their programs were running in isolation against the database.The DBMS executes the actions of transactions in an interleaved fashion to obtaingood performance
40、,but schedules them in such a way as to ensure that cflictingoperations are not permitted to proceed concurrently.Further,the DBMS maintains a continuous log of the changes to the data,and if there is a system crash,it can restore the database to a transaction-consistent state.That is,the actionsof
41、incomplete transactions are undone,so that the database state ftects only theactions of completed transactions.Thus,if each complete transaction,executingalone,maintains the consistency criteria,then the database state after recoveryfrom a crash is consistent.If these advantages are not important fo
42、r the application at hand,using a collection offiles may be a better solution because of the increased cost and overhead of purchasing andmaintaining a DBMS.3.What is logical data independence and why is it important?Answer 1.2 Logical data independence means that users are shielded from changes int
43、he logical structure of the data,i.e.,changes in the choice of relations to be stored.For example,if a relation Students(sid,sname,gpa)is replaced by Studentnames(sid,sname)and Studentgpas(sid,gpa)for some reason,application programs that operateon the Students relation can be shielded from this cha
44、nge by(fciing a view Stu-dents(sid,sname,gpa)(as the natural join of Studentnames and Studentgpas).Thus,application programs that refer to Students need not be changed when the relation Students isreplaced by the other two relations.The only change is that instead of storing Students tuples,these tu
45、ples are computed as needed by using the yficntiafe this is transparent to theapplication program.Exercise 1.5 What are the responsibilities of a DBA?Answer 1.5 The DBA is responsible for:Designing the logical and physical schemas,as well as widely-used portions of theexternal schema.Security and au
46、thorization.Data availability and recovery from failures.Database tuning:The DBA is responsible for evolving the database,in particularthe conceptual and physical schemas,to ensure adequate performance as userrequirements change.Answer 1.7 Let us discuss the choices in turn.The data definition langu
47、age is important in representing information because itis used to describe external and logical schemas.The data manipulation language is used to access and update data;it is notimportant for representing the data.(Of course,the data manipulation languagemust be aware of how data is represented,and
48、itects this in the constructs thatit supports.)The buffer manager is not very important for representation because it bringsarbitrary disk pages into main memory,independent of any data representation.The data model is fundamental to representing information.The data modeldetermines what data repres
49、entation mechanisms are supported by the DBMS.The data definition language is just the specific set of language constructs availableto describe an actual applications data in terms of the data model.Exercise 2.4 A company database needs to store information about employeefiecteyitissn,with salary an
50、d phone as attributes),departments(ideiftcd by dno,with dname and bud get asattributes),and children of employees(with name and age as attributes).Employees work indepartments;each department is managed by an employee;a child must be idenfied uniquely byname when the parent(who is an employee;assume