Although we all use different mobile applications and visit different websites, we rarely stop to think about how these are structured and function. In particular, even many developers, focus on design and programming language to make their project more attractive and efficient, leaving out one of the main components of any project: the database. Designing appropriately and writing queries correctly is fundamental to the development of any software. In this series of articles we want to introduce the most popular and widely used language for querying relational databases: SQL.
The Structured Query Language, or SQL for friends, is a programming language introduced with early versions of relational databases both to extract data from the database and to manipulate the data. It is essential to know it nowadays to work with databases and improve the efficiency of any project, from the smallest to the largest. In this article we will quickly introduce the data types that are available to relational databases, and next we will review the main instructions for creating a database and tables.
Table creation must come from a well-structured and accurate modeling process. If you want to learn more about this, we encourage you to read the book “Database designer: Dalla teoria alla pratica per apprendere i segreti della progettazione dei database relazionali” for now only available in Italian.
For simplicity’s sake, the commands we will report are related to MySQL, one of the most widely used databases in the world. In fact, being an open source and free product, despite being developed by a world giant like Oracle, it is chosen to support the creation of both Web sites (from blogs to e-commerce) and mobile applications. There are, of course, other relational databases starting with Oracle‘s commercial database and then moving on to PostgreSQL and ending with Access. Each database has its own peculiarities and also differs in some details in the SQL language. Therefore, it is always suggested to read the documentation of the database you are using.
Data types in SQL
In SQL there is a plethora of data types. They range from the most basic ones such as strings (e.g. VARCHAR) and numbers (e.g. INTEGER) to more complex ones such as geographic data and JSON. Although some of them are defined in the SQL standard, some types are specific to the database itself. A table with the various data types is given, which may not be exhaustive.
| Data type | SQL |
|---|---|
| Text | CHARACTER [VARYING] [(Length)] [CHARACTER SET FamilyNameCharacters] VARCHAR (Length) TEXT |
| Binary | BIT [VARYING] [(Length)] BLOB BINARY |
| Boolean | BOOLEAN |
| Integers | INTEGER SMALLINT BIGINT |
| Real numbers | NUMERIC [( Precision, Scale )] DECIMAL [( Precision, Scale )] FLOAT [(n)] REAL DOUBLE PRECISION |
| Time | TIMESTAMP [(Precision)] [WITH TIME ZONE] DATE DATETIME |
| JSON | JSON |
| Spatial | SDO_GEOMETRY GEOMETRY POINT LINESTRING POLYGON |
Creating a database
A database is a structured collection of data organized and stored in a way that allows efficient retrieval and manipulation of that data. The CREATE DATABASE command to create a new database. The syntax is as follows.
CREATE DATABASE db_name;
Database deletion
The DROP DATABASE command is used to delete an existing database. When you delete a database, all tables, data, and other database objects (indexes, views, etc.) are permanently deleted.
DROP DATABASE db_name;
Types of SQL commands
Before delving into the SQL language statements, it is appropriate to give a brief overview of the types of commands available. The commands can be divided into
- Data Definition Language (DDL): are used to define the database schema. DML commands are used to create, modify and delete database structures, but not data.
- Data Manipulation Language (DML): are used to read data and make changes to the contents of the database, i.e., all CRUD operations (create, read, update, and delete).
- Data Controlling Language (DCL): allow users’ permissions to be set.
- Transaction Control Language (TCL): are used to manage transactions in the database. They are used to handle changes made by DML statements. They also allow statements to be grouped into logical transactions.
Data Definition Language (DDL)
The Data Definition Language is used to define the structure or schema of the database. DDL is also used to specify additional properties of the data. The storage structure and access methods used by the database system are defined by a set of statements in a special type of DDL, called the Data Definition and Storage Language. These statements define the implementation details of the database schema, which are usually hidden from users. Data values stored in the database must satisfy certain consistency constraints.
For example, suppose the university requires that a department’s account balance never be negative. The DDL provides the ability to specify such constraints. The database system checks these constraints each time the database is updated. In general, a constraint can be an arbitrary predicate related to the database. However, arbitrary predicates can be expensive to test. Therefore, the database system implements integrity constraints that can be tested with minimal overhead.
The main constraints are:
- Domain Constraints: Each attribute must be associated with a domain of possible values (e.g., integer types, character types, date/time types). The declaration that an attribute belongs to a particular domain acts as a constraint on the values it can take.
- Referential integrity: there are cases where you want to ensure that a value that appears in one relation for a certain set of attributes also appears in a certain set of attributes in another relation, i.e., referential integrity. For example, the department listed for each course must be one that actually exists.
Creating a table
The CREATE TABLE command allows both the definition of all the attributes (the columns) of the table and the integrity constraints, if any. The syntax is as follows:
CREATE TABLE NomeTabella
(NomeAttributo Dominio [ValoreDiDefault ] [Vincoli]
{ , NomeAttributo Dominio [ValoreDiDefault ] [Vincoli ]}
AltriVincoli
);
Modifying a table
By means of in ALTER TABLE command you can:
- add a new column
- define a new default value for an existing column
- delete an existing column
- define a new integrity constraint
- delete an existing integrity constraint
The syntax is as follows:
ALTER TABLE NomeTabella
< ADD COLUMN <Definizione-Attributo> |
ALTER COLUMN NomeAttributo
< SET <Definizione-Valore-Default> | DROP DEFAULT>|
DROP COLUMN NomeAttributo
< CASCADE | RESTRICT > |
ADD CONSTRAINT [NomeVincolo]
< definizione-vincolo-unique > |
< definizione-vincolo-integrità-referenziale > |
< definizione-vincolo-check > |
DROP CONSTRAINT [NomeVincolo]
< CASCADE | RESTRICT >
The RESTRICT and CASCADE parameters indicate how the database should behave in the presence of some definition of another element (column or constraint) that references the element being modified. By default, the RESTRICT policy is implemented, which does not allow the operation if there are cross-references. Otherwise, CASCADE also operates on dependencies as long as there are any.
Deleting a table
The DROP TABLE command deletes the referenced table along with its contents. The syntax is as follows:
DROP TABLE NomeTabella [ RESTRICT | CASCADE];
By default the RESTRICT option is applied, which prevents the removal of the table if it is present in some table definition, constraint or view. With the CASCADE option, on the other hand, you modify the objects (views and tables) that reference the table itself.
One Response