Transcript Revision of course

Revision of course
For examination purposes
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%.
Question 1
• Given a description, you are asked to
design an ERD and write appropriate
CREATE statements to set it up.
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.
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.
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?
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.
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?
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?
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?
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?