Curricular Unit:Code:
Databases832BDAD
Year:Level:Course:Credits:
3UndergraduateComputer Systems Engineering6 ects
Learning Period:Language of Instruction:Total Hours:
Portuguese/English78
Learning Outcomes of the Curricular Unit:
This course introduces the classical database management system’s architecture, studies logical and physical database models, designs and optimizes database models, explains indexing, query languages, describes concurrency problems and algorithms, and introduces recovery.
At the end of the module a student will be able to:
• Describe the classic DBMS architecture and its main components
• Analyze problems and design database models, using the ER model
• Use functional dependencies to normalize database models
• Physically design databases
• Understand and use the Relational Algebra as a query language
• Write SQL queries
• Identify storage models and optimize usage of indexes
• Identify and analyze the implications of concurrency control
• Explain database recovery and describe the techniques to use
Syllabus:
1. Introduction
1,1 DBMS reference archictecture
1.2 Components of a DBMS
1.3 Data models, transactions and recovery
2. The Relational Model of Data
2.1 Relations, constraints
2.2 Functional dependencies
2.3 Normalization, normal forms
3. Schema design
3.1 The Entity-Relationship Model
3.2 Mapping to the Relational Model
4. Relational Algebra
4.1 Operators. Rules
4.2 Queries
5. SQL
5.1 Schema definition. Constraints
5.2 Data insertion, modification and querying
5.3 Joins. Grouping and aggregate functions
5.4 Sub-selects and predicates
5.5 Set operations
5.6 Views.
6. Storage models and file organization
6.1 The NSM, DSM and PAX models.
6.2 Indexing techniques
6.3 Trees and hashing
7. Concurrency control
7.1 Transactions. Schedules, conflicts
7.2 Serialization
7.3 Concurrency control algorithms. Locking, Optimistics and Multiversion algorithms
7.4 Isolation levels.
8. Recovery techniques
8.1 Recoverable schedules
8.2 Recovery algorithms.
Demonstration of the Syllabus Coherence with the Curricular Unit's Objectives:
The syllabus addresses the main components of a modern DBMS. The learning objectives imply an understanding of how these components work and interact. Being an introductory DBMS course, the topics are organized around the main DBMS components. The analysis and design objectives are addressed in topics 2 and 3. Topic 5 (SQL) allows the students to manipulate programmatically a DBMS.
Teaching Methodologies (Including Evaluation):
The methodology of teaching and learning is expository, and demonstrative. Students install in their laptop a DBMS, which they can use during the classes to test and experiment. Labs are geared towards practical exercises, and imply most tasks a database administrator would perform. Students are in charge of installation, configuration, administration and maintenance of their DBMS.
The assessment includes:
• Two written tests (25% each)
• Submission of the proposed assignments (50%)
Demonstration of the Coherence between the Teaching Methodologies and the Learning Outcomes:
The proposed methodologies are consistent with the objectives set for the course since they rely on the understanding of DBMS fundamentals, knowledge of internal components, and usage of the API, through SQL. Problem-solving, including conceptual design, but also configuration, programming and database administration allow the students to acquire the desired competencies.
Reading:
1. Feliz Gouveia, Bases de Dados – Fundamentos e Aplicações, FCA, Lisboa, 2021.
2. Chris J. Date, An Introduction to Database Systems, 8º ed., Addison-Wesley, USA, 2003.
3. Luís Damas, SQL: Structured Query Language, 14ª edição, FCA, Lisboa, 2017.
4. Jeffrey Ullman, Jenniffer Wisdom, A First Course in database Systems, Prentice-Hall, 2014.