DATABASE ORGANIZATION

Lecturer: Ph.D. , Associate Professor, Associate Professor of Department 105

National Aerospace University

"Kharkiv Aviation Institute"

Karatanov Oleksandr Volodymyrovych

What will you learn in this course?

Database design

DDL

DML

DQL

View

What will you learn in this course?

Who can you become after studying the course?

• database analyst;
• database developer;
• database system administrator;
• someone else in the IT field.

​

Most popular languages

https://survey.stackoverflow.co/2024/technology#most-popular-technologies-language

Programming, scripting, and markup languages

Most popular languages

© Zecomms Agency , 2023

Most popular languages (Djinni)

Database administrator salaries abroad

https://money.usnews.com/careers/best-jobs/database-administrator/salary

• Bachelors degree in Computer Science , Computer or Electrical Engineering, Mathematics, or a related field

​

• Minimum of 5 years Database Administration experience

​

• Minimum of 5 years data modeling experience

​

• Excellent SQL and DB performance tuning skills

​

• Minimum of 5 years troubleshooting and problem-solving experience.

Database Engineer – FinTech

Database​​​

1. a set of interrelated data organized according to a database schema in such a way that a user can work with them [GOST 34.321-96, DSTU ISO/IEC 2382:2017 Information technologies. Glossary of terms ].

​

• a named structured collection of data related to a specific subject area [ DSTU 2226-93 ].

​

Data models

• Database schema ( Database schema ) – a formal description of data according to a specific data schema [GOST 34.321-96].

​

• Data schema ( Data schema ) or Model data – logical representation of data organization [GOST 34.321-96].

​

​

Data models

Types of data models

Hierarchical data model

• This is a data model that uses a database representation in the form a tree-like (hierarchical) structure consisting of objects (data) of different levels.

​

​

• There are relationships between objects, each object can include several lower-level objects.
• Such objects:
• ancestor (object closer to the root);
• descendant (lower-level object).
• An ancestor object may have no descendants or may have several.
• In a child object necessarily only one ancestor .
• Objects that share a common ancestor are called twins .

Network data model

• The difference between a hierarchical data model and a network data model is that in hierarchical structures, a child record must have exactly one ancestor , and the network data structure of the descendant can be any number of ancestors .

​

In a network model, connections between all information objects and all others are possible.

​

For example, each teacher can teach many students and each student can study with many teachers.

Using hierarchical or network models speeds up access to information, but requires significant memory resources .

Teacher 1

Teacher 2

Teacher 3

Student 1

Student 2

Student 3

Student 4

Semantic network

• Semantic network – an information model of a subject area, which has the form of a directed graph, the vertices of which correspond to objects of the subject area, and the arcs (edges) define the relationships between them.

Relational data model

• relational data model aims to organize data as relationships .

​

• Developed in the early 1970s by Edgar F. Codd .

​

​

Subject area model

When designing a database, the subject area is in the form of three models:

1. representation of the subject area as it really exists – infological (informational, semantic, conceptual) model;
2. the data model as it is presented by the database designer – data logical (logical) model;
3. a data model in a form suitable for storage in computer memory – a physical model.

​

The data logical model is designed based on the information model, by normalizing it and reducing it to third normal form.

Database design

​

​

​

​

0. Analysis of the subject area.

1. Designing an information model.
2. Development of a data logical model.
3. Physical database design.

database design process is a sequential transition from an informal verbal description of the information structure of a subject area to a formalized description in terms of a certain model and, in turn, includes several stages.

DEVELOPMENT OF AN INFOLOGICAL MODEL

0 Domain Analysis

1. What data will the database work with?
2. Who will work with the database?
3. How is the database work?

​

As a result, we should get a verbal description of the information objects of the subject area.

​

Subject area

• At the heart of any database is the concept of a subject area.
• A subject area is a part of the real world, information about which should be contained in the database.

Example of a subject area description

• The company repairs computers.

​

• It is necessary to develop a database to store information about the performance of repair work by the company's employees.

​

• When placing an order , the date of order execution , type of work performed , and the person performing the work are recorded .

1 Designing an infological model

• An infological model allows you to represent a subject area in a formalized form.

​

• To build an infological data model, various types of semantic models are used : semantic networks , the entity-relationship model ( ER model ), the IDEF1X model, etc.

​

Designing an infological model

1. After performing a subject area analysis, identify entities (table names) to represent them in the database.

​

• Reveal attributes (column names) of entities.

​

• Enter (select) a unique identifier attribute. It is called the primary key (or simply "key" ) .

​

• Identify the type of dependencies ( relationships ) of entities.

Entity- relationship model ( ER model ) ERM) – a data model that allows you to describe the conceptual schemes of a subject area.

IDEF1X (IDEF1 Extended ) Data Modeling – database modeling methodology based on the entity-relationship model.

​

It is used to build an information model that represents the structure of information necessary to support the functions of a production system or environment.

Basic concepts of ER diagrams

1. Essence
2. Entity instance
3. Entity attribute
4. Entity key
5. Communication

​

Essence

• An entity is a class of objects of the same type, information about which must be contained in the model.
• Each entity must have a name expressed by a singular noun .
• Examples of entities can be such object classes as "Student", "Faculty", "Teacher". Each entity of the model is depicted as a rectangle with a name.

​

Entity instance

