Computer Science and Electronics for Digital Assistant · Chapter 2

Database Management Systems

What to remember

A DBMS stores data once, keeps it consistent, and lets many users share it safely. It reduces redundancy and gives security, integrity and backup.
The relational model keeps data in tables (relations) of rows (tuples) and columns (attributes). Keys link the tables.
SQL is the standard language. DDL defines structure, DML changes data, DCL gives permissions, TCL controls transactions. Normalization removes redundancy.

1. Basic ideas

Data is raw facts. A database is an organised collection of related data. A DBMS (Database Management System) is the software that creates, stores, retrieves and protects the database. Examples are MySQL, Oracle, PostgreSQL and MS Access.

A file system stores data in separate files. It has problems that a DBMS solves.

Point	File system	DBMS
Redundancy	High, same data repeated	Low, controlled
Consistency	Hard to keep	Easy to keep
Sharing	Poor	Many users together
Security	Weak	Strong, with permissions
Backup and recovery	Manual	Built in
Query	Needs a program	Query language such as SQL

Advantages of a DBMS: less redundancy, data integrity, data independence, concurrent access, security, backup and recovery.

Users of a database: the Database Administrator (DBA) controls the whole system and security; application programmers write programs; end users use the data. The data dictionary (catalog) stores data about data, called metadata.

2. Three-level architecture and data independence

The ANSI-SPARC architecture has three levels:

1. Internal (physical) level: how data is stored on disk.
2. Conceptual (logical) level: the whole database structure for the community of users, tables and relationships.
3. External (view) level: what each user or group sees.

Data independence means a change at one level does not force a change at the next higher level. Physical data independence is a change in storage without changing the conceptual schema. Logical data independence is a change in the conceptual schema without changing the views or programs. Logical independence is harder to achieve.

A schema is the design (structure) of the database. An instance is the data present at a given moment.

3. Data models and the ER model

A data model describes how data is organised. The main types are hierarchical (tree), network (graph), relational (tables) and object-oriented. The relational model, proposed by E. F. Codd in 1970, is the most widely used.

The Entity-Relationship (ER) model is a design tool, introduced by Peter Chen.

Entity: a real-world thing, such as Student. Entity set: all entities of one type.
Attribute: a property of an entity. Types are simple, composite, single-valued, multi-valued and derived (for example, age derived from date of birth).
Relationship: an association between entities. Mapping cardinality can be one-to-one, one-to-many, many-to-one or many-to-many.
Weak entity: it has no key of its own and depends on a strong (owner) entity.

ER diagram symbols: rectangle for entity, ellipse for attribute, diamond for relationship, double rectangle for weak entity, double ellipse for multi-valued attribute, dashed ellipse for derived attribute.

4. Relational model and keys

Relation: a table. Tuple: a row (record). Attribute: a column (field). Domain: the set of allowed values for an attribute.
Degree: number of columns. Cardinality: number of rows.
Rules: each cell holds one value, row order and column order do not matter, and duplicate rows are not allowed.

Key	Meaning
Super key	Any set of attributes that identifies a row uniquely
Candidate key	A minimal super key (no extra attribute)
Primary key	The candidate key chosen to identify rows; cannot be NULL or duplicate
Alternate key	Candidate keys not chosen as primary
Composite key	A key made of two or more attributes
Foreign key	An attribute that refers to the primary key of another table

Integrity rules: *Entity integrity* says a primary key cannot be NULL. *Referential integrity* says a foreign key value must match an existing primary key value in the parent table, or be NULL. A table can have only one primary key but many candidate keys and many foreign keys.

5. Relational algebra

Relational algebra is a procedural query language. Main operations: Select (σ) picks rows; Project (π) picks columns; Union (∪), Intersection (∩), Difference (−); Cartesian product (×); Join (⋈); Rename (ρ). Union, intersection and difference need union-compatible relations (same number and type of columns). Relational calculus is the non-procedural form.

6. SQL (Structured Query Language)

SQL is the standard language of relational databases. It is not case-sensitive for keywords.

