Database Design - 1DL300+1DL400, 1MB025 and 1DL029
Fall 2008
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
- 2008-12-12 - Please register and define groups using Course Manager. This will allow you to effectively track the status of your assignments and will allow us to easily manage the registration of assignment grades. See registration instructions on the lab course page./GYG
- 2008-11-07 - Attendance on tutorials and labs is NOT mandatory. You may choose whether or not you want to go./EZ
- 2008-11-04 - The tutorials and labs come in [a, b]-pairs (T1a, T1b; L2a, L2b etc). a and b are exactly the same, so you will choose to go to one of them. /EZ
- 2008-10-24 - 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).
- Functional DBMS Amos II: Manual, Tutorial, Slides, Download
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: erik.zeitler at it dot uu dot se, phone: 471 3390, room 1320
- Gyozo Gidofalvi, course assistant, email: gyozo gidofalvi at it dot uu dot 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 Thursday 2008-12-19 from
??.00 to ??.00 at ??.
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 of 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
- Exam 051221 and suggested solutions
- Exam 060419 and suggested solutions
- Exam 061220 and suggested solutions
- Exam 070822 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) [Erik's lecture Mon Nov 24: PPT | PDF]
- Transactions and concurrency control (as *.pdf)
- Recovery (as *.pdf)
- Physical database design - storage structures and index (as *.pdf)
- Integrity constraints (as *.pdf)
- Database security (as *.pdf)
- Procedural SQL and application interfaces (as pdf in
Swedish)
- Active databases (as *.pdf)
- Introduction to object-oriented and object-relational databases (as *.pdf)
- Functional DBMS Amos II(as Manual, Tutorial, Slides, Download)
- Relational Calculus (as *.pdf)
- Query optimization (as *.pdf)
- Data warehousing
- Multimedia and spatial databases (as *.txt)
Reading instructions (updated
for the 5th ed.)
- Reading instructions for the the book “Fundamentals of Database
Systems”, Elmasri - Navathe, 5th 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 |
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 | Omitted |
29 | All |
30 | Read 30.2. Read 30.3-30.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