�ݺ�ߣ

DATABASE DESIGN & DEVELOPMENT

5th DECEMBER 2010

MARKING SCHEME

This Marking Scheme has been prepared as a guide only to markers. This is not a set of model
answers, nor is the Marking Scheme exclusive, for there will frequently be alternative responses
which will provide a valid answer. Unless a question specifies that an answer be produced in a
particular form, then an answer that is correct, factually or in practical terms, must be given the
available marks.

If there is doubt as to the correctness of an answer the relevant NCC Education materials and
associated module textbook should be the first authority.

Throughout the question, please credit any valid alternative point.

Notice to Markers

Where markers award half marks in any part of a question they should ensure that the total
mark recorded for a task is rounded up to a whole mark.

ANSWER ANY FOUR QUESTIONS

QUESTION 1 Marks

a) Give a definition of the following contemporary database types
i) Operational database 4
• Used to collect operational data (1 mark).
• Used to support standard operations of an organisation by providing data that is reliable,
timely and valid. (up to 2 marks).
• Operations that should be able to be performed are create, retrieve, update and delete (1
mark).

ii) Decision support database 4
• Designed to support decision makers in the organisation, such as varying levels of
manager, in making decisions (1 mark).
• This is usually a read-only database (1 mark).
• Should support query tools (1 mark)
• or other custom applications for interrogating and representing data trends (1 mark).

iii) Mass deployment database 4
• Deliver data to the desktop (1 mark).
• Usually single user systems (1 mark).
• Updated from other types of database (1 mark).
• Further mark can be given for example of a commercial product such as Microsoft Access
or example of use of such as system such as in a university system a individual lecturer
with their own student records.

b) Identify and describe the THREE (3) different user types of a database system. 9
Award 1 mark for identification of End-users, Database administrators/data administrators AND
system developers. (3 Marks)

End-users. Up to 2 further marks for characteristics of end-users. This answer could encompass a
very broad set of definitions. Everyday users who input transactional data, users of deployed systems
through some other ICT interface who might not know they are using a database e.g. on-line
shoppers. Mention might also be made of sophisticated users who are allowed access to query tools.

Database/Data Administrators: Up to 2 further marks for database administrators creating an
maintaining databases, managing DBA etc. Data administrators are those in charge of maintaining
accuracy and legality of data.

Systems Developers: Up to 2 further marks for those who develop applications on top of databases,
those involved in systems integration between different types of hardware and/or software and a
database system, usability/interface professionals. This is a broad category and students should be
rewarded for applying knowledge and imagination here.

Marks

c) Identify and explain the difference between data and information 4
Data are units of symbols or individual symbols stored in some form by a system. (1 mark for this or
similar). Information is data that is processed in some way (1 mark) so as to become meaningful (1
mark). Processing of data might be summarising, analysis, assembly, change in representation,
change in context etc. (1 mark for this or similar).
The precise nature of the answer to this question is not important in itself; marks should be given
that demonstrate an understanding of the concepts.

Total 25 Marks

Database Design & Development AM December 2010 06/10/10 Final © NCC Education Ltd 2010
Marking Scheme

QUESTION 2
Throughout the question, please credit any valid alternative point. Marks

In a Sales Order processing system, the following tables have been created using the following SQL DDL
commands.

1) CREATE TABLE CustOrder
(
OrderNo varchar2 (4) not null,
CustNo varchar2 (4) not null,
OrderDate date,
OrderValue number(6,2),
PRIMARY KEY (OrderNo),
FOREIGN KEY (CustNo) REFERENCES Customer(CustNo) );

2) CREATE TABLE Customer
(
CustNo varchar2 (4) not null,
CustName varchar2(10),
Address Varchar2(20),
PRIMARY KEY (CustNo) );

3) CREATE TABLE OrderDetail
(
OrderNo varchar2 (4) not null,
ProductNo varchar2 (4) not null,
Quantity number(5),
PRIMARY KEY (OrderNo,ProductNo),
FOREIGN KEY (OrderNo) REFERENCES CustOrder (OrderNo),
FOREIGN KEY (ProductNo) REFERENCES Product (ProductNo),
Check (Quantity >0 ) );

4) CREATE TABLE Product
(
ProductNo varchar2 (4) not null,
ProductName Varchar2(10),
Colour Varchar2(10),
Weght number(3),
PRIMARY KEY (ProductNo),
CHECK (Colour in (‘RED’,’GREEN’,’BLUE’,’BLACK’,’YELLOW’,’PINK’)) );