Group	Full form	Commands
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	Data Manipulation Language	SELECT, INSERT, UPDATE, DELETE
DCL	Data Control Language	GRANT, REVOKE
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT

Some books place SELECT in a separate group called DQL.

Common syntax:

`CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(30), marks INT);`
`INSERT INTO student VALUES (1, 'Ravi', 80);`
`SELECT name FROM student WHERE marks > 50 ORDER BY name;`
`UPDATE student SET marks = 90 WHERE roll = 1;`
`DELETE FROM student WHERE roll = 1;`

DELETE vs TRUNCATE vs DROP: DELETE removes chosen rows and can use WHERE. TRUNCATE removes all rows but keeps the table structure. DROP removes the table itself.

Clauses: WHERE filters rows; GROUP BY groups rows; HAVING filters groups; ORDER BY sorts (ASC is the default). Aggregate functions: COUNT, SUM, AVG, MIN, MAX. LIKE uses `%` for any number of characters and `_` for one character. Use `IS NULL` to test for NULL, never `= NULL`. DISTINCT removes duplicates.

Joins: INNER JOIN returns matching rows only; LEFT, RIGHT and FULL OUTER JOIN also keep unmatched rows of one or both tables; CROSS JOIN gives the Cartesian product.

Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

7. Normalization

Normalization is the process of organising tables to remove redundancy and avoid insertion, deletion and update anomalies. Functional dependency X → Y means each value of X determines exactly one value of Y.

Normal form	Condition
1NF	All values atomic, no repeating groups
2NF	In 1NF and no partial dependency on part of a composite key
3NF	In 2NF and no transitive dependency
BCNF	For every dependency X → Y, X is a super key

Worked example: Table (RollNo, Subject, Marks, StudentName) with key (RollNo, Subject). StudentName depends only on RollNo, which is a partial dependency. Splitting into Student(RollNo, StudentName) and Marks(RollNo, Subject, Marks) gives 2NF. Denormalization adds redundancy on purpose for speed.

8. Transactions

A transaction is a logical unit of work. The ACID properties are:

Atomicity: all or nothing.
Consistency: the database moves from one valid state to another.
Isolation: transactions do not disturb each other.
Durability: committed changes survive failure.

Example: moving money from account A to B must debit and credit together, or neither. COMMIT saves changes; ROLLBACK undoes uncommitted changes. A deadlock happens when two transactions wait for each other forever. Locks and serializability give concurrency control. An index speeds up searching, like the index of a book. A view is a virtual table made from a query. A stored procedure is saved SQL code that can be run again.

Worked examples

1. Keys: In Student(RollNo, Aadhaar, Name, Phone), both RollNo and Aadhaar identify a row uniquely and neither has an extra attribute, so both are candidate keys. If RollNo is chosen as primary key, Aadhaar becomes an alternate key.
2. Degree and cardinality: A table with 5 columns and 40 rows has degree 5 and cardinality 40.
3. Aggregate query: `SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 2;` lists each department with more than two employees.
4. Anomalies: If a table repeats a customer's address in every order row, changing the address needs many updates (update anomaly); deleting the only order may lose the customer (deletion anomaly); a new customer cannot be added without an order (insertion anomaly). Splitting into Customer and Order tables removes these.
5. Cartesian product: if relation R has 4 rows and S has 3 rows, R × S has 12 rows and the degree is the sum of both degrees.

Exam traps

Primary key vs candidate key: the primary key is only the chosen one of the candidate keys.
Degree is the number of columns; cardinality is the number of rows.
DELETE vs TRUNCATE vs DROP: rows chosen, all rows, whole table.
WHERE filters rows before grouping; HAVING filters groups after grouping.
DDL vs DML: CREATE, ALTER, DROP are DDL; SELECT, INSERT, UPDATE, DELETE are DML.
Foreign key can be NULL and duplicate; primary key cannot be NULL.
Physical vs logical data independence: logical is harder.
The select operation (σ) picks rows; project (π) picks columns.

One-liners

