关系数据库（高级）01一、填空题（每空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: HOME Column: Home_Name__________________________________________________________________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_Priceand 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):__________________________________________________________________8. Create a synonym named ANOTHER_ITEM on the table ITEMCOPY.__________________________________________________________________ 9. Insert the following record into the Franchise table:Franchise No MF999Franchise Name Mature Fashions (Shetlands)Franchise Address 1, Lonely Spot, LerwickFranchise Postcode 2E1 1AAFranchise Tel 01595 1245Franchise Fax 01595 2356Franchise Start Date 22nd January 2002__________________________________________________________________ 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 V ARCHAR2 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 arelationship with another table in the database (known as the parent). The valueentered 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 HA VING clause works in conjunction with the GROUP BY clause. That is, you cannot have a HA VING 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 employeeGROUP BY date_of_birthHA VING COUNT(*) > 1; ( )三、简答题（每小题3分，共30分）Candidate InstructionsYou 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．In the following scenario identify the main entities. (3)The company ‘Hire a Wreck’ runs a car and van rental business at very competitive rates. Customers can hire vehicles on a daily or weekly period.A discount scheme is in operation whereby frequent customers are offered reducedhire rates. For legal reasons vehicles must be serviced on a regular basis.2. What is meant by the term entity occurrence? Illustrate your answer with an example. (3 )3. In a Relational Database System each entity must have a primary key defined. Give a definition of a primary key. Illustrate your answer withan example. (3 )4. A key may be defined as compound key. What is a compound key? Illustrate with an example. (3)5. From the following statements identify the degree of the relationships.The entities that are participating in the relationship are shown in quotes.a) A ‘customer’ may place one or more ‘orders’ each week.b) A ‘customer’ can order up to 5 different ‘products’ on an order. The same product can be ordered by different customers.c) A ‘salesman’ has a ‘company car’ that can onl y be driven by the salesman.d) Each ‘customer’ may have more than one ‘delivery address’.e) In a dental surgery a ‘dentist’ has many ‘patients’. At any one time a patient can only be registered with one dentist. However, over time a patient may register with different dentists.f) A ‘patient’ has many ‘appointments’.(3)6. An automated library system is to be implemented which will managethe loan of books to customers. Customers can take out up to 5 booksat a time. Each book has a defined loan period type (ie. P1=3 days,P2=7 days, P3=2 weeks, P4=1 month). Books are categorised into oneor more subject areas, eg geography, history, war, horror etc Thesystem will allow books to have joint authors. There is only one copy ofeach book in the library . (3)Using the scenario and entity relationship diagram above suggest suitable primary keys for:a) any two from Author, Book, Customer, Category and Loan Periodb) any two from Author/Book, Loan and Book/Category7. A relationship may be defined as being recursive. What is meant by theterm 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 withan 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 nameis that key known? Illustrate your answer with an example. (3)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 InstructionsThe 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 ReportThe 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 integrityThis section of the report is to be between 200 to 300 words in total.Definitions must be accurate and descriptions must be essentiallyaccurate but need not be comprehensive.Performance optimisation: (200 to 300 words) （12）Document the performance advantages and disadvantages of dataaccess 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 .2. You are required to design a relational database from a supplied case study. Candidate InstructionsData 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 ReportUNF1NF: (5)2NF: (5)3NF: (6)关系数据库（高级）01评分标准一、填空题（每空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 ASSELECT item_no, item_description, item_wholesale_price, item_retail_price FROM itemORDER 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');10. UPDATE franchiseSET franchise_address = '1, Main Street, Lerwick', franchise_startdate = '15/Feb/2002'WHERE franchise_no = 'MF999';二、判断以下的说法是否正确，如果正确，将在括号中，填入T(TRUE),否则，填入F(FALSE)。