关系数据库试题2(英文版以及答案).doc

第 1 页 试题 01 共 21 页 关系数据库 高级 02 题号一二三四五六七八总分评卷人 分数 一 填空题 每空 2 分 共 20 分 1 Once a user enters the data for his transaction he can either commit the transaction to make the changes permanent or the transaction to undo the changes 2 SQL consists of three components Data Definition Language DDL Data Manipulation Language DML 3 By use of the SQL statement ALTER delete the column Item Size from the table ITEMCOPY 4 Using the SELECT option of the SQL statement CREATE TABLE create a table named ITEMCOPY which is a copy of the table ITEM 5 Create an index for the following column that allows duplicate data to be entered Table HOMEColumn Home Name 得分 密 封 线 专业 班级 姓名 学号 第 2 页 试题 01 共 21 页 6 Create a view named ITEM PRICELIST on the table ITEM which only includes the columns Item No Item Description Item Wholesale Price Item Retail Price and sorts the result by the Item Description 7 Assign the appropriate privileges on the table ITEMCOPY to the last one of the users detailed below HN92 UserPrivilege HN90Read only HN91Read and insert only HN92Read insert update and delete 8 Create a synonym named ANOTHER ITEM on the table ITEMCOPY 9 Insert the following record into the Franchise table Franchise No MF999 Franchise NameMature Fashions Shetlands Franchise Address 1 Lonely Spot Lerwick Franchise Postcode 2E1 1AA Franchise Tel 01595 1245 Franchise Fax 01595 2356 Franchise Start Date22nd January 2002 第 3 页 试题 01 共 21 页 10 Update the above record and change the address to 1 Main Street Lerwick and the Start Date 15th February 2002 二 判断以下的说法是否正确 如果正确 将在括号 中 填入 T TRUE 否则 填入 F FALSE 每小题 1 分 本大题共 10 分 1 SQL Plus commands assist with querying data 2 There are several different character datatypes in oracle The CHAR datatype stores character values with a fixed length The VARCHAR2 datatype stores variable length character strings 3 The NULL value is one of the key features of the relational database The NULL in fact doesn t represent any value at all it represents the lack of a value 4 A view is an Oracle data structure constructed with a SQL statement The SQL statement is stored in the database Every view contains data 5 An index is a data structure that speeds up access to particular rows in a database An index is associated with a particular table and contains the data from one or more columns in the table 6 The foreign key constraint is defined for a table known as the child that has a relationship with another table in the database known as the parent The value 得分 第 4 页 试题 01 共 21 页 entered in a foreign key must be present in a unique or primary key of another specific table 7 The ANSI standard Structured Query Language SQL provides basic functions for data manipulation transaction control and record retrieval from the database and most end users interact with Oracle through it 8 The HAVING clause works in conjunction with the GROUP BY clause That is you cannot have a HAVING clause without a GROUP BY clause 9 This SELECT statement will execute successfully SELECT forename surname MIN Salary FROM employee 10 This SELECT statement won t execute successfully SELECT date of birth COUNT FROM employee GROUP BY date of birth HAVING COUNT 1 三 简答题 每小题 3 分 共 30 分 Candidate Instructions You are required to answer each of the following 10 short response questions The maximum marks for each question are shown in bracket Read each question carefully some ask you to qualify your answer with an example 1 During the data analysis phase of systems development objects known as entities are identified What is an entity Illustrate your answer with an example 3 得分 第 5 页 试题 01 共 21 页 2 In the following scenario identify the main entities 3 The company Wheels Are Us runs a moped and motorbike rental business Customers can hire bikes on a daily or weekly period A discount scheme is in operation whereby frequent customers are offered reduced hire rates For legal reasons bikes must be serviced on a regular basis 3 During the data analysis phase of systems development attributes are identified What is an attribute Illustrate your answer with an example 3 第 6 页 试题 01 共 21 页 4 In a Relational Database System each entity must have a primary key defined Give a definition of a primary key Illustrate your answer with an example 3 5 A key may be defined as a simple key What is a simple key 3 Illustrate with an example 6 A key may be defined as a composite key What is a composite key Illustrate with an example 3 第 7 页 试题 01 共 21 页 7 A relationship may be defined as being recursive What is meant by the term recursive relationship Illustrate with an example 3 8 An entity may have more than one choice for the primary key What name is given to an alternative key Illustrate your answer with an example 3 9 Entities in a relational model are often inter dependent upon one another A special type of key implements these relationships By what name is that key known Illustrate your answer with an example 3 第 8 页 试题 01 共 21 页 10 During entity modeling the degree of relationships cardinality are determined There are three types of degrees of relationships name them 3 四 综合题 第 1 题共 40 分 1 You need to produce a report outlining the issues involved in the implementation of relational database systems Candidate Instructions The object of this question is to allow you to become familiar with the main knowledge areas of RDBMSs to enable you to make informed and justifiable decisions on the implementation of relational database systems You are required to produce a report on the topics detailed overleaf The majority of issues should be illustrated using examples within the specific RDBMS chosen for delivery ie Oracle The candidate will produce evidence in the form of a report outlining the issues involved in the implementation of relational database systems The Report The report must include the following items Data integrity measures 200 to 300 words 12 Transaction processing and the implications of rollback and commit Locking strategies covering read write and shared locks Cascade events with reference to referential integrity 得分 第 9 页 试题 01 共 21 页 This section of the report is to be between 200 to 300 words in total Definitions must be accurate and descriptions must be essentially accurate but need not be comprehensive Performance optimisation 200 to 300 words 12 Document the performance advantages and disadvantages of data access for each of the following Indexing versus full table scans Numeric versus non numeric key values Maintaining versus calculating calculated fields This section of report may be tabular graphical or textual and should be accompanied by brief descriptions and or summaries between 200 to 300 words 第 10 页 试题 01 共 21 页 第 11 页 试题 01 共 21 页 2 You are required to design a relational database from a supplied case study Candidate Instructions Data sources normalised to 3NF showing all intermediate stages if preferred the normalisation from each data source may be submitted separately for marking before moving onto the next data source Please show all the normalisation steps each step UNF 1NF 2NF 3NF and all keys primary and foreign must be clearly marked Invoice Report UNF Franchise No Franchise Name Franchise Address Fr Tel Fr Fax Invoice Number Invoice Date Date Due Order Number Order Date Invoice Net MF001MidlandsB ham345346MF001 115 02 9815 03 98MF001 18 1 98341 50 MF001 223 02 9823 03 02MF001 215 1 98200 00 MF002SE KentRamsgate678679MF002 115 02 9815 03 02MF002 18 1 98100 00 1NF 5 第 12 页 试题 01 共 21 页 2NF 5 3NF 6 第 13 页 试题 01 共 21 页 关系数据库 高级 02 评分标准 一 填空题 每空 2 分 共 20 分 1 rollback 2 Data Control Language DCL 3 ALTER TABLE itemcopy DROP COLUMN item size 4 CREATE TABLE itemcopy AS SELECT FROM item 5 CREATE INDEX home name index ON home home name 6 CREATE VIEW item pricelist AS SELECT item no item description item wholesale price item retail price FROM item ORDER BY item description 7 GRANT select insert update delete ON itemnosize TO HN92 8 CREATE SYNONYM another item FOR itemcopy 9 INSERT INTO franchise VALUES MF999 Mature Fashions Shetlands 1 Lonely Spot Lerwick 2E1 1AA 01595 1245 01595 2356 22 Jan 2002 MF000 第 14 页 试题 01 共 21 页 10 UPDATE franchise SET franchise address 1 Main Street Lerwick franchise startdate 15 Feb 2002 WHERE franchise no MF999 二 判断以下的说法是否正确 如果正确 将在括号中 填入 T TRUE 否则 填入 F FALSE 每小题 1 分 本大题共 10 分 1 F 2 T 3 F 4 F 5 T 6 T 7 T 8 T 9 F 10 F 三 简答题 每小题 3 分 共 30 分 1 2 marks for a full explanation 1 mark for a partial explanation else 0 Plus 1 mark for a suitable example A uniquely identifiable information object real or conceptual which is of distinct and enduring significance to the enterprise Objects that are considered to be important in a system A data analysis term used to describe a thing or object about which things are known Any object in the system that we want to model and store information about An entity is something about which the system would want to store information Suitable examples real employee car department conceptual course giving delivery 2 Entities COMPANY 第 15 页 试题 01 共 21 页 VEHICLE could also have MOTORBIKE CUSTOMER RENTAL could also have REDUCED HIRE RATE DISCOUNT SCHEME SERVICE 3 2 marks for a full explanation 1 mark for a partial explanation else 0 Plus 1 mark for a suitable example A characteristic or element of information that is used to identify classify or describe any occurrence instance of an entity Describes the properties of entities and relationships An item of data that describes an entity A property of an entity A piece of information which is stored about an entity Suitable examples surname DOB postcode 4 2 marks for a full definition 1 mark for a partial definition else 0 Plus 1 mark for a suitable example A field or combination of fields that uniquely identifies a record A column or group of columns in a single relation that can be used to uniquely identify a row in that same relation Suitable examples Employee employee no Student student id etc 5 2 marks for a full explanation 1 mark for a partial explanation Plus 1 mark for a suitable example Simple Key an attribute that on its own provides a unique reference 第 16 页 试题 01 共 21 页 to an entity eg Employee Id 6 2 marks for a full explanation 1 mark for a partial explanation else 0 Plus 1 mark for a suitable example Composite Key A key that consists of more than one attribute ie where more than one attribute is required to uniquely identify an entity occurrence However one or more of the attributes which make up the key are not simple keys in their own right eg instead of Student Id could use Student surname Student firstname Student DOB 7 2 mark for a full definition 1 mark for a partial definition else 0 Plus 1 mark for example A recursive relationship is where the same entity is participating in the relationship For example an employee entity with an attribute of manager employee number for that employee The manger employee number is an entity occurrence in the employee entity 8 1 mark for Secondary Key Candidate Key is also acceptable answer 2 mark for suitable example eg Book Book Title 9 1 mark for Foreign Key 2 mark for suitable example eg Loan Period Type in Book table or any part of the PK in a table referring to another table eg Customer Id in Loan table referring to Customer table 10 1 mark for each relationship degree identified One to one 1 1 One to many 1 M Many to many M N 四 综合题 第 1 题共 40 分 1 1 A transaction in database terminology is a set of one or more related SQL statements transaction is a work request from a client to insert update or delete data The statements that change data are a subset of the SQL language called Data Manipulation Language DML Transactions must be handled in a way that guarantees their integrity In database terms a transaction is a logical 第 17 页 试题 01 共 21 页 unit of work composed of one or more data changes A transaction consists of one or more INSERT UPDATE and or DELETE statements affecting data in multiple tables The entire set of changes must succeed or fail as a complete unit of work A transaction starts with the first DML statement and ends with either a commit or a rollback Once a user enters the data for his transaction he can either commit the transaction to make the changes permanent or roll back the transaction to undo the changes If a transaction is successful then any changes made to the database within that transaction need to be permanently saved This is achieved by execution of the SQL command COMMIT Once changes have been committed they cannot be undone If a transaction is unsuccessful then any changes made to the database within that transaction need to be undone This is achieved by execution of the SQL command ROLLBACK A rollback undoes changes to the last commit point 2 Some databases also To allow concurrency of database access both read and update by multiple users a locking strategy needs to be enforced automatically by the RDBMS The level of locking is known as granularity A lock can be placed on a column row field or an entire table Locks can be either SHARE locks allows other transactions to acquire share locks on the same data or EXCLUSIVE locks prevents other transactions acquiring locks of any type on the same data use read locks or shared locks A read lock can be held by any number of users who are merely reading the data because the same piece of data can be shared among many readers However a read lock prevents a write lock from being placed on the data as the write lock is an exclusive lock 3 Normally you cannot delete a row in a parent table if it causes a row in the child table to violate a foreign key constraint However you can specify that a foreign key constraint causes a cascade delete which means that deleting a referenced row in the parent table automatically deletes all rows in the child table that reference the primary key value in the deleted row in the parent table 2 You are required to design a relational database from a supplied case study Candidate Instructions Data sources normalised to 3NF showing all intermediate stages if preferred the normalisation from each data source may be submitted 第 18 页 试题 01 共