Database Management: formula sheet

Key formulas

Data vs information

Information = processed, organised data that is meaningful for decisions

Use this to open any definition answer.

Hierarchical model

Tree structure; one parent per child; one-to-many links

Access starts from the root. Example: organisation chart.

Network model

Graph structure; a child can have many parents; many-to-many links

More flexible than hierarchical but more complex to design and maintain.

Relational model

Data in tables (relations) of rows (tuples) and columns (attributes); linked by keys

Queried with SQL. Rows are records, columns are fields.

Object-oriented model

Data stored as objects = attributes + methods; supports classes and inheritance

Suited to complex data such as multimedia and design data.

DBMS advantages

Less redundancy, better consistency, data sharing, security, integrity, backup and recovery, data independence

Contrast each with the file system problem.

Tier architecture

One-tier: user + application + data together | Two-tier: client ↔ database server | Three-tier: client ↔ application server ↔ database server

The key difference is that in three-tier the client never accesses the database directly.

Three schema levels

External (user views) → Conceptual (whole logical design) → Internal (physical storage)

Remember the order from user side to storage side.

Data independence

Logical = change conceptual schema without changing external views/programs | Physical = change internal schema without changing conceptual schema

Mappings between levels make this possible.

Database languages

DDL: CREATE, ALTER, DROP, TRUNCATE | DML: INSERT, UPDATE, DELETE (SELECT) | DCL: GRANT, REVOKE | TCL: COMMIT, ROLLBACK, SAVEPOINT

Placement of SELECT varies by textbook; mention that it retrieves data.

Super key

Any attribute set that uniquely identifies every row

It may contain extra attributes. Every candidate key is a super key.

Candidate key

Minimal super key (no attribute can be removed)

A table can have several. One becomes the primary key.

Primary key

Chosen candidate key; unique and NOT NULL

Only one per table. It can be composite.

Foreign key

Attribute(s) in child table referring to primary key of parent table

It may repeat and may be null unless restricted. It enforces referential integrity.

Weak entity key

Owner's primary key + partial key (discriminator)

Needs an identifying relationship, usually shown with a double rectangle and double diamond.

ER to table mapping: 1:N

Put the primary key of the 'one' side as a foreign key on the 'many' side

No separate table is needed.

ER to table mapping: M:N

Create a new table with both primary keys as a composite key

Relationship attributes go into this table.

Diagram symbols

Rectangle = entity; ellipse = attribute; diamond = relationship; underline = key; double ellipse = multi-valued; dashed ellipse = derived

Draw them consistently.

Functional dependency

X → Y

If two tuples agree on X, they must agree on Y. X is the determinant.

Attribute closure

X⁺ = set of all attributes determined by X

X is a candidate key if X⁺ contains all attributes and no proper subset of X does the same.

Armstrong's axioms

Reflexivity: Y ⊆ X ⇒ X → Y. Augmentation: X → Y ⇒ XZ → YZ. Transitivity: X → Y and Y → Z ⇒ X → Z

Union, decomposition and pseudo-transitivity follow from these.

1NF

All attribute values are atomic; no repeating groups

Each cell holds a single value.

2NF

1NF + no partial dependency of a non-prime attribute on part of a candidate key

Matters only when a candidate key is composite.

3NF

For every FD X → A: X is a super key, or A is a prime attribute

Removes transitive dependency of non-prime attributes on a key.

BCNF

For every non-trivial FD X → A: X is a super key

Stricter than 3NF. Every BCNF relation is in 3NF.

Lossless join test (binary)

R1 ∩ R2 → R1 or R1 ∩ R2 → R2 (as FDs in R)

A decomposition of R into R1 and R2 is lossless if the common attributes determine one of the two.

Join size bounds

Cartesian product R × S has |R| × |S| tuples and degree(R) + degree(S)

Natural join has at most |R| × |S| tuples.

Basic query structure

SELECT columns FROM table WHERE condition GROUP BY column HAVING condition ORDER BY column

WHERE filters rows before grouping. HAVING filters groups after aggregation.

Insert, update, delete

INSERT INTO t (c1, c2) VALUES (v1, v2); UPDATE t SET c1 = v WHERE condition; DELETE FROM t WHERE condition;

Without WHERE, UPDATE and DELETE affect every row.

Inner join

SELECT … FROM A INNER JOIN B ON A.key = B.key

Returns only rows that match in both tables.

Outer joins

LEFT JOIN: all of A + matches in B; RIGHT JOIN: all of B + matches in A; FULL JOIN: all of both

Unmatched side shows NULL.

ACID

Atomicity, Consistency, Isolation, Durability

Learn each with a one-line meaning and a bank-transfer example.

Transaction control

COMMIT saves changes; ROLLBACK undoes uncommitted changes; SAVEPOINT marks a point to roll back to

These are TCL commands.

Two-phase locking (2PL)

Growing phase: acquire locks only; shrinking phase: release locks only

Guarantees serialisable schedules. It can still cause deadlock.

Serialisability

