SQL (PostgreSQL, MySQL, SQLite) Module 13 – Designing database schemas

Normalisation: 1NF to BCNF in practice

Spot update, insert and delete anomalies, find functional dependencies with GROUP BY queries, and split a flat table into BCNF tables that join back.

Intermediate
35 minutes
Examples run with sql.js 1.14.2
By MySmartCoPilot

Free: One flat sheet and its three anomalies · Dependencies, checked with a query · The normal forms, applied to the sheet · Your turn. With a pass: 3 in-depth parts and 3 interview answers, about 23 minutes.

What you will learn

Identify update, insert and delete anomalies in a flat table
Check functional dependencies and candidate keys with queries on real data
Normalise a table to 3NF and BCNF, one dependency at a time
Prove with queries that the new tables join back to exactly the original rows
Decide when a design is normalised enough

Before you start

From requirements to an ER model

What you’ll learn · Before you start

What you will learn

Identify update, insert and delete anomalies in a flat table
Check functional dependencies and candidate keys with queries on real data
Normalise a table to 3NF and BCNF, one dependency at a time
Prove with queries that the new tables join back to exactly the original rows
Decide when a design is normalised enough

Before you start

From requirements to an ER model

On this page

Normalisation splits a table that writes the same fact on many rows into smaller tables that store each fact once, so that no update can leave two copies of a fact disagreeing. Its tool is the functional dependency: A -> B holds when any two rows with the same value of A also have the same value of B, the way a roll number determines a student’s name. The normal forms are checkpoints along the way. First normal form (1NF) keeps one value in each cell. Second normal form (2NF) moves out every column that depends on only part of a composite key. Third normal form (3NF) moves out every column that depends on another non-key column. Boyce-Codd normal form (BCNF) asks that the left side of every dependency be a candidate key. In practice you take the dependencies from the requirements, confirm them in the data with a GROUP BY query, and split the table along them.

Every query below can be run from the page itself: sql.js 1.14.2 brings SQLite 3.49.1 into the browser. Notes mark the few places where PostgreSQL 18 or MySQL 8.4 want other syntax.

One flat sheet and its three anomalies

The placement cell of the previous lesson exports its results as one spreadsheet, sheet: a row for each student, company and round, carrying the student’s branch and head of department (hod), the company’s CTC and the branches it accepts (eligible, a list in one cell).

The export, the facts it repeats, and an update that half happens SQL · flat_sheet.sql

-- The placement cell's export: one row per student, company and round. ctc is in lakh rupees a year; eligible lists
-- the branches a company accepts.
CREATE TABLE sheet (
  roll_no TEXT, student TEXT, branch TEXT, hod TEXT,
  company TEXT, ctc REAL, eligible TEXT, round_no INTEGER, cleared INTEGER
);
INSERT INTO sheet VALUES
  ('22CS014', 'Ananya',   'CSE', 'Dr. Rao',  'Neelgiri', 9.5, 'CSE,ECE',    1, 1),
  ('22CS014', 'Ananya',   'CSE', 'Dr. Rao',  'Neelgiri', 9.5, 'CSE,ECE',    2, 1),
  ('22CS031', 'Rohit',    'CSE', 'Dr. Rao',  'Neelgiri', 9.5, 'CSE,ECE',    1, 1),
  ('22CS031', 'Rohit',    'CSE', 'Dr. Rao',  'Neelgiri', 9.5, 'CSE,ECE',    2, 0),
  ('22EC007', 'Fatima',   'ECE', 'Dr. Sen',  'Neelgiri', 9.5, 'CSE,ECE',    1, 1),
  ('22EC007', 'Fatima',   'ECE', 'Dr. Sen',  'Neelgiri', 9.5, 'CSE,ECE',    2, 1),
  ('22EC007', 'Fatima',   'ECE', 'Dr. Sen',  'Sahyadri', 5.5, 'ECE,ME',     1, 1),
  ('22ME003', 'Gurpreet', 'ME',  'Dr. Paul', 'Sahyadri', 5.5, 'ECE,ME',     1, 1),
  ('22ME003', 'Gurpreet', 'ME',  'Dr. Paul', 'Sahyadri', 5.5, 'ECE,ME',     2, 1),
  ('22ME022', 'Divya',    'ME',  'Dr. Paul', 'Sahyadri', 5.5, 'ECE,ME',     1, 0),
  ('22CS014', 'Ananya',   'CSE', 'Dr. Rao',  'Tapti',    8.0, 'CSE,ECE,ME', 1, 1),
  ('22ME022', 'Divya',    'ME',  'Dr. Paul', 'Tapti',    8.0, 'CSE,ECE,ME', 1, 1);

