Appunti Computer Science for Food industry

COMPONENTS

• CATALOG - Contains metadata, which is information that describes the type and structure of the data contained in the database
• TABLE - It is a structured collection of data containing information regarding a single element, formed by fields (attributes) each of which contains a single piece of information
• RECORD (or tuple) - In relational DB, it is a set of fields related to one instance. It is a row of the table (in some book, this is also called relationship, due to the relation between attributes in one table row. Please do not confuse it with the relationship of the ER models)
• RELATION - The way information in a table refers to information in another table. The notion of relationship comes from mathematics, from set theory
• VIEW or QUERY - Extraction of a subset of data from the database, subject to the access authorization mechanism through which it is possible to regulate user access to the database. A query is a request for

Data or information from a database table or a combination of tables. The view is the result of the query (the view is the result of the previous operation). Actually, a view does not store data (some refers to a view as a virtual table). You can query a view like you can call a full table.

JOIN - Process that allows to associate tables and queries using the values of their related data. You can combine data from two or more tables using join operations.

Example:

Each column is an attribute (or field), and the name of each attribute could be called header. The info for each student forms the record (tuple or instance).

RELATIONAL ALGEBRA (= Algebra relazionale)

INTRODUCTION

To properly understand the query and, in particular, the join operation, we need to know the relational algebra, which is a family of algebra used for modelling the data stored in relational databases. Relational algebra, first created by Edgar F. Codd while working at IBM, is a family of algebras with a well-founded

semantics used for modelling the data stored in relational databases, and defining queries on it. It derives from set algebra (= Algebra degli insiemi).

SETS
A set (= insieme) is a collection of distinct objects. We define a set through the description of the elements that make it up (that are collected in it).

Example: the set of integers
Example: the set of classes belonging to a course of study

OPERATORS
In relational algebra, we have a wide set of operators, but we can focus on 6 main ones:

• Selection (• →)
  The selection from a table (or a set) is the subset of rows (= file) belonging to the table and which satisfy a series of conditions indicated in the selection itself. For example, from the table Employee (which has 3 attributes: Surname name, ID, Age), we want to select the ones that satisfy the condition "Age > 35"
• Union (OR) (• →)
  It is essential to extend our search among multiple tables. Its result is a database view with the same set of attributes of the

DATABASE DESIGN OVERVIEW

Designing a database means defining its structure, characteristics and contents. This is a process in which many delicate decisions must be taken. The use of appropriate techniques is essential for the creation of a high-quality data organization, thus of a

high-quality database.Database design is just one of the many activities in the development of an Information System, within a company or an organisation.

The task of creating a database application is a complex one, involving design of the database schema, design of the programs that access and update the data, and design of a security scheme to control access to data. The needs of the users play a central role in the design process.

The design of a complete database application environment that meets the needs of the enterprise being modelled requires attention to a broad set of issues. These additional aspects of the expected use of the database influence a variety of design choices at the physical, logical and view levels.

PHASES

In almost all the applicative cases, the reality we want to represent in a database is too much complex to be approached without a strategy and a well-defined method.

The strategy starts with the collection and the analysis of the requirements. It consists of the

Definition and the study of the properties of the IS. It requires the interaction with the users, in order to ask and capture the application requirements. An example can be the definition of the requirements of a database for a small cheese factory. Firstly, we have to understand which data should be saved (ex. customer information, list of cheese, information related to shipment and suppliers, etc.). After that, it is important to know which operations should be performed on data. For small applications, it may be feasible for a database designer who understands the application requirements to decide directly on the relations to be created, their attributes and constraints on the relations. However, such a direct design process is difficult for real-world applications, since they are often highly complex. The database designer must interact with users of the application to understand the needs of the application, represent them in a high-level fashion that can be understood by the users and

then translate the requirements into lower levels of the design.

A high-level data model serves the database designer by providing a conceptual framework in which to specify, in a systematic fashion, the data requirements of the database users, and a database structure that fulfills these requirements.

DATABASE PROJECT STRATEGIES

With specifications, the design phase can start and it is divided into 3 phases, to be performed consequently (one after the other).

From specifications, with the conceptual design, we obtain the conceptual scheme, which is an abstract representation of data.

Then we perform a logical design, obtaining what is called a logical scheme (in our course it is the relational scheme).

Finally, with the physical design, a physical scheme can be derived.

Summary (From specifications to physical scheme)

• Conceptual design permit to obtain a conceptual scheme
• Logical design permit to obtain a logical scheme
• Physical design permit to obtain physical scheme

Abstraction

• classification
• aggregation
• generalization

High-level description of the database

Description, through the Data Definition Language, of the data structure according to the chosen model (e.g., relational model)

Optimization of the format and of the data storage modality charged to the DBMS

INITIAL PHASE

The initial phase of database design is to fully characterize the data needs of the prospective database users. The database designer needs to interact extensively with domain experts and users to carry out this task.

Particular attention needs to be paid to defining specifications of user requirements: later an inadequacy in the software development cycle is discovered, the greater the repair cost.

Speaking about the first preliminary phase, the collection and analysis of all requirements, we see that it requires the interaction with the user, in order to ask and capture the application requirements, to study all the properties and functionalities required by the

• control methods for quality, testing, installation, training, operation, maintenance and assistance

This image is related to the analysis of the quality of the design. This analysis is very important.

Firstly, it must be stated that quality analysis should not be relegated at the end of the database design, but it is a parallel activity during the development of a database design.

SOFTWARE

There is no general method for software design.

• Several projects may correspond to the same specification, i.e. different solution methods
• The project choices are expressed and implemented in such a way that the project is sufficiently independent
• The design choices must be able to change in response to changing needs without lead the whole project (and therefore the produced software) to be radically modified

The design of a software system is therefore a highly creative activity that requires the ability to know how to anticipate changes and

1. CONCEPTUAL DESIGN

Its purpose is to represent the informal specifications of the reality of interest in terms of a formal and complete description, but independent of the representation criteria used in the database management systems.

In this phase it is necessary to represent the informative content of the database, without worrying about the modalities with which these will be codified, nor about the efficiency of the programs that will use it.

The product of this phase is the conceptual scheme. In accomplishing this, a conceptual data model will be used.

We obtain a conceptual scheme, that refers to a conceptual model. This model allowed the description of the organisation of data at high level of abstraction.

The most famous conceptual scheme is the Entity-Relationship model (E-R model).

2. LOGICAL DESIGN

It consists in the translation of the conceptual-scheme into the data representation model adopted by the available dat