28027 - Information Systems T

Learning outcomes

Knowledge of models and tools for organizing, managing and designing relational databases.

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.

Readings/Bibliography

Copies of lectures' slides are enough to prepare the exam (available at http://www-db.disi.unibo.it/courses/SI-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

Lectures cover the basic issues related to all course contents. Guided class and lab practice will allow the student to work out the theoretical materials by applying them to problems of various complexity degree.

As concerns the teaching methods of this course unit, all students must attend Module 1, 2 [https://www.unibo.it/en/services-and-opportunities/health-and-assistance/health-and-safety/online-course-on-health-and-safety-in-study-and-internship-areas] on Health and Safety online

Assessment methods

The exam consists of a practical test. With the help of lab tools, the student will be required to solve exercises on database languages, conceptual design and logical design.

Teaching tools

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

Links to further information

http://www-db.disi.unibo.it/courses/SI-T/

Office hours

See the website of Paolo Ciaccia