Course leader: Daniel Stamate
Database and other software
to use
- On the departmental
server Igor: PostgreSQL
Database Management Server
- On theWB300 & WB316 lab room
PCs: pgAdmin as a client interface for PosgreSQL running on the
server Igor
- On your laptops: Download
a copy of PostgreSQL 8.4.x
(not
9.x
in order to maintain full compatibility with the departmental server
software) on your laptops and install the software as indicated here.
In addition you will need the SSH Secure Shell software installed for
secure data transfer between your laptops and the departmental
server, available from the Department of Computing or online from
this mirror,
and TextPad (get a licensed copy from the Department). Remember to
bring your laptops with you in all lectures from Week 2 onwards.
Note:
for
full support regarding
the installation of PostgreSQL DBMS, or of any other College
supported software on your laptops, or for any problems regarding
your laptops provided by the College, or your PostgreSQL accounts on
the departmental server, you are to contact/see the System Support
team of the department at 25 St James, Room 1, (email
systems@doc.gold.ac.uk
).
In the table below the bold
written text refers to lecture titles and material. Lab
entries are in normal font.
Chapter numbers
in the slides and in weekly entries (terms 1&2) refer to
Connolly's Database Systems book.
Term 1
|
|
|
|
Week 1 (4 Sept - ) |
Course presentation/organization pdf Introduction to databases [Read Chapter 1 pages 3-30 in 4th edition, OR Chapter 1 pages 3-31 in 5th edition] pdf Database environment [Read Chapter 2 pages 33-60 in 4th edition, OR Chapter 2 pages 35-54 plus Chapter 3 pages 57-68 and Section 3.5 from pages 77-79 in 5th edition] pdf No lab Note: Bring laptops with you in class from next week onwards; have PostgreSQL software installed on (see above the entry Database Software to use) |
|
Week 2 (11 Oct - ) |
The relational model [Read Chapter 3 pages 69-85 in 4th edition, OR Chapter 4 pages 91-107 in 5th edition] pdf Lab: Follow the tutorial on pgAdmin III tool pdf |
|
Week 3 (18 Oct - ) |
SQL: data manipulation part 1 [Read Chapter 5 pages 112-154 in 4th edition OR Chapter 6 pages 133-171 in 5th edition] pdf Lab: Follow the tutorial on pgAdmin and SQL pdf |
|
Week 4 (25 Oct - ) |
SQL: data manipulation part 1 (finished) Lab: SQL data manipulation pdf |
|
Week 5 (1 Nov - ) |
SQL: data manipulation part 2 [Read Chapter 5 pages 112-154 in 4th edition OR Chapter 6 pages 133-171 in 5th edition] pdf Lab: SQL data manipulation pdf |
|
Week 6 (8 Nov - ) |
Reading week - no classes |
|
Week 7 (15 Nov - ) |
SQL: data definition part 1 [Read Chapter 6 pages 157-194 in 4th edition OR Chapter 7 pages 175-209 in 5th edition ; subsection "With Check Option" and section "Transactions" from this chapter are excluded] pdf plus exercise in class Lab: SQL data manipulation pdf |
|
Week 8 (22 Nov - ) |
SQL: data definition part 1 (continued) Lab: SQL - integrity constraints and data definition pdf |
|
Week 9 (29 Nov - ) |
SQL: data definition part 2 [Read Chapter 6 pages 157-194 in 4th edition OR Chapter 7 pages 175-209 in 5th edition ; subsection "With Check Option" and section "Transactions" from this chapter are excluded] pdf Lab: Integrity constraints via Rules in PostgreSQL pdf |
|
Week 10 (6 Dec - ) |
Practical session Lab: Integrity constraints via Rules in PostgreSQL (continued) |
|
Week 11 (13 Dec - ) |
Practical session Lab: Finish remaining lab work from this term |
Term 2 (lecture session themes in bold)
|
Week 1 (17 Jan - ) |
Conceptual DB design: Entity-Relationship Modeling [Read Chapter 11 pages 342-367 in 4th edition OR Chapter 12 pages 321-346 in 5th edition] pdf Lab: Web interface to back end databases - Introduction doc |
|
Week 2 (24 Jan - ) |
Conceptual DB design: Enhanced Entity-Relationship Modeling [Read Chapter 12, pages 371-385 in 4th edition OR Chapter 13, pages 349-363 in 5th edition] pdf Lab: Finish work from week 1 (25 min). Then tackle work from here doc (25 min) |
|
Week 3 (31 Jan - ) |
Logical DB design: converting ER/EER diagrams to relational [Read Chapter 16, pages 461-472 in 4th edition OR Chapter 17 pages 439-450 in 5th edition] pdf Lab: Continue work from week 2 |
|
Week 4 (7 Feb - ) |
Functional dependencies and normal forms [read Chapter 13, pages 387-412 in 4th edition OR Chapter 14, pages 365-389 in 5th edition] pdf Lab: finish work from week 2 (make sure you properly read the explanations in the handout). If you finished and still have time in this session, you may tackle the optional tasks from the lab handout. |
|
Week 5 (14 Feb - ) |
Functional dependencies and normal forms (continued) Lab: Web-Database mini application with data input, retrieval and deletion doc |
|
Week 6 (21 Feb - ) |
Reading week: no classes |
|
Week 7 (28 Feb - ) |
Normalization [read Chapter 14, pages 415-428 excluding 4NF, 5NF in 4th edition, OR Chapter 15, pages 393-405 excluding 4NF, 5NF in 5th edition] pdf Lab: Continue work from lab week 5 |
|
Week 8 (7 Mar - ) |
Normalization (continued) Lab: Finish work from lab week 5 |
|
Week 9 (14 Mar - ) |
Physical Database Design [Read Chapter 17, pages 494-517 in 4th edition OR Chapter 18, pages 471-493 in 5th edition] pdf Lab: Work on the assignment |
|
Week 10 (21 Mar - ) |
Transaction Management [Read Chapter 20, pages 572-591 and 594(from Deadlock)-597 excluding Timestamping methods in 4th edition, OR Chapter 22, pages 569-588 and 591(from Deadlock)-594 excluding Timestamping methods in 5th edition] pdf Lab: Work on the assignment |
|
Week 11 (28 Mar - |
Data recovery [Read Chapter 20, pages 605(from Database recovery)-615 excluding the section Advanced Transaction Models in 4th edition OR Chapter 22, pages 602(from Database recovery)-611 excluding the section Advanced Transaction Models in 5th edition] pdf Lab: Exercises |
Revision session pdf
Additional
resources:
PostgreSQL
Tutorial
PostgreSQL
Documentation