下载文档原格式
8.1 Excel数据库简介8.1.1 Excel数据库概述Excel提供了一种极其简易的数据库管理功能，运用它可以进行简单的数据的组织管理工作，如在工作表中输入一个单位的财务数据或人事档案，并对这些数据进行排序、筛选、分类、汇总、查询等操作。
但是，Excel并不能取代真正意义上的数据库系统，也不能用于建立较为复杂的数据库信息系统。
在数据量很大，数据的种类较多，数据的关系比较复杂时，用户用它来建立数据库管理信息系统时，很难处理好数据之间的各种关系。
但这并不表示不能用Excel进行企业的数据管理，毕竟，复杂的大型数据库信息系统是较少数的。
在数据量不大（一个工作表最多65 536行），数据种类不多，企业规模不大时，用Excel进行数据的组织和管理工作会给我们的工作带来许多方便，简化我们的工作，提高工作效率。
同专业数据库系统相比较，Excel操作简单，数据的表示非常直观，与我们平常工作中所见的表格没有什么两样，任何人只需通过简单的学习就会使用，不像数据库系统要经过专业的学习才会运用。
而且，Excel具有相当强大的数据计算功能，提供了许多有用的函数和数据分析工具，如财务函数、统计函数、图表分析等，这些功能恰好是某些专业数据库系统较弱的地方。
用户用它进行小单位的财务管理，财务分析，资产管理等非常方便。
例如，人们可以直接调用Excel的财务函数进行投资分析、资产折旧、债券分析、工资计算等。
8.1.2 认识Excel数据库在Excel中，一个数据库是一张特殊的工作表，这种工作表和数据清单相似，有关数据清单的内容在第5章已有较为详细的讨论，请读者参考。
图8.1就是一个Excel数据库，实际它也是一个数据清单。
图8.1 数据库示例如图8.1所示的Excel数据库具有以下特征：（1）数据库是一个数据区域，该区域的首行是一些描述性的词组，它能够表明相应列的数据性质及数据类型。
在数据库中，这一行称为数据库的结构名，每一列的第一行称为属性或列标，也称为字段。
《数据库系统原理》教学大纲一、课程名称：数据库系统原理Principles of Database Systems二、课程编号：0601033三、学分学时：3学分/48学时四、使用教材：王能斌：《数据库系统教程（上册）》，电子工业出版社（始终选用最新版本）五、课程属性：专业课/必修六、教学对象：计算机科学与技术专业本科生七、开课单位：计算机及信息工程学院八、先修课程：算法与数据结构、编译原理、操作系统九、教学目标：通过本课程的学习，使学生建立现代数据管理技术的基本概念，掌握关系数据库系统及关系数据库设计的基本原理、方法与技术，奠定数据库理论研究和应用开发的必备基础。
十、课程要求：⑴教师在课堂上精心讲授，学生课后认真消化教材和补充素材内容，认真完成作业和课程设计实验，通过课堂讲解、课堂讨论、自主探究、实践训练等相互联系和互补的教学环节来提高教学效果；⑵遵循“研究性教学”理念，在本课程网站上提供丰富教学资源（精选的英文文献、国内外大学同类课程链接、数据库工业界和学术界资源等），引导和帮助学生去主动学习、扩充学习和发展个性。
十一、教学内容：本课程主要由以下内容组成：第一章数据库系统引论（4学时）⏹知识要点：数据管理技术的发展；数据库系统；数据模型和数据模式；数据库的生存周期。
⏹重点难点：数据模型的概念；数据模式的概念；多级数据模型/模式。
⏹教学方法：讲授、自学、作业、讨论。
第二章数据模型（6学时）⏹知识要点：关系数据模型；E/R数据模型；后关系数据模型。
⏹重点难点：关系数据模型三要素（结构、约束与操作）；键、超键、主键、外键；E/R数据模型的构造子及语义。
⏹教学方法：讲授、自学、作业、讨论。
第三章SQL语言（6学时）⏹知识要点：数据库的用户接口；SQL语言概况；SQL数据定义语言；SQL查询语言；SQL数据操纵语言；SQL视图；嵌入式SQL和SQL过程化扩充（简介）。
⏹重点难点：数据库语言的特点；条件查询；连接查询；嵌套查询；视图定义。
数据库系统基础教程答案ch8Section 1Exercise 8.1.1a)CREATE VIEW RichExec ASSELECT * FROM MovieExec WHERE netWorth >= 10000000;b)CREATE VIEW StudioPres (name, address, cert#) ASSELECT , MovieExec.address, MovieExec.cert# FROM MovieExec, Studio WHERE MovieExec.cert# = Studio.presC#;c)CREATE VIEW ExecutiveStar (name, address, gender, birthdate, cert#, netWorth) AS SELECT , star.address, star.gender, star.birthdate, exec.cert#, WorthFROM MovieStar star, MovieExec exec WHERE = ANDstar.address = exec.address;Exercise 8.1.2a)SELECT name from ExecutiveStar WHERE gender = ‘f’;b)SELECT from RichExec, StudioPres where = ;c)SELECT from ExecutiveStar, StudioPresWHERE Worth >= 50000000 ANDStudioPres.cert# = RichExec.cert#;Section 2Exercise 8.2.1The views RichExec and StudioPres are updatable; however, the StudioPres view needs to be created with a subquery.CREATE VIEW StudioPres (name, address, cert#) ASSELECT , MovieExec.address, MovieExec.cert# FROM MovieExecWHERE MovieExec.cert# IN (SELECT presCt# from Studio);Exercise 8.2.2a) Yes, the view is updatable.CREATE TRIGGER DisneyComedyInsertINSTEAD OF INSERT ON DisneyComediesREFERENCING NEW ROW AS NewRowFOR EACH ROWINSERT INTO Movies(title, year, length, studioName, genre)VALUES(NewRow.title, NewRow.year, NewYear.length, ‘Disney’, ‘comedy’);c)CREATE TRIGGER DisneyComedyUpdateINSTEAD OF UPDATE ON DisneyComediesREFERENCING NEW ROW AS NewRowFOR EACH ROWUPDATE Movies SET length NewRow.lengthWHERE title = NewRow.title AND year = NEWROW.year ANDstudionName = ‘Disney’ AND genre = ‘comedy’;Exercise 8.2.3a) No, the view is not updatable since it is constructed from two different relations.b)CREATE TRIGGER NewPCInsertINSTEAD OF INSERT ON NewPCREFERENCING NEW ROW AS NewRowFOR EACH ROW(INSERT INTO Product VALUES(NewRow.maker, NewRow.model, ‘pc’))(INSERT INTO PC VALUES(NewRow.model, NewRow.speed, NewRow.ram, NewRow.hd, NewRow.price));c)CREATE TRIGGER NewPCUpdateINSTEAD OF UPDATE ON NewPCREFERENCING NEW ROW AS NewRowFOR EACH ROWUPDATE PC SET price = NewPC.price where model = NewPC.model;d)CREATE TRIGGER NewPCDeleteINSTEAD OF DELETE ON NeePCREFERENCING OLD ROW AS OldRowFOR EACH ROW(DELETE FROM Product WHERE model = OldRow.model)(DELETE FROM PC where model = OldRow.model);Section 3Exercise 8.3.1a)CREATE INDEX NameIndex on Studio(name);b)CREATE INDEX AddressIndex on MovieExec(address);c)CREATE INDEX GenreIndex on Movies(genre, length);Section 4Exercise 8.4.1Exercise 8.4.2Q1 = SELECT * FROM Ships WHERE name = n;Q2 = SELECT * FROM Ships WHERE class = c;Q3 = SELECT * FROM Ships WHERE launched = y;I = InsertsIndexesNone Name Class Launched Name & Name & Class & ThreeSection 5Exercise 8.5.1Updates to movies that involves title or yearUPDATE MovieProd SET title = ‘newTitle’ where title=’oldTitle’ AND year = oldYear;UPDATE MovieProd SET year = newYear where title=’oldYitle’ AND year = oldYear; Update to MovieExec involving cert#DELETE FROM MovieProdWHERE (title, year) IN (SELECT title, yearFROM Movies, MovieExecWHERE cert# = oldCert# AND cert# = producerC#);INSERT INTO MovieProdSELECT title, year, nameFROM Movies, MovieExecWHERE cert# = newCert# AND cert# = producerC#;数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8 Exercise 8.5.2Insertions, deletions, and updates to the base tables Product and PC would require a modification of the materialized view.Insertions into Product with type equal to ‘pc’:INSERT INTO NewPCSELECT maker, model, speed, ram, hd, price FROM Product, PC WHEREProduct.model = newModel and Product.model = PC.model;Insertions into PC:INSERT INTO NewPCSELECT maker, ‘newModel’, ‘newSpeed’, ‘newRam’, ‘newHd’, ‘newPrice’FROM Product WHERE model = ‘newModel’;Deletions from Product with type equal to ‘pc’:DELETE FROM NewPC WHERE maker = ‘deletedMaker’ AND model=’deletedModel’; Deletions from PC:DELETE FROM NewPC WHERE m odel = ‘deletedModel’;Updates to PC:Update NewPC SET speed=PC.speed, ram=PC.ram, hd=PC.hd, price=PC.price FROMPC where model=pc.model;Update to the attribute ‘model’ needs to be treated as a delete and an insert.Updates to Product:Any changes to a Product tuple whose type is ‘pc’ need to be treated as a delete or aninsert, or both.Exercise 8.5.3Modifications to the base tables that would require a modification to the materialized view: inserts and deletes from Ships, deletes from class, updates to a Class’ displacement.Deletions from Ship:UPDATE ShipStats SETdisplacement=((displacement * count) –(SELECT displacementFROM ClasssesWHERE class = ‘DeletedShipClass’)) / (count – 1),count = count – 1WHEREcountry = (SELECT country FROM C lasses WHERE class=’DeletedShipClass’); Insertions into Ship:Update ShipStat SETdisplacement=((displacement*count) +(SELECT displacement FROM ClassesWHERE class=’InsertedShipClass’)) / (count + 1),count = count + 1WHEREcountry = (SELECT c ountry FROM Classes WHERE classes=’InsertedShipClass); Deletes from Classes:NumRowsDeleted = SELECT count(*) FROM ships WHERE class = ‘DeletedClass’; UPDATE ShipStats SETdisplacement = (displacement * count) - (DeletedClassDisplacement *NumRowsDeleted)) / (count – NumRowsDeleted),count = count – NumRowsDeletedWHERE country = ‘DeletedClassCountry’;Update to a Class’ displacement:N = SELECT count(*) FROM Ships where class = ‘UpdatedClass’;UPDATE ShipsStat SETdisplacement = ((displacement * count) + ((oldDisplacement – newDisplacement) * N))/countWHEREc ountry = ‘UpdatedClassCountry’;Exercise 8.5.4Queries that can be rewritten with the materialized view:Names of stars of movies produced by a certain producerSELECT starNameFROM StarsIn, Movies, MovieExecWHERE movieTitle = title AND movieYear = year AND producerC# = cert# AND name = ‘Max Bialystock’;Movies produced by a certain producerSELECT title, yearFROM Movies, MovieExecWhere producerC# = cert# AND name = ‘George Lucas’;Names of producers that a certain star has worked withSELECT nameFROM Movies, MovieExec, StarsInWhere producerC#=cert# AND title=movieTitle AND year=movieYear AND starName=’Carrie Fisher’;The number of movies produced by given producerSELECT count(*)FROM Movies, MovieExecWHER E producerC#=cert# AND name = ‘George Lucas‘;Names of producers who also starred in their own moviesSELECT nameFROM Movies, StarsIn, MovieExecWHERE producerC#=cert# AND movieTitle = title AND movieYear = year AND = starName;The number of stars that have starred in movies produced by a certain producer SELECT count(DISTINCT starName)FROM Movies, StarsIn, MovieExecWHERE producerC#=cert# AND movieTitle = title AND movieYear = year AND n ame ‘George Lucas’;The number of movies produced by each producerSELECT name, count(*)FROM Movies, MovieExecWHERE producerC#=cert# GROUP BY name。
