These answers may not be correct so don't just take my word for it!
Do the questions yourself by cheating and using your course
notes and books. If they look like mine, then the chances are
quite good that we are both correct. It does not matter if it
takes you more than one hour for each question, the fact you have
got an answer will stick in you mind and will help during the
real exam.
Learn to read the question properly and only answer what is
asked and not what you think they want. Don't forget to do
all the parts of each question. Explain any assumptions you
have made if the question is not clear. Read all the questions
before you write anything down, there may be an easier one
further down the paper. Tick off the ones you can answer as
you read the paper, then go back and assess which will be the
best question to start with.
I have looked at both of these past papers and there is a common
theme of 6 questions from which you must select 4. There is
no compulsary question so you can just cross out the two
you don't like in the exam. All questions are worth the same
25 marks (hence 4 = 100%) although the sub questions do vary
the the marks they attract. I cannot see any parts with less
than 1 mark although some of the larger ones do attract 10 or
more on occasion.
The questions usually group all their parts around a common
subject area so there should be no rogue parts to these
questions.
In common with previous exams I have assumed that you get
a point for each statement, bullet point or sentence that
gives a complete answer and uses all the buzz-words. If your
answer does not do this only expect half a point per
statement. Sometimes it is easier to draw a diagram than
try to describe something in words (an EERM for
instance). In this case you will get marks for every correct
physical object drawn, and every correct label that explains
what it is.
Keep an eye on the time, there are 4 questions to answer in
3 hours that's 15 mminutes to read the paper, 37 minutes per
question and 15 minutes checking at the end. That works out
at about 90 seconds per mark, so if you are still working on
a 3 mark question after 5 minutes, stop, leave a few lines
blank and go back to it at the end.
I have not bothered to repeate the questions before I answer
them. Please print out the exam papers by downloading the zip
file in the DS Index page. This
contains two PDF files, one of each exam paper.
1.h(4 mark) |
(Relational Algebra)
|
2.a(3 mark) |
(Concept, Physical, Logical design)
Conceptual Design is a model of the real-world objects
that is understandable by the customer community.
Logical Design is the implementation independant list
of database objects (tables) that represents the Conceptual
model.
Physical Design is the implementation specific
design details of indexes, files, disks, blocks, etc where
the database is actually stored.
|
2.b(4 mark) |
(ER Diagram)
|
2.c(4 mark) |
(Existence List)
An Existence Entity is a look-up table. It contains (as
a minimum) a single attribute
whose values map into the
domain for the attribute.
A good example in the case study is a list of valid property
types for the TYPE domain of the PROPERTY table.
The content of the TYPE domain would be (flat,
house, bungalow).
A second example would be POSITION which would contain a list
of valid company positions for staff, such as: manager
secretary, agent.
In both cases the existence entity becomes a check constraint
on the legal values for that attribute enforced by a Foreign Key
constraint of the referencing table against the Primary Key on
the domain existence entity.
|
2.d(6 mark) |
(EERM)
The EERM brings inheritance to the ERM by allowing Super and
Sub Classes, Aggrigation and Composition (Association).
For instance, in the case of the STAFF entity, there may be
some attributes which only relate to some staff types as follows:
At the top is the SuperClass with the common attributes of
StaffNo and StaffName, at the bottom are the SubClasses showing
their specialisations by exhibiting differential attributes.
Each of the SubClass entities would inherit the common attributes
from the SuperClass entity (STAFF).
|
2.e(8 mark) |
(Physical Design Activities)
- Design Base Relations (Tables) and enterprise constraints
using the available facilities of the target RDBMS.
- Choose File Organisations and Indexes based on what is
available on the implemented OS and the target Hardware.
- Decide how each User View should be implemented and how
security will be organised - views, grants, roles, profiles.
- Monitor and Tune the operational system to identify and solve
any ongoing performance issues.
|
3.a(1 mark) |
(Views)
A View is stored in the DBMS as a text string of it's definition,
not a collection of data as is a real table. When a view is queried
the RDBMS merges the where part of the view difinition with the
where of the query to prouce a run-time query which is then executed.
|
3.b(4 mark) |
(Reasons for Views)
The two main reasons for Views are Security (Read Only Views)
and convenience (preconfigured queries and joins).
To make a Read Only view, reference the base table but leave out
any mandatory not-null attribute (usually the Primary Key).
EG: CREATE VIEW staff-branch
(employee, job, branch-id)
AS SELECT name, position, staff-branch
FROM staff;
Note the ability to change column names between the view
and the base tables.
Pre-configured join to find which properties are assigned to
branches.
EG: CREATE VIEW branch-prop
(branch-no, prop-no, type, rooms)
AS SELECT B.branch-no,
P.prop-no,
P.type,
P.rooms
FROM branch B
property P
staff S
viewing V
WHERE B.branch-no = S.staff-branch
S.staff-no = V.staff-no
V.prop-no = P.prop-no;
|
3.c(3 mark) |
(Views)
The view is not updateable, as it does not contain the primary
key from the base table. As the primary key (by definition) is
a not-null field, attempting to insert a row through the view
leaving the primary key blank will violate the Primary Key
entity constraint.
|
3.d(4 mark) |
(Views)
Create a view such as:
CREATE VIEW staff-salary
(name, salary, type)
AS SELECT name, salary, position
FROM staff
WHERE position <> 'manager';
Then query the view thus:
SELECT * FROM staff-salary WHERE type = 'agent';
This would resolve into the following base table query:
SELECT name, salary, position AS 'type'
FROM salary
WHERE position <> 'manager'
AND position = 'agent';
Which would list details of names and salaries of AGENT
staff only.
|
3.e(6 mark) |
(ANSI-SPARC Architecture)
External Level (Views) for
user access to data |
 |