Marks

a) Draw a diagram to illustrate the relationship between these tables 7

Up to 7 marks. 1 mark for each entity, 1 mark for each relationship. Can ignore optionality.

b) Give the correct sequence for creating the tables Customer, CustomerOrder and OrderDetail in order to 6
avoid the violation of integrity constraints.
6 marks for right sequence. Marks can also be given for partially correct answers.

CREATE TABLE Customer before CREATE TABLE CustOrder and CREATE TABLE
OrderDetail
CREATE TABLE CustOrder before CREATE TABLE OrderDetail
CREATE TABLE Product before CREATE TABLE OrderDetail

Marking Scheme

Marks

c) A user tried to execute the following commands in the given order to insert values into the Customer, 12
CustomerOrder and OrderDetails tables. Identify those commands that would result in the return of an
error message and explain why they would return an error message.

1) INSERT INTO Customer values (‘5566’, ‘JONES’, ‘CROSS ROAD’);
2) INSERT INTO Customer values (‘5667’, ‘JONES’, ‘RIVER LANE’);
3) INSERT INTO Customer values (‘5667’, ‘SMITH’, ‘MILL LANE’);
4) INSERT INTO CustOrder values (‘1005’, ‘5566’, ’03-FEB-2004’, 20.50);
5) INSERT INTO CustOrder values (‘1006’, ‘6566’, ’04-FEB-2004’, 40.50);
6) INSERT INTO Product values (‘2345’, ‘BOLT’, ’GREY’, 25);
7) INSERT INTO Product values (‘2344’, ‘NUT’, ’ GREEN’, 15);
8) INSERT INTO Product values (‘2346’, ‘SCREW’, ’BLUE’, 5);
9) INSERT INTO OrderDetail values (‘1010’, ‘2344’, 100);
1 mark for each error identification.
1 mark for each correct explanation

INSERT INTO Customer values (‘5566’, ‘JONES’, ‘CROSS ROAD’); OK
INSERT INTO Customer values (‘5667’, ‘JONES’, ‘RIVER LANE’); OK
INSERT INTO Customer values (‘5667’, ‘SMITH’, ‘MILL LANE’); Error
Primary key constraint. CustomerNo ‘5667’ already exists in Customer table.
INSERT INTO CustOrder values (‘1005’, ‘5566’, ’03-FEB-2004’, 20.50); OK
INSERT INTO CustOrder values (‘1006’, ‘6566’, ’04-FEB-2004’, 40.50); Error
Foreign key constraint. Cannot find Primary key CustomerNo ‘6566’ in Customer table.
INSERT INTO Product values (‘2345’, ‘BOLT’, ’GREY’, 25); Error
Domain constraint. Colour ‘GREY’ is an invalid value.
INSERT INTO Product values (‘2344’, ‘NUT’, ’ GREEN’, 15); OK
INSERT INTO Product values (‘2346’, ‘SCREW’, ’BLUE’, 5); OK
INSERT INTO OrderDetail values (‘1010’, ‘2344’, 100); Error
Foreign key constraint. Cannot find Primary key OrderNo ‘1010’ in CustOrder table.
INSERT INTO OrderDetail values (‘1005’, ‘2344’, 200); OK
Foreign key constraint. Cannot find Primary key ProductNo ‘2345’ in Product table.
Primary key constraint. Composite key ‘1005’and ’2344’ already exists in CustOrder table

Total 25 Marks

QUESTION 3

a) Briefly define each of the following terms, which relate to internet infrastructure.
i) TCP/IP 2
• Transmission Control Protocol/Internet Protocol (1 mark).
• The communication software model underlying the internet (1 mark).
• Answers that do not give the precise meaning of the acronym but supply other details
should be rewarded.

ii) HTTP 2
• Hypertext Transfer Protocol (1 mark). Defines how information can be transferred
between clients and servers (1 mark).
should be rewarded

iii) IP addresses 2
• Internet Protocol Address (1 mark). Way of identifying a computer system on the internet
(1 mark).
should be rewarded

iv) URL 2
• Universal Resource Locator (1 mark). Provide a unique address for a document on the
WWW. (1 mark).
should be rewarded

v) Domain names 2
• An agreed piece of text that has been recognised as meaningful on the WWW. Examples
are .ac for academia or country specific domains. (1 mark).
• A domain name identifies and locates a host computer or service on the internet. (1 mark)

