Database Models
Database models allow developers and data analysts to work with structured, semi-structured, and unstructured data. Furthermore, they enable managing transactions using various languages and tools.
Relational databases organize information into tables containing rows and columns. They use unique identifiers called primary keys to link records across tables.
Relational Databases
Relational databases organize data records in tables with rows and columns, similar to spreadsheet programs. Each table is identified with its own unique identifier called a primary key; foreign keys allow connections among data records in different tables through foreign key references that point back to another primary key’s primary key value.
Each value in a table is assigned the same data type, making it easy for application developers to use database functions they need for retrieving and processing of the information in it. Furthermore, rows from this particular table can be retrieved in any order, making addition of new information seamless without altering other rows in it.
One drawback of relational database models is their difficulty managing relationships between multiple tables, potentially leading to inconsistent data in applications and databases. Furthermore, maintaining this model when data changes can be costly.
SQL
SQL (Structured Query Language) is a non-procedural programming language designed for database systems that allows you to add, modify and delete data as well as query for information. Used widely within relational database models.
Database models provide visual representations of databases to make their structure easily understood, with Entity-Relationship Model (ER Model) being the most commonly employed approach to modeling databases. By breaking it down into entities, attributes, and relationships, an ER Model allows designers to communicate better with stakeholders and users while decreasing revision time and rework costs.
Relational databases organize data in tables with rows and columns. They support one-to-many relationships, which offer greater flexibility than traditional hierarchical databases. For example, one student may belong to multiple classes, and vice versa; these models are widely used for eCommerce websites and business applications and supported by NoSQL databases allowing more flexibility.
Normalization
Normalization is a database design technique used to reduce redundant data and ensure dependencies make sense. It works by breaking larger tables into multiple smaller ones connected by relationships. First developed by Edgar Codd in 1948, normalization can now be found all around the world with several advantages that include improved query response time and lower storage costs.
Normalizing data also reduces insertion, update and deletion anomalies. For example, if your table stores customer records with their prices associated with each record in it, normalization allows you to create another table with only customer records, so that any deletions don’t impact customer records in any way.
Notably, database performance degrades when normalizing relations to higher normal forms, such as 4NF or 5NF. You may experience significant data anomalies. Therefore it is crucial that you gain an in-depth knowledge of normalization prior to applying it in your database.
ACID properties
Transactions are series of operations that must be successfully completed to leave the database in an acceptable state and ensure application availability and data integrity. A failed transfer between savings and checking accounts could leave users without their funds, necessitating ACID properties being met to maintain application availability and data integrity.
These four properties are: atomicity, consistency, isolation and durability. Atomicity refers to when operations will either succeed or fail in one step; consistency ensures that transactions do not violate certain constraints; isolation keeps effects of transactions invisible until committed; and durability makes sure any changes made will remain visible after their completion.
Recently, non-relational databases did not support ACID transactions. But this changed with the launch of two leading document databases such as MongoDB and RavenDB that have recently implemented ACID capabilities at cluster level.