1. A DBMS is software that manages databases.
2. Metadata means data about data.
3. The relational model was proposed by E. F. Codd.
4. The ER model was proposed by Peter Chen.
5. A row of a table is a tuple.
6. A primary key cannot be NULL.
7. A foreign key refers to a primary key of another table.
8. GRANT and REVOKE are DCL commands.
9. COMMIT and ROLLBACK are TCL commands.
10. 1NF means atomic values.
11. ACID stands for Atomicity, Consistency, Isolation, Durability.
12. A view is a virtual table.

Practice questions

Who proposed the relational model of data?
1. Charles Babbage
2. Peter Chen
3. E. F. Codd
4. Dennis Ritchie
Answer
C. E. F. Codd
The relational model was proposed by E. F. Codd in 1970.
In a relational table, a row is also called a
1. schema
2. tuple
3. attribute
4. domain
Answer
B. tuple
A row is a tuple; a column is an attribute.
The number of columns in a relation is called its
1. cardinality
2. degree
3. instance
4. domain
Answer
B. degree
Degree = number of columns; cardinality = number of rows.
Data about data is known as
1. big data
2. microdata
3. metadata
4. raw data
Answer
C. metadata
The data dictionary stores metadata.
Which key cannot hold a NULL value?
1. Foreign key
2. Alternate key only
3. Super key only
4. Primary key
Answer
D. Primary key
Entity integrity forbids NULL in a primary key.
Which SQL command removes the table structure itself along with its data?
1. DROP
2. DELETE
3. UPDATE
4. TRUNCATE
Answer
A. DROP
DROP deletes the whole table; DELETE and TRUNCATE remove rows only.
GRANT and REVOKE belong to
1. DML
2. DCL
3. DDL
4. TCL
Answer
B. DCL
Data Control Language gives or takes back permissions.
Which command saves the changes of a transaction permanently?
1. SAVEPOINT
2. ROLLBACK
3. COMMIT
4. REVOKE
Answer
C. COMMIT
COMMIT makes changes permanent; ROLLBACK undoes them.
The symbol used for a relationship in an ER diagram is a
1. triangle
2. diamond
3. ellipse
4. rectangle
Answer
B. diamond
Rectangle = entity, ellipse = attribute, diamond = relationship.
The ACID property that means 'all or nothing' is
1. Consistency
2. Isolation
3. Atomicity
4. Durability
Answer
C. Atomicity
Atomicity means a transaction is completed fully or not at all.
Which SQL clause is used to filter groups formed by GROUP BY?
1. HAVING
2. WHERE
3. DISTINCT
4. ORDER BY
Answer
A. HAVING
HAVING filters groups; WHERE filters rows before grouping.
A foreign key in a table refers to
1. a NULL value only
2. any non-key column of the same table
3. the primary key of another table
4. the DBA of the system
Answer
C. the primary key of another table
A foreign key links to a primary key of the parent table.
In relational algebra, the operation that picks specific columns is
1. Union
2. Cartesian product
3. Select
4. Project
Answer
D. Project
Project (π) picks columns; Select (σ) picks rows.
The person who controls the whole database system and its security is the
1. compiler
2. application programmer
3. end user
4. DBA
Answer
D. DBA
The Database Administrator manages and secures the database.
Which is the first normal form that removes partial dependencies?
1. 1NF
2. 2NF
3. BCNF
4. 3NF
Answer
B. 2NF
2NF removes partial dependency on part of a composite key.
Which is the first normal form that removes transitive dependencies?
1. 3NF
2. 1NF
3. 2NF
4. BCNF
Answer
A. 3NF
3NF = 2NF with no transitive dependency.
A table has 6 columns and 25 rows. Its degree and cardinality are
1. 25 and 6
2. 6 and 25
3. 6 and 150
4. 31 and 6
Answer
B. 6 and 25
Degree is column count (6); cardinality is row count (25).
Which statement correctly finds names that start with the letter A?
1. SELECT name FROM t WHERE name = 'A%';
2. SELECT name FROM t WHERE name LIKE '%A';
3. SELECT name FROM t HAVING name = 'A';
4. SELECT name FROM t WHERE name LIKE 'A%';
Answer
D. SELECT name FROM t WHERE name LIKE 'A%';
LIKE with % matches any number of characters after A.
To test for missing values in SQL one should write
1. WHERE marks == 0
2. WHERE marks = NULL
3. WHERE marks IS NULL
4. WHERE marks LIKE NULL
Answer
C. WHERE marks IS NULL
NULL is tested with IS NULL; = NULL never matches.
A relation R has 4 rows and relation S has 5 rows. The Cartesian product R × S has how many rows?
1. 9
2. 1
3. 20
4. 45
Answer
C. 20
Cartesian product gives 4 × 5 = 20 rows.
A bank transfer must debit one account and credit the other together or not at all. This is ensured by
1. atomicity
2. normalization
3. redundancy
4. data independence
Answer
A. atomicity
Atomicity makes the two steps one unit.
Changing how files are stored on disk without changing the conceptual schema shows
1. physical data independence
2. logical data independence
3. entity integrity
4. referential integrity
Answer
A. physical data independence
Physical independence hides storage changes from the conceptual level.
Which of the following SQL statements removes only some rows of a table?
1. ALTER TABLE student;
2. TRUNCATE TABLE student;
3. DROP TABLE student;
4. DELETE FROM student WHERE marks < 35;
Answer
D. DELETE FROM student WHERE marks < 35;
DELETE with WHERE removes selected rows.
A candidate key that is not chosen as the primary key is called an
1. foreign key
2. alternate key
3. weak key
4. composite key
Answer
B. alternate key
Unchosen candidate keys are alternate keys.
A table where each cell holds a single atomic value and has no repeating groups is in at least
1. BCNF
2. 1NF
3. 3NF
4. 2NF
Answer
B. 1NF
1NF requires atomic values.
A virtual table created from the result of a query is called a
1. view
2. trigger
3. cursor
4. index
Answer
A. view
A view stores a query, not separate data.
Which of these is an aggregate function?
1. LIKE
2. ORDER
3. JOIN
4. AVG
Answer
D. AVG
AVG, SUM, COUNT, MIN, MAX are aggregate functions.
Which type of join returns only the rows that match in both tables?
1. FULL OUTER JOIN
2. LEFT OUTER JOIN
3. INNER JOIN
4. CROSS JOIN
Answer
C. INNER JOIN
INNER JOIN keeps only matching rows.
A weak entity in an ER diagram is shown by a
1. single diamond
2. double ellipse
3. dashed ellipse
4. double rectangle
Answer
D. double rectangle
Weak entity = double rectangle; multi-valued attribute = double ellipse.
Which SQL constraint ensures that all values in a column are different?
1. UNIQUE
2. CHECK
3. NOT NULL
4. DEFAULT
Answer
A. UNIQUE
UNIQUE blocks duplicate values; NOT NULL only blocks missing values.
The order of clauses in a typical SELECT query is
1. SELECT, WHERE, FROM, HAVING, GROUP BY, ORDER BY
2. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
3. FROM, SELECT, GROUP BY, WHERE, ORDER BY, HAVING
4. SELECT, FROM, GROUP BY, WHERE, HAVING, ORDER BY
Answer
B. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
This is the standard written order of clauses.
Consider these statements about keys. 1. A table can have only one primary key. 2. A foreign key value can never be NULL. Which is/are correct?
1. 1 only
2. 2 only
3. Both 1 and 2
4. Neither 1 nor 2
Answer
A. 1 only
A table has one primary key; foreign keys may be NULL, so 2 is wrong.
Consider these statements about SQL. 1. TRUNCATE removes all rows but keeps the table structure. 2. DELETE cannot use a WHERE clause. Which is/are correct?
1. 1 only
2. 2 only
3. Both 1 and 2
4. Neither 1 nor 2
Answer
A. 1 only
DELETE can use WHERE, so statement 2 is wrong.
Consider these statements. 1. DROP is a DDL command. 2. INSERT is a DML command. Which is/are correct?
1. 1 only
2. 2 only
3. Both 1 and 2
4. Neither 1 nor 2
Answer
C. Both 1 and 2
Both are correct classifications.
Consider these statements about normalization. 1. It reduces data redundancy. 2. It helps avoid insertion, deletion and update anomalies. Which is/are correct?
1. 1 only
2. 2 only
3. Both 1 and 2
4. Neither 1 nor 2
Answer
C. Both 1 and 2
Both are goals of normalization.
Consider these statements about ACID. 1. Durability means committed changes survive a system failure. 2. Isolation means transactions run without interfering with each other. Which is/are correct?
1. 1 only
2. 2 only
3. Both 1 and 2
4. Neither 1 nor 2
Answer
C. Both 1 and 2
Both describe the properties correctly.
Consider these statements. 1. A view stores a separate copy of the data permanently. 2. An index speeds up searching. Which is/are correct?
1. 1 only
2. 2 only
3. Both 1 and 2
4. Neither 1 nor 2
Answer
B. 2 only
A view is virtual and does not store a separate copy; so only 2 is correct.
Consider these statements about data independence. 1. Logical data independence is easier to achieve than physical data independence. 2. Physical independence means storage changes do not affect the conceptual schema. Which is/are correct?
1. 1 only
2. 2 only
3. Both 1 and 2
4. Neither 1 nor 2
Answer
B. 2 only
Logical independence is harder; so only 2 is correct.
Match the SQL command with its group. P. SELECT Q. GRANT R. ROLLBACK 1. TCL 2. DCL 3. DML
1. P-3, Q-1, R-2
2. P-1, Q-2, R-3
3. P-2, Q-3, R-1
4. P-3, Q-2, R-1
Answer
D. P-3, Q-2, R-1
SELECT is DML (or DQL), GRANT is DCL, ROLLBACK is TCL.
Match the ER symbol with its meaning. P. Rectangle Q. Ellipse R. Diamond 1. Relationship 2. Entity 3. Attribute
1. P-2, Q-1, R-3
2. P-1, Q-3, R-2
3. P-3, Q-2, R-1
4. P-2, Q-3, R-1
Answer
D. P-2, Q-3, R-1
Rectangle is an entity, ellipse an attribute, diamond a relationship.
Match the key with its description. P. Super key Q. Candidate key R. Foreign key 1. Minimal unique identifier 2. Refers to another table's key 3. Any attribute set that identifies rows uniquely
1. P-3, Q-1, R-2
2. P-2, Q-1, R-3
3. P-1, Q-3, R-2
4. P-3, Q-2, R-1
Answer
A. P-3, Q-1, R-2
A super key may have extra attributes; a candidate key is minimal.
Table Emp(Id, Name, Dept) with Id as primary key. Which query counts the employees in each department?
1. SELECT Dept, COUNT(*) FROM Emp ORDER BY Dept;
2. SELECT COUNT(Dept) FROM Emp WHERE Dept;
3. SELECT Dept FROM Emp HAVING COUNT(*);
4. SELECT Dept, COUNT(*) FROM Emp GROUP BY Dept;
Answer
D. SELECT Dept, COUNT(*) FROM Emp GROUP BY Dept;
GROUP BY with COUNT(*) gives a count per department.
A relation has attributes (RollNo, Subject, Marks, StudentName) with key (RollNo, Subject). StudentName depends only on RollNo. This is a
1. multi-valued attribute
2. partial dependency
3. referential constraint
4. transitive dependency
Answer
B. partial dependency
Dependence on part of a composite key is partial dependency, removed in 2NF.
Two transactions each hold a lock the other needs and both wait forever. This situation is called
1. normalization
2. atomicity
3. deadlock
4. durability
Answer
C. deadlock
Waiting forever for each other's locks is deadlock.
Which one is a relational database management system?
1. Photoshop
2. Notepad
3. MySQL
4. Python IDLE
Answer
C. MySQL
MySQL, Oracle and PostgreSQL are RDBMS software.

Page 1 of 1

Finished reading?

Test yourself in 10 minutes

A free 10-question mini-mock on your exam's real pattern, with negative marking and an instant score. अपनी तैयारी 10 मिनट में जाँचें।

Take a free mini-mock →Topic-wise practice