Database 1
Introduction
- Prof. dr. Paul M.E. De Bra
- Dept. of Computer Science
- Eindhoven Univ. of Technology
Why do we study databases?
- We live in an information-rich society:
- Databases are everywhere: in your telephone (address book), your television (presets), your VCR (presets and timer program), the train ticket vending machines (stations, routes and prices), shops and supermarkets (inventory and prices), city hall, tax office, bank, etc.
- Behind the user interfaces for these databases is software to manipulate data: database systems. As computer scientists we must be able to configure and optimize databases and manipulate the data stored in the databases.
What is Databases 1 about?
- Database design / modeling:
- analyzing properties of a database
- improving / optimizing database schemes
- Data manipulation:
- how to translate a question from natural language to a formal query language
- how to read and understand queries
Course Organization
- Lecture: only week 1
- Colstruction (combination of lecture+instruction): weeks 2 to 9
- Labsessions: weeks 1 to 9
- Homework: weeks 1 to 9
- (there are small deviations due to holidays; these will be announced during the lecstructions)
- Attending colstructions and labsessions and doing homework is highly advisable if you wish to pass the exam.
Effort calculation
- Databases 1 is 4 ects, or 4x28=112 hours:
- 9 weeks of 2 hours lecture/colstr. = 18 hours
- 9 weeks of 2 hours labsession = 18 hours
- 9 weeks of 3 hours studying = 27 hours
- 9 weeks of 5 hours homework = 45 hours
- 3 hours of exam
- Total: 18+18+27+45+3 = 111 hours.
- Conclusion: we are not asking too much of you if you need to spend 3 hours of study and 5 hours on homework each week.
The exam
- The exam consists of:
- A short “pre-exam” with 3 easy questions. If you make any mistake in any of these questions you score 1/10 (and we ignore the rest of your exam).
- A “main” exam consisting of exercises. The exam is “open book”. (all written material allowed, no equipment allowed)
- When you are present (and registered) at 6 labsessions and hand in 6 homeworks you are exempt from the “pre-exam”.
Database Design (mostly chapter 7)
- We assume that you already master:
- Translation of an information system description (in natural language) to an E-R schema).
- Addition of cardinality constraints to the E-R schema to express constraints from the description.
- Transformation of an E-R schema to a relational schema (with “tables”).
- Identification and removal of redundant tables.
- Definition of (primary) keys for the tables.
Constraints
- Two reasons for using (and studying) constraints:
- Avoiding (detecting) data entry errors.
- possible but wrong data not detected (transfer 120₠ instead of 130₠, give student a wrong mark).
- impossible data detected (illegal bank account number or student number).
- Avoid database structures that lead to redundant storage of information.
- e.g. because of a derived attribute in an E-R schema.
Example of redundancy
Decomposition to avoid redundancy
Inference rules
- Checking that a database instance (a table) satisfies constraints is expensive:
- We try to change a database scheme so that constraints are automatically satisfied;
- We try to verify the constraint on a small amount of data;
- We try to avoid verifying too many constraints: some constraints are a logical consequence of others;
- We try to find a minimal set of needed constraints.
Schema modification
- Dealing with new constraints (or with removal of constraints):
- Complete redesign: E-R schema design and translation to tables new schema may differ a lot from the old one; lot of work adapting interfaces
- Small changes to the schema of tables need to verify the new design; little work on the interfaces
Preparation for labsession 1
- Study chapter 7 of the database book, up to and including section 7.3.1.
- 7.1 talks about “first normal form”: having atomic values
- 7.2 explains pitfalls in database design: it describes constraints and redundancy
- 7.3.1 defines functional dependencies, the most important constraints in Databases 1.