Conceptual Level (Base Tables)
What data is stored and their relations
Community Data |
Internal Level (Physical) How
the data is stored |
Physical Data Organisation
The OS Implementation |
|
3.f(3 mark) |
(External Schema - View)
A "staff-viewing" view would be a useful external schema to allow
agents to plan their viewing day routes to properties when organising
customer viewing sessions.
CREATE VIEW staff-viewing
(prop-no, address, date, comments)
AS SELECT P.prop-no, P.address, V.vew-date, V.comments
FROM property P, viewing V
WHERE P.prop-no = V.prop-no
AND V.view-date = sysdate
AND V.staff-no = '';
There would be one view for each staff member with its name
including the staff number of each member. Where queried the
view would list out all the visits for that day for the
enquiring staff member.
|
3.g(4 mark) |
(Phisical Data Independence)
The ANSI-SPARC Architecture limits the disruption casued in layers
above by changes in the layers below. Physical Data Independence
means that changes to the physical level will not be visible to
conceptual or the user lelevs. So changes to file layouts and
disk drive organisations should have no effect on the table
structures, likewise, changes to the indexing schemes should have
no effect on (require no changes to) the application layers.
|
4(25 mark) |
(Roles)
This question is asking about roles which were not covered in
this years syllabus. You can answer only parts of the is question.
I just didn't bother. Q 4.c is the only part you can completely
answer with the knowledge gained this year.
|
5.a(6 mark) |
(Transactions - ACID)
- Atomicity The unit of work done by the transaction
cannot be smaller and still maintain database integrity.
It should not be possible to execute part of a transaction,
it is an all or nothing approach controlled by the RDBMS.
- Consistency Database integrity must never be
compromised by any transaction or else it must roll back.
This in enforced by the integrity constraints and maintained
by the RDBMS and the Application.
- Isolation A transaction should not impact any other
transaction in the database but should stand alone. The
results of a part completed reansaction should never be
visible to another transaction. The RDBMS concurrency
control and scheduling system should maintain this aspect.
- Durability Once successfully completed the results of
a transaction should be permanent after a commit is issued.
The database recovery process should be able to recover to
this point after a crash.
What kind of examples are they looking for here?
|
5.b(4 mark) |
(Locks)
Any SELECT statment would automatically set a share lock. EG:
SELECT * FROM staff;
Any INSERT, UPDATE, DELETE statement would set an exclusive lock. EG:
UPDATE staff SET salary = salary * 1.1 WHERE position = 'agent';
The purpose of the share lock is to make sure the RDBMS is aware
that a transaction is looking at some data. It can then be alert
for read-consistancy if a new transaction attempts to apply an
exclusive lock to the same object.
The exclusive lock is used to stop the lost-update problem. A
transaction attempting to put an exclusive lock on an object that
already carries an exclusive lock, must wait for the object to
become free or else timeout.
|
5.c(3 mark) |
(2PL)
The two phase locking protocol consists of a growing phase when
locks can be acquired and a shrinking phase during which locks
can be released. During the growing phase no locks can be released
and during the shrinking phase, no more locks must be acquired.
2PL guarantees:
- Prevents lost updates
- Prevents uncommitted dependency
- Prevents inconsistent analysis
|
5.d(4 mark) |
(Transaction Diagrams)
Lost Update:
| Time | Trans 1 | Trans 2 | [Value]x |
|---|
| 1 | begin | | 10000 |
| 2 | write-lock(sal-x) | begin | 10000 |
| 3 | read(sal-x) | write-lock(sal-x) | 10000 |
| 4 | sal-x=sal-x*1.1 | read(sal-x) | 10000 |
| 5 | write(sal-x) | sal-x=sal-x-10 | 11000 |
| 6 | commit/unlock(sal-x) | write(sal-x) | 09990 |
| 7 | | commit/unlock(sal-x) | 09990 |
Two Phase Locking:
| Time | Trans 1 | Trans 2 | [Value]x |
|---|
| 1 | begin | | 10000 |
| 2 | write-lock(sal-x) | begin | 10000 |
| 3 | read(sal-x) | write-lock(sal-x) | 10000 |
| 4 | sal-x=sal-x*1.1 | WAIT | 10000 |
| 5 | write(sal-x) | WAIT | 11000 |
| 6 | commit/unlock(sal-x) | WAIT | 11000 |
| 7 | | read(sal-x) | 11000 |
| 8 | | sal-x=sal-x-10 | 11000 |
| 9 | | write(sal-x) | 10990 |
| 10 | | commit/unlock(sal-x) | 10990 |
|
5.e(5 mark) |
(Wait-For-Graph)
This diagram is a wait-for-graph (WFG) representing a deadlock.
Each of the transactions in the diagram is waiting for resources
currently held (or locked) by another transaction.
The RDBMS must be able to detect deadlocks and resolve them. There
are three usual methods for detecting deadlock: timeout, wait-die,
wound-die. The DBMS creates and maintains WGF's to enable detection
and resolution.
|
5.f(3 mark) |
(Locks)
Because if the SELECT query is long running, it needs to be sure
of getting "read-consistent" data even if a short UPDATE transaction
affects a few rows in the queried table between the SELECT starting
and completing its read operation.
Do we need further examples or is that enough for 3 marks...?
|
6.a.(i)(4 mark(2)) |
(Candidate Key)
A Candidate Key is a single or multiple attribute which can
uniquely identify one row in a table. Several Candidate Keys
may exist in a table and from these one is selected to become
the Primary Key. In the PROPERTY table both prop-no and address
could be considered as Candidate Keys. The attribute prop-no
was chosen to be the Primary Key because the field length is
shorter and results in a more efficient implementation.
|
6.a.(ii)(4 mark(2)) |
(Functional Dependency)
Functional Dependency relates to how attributes are related
(connected) to a table. It is usually (but not always) a one-way
relationship as follows:
staff-no  staff-name
The attribute staff-name is dependant on staff-no. But the reverse
is not true as two staff with the same name could exist and they
would have different staff-no values.
|
6.b(4 mark) |
(BCNF)
Every determinant is a candidate key.
subject + student tutor
tutor subject
tutor student
|
6.c(4 mark) |
(Hidden Attributes)
Can anyone find the definition for this question - it is not
mentioned in the index to Connolly...?
|
6.d(8 mark) |
(Unknown Values)
In the handout it states CODDS rules do not handle "unknown"
values. So the NULL value is something that was thought of to
make RDBMS implementations possible. What do they want here?
|
6.e(2 mark) |
(Natural Join)
| Sno | Sname | SCity | Status | Pno | Colour | Pcity |
|---|
| S1 | Jones | London | 10 | P3 | red | London |
| S1 | Jones | London | 10 | P6 | yellow | London |
| S4 | Clark | Paris | 30 | P4 | blue | Paris |
|
6.f(3 mark) |
(Maybe Join [outer join ?])
| Sno | Sname | SCity | Status | Pno | Colour | Pcity |
|---|
| S1 | Jones | London | 10 | P3 | red | London |
| S1 | Jones | London | 10 | P6 | yellow | |
| S2 | Smith | | 10 | P3 | red | London |
| S2 | Smith | | 10 | P4 | blue | Paris |
| S2 | Smith | | 30 | P6 | yellow | |
| S4 | Clark | Paris | 30 | P4 | blue | Paris |
| S4 | Clark | Paris | 30 | P6 | yellow | |
|
