ડેટાબેઝ મેનેજમેન્ટ સિસ્ટમ્સ (DBMS) અને SQL — ટેકનિકલ રિવિઝનDBMS, Relational Algebra & SQL Cheat Sheet
નોર્મલાઇઝેશન (1NF થી BCNF), ACID ગુણધર્મો, ટ્રાન્ઝેક્શન આઇસોલેશન, TRUNCATE vs DELETE, અને SQL એક્ઝિક્યુશન ઓર્ડર.Normalization (1NF to BCNF), ACID properties, Transaction isolation levels, Relational Keys, TRUNCATE vs DELETE, and SQL execution order.
🗄️ ડેટાબેઝ મેનેજમેન્ટ સિસ્ટમ્સ (DBMS) અને SQL — કોર ટેકનિકલ રિવિઝન
🎯 પરીક્ષા સંદર્ભ: GPSC ICT ઓફિસર (વર્ગ-૨), આસિસ્ટન્ટ એન્જિનિયર (કમ્પ્યુટર/IT), અને સરકારી ટેકનિકલ ભરતી પરીક્ષાઓમાં DBMS ના પ્રશ્નોનું ભારણ ઘણું ઊંચું હોય છે. નોર્મલાઈઝેશન (1NF થી BCNF), ટ્રાન્ઝેક્શન ACID પ્રોપર્ટીઝ, આઇસોલેશન લેવલ્સ અને SQL આદેશોમાંથી સીધા કન્સેપ્ટ આધારિત પ્રશ્નો પૂછાય છે.
📋 ૧. રિલેશનલ નોર્મલ ફોર્મ્સ (Normalization) અને વિસંગતતા નિવારણ
| નોર્મલ ફોર્મ | કઈ ખામી દૂર કરે છે? | મુખ્ય ફંક્શનલ ડિપેન્ડન્સી (FD) નિયમ | વિશેષ ટેકનિકલ નોંધ |
|---|---|---|---|
| 1NF (પ્રથમ) | મલ્ટી-વેલ્યુડ એટ્રિબ્યુટ્સ અને રિપીટિંગ ગ્રુપ્સ | કોષ્ટકના તમામ ખાનામાં કિંમતો પરમાણ્વિક (Atomic - અવિભાજ્ય) હોવી જોઈએ. | એક જ ખાનામાં અલ્પવિરામથી બે મૂલ્યો ન હોઈ શકે. |
| 2NF (દ્વિતીય) | આંશિક નિર્ભરતા (Partial Dependency) | 1NF માં હોવું જોઈએ + કોઈપણ નોન-પ્રાઈમ એટ્રિબ્યુટ કેન્ડિડેટ કીના કોઈ એક ભાગ પર આંશિક રીતે નિર્ભર ન હોવું જોઈએ. | સંપૂર્ણ નિર્ભરતા (Full Functional Dependency) હોવી જરૂરી છે. |
| 3NF (તૃતીય) | ટ્રાન્ઝિટિવ નિર્ભરતા (Transitive Dependency) | 2NF માં હોવું જોઈએ + પ્રત્યેક નોન-ટ્રિવિયલ $X o A$ માટે:
• $X$ સુપર કી (Super Key) હોવી જોઈએ, અથવા • $A$ પ્રાઈમ એટ્રિબ્યુટ (Prime Attribute) હોવો જોઈએ. |
3NF માં Lossless Join અને Dependency Preservation બંનેની ગેરંટી હોય છે. |
| BCNF | તમામ FD આધારિત રિડન્ડન્સી | પ્રત્યેક નોન-ટ્રિવિયલ $X o A$ માટે $X$ ફરજિયાતપણે સુપર કી જ હોવી જોઈએ. | 3NF કરતાં વધુ કડક. Lossless join હોય છે, પરંતુ તમામ ડિપેન્ડન્સી સચવાય તે જરૂરી નથી. |
⚠️ GPSC ટેકનિકલ ટ્રેપ — SQL માં TRUNCATE વિરુદ્ધ DELETE:
| માપદંડ | TRUNCATE | DELETE | DROP |
|---|---|---|---|
| ભાષા પ્રકાર | DDL (Data Definition Language) | DML (Data Manipulation Language) | DDL |
| રોલબેક (Rollback) | સરળતાથી રોલબેક ન થાય (ઓટો-કમિટ થાય) | ટ્રાન્ઝેક્શનમાં રોલબેક શક્ય છે | રોલબેક શક્ય નથી |
| WHERE ક્લૉઝ | ના (સમગ્ર ટેબલનો ડેટા ખાલી કરે) | હા (શરત મુજબ ચોક્કસ રેકોર્ડ ડિલીટ થાય) | ના (ટેબલ માળખું જ કાઢી નાખે) |
| ટ્રિગર્સ (Triggers) | DELETE ટ્રિગર્સ ફાયર થતા નથી | રો-લેવલ ટ્રિગર્સ ફાયર થાય છે | ટ્રિગર્સ ફાયર ન થાય |
| Auto-Increment | કાઉન્ટર રીસેટ (૧ થી શરૂ) થઈ જાય છે | કાઉન્ટર રીસેટ થતું નથી (જ્યાં હતું ત્યાંથી આગળ વધે) | સ્ટ્રક્ચર જ નાશ પામે |
🔄 ૨. ટ્રાન્ઝેક્શનના ACID ગુણધર્મો
- એટોમિસિટી (Atomicity - All or Nothing): ટ્રાન્ઝેક્શનના તમામ કાર્યો સફળતાપૂર્વક પૂર્ણ થાય અથવા એકપણ કાર્ય ન થાય (સંપૂર્ણ રોલબેક). Recovery Manager દ્વારા WAL (Write-Ahead Logging) વડે નિયંત્રિત થાય છે.
- કન્સિસ્ટન્સી (Consistency): ટ્રાન્ઝેક્શન પહેલાં અને પછી ડેટાબેઝના તમામ નિયમો (પ્રાઇમરી કી, ફોરેન કી) અકબંધ રહેવા જોઈએ.
- આઇસોલેશન (Isolation): એકસાથે ચાલતા અનેક ટ્રાન્ઝેક્શન્સ એકબીજાના કાર્યમાં ખલેલ પહોંચાડ્યા વિના સ્વતંત્ર રીતે ચાલે છે. Concurrency Control Manager દ્વારા હેન્ડલ થાય છે.
- ડ્યુરેબિલિટી (Durability): એકવાર ટ્રાન્ઝેક્શન કમિટ (Commit) થઈ જાય પછી સિસ્ટમ ક્રેશ થાય તો પણ તેના ફેરફારો હંમેશ માટે સચવાયેલા રહે છે.
🔒 ૩. ટ્રાન્ઝેક્શન આઇસોલેશન લેવલ્સ
| Isolation Level | Dirty Read (કાચો ડેટા વાંચવો) | Non-Repeatable Read (કિંમત બદલાઈ જવી) | Phantom Read (નવો રેકોર્ડ ઉમેરાઈ જવો) |
|---|---|---|---|
| Read Uncommitted | પરવાનગી મળે ❌ | પરવાનગી મળે ❌ | પરવાનગી મળે ❌ |
| Read Committed | અટકાવાય ✅ | પરવાનગી મળે ❌ | પરવાનગી મળે ❌ |
| Repeatable Read | અટકાવાય ✅ | અટકાવાય ✅ | પરવાનગી મળે ❌ |
| Serializable | અટકાવાય ✅ | અટકાવાય ✅ | અટકાવાય ✅ (સૌથી ઉચ્ચ સુરક્ષા) |
⚡ SQL ક્વેરીનો સાચો એક્ઝિક્યુશન ક્રમ (Execution Order):
SQL એન્જિન લખેલા ક્રમમાં નહીં પરંતુ આ ચોક્કસ તાર્કિક ક્રમમાં ક્વેરી રન કરે છે:
FROMઅનેJOIN(ટેબલ ભેગા કરવા)WHERE(રો ફિલ્ટર કરવી - એગ્રીગેટ ફંક્શન અહીં ન વપરાય)GROUP BY(ગ્રુપીંગ કરવું)HAVING(ગ્રુપ ફિલ્ટર કરવું)SELECT(કોલમ પસંદ કરવી અને એલિઆસ આપવા)DISTINCT(ડુપ્લિકેટ દૂર કરવા)ORDER BY(ક્રમમાં ગોઠવવું)LIMIT / OFFSET(પરિણામ મર્યાદિત કરવું)
🔑 ૪. રિલેશનલ કી (Keys) નું વર્ગીકરણ
- સુપર કી (Super Key): કોષ્ટકના કોઈપણ રેકોર્ડને અનન્ય (Unique) રીતે ઓળખતા એક કે તેથી વધુ એટ્રિબ્યુટ્સનો સમૂહ.
- કેન્ડિડેટ કી (Candidate Key): લઘુત્તમ સુપર કી (Minimal Super Key). જેમાંથી કોઈપણ એટ્રિબ્યુટ હટાવતાં યુનિકનેસ જળવાતી નથી.
- પ્રાથમિક કી (Primary Key): ડેટાબેઝ ડિઝાઇનર દ્વારા પસંદ કરાયેલી મુખ્ય કેન્ડિડેટ કી. તેમાં ક્યારેય NULL વેલ્યુ ન હોઈ શકે.
- ફોરેન કી (Foreign Key): અન્ય ટેબલની પ્રાઇમરી કી સાથે સંબંધ બાંધી Referential Integrity જાળવતી કી.
📚 પ્રમાણિત સંદર્ભ સાહિત્ય
- Database System Concepts: Korth, Silberschatz, Sudarshan.
- Fundamentals of Database Systems: Elmasri & Navathe.
- GATE Computer Science (DBMS): Made Easy Publications.
🗄️ Database Management Systems & SQL — Core Technical Revision
🎯 High-Yield Exam Context: DBMS is a high-scoring section in GPSC ICT Officer, Assistant Engineer, and State CBT IT recruitment exams. Over 60% of technical DBMS questions test Normalization conditions (3NF vs BCNF), ACID guarantees, Isolation phenomena, and SQL execution order.
📋 1. Relational Normal Forms & Anomaly Elimination
| Normal Form | Eliminates Anomalies | Core Functional Dependency (FD) Rule | Key Properties & Guarantees |
|---|---|---|---|
| 1NF (First Normal Form) | Repeating groups & multi-valued attributes | All attribute values must be atomic (indivisible single values). | No arrays, lists, or nested relations in column values. |
| 2NF (Second Normal Form) | Partial functional dependencies | 1NF + No non-prime attribute is functionally dependent on a proper subset of any candidate key. | Full functional dependency required. If candidate key is a single attribute, 1NF relation is automatically in 2NF. |
| 3NF (Third Normal Form) | Transitive functional dependencies | 2NF + For every non-trivial FD $X o A$, either:
• $X$ is a Super Key, OR • $A$ is a Prime Attribute (part of some candidate key). |
Guaranteed to be both Lossless Join and Dependency Preserving. |
| BCNF (Boyce-Codd Normal Form) | All redundancy from functional dependencies | For every non-trivial FD $X o A$, $X$ MUST strictly be a Super Key. | Stricter than 3NF. Always Lossless, but may NOT preserve all functional dependencies. |
⚠️ GPSC Technical Trap — SQL DDL vs DML Differences:
| Dimension | TRUNCATE | DELETE | DROP |
|---|---|---|---|
| Language Category | DDL (Data Definition) | DML (Data Manipulation) | DDL (Data Definition) |
| Transaction Rollback | Cannot be easily rolled back (Auto-commit in most engines) | Can be rolled back within active transaction | Cannot be rolled back |
| WHERE Clause | No (removes entire table data) | Yes (conditional row deletion) | No (drops entire table schema) |
| Trigger Execution | Does NOT fire DELETE triggers | Fires row-level triggers | Does not fire row triggers |
| Identity Reset | Resets AUTO_INCREMENT counter | Retains AUTO_INCREMENT counter | Destroys structure |
🔄 2. ACID Properties & Database Engine Subsystems
- Atomicity (All-or-Nothing): Either all operations of a transaction succeed, or the entire transaction is rolled back. Managed by the Recovery Manager using Write-Ahead Logging (WAL).
- Consistency: Preserves database integrity constraints (primary keys, foreign keys, check constraints) from state $S_1$ to $S_2$.
- Isolation: Concurrent transactions execute as if they were running serially without interference. Managed by the Concurrency Control Manager.
- Durability: Once a transaction commits, its modifications are permanently recorded in non-volatile storage, surviving OS crashes and power outages.
🔒 3. Transaction Isolation Levels & Concurrency Anomalies
| Isolation Level | Dirty Read (Read uncommitted data) | Non-Repeatable Read (Row values modified mid-transaction) | Phantom Read (New rows inserted into query range) |
|---|---|---|---|
| Read Uncommitted | Allowed ❌ | Allowed ❌ | Allowed ❌ |
| Read Committed | Prevented ✅ | Allowed ❌ | Allowed ❌ |
| Repeatable Read | Prevented ✅ | Prevented ✅ | Allowed ❌ |
| Serializable | Prevented ✅ | Prevented ✅ | Prevented ✅ (Highest Isolation) |
⚡ SQL Logical Query Execution Order (Crucial for Debugging Queries):
SQL engines execute clauses in this strict order, NOT as written:
FROM&JOIN(Gathers source tables and cartesian/join products)WHERE(Filters raw table rows before aggregation; cannot use aggregate functions like SUM)GROUP BY(Aggregates rows into buckets)HAVING(Filters aggregated buckets; can evaluate aggregate conditions)SELECT(Projects columns, evaluates expressions and aliases)DISTINCT(Removes duplicate result tuples)ORDER BY(Sorts final result set; can reference SELECT aliases)LIMIT / OFFSET(Paginates output rows)
🔑 4. Relational Keys Hierarchy
- Super Key (SK): Any set of attributes whose values uniquely identify an entity tuple in a relation.
- Candidate Key (CK): A minimal Super Key (removing any single attribute destroys its uniqueness). A relation can have multiple candidate keys.
- Primary Key (PK): The candidate key selected by the database architect as the primary row identifier. Strictly forbids NULL values.
- Foreign Key (FK): An attribute in a child table referencing the primary key of a parent table, enforcing Referential Integrity.
🔒 5. Concurrency Control & Two-Phase Locking (2PL)
| 2PL Locking Variant | Lock Release Mechanism | Serializability Guaranteed? | Deadlock & Cascading Rollback Risk |
|---|---|---|---|
| Basic 2PL | Growing Phase (acquire) → Shrinking Phase (release). Once a lock is released, no lock can be acquired. | Guarantees Conflict Serializability | Prone to Deadlocks and cascading aborts. |
| Strict 2PL | All Exclusive (Write) locks must be held until transaction termination (Commit/Abort). | Yes | Eliminates Cascading Aborts; deadlocks still possible. |
| Rigorous 2PL | All Shared (Read) and Exclusive (Write) locks held until transaction termination. | Yes | Completely eliminates cascading rollbacks; maximum locking overhead. |
📚 Authoritative Reference Books
- Database System Concepts: Abraham Silberschatz, Henry F. Korth, S. Sudarshan (McGraw Hill).
- Fundamentals of Database Systems: Ramez Elmasri & Shamkant B. Navathe (Pearson).
- GATE Computer Science & IT (DBMS): Made Easy Publications.
હવે પોતાની કસોટી કરોTest yourself next
દરેક પરીક્ષાનો મોક 1 ફ્રી છે, સાઇન અપ વગર, અને દરેક પ્રશ્નની ગુજરાતી અને અંગ્રેજી સમજૂતી છે.Mock 1 of every exam is free, with no sign-up, and every question has a Gujarati and English explanation.