B8557 - BASI DI DATI T

Learning outcomes

Upon completion of the course, students will understand the key concepts related to relational databases and database management systems. Specifically, they will: a) have a thorough understanding of SQL and relational algebra and know how to use these languages to extract information of interest; b) understand the principles underlying database design and the Entity-Relationship conceptual model, and how schemas expressed in this model can be represented in various relational forms; c) be able to recognize and enforce data constraints of arbitrary complexity and manage data access through authorization; d) understand the problems associated with concurrent transaction execution and how to avoid them.

Course contents

1. Database Management Systems (DBMSs)
   Information Systems and the role of DBMSs. Basic functionalities of DBMSs: 3-tier architecture, languages, access and concurrency control, fault tolerance.
2. The Relational Data Model
   Basic concepts: domains, attributes, tuples and relations, 1NF relations, null values. Data constraints: on domains, tuples, keys, referential integrity.
3. The Relational Algebra
   Unary operators: selection, projection, and renaming. Binary operators: natural join, union, and difference. Derived operators: intersection, theta-join, and division. Algebra with null values: 3-valued logic, outer join. Expressions and basic equivalence rules.
4. The SQL (Structured Query Language) Language
   Data definition language (DDL) and definition of constraints. The "Select-From-Where" block. Ordering the result. Insert, delete, and update data. Aggregate queries. Nested queries. Definition of views and recursive queries. Granting and revoking authorities and privileges. Triggers. Transactions.
5. Data Base Design
   Data-oriented design. Design steps. Conceptual and logical data models. Basic abstraction primitives.
6. The Entity-Relationship (E-R) Model
   Basic elements: entities, relationships, attributes. Internal and external identifiers. Hierarchies.
7. Conceptual Design (hints)
   Collecting and analyzing requirements. Design strategies. Typical design patterns.
8. Logical Design
   Mapping entities and relationships onto relational tables. Restructuring an E-R schema: dropping hierarchies, managing multi-valued attributes, choosing the principal identifiers. Optimization criteria: Workloads (data volumes and navigation schemas), redundancy analysis, partitioning and merging entities and relationships.
9. Normalization of Relational Schemas
   Functional dependencies and data anomalies. Normal forms (3NF and BCNF). Schema decompositions. Lossless and dependency preservation decompositions. Functional dependencies and E-R schemas.

Module 1 covers all the topics described above in the classroom, while Module 2 is taught in the laboratory and consists of exercises on specific topics covered in class. Solving the exercises requires the use of specific software tools (DBMS client and a design tool).

Readings/Bibliography

Copies of lectures' slides are enough to prepare the exam (available at http://www-db.disi.unibo.it/courses/BD-T/ and on the Virtuale platform).

Any standard textbook on relational DBMS's is valuable to deepen your knowledge on the course topics, in particular:

• P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone: Basi di Dati [https://www.mheducation.it/basi-di-dati-6-ed-con-connect-9788838656545-italy], 6/ed McGraw-Hill, 2023

Teaching methods

481The course is delivered in a classroom setting using slides projected from the instructor's computer. Lectures are complemented by classroom exercises led by the instructor and practical laboratory exercises. Each laboratory exercise addresses a specific topic covered in class, offering exercises that allow students to delve deeper into the material covered in class. Solving the exercises requires the use of various software tools (design tool and DBMS client).

As concerns the teaching methods of this course unit, all students must attend Module 1, 2 (https://site.unibo.it/tutela-promozione-salute-sicurezza/en/training/students-general-and-specific-training) on Health and Safety online

Assessment methods

The exam consists of a laboratory test covering the entire course program. The oral exam is conducted only at the instructor's request. The exam includes several exercises, with a fixed score, which require the use of the software tools practiced in the lab.

• Score is based on a 20-point scale, which is then converted to a 33-point scale (x1.65).
• Passing grade: approximately 10.70 points
• Honors: at least 19 points
• The first two exercises are worth a total of 8 points, and a minimum score of 3 is required to pass.

There are also two multiple-choice questions (without points), with one or more correct answers.

• Passing grade: At least one question must be answered correctly
• Honors: Both questions must be answered correctly

Exam Rules

• During the exam, students are permitted to consult texts, notes, pen drives, etc.
• The use of personal laptops, tablets, smartphones, etc., is NOT permitted. The use of any AI tools is also prohibited.
• The grade earned in a test is valid indefinitely.
• Anyone who takes a test (i.e., reads the test text) loses any previously earned grade.

Teaching tools

Lectures will be based on the material available on the course Web site (http://www-db.disi.unibo.it/courses/BD-T/ ) and on the Virtuale platform.

Links to further information

https://www-db.disi.unibo.it/courses/BD-T/

Office hours

See the website of Paolo Ciaccia