1. Sql - Introduction
What is Data and What is Strcutured Data ?
Data - piece of information that can be stored and processed.
Strcutured data - data that is organized in a predefined format so that it can be easily stored and processed.
Ex: DB table is the example of structured data
WAYS TO KEEP DATA STRCUTURED ?
Database Tables (Most Common) - Keep data in tables in relational databses like MySql and postgres
JSON Format
WHY SQL NEEDED ? Why no just excel files ?
1. Stored Structured Data
2. Query the Data
3. Update/Delete Data efficiently
4. Provide security as only that person access data who have acces of it.
-> In Sql make searching fast while in excel/file have slow.
-> Sql have millions of rows while excel dont have if we even try then it is difficult it maintain such huge data in excel
-> Sql have high security but excel have just basic security.
One Line -> Sql handle large complex datasets while excel handle only small simple datasets.
DATABASES
Organised collection of data.
RELATIONAL(SQL) vs NON RELATIONAL(NO SQL)
SQl -> Data stored in tables , Data is highly strcutured , Uses in banking and e-commerce transactions
NoSQL -> Data stored in documents , key-value pairs , Data is less sstructred , Uses in Real time application , socila media etc. , (More flexible)
WHAT IS SQL ?
SQL is a structured query language used to intract with relational databases.
It makes data retrival efficient , updateion , deletion efficient.
Relational DataBase Management System (RDMS)
It is software used to store, manage, and retrieve data in the form of tables, where tables can be related to each other using keys.
Ex: MySQL -> Popular open source RDMS used in WEB DEV
PostgresSQL -> Advance open source RDMS , used to handle complex queries
SQLIte -> Used in mobile /local setups
Oracle -> enterprise level , SQLServer
SCHEMA
A schema is like the blueprint of your database.
It describe tables , fields , relationship
Think of it like a map that tell the database how your data is orgainised.
DATABASE NORMALIZATION
Database Normalization is the process of organizing data in a database to reduce redundancy (duplicate data) and improve data integrity.
Need of Normalization real world example:
For an e-commrece platform you want to store data of customer
If we store data in a table like this : customerid | customerName | Mobile | Product
then there is a chances of redundancy(duplicate customer name)
Intead of this we can store data in multiple tables(normalize this) like this:
Customers -> cusomerId | Name | Phone
Product -> Id | Name | Rating
Order -> OrderId | CustommerId
This will avoid redundancy
Anomaly -> Inconsistencty
FORMS/RULES of NORMALIZATION
We can achieve normalization from following ways -:
1. 1NF (First Normal Form):
Each col should contain atomic(individual) values.
Still above have some problems
2. 2NF (Second Normal Form)
Must already in 1NF + No Partial dependecy -> which means A non-key column should depend on the whole primary key(composite key), not part of it.
3NF
Already in 2NF + No Transitive dependecy which means Every non key attribute depends only on the primary key.
BCNF (Boyece codd normal form)
Advance version of 3NF : every determinant should be a candidate key.Example of Normalization for All : TODO
Entity Relationship(ER) Diagrams
It is the visual representation of how entities (tables) related to each other.
Entity -> Table
Attributes -> Columns
Relationships-> How tables connect
PRIMARY KEY
A column (or set of columns) that uniquely identifies each row in a table.
-> Can not be null
-> Must be unique
FOREIGN KEY
A column in one table that refers to the Primary Key of another table.
It creates a relationship between tables and maintains referential integrity.
RELATIONSHIP TYPES
1. One to One (1:1) -> One record in Table A is related to only one record in Table B, and vice versa.
Ex: We have user and Passport table ; In user table we have passport id as foreign key that mean one use have only one passport which is one to one.
2. One to Many(1:M) -> One record in Table A can be related to many records in Table B.
Ex: One teacher can teach many students ; We have teacher table and student table ; In student table we have same teacher ids with different students that mean one teacher related to many student:
3. Many to Many (M:M) -> Many records in Table A can be related to many records in Table B.
Ex: A student can have many courses and a course can have many students.
(Table meh 2ono col me same chez repeat ho jaay)
TODO: ER diagrams practice
TODO: Convert ER Diagrams to SQL Table
TODO: Normalization