Database Design

You are here

Home » Studies » Bachelor's degrees » Bachelor Degree in Informatics Engineering » Curriculum » Syllabus » Database Design
Credits
6
Types
Specialization compulsory (Software Engineering)
Requirements
  • Prerequisite: BD
  • Precorequisite: IES
Department
ESSI
Web
https://learnsql2.fib.upc.edu/moodle/
This course trains students in skills needed to design and manage databases, evaluating the different alternatives in the context of the company. The course deals with concepts of generic relational databases (applicable to both operational and decisional environments). The skills taught are essential to tackle the tasks of a database designer or a database administrator.

Teachers

Person in charge

  • Alberto Abello Gamazo ( )
  • Xavier Burgués Illa ( )

Others

  • Vicent Picornell Alandete ( )

Weekly hours

Theory
2
Problems
0
Laboratory
2
Guided learning
0
Autonomous learning
6

Competences

Technical Competences

Common technical competencies

  • CT2 - To use properly theories, procedures and tools in the professional development of the informatics engineering in all its fields (specification, design, implementation, deployment and products evaluation) demonstrating the comprehension of the adopted compromises in the design decisions.
    • CT2.2 - To demonstrate knowledge and capacity to apply the characteristics, functionalities and structure of data bases, allowing an adequate use, design, analysis and implementation of applications based on them.
    • CT2.4 - To demonstrate knowledge and capacity to apply the needed tools for storage, processing and access to the information system, even if they are web-based systems.
  • CT7 - To evaluate and select hardware and software production platforms for executing applications and computer services.
    • CT7.2 - To evaluate hardware/software systems in function of a determined criteria of quality.

Technical Competences of each Specialization

Software engineering specialization

  • CES1 - To develop, maintain and evaluate software services and systems which satisfy all user requirements, which behave reliably and efficiently, with a reasonable development and maintenance and which satisfy the rules for quality applying the theories, principles, methods and practices of Software Engineering.
    • CES1.1 - To develop, maintain and evaluate complex and/or critical software systems and services.
    • CES1.2 - To solve integration problems in function of the strategies, standards and available technologies
    • CES1.5 - To specify, design, implement and evaluate databases.
    • CES1.6 - To administrate databases (CIS4.3).
  • CES3 - To identify and analyse problems; design, develop, implement, verify and document software solutions having an adequate knowledge about the current theories, models and techniques.
    • CES3.2 - To design and manage a data warehouse.

Transversal Competences

Appropiate attitude towards work

  • G8 [Avaluable] - To have motivation to be professional and to face new challenges, have a width vision of the possibilities of the career in the field of informatics engineering. To feel motivated for the quality and the continuous improvement, and behave rigorously in the professional development. Capacity to adapt oneself to organizational or technological changes. Capacity to work in situations with information shortage and/or time and/or resources restrictions.
    • G8.3 - To be motivated for the professional development, to face new challenges and the continuous improvement. To have capacity to work in situations with a lack of information.

Third language

  • G3 [Avaluable] - To know the English language in a correct oral and written level, and accordingly to the needs of the graduates in Informatics Engineering. Capacity to work in a multidisciplinary group and in a multi-language environment and to communicate, orally and in a written way, knowledge, procedures, results and ideas related to the technical informatics engineer profession.
    • G3.1 - To understand and use effectively handbooks, products specifications and other technical information written in English.

