Transcript Revision Lecture - Homepages | The University of Aberdeen

CS2008/CS5035 Exam
Preparation
Organization of Lecture Notes
•
Group 1 - SQL
•
Group 2 – Database Design
•
Group 3 – Database Connectivity
•
Group 4 – Database Internals
•
Group 5 – Systems/Applications
–
–
–
–
–
–
–
–
L1 – Introduction
L2 – Relational Model
L3 – SQL –I - SQL Introduction
L4 – SQL –II – Aggregate Functions, Sub-queries
L5 – SQL – III - Joins
L6 - SQL – IV - Data Definition & Integrity Constraints – Create Table etc
L7 – SQL –V - Access Control & Views
Additional lecture notes on writing SQL select queries
–
–
–
–
L8 – Database Design: ER Modelling
L9 – Database Design: ER Modelling (Continued)
L10 – Logical Design
L11 – Normalization
–
–
–
L12 – Database Programming – JDBC
L13 – Introduction to Web-based DBMS Technology
L14 – Web-based Database Programming with PHP
–
–
–
–
–
L15 – Database Security
L16 – Transaction Management
L18 – Query Processing
L19 – File Organization & Indexing
L20 – B+-Trees
–
–
L21 – MySQL
L22 – Spatial Databases
Dept. of Computing Science, University of Aberdeen
2
Exam Structure
• Exam Paper: Answer 2 out of 3 questions in 2
hours
•
– Q1: Database Design
– Q2: SQL + Other
– Q3: Other
‘Other’ could mean anything else in the course !!
– DBMS internals, Database connectivity (JDBC, PHP) and
Spatial databases
• Your strategy should be to
– Take 5-10 minutes to look over the whole paper first
– Then, aim for about 55 minutes per question
Dept. of Computing Science, University of Aberdeen
3
Question Structure
• The questions aim to test your knowledge and
understanding
• Each question (25 marks) will be divided
approximately into 3 levels:
– Bookwork: (<6) – “knowledge” - what all of you should know!
– Seen: (10) – “understanding” - can you solve a problem you've
seen ?
– Unseen: (9) – “knowledge & understanding” – solving an unseen
problem
• For a “pass”, you'll need to get about 12/25...
• For a “first”, you'll need to get about 20/25 or more.
Dept. of Computing Science, University of Aberdeen
4
Marking Strategy
• You can exploit the way examiners mark papers...
• Most examiners, including me, “work towards the
middle”:
– We try to give marks for poor answers
– We try to take away marks from good answers
– Intermediate answers are hardest to mark!
• Your strategy should be to make the examiners work
as hard as possible:
– Always attempt every part of your chosen 2 questions!!
• Record all the known facts and try to derive any new facts –
required solution could be among the derived facts
– If you have time at the end, go back and check your answers!!
Dept. of Computing Science, University of Aberdeen
5
Common Questions
• Will there be any essay-type questions ?
– No - but some answers may need a short paragraph
•
Will there be a question on topic X ?
•
Will there be any PHP/Java/JDBC programming
questions ?
•
– Maybe
– No, but you should be prepared to answer general questions
on any of these areas
Do I need to memorise all those SQL statements ?
– You should know how to use common DML/DDL/DCL
statements, especially SELECT, and be able to say what most
other statements do...
• For a “pass” you just need to learn SELECT statements in all its
forms
• For a “first” you should learn all the SQL statements
Dept. of Computing Science, University of Aberdeen
6
More Questions
• Will we have to draw any diagrams ?
– Probably - you will not be required to draw complex
figures, but you might be asked for a sketch to
illustrate your answer...
• Do we need to read more about Spatial
Databases ?
– No - The lecture notes should be enough.
– Same for MySQL and B+Trees
• Can we get solutions for past papers ?
– No - we never give out exam paper solutions: the
main reason is we sometimes want to re-use
questions!!
Dept. of Computing Science, University of Aberdeen
7
Example “Bookwork” Questions
• Describe the difference between file-based and
database systems (3).
• Describe the three major abstractions in EER
modelling (3).
• The various language statements in SQL are often
classified as DDL (Data Definition Language), DML
(Data Manipulation Language), and DCL (Data Control
Language) statements. Give one simple example of
each type of statement (3).
• In SQL, explain the difference between:
DELETE FROM Staff;
and
DROP TABLE Staff; (2).
Dept. of Computing Science, University of Aberdeen
8
Example “Seen” Questions
• Transform the EER fragment shown below into a set of relations
and show the primary and foreign keys of each resulting table
(6).
• Briefly explain how relational database query engines use
relational algebra to find efficient strategies to execute SQL
queries (5).
• The tables below describe the nuts and bolts stocked by a small
hardware shop.
•
(i) Write an SQL query to show the different types of nut stocked (3).
(ii) Write an SQL query to select all the types of nut which will fit a
6mm-thread brass bolt. (5)
In spatial databases, explain with the aid of a sketch how a
bounding box index can help accelerate the search for objects
within a given query window (5).
Dept. of Computing Science, University of Aberdeen
9
Example “Unseen” Questions
• Given the set of related entities with attributes
specified in Figure X, create a hierarchy with them.
Discuss why some entities do not fit into your
hierarchy (6).
• With reference to the hardware shop tables, the
following query selects the suppliers of brass bolts
costing less than 5p each:
SELECT S.Name FROM Supplier S, Bolt B
WHERE S.Id = B.SupplierNo
AND B.Price < 0.05;
Using the usual relational algebra notation, write this
query in relational algebra in three different but
equivalent ways. You may use the given table aliases in
your answer (6).
Dept. of Computing Science, University of Aberdeen
10