Essential SQL and NONSQL concepts in software development
In this article, I want to explain the most essential database concepts that people on the software team should understand.
A Software development team creates the software program that consists of the front end and the back-end.
A user interacts with the software program through the front-end which refers to either a user interface screen or terminal.
The back-end consists of a Server/Cloud, the software program files, and the actual database program all stored on the Server/Cloud.
The entire back-end part is not visible to a user.
The database is a separate (either paid or open-source) software program stored in a particular file folder on a server/cloud.
A software developer writes programming code logic to store, process, and retrieve data to interact with a user interface, the physical database, and the server/cloud.
The software development team can choose to use either a SQL or NON-SQL database program in the software program.
It is important to understand key concepts about databases to be able to confirm whether a database interaction works as expected in a software program.
I came up with the following concepts important to understand.
A Database is a software program that stores data in a logical and organized approach.
There are two types of databases: SQL and NONSQL.
SQL Database
1. SQL database stores data in a table cell with a row (called a record) and a column(called database field).
2. SQL database contains at least one table.
3. SQL database field in a particular table allows only one type of data.
4. SQL database table field cannot contain mixed data types.
5. There are many SQL databases.
6. Each programming language either includes a built-in or add-on library code package to work with SQL databases.
7. A software developer is writing a SQL statement inside a programming language statement to programmatically interact with front-end and back-end.
SQL Database may have Primary and Foreign keys
1. The Primary Key is an individual column(s) with distinct value records used to separate records in the table.
2. Each primary key value corresponds to a single non-duplicate record in a table.
3. a Foreign key is a column(s) in a table that refers to either a primary key or a unique constraint column(s) in a different table.
4. The Primary and the Foreign Key define a relationship between two tables.
5. The Foreign Key contains identical records (rows) between the tables.
6. A table may have more than one foreign key column to connect more than one table.
7. Not all tables in a database have a primary key and foreign keys.
8. The column names between the tables may differ.
SQL Database may have A UNIQUE Constraint
1. A UNIQUE Constraint is a restriction that does not allow duplicate records in a column.
2. A table may have more than one unique constraint column.
SQL Database has a Data Model
The data model term refers to the relations among tables in a SQL database and the data each table holds.
SQL stands for Structured Query Language
1. The SQL query language will have slight syntax variations among different SQL databases.
2. A user or a database administrator can interact with a database directly without using the software program through modifying a database, saving new data, modifying existing data, and extracting data using SQL.
NoSQL Database
1. NoSQL database does not store data in tables with rows and columns
2. NoSQL database stores data based on the following common database types: Key-value, Wide Column, Document, and Graph
3. There are many types of NONSQL databases where common types are outlined below:
Key-value Database:
a) Store data in two columns as key-value pairs.
b) A key distinguishes a database record.
c) A value can range from a simple data type to an object.
Wide Column Database:
a) Stores data in columns.
b) Each row consists of a key and at least one column.
c) The column is a key value.
d) Each column may contain more than one column inside the single column.
e) Each row may have a different number of columns with different data.
Document Database:
a) Stores data in documents.
b) Each document has a key associated with it.
c) The document format may be XML or JSON.
d) Each document may have a different layout.
Graph Database:
a) Graph database is based on the Mathematical Graph theory.
b) A graph consists of dots and lines that connect the dots (called vertices or nodes).
c) The lines are called relationships.
d) An edge is pair of closest nodes.
e) A property is data in the key-value pair format.
f) Nodes and edges can hold property data.
4. Each major programming language either includes a built-in or add-on library code package to work with NONSQL databases.
5. A software developer is writing a statement inside a programming language statement to programmatically interact with front-end and back-end when using NONSQL database.
6. Each NONSQL database uses own query language if you want to find something on your own in the database outside the software program.
General Database notes
- Databases are important in software testing b/c a software test engineer needs to confirm that a value is properly saved in a database and properly retrieved from a database.
2. When a user saves a value on a screen, the software program stores the value in the database.
3. To check a value saved on the screen, the test engineer will need to find a location in a database that stores a value from the particular screen.
4. Please note that a default screen value is not stored in a database.
5. When a user opens a screen with values filled out, the software program extracts values from the database and displays the values on the screen.
6. When a user copies values inside the software program, the database creates an additional database record in at least one part of the database storage area.
7. When a user generates a report or performs calculations, the software program extracts data from the database, processes data, and generates an output file.
In conclusion, I highlighted the most essential concepts people on the software development team should understand about databases.
Thank You for Reading