A concurrent schedule is acceptable if its result equals that of some serial execution

This is the correctness test for concurrency control.

Deadlock

Transactions wait for each other's locks in a cycle

Handled by prevention, detection (wait-for graph) or timeout, then rolling back a victim.

Data warehouse characteristics

Subject-oriented + Integrated + Time-variant + Non-volatile

Learn these four words. They are the classic definition and are asked often.

ETL

Extract → Transform → Load

Data is pulled from sources, cleaned and standardised, then loaded into the warehouse.

5 Vs of big data

Volume, Velocity, Variety, Veracity, Value

Give one line of meaning for each. Mention that some texts add more Vs.

OLTP vs OLAP

OLTP = many short transactions on current data; OLAP = complex queries on historical data

Use this as the opening line of any comparison answer.

Data mining techniques

Classification, Clustering, Association, Regression, Anomaly detection

Classification uses known classes; clustering does not.

CIA triad

Security = Confidentiality + Integrity + Availability

Link each control in your answer to the goal it protects.

Least privilege

User rights = minimum rights needed for the job

Pair it with role-based access control and periodic review of rights.

Backup rule of thumb (3-2-1)

3 copies of data, on 2 different media, 1 copy off-site

A common good practice, not a legal rule. A backup is reliable only if you test the restore.

SQL injection defence

Parameterised query + input validation + least-privilege database account

Never build a query by joining user input into the SQL text.

Section 43A, IT Act, 2000

Negligence in keeping reasonable security practices for sensitive personal data → compensation for wrongful loss or gain

Applies to a body corporate that possesses, deals with or handles such data in a computer resource it owns, controls or operates.

Reasonable security practices (SPDI Rules, 2011)

Documented information security programme and policy, matched to the data held; IS/ISO/IEC 27001 is one recognised standard

A body corporate may follow another standard if it is approved and notified by the Central Government.

DPDP Act, 2023: duties of Data Fiduciary

Reasonable security safeguards + breach intimation to the Board and affected Data Principals

Penalty for failing to take reasonable security safeguards can reach ₹250 crore as per the Act's Schedule.

Quick revision

• A DBMS stores, manages and controls access to data and reduces redundancy compared with file systems.
• Know the main data models: hierarchical, network, relational and object-based.
• Three-level architecture separates external, conceptual and internal views, giving data independence.
• DDL defines structure, DML manipulates data, and DCL and TCL control access and transactions.
• An ER diagram uses entities, attributes and relationships with cardinality such as one-to-many.
• A primary key uniquely identifies a row; a foreign key links tables.
• Normalization removes redundancy and anomalies; know 1NF, 2NF and 3NF and what each removes.
• Transactions follow ACID: atomicity, consistency, isolation, durability.
• Know SELECT, WHERE, GROUP BY, ORDER BY and JOIN syntax and what each does.
• A data warehouse stores integrated historical data for analysis; data mining finds patterns in it.
• Big data is described by volume, velocity and variety.
• Security controls include access control, encryption, backup and audit trails, and they support legal compliance.

Common mistakes

• Using data and information as the same thing. Fix: Always write that data is raw and information is processed data with meaning. Add a short example.
• Saying a database and a DBMS are the same. Fix: A database is the stored data. A DBMS is the software that manages it.
• Saying the client talks to the database in three-tier architecture. Fix: Write that the client talks only to the application tier, which talks to the database.
• Mixing up logical and physical data independence. Fix: Link logical to the conceptual schema (adding a column) and physical to the internal schema (adding an index).
• Treating a candidate key and a primary key as the same thing. Fix: Say that candidate keys are all the minimal options, and the primary key is the one chosen from them.
• Saying a foreign key must be unique and not null. Fix: A foreign key refers to a primary key elsewhere. In the child table it can repeat, and it can be null unless the design forbids it.
• Choosing the wrong candidate key, or only one when there are several. Fix: Compute the closure of the likely key. Also check whether other attributes on the right sides of FDs can act as keys.
• Applying 2NF to a relation whose key is a single attribute. Fix: If every candidate key is a single attribute, the relation is automatically in 2NF once it is in 1NF.
• Confusing DELETE, TRUNCATE and DROP. Fix: DELETE removes selected rows and is DML. TRUNCATE removes all rows but keeps the table structure. DROP removes the table itself. TRUNCATE and DROP are DDL.
• Using WHERE to filter on aggregate results. Fix: WHERE filters individual rows before grouping. Use HAVING for conditions such as COUNT(*) > 5 or SUM(amount) > 1,00,000.

Exam tips

• Open every answer with a crisp definition; examiners award marks for it.
• For comparison questions, use the same points on both sides and write at least four.
• Attach a business example to each data model, such as a bank, a company or a library.
• Link this topic to later ones: relational model, normalization, SQL and database security under the IT Act.
• Keep the answer structured with short points; this paper is written and descriptive.
• Case questions usually give a business scenario. Name the architecture or language and justify it from the facts.
• For differences, use a point-by-point format with at least four bases.
• Always attach one command or one example to each language and each schema level.