Database Management: Logical Model and Relational Principles

Database Management: Logical Model

3.

Logical model . It allows us to translate the abstract representation (conceptual model) into a data representation that is still free from physical details but concrete, as it is present in the DBMS.

• To implement it, it is necessary to use a data definition language (DDL) generally provided by current DBMS.
• Examples of Logical Data Models:

1. Hierarchical
2. Reticular
3. Relational
4. Object-Oriented

Relational Model Introduction

1Database Management II. Data and Computers c) Relational model

• The pre-existing models (hierarchical and reticular) were strongly influenced by physical considerations.
• The relational model was introduced in 1970 by Edgar Codd (IBM) in order to foster independence between theoretical analysis and the adopted DBMS.
• It is the most widely used logical model and consists of a series of tables.
• Each table corresponds to the concept of a relationship.
• Features:
  • Simplicity
  • Existence of numerous tools for database management
  • Rich and complete theoretical treatment
  • Existence of powerful accepted and widespread query languages (e.g. SQL)

Relational Database Concepts

2II. Data and Computers Database Management

• Tools such as relational calculus and relational algebra are used for the treatment of relational databases.
• The fundamental concepts of the relational data model are those of relation, tuple, and attribute.

PATIENT Pat.no. Name Gender Birth date Address City Phone no. 12 Johnson, M. male Aug 12-1934 4808 Main St New York (123) 456-789 66 Smith, A.F. fem ale Mar 13-1950 12 Hill Lane Baltimore 45 Brown, M. male Dec 03-1960 234 NewRd. Boston (987)-654-321 Tuple Attributo Tuple: Different information about the same entity Attribute: Same information for different entities

Relational Properties and Schema

3II. Data and Computers Database Management

• A relation is a set of tuples that are themselves composed of attribute values, with the following properties:
  • There is no sort order between tuples of a relationship
  • There can be no identical tuples in a relationship
  • There is no sorting between the attributes of a relationship

Relationship Schema and Relationship Instance: Patient (number, name, gender, date of birth, address, city, telephone number)

PATIENT Pat.no. Name Gender Birth date Address City Phone no. 12 Johnson, M. male Aug 12-1934 4808 Main St NewYork (123) 456-789 66 Smith, A.F. fem ale Mar 13-1950 12 Hill Lane Baltimore NULL 45 Brown, M. male Dec 03-1960 234 NewRd. Boston (987)-654-321 Instance

Super Key Definition

4II. Data and Computers Database Management

• There can be no identical tuples in a relationship There are attribute sets that retain this property

SUPER KEY = Any set of attributes in a relationship that satisfies the property of not having the same combination of attribute values for two or more tuples.

Identification code Surname Name Date of birth Place of birth Pathology 123 Rossi Luigi 01/01/1950 Ancona Hypertension 124 Rossi Dario 01/01/1950 Milano Diabetes 125 Rossi Luca 03/05/1940 Perugia Infarction 126 Neri Luca 03/05/1940 Roma Renal colic 127 Neri Luca 20/04/1952 Torino Respiratory problems

Key Definition

5II. Data and Computers Database Management

KEY = It is a minimal superkey, i.e. a superkey in which it is not possible to find a proper subset of attributes that retains the property of being a superkey.

Identification code Surname Name Date of birth Place of birth Pathology 123 Rossi Luigi 01/01/1950 Ancona Hypertension 124 Rossi Dario 01/01/1950 Milano Diabetes 125 Rossi Luca 03/05/1940 Perugia Infarction 126 Neri Luca 03/05/1940 Roma Renal colic 127 Neri Luca 20/04/1952 Torino Respiratory problems Minimal superkey?

6II. Data and Computers Database Management

Identification code Surname Name Date of birth Place of birth Pathology 123 Rossi Luigi 01/01/1950 Ancona Hypertension 124 Rossi Dario 01/01/1950 Milano Diabetes 125 Rossi Luca 03/05/1940 Perugia Infarction 126 Neri Luca 03/05/1940 Roma Renal colic 127 Neri Luca 20/04/1952 Torino Respiratory problems Key Key

• There can be multiple keys in a relationship.

Primary Key Definition

7II. Data and Computers Database Management

• Primary Key: is a particular key that the DB designer defines as the preferred way to identify the tuples of a table.
• How do you indicate a primary key? Patient {ID, Last Name%, First Name%, Date of Birth%, Place of Birth}
• What is a primary key used for?
  • The primary key allows you to uniquely identify a record
  • The primary key allows you to find a record efficiently

DEFINITION OF PRIMARY KEY A proper subset K of attributes such that

1. two separate records can't exist that have the same values in attribute fields in K (uniqueness)
2. it is not possible to subtract an attribute from K without the uniqueness condition ceasing to apply (non-redundancy)

Relational Model Query Languages

8II. Data and Computers Database Management Query languages for the relational model can be divided into two categories:

A. Algebraic languages (procedural or navigational languages), where a question is formulated by means of an expression constructed by operators acting on relations (union, projection, selection, Cartesian product, natural join ... ). The answer to the question is the relationship resulting from the evaluation of the expression.