• An entity instance is a specific representative of that entity.
• For example, the representative of the entity "Student" could be "Student Bozhenko".
• Instances of entities must be distinct, meaning entities must have some properties that are unique to each instance of that entity.
• Entity instances are not necessarily reflected in the database model, but we should keep them in mind when designing.

​

Entity attribute

• An entity attribute is a named characteristic that is some property of an entity.
• The name of the attribute should be expressed as a singular noun (possibly with characterizing adjectives).
• Examples of attributes of the "Student" entity can be attributes such as "Student ID Number", "Last Name", "First Name", "Patronymic", "Group", "Specialty", etc.
• Attributes are depicted within a rectangle that defines the entity.

​

Entity key

• An entity key is a non-redundant set of attributes whose values are collectively unique for each instance of the entity.
• This is the minimum set of attributes whose values can be used to uniquely find the required entity instance.
• Key attributes are displayed in the diagram by underlining and/or by the abbreviation PK ( Primary Key ) .

​

Simple and compound key

• A composite key is a key that consists of two or more attributes ( table columns ) that together uniquely identify an entity (table row).

Compound key

Simple key

Natural vs Surrogate Key

• A natural key (also known as a business key or domain key) is a type of unique key in a database, formed from attributes that exist and are used in the external world outside the database .
• Advantages of using a natural key:
• less disk space usage;
• facilitates database model design ;
• simplifies its understanding.

​

Communication

• A connection is some association between two entities.
• One entity can be related to another entity or to itself.
• For example, relationships between entities can be expressed in the following phrases:

"A teacher can have multiple Disciplines",

"Every student must study at least one specialty."

• Graphically, a relationship is represented by a line connecting two entities.

​

Types of relationships between entities

• "one to one" - each instance of the first entity corresponds to only one instance of the second entity.
• Such connections mostly don't make sense, because these two entities can usually be combined into one.
• Example: each company has one director.

​

• "one-to-many" - each instance of the first entity can correspond to multiple instances of the second entity, but not vice versa.
• Example : One company has many board members.

​

• "many to many" - each instance of the first entity can correspond to several instances of the second entity and vice versa.
• It is best to avoid such connections. They are not supported by the DBMS.
• Example: each company can have several lawyers, while each lawyer can serve several companies.

Overview of software for designing database architecture

MySQL Workbench

​

MS Visio

​

​

​

​

ErWin

​

ARIS

​

draw.io

Crow's Foot Notation� (Crow's foot)

• Three symbols are used to represent relationships:
1. the circle is "zero";
2. a dash represents "one";
3. crow's foot " many" or "infinity".

Types of connections in �Crow's Foot notation

The inner element of the notation represents the minimum value, and the outer element (closest to the essence) represents the maximum.

Subject area

• Let's consider the dean's office data model.

Data model

DEVELOPMENT OF A DATA LOGICAL MODEL

Normalization of relations

Normalization is a sequential process of partitioning and transforming some small source set of tables to construct a set of interdependent tables in normal forms.

Definition for the alternatively gifted

Normalization – this is when we had a lot of tables, and we would make even more so that they would comply with some rules.

Normal form

Normal form (NF) is a requirement for the structure of tables in theory relational databases to remove redundant data from the database functional dependencies between attributes (table fields).

eight NFs in total :

– 1-6 NF;

– NF Boyce-Codd;

– domain-key NF.

Functional dependency

A set of attributes Y is functionally dependent on X if and only if at any point in time for each of the different values of Y there is only one of the different values of X.

​

There is also an equivalent term: plural X determines Y .

​

Designation –

X → Y .

Each of the normalization levels restricts the types of allowable functional dependencies of a relation.

determinant

dependent part

Functional dependency. Example

• Consider the relationship given by the following scheme:

schedule (Pilot, Flight, Date, Time).

​

• Their dependence is given by the following constraints :
• only the departure time is specified for each flight;
• for the attributes ( Pilot, Date, Time ) only one flight is defined;
• for attributes ( Flight, Date ) a single pilot is defined.

​

• Thus, the set is given functional dependencies :
• Flight → Time
• (Pilot, Date, Time)→ Flight
• (Flight, Date)→ Pilot

​

1 normal form

• Conditions of the first normal form:
1. each entity corresponds to a separate table;
2. each set of related data is identified by a primary key;
3. fields do not have duplicates in each record;
4. each field contains only one value ( atomically ).

Atomicity ( indivisibility ) of a field means that the values it contains must not be divided into smaller ones.

1 normal form. Example

• Let's say for a relationship with the Flight schema (Number, Destination, Departure), the Departure attribute is defined as the pair (Day, Time) .
• In this case, it is easy to implement queries like “Return all flights to Kharkiv,” unlike the query “Return all flights departing on Monday.” From the perspective of the second query, the relation is not in 1NF.

​

​

​

Duplication of information

Lack of atomicity

1 normal form. Example

• The transformation is obvious: the relation is replaced by another one with the scheme:

Flight (Number, Destination, Departure day, Departure time).

• This will allow for atomicity .

​

Allocating destinations to a separate table will eliminate duplication of information.

1 normal form. Example 2

• If the "Division" field contains the name of the faculty and department, the requirement of indivisibility is not met and it is necessary to separate the name of the faculty or department into a separate field.

​

1 normal form. Redundancy