SELECT * FROM sheet WHERE roll_no = '22CS014';

-- How many times the sheet writes down each fact that should be stored once.
SELECT 'hod of CSE' AS fact, count(*) AS times_written FROM sheet WHERE branch = 'CSE'
UNION ALL SELECT 'ctc of Neelgiri', count(*) FROM sheet WHERE company = 'Neelgiri'
UNION ALL SELECT 'name of 22CS014', count(*) FROM sheet WHERE roll_no = '22CS014';

-- Update anomaly: CSE gets a new head of department, and the update reaches only the rows someone filtered on.
UPDATE sheet SET hod = 'Dr. Iyer' WHERE branch = 'CSE' AND company = 'Neelgiri';
SELECT branch, hod, count(*) AS rows FROM sheet WHERE branch = 'CSE' GROUP BY branch, hod;

Output

┌─────────┬─────────┬────────┬─────────┬──────────┬─────┬────────────┬──────────┬─────────┐
│ roll_no │ student │ branch │   hod   │ company  │ ctc │  eligible  │ round_no │ cleared │
├─────────┼─────────┼────────┼─────────┼──────────┼─────┼────────────┼──────────┼─────────┤
│ 22CS014 │ Ananya  │ CSE    │ Dr. Rao │ Neelgiri │ 9.5 │ CSE,ECE    │ 1        │ 1       │
│ 22CS014 │ Ananya  │ CSE    │ Dr. Rao │ Neelgiri │ 9.5 │ CSE,ECE    │ 2        │ 1       │
│ 22CS014 │ Ananya  │ CSE    │ Dr. Rao │ Tapti    │ 8.0 │ CSE,ECE,ME │ 1        │ 1       │
└─────────┴─────────┴────────┴─────────┴──────────┴─────┴────────────┴──────────┴─────────┘
┌─────────────────┬───────────────┐
│      fact       │ times_written │
├─────────────────┼───────────────┤
│ hod of CSE      │ 5             │
│ ctc of Neelgiri │ 6             │
│ name of 22CS014 │ 3             │
└─────────────────┴───────────────┘
┌────────┬──────────┬──────┐
│ branch │   hod    │ rows │
├────────┼──────────┼──────┤
│ CSE    │ Dr. Iyer │ 4    │
│ CSE    │ Dr. Rao  │ 1    │
└────────┴──────────┴──────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < flat_sheet.sql

The sheet writes the head of the CSE department on 5 of its 12 rows and Neelgiri’s CTC on 6. Copies like these are where anomalies come from:

Update anomaly. The new head of CSE was written onto the four Neelgiri rows and missed the Tapti row, so the sheet now names two heads for one department.
Insert anomaly. A new branch and its head cannot be recorded until one of its students takes a round, because every row is a result.
Delete anomaly. Deleting Tapti’s two rows to correct a mistake also deletes everything the sheet knows about Tapti: its CTC and the branches it accepts.

Dependencies, checked with a query

A dependency A -> B breaks wherever one value of A appears with two values of B, so GROUP BY a HAVING count(DISTINCT b) > 1 lists the values of A that break it. The same counting finds a candidate key: a set of columns whose distinct combinations are as many as the rows, and which stops being unique when any column is dropped.

A candidate key and three dependencies, tested on the export SQL · fd_check.sql

-- The export as it arrived, after the half-done update of the previous example.
CREATE TABLE sheet (
  roll_no TEXT, student TEXT, branch TEXT, hod TEXT,
  company TEXT, ctc REAL, eligible TEXT, round_no INTEGER, cleared INTEGER
);
INSERT INTO sheet VALUES
  ('22CS014', 'Ananya',   'CSE', 'Dr. Iyer', 'Neelgiri', 9.5, 'CSE,ECE',    1, 1),
  ('22CS014', 'Ananya',   'CSE', 'Dr. Iyer', 'Neelgiri', 9.5, 'CSE,ECE',    2, 1),
  ('22CS031', 'Rohit',    'CSE', 'Dr. Iyer', 'Neelgiri', 9.5, 'CSE,ECE',    1, 1),
  ('22CS031', 'Rohit',    'CSE', 'Dr. Iyer', 'Neelgiri', 9.5, 'CSE,ECE',    2, 0),
  ('22EC007', 'Fatima',   'ECE', 'Dr. Sen',  'Neelgiri', 9.5, 'CSE,ECE',    1, 1),
  ('22EC007', 'Fatima',   'ECE', 'Dr. Sen',  'Neelgiri', 9.5, 'CSE,ECE',    2, 1),
  ('22EC007', 'Fatima',   'ECE', 'Dr. Sen',  'Sahyadri', 5.5, 'ECE,ME',     1, 1),
  ('22ME003', 'Gurpreet', 'ME',  'Dr. Paul', 'Sahyadri', 5.5, 'ECE,ME',     1, 1),
  ('22ME003', 'Gurpreet', 'ME',  'Dr. Paul', 'Sahyadri', 5.5, 'ECE,ME',     2, 1),
  ('22ME022', 'Divya',    'ME',  'Dr. Paul', 'Sahyadri', 5.5, 'ECE,ME',     1, 0),
  ('22CS014', 'Ananya',   'CSE', 'Dr. Rao',  'Tapti',    8.0, 'CSE,ECE,ME', 1, 1),
  ('22ME022', 'Divya',    'ME',  'Dr. Paul', 'Tapti',    8.0, 'CSE,ECE,ME', 1, 1);

-- A candidate key: no two rows share (roll_no, company, round_no), and dropping any one of the three columns leaves
-- fewer distinct values than rows.
SELECT count(*) AS rows,
       (SELECT count(*) FROM (SELECT DISTINCT roll_no, company, round_no FROM sheet)) AS all_three,
       (SELECT count(*) FROM (SELECT DISTINCT roll_no, company FROM sheet)) AS no_round,
       (SELECT count(*) FROM (SELECT DISTINCT roll_no, round_no FROM sheet)) AS no_company,
       (SELECT count(*) FROM (SELECT DISTINCT company, round_no FROM sheet)) AS no_roll_no
FROM sheet;

-- One check per dependency: the groups whose rows disagree on the right-hand side.
SELECT 'roll_no -> student, branch' AS dependency, count(*) AS groups_that_break_it
  FROM (SELECT roll_no FROM sheet GROUP BY roll_no HAVING count(DISTINCT student) > 1 OR count(DISTINCT branch) > 1)
UNION ALL
SELECT 'branch -> hod', count(*)
  FROM (SELECT branch FROM sheet GROUP BY branch HAVING count(DISTINCT hod) > 1)
UNION ALL
SELECT 'company -> ctc, eligible', count(*)
  FROM (SELECT company FROM sheet GROUP BY company HAVING count(DISTINCT ctc) > 1 OR count(DISTINCT eligible) > 1);

-- The rows behind the broken dependency.
SELECT branch, hod, count(*) AS rows, group_concat(DISTINCT company) AS companies
FROM sheet
WHERE branch IN (SELECT branch FROM sheet GROUP BY branch HAVING count(DISTINCT hod) > 1)
GROUP BY branch, hod
ORDER BY rows DESC;

Output

┌──────┬───────────┬──────────┬────────────┬────────────┐
│ rows │ all_three │ no_round │ no_company │ no_roll_no │
├──────┼───────────┼──────────┼────────────┼────────────┤
│ 12   │ 12        │ 8        │ 9          │ 5          │
└──────┴───────────┴──────────┴────────────┴────────────┘
┌────────────────────────────┬──────────────────────┐
│         dependency         │ groups_that_break_it │
├────────────────────────────┼──────────────────────┤
│ roll_no -> student, branch │ 0                    │
│ branch -> hod              │ 1                    │
│ company -> ctc, eligible   │ 0                    │
└────────────────────────────┴──────────────────────┘
┌────────┬──────────┬──────┬───────────┐
│ branch │   hod    │ rows │ companies │
├────────┼──────────┼──────┼───────────┤
│ CSE    │ Dr. Iyer │ 4    │ Neelgiri  │
│ CSE    │ Dr. Rao  │ 1    │ Tapti     │
└────────┴──────────┴──────┴───────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < fd_check.sql

(roll_no, company, round_no) is a candidate key: 12 distinct combinations for 12 rows, and fewer as soon as any of the three columns is left out. Of the three dependencies, only branch -> hod fails, in one group, and the last query shows the rows behind it.

A query can only refute a dependency. When it returns nothing, the rows you have agree today, which may be chance; whether branch -> hod is a rule is for the business to say. Once it is, a primary key on branch in a table of branches enforces it on every future row.

The normal forms, applied to the sheet

What each normal form asks, and what it changes in the sheet
Criterion	1NF	2NF	3NF	BCNF
Rule	One value in each cell	No non-key column depends on part of a key	No non-key column depends on another non-key column	The left side of every dependency is a candidate key
Broken in the sheet by	eligible, a list of branches	student and branch (on roll_no), ctc (on company)	hod, which depends on branch	Nothing more once 3NF holds
The step	company_branches, one row per branch	students, companies and results	branches (branch, hod)	None needed

Normalising the placement sheet, one normal form per step

Text description of the diagram

The diagram is a column of five boxes joined by arrows, read from top to bottom.

The export, sheet, with the columns roll_no, student, branch, hod, company, ctc, eligible (a list of branches in one cell), round_no and cleared. Its key is (roll_no, company, round_no).
An arrow labelled "split the lists" leads to 1NF, one value in each cell: the table company_branches (company, branch) replaces the list in eligible.
An arrow labelled "move roll_no -> student, branch and company -> ctc" leads to 2NF, no fact about part of the key: students (roll_no, student, branch, hod), companies (company, ctc) and results (roll_no, company, round_no, cleared).
An arrow labelled "move branch -> hod" leads to 3NF and BCNF, no fact about a non-key: branches (branch, hod), while students keeps roll_no, student and branch.
The last box, with a double border, lists the result: five tables in which each fact is stored once, namely results, students, branches, companies and company_branches.

BCNF is stricter than 3NF only when columns that are not a key determine part of a candidate key. The sheet has no such dependency, so its 3NF tables are in BCNF as well. Where the two differ, a BCNF split can lose a dependency that 3NF keeps; the DBMS track works through that trade-off and the proofs behind lossless splits.

Normalised enough means this: tables that are written to are in BCNF, or in 3NF where BCNF would lose a dependency you want the database to enforce. A value copied on purpose, such as the price an order was placed at, is a different fact rather than a copy. Copies kept for speed are the subject of the next lesson.

GATE CS

The GATE 2027 CS syllabus lists integrity constraints and normal forms in its Databases section, so the definitions above, and finding the highest normal form of a table from its dependencies, are exam material.

Your turn

Exercise · Easy · SQL

Find the companies whose rows disagree on the CTC

A placement cell exported its drive results into one table, drive_sheet, with the columns roll_no, company, ctc and round_no: one row per student, company and round, with the company's CTC copied onto every row.

The cell believes that company -> ctc holds: every row of a company carries the same CTC. Write a query that checks the belief. It returns one row for each company whose rows do not all agree, with two columns:

company: the company's name;
ctc_values: how many different CTC values its rows hold.

Companies whose rows agree are not listed. The order of the rows does not matter. In these tests no CTC is missing.

Starter code · ctc_check.sql

-- One row for each company whose rows disagree on the CTC, with the number of different values.
SELECT company, count(*) AS ctc_values
FROM drive_sheet
GROUP BY company;

The sample tests · tests.yaml

# Sample tests: each one fills drive_sheet, runs your query and compares the rows it returns (in any order).
tests:
  - name: finds the company with two CTC values
    setup: |
      CREATE TABLE drive_sheet (roll_no TEXT, company TEXT, ctc REAL, round_no INTEGER);
      INSERT INTO drive_sheet VALUES
        ('22CS014', 'Neelgiri', 9.5, 1), ('22CS014', 'Neelgiri', 9.5, 2), ('22CS031', 'Neelgiri', 9.5, 1),
        ('22EC007', 'Neelgiri', 9.0, 1), ('22ME003', 'Sahyadri', 5.5, 1), ('22ME022', 'Sahyadri', 5.5, 1);
    expected:
      columns: [company, ctc_values]
      rows:
        - ['Neelgiri', 2]
  - name: lists nothing when every company agrees
    setup: |
      CREATE TABLE drive_sheet (roll_no TEXT, company TEXT, ctc REAL, round_no INTEGER);
      INSERT INTO drive_sheet VALUES
        ('22CS014', 'Tapti', 8.0, 1), ('22ME022', 'Tapti', 8.0, 1), ('22EC007', 'Sahyadri', 5.5, 1),
        ('22EC007', 'Sahyadri', 5.5, 2);
    expected:
      columns: [company, ctc_values]
      rows: []
  - name: counts three values for one company and two for another
    setup: |
      CREATE TABLE drive_sheet (roll_no TEXT, company TEXT, ctc REAL, round_no INTEGER);
      INSERT INTO drive_sheet VALUES
        ('22CS014', 'Tapti', 8.0, 1), ('22CS031', 'Tapti', 8.5, 1), ('22EC007', 'Tapti', 7.5, 1),
        ('22EC007', 'Tapti', 7.5, 2), ('22ME003', 'Kaveri', 6.0, 1), ('22ME022', 'Kaveri', 6.5, 1),
        ('22ME030', 'Narmada', 4.5, 1);
    expected:
      columns: [company, ctc_values]
      rows:
        - ['Tapti', 3]
        - ['Kaveri', 2]

A hint

Group the rows by company, so that each group holds the rows the dependency talks about. count(DISTINCT ctc) counts the different values in a group; a dependency holds for the group when that count is 1. HAVING filters groups the way WHERE filters rows.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Run SQL on CSV & Excel Files Load your own spreadsheet export and run this lesson's GROUP BY dependency checks on it, in your browser.

Splitting the placement sheet into BCNF tables, with every step run Pro: opens with a Pro, Premium or Ultimate pass

Each step of the split is a few lines of SQL, and each one can be checked. This part runs the split from the first normal form to BCNF and proves with two queries that the five tables hold exactly the rows of the sheet.

In this part:

First normal form: a list in a cell becomes rows
Second and third normal form: one table per determinant
Proof that nothing was lost or invented

3 runnable examples (SQL) · about 3 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

When the data breaks a dependency: NULLs, spellings and repairs Pro: opens with a Pro, Premium or Ultimate pass

Real exports seldom satisfy their dependencies, and the usual check can miss a violation or report one that is not there. This part reproduces both errors and repairs the data so that the split can load.

In this part:

Two ways the usual check misleads, reproduced
How far to clean, with one rule per kind of column
Loading one value per key, and keeping the conflicts

2 runnable examples (SQL) · about 3 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Normalising a table that programs still use, without stopping them Pro: opens with a Pro, Premium or Ultimate pass

A table in use cannot be dropped and rebuilt in one go. This part keeps its old name working as a view, keeps old inserts working through a trigger, and gives the order of the steps with the check that guards each one.

In this part:

A view with the old name and columns
The order of the steps, and the check after each

1 runnable example (SQL) · about 2 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Interview questions

Fresher to mid · Warm-up

What is a functional dependency, and how do you check one in a table's data?

A functional dependency A -> B says that the value of A determines the value of B: any two rows that agree on A also agree on B. A roll number determines a student's name, and a branch determines its head of department. Dependencies come from the rules of the business, and they decide the keys: the left side of each one should be a key of some table. In data you can only look for rows that break a dependency: SELECT a FROM t GROUP BY a HAVING count(DISTINCT b) > 1 lists every value of A that appears with two different values of B. An empty result means that the current rows agree, not that the rule holds, and the check skips NULLs, because count(DISTINCT b) does not count them.
Mid to senior

You are about to split a 2-million-row export on the dependency company -> ctc, and a check finds 1,214 companies whose rows hold two or more CTC values. What do you do before splitting, and how do you decide which value is right?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Fresher to mid

Normalise exam_results(roll_no, student_name, branch, branch_hod, subject_code, subject_name, credits, marks) to BCNF, naming every dependency and key. Each student takes each subject once, and marks belong to a student and a subject.

Model answer, Pro. First step and follow-ups
Senior to staff

A 40-column orders table repeats the customer's name, email and city on every order, and dozens of reports read it directly. Plan the move to a normalised design without breaking the reports or losing data. What do you check at each step?

Model answer, Pro. First step and follow-ups

Key takeaways

A functional dependency A -> B says one value of A never appears with two values of B; GROUP BY a HAVING count(DISTINCT b) > 1 finds the rows that break it, but no query proves it.
1NF keeps one value per cell, 2NF removes facts about part of a key, 3NF removes facts about non-key columns, and BCNF makes every determinant a key.
Each dependency becomes a table whose primary key is its left side, and that key enforces the dependency from then on.

References

PostgreSQL 18 Documentation: Constraints (The PostgreSQL Global Development Group)
PostgreSQL 18 Documentation: String Functions and Operators (string_to_table) (The PostgreSQL Global Development Group)
PostgreSQL 18 Documentation: CREATE TRIGGER (INSTEAD OF triggers on views) (The PostgreSQL Global Development Group)
SQLite: The SELECT statement (GROUP BY, HAVING, compound SELECT) (SQLite)
SQLite: Built-in Aggregate Functions (SQLite)
SQLite: The WITH Clause (recursive common table expressions) (SQLite)
SQLite: CREATE TRIGGER (INSTEAD OF triggers) (SQLite)
MySQL 8.4 Reference Manual: Aggregate Function Descriptions (Oracle Corporation)
MySQL 8.4 Reference Manual: Server SQL Modes (PIPES_AS_CONCAT) (Oracle Corporation)
MySQL 8.4 Reference Manual: CREATE TRIGGER Statement (Oracle Corporation)
GATE 2027 Computer Science and Information Technology (CS) syllabus (Indian Institute of Technology Madras (GATE 2027))

Report a problem with this lesson

In this module: Designing database schemas

From requirements to an ER model Free lesson, Pro answers: 30 minutes
Normalisation: 1NF to BCNF in practice Free core, Pro depth: 12 of 35 minutes free
Denormalisation and derived data Free core, Pro depth: 10 of 30 minutes free
Many-to-many, one-to-one and polymorphic links Free core, Pro depth: 10 of 30 minutes free
Storing trees: adjacency, paths and closure tables Free core, Pro depth: 10 of 30 minutes free

Your country

Normalisation: 1NF to BCNF in practice

What you will learn

Before you start

What you will learn

Before you start

One flat sheet and its three anomalies

Dependencies, checked with a query

The normal forms, applied to the sheet

Check yourself

Your turn

Find the companies whose rows disagree on the CTC

Results of the sample tests

Splitting the placement sheet into BCNF tables, with every step run Pro: opens with a Pro, Premium or Ultimate pass

When the data breaks a dependency: NULLs, spellings and repairs Pro: opens with a Pro, Premium or Ultimate pass

Normalising a table that programs still use, without stopping them Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

What is a functional dependency, and how do you check one in a table's data?

You are about to split a 2-million-row export on the dependency company -> ctc, and a check finds 1,214 companies whose rows hold two or more CTC values. What do you do before splitting, and how do you decide which value is right?

Normalise exam_results(roll_no, student_name, branch, branch_hod, subject_code, subject_name, credits, marks) to BCNF, naming every dependency and key. Each student takes each subject once, and marks belong to a student and a subject.

A 40-column orders table repeats the customer's name, email and city on every order, and dozens of reports read it directly. Plan the move to a normalised design without breaking the reports or losing data. What do you check at each step?

Key takeaways

References

Related tools