Database Design - 1DL116
Spring 2007
Contents
- News
- Literature
- Assignments
- Teachers
- Schedule
- Goal, content and prerequisite
- Organization and examination
- OH slides and compendium
- Reading instructions
- Miscellaneuos information
- F.A.Q.
News
- 2007-07-04 - Exam results are here examresults-070529.txt.
(Sorry for late reporting. We have hade some unavoidable and
unforeseen problem) /KO
- 2007-05-30 - The course evaluation can now be filled in at http://evaluering.ibg.uu.se/it/
using your UpUnet account. / KO
- 2007-05-09 - added sql slides and som FAQ info. /KO
- 2007-03-15 - the course starts 2007-03-26 at 15.15 (3.15pm) in room 1311 /KO
- 2007-03-15 - this page made accesible /KO
Literature
- Elmasri, R. & Navathe, S. B.: Fundamentals of Databases, 5th Edition, Addison-Wesley, 2006 (available e.g. at Akademibokhandeln).
- Padron-McCarthy, T. & Risch, T.: Databasteknik, Studentlitteratur, 2005 (available e.g. at Akademibokhandeln).
- Compendium: Amos II User's Manual.
Assignments
- Instructions and material for the database assignments are here.
Teachers
- Kjell Orsborn, examinator, lecturer, email: kjell.orsborn@it.uu.se, phone: 471 1154, room 1321
- Tore Risch, lecturer, email: tore.risch@it.uu.se, phone: 471 6342, room 1353
- Erik Zeitler, course assistant, email: , phone: 471 3390, room 1320
- Johan Petrini, course assistant, email: johan.petrini@it.uu.se, phone: 471 6345, room 1316
Schedule
No: | Subject: | ENCh: | PRCh: | Tchr: |
L1 | Intro to database terminology | 1, 2 | 1 | KO |
L2 | ER and EER modeling | 3, 4 | 2, 3 | KO |
L3 |
ER example |
|||
L4 | Relational model | 5, 7 | 5, 6 | KO |
L5 | Relational algebra | 6 | 10 | KO |
L6 | Physical database design - normalization | 10 | 11 | KO |
L7 | Normalization example | - | - | KO |
L8 | SQL query language | 8 | 7,8,9 | KO |
L9 | SQL continued | - | - | KO |
L10 | Transactions and concurrency control | 17, 18 | 23, 24 | KO |
L11 | Recovery | 19 | 23, 24 | KO |
L12 | Physical database design - storage structures and indexes | 13, 14 | 21, 22 | KO |
L13 | Physical database design - continued | - | - | KO |
L14 | Security and data integrity | 23, 5 | 12, 13 | KO |
L15 | Procedural SQL and application interfaces | 9 | 14, 20 | TR |
L16 | Active databases | 9, 24.1 | 15 | TR |
L17 | Object databases | 20 | 16 | TR |
L18 | Object-relational databases and AmosQL | 22 | 16 | TR |
L19 | Query optimization | 15 | 25 | TR |
L20 | Query optimization continued | - | - | TR |
L21 | Data warehousing | 28 | 17 | TR |
L22 | Multimedia and spatial databases | 24.3, 29.2-3 | TR | |
LXX | Guest lecture - reserve |
|
||
?? |
Feedback on study questions,
general questions |
|||
T | Tentamen in the "Skrivsalen" hall at Polacksbacken | |
|
Goal,
content and prerequisites
Goal: This course will give principles and practical solutions for storage and retrieval of information using a computer system, particularly for large quantities of data, and with an emphasis both on the use and on the implementation of database management systems.
Content: This
course covers the fundamentals
of the database field. The subject of the database field is how
to use computers to store and manage, usually large
quantities, of data.
If you are going to create a database that models part of the real
world, for example a company or some other organisation, the first step
is usually to make a description of the structure and function of that
part of the
real world, i.e. a conceptual data
model. In this course we will learn, and use, a method called Entity-Relationship modeling for
the
conceptualization phase.
The conceptual model actually has nothing at all to do with computers,
since it is just a description of the world, which could equally well
be used
e. g. by someone who needs to study the internal functioning of the
company.
If you want to create a database in a computer you must first translate
the
conceptual data model to an implementation
data model, which is then used to implement the database. There
are several different classes of implementation data models, and in
this course we will study the relational
and object-oriented
models.
The emphasis is on the
relational model.
A database is a collection
of
data. It is usually managed by a special program or program system,
called
a database management system,
or DBMS for short.
During the
course
we will study how a DBMS is structured and how it functions, among
other
things how it stores its data internally, how it prevents unauthorized
access
to the data, and how it solves the problems that arise when several
users
simultaneously want to look at or change the data.
Since we want to access the database, i. e. ask questions about the
data, change the data, or define which data that are to be stored, the
database management system provides several interfaces, for example a
query language. We will look at some of these interfaces, mostly for
the relational model. The emphasis is on the query language SQL, and also on an object-oriented
dialect of SQL. We will also study some modern database technologies,
such as active and multimedia databases, and we will also have a look
at data warehousing.
Prerequisites: (1) Elementary knowledge about computers including some experience using Unix or Windows. (2) Knowledge about programming in some common programming language. (3) Knowledge about data structures and algorithms, corresponding to the basic course on Data Structures and Algorithms.
Organization and examination
This course is organised as a series of lectures, with an accompanying series of mandatory assignments (labs) to be solved with the help of a computer. The practical assignments are made by the students on their own with support from course assistants.
Most of the content of the course will be covered in the lectures and in the exercises, but it is nevertheless necessary to use your own time to read the course literature and to work with the course material and the assigments.
The requirements for passing the course is to pass the mandatory assignments and the written exam. For the assignment part of the course you will get a final pass or fail grade for the complete set of assignments taken together (individual assignments that are incomplete or failed are normally returned to the student for completion). The grade of the written exam will become the resulting grade for the course, i.e. some of Failed (U), Passed (G) or Passed with honour (VG), for swedish grades (except Failed (U), 3, 4 or 5 for Civilingenjörsprogram). Exchange and masters students can also get ECTS grades.
The written exam (tentamen) is given on Tuesday 2007-05-29 from ??.00 to ??.00 in the "Skrivsalen", Polacksbacken.
You can also check the exam schedule online.Time and place for the next exam is not available at the moment but can be found here, when announced.
Examples on old exams and suggested solutions are available here:
- Exam 021217 and suggested solutions
- Exam 030415 and suggested solutions
- Exam 030520 and suggested solutions
- Exam 030822 and suggested solutions
- Exam 031218 and suggested solutions
- Exam 040414 and suggested solutions
- Exam 040528 and suggested solutions
- Exam 040816 and suggested solutions
- Exam 041220 and suggested solutions
- Exam 050405 and suggested solutions
- Exam 050530 and suggested solutions
- Exam 050729 and suggested solutions
- Exam 050823 and suggested solutions
OH slides and
compendie (to be updated)
- Introduction to database terminology (as *.pdf)
- Introduction to ER and EER-modeling (as *.pdf)
- The relational model (as *.pdf)
- Relational algebra (as *.pdf)
- Normalization (as *.pdf)
- Normalization example (as *.pdf)
- Introduction to SQL ( as *.pdf)
- Transactions and concurrency control (as *.pdf)
- Recovery (as *.pdf)
- Physical database design - storage structures and index (as *.pdf)
- L16: Procedural SQL and application interfaces
- F17: Active databases (as *.pdf)
- F18: Introduction to object-oriented and object-relational databases (as *.pdf)
- L19: Object-relational databases and AmosQL (as *.pdf)
- L18: AmosQL (as *.html)
- L19: Relational Calculus (as *.pdf)
- L19-L20: Query optimization (as *.pdf)
- L21: Data warehousing
- L22: Data warehousing, Multimedia and spatial databases (as *.txt)
- LXX: Multimedia databases + wrap up
Reading instructions (to be
updated)
- Reading instructions for the the book “Fundamentals
of Database
Systems”,
Elmasri - Navathe, 4th ed., 2003.
- Note that supplementary material, such as overhead pictures and compendie, are also part of the course material.
Chapter: | Note: |
1 | All. Read 1.4 and 1.5 on your own. |
2 | All. Read 2.5 on your own. |
3 | All. |
4 | All. Read 4.5 on your own. |
5 | All. |
6 | All. |
7 | All except 7.2. |
8 | All. General knowledge about functionality in SQL is required. You shall be able to formulate a query given a database schema and be able to use constructs covered in lectures and labs. |
9 | All except 9.4.4 and 9.4.5 |
10 | All except 10.2.2, 10.2.3 and 10.2.4. |
11 | Omitted. |
12 | Omitted. |
13 | Read Ch. 13.1-13.3
if you need an introduction to disk storage. Read Ch. 13.3-13.8 to the extension that is required to understand the principles for file-organization and indexing techniques. Skip Ch. 13.9, 13.11. |
14 | Read to the
extension that is required to understand the principles for
file-organization and indexing techniques. Skip Ch. 14.4-14.5. |
15 | All except 15.4, 15.5, 15.7, 15.9, and 15.10 |
16 | Omitted. |
17 | Ch. 17.5 omitted. Note! However, you should know what is meant by the concepts serial, non-serial, and serializable transaction schedule. |
18 | Ch. 18.2, 18.3, 18.6, 18.7 omitted. |
19 | Ch. 19.5, 19.6 omitted. |
20 | Skip 20.2.2,
20.2.3, 20.3.2, 20.6. General knowledge about object-oriented concepts to understand the distributed slides fully. |
21 | Omitted. |
22 | Skip 22,4, 22.5, 22.6 |
23 | Skip 23.3 - 23.6. |
24 | Read 24.1, 24.3 only. |
25 | Omitted. |
26 | Omitted. |
27 | Omitted. |
28 | Read ch. 28. |
29 | Read 29.2. Read 29.3-29.4 on your own. |
- Reading instructions for the the book
“Databasteknik”,
Padron-McCarthy - Risch, Studentlitteratur 2005.
- Note that supplementary material, such as overhead pictures and compendie, are also part of the course material.
Chapter: |
Note: |
1 |
All. |
2 |
All. |
3 |
All. |
4 |
All. |
5 |
All. |
6 |
All. |
7 |
All. |
8 |
All. General knowledge about functionality in SQL is required. You shall be able to formulate a query given a database schema and be able to use constructs covered in lectures and labs. |
9 |
All. |
10 |
All except 10.17,10.18,10.19,10.20. |
11 |
All. |
12 |
All except 12.12,12.13 |
13 |
All. |
14 |
All. |
15 |
All. |
16 |
All. |
17 |
All. |
18 |
18.6 only. |
19 |
Skip |
20 |
All. |
21 |
All. |
22 |
All. |
23 |
All except 24.21,24.22,24.23. |
24 |
??. |
25 |
All. |
26 |
Omitted. |
27 |
Omitted. |
28 |
Omitted. |
29 |
For the interested reader. |
30 |
For the interested reader. |
Miscellaneous information
- The link to the "Fundamentals of Database Systems" book.
- Addison Wesley's Database
Place where you can find material within the database area.
- The link to the "Databasteknik" book at the publisher Studentlitteratur web site. A companion web site also exist for the book including a web-course on databases and a database dictionary in swedish.
F.A.Q.
Q: Is this section used for answering
frequently asked questions?
A: Yes!
Q: Are there any tools that
support ER modeling.
A: Yes, of course there are
numerous tools for ER modeling ranging from simple ones to more
advanced (that support e.g. code generation) and expensive.
An example of a simple tool is ConceptDraw for
Windows and MacOS that has a module for ER diagrams (trial version
works for 30 days).
Additional examples: Dia
(http://www.gnome.org/projects/dia/) for Windows and Linux