Structured Query Language (SQL): An Overview
A database is a storage system that collects data or records. Database management systems are designed to manage databases. A database management system (DBMS) is a software system that uses a standard method to store and organize data. The data can be added, updated, deleted, or traversed using various standard algorithms and queries.
There are several types of database management systems. Here is a list of some common database management systems:
1. Hierarchical databases
2. Network databases
3. Relational databases
4. Object-oriented databases
5. Graph databases
6. ER model databases
7. Document databases
8. NoSQL databases.
For the purpose of this article, we will focus on Relational Database Management System (RDBMS). A Relational Database Management System is a software that enables the implementation of databases with tables, columns and indexes. There are different examples of RDBMS such as Oracle, Microsoft Access, MySQL, Informix, PostgreSQL, SQLite, MongoDB, Microsoft SQL Server and Cassandra.
Structured Query Language (SQL)
Structured Query Language is a computer language used for storing, manipulating and retrieving data stored in a relational database. SQL is the most popular query language used by major relational database management system such as MySQL, Oracle, Microsoft SQL Server etc.
1. SQL allows users to create and drop databases and tables.
2. SQL allows users to access data in relational database management systems.
3. SQL allows users to define data in a database.
4. SQL allows users to describe data in a database.
5. SQL allows users to create view, stored procedure and functions in a database.
6. SQL allows users to set permission on tables, procedures and views.
Entity Relationship Diagram (ER Diagram)
An ER Diagram describes the relationship of entities that needs to be stored in a database. It is a specialised framework of symbols that is used to define relationships between entities. They are created based in three main components which are: Entities, Attributes and Relationships.
ER Diagram helps to describe elements using entity Relationship model. It helps reduce complexity and saves time which allows users to build databases quickly. It gives better understanding of the information stored in a database. It helps to conceptualise databases and gives users the knowledge of what field(s) needs to be embedded with a particular entity.
Entity: An entity can be a living or non-living component which is depicted as a rectangle in an ER Diagram. A weak entity is one that relies over an entity and it is depicted as a double rectangle in an ER Diagram.
Attribute: An attribute describes the property of an entity which is depicted as an oval in an ER Diagram.
A key attribute uniquely identifies an entity from an entity set. The text of a key attribute is underlined.
A composite attribute is an attribute that is composed of other attributes and is depicted with an oval.
A multivalued attribute is am attribute that possess more than one value and it is depicted by a double oval.
A derived attribute is an attribute that can be extracted from other attributes and it is depicted by a dashed oval.
Relationship: A relationship is depicted by a diamond shape in an ER Diagram. It shows the relationship between entities. There are four types of relationships which are:
1. One-to-One Relationship: This occurs when a single element of an entity is associated with a single element of another entity e.g a student having an identification card.
2. One-to-Many Relationship: This occurs when a single element of an entity is associated with more than one element of another entity e.g a customer placing many orders in a store.
3. Many-to-One Relationship: This occurs when more than one element of an entity is related with a single element of an entity e.g a students enrolling for a course.
4. Many-to-Many Relationship: This occurs when one element of an entity is associated with more than one element of another entity e.g employees assigned to many projects in an organisation.
There are four main types of SQL commands, they are:
1. Data Definition Language (DDL): This changes the structure of a table like creating a table, deleting a table, altering a table etc. They are auto-committed which means it permanently saves all the changes in the databes. Some DDL commands includes: CREATE, ALTER, DROP and TRUNCATE.
2. Data Manipulation Language (DML): This modifies a database and responsible for all forms of changes in the database. They are not auto-committed which means it can’t permanently save all the changes in the database. Some DML commands includes: INSERT, UPDATE, DELETE.
3. Data Control Language (DCL): These are commands used to grant and take back authority from any database user. Some DCL commands includes: GRANT, REVOKE.
4. Transaction Control Language (TCL): These are commands used with DML commands. Some TCL commands includes: COMMIT, ROLLBACK, SAVEPOINT.
There are different data types in SQL which includes:
Numeric: int and float
Date and Time: date, time and timestamp
Unicode Character String: char and text
Character String: char and varchar
Binary: binary, varbinary and image
Miscellaneous: JSON, XML
Arithmetic: +, -, *, /, %
Logical: ALL, AND, ANY, BETWEEN, EXISTS
Comparison: =, !=,
Allen G. T (2019) SQL for dummies, John Wiley & Sons Inc, Hoboken, New Jersey.
Han-Petter. H (2017) Structured Query Language, retrieved from www.halvorsen.blog 27/04/2022.
Alan. B (2020) Learning SQL: Generate, Manipulate and Retrieve Data, O’Reilly Media Inc, Sebastopol, CA.