SQL: creating a database

Relational databases play a key role in the design and development of any software application. Learning how to model and query a database in addition to properly manipulating data using the SQL language is within everyone's reach. Let's start by discovering some features of this language through the instructions for creating a database and defining its constituent tables.

Share

Reading time: 4 minutes

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.

More To Explore

Python language

Pandas: data analysis with Python [part 2].

Pandas is a Python library that allows us to analyze data from a variety of sources. Among the most useful features we surely find several functions to clean our data and extract some statistics about the distribution of values of various attributes. In addition, we can create aggregations with different logics and graph the data to extract more information. Let’s find out how to do all this with just a few lines of code!

Python language

Pandas: data analysis with Python [part 1].

Data scientists continually need to read, manipulate, and analyze data. In many cases they use specific tools, but sometimes they need to develop their own code. To do this, the Pandas library comes to our aid. Let’s learn about its data structures, how we can read data from different sources and manipulate it for our purposes.

One Response

Leave a Reply

Your email address will not be published. Required fields are marked *

Progetta con MongoDB!!!

Acquista il nuovo libro che ti aiuterà a usare correttamente MongoDB per le tue applicazioni. Disponibile ora su Amazon!

Design with MongoDB

Design with MongoDB!!!

Buy the new book that will help you to use MongoDB correctly for your applications. Available now on Amazon!