English
Menu

Database Basics

What is the main purpose of normalization of tables in a relational database?

  1. ATo reduce the number of tables so that any search can be done quickly without joins
  2. BTo encrypt data so that unauthorized users cannot read it
  3. CTo eliminate duplicated data and prevent anomalies such as contents becoming inconsistent after an update
Show answer and explanation

Answer: C. To eliminate duplicated data and prevent anomalies such as contents becoming inconsistent after an update

Normalization is a design procedure that splits tables based on functional dependencies between columns (a relationship in which the value of one column determines the value of another) so that the same fact is not recorded in several places. For example, if every row of an employee table also holds the department name, renaming a department requires rewriting every row of that department's employees, and missing any of them leaves the contents inconsistent (an update anomaly). Other problems arise as well: a department with no employees yet cannot be registered (an insertion anomaly), and deleting the last employee of a department also erases the department's information (a deletion anomaly). If department information is moved into a separate department table, a department name needs to be corrected in only one place. On the other hand, splitting tables means more joins when searching, so normalization does not necessarily make searches faster.

  • A. Normalization splits tables into more of them, and searches more often need joins.
  • B. Encryption is a security measure and has nothing to do with normalization.
  • C. Correct. The purpose is to remove duplication and prevent update, insertion and deletion anomalies.
Practice this topic Back to topic

Related calculators

SQL PracticeRun SQL in your browser and check the results, with 10 practice exercises that grade your answers

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

More Database Basics questions

  1. The primary key of an order table (order_no, item_no, qty, cust_no, cust_name) is {order_no, item_no}, and its functional dependencies are {order_no, item_no} → qty, order_no → cust_no and cust_no → cust_name. Which is the correct result of normalizing this table up to third normal form (3NF)?
  2. The primary key of an employee table (emp_no, name, dept_code, dept_name) is emp_no, and every column holds a single value. The functional dependencies are emp_no → name, emp_no → dept_code and dept_code → dept_name. Which correctly states the normal form of this table?
  3. In a database used mainly for reading and aggregation, normalized tables are sometimes deliberately merged into one so that the same data is held in duplicate (denormalization). Which correctly pairs an advantage and a disadvantage of this design?
  4. In an E-R diagram, there is a many-to-many relationship between students and courses: one student takes several courses, and one course is taken by several students. Which is an appropriate way to implement this relationship with relational database tables?
  5. In an account table, a transaction is started, and an UPDATE that subtracts 300 from account A's balance and an UPDATE that adds 300 to account B's balance are executed. Then ROLLBACK is executed instead of COMMIT. What happens to the account table?