SQL
Structured Query Language, is also known as Relational Database
Keywords
Why SQL is called Relational Database?
A relational database is one that stores data in tables. The relationship between each data point is clear and searching through those relationships is relatively easy.
The relationship between tables and field types is called a schema. For relational databases, the schema must be clearly defined
Relationships are established through Primary and Foreign keys
What is normalisation?
Database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.
Normalisation rules divides larger tables into smaller tables and links them using relationships.
The purpose of Normalisation in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.
What is a key?
A value used to identify a record in a table uniquely.
A KEY could be a single column or combination of multiple columns
Primary Key:
A primary key is a single column value used to identify a database record uniquely
A primary key cannot be NULL
A primary key value must be unique
The primary key values should rarely be changed
The primary key must be given a value when a new record is inserted
Composite Key: is a primary key composed of multiple columns used to identify a record uniquely
Foreign Key:
A foreign key is references the primary key of another Table
A foreign key can have a different name from its primary key
It ensures rows in one table have corresponding rows in another
Unlike the Primary key, they do not have to be unique. Most often they aren't
Foreign keys can be null even though primary keys can not
Schema and Queries Use Case?
Use Cases
How normalisation is used?
How multiple tables are used to store data?
How concepts of primary and foreign keys are used to retrieve data and presented as per requirement?
All this concepts can be understood by looking at one use case of Stack Overflow data, go through the "Use Cases" page link as attached, to understand it better
SQL Database Properties
ACID Property
Atomicity: Atomicity means that you guarantee that either all of the transaction succeeds or none of it does. You don’t get part of it succeeding and part of it not.
If one part of the transaction fails, the whole transaction fails. With atomicity, it’s either “all or nothing”.
Consistency: This ensures that you guarantee that all data will be consistent.
All data will be valid according to all defined rules, including any constraints, cascades, and triggers that have been applied to the database.
Isolation: Guarantees that all transactions will occur in isolation.
No transaction will be affected by any other transaction. So a transaction cannot read data from any other transaction that has not yet completed.
Durability: Durability means that, once a transaction is committed, it will remain in the system – even if there’s a system crash immediately following the transaction.
Any changes from the transaction must be stored permanently. If the system tells the user that the transaction has succeeded, the transaction must have, in fact, succeeded
References for Further Reading
Last updated
Was this helpful?