Objectives

  1. Being able to design and obtain information from multidimensional databases
    Related competences: CT2.2, CES1.1, CES1.5, CES3.2, CT7.2, CT2.4,
    Subcompetences:
    • Being able to move a UML multi-dimensional diagram to a relational star-join scheme.
    • Being able to get the multi-dimensional query corresponding to a statistical table using the SQL-99 specific sentences
  2. Being able to implement a logical design onto a physical design analysing and choosing the best mechanism to include integrity constraints in the DB.
    Related competences: CT2.2, CES1.1, CES1.5, CT7.2,
  3. Being able to decide which materialized views must be defined according to the expected operations
    Related competences: CT2.2, CES1.1, CES1.5, CT7.2,
  4. Being able to decide which indexes to be defined according to the expected operations
    Related competences: CES1.1, CES1.5, CT2.4,
    Subcompetences:
    • List the types of indexes that can be defined and the cost of each operation (insertion, deletion, single result query, several results query) for each index
    • Being able to replicate the execution of each operation on each index
  5. Being able to list the optimization options regarding policies, methods and time, together with advantages, disadvantages and application conditions.of each one.

    Related competences: CES1.5, CES1.6,
  6. Being able to obtain the access plan for a query according to given optimization criteria
    Related competences: CT2.2, CES1.1, CES1.6, CT7.2,
    Subcompetences:
    • Being able to estimate the size of the intermediate and final results of a query and the cost of solving each step
    • Being able to optimize a syntax tree
  7. Being able to replicate the execution of the algorithms involved in a process tree and to estimate their cost
    Related competences: CT2.2, CES1.1, CES1.5, CES1.6,
  8. Being able to list the main options and parameters that affect concurrency and to justify the effect of modifying their values
    Related competences: CT2.2, CES1.6,
    Subcompetences:
    • Being able to replicate the concurrent execution of transactions taking the isolation level into account
  9. Being able to list the main options and parameters that affect the recovery of the database and to justify the effect of modifying them.
    Related competences: CT2.2, CES1.6,
    Subcompetences:
    • Being able to replicate the execution of an operation in the DBMS
  10. Being able to list the subsystems of an organization and the information generated and needed by them.
    Related competences: CES3.2,
    Subcompetences:
    • Distinguish between operational and decision-making systems.
  11. Being able to enumerate the stages of database development and the result of each one
    Related competences: CES1.1, CES1.2, CES1.5, CES3.2,
  12. Being able to detect and correct defects in a logical design.
    Related competences: CT2.2, CES1.1, CES1.5, CT7.2,
    Subcompetences:
    • Ability to detect problems of satisfiability, liveliness, redundancy and reachability and to recognize query containment.
    • Ability to determine the normal form in which the logical design of an operational information system is and to normalize it to the required level
    • Being able to replicate the execution of analysis algorithm
    • Being able to decide on the possibility of aggregating data.
  13. Being able to perform the logical design of an IS database.
    Related competences: CES1.1, CES1.5,
    Subcompetences:
    • Being able to build SQL queries considering the existence of null values ​​and using outer joins when needed.
    • Being able to perform the logical design starting from a conceptual schema expressed in UML considering the consequences of null values
  14. Ability to acquire concepts and skills in an autonomous way.
    Related competences: G3.1, G8.3,

Contents

  1. Introduction
    Concepts related to design and administration.
    Life Cycle of a DB. Alternatives.
    Decisional and operational DB.
  2. Decision suport systems
    Data Warehouses.
    Multidimensional Databases (OLAP tools)
  3. Logical design. The transition to the relational model.
    The decisional case.
    The operational case starting from UML.
  4. Correctness of design
    Normalisation.
    Validation.
  5. Physical Design
    Materialized views.
    Constraints.
  6. Optimization
    Semantic opt.
    Syntactic opt.
    Physical opt.
    Volume estimation.
  7. Estructuras de acceso
    B +.
    Cluster.
    Hash.
  8. Algorithms
    Selection.
    Sorting.
    Join.
    Projection.
  9. Advanced indexing
    Multiatribute.
    Bitmaps.
    Index-only query answering.
    Join indexes.
  10. Administration
    Definitions, objectives and tasks.
    Access Plan.
    Workload optimization.
    Transactions.
    Recovery.
    Concurrence.
    Virtual and physical spaces
  11. Beyond the relational model
    Basic knowledge on NOSQL databases. Differences with respect to the relational model. Main kinds of NOSQL BD .

Activities

Activity Evaluation act


Introduction

Students must prepare their computer according to one of the alternatives proposed to carry out the activities that will arise during the course. Basically, they must have access to Moodle, and Oracle.
Objectives: 14
Theory
1h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
1h

Study of the introductory concepts

Studying exposed contents and proposing exam questions using the glossary Moodle module
Objectives: 10 11
Contents:
Theory
1h
Problems
0h
Laboratory
1h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to decision support systems

