DATABASE DESIGN

Teaching in italian
DATABASE DESIGN
Teaching
DATABASE DESIGN
Subject area
ING-INF/05
Reference degree course
DIGITAL HUMANITIES
Course type
Master's Degree
Credits
6.0
Teaching hours
Frontal Hours: 42.0
Academic year
2022/2023
Year taught
2022/2023
Course year
1
Language
ENGLISH
Curriculum
COMUNE/GENERICO
Reference professor for teaching
MONGELLI Antonio

Teaching description

Knowledge of basic computer science.

The course aims at providing the students coming from the humanities with the basics of Database Design. A particula focus will be placed on theories and tools that have become fundamental in their field of interest.
The following topics will be taught:
- Databases and Database Users
- Database System Concepts and Architecture
- Data Modeling Using the Entity-Relationship (ER) Model
- The Enhanced Entity Relationship (EER) Model
- The Relational Data Model and Relational Database Constraints
- Relational Database Design by ER- and EER-to-Relational Mapping
- Basic SQL

The student will be able to design relational databases and implement them using the sql language. This will allow him to collaborate with software engineers to design data-centric applications. These skills will also prove useful in other courses (e.g. Web Technologies) to design applications and online services for the humanities.

The lectures, for the theoretical aspects, will be followed by participatory learning sessions and practical sessions to strengthen understanding and acquire skills related to the field of database design. Various scenarios useful for the theoretical-practical acquisition of the methodology for designing a relational database will be analysed. Starting from the design of the ER model up to the implementation of the database using the sql language.

The evaluation consists in the presentation and discussion of a project related to the design of a database. The project consists in:

designing of a database with a topic related to Cultural Heritage. The chosen topic must be briefly discussed with the teacher to verify its feasibility. The project can be submitted by a single student or by a maximum of two students. All the design phases discussed during the course must be respected:

  • Requirements analysis
  • Designing and Drawing of the ER model
  • Mapping and drawing of the Relational Model
  • Implementation of the database using the MySql language
  • Inserting data into the tables
  • Database queries.

 

  • Requirements Analysis: For this step the preparation of the Requirements Analysis Document is needed. This document must be inserted in a PowerPoint presentation (or pdf document). The Document must contain the following information:

          · the detailed description of the context that clarifies its logical functioning, the data, the actors involved and the purpose of the choice of the context;

          · any limitations of the normal logical functioning of the context;

          · the list of data and their typology;

          · the list of relationships involved.

  • Designing and drawing of the Entity Relational Model: it must be drawn using an application suitable for the drawing and must derive from the information present in the requirements analysis document. To indicate the cardinality of the relationships, the student can, at his choice, use one of the two notations discussed during the course. The drawing must be inserted into the same PowerPoint presentation (or pdf document) as the previous step.
  • Drawing of the Relational Model: It must be drawn using an application suitable for the drawing and must derive from the mapping phase of the ER Model. The drawing must be inserted into the same PowerPoint presentation (or pdf document) as the previous step.
  • Implementation of the database using MySql language: Creation of the physical model of the database using MySql language. This step must respect the Tables and Relationships present in the Relational Model. Furthermore, the tables must be constructed to respect the constraints studied during the course. For this step you can use the Linux server account, created for each student enrolled in the course. The construction of the tables, with the use of MySql commands, must be documented with images taken from the screen. These images must be inserted in the same PowerPoint document (or pdf document) as the previous step.
  • Inserting data in the tables: Each table must contain at least 10 records. The data, even if not real, must relate to the chosen context. The phase of populating the tables, using the MySql commands, must be documented with images taken from the screen. It will be sufficient to document the insertion of only one record for each table. These images must be inserted in the same PowerPoint document (or pdf document) as the previous step.
  • Database Queries: At least 6 different queries must be implemented that show the data present on different tables. Queries should be simple and complex. Furthermore, MySql View command on at least 1 query must be used. The construction of queries and View, with the use of MySql commands, and the relative results, must be documented with images taken from the screen. These images must be inserted in the same PowerPoint document (or pdf document) as the previous step.

The PowerPoint presentation (or pdf document), which includes all the previous steps, must be sent by e-mail to the teacher, at least 5 days before the exam.

FUNDAMENTALS OF DATABASE SYSTEMS SIXTH EDITION Ramez Elmasri

Chapter 1 Databases and Database Users

1.1       Introduction

1.2       An Example

1.3       Characteristics of the Database Approach

1.4       Actors on the Scene

1.5       Workers behind the Scene

1.6       Advantages of Using the DBMS Approach

1.7       A Brief History of Database Applications

1.8       When Not to Use a DBMS

 

Chapter 2 Database System Concepts and Architecture

2.1       Data Models, Schemas, and Instances

2.2       Three-Schema Architectureand Data Independence

2.3       Database Languages and Interfaces

2.4       The Database System Environment

2.6       Classification of Database Management Systems

 

Chapter  7 Data Modeling Using the Entity-Relationship (ER) Model

7.1       Using High-Level Conceptual Data Models for Database Design

7.2       A Sample Database Application

7.3       Entity Types, Entity Sets, Attributes, and Keys

7.4       Relationship Types, Relationship Sets, Roles, and Structural Constraints

7.5       Weak Entity Types

7.6       Refining the ER Design for the COMPANY Database

7.7       ER Diagrams, Naming Conventions, and Design Issues

7.9       Relationship Types of Degree Higher than Two

 

Chapter  8 The Enhanced Entity Relationship (EER) Model

8.1       Subclasses, Superclasses and Inheritance

8.2       Specialization and Generalization

 

Chapter  3 The Relational Data Model and Relational Database Constraints

3.1       Relational Model Concepts

3.2       Relational Model Constraints and Relational Database Schemas

3.3       Update Operations, Transactions, and Dealing with Constraint Violations

 

Chapter  9 Relational Database Design by ER- and EER-to-Relational Mapping

9.1       Relational Database Design Using ER-to-Relational Mapping

9.2       Mapping EER Model Constructs to Relations

9.2.1    Mapping of Specialization or Generalization

 

Chapter  4 Basic SQL

4.1       SQL Data Definition and Data Types

4.2       Specifying Constraints in SQL

4.3       Basic Retrieval Queries in SQL

 

MySql  (Use from the command line)

  •  Databases operations: Show, Create, Use, Drop.

  •  Tables operations: Show, Create, Describe. Alter Table: Rename-Modify-Change-Add-Drop.

  •  Input and Dropping Indexes and Constraints: Primary Key, Unique, Auto_Increment,, Foreign Key: On delete, On Update.

  •  Input data in tables: Insert Into, Load Data Infile. Examples of constraints violation with insert command.

  •  Updating data in tables: Update.  Examples of constraints violation with Update command.

  •  Deleting data in tables: Delete.  Examples of constraints violation with Delete command.

  •  Viewing data: Select, Order By, Limit, Distinct, Use of And-Or Operators, Round Function.

  •  Creating Views: Create View, Drop View.

Fundamental of Database Systems, Ramez Elmasri. 6th edition. Pearson ed.
All needed materials and lessons slides, are organized and constantly updated by the teacher and will be uploaded in the didactic section on this web site.

Semester
First Semester (dal 19/09/2022 al 13/01/2023)

Exam type
Optional

Type of assessment
Oral - Final grade

Course timetable
https://easyroom.unisalento.it/Orario

Download teaching card (Apre una nuova finestra)(Apre una nuova finestra)