b) Describe each of the following types of web-enabled database applications.
i) Static report publishing 5
A database generates a static report, form or query response in HTML format and posts it to a
web site. This is a one-way process, the user provides no data. An example might be a data
sales summary posted to the web

ii) Query publishing 5
A HTML form is generated which allows the user to enter some query criteria. The data
transmission is two-way between user and server with results posted to the web site. An
example might be a query made about the progress of a customers order. Each query is
independent and both client and server need not take any account of the state of their
interaction.

Marking Scheme

Marks
iii) Application publishing 5
Application publishing is a type of publishing closest to database applications. Unlike the
other types of publishing it is important that state is maintained between the client and the
database server so that no transaction are disrupted. This is accomplished by the web server.
An example might be any transaction processing such as a customer managing their bank
account over the internet.
Total 25 Marks

QUESTION 4
Marks
a) The following relate the key functions associated with database administration
i) Describe FOUR (4) of the functions associated with the administration of a database. 8
There are a number of key activities that can be mentioned. 1 mark should be given for the key
activities mentioned in the answer plus additional 1 mark for detailing that category.

The students should have selected four from those shown below:
• Physical design
• Data standards and documentation
• Monitoring data usage and tuning database
• Data archiving
• Data backup and recovery

ii) Describe the functions associated with the administration of a DBMS. 6

• Installation
• Configuration control
• Monitoring DBMS usage and tuning DBMS

iii) Describe the functions associated with the administration of a database environment. 8

• Data control
• Impact assessment
• Privacy, security and integrity
• Training

b) Discuss the role of views in data security. 3
Definition of a view is a virtual table that doesn’t store data but presents it to the user in a particular
form (1 mark).
Views can be used to create a particular view of data that restricts users to only seeing part of a total
data set (1 mark).
This can be used in security by restricting users of a particular department or a particular access
level/level of authority to as set of data represented in a particular view (1 mark).
Alternative answers that use an example (such as a university) should be allocated appropriate marks
up to the full three.

Total 25 Marks

QUESTION 5

a) With regard to Object Orientated (OO) databases define the following concepts:
i) Encapsulation 3
The packaging together of both data and methods (1 mark) within a defined interface (1 mark)
allowing controlled access across that interface (1 mark).
ii) Object classes 3
Objects are sets of data and methods (1 mark). An object class in a grouping of similar objects
(1 mark). They all have the same attributes and methods (1 mark).
iii) Inheritance 3
Inheritance is concerned with the generalisation hierarchy of class and subclasses (1 mark for
this or similar that captures the concept).
Answer does not have to mention by name structural and behavioural inheritance but can
instead talk about inheritance of attributes (1 mark) and inheritance of methods (1 mark)
b) In the context of OO databases describe the FOUR (4) major forms of method. 12
1 mark each for identifying each of the forms:
• Constructor methods
• Destructor methods
• Accessor methods
• Transformer methods

Up to a further 2 marks each for giving a definition and/or example:

• Constructor methods. Create new instances of a class.
• Destructor methods. Remove unwanted objects.
• Accessor methods Yield properties of the object.
• Transformer methods. Yield new objects from existing objects.

c) Give an example of the declaration of a class called ‘Student’.
Any format or pseudo-code is acceptable marks (up to 4) should be give for: 4
• Create Class (keywords 1 mark)
• Superclass (1 mark, but this is optional)
• Appropriate attributes (1 mark)
• Type declarations of attributes (1 mark)
• Appropriate methods (1 mark)
• Any relationships (1 mark).

Total 25 Marks

Marking Scheme

Learning Outcomes Matrix

LO1 LO2 LO3 LO4 LO5 LO6 LO7 Learning
Materials
Reference
1 a) 12 Chapter 4 (p57)
b) 9 Chapter 4 (p58)
c) 4 Chapter 3 (p20)

2 a) 7 Chapter 11 (p157)
Chapter 16 (p223 on)
b) 6 Chapter 12 (p168 on)
c) 12 Chapter 12 (p168 on)

3 a) 10 Chapter 43 (p556 to
558)
b) 15 Chapter 43 (p563 to
564)

4 a) 22 Chapter 23 (p345)

5 a) 9 Chapter 8 (p119)
c) 4 Chapter 8 (p120)

Total 56 47 22

�ݺ�ߣ

Ddd ms dec 2010

More Related Content

Ddd ms dec 2010