Studying exposed contents and self-study materials. Resolving exercises and proposing exam questions using the glossary Moodle module
Objectives: 1 14
Contents:
Theory
1h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
15h

Study of concepts related to logical design

Studying exposed contents and self-study materials. Resolving exercises and proposing exam questions using the glossary Moodle module
Objectives: 13
Contents:
Theory
2h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
12h

Study of concepts related to the correctness of the design

Studying exposed contents and proposing exam questions using the glossary Moodle module
Objectives: 12
Contents:
Theory
2h
Problems
0h
Laboratory
3h
Guided learning
0h
Autonomous learning
5h

SQL training

The session is identical to the advanced SQL session but this one does'nt have any impact on the evaluation.
  • Autonomous learning: Students must analyze the session questionnaire and study the related theory
Objectives: 13
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 1: Advanced SQL Queries

Students, in pairs, must answer a Moodle questionnaire that is corrected instantly by LEARN-SQL. Every time that students send an answer to a question receive a rating. Students may decide to submit new answers in order to improve the previous ones. Every new submission yelds a penalty. Students must also answer some questions in writing. The teacher will review the written answers and the mark provided by LEARN-SQL will be refined accordingly. During the previous week, students will solve a remote training Moodle questionnaire with the same partner.
Objectives: 13
Week: 3
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to physical design

Studying exposed contents and proposing exam questions using the glossary Moodle module
Objectives: 2 3
Contents:
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
4h

Study the concepts related to optimization

Studying exposed contents and self-study materials. Resolving exercises and proposing exam questions using the glossary Moodle module
Objectives: 6 5
Contents:
Theory
1h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
7h

Translation and normalization training

The session is identical to the translation and normalization session but this one does'nt have any impact on the evaluation.
  • Laboratory: The session is identical to the translation and normalization session but this one does'nt have any impact on the evaluation.
  • Autonomous learning: Students must analyze the session questionnaire and study the related theory
Objectives: 13 12
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 2: relational translation and normalization

Students, in pairs, must answer a Moodle questionnaire that is corrected instantly by LEARN-SQL. Every time that students send an answer to a question receive a rating. Students may decide to submit new answers in order to improve the previous ones. Every new submission yelds a penalty. Students must also answer some questions in writing. The teacher will review the written answers and the mark provided by LEARN-SQL will be refined accordingly. During the previous week, students will solve a remote training Moodle questionnaire with the same partner.
Objectives: 13 12
Week: 5
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to access structures

Studying exposed contents and proposing exam questions using the glossary Moodle module
Objectives: 4
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to algorithms

Studying exposed contents and proposing exam questions using the glossary Moodle module
Objectives: 7
Contents:
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
3h

Study of concepts related to advanced indexing

Studying exposed contents and proposing exam questions using the glossary Moodle module
Objectives: 4
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Correctness and views training

The session is identical to the correctness and views session but this one does'nt have any impact on the evaluation.
  • Laboratory: The session is identical to the correctness and views session but this one does'nt have any impact on the evaluation
  • Autonomous learning: Students must analyze the session questionnaire and study the related theory
Objectives: 12 2 3
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 3: correctness and views

Students, in pairs, must answer a Moodle questionnaire that is corrected instantly by LEARN-SQL. Every time that students send an answer to a question receive a rating. Students may decide to submit new answers in order to improve the previous ones. Every new submission yelds a penalty. Students must also answer some questions in writing. The teacher will review the written answers and the mark provided by LEARN-SQL will be refined accordingly. During the previous week, students will solve a remote training Moodle questionnaire with the same partner.
Objectives: 12 2 3
Week: 7
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to administration

Studying exposed contents and self-study materials. Resolving exercises and proposing exam questions using the glossary Moodle module
Objectives: 9 8 14
Contents:
Theory
6h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
9h

Indexes and cost of queries including joins training

The session is identical to the indexes and cost of queries including joins session but this one does'nt have any impact on the evaluation.
Objectives: 4 7
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 4: indexes and cost of queries including joins

