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

关系数据库（高级）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: 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_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):

__________________________________________________________________

8. Create a synonym named ANOTHER_ITEM on the table ITEMCOPY.

__________________________________________________________________ 9. Insert the following record into the Franchise table:

Franchise No MF999

Franchise Name Mature Fashions (Shetlands)

Franchise Address 1, Lonely Spot, Lerwick

Franchise Postcode 2E1 1AA

Franchise Tel 01595 1245

Franchise Fax 01595 2356

Franchise 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 a

relationship with another table in the database (known as the parent). The value

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 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 employee

GROUP BY date_of_birth

HA VING 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)

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)

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. 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)

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

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 .

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

1NF: (5)

2NF: (5) 3NF: (6)

关系数据库（高级）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'

);

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

VEHICLE (could also have MOTORBIKE)

CUSTOMER

RENTAL (could also have REDUCED HIRE RA TE)

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

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, i n 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 https://www.360docs.net/doc/705227812.html,nguage (DML). Transactions must be handled in a way

that guarantees their integrity.In database terms, a transaction is a logical 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 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.

1NF

2NF

(NOTE – Invoice Number unique within the system, therefore the Invoice Date, Date Due, etc dependent upon Invoice Number only)

3NF