B. Languages based on relational calculus (non-procedural or declarative languages), where questions are expressed by specifying predicate calculus formulas. The answer to the question is the relation whose tuples satisfy the property that is stated in the question itself.

Procedure to get the result vs. properties of the result Ex: SQL

Relational Algebra

9Database Management II. Data and Computers RELATIONAL ALGEBRA

• It is a procedural language that allows to formalize the operations made possible in the relational model of data.
• Procedural language defines a set of operations that allow to manipulate entire relationships and obtain other relationships as a result.
• Operations can be composed into expressions that allow complex queries to be made on the database.

Relational Algebra Operators

10II. Data and Computers Database Management A few premises

• Relational algebra consists of a set of operators that apply to one or more relations and produce a relation:
  • Unary Base Operators
  • Binary Basic Operators
• The semantics of each operator are defined by specifying:
  • How the schema (set of attributes) of the result depends on the schema of the operands
  • How the result instance depends on the input instances
• Operators can be composed, resulting in algebraic expressions of arbitrary complexity
• Operands are either (names of) DB relations or (well-formed) expressions

Main Relational Algebra Operations

11II. Data and Computers Database Management Main operations of relational algebra

1. Select, project, and rename
2. Union, Intersect, Difference
3. Cartesian Product, Natural Join, and Theta-Join

Select Operation

12Database Management II. Data and Computers Select (a)

Allows to select a subset of tuples in a relation Syntax: NewRelation& O(condition) (RelationName)

• The result is a new relation with a structure identical to the starting relation whose tuples satisfy what is defined in the part (condition) of the operation.
• The part (condition) is obtained by connecting, through logical operators (AND, OR, NOT), conditions comparing attribute values and fixed values, or between values of different attributes of the relation under consideration.

Select Operation Example

13II. Data and Computers Database Management Esempio:

Identification code Surname Name Date of birth Place of birth Department 123 Rossi Luigi 01/01/1950 Ancona Cardiology 124 Rossi Dario 01/01/1950 Milano Surgery 125 Rossi Luca 03/05/1940 Perugia Surgery 126 Neri Luca 03/05/1940 Roma Emergency 127 Neri Luca 20/04/1952 Torino Surgery NewPatient <- O(Department = Surgery) Patient) NewPatient (Department = Surgery) AND (ID = 125) (Patient)

14II. Data and Computers Database Management

NewPatient <- O(Department = Surgery) (Patient) 124 Rossi Dario 01/01/1950 Milano Surgery 125 Rossi Luca 03/05/1940 Perugia Surgery 127 Neri Luca 20/04/1952 Torino Surgery NewPatient&O(Department (Department = Surgery) AND (ID = 125) (Patient) 125 Rossi Luca 03/05/1940 Perugia Chirurgia

Project Operation

15Database Management II. Data and Computers Project (Tt)

Creates a new relation that contains the tuples that match the tuples of the starting relation but are limited to the attribute values that are specified in the operation Syntax: NewRelation& -T (Attribute List) (RelationName)

• a new relationship is obtained that has only the attributes expressed in the part (attribute list) of the operation.
• In general, the cardinality of the result is less than or equal to the cardinality of the starting relation (the projection eliminates duplicates).
• Equality is guaranteed if and only if attributes are a superkey to the relationship.

Project Operation Example

16II. Data and Computers Database Management Example:

Identification code Surname Name Date of birth Place of birth Department 123 Rossi Luigi 01/01/1950 Ancona Cardiology 124 Rossi Dario 01/01/1950 Milano Surgery 125 Rossi Luca 03/05/1940 Perugia Surgery 126 Neri Luca 03/05/1940 Roma Emergency 127 Neri Luca 20/04/1952 Torino Surgery NewPatient& -T(Surname, Name, Date of Birth) 'Patient) NewPatient&T(Surname, (Patient)

17II. Data and Computers Database Management Example:

NewPatient& T (Surname, Name, Date of Birth) (Patient) Surname Name Date of birth Rossi Luigi 01/01/1950 Rossi Dario 01/01/1950 Rossi Luca 03/05/1940 Neri Luca 03/05/1940 Neri Luca 20/04/1952

18II. Data and Computers Database Management Example:

NewPatient& -T(Surname, Name) (Patient) Surname Name Rossi Luigi Rossi Dario Rossi Luca Neri Luca

Rename Operation

19Database Management II. Data and Computers Rename (p)

Allows to rename the attributes of a relationship to standardize the names of attributes that have similar meaning but come from different relationships. Syntax: NewRelation& P(Renaming Criteria) (Relation1)

• A new relationship is obtained that is identical to the original one, but the attributes mentioned in (renaming criteria) will have a different name as specified.

Union Operation

20Database Management II. Data and Computers Union (u)

Allows to construct a new relationship that includes all the tuples that are present in multiple starting relations Syntax: NewRelation <- Relation1 u Relation2

• The two relationships must have the same structure.
• Tuples in two or more relationships are not duplicated in the result report.