What is SQL?
Standard computer language for Relational Database Systems. It’s used to store, manipulate and retrieve data in a relational database.
It allows users to access and manipulate data, create or destroy database objects and set user permissions.
SQL Commands
The standard SQL commands used to interact with the database are classified into 3 distinct categories (DDL, DML and DCL)
- DDL – Data Definition Language
| CREATE | Creates a new database object (table, view, etc) |
| ALTER | Modifies an existing database object |
| DROP | Deletes objects in the database |
- DML – Data Manipulation Language
| SELECT | Retrieves records from one or more tables |
| INSERT | Creates a record |
| UPDATE | Modifies records |
| DELETE | Deletes records |
- DCL – Data Control Language
| GRANT | Assings privileges to users |
| REVOKE | Removes granted privileges |
Constraints
Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can be inserted into a column, ensuring the accuracy and reliability of the data in the database.
Some of the most commonly used constraints in SQL are:
- NOT NULL – Ensures that a column cannot have a NULL value
- UNIQUE – Ensures that all the values in a column are different
- PRIMARY KEY – Refers to a primary key in another table. Links both table’s data and prevents errors.
- FOREIGN KEY – Uniquely identifies a row in another table
- CHECK – Ensures that all values in a column satisfy certain criteria
- INDEX – Used to create and retrieve data from the database very quickly
RDBMS
RDBMS stands for Relational Database Management System, and is the basis for SQL. It’s a database management system that is based on the relational model (all the data is related to each other ).
The data in a RDBMS is stored in database objects called tables. A table is basically a collection of related data, consisting of columns and rows.
A column is a field in a table designed to maintain specific information about every record in the table.
A row is an individual record of data in a table. It is a collection of attributes.
Designing a database: Best Practices
- Consider every viewpoint
- Don’t start building a database without input from the project owner and stakeholders.
- Get consensus on precise expectations/functionality
- Choose a database type
- Usually as easy as deciding between SQL and NoSQL
- Depending on the project there are types of databases more appropriate, e.g., Oracle for large organizations with structured data, SQL Server for error handling or XML/JSON integrations and NoSQL for machine learning and IoT applications
- Normalize data
- Data normalization is critical to achieve minimum redundancy and data consistency
- When a database abides by the first three normal forms we can say it’s normalized
- Transparency
- Design with the end users in mind
- Use consistent naming conventions, as to make things easy for those who may need to modify the database in the future
- Define constraints
- This is important to maintain data integrity and enforce business rules
- This way the database will prevent bad data from getting in
- Document everything
- Documentation is essential, although sometimes annoying, promotes a clean development and makes maintenance easy
- Document the design, implementation and every database object for future users
- Keep privacy primary
- In the era of GDPR privacy concerns are increasingly important
- Encrypt passwords and be careful when assigning administrator permissions
- Vulnerabilities impact data integrity, which impacts everything else
- Optimize for speed
- Create indexes for queries that are used regularly
- Use an analysis tool to check if an index or clustered index is necessary
- Avoid dynamic SQL
- Always avoid dynamic queries, as this type of development can lead to SQL injection attacks.
If you enjoyed this text, continue to explore our blog and check out other technical articles and guides.
