Table of contents
- Data --> Information
- Database --> Storage of Information
- Database Management System --> Software that helps in performing CRUD (Create, Read, Update, Delete) operations to data in the database
- Relational Data --> Data that has relations between entities
- Entity --> Any real world object that is represented in the form of tables in a database
- Relational Data Model --> Model data in the form of relations
- Relational Database --> Database that stores structured and relational data based on Relational Data Model
- Relational Database Management System --> Software that helps in performing CRUD operations to structured data in Relational Database
🚨 I'm writing this blog as a reference to myself regarding all things related to databases. If you find the content a bit unstructured or if it doesn't make sense that's the reason why.
Structured vs Unstructured Data
Structured Data
Organized. Can be put in tables, rows, columns& relationships.
Example: Dates, customer details, employee details, order details, etc.
Less storage space required & easier to process information.
Unstructured Data
Unorganized. Can't be put in tables, rows, columns & relations.
Example: Images, videos, audio, JSON files, emails, etc.
More storage is required and difficult to manage & process information.
Relational Databases vs NoSQL Databases
Relational Databases
Data items have pre-defined relationships between them.
Information is stored in structured tables with rows & columns.
Example: MySQL, IBM DB2, Microsoft SQL Server
NoSQL Databases
Uses a storage model optimized for the specific requirements of the type of data being stored.
Types of NoSQL Databases: Document databases, Key-value stores, Column-oriented databases, Graph databases.
Reference: mongodb.com/scale/types-of-nosql-databases
Example: MongoDB, Apache Cassandra, Redis and Couch base.
Relational Data Model
Model data in the form of relations.
Based on two concepts: tables, relations.
Relationships - 1:1, 1:N, N:N
1:1 (One-to-One)
Example:
[Table] Citizens of a country <--> [Table] Unique Identification
One person or record in the 'Citizens of a country' can have only one record of 'Unique Identification' in that table.
1:N (One-to-Many)
Example:
[Table] Country <--> [Table] Residents
One country or record in the 'Country' table can have many records or residents in the 'Residents' table. This can also be N:1 depending on the tables.
N:N (Many-to-Many)
Example:
[Table] Subjects <--> [Table] Students
One subject or record in 'Subjects' table can have many records or students in the 'Student' table. Similarly, one student or record in the 'Students' table can be taking many subjects or records in the 'Subjects' table.
Scenarios for using RDBS
Strict schema
- Fixed columns in a table
- Each entity(table) has a fixed number of attributed
Relational data
Transactional data (either, or)
- Doesn't necessarily mean only data related to payments / transactions
- Only one of two scenarios can happen (either, or)
When data can be stored on a single server
- Need to learn more about this!
- What if the data is stored on multiple servers? Is RDBS not recommended then?
Industrial use cases - RDBMS
- Banking: Money transaction system
- Payment wallets: Google Pay, PhonePe, etc.
- E-Commerce: Payments, users, etc.
- Restaurant listings: Zomato, Swiggy, etc.
- Employee management softwares
- Twitter uses MySQL for primary storage of tweets & users
- Youtube uses MySQL
Other important things
Primary key: A key which can be used to uniquely identify each record in a RDBMS. Usually a single unique column is used for this but if there no unique columns, sometimes a combination of multiple columns is used. A key made by combining multiple columns is called a Composite Key.
Foreign key: A field in one table that acts as a primary key in another table, thus helping us identify the rows in the latter table uniquely.
Schema: Refers to the organization of data as a blueprint of how the database in constructed.