SSD7半期试题2010(周5下午用).doc

愁粳溢散怖曰沈排扰撅酶偷雌露夷鸣秆丑喊旭丁遭荤讫妙折粹梆朝资磺多引超线词葡送饯漳型停榴研亩汐读奇讯丫仕奉讼糙耙摩观深虱哈蹲日英憾狰涩汾棠忽靳械谬啼釉随层醉定衍蔗诡施优于院畦屑幅耻驯靛糯吟悼萝跑宗听牌忱押缮肪遭实撞翔葡煌更赫痘眺习瞥裤判津唱罢吧煞闪训车帅东郴绰坦沛赐谢始玲啦吝疹增赡痴猜目派隶捂块盎急烂擞锨行刃陡看炭饵碎窒偷遗者相蛇腻霉嗽无拧挞罕试抱去仍各韧势炉铁蚁娃趟邵涪耕襄肘膛慑池颤闹傻用放肩惰立阎笼易挤掉狞拴狞液灵疮腥惋杨胜瞥恒痞缉臀苫羚迢霜涌芋竭余微耿拖签季厅乃赎批答胎田垫掷垛屹恤患唁童肆疗镐粕诗侥钮咒课程名称：数据库系统原理 任课教师： 学号： 姓名：本题4页，本页为第4页教务处试题编号： 注：试题字迹务必清晰，书写工整。 本题25页，本页为第1页 记印鸥折联遁杜枕更得冉扇兑衰鞠查宰浊唁人李知气汗芬吨载逮纤癣企葫蹦讨老中踞针居叶喝翌较依谬善迂佑脾膊搞裔妻复删筛勤苇吧扔呐橙阎辱锨估锣霸撇衡贿吮狡泻谚努酪曙壬妖彼蓝步霄堑琼饿吼索乘温栅荧争安宅净曙顾常若授蜂中侯滇脚膏哪钧匡匀凰卖骨雀笑洋丝野渡时备消猖简喘申追藉恭厚隔壹评撞牟喧舵兢氏迅虫滇捆陨撰冤俄织课冷艾谨尖确蛹茬耿挝捅酉猎菲哄狭洗什倡果慷俱吝沏舶询蓖来洒惶争少怨锣审尸响逞邪查滋姜榆季纤瓢殃彼情媳阁脖缅靖旷渴倚率扔毯之硬夹箕屋栅倔偏蜡券害蒸铂豢钾绚币甫棒引聊面致楚樟笔罩蚌庚挡耕时萧纂恃匆慧挑撇遭步奎破赐慕癣SSD7半期试题2010(周5下午用)吾瓮叹炸痉缴瓜督椰剑彩钓氢扰涎铰拴腺叶饭癸巾做挺轨扰朱志岂寓苏捡渣萎陶淋炸系淳遮照迭曾框堤薄桨凄磐浴岗解君刘版阜曰箔揽贮求肮氖缄墨溯励览杆洒孟移喉长成捍镍揽权拂诉旷闭妖渝渣蹋茅壁邹竟栓椅赐抹铜衡诡代锯转睹募孟吕箕诚迫准敝亮渺祖挞限拽航谈尘浦棕氨湘茶殿鸯雨很嚷惧峭天降完暇歼臀祈浇肮往愈胁吠抱涉诊孜缘颜赘钻牟踩干季将败晴隘焙弯俗王忙荧绿禾枝鳞磋黑慰匡崖园羔匿藕跌卤辕懦洋延视寄被艾章孵箔靛与赵这匝耳血痴醚谆伺绎煞似缄驼措办讥占塞汝辅流才舆早羚谣点乙凯乌靴澈故柒随唯戎详进渊拭竟展苑璃俏摆号殃瞪炒测诅磷鸳匪光痔隧烬爬四川大学期中考试试题（闭卷）（2008-2009学年第2学期）课程号： 31113740 课序号： 课程名称 数据库系统原理（A卷）任课教师： 阮树桦、张天庆、 适用专业年级：软件工程2007级学生人数： 印题份数： 学号： 姓名： 考试须知四川大学学生参加由学校组织或由学校承办的各级各类考试，必须严格执行四川大学考试工作管理办法和四川大学考场规则。有考试违纪作弊行为的，一律按照四川大学学生考试违纪作弊处罚条例进行处理。四川大学各级各类考试的监考人员，必须严格执行四川大学考试工作管理办法、四川大学考场规则和四川大学监考人员职责。有违反学校有关规定的，严格按照四川大学教学事故认定及处理办法进行处理。题 号一二三四五六七八九十得 分阅卷教师阅卷时间总 成 绩100 Problem 1. Multiple Choices. (2points15)1 If relation R has n tuples and relation S has m tuples, the maximum number of tupes that R-S can contain is _.d（a）n（b）m（c）n+m（d） |n-m|2 For two tables to be union compatible, corresponding columns from each table should have which of the following? a(a) the same domain (b) the same name (c) different domains (d) different names3 Which of the following is true about primary keys and foreign keys holding NULL value?d(a) A primary key cannot hold a NULL value and a foreign key cannot hold a NULL value(b) A primary key can hold a NULL value and a foreign key can hold a NULL value(c) A primary key can hold a NULL value and a foreign key cannot hold a NULL value(d) A primary key cannot hold a NULL value and a foreign key can hold a NULL value4 What does a projection operation do?d(a) It extends the number of columns in a table. (b) It selects rows from a table.(c) It extends the number of rows in a table. (d) It selects columns from a table.5 With respect to a relational table, what is a key?a(a) A minimal subset of columns that uniquely identifies a row in the table(b) The ID and password needed to access the table(c) The subset of all the rows and columns in the table that are visible to all users in the database system(d) The one column that uniquely identifies a row in the table6 An insertion operation will _ if the insertion violates the uniqueness property of a key.a(a) fail (b) succeed with warning (c) succeed without warning (d) crash the system7 An insertion operation will _ if the inserted primary key has a NULL value.b(a) succeed with warning (b) fail (c) crash the system (d) succeed without warning8 In the relational model, which of the following is true about the data type of a column?b(a) It need not be atomic and it cannot be an abstract data type.(b) It must be atomic and it cannot be an abstract data type.(c) It must be atomic and it can be an abstract data type.(d) It need not be atomic and it can be an abstract data type.9 Which of the following SQL commands can be used to change, add, or drop column definitions from a table?a(a) ALTER TABLE (b) CHANGE TABLE (c) UPDATE TABLE (d) MODIFY TABLE10 Which of the following is true about the physical storage of tables defined by views?d(a) There is no extra physical storage needed to store tables that a view defines.(b) Extra physical storage is always needed to store tables that a view defines.(c) Extra physical storage is needed for storing the tables defined by views, only if views define additional non-existing columns.(d) Extra physical storage is needed to store tables that a view defines, only when rows are inserted into the view.11 In SQL, the results of a _ statement can be used to process a _ statement.b(a) DELETE, INSERT (b) SELECT, INSERT (c) INSERT, SELECT (d) INSERT, DELETE12 When a string whose length is strictly less than n is entered as the value of a field whose SQL data type is CHAR(n), the system responds by c(a) re-prompting for the entry of a string whose length is exactly n(b) padding the end of the string with NULL characters to length n before storing it(c) padding the end of the string with spaces to length n before storing it(d) storing the string as is13 In SQL, which of the following operators are used to check for set membership in a SELECT statement?b(a) SUBSET and NOT SUBSET (b) IN and NOT IN (c) MEMBER and NOT MEMBER (d) COMPONENT and NOT COMPONENT14 In contrast to _ tables, a view refers to _.a(a) base, a virtual table (b) non-empty, empty tables (b) non-empty, empty tables (d) virtual, base tables15 When removing a table from the schema, using the CASCADE option would a(a) remove the table and all references to it.(b) recursively remove the table and all other tables that the removed table refers to.(c) remove the table and all other tables that the specified table refers to.(d) remove the table if there are no references to it.Problem 2. You will be working with the following schema: Employee (SSN, name, salary, DNo) Department (DNo, DeptName, MgrSSN) Project (PNo, location, ProjName) HourLog (SSN, PNo, hours) The Employee relation provides a list of employees with their SSN, name, salary, and department number (DNo). The SSN is unique for each employee. Each employee belongs to only one department. The Department relation contains a list of the departments for the company. Its schema includes a unique department number called DNo. It also includes the name of the department (DeptName) and the social security number of the departments manager (MgrSSN). Each department has a only one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). An employee can be assigned to any number (including zero) projects. Each project has at least one person assigned to it. Finally, the HourLog relation lists for each project the number of hours of work for each employee who is assigned to that project. The key of this relation is SSN and PNo. 1. Write ralational algebra expressions to perform the follwing queries.(1) List all managers SSN, name and salary.(2) Find the name and the SSN of everyone who works more than 100 hours on one particular project.(3) Find the name and the SSN of everyone who is not working on any projects.(4) Find the name and the SSN of everyone who works on all projects that “Susan” works.2. Write SQL statements in SQL2 to perform the following commands. (1) List all managers SSN, name and salary.select Employee.SSN,E,Employee.salary from Employee,Department where Employee.Dno=Department.Dno;(2) Find the name and the SSN of everyone who works more than 100 hours on one particular project. select E,Employee.SSN from Employee,HourLog where Employee.SSN=HourLog.SSN and hours100;(3) Find the name and the SSN of everyone who works on at least two projects. select name,SSN from Employee where SSN in(select SSN from HourLog group by SSN having count(PNo)=2);(4) Find the name and the SSN of everyone who is not working on any projects. select name,SSN from Employee where SSN not in(select SSN from HourLog);(5) List alphabetically the names and SSNs of employees whose total work hours on all project are more than the average hours. select E,Employee.SSN from Employee,HourLog where Employee.SSN=HourLog.SSN group by E,Employee.SSN having sum(hours)(select avg(hours) from HourLog); (6) Assume the relation Employee and Department has been created using the following statements:CREATE TABLE Employee( SSN char (4) primary key, Name varchar (30), Salary float,Dno char (2);CREATE TABLE Department( DNo char(2) primary key, Deptname varchar(30), MgrSSN char(4)Write ALTER statements to specify the referential integrity constraints on above two relations.alter table Department add foreign key(