Database Modeling and Design
About the CourseIn this course, you will achieve expertise in two topics, database modeling and database design. Databases are the fundamental technology adopted in all information systems worldwide, since they allow organizing, storing and retrieving digital data that are useful for both personal activities and decisions, and for core activities of enterprises and public administrations. Digital data managed in databases are at the basis of a huge amount of digital services and apps we use every day.
Referring to the modeling capability, the course introduces two models widely adopted to represent databases, the Relational model and the Entity relationship model. The Relational model is used by the majority of software systems that manage databases, while the Entity relationship model, that is provided of an user friendly diagrammatic representation, is adopted in the early stage of the database design process.
Referring to the design capability, the database design process starts usually with a natural language or pictorial description of user requirements; it is worthwhile to represent initially such description using the Entity Relationship model, that is understandable by users, and provides a rich set of semantic characteristics of data. The result of the design activity at this stage is a database schema. After that, the design can proceed translating the Entity Relationship schema into a logical schema in the relational model. Such translation has to be performed using several practical rules that have the goal to optimize the relational schema w.r.t. queries and transactions performed by users. Once obtained the logical schema, one can add to relations (last step) data that represent, in our case, the students, courses and exams.
The course assumes you are familiar with basic mathematical concepts such as set, subset, table, function, and domain of a function.
Database Modeling and Design
Nel corso diventerete esperti in due tematiche, la modellazione di basi di dati e il progetto di basi di dati. Le basi di dati sono la tecnologia fondamentale utilizzata in tutti i sistemi informativi delle organizzazioni pubbliche e delle imprese in tutto il mondo, e questo perché esse permettono di organizzare, memorizzare, interrogare dati digitali utili nelle attività “core” delle pubbliche amministrazioni e delle imprese. I dati digitali estratti dalle basi di dati sono utilizzati in un innumerevole insieme di servizi e applicazioni che usiamo ogni giorno. Riguardo alla capacità di modellare basi di dati, il corso introduce due modelli diffusamente adottati nelle basi di dati, il modello Entità Relazione, che è dotato di una rappresentazione grafica di facile comprensione, e il modello Relazionale, che rappresenta i dati mediante tabelle, e che è adottato nei Sistemi di Gestione di Basi di Dati più diffusi. Riguardo alla capacità di progettare basi di dati, viene descritto nel corso come si fa a rappresentare una specifica descritta in linguaggio naturale (che descrive, ad esempio, studenti, corsi ed esami) in termini di un diagramma di concetti nel modello Entità Relazione. Viene poi insegnato a tradurre il diagramma Entità Relazione in un insieme di tabelle nel modello relazionale, che può essere alimentato dai dati di interesse (ad es. i singoli studenti, i singoli corsi ed esami sostenuti).
By the end of this course, you will be able to:
By the end of this course, you will be able to:
RequisitiBasic knowledge of set theory, basic knowledge of mathematical functions and relations.
Libri di testo e letture consigliate
- Carlo Batini - Transcription of video lessons of the Eduopen Course - This text is a
transcription of the video lessons. It can be useful for those of you that, after watching at the video, need for a moment of further in depth analysis of the concepts presented. It is also useful for those of you that do not possess entirely spoken English language, and need for a written version of lessons to be examined comfortably. It can also be used independently form the video version.
- Carlo Batini, Stefano Ceri, Sham B. Navathe - Conceptual Database Design: an Entity Relationship Approach - ISBN-10: 0805302441 • ISBN-13: 9780805302448 ©1992, Addison-Wesley, 470 pp, available in the e-market.
- Paolo Atzeni, Stefano Ceri, Stefano Paraboschi and Riccardo Torlone - Database Systems Concepts, Languages and Architectures - McGraw-Hill Book Company 1999, ISBN 0-07-709500-6 Freely available at http://dbbook.dia.uniroma3.it/
Formato del Corso
The course consists of five Sections. Each Section consists of 4 to 9 video-lectures. Each video-lecture consists of a methodology video, during which several exercises are proposed. When an exercise is proposed, you can pause the video, and once you have conceived a solution, you can watch the video and compare the solution with the one provided. If you want, you can also use the booklet, that is an accurate transcription of the video lessons. When an exercise is proposed, you can conceive a solution, and then you turn the page and compare the solution in the booklet with your solution. The whole set of concepts that are discussed in the course can be divided into three blocks: 1) Basic concepts, 2) Models and 3) Design methods. Let us go in more depth for specific parts. Part 1 - Introduction to Databases: in this section you will learn the basic concepts on database modeling and design, with specific reference to the two models dealt with in the course, the Relational model and the Entity relationship model, and the two phases of database design, Conceptual design and Logical design; Part 2 – Entity Relationship model: in this section you will learn the basic constructs of the Entity relationship model such as entity, relationship, attribute, is-a relation and generalization. You will also learn the diagrammatic representations of constructs, and you will start to design simple schemas and related diagrams.
Regole per l'ottenimento degli Attestati e per sostenere gli Esami
|1.1 Introduction to Data, Information, Organizational System, Information System and Computer Structure (9.44)|
|1.2 Traditional Data Management and Modern Data Management with Databases first-part (7.30)|
|1.2 Traditional Data Management and Modern Data Management with Databases second-part (16.22)|
|1.3 Database Models (19.22)|
|1.4 Course Organization and Parts (17.49)|
|2.1 Introduction to the Entity Relationship Model (7.57)|
|2.2 Entity and Attribute of Entity (18.42)|
|2.3 Relationships and Attributes of Relationships (22.50)|
|2.4 Exercise on Entities and Relationships (11.36)|
|2.5 Is-a Hierarchies and Generalizations (22.15)|
|2.6 Design a not-so-simple ER Schema (20.55)|
|2.7 Minimum and maximum cardinalities of entities in relationships (18.28)|
|2.8 Identifiers first-part (15.07)|
|2.8 Identifiers second-part (19.42)|
|3.1 Introduction to the Relational model first-part (16.41)|
|3.1 Introduction to the Relational model second-part (17.48)|
|3.2 Basic Definitions of the Relational Model (23.32)|
|3.3 Introduction to Integrity Constraints (13.09)|
|3.4 Intrarelation Constraints first-part (18.03)|
|3.4 Intrarelation Constraints second-part (7.37)|
|3.5 Interrelation Constraints Referential Integrity Constraints first-part (11.55)|
|3.5 Interrelation Constraints Referential Integrity Constraints second-part (15.26)|
|3.6 Normalization first-part (15.42)|
|3.6 Normalization second-part (22.06)|
|4.1 Introduction to Conceptual Design (14.17)|
|4.2 Schema quality dimensions - first part (19.34)|
|4.3 Schema quality dimensions - second part (16.32)|
|4.4 Strategies in Conceptual Design first-part (12.54)|
|4.4 Strategies in Conceptual Design second-part (16.48)|
|5.1 Introduction to Logical Design first-part (14.00)|
|5.1 Introduction to Logical Design second-part (12.20)|
|5.2 Simplification and Optimization Steps first-part (14.06)|
|5.2 Simplification and Optimization Steps second-part (15.12)|
|5.3 Translation Step – first part (15.05)|
|5.4 Translation Step – second part (16.56)|
|5.5 Methodology for Logical design first-part (22.11)|
|5.5 Methodology for Logical design - second part (14.31)|
|5.5 Methodology for Logical design - third-part (16.42)|