Students, in pairs, must answer a Moodle questionnaire that is corrected instantly by LEARN-SQL. Every time that students send an answer to a question receive a rating. Students may decide to submit new answers in order to improve the previous ones. Every new submission yelds a penalty. Students must also answer some questions in writing. The teacher will review the written answers and the mark provided by LEARN-SQL will be refined accordingly. During the previous week, students will solve a remote training Moodle questionnaire with the same partner.
Objectives: 4 7
Week: 10
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Access structures training

The session is identical to the acces structures session but this one does'nt have any impact on the evaluation.
  • Autonomous learning: Students must analyze the session questionnaire and study the related theory

Theory
0h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Session 5: access structures

Students, in pairs, must answer a Moodle questionnaire that is corrected instantly by LEARN-SQL. Every time that students send an answer to a question receive a rating. Students may decide to submit new answers in order to improve the previous ones. Every new submission yelds a penalty. Students must also answer some questions in writing. The teacher will review the written answers and the mark provided by LEARN-SQL will be refined accordingly. During the previous week, students will solve a remote training Moodle questionnaire with the same partner.
Objectives: 4 6 7
Week: 12
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Workload optimization training

The session is identical to the workload optimization session but this one does'nt have any impact on the evaluation.
  • Laboratory: The session is identical to the workload optimization session but this one does'nt have any impact on the evaluation.
  • Autonomous learning: Students must analyze the session questionnaire and study the related theory
Objectives: 4 6 7
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 6: workload optimization

Students, in pairs, must answer a Moodle questionnaire that is corrected instantly by LEARN-SQL. Every time that students send an answer to a question receive a rating. Students may decide to submit new answers in order to improve the previous ones. Every new submission yelds a penalty. Students must also answer some questions in writing. The teacher will review the written answers and the mark provided by LEARN-SQL will be refined accordingly. During the previous week, students will solve a remote training Moodle questionnaire with the same partner.
Objectives: 4 6 7
Week: 14
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to NOSQL

Studying exposed contents and self-study materials. Resolving exercises and proposing exam questions using the glossary Moodle module
Objectives: 10 13 11 14
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
3h

Examination

Some questions may be drawn from the set of questions proposed by students voluntarily through the mechanism of the glossary.
Objectives: 10 11 2 4 5 9 8
Week: 15 (Outside class hours)
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
0h

Review of laboratory sessions

Students may ask for explanations on the evaluation or submit claims following the established guidelines .

Theory
0h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
0h

Examination review

Students may ask for explanations on the evaluation or submit claims following the established guidelines .

Theory
0h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
0h

Normalization II (beyond BCNF)

Studying exposed contents and proposing exam questions using the glossary Moodle module. Application in the laboratory.
Objectives: 13 12
Contents:
Theory
2h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
1h

Teaching methodology

Lecture hours would be mainly devodeted to the resolution of doubts and problems. It will be assumed that students went through all the material available in the virtual campus a priori.

In the lab sessions the teacher opens a questionnaire on the virtual campus that students must solve in pairs. In the days before the session, the same couples have had the opportunity to work in a questionnaire similar to the one of the laboratory session. They can do so from anywhere with internet access getting feedback from the automatic correction tool. In addition, the same module allows the partners to discuss about the exercises without having to meet physically.

Evaluation methodology

Final mark = 25% ExP+ 25%ExF + 10%P + 40% L

ExP = mid-term exam grade.
ExF = final exam grade.
P= weighted average grade of problems done in the classroom and questionnaires done at home
L = weighted average grade of lab sessions

Calculation of L:
1) Multiply the grade from activity by a weight equal to 1, 2, 4 or 8 (depending on the content of the corresponding activity)
2) Divide the sum of these values by the sum of weights assigned minus 4

The rating of the generic skill "right attitude to the work" will be A (passed with excellence), B (competence level exceeded the expected), C (competence at basic level) or D (failed to achieve the competence). This grade is computed as the combination of P and the attitude evaluation made by the teacher (participation in activities during classes).

The competence "third language" is evaluated by means of the grade in activities based on material written in English.

Bibliography

Basic:

Complementary:

Previous capacities

Being able to list the software engineering process steps
Ability to understand UML class diagrams
Ability to create, query and manipulate databases using SQL