103222 - DATABASE DESIGN

Subject type: Mandatory

Coordinator: Vladimir Bellavista Parent

Trimester: Second term

Credits: 4

Academic year: 2025

Teaching course: 2

• Catalan

This subject is taught in Catalan. However, the bibliography and part of the content and tools may be in Spanish or English.

The general objective of the subject is to deepen the knowledge of databases and database management systems (DBMS). The course goes through all the stages of database design - conceptual, logical, physical, external - and studies the advanced features of SQL.  

The classroom (physical or virtual) is a safe space, free of sexist, racist, homophobic, transphobic and discriminatory attitudes, either towards students or towards teachers. We trust that together we can create a safe space where we can make mistakes and learn without having to suffer prejudice from others. 

Content 1: The database design process

Dedication:

• large group activities: 10 hours
• seminars: 6 hours
• small group activities: 0 hours
• independent learning: 34 hours

Description: 

Introduction to the process of designing and implementing a database from the capture of requirements to the generation of the database, through the stages of conceptual design, logical design and physical design.

Conceptual data modeling and standardization tools and techniques are used for the conceptual design stage and relational data modeling for logical and physical designs. 

Topics:

1.1 Introduction to database design

1.2 Conceptual design: from the requirements to the conceptual data scheme

1.3 Logical design: from the conceptual scheme to the logical scheme

1.4 Physical design: database generation

1.5 Normalization

Content 2: Development and use of databases with advanced SQL

Dedication:

• large group activities: 8 hours
• seminars: 2 hours
• small group activities: 8 hours
• independent learning: 32 hours

Description: Deepening the knowledge of SQL to implement the logical and physical design of a relational database

Topics: 

2.1 Data Recovery with Advanced SQL: Functions, Operators, Table Combinations, Set Operations, and Results Sorting

2.2 Time management in advanced SQL

2.3 Advanced retrieval of grouped data

2.4 Subconsultations

2.5 Hierarchical recoveries

2.6 DML statements for large data sets

2.7 DDL statements for managing database schema objects

2.8 Other objects: views, indexes, synonyms

Qualification system (evaluation)

Each of the two topics (Design and advanced SQL) account for 50% of the subject's grade.

You need a grade above 5 in each subject to pass the subject and be able to pass the average.

Attendance and active participation in practice labs and seminars is mandatory for each topic to be evaluated.

Continuous evaluation:

TOPIC 1. DATABASE DESIGN:

First block (66,66%) Conceptual design: • Attendance and participation in seminars 2 and 3: 0,5+1 points • Previous work: 0,5 points • Individual assessment: 8 points

Second block (33,33%) Standardization, logical and physical design: • Attendance and participation in seminars 3 and 4: 1+1 points. • Individual evaluation: 8 points

TOPIC 2. ADVANCED SQL:

First block (33,33%): SQL queries and basic DML statements: • Attendance and participation in seminar 1 and practical session 1: 1+1 points • Individual evaluation of basic DML queries and statements (SQL laboratories 1 and 2): 8 points

Second block (33,33%): DDL statements and advanced constraint management: • Attendance and participation in practical sessions 2 and 3: 1 point • Previous work and participation: 1 point • Individual evaluation of DDL statements and advanced constraint management (laboratories 2 and 3): 8 points 

Third block (33,33%): Advanced data retrieval - queries - with SQL and working in SQL with other objects (views, synonyms, sequences, indexes): • Attendance and participation in practical session 4: 0,5+0,5 points • Work carried out throughout the course on the student's schema in the database: 1 point • Individual evaluation of advanced queries and working with other database objects (laboratory 4): 8 points

Recovery:

If one or both of the two subjects do not obtain a grade equal to or higher than five in continuous assessment, it is necessary to take the retake exam for the part or parts that were not passed. 

• Retake exam for topic 1: conceptual, logical and physical database design 

• Topic 2 retake exam: advanced programming with SQL 

The result of the assessment in the retake exam is only PASS(5) or FAIL.

Only those who have attended all continuous assessment activities can take the retake.
 

Rules for carrying out practices and seminars

Is compulsory attendance in practice sessions and seminars. 

The five individual assessment questionnaires are mandatory. If they are not completed, they will be evaluated as 0.

Any form of academic fraud will be sanctioned in accordance with the center's assessment regulations. If signs of fraud are detected, including the improper use of generative artificial intelligence tools, the subject's teaching staff may call the student for an individual interview with the aim of verifying their authorship.

Elmasri, R .; Navathe, SB "Fundamentals of Database Systems, Global Edition". Seventh Edition. Harlow, Essex: Pearson, 2017. ISBN 978-1-292-09761-9.

Silberschatz, Abraham; Korth, Henry; Sudarshan, S. Database Systems Concepts. Seventh Edition. McGraw-Hill-Education, 2019. ISBN 978-1260084504.

Silberschatz, Abraham; Korth, Henry; Sudarshan, S. "Fundamentals of Databases". sixth edition Madrid-McGraw-Hill-Education, 2014. ISBN 978-84-481-9033-0.

Date, CJ “An Introduction to Database Systems”. Seventh edition. Addison Wesley Longman, 2001. ISBN 978-0201787221 / Eighth edition. Person, 2003 ISBN 978-0321197849

Date, CJ "Introduction to database systems". Seventh edition México-Pearson Education, 2001. ISBN 9684444192