Data redundancy – data duplication in the database.

Reduction to first normal form provokes redundancy

Example 3

1 normal form. Integrity

• Data redundancy or duplication not only wastes space; it can also cause data integrity issues (inconsistencies) in the database.

​

​

Data integrity – consistency of data in the database.

Anomalies

• To achieve integrity, anomalies must be eliminated.

​

1st normal form. Example 4

• Let's assume that during logical modeling, in the first step, it is proposed to store data in a single relation that has the following attributes:

​

​

​

​

​

​

• The potential key of the relation should be a pair of attributes (Employee's timesheet number , Project number), since each employee in each project performs exactly one task.
• From the table EMPLOYEES_DEPARTMENTS_PROJECTS It is clear that these relations are preserved in it with great excess .
• Many lines contain repeated employee names, phone numbers, and project names. In addition, in this regard, independent data is stored together - data about employees, departments, projects, and project work.

​

EMPLOYEES_DEPARTMENTS_PROJECTS

1st normal form. Example 4

• The relationship EMPLOYEES_DEPARTMENTS_PROJECTS is decomposed into three relationships – COLLABORATORS_DEPARTMENTS, PROJECTS, TASKS.

​

Table 2. Relationship EMPLOYEES_DEPARTMENTS

Table 3. Relationship PROJECTS

Functional dependencies:

Project number

→ Project name

Table 4. TASKS relationships

Functional dependencies:

( Employee's record number , Project number ) → Task number

Functional dependencies:

Dependence of attributes characterizing an employee on the employee's personnel number:

Registration number → Last name

Registration number → Department number

​

Dependence of phone number on department number:

Department number → Department phone number

2 normal form

• Conditions of the second normal form:
1. the model meets the conditions of 1 NF;
2. all fields of each table that are not part of the primary key are linked by a full functional dependency to the primary key.
1. Note: If the key of a relation is simple, then the relation is automatically in 2NF.

​

A simple key is a key with a single attribute.

A composite key is a key made up of two or more attributes.

Complete and partial functional dependence

• A full functional dependency is a dependency in which a non-key field depends on all attributes of the composite primary key.

​

• A partial functional dependency is a dependency in which a non-key field depends on some of the attributes of a composite primary key.

​

2nd normal form. Example

• The entity EMPLOYEES_DEPARTMENTS is not in 2NF, because there are attributes that depend on part of the composite key:
• The employee's last name does not depend on the department number.
• In order to eliminate the dependency of attributes on part of the composite key, it is necessary to decompose the relationship into several relations. In this case, the attributes that depend on part of the composite key are transferred to a separate relation.

​

Table 2. Relationships DEPARTMENT EMPLOYEES

3 normal form

• Conditions of the third normal form:
1. the model satisfies the conditions of 2 NF;
2. all non-key fields are independent of each other.

​

A non-key attribute is an attribute that is not part of the primary key.

Two or more attributes are called mutually independent if none of them is functionally dependent on any combination of the other attributes.

3rd normal form. �Alternative definition

• Conditions of the third normal form:
1. the model satisfies the conditions of 2 NF;
2. there is no transitive functional dependence of key fields on the primary key.

​

A transitive functional dependency is a dependency where two attributes are related to each other through a third attribute.

+ and – normalizations

• As can be seen from the table, more highly normalized relationships appear to be better designed ( three pluses , one minus ).
• They are more relevant to the subject area, easier to develop, and database modification operations are faster for them.
• However, this is achieved at the cost of some slowdown in data retrieval operations .

HIGHER NORMAL FORMS

BCNF ( Boyce -Codd Normal Form)

Boyce-Codd normal form conditions :

1. the model satisfies the conditions of 3 NF;
2. the determinants of all functional dependencies are potential keys.

�

Alternative item 2

2. a relation must not have two (or more) possible keys that are complex and share a common attribute.

Potential key

​

​

​

• There can be several potential keys at the same time.

​

• One of them can be chosen as the primary key of the relation, then the other potential keys are called alternate keys.

​

Potential key – in the relational data model – a subset of the attributes of a relationship that satisfies the requirements of uniqueness and minimality (irreducibility/ atomicity ).

Uniqueness means that there are no two tuples of this relation in which the values of this subset of attributes are the same (equal).

BCNF ( Boyce -Codd Normal Form)

• There is a functional dependency Tariff → Court Number in which the left-hand side (determinant) is not a potential key of the relation, i.e. the relation is not in Boyce-Codd normal form .

​

�

The disadvantage of this structure is that, for example, the "Economical" rate can be mistakenly attributed to a reservation of 2 courts, although it may only apply to 1 court.

Fourth Normal Form (4 NF)

Conditions of the fourth normal form :

1. the model complies with the BCNF conditions;
2. all non-trivial multivalued dependencies are practically functional dependencies on its potential keys.

​

Alternative item 2

2. all rows of the table must be independent of each other .

Fourth normal form

• In other words, all rows of the table must be independent of each other .
• In the sense that the presence of some row X should not mean that row Y is also somewhere in this table.

​

Relations with non-trivial multivalued dependencies typically arise from the natural combination of two relations on a common field that is not a key in either relation. This effectively results in an attempt to store information about two independent entities in a single relation.

​

For example, an employee may have many jobs and many children. Storing information about jobs and children in a single relation results in a non-trivial multivalued dependency Employee - Job - Children.

Fourth normal form

• The decomposition of the relationship "Applicants-Faculties-Subjects" cannot be performed taking into account functional dependencies , since this relationship does not contain any functional dependencies . This relationship is completely key, that is, the key of the relationship is the entire set of attributes. But it is clear that there is some kind of relationship between the attributes. This relationship is described by the concept of multivalued dependency .

​

"Applicants-Faculties-Subjects" has a non-trivial multivalued dependency:

Faculty→→ Applicant | Subject.

Fourth Normal Form (4 NF)

There seems to be an update anomaly related to the fact that the names of applicants, names of faculties, and names of subjects are duplicated.

Elimination: by moving all names into separate relations, leaving only the corresponding numbers in the original relation.

Insertion anomaly . When trying to add a new tuple to the relationship "Applicants-Faculties-Subjects", for example ( Star , Aircraft Construction, Mathematics), we must also add a tuple ( Star , Aircraft Engineering, Computer Science), since all applicants to the Aircraft Engineering Faculty are required have the same list of items to be given. Accordingly, when trying to insert the tuple (3, 1, 1) into the modified relation, we must also insert the tuple (3, 1, 2).

Removal anomaly.

Insertion and deletion of tuples cannot be performed independently of other tuples of the relation.

In addition, if we delete these tuples, information about the subjects that must be taken at the SULA faculty will be lost.

Fifth Normal Form (5NF)

Conditions of the fifth normal form ( projective -adjoint normal form) :

1. the model satisfies the conditions of 4 NF;
2. each non-trivial join dependency in it is defined by the potential key(s) of this relation.

​

Alternative item 2

2. The model complies with certain constraints on the combination of attributes .

Fifth Normal Form (5 NF)

Suppose that it is necessary to take into account the following restriction : each seller has in his assortment a limited list of firms and a limited list of types of good goods and offers goods from the list of goods, produced by firms from the list of firms.

In the example, in particular, it is assumed that the seller Bova has the right to sell goods only from the company "Rogy ta Kopyta", the seller Oliynyk - goods only from the company "Kalashnikov", however, the seller Karpenko does not have the right to sell barbiturates and steroids, etc.

Sellers' goods

Companies sellers

Goods firms

Non-trivial connection dependency

• A compound dependence X is called a nontrivial compound dependence if two conditions are met:
• one of the attribute sets X does not contain a potential key of relation Y.
• none of the attribute sets of X coincides with the entire attribute set of relation Y.

​

• For convenience, we will formulate the definition in the same way in negative form.

​

• A connection dependency X is called a trivial connection dependency if one of the following conditions is met:
• or all attribute sets X contain a potential relation key Y;
• or one of the attribute sets of X coincides with the entire attribute set of the relation Y.

​

Sixth normal form

• A model is in sixth normal form if and only if it satisfies all nontrivial connection dependencies.

​

• This model is final and cannot be further decomposed without loss.

​

• Practically not applicable. For chronological databases, the maximum possible decomposition allows you to combat redundancy and simplifies maintaining database integrity.

Domain- key normal form

Alternative definition: a relation is in DCNF if it has no modification anomalies.

This is achieved by adding additional constraints on the values of the record field. Data integrity maintenance relies on the mechanisms of a specific DBMS . For example, default field values, data range restrictions, triggers that fire when creating, modifying, or deleting records, and so on. In short, anything that helps maintain data integrity. It was proposed by Ronald Fagin in 1981.

Domain -key normal form (DKNF) is a form where any operation of adding or deleting a record cannot lead to a violation of data integrity.

Domain restrictions – a constraint that requires that only values from a specific domain be used for a particular attribute. A constraint is essentially a list (or logical equivalent of a list) of permissible values of a type and a declaration that the specified attribute has that type.

Key constraint – a constraint that states that a particular attribute or combination of attributes is a potential key.

DEVELOPMENT OF �A PHYSICAL MODEL

DBMS

Data Management System ( DBMS ) (DBMS ) – a set of language and software tools that provide database management [GOST 34.321-96].

Alternative definition

A DBMS is a program, or a set of programs, designed for full-featured work with data.

Typically, it includes tools for creating and modifying the storage structure of data sets, as well as means of accessing stored data with the ability to read, add, modify, and delete it.

The DBMS is designed to play the role of an intermediary between the user (or rather, his direct query or the application in which the query is formulated) and the database.

Classification of DBMS. �By method of accessing the database

• There are the following types of DBMS:
1. file server;
2. client-server;
3. built-in.

​

Database classification. �By data model

• There are the following types of DBMS:
1. hierarchical;
2. network;
3. relational;
4. object-oriented.

​

Classification of DBMS. �By level of distribution

• There are the following types of DBMS:
1. local DBMS (all parts of a local DBMS are located on one computer);
2. distributed DBMS (parts of the DBMS can be located on two or more computers).

​

File server

• In file server DBMS files data is placed centrally on a file server .
• The DBMS is located on each client computer (workstation).
• DBMS access to data is carried out through local Network . Synchronization of reads and updates is done using file locks .

​

Client-server

• The client-server DBMS is located on server together with DB and accesses the DB directly, in exclusive mode.
• All client requests for data processing are handled centrally by a client-server DBMS.

​

File server vs. Client -server

- user interface,

- processing logic ,

- data management

- user interface,

- processing logic

- data storage

- data storage

- processing logic ,

- data management

File server

Client-server

Server

Clients

High network load – large amounts of data are being transferred

Low network load – requests and results are transmitted

Built-in

• Embedded DBMS – a DBMS that can be delivered as a component of a software product, without requiring a separate installation procedure.
• A physically embedded DBMS is most often implemented as a plug-in library. Access to data from the application side can be via SQL or through special programming interfaces.

​

Database rating

https://survey.stackoverflow.co/2024/technology#most-popular-technologies-database-learn

All users Beginners

Database rating

https://db-engines.com/en/ranking_trend

MySQL Workbench

1. Visual database design
2. Ability to edit data in the table in visual mode
3. Full-featured SQL editor
4. Schema and database synchronization

​

How to install MySQL Workbench on Windows?

https://dev.mysql.com/downloads/installer/

MySQL Workbench – a tool for visual database design.

MySQL Workbench is NOT a DBMS!

MySQL Workbench

MySQL Workbench

MySQL Workbench . �Visual design

• The first thing worth noting is that Workbench allows you to visually design database, that is, to create a database schema.
• A visual representation of your database always provides much more information than a dry list of tables.
• In this version, you immediately see how the tables are related to each other, you can group the tables by any parameters and reflect this in the diagram.

MySQL Workbench

• The program has a built-in SQL code editor, which allows you to quickly make any corrections to SQL queries.
• At the same time, you can build queries of any complexity:
1. get different samples from tables,
2. to tie them,
3. create new tables and edit existing ones,
4. work with keys, fields, relationships.

MySQL Workbench

• MySQL Workbench allows you to synchronize your local database schema with the real database on your local or production server.

​

• Thanks to this, after designing , you do not need to manually create tables in the database on your server; just perform a few simple actions in the program, after which a full-fledged database with all the specified relationships and parameters will be created on the working server.

​

MySQL Workbench. �Creating a new data model

• To create a new model, select File -> New Model from the menu or click on the plus sign at the bottom of the list of all models.
• A window will appear for building tables, views, schemas, roles, scripts, etc.

.

To create a new table on the Physical schema tab ( Physical Schemas ) select " Add Table » and fill in the fields

MySQL Workbench

Next, go to the " Columns " tab and create table fields.

Enter the field name, data type, parameters, and comment for it.

Each column has:

1 name (do not use Cyrillic characters in the name!),

​

Data type 2. The most common data types:

• INT – integer;
• VARCHAR (size) – variable-length character data, the maximum size is indicated in parentheses;
• DECIMAL (size, decimal_signs ) – decimal number;
• DATE – date:
• DATETIME – date and time.

​

3 PK ( primary key ) – primary key;

​

4 NN ( not null ) – the cell does not allow empty values;

5 U Q ( unique ) – the value must be unique within the column;

​

6 AI ( auto incremental) – this property is useful for a simple primary key, it means that the primary key will be automatically filled with natural numbers 1, 2, 3, etc.;

​

7 DEFAULT – default value, that is, the value that is automatically inserted into the cell by the server when adding a new row to the table if the user left the cell blank.

Data types

1. numeric types;
2. character types;
3. temporal types (date and/or time);
4. other data types.

Numeric integer types

• INT (4) – values are assumed to be four-digit, but in fact will store the maximum possible.
• INT (5) ZEROFILL – fills the empty positions on the left with zeros. For example, the value 2 will be displayed as 00002.

Numerical thing types

• UNSIGNED disallows negative numbers, but the range of values changes.
• FLOAT (5,2) – will store numbers with 5 characters, 2 of which will be after the decimal point (for example: 46,58).
• DECIMAL (5,2) – will store numbers from -99.99 to 99.99. M – the number of characters to be allocated (maximum value – 64). D – the number of decimal places (maximum value – 30).

Character data types

Calendar data types

• DATATIME- Time zone independent.
• TIMESTAMP - when received from the database, it is displayed taking into account the time zone.

Calendar data types

• A "light" syntax is allowed - you can use any punctuation mark as a separator between parts of date or time sections.
• For example, like this:
1. '87-12-31 11:30:45',
2. '87.12.31 11+30+45',
3. '87/12/31 11*30*45',
4. ' 1987 @ 12 @ 31 11 ^ 30 ^ 45'.

​

Other types

• The TEXT type is used to store text, and the BLOB type is used to store images, sound, electronic documents, etc.
• ENUM ('yes', 'no') – a column with this type can store only one of the available values (+or null , or an empty string).
• SET ('first', 'second') – a column with this type can store one of the listed values, both at once, or the value may be completely absent ( null ).

http://www.mysql.ru/docs/man/ENUM.html

MySQL

• https://metanit.com/sql/mysql/2.3.php

​

• Select data type:
• https://ruhighload.com/%D0%92%D1%8B%D0%B1%D0%BE%D1%80+%D1%82%D0%B8%D0%BF%D0%BE%D0%B2+% D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85+%D0%B2+mysql

​

MySQL Workbench

After creating several tables, you can proceed to create relationships between them. You can do this either through the Foreign tab Keys in the required table, or directly when drawing a diagram of your database.

​

If you do it through the " Foreign" tab Keys ", then go to this tab, enter the name of your key and select the table for the relationship. Then, in the right part of the form, where the table with fields is indicated, select the fields of the previously specified table for the desired field, that is, this is where the relationship between the fields is indicated.

​

Here is the simplest example of a key:

ON DELETE

• CASCADE (to spread, cascade )

​

• SET NULL (set empty value)

​

• SET DEFAULT (set default value)

​

DDL

Integrity by reference

• The referential integrity requirement , or foreign key requirement , is that for each foreign key value that appears in a referenced relation, there must be a tuple with the same primary key value in the referenced table, or the foreign key value must be undefined, i.e., point to nothing.

Integrity by reference

• What happens when a tuple is deleted from the relation it references?
• Operation cascaded – that is, deleting tuples results in deleting the corresponding tuples in the related relation.
• Operation is restricted – that is, those tuples that do not have related information in another respect are deleted. If such information exists, deletion cannot be performed. In this case, you must first either delete the referenced tuples or change their foreign key values accordingly.

Integrity by reference

• What happens when you try updating the primary key of a relation referenced by a specific foreign key?
• Operation cascaded – that is, when the primary key is updated, the foreign key in the related relationship is updated.

​

2. Operation is limited – that is, only those primary keys for which there is no related information in another respect are updated.

If such information exists, the update cannot be made.

​

MySQL Workbench

Creating a database diagram

To create a diagram, use the Model -> Add menu. Diagram ( Ctrl+T ) or on the " EER Diagrams " tab, click on the add new diagram button. A checkered drawing field will open. Select the required tables on the left in the table list and drag them with the mouse onto this checkered area. As a result, a complete diagram of these tables will be immediately built, taking into account the connections we created earlier .

That's all the drawing. Nothing complicated.

As you may have noticed here, there is a toolbar where you can choose to create a new table, images, comment blocks, blocks for merging tables, and various options for relationships between tables (one-to-one, one-to-many, etc. ). By the way, if you create a new table visually here, it will immediately be added to the model itself, i.e. you don't need to duplicate anything.

EER diagram ( EER = Enhanced Entity Relationship)

Creating an SQL CREATE script

​

MySQL database . To do this, you need to create an SQL script. Go to the menu. Select the menu item File -> Export -> Forward Engineer SQL CREATE Script .

In the window that appears in the Output field file using the Browse button Select the path to save the script and specify its name. Click the Next button .

In the next window, click the Finish button .

MySQL Workbench

Synchronization with the server

Select Database -> Synchronize Model… from the menu and enter your server parameters in the window.

After that, go further, the connection will be checked and, if everything is ok , you can proceed to selecting a model for synchronization. Here you select your model, and below you select the database on the server. Go further again, where all your data will be checked again.

After that, you will already be in the list of tables in your model in the program and in the database on the server. It is important that this synchronization method completely synchronizes data, i.e. not only does it upload new tables to the server, but it can also upload tables from the server to the program . To do this, just select the tables in the database and click the " Update Model " button so that they are uploaded to the model during synchronization.

Identifying and non-identifying relationships

• An identifying relationship is shown on the diagram as a solid line with a bold dot at the child end of the relationship, a non-identifying relationship is shown as a dotted line .

​

​

​

​

​

​

​

​

• When establishing a non-identifying relationship (dotted line), the child entity remains independent , and the primary key attributes of the parent entity migrate to the non-key components of the parent entity.
• A non-identifying relationship serves to link independent entities.
• An instance of the "Employee" entity can exist independently of any instance of the "Department" entity, meaning an employee can work in an organization and not be listed in any department.

​

There cannot be an order item number without an order.

Lecture 8. SQL language

SQL

SQL

• SQL ( language structured Structured Query Language (pronounced "s-q-el") is a declarative language programming for interactions user with bases data , used for querying, updating and managing relational databases, creating and modifying database schemas, and database access control systems [ SQL:2016 or ISO/IEC 9075:2016 ] .

Differences between SQL and relational theory

The SQL language uses terms that are somewhat different from relational theory terms, for example,

• instead of " relationships " " tables " are used,
• instead of " tuples " – " strings ",
• instead of " attributes " – "columns" or " columns ".

The SQL language standard, although based on relational theory, deviates from it in many places.

History of SQL

• In the early 1970s, an experimental relational database system, IBM System R, was developed in one of IBM 's research laboratories, for which a special language, SEQUEL ( Structured Query Language) , was later created. English​ QUERY​ Language ) . Later language SEQUEL was renamed to SQL.
• The first DBMS to support the new language was Oracle V2 from Relational in 1979 . Software Inc. (which later became Oracle ) and IBM's System /38, based on System /R.
• In 1986, the first SQL language standard was adopted by ANSI ( American National Standards Institute ).
• A year later, the International Organization for Standardization ( International Organization for Standardization , ISO) published the standard ISO 9075-1987 « Database Language SQL» (SQL database language).
• The latest revision at the moment is: ISO/IEC 9075:2016 "Information technology. Database languages. Structured query language (SQL)", or SQL:2016 .

​

Most popular languages

https://survey.stackoverflow.co/2022/#most-popular-technologies-language-prof

Programming, scripting, and markup languages

Advantages of SQL

• independence from specific DBMSs
• SQL language standardization
• portability from one hardware environment to another
• relational basis of language

Bonus

• the ability to create interactive queries
• possibility of programmatic access to the database
• providing different data representations
• the ability to dynamically change and expand the database structure
• support for client/server architecture

Dialects SQL

• Despite the existence of an international SQL standard, many companies involved in DBMS development make changes to the SQL language.
• Each implementation of the SQL language in a particular DBMS is called a dialect.
• PL/SQL – in the Oracle DBMS;
• Transact-SQL – in the Microsoft SQL DBMS;
• Informix SQL – in the Informix DBMS;
• MySQL – in the MySQL database ;
• JetSQL – Microsoft Access.

How to use SQL?

127.0.0.1 (aka localhost ) is an IP address that a computer can use to contact itself over the network, regardless of whether it is connected to the network, the type, and the address of the computer on it.

How to use SQL?

How to study SQL ?

• https://www.w3schools.com/sql/default.asp

​

• Writing SQL queries online: http://sqlfiddle.com/

​

• http://www.sql-ex.ru ( VPN ) – a site with SQL exercises

​

• Martin Graber . SQL for mere mortals https://library-it.com/wp-content/uploads/2020/09/martin_graber_sql_dlja_prostyh_smertnyh.pdf

How to study SQL ?

1. HackerRank : https://www.hackerrank.com/domains/sql
2. SQL simulator ( VPN ): https://stepik.org/course/63054/syllabus
3. PostgreSQL Exercises : https://pgexercises.com/
4. Live SQL: https://livesql.oracle.com/apex/f?p=590:1000
5. Codility: https://app.codility.com/programmers/trainings/6/
6. CodeSignal : https://app.codesignal.com/arcade/db

​

​

CodePad.SQL​

SELECT Product_ID, Name , Price

FROM product

WHERE price > 100

ORDER BY price Description

Useful links

• https://www.w3schools.com/sql/sql_datatypes.asp – SQL data types;

​

• http://codingcraft.ru/php-samples/sql.php (VPN) – executing SQL queries online;

​

• https://devionity.com/ru/courses/mysql-training-1 – SQL course;

​

• https://habrahabr.ru/post/181033/ – testing for SQL knowledge;

Useful links

• SQL Academy – a simulator for writing SQL queries that is pleasant to use (with VPN): https://sql-academy.org/

​

• Step-by-step interactive SQL tutorial (in English): https://sqlbolt.com/

​

• Interactive online courses on SQL DBMS PostgreSQL (with VPN): https://learndb.ru/

​

• Course "Interactive SQL Trainer": https://stepik.org/course/63054/

SQL language components

• The basis of the SQL language is made up of operators, conventionally divided into several groups according to the functions performed:

​

1. DDL ( Data Definition Language ) – definition operators database objects

​

• DML ( Data Manipulation Language ) – manipulation operators data.

​

• DQL ( Data Query Language ) is a data selection operator .

​

• DCL ( Data Control Language ) – operators for working with access rights .

​

• TCL ( Transaction Control Language ) – transaction control operators .

Operators SQL

Operators SQL

DDL (Data Definition Language) – statements defining database objects

CREATE SCHEMA – create a database schema DROP SHEMA – delete a database schema

CREATE TABLE – create a table ALTER TABLE – alter a table DROP TABLE – delete a table

CREATE DOMAIN – create a domain ALTER DOMAIN – change a domain DROP DOMAIN – delete a domain

CREATE COLLATION – create a sequence DROP COLLATION – delete a sequence

CREATE VIEW – create a data view DROP VIEW – delete a data view

DML ( Data Manipulation Language ) – data manipulation operators

SELECT – select rows from tables

INSERT – add rows to a table

UPDATE – change rows in a table

DELETE – delete rows in a table

D C L ( Data Control Language ) – operators for working with access rights

CREATE ASSERTION – create a constraint DROP ASSERTION – remove constraint

GRANT – grant privileges to a user or program to manipulate objects

REVOKE – revoke user or program privileges

TCL ( Transaction Control Language ) – statements transaction management

COMMIT – commit changes

ROLLBACK – roll back changes made

DDL data definition statements

CREATE

DROP

ALTER

TABLE

VIEW

…

+

Name

tables /

kind

+

DDL data definition statements

Loading data using a text file

​

​

​

​

​

​

​

​

​

​

​

​

​

​

Let's say your records match the ones in this table (note: MySQL accepts dates in the format YYYY-MM-DD ; you may not be used to this notation).

Since you are starting with a blank table, the easiest way to fill it in is to create a text file containing a row for each animal, and then load its contents into the table with a single command.

​

Blank fields (such as unknown sex or death dates for animals alive today) can be given a NULL value. In a text file, this value is the characters \N.

​

For example, the entry for the bird Whistler should look something like this (single tabs should be placed between values):

The end-of-line marker and column-value separator can be specified in the LOAD DATA command, but the defaults are tab and newline. Assuming these, the command will be able to read the pet.txt file

You can load the pet.txt file into a table using the following command:

mysql > LOAD DATA LOCAL INFILE "pet.txt" INTO TABLEpet ;

CREATE TABLE pet ( name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE,death DATE);

SQL not SQL

CREATE DATABASE

CREATE DATABASE database_name [ ON

{[PRIMARY] (

    NAME = logical_file_name,

    FILENAME = 'os_file_name'

[, SIZE = size]

[, MAXSIZE = max_size]

[, FILEGROWTH = growth_increment] )

} [,...n]

]

[ LOG ON

{ ( NAME = logical_file_name,

FILENAME = 'os_file_name'

[, SIZE = size] )

} [,...n] ] [FOR RESTORE]

FILENAME – the full path and file name for the database location, must point to the local disk of the computer on which SQL Server is installed.

SIZE – initial size of each file in MB .

MAXSIZE – maximum file size in MB , if not specified, the size is not limited.

FILEGROWTH – the unit of volume added to a file each time it needs to be increased, specified in MB (by default) or as a percentage (i.e., 0 is added to the number); the value 0 prohibits the file from increasing.

1 MB – default

DCL

DROP DATABASE

• Completely deletes the existing database:

​

​

• You must have the appropriate rights to delete.

DROP DATABASE databasename;

DCL

CREATE TABLE

• CREATE TABLE statement is used to create tables .
• Here is the simplified syntax of this operator:

​

CREATE TABLE table_name

(

column_name data_type [NULL | NOT NULL]

[,...n]

)

CREATE TABLE table (

column1 type1 [(size1)][CONSTRAINT _ column-constraint1]

[, column2 type2 [(size2)][CONSTRAINT _ column-constraint2]

[, ...]]

[CONSTRAINT table-constraint1 _ [,table-constraint2 [, ...]]]

);

DDL

CREATE TABLE

​

​

​

​

​

​

• This statement creates a table member consisting of four columns:
• member _ no – has type int , NULL values are not allowed;
• last name – has type char ( 50 ) – 50 characters, NULL values are not allowed;
• first name – similar to lastname ;
• photo – has the type image (image), NULL is allowed .

CREATE TABLE member (

member_no int       NOT NULL ,

lastname char ( 50 ) NOT NULL ,

firstname char ( 50 ) NOT NULL ,

photo image

)

-- MySQL:

id INTEGER PRIMARY KEY AUTO_INCREMENT

-- MS SQL Server:

id INT identity ( 1 , 1 ) PRIMARY KEY

DDL

CREATE TABLE

• In this statement, you must specify the field name, its data type (the data type must be supported by the given DBMS), length (for some field types), and, if necessary, server constraints (using the CONSTRAINT keyword).
• For example, the following query creates a table named Simple with four columns – LastName , FirstName , Email and HomePage :

​

CREATE TABLE Simple (

FirstName varchar ( 50 ) NOT NULL ,

LastName varchar ( 50 ) NOT NULL ,

Email varchar ( 50 ),

HomePage varchar ( 255 )

)

DDL

CREATE TABLE

• We can extend this table by adding a PersonID field , which will be used as a primary key :

​

​

​

​

​

• and specify that the combination of the LastName and FirstName fields must be unique .

​

CREATE TABLE Simple (

PersonID Integer     NOT NULL PRIMARY KEY ,

FirstName varchar ( 50 ) NOT NULL ,

LastName varchar ( 50 ) NOT NULL ,

Email varchar ( 50 ),

HomePage varchar ( 255 )

)

DDL

CREATE TABLE Simple (

PersonID Integer NOT NULL PRIMARY KEY ,

FirstName varchar ( 50 ) NOT NULL ,

LastName varchar ( 50 ) NOT NULL ,

Email varchar ( 50 ),

HomePage varchar ( 255 ),

  CONSTRAINT SimpleConstraint UNIQUE (FirstName, LastName)

)

DROP TABLE

• To be able to delete a table, the user must have owner rights (i.e. be the creator) of that table.

​

• Before deleting a table from a SQL database, you will need to clear it of data.

DROP TABLE <table name>;

DDL

ALTER TABLE

• You can use the ALTER TABLE statement to change the structure of an existing table.
• Using it, you can add or remove a field or server restriction.
• There are four varieties of the ALTER TABLE statement:
• for adding columns to tables ;
• for adding server restrictions to tables ;
• to remove fields from tables ;
• to remove about indexed​ fields .

DDL

ALTER TABLE

ALTER TABLE table name

�,

DROP column name

�ADD column name data type

(size) NOT NULL

NOT NULL WITH DEFAULT

,

RENAME column name new name

TABLE new name

,

MODIFY column name

data type ( length ) NULL

NOT NULL

NOT NULL WITH DEFAULT

Changes the structure of columns in a table.

ALTER TABLE �( Referential Integrity )

ALTER TABLE table name

​

,

PRIMARY KEY (column name)

DROP

,

,

FOREIGN KEY (column name) REFERENCES parent table name

DROP foreign key

name

​

ON DELETE RESTRICT

CASCADE

SET NULL

Changes the PRIMARY KEY/FOREIGN KEY structure in a table.

ALTER TABLE

1. The first variant of this operator is used to add a column to a table , and its syntax is as follows:

​

​

• In queries of this type, the table name, the name of the new field, its data type, and, if necessary, the size are specified.
• Additionally, you can specify a server restriction associated with this field.
• For example, to add a Phone field to the Simple table , you can execute the following query:

ALTER TABLE table ADD [COLUMN] column datatype [(size)]

[CONSTRAINT sinlge-column-constraint]

ALTER TABLE Simple ADD Phone varchar ( 30 )

DDL

ALTER TABLE

• The column will be added with a NULL value. for all rows of the table.

​

• The new column will be the last column in the table.

​

• You can add multiple new columns at once, separated by commas, in one command.