UP Police Computer Operator DBMS and SQL: Learn Tables, Keys and Queries with a Police-Records Database

Terms such as table, record, primary key, foreign key, normalisation and query are easy to memorise separately. A question that combines them is harder unless you can follow the data from design to result. One small police-records database makes that possible: three stations, four officers, four complaints and the assignments linking them. Those rows never change, so every key, dependency and query result stays checkable against the same data. The names and values are invented for teaching, not real police records.

DBMS and relational tables: build the police-records example

A database is an organised collection of related data. A DBMS is software that defines, stores, retrieves and controls it. An RDBMS is a DBMS organised mainly around related tables, but not every DBMS is relational.

A schema defines structure. A table (relation) contains rows (tuples) and columns (attributes). A domain is an attribute's permitted value set. Complaint(complaint_id, complainant_name, station_id, category, status) has degree 5 for five columns and cardinality 4 for four rows. Table cardinality is not relationship cardinality.

Use this data:

Keys and relationships in DBMS: identify every role precisely

A superkey uniquely identifies a row. A candidate key is a minimal superkey; the chosen candidate is primary and an unchosen candidate is alternate. In Complaint, {complaint_id} is primary. {complaint_id, complainant_name} is a superkey, not a candidate key, because the name is unnecessary and not assumed unique. No alternate key exists here either: station_name and officer_name happen to be distinct in this data, but nothing declares them unique, so neither is a candidate key waiting to be chosen.

Complaint.station_id and Officer.station_id are foreign keys referencing Station.station_id. Foreign keys can repeat, so 101 validly appears in complaints 201 and 203. Whether one may be NULL depends on its column constraint.

Assignment has composite primary key (complaint_id, officer_id). Complaint 201 pairs with officers 501 and 503; officer 501 pairs with complaints 201 and 203. Neither component is unique alone; each pair is unique.

Station has one-to-many relationships with Complaint and Officer. Complaint and Officer have a many-to-many relationship resolved through Assignment.

Normalisation basics: take one repeated case sheet to 3NF

Start with CaseSheet(201, 'Nikhil Sharma', 101, 'Hazratganj', '501,503', 'Aditi Singh,Neha Verma', 'OPEN'). The officer lists are repeating, non-atomic groups. First normal form requires one value per cell, so create atomic CaseOfficer rows (201, 501, ...) and (201, 503, ...), keyed by (complaint_id, officer_id).

In CaseOfficer(complaint_id, officer_id, complainant_name, station_id, station_name, officer_name, status), these partial dependencies remain:

• complaint_id -> complainant_name, station_id, status

• officer_id -> officer_name

These attributes depend on only part of the composite key. Splitting into Complaint, Officer and Assignment reaches 2NF. A transitive dependency remains: complaint_id -> station_id, while station_id -> station_name, district. Move station facts to Station and retain station_id in Complaint for 3NF. A Hazratganj district-label change then needs one Station update, not edits to every complaint. BCNF tightens the rule one step further, demanding that every determinant be a superkey, a decomposition worked line by line in Normalization in DBMS: 1NF to BCNF.

SQL table creation: turn the design into constraints

CREATE TABLE Station (
  station_id INTEGER PRIMARY KEY,
  station_name VARCHAR(50), district VARCHAR(50)
);
CREATE TABLE Officer (
  officer_id INTEGER PRIMARY KEY, officer_name VARCHAR(50),
  station_id INTEGER, FOREIGN KEY (station_id) REFERENCES Station(station_id)
);
CREATE TABLE Complaint (
  complaint_id INTEGER PRIMARY KEY, complainant_name VARCHAR(50),
  station_id INTEGER, category VARCHAR(30) NOT NULL, status VARCHAR(10) NOT NULL,
  FOREIGN KEY (station_id) REFERENCES Station(station_id)
);
CREATE TABLE Assignment (
  complaint_id INTEGER, officer_id INTEGER, assigned_on DATE,
  PRIMARY KEY (complaint_id, officer_id),
  FOREIGN KEY (complaint_id) REFERENCES Complaint(complaint_id),
  FOREIGN KEY (officer_id) REFERENCES Officer(officer_id)
);

CREATE TABLE is DDL because it defines structure. INSERT, UPDATE and DELETE are DML because they change rows; SELECT retrieves data, and is classed as DQL where a syllabus keeps a separate retrieval category and as DML where it does not. With foreign-key enforcement, a complaint using station_id = 999 is rejected because no Station has key 999. Another using 101 is valid because foreign keys may repeat.

SQL queries on police records: work every result

Filter and sort:

SELECT complaint_id, complainant_name FROM Complaint
WHERE status = 'OPEN' ORDER BY complaint_id;

WHERE keeps rows 201, 203 and 204; ORDER BY fixes their order. Output: (201, 'Nikhil Sharma'), (203, 'Meera Joshi'), (204, 'Arjun Patel').

Now pair each complaint with its Station row, the operation worked through in full in SQL Queries and Joins in DBMS: A Clear Guide:

SELECT c.complaint_id, c.complainant_name, s.station_name
FROM Complaint c JOIN Station s ON c.station_id = s.station_id
WHERE c.category = 'CYBER_FRAUD' AND c.status = 'OPEN'
ORDER BY c.complaint_id;

From four complaints, the filter retains 201 and 203; both match station 101. Output: (201, 'Nikhil Sharma', 'Hazratganj') and (203, 'Meera Joshi', 'Hazratganj').

Traverse Assignment:

SELECT o.officer_id, o.officer_name
FROM Assignment a JOIN Officer o ON a.officer_id = o.officer_id
WHERE a.complaint_id = 201 ORDER BY o.officer_id;

Result: (501, 'Aditi Singh') and (503, 'Neha Verma'). Aggregate:

SELECT station_id, COUNT(*) AS open_cases FROM Complaint
WHERE status = 'OPEN' GROUP BY station_id ORDER BY station_id;

Output: (101, 2) and (103, 1). Station 102 is absent because its only complaint is closed.

DBMS and SQL traps: diagnose the wrong answer

• Missing join condition: four Complaint rows times three Station rows gives 4 x 3 = 12 Cartesian rows. ON c.station_id = s.station_id gives four matched rows here.

• Confusing key roles: Complaint.complaint_id identifies a complaint. Complaint.station_id refers to Station and is expected to repeat.

• Using half a composite key: 201 and 501 repeat; only (complaint_id, officer_id) identifies an Assignment row.

• Splitting without dependencies: write dependencies first, then decompose and confirm that primary and foreign keys reconstruct the relationships.

• Misplacing aggregate conditions: WHERE filters rows before grouping; HAVING filters completed groups. status = 'OPEN' belongs in WHERE.

UP Police Computer Operator DBMS and SQL practice

Practise four forms: identify a key, choose a correct 1NF or 3NF decomposition, predict a join or GROUP BY result, and classify an operation as DDL or DML. Rapid checks are:

• Assignment key: (complaint_id, officer_id).

• Condition-free Complaint and Station product: 4 x 3 = 12 rows.

• Open-case groups: 101 has 2; 103 has 1.

• Dependency moving station facts out of Complaint: station_id -> station_name, district.

Use the Uttar Pradesh Police Recruitment and Promotion Board for the current notification and syllabus. Attribute current marks, dates, duration, question counts, recruitment stages and syllabus wording to it. A course syllabus or a practice set reflects teaching coverage, never an official DBMS weightage. After concept study, the UP Police Computer Operator Test Series provides unit tests, mock tests and exam-pattern practice.

UP Police Computer Operator DBMS and SQL: the short version

A table stores rows under a schema. A primary key identifies, a foreign key connects, a bridge table resolves many-to-many, and normalisation removes dependency-driven repetition. SELECT, JOIN, WHERE, GROUP BY and ORDER BY turn stored rows into answers.

Redraw all four tables, explain why Assignment needs both key columns, and recompute the outputs. Check for three open complaints, two open cyber-fraud complaints at Hazratganj, two officers on complaint 201, and counts 101 -> 2, 103 -> 1. For the full exam path, use the UP Police Computer Operator course; the UP Police Computer Operator Exam Prep category shows course and practice together.