




版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、精选优质文档-倾情为你奉上Exam of Database Technology & Applications 1. Describe the three levels and data independence.2. What are key constraints and foreign constraints?3. Explain LEFT JOIN, OUTER JOIN and INNER JOIN.4. For the following relation schema and sets of FDs: R is (A, B, C, D, E, F,G) with FDs
2、A-> B, B-> C, A-> E, CD->G.1) Identify the candidate key(s) for R.2) Identify the best normal form that R satisfies3) Decompose it in 3NF if necessary.5. Explain the ACID properties.6. Notown Records has decided to store information about musicians who perform on its albums (as well as o
3、ther company data) in a database. The company has chosen to hire you as a database designer.l Each musician that records at Notown has an SSN, a name, an address, and a phone number. l Each instrument used in songs recorded at Notown has a name (e.g., guitar, synthesizer,flute) and a musical key (e.
4、g., C, B-flat, E-flat).l Each album recorded on the Notown label has a title, a copyright date, a format (e.g.,CD or MC), and an album identifier.l Each song recorded at Notown has a title and an author.l Each musician may play several instruments, and a given instrument may be played by several mus
5、icians.l Each album has a number of songs on it, but no song may appear on more than one album.l Each song is performed by one or more musicians, and a musician may perform a number of songs.l Each album has exactly one musician who acts as its producer. A musician may produce several albums, of cou
6、rse.1) Defining the completed E-R diagram.2) Defining information for each relation.7. Consider the following relational schema and give T-SQL expressions for the following queries.Sailors(sid, sname, age) Boats(bid, bname, color)Reservers(sid, bid , day)1)Create the table Sailors (sid, sname , age)
7、. It includes the domain of values associated with each attribute and integrity constraints. sidINTNOT NULLPRIMARY KEYsnameVARCHAR(10)NOT NULLageINTNULL0<age<1002) Change the attribute sname VARCHAR(12).3) Delete all tuples in the Sailors relation for sailors whose age is less than 18. 4) Find
8、 the names of sailors who have reserved a boat on 2010-1-1.5) Find the names of sailors who have reserved a red boat.6) Find the names of sailors who have reserved at least one boat.7) Find the sid of sailors who have reserved a red boat and a green boat.8) Find the names of sailors who have reserve
9、d all boats.8. Consider the Buys_computer Relation shown in Figure 1. The first four columns show the age and salary of a potential customer and the Buys_computer column shows whether the person buys a computer. We want to use this data to construct a decision tree that helps predict whether a perso
10、n will buy a computer.AgeSalarySubscriptionyouthhighnoyouthhighnomiddle_agedhighyesseniormediumyesseniorlowyesseniorlownomiddle_agedlowyesyouthmediumnoyouthlowyesseniormediumyesyouthmediumyesmiddle_agedmediumyesmiddle_agedhighyesseniormediumnoFigure 1 The Buys_computer RelationANSWER1. The three lev
11、els are physical level, logical level and view level. Physical level describes all relations that are stored in the database. Logical level summarizes how the relations are actually stored on secondary storage devices. Each view level consists of a collection of one or more views and relations from
12、the conceptual level.There are actually two mappings: the conceptual/internal mapping and the external/conceptual mapping. The conceptual/internal mapping lies between the conceptual and internal levels. If the structure of the stored database is changed, then the conceptual/ internal mapping must a
13、lso be changed accordingly so that the view from the conceptual level remains constant. It is this mapping that provides physical data independence for the database.The external/conceptual view lies between the external and conceptual levels. If the structure of the database at the conceptual level
14、is changed, then the external/conceptual mapping must change accordingly so the view from the external level remains constant. It is this mapping that provides logical data independence for the database.2. Primary key constraints assure that the keys of any two records are not same in a table. The p
15、rimary key constraints enforce the entity integrity of the table.Foreign key constraints control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. This constraint enforces referential integrity by ensuring that changes cannot be mad
16、e to data in the primary key table if those changes invalidate the link to data in the foreign key table. 3. The LEFT OUTER JOIN includes all rows in the left table in the results, whether or not there is a match on the join column in the right table. FULL OUTER JOIN includes all
17、 rows from both tables, regardless of whether or not the other table has a matching value.This INNER JOIN is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.4.1)(A,D) is the primary key for R2) R1NF3) R1
18、(A,B,E) ,R2(B,C,F),R3(C,D,G)5. Atomicity: This property guarantees that a set of records that are part of a transaction is indivisible. Thus either all operations of the transaction are properly reflected in the database or none are.Consistency: Database consistency is the property that every transa
19、ction sees a consistent database instance. Database consistency follows from transaction atomicity, isolation, and transaction consistency.Isolation: Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate t
20、ransaction results must be hidden from other concurrently executed transactions. Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.6.1)pruducenmm1mm1musicianinstrumentalbumsongperformplaycontainn2)musician (SSN,
21、 m_name, address, phone number.)instrument ( i_name, musical key)album (a_title, copyright date, format , album identifier, SSN)song ( s_title, author, a_title)play(SSN,i_name)produce(SSN, s_title)7. 1)CREATE TABLE Sailors(sid INT PRIMARY KEY,sname VARCHAR(10) NOT NULL,age INT CHECK( age BETWWEN 0 A
22、ND 100)2) ALTER TABLE Sailors ALTER COLUMN sname VARCHAR(12)3) DELETE FROM Sailors WHERE age<184) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sidWHERE rday='2010-1-1'5) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sid JOIN Boats B ON B.bid=R.bid WHERE color='red'6) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sid 7) SELECT sname FROM Sailors S1 JOIN Reserves R1 ON S1.sid=R1.sid JOIN Boats B1 ON B1.bid=R1.
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 出差人员住宿标准及费用结算合同
- 教学课件怎么设计
- 消防救援知识综合测试试卷含应急管理等要点
- 三年级快乐宝贝教学课件
- 印刷包装设计中的绿色供应链管理考核试卷
- 冷链物流与电子商务融合分析考核试卷
- 表面处理对乐器零件寿命的影响考核试卷
- 影视媒体对动漫产业产业链重构的影响考核试卷
- 行业影响力评估考核试卷
- 离谱面试题目及答案
- 2023-2024学年四川省雅安市小学数学一年级下册期末高分试卷
- 网络游戏代理合同通用版范文(2篇)
- GB/T 6414-1999铸件尺寸公差与机械加工余量
- GB/T 27773-2011病媒生物密度控制水平蜚蠊
- GB/T 12817-1991铁道客车通用技术条件
- 质量风险识别项清单及防控措施
- 【课件超声】常见的超声效应与图象伪差
- 外墙保温、真石漆工程施工方案
- 自然指数NatureIndex(NI)收录的68种自然科学类期刊
- 建立良好的同伴关系-课件-高二心理健康
- 老年人健康管理随访表
评论
0/150
提交评论