Revision of course

1
Revision of course

• For examination purposes

2
Outline of Examination

• Question 1 is compulsory and is worth 40.
• There are five other questions, of which you must
  answer 3. Each is worth 20.

3
Question 1

• Given a description, you are asked to design an
  ERD and write appropriate CREATE statements to
  set it up.

4
Other questions

• There is one question on data normalisation.
• The remaining questions involve the practical
  application of your knowledge of SQL and PL/SQL.
• This means that you must know
• How to do the queries and programs that you have
  been asked to do throughout the year.
• What the practical implications of running those
  queries / programs will be.
• There is an addendum to the examination script
  that will give you some small amount of syntax.

5
Topics covered this year

• Data acquisition and storage
• Database definition
• Modelling notation
• Relational data and Normalisation
• Top-down and bottom-up data modeling.
• Relational algebra implementation
• Aggregation and sub-queries.
• Tables, views, procedures, functions, packages,
  sequences, cursors, triggers.
• Practical application through SQL Server and
  Oracle database management systems.

6
Revising for Question 1

• Revise the Data Model Notation tutorial from
  Semester 1.
• Try out the Modeling Entities tutorial in
  Semester 1.
• Do tutorials 1 to 6 in Semester 1.
• Go back over your cross-curricular assignment and
  revise the reasons you came up with the data
  model that you used.
• Did it work well? If so, why? If not, why not?

7
Revising for the Data Normalisation question

• Go back over the Normalisation Lectures
• FirstNormalForm.ppt and
• L04MoreNormalisation.ppt
• Try the normalisation questions from the old
  papers (See P drive)
• Try normalising the Wellingtons documents.

8
Revising for the other questions

• Read over the lecture notes on database design,
  structure, features and algebra.
• Look over the queries that you have implemented.
• Try to remember any errors that you got
• Why did that error occur?
• How did you fix it?
• Was it a structural or syntactical error?

9
Revising for the other questions

• Look over the programs you have written.
• What was their structure?
• How were they called?
• Was the program resident in the DBMS or did you
  need to load it each time?
• What caused the program to run?
• What errors did you encounter?
• How did you fix them?
• What did you learn from them?

10
Revising for the other questions

• There is no need to go back over SQL Server, but
  you DO need to go back over the queries that you
  were asked to run in SQL Server.
• Look at the data manipulation commands
• Insert
• Update
• Delete
• What sort of errors can you get? Why?
• Look at the Select
• How does it join tables? How does it pick some
  rows and not others? How does it eliminate rows?
  How does it sum / count some fields?

11
Revision for the other questions

• What sort of problems require PL/SQL?
• How is PL/SQL used?
• Think about step-wise refinement.
• Are there sequence / selection / iteration
  commands in it?
• Do you need to call subroutines?
• Do you need to lock out others?
• What is the difference between a script and a
  PL/SQL block?
• What does a transaction require?