What is the purpose of Normalization or schema refinement?
- Eliminating redundant data
- Minimize data modification errors
- Simplify the query process
Normalization is a systematic approach to organizing data in the database to eliminate redundancy and anomalies.
Define anomaly in the context of databases.
Problems that occur after poorly planned and normalized databases
Anomalies include Insertion, Update, and Deletion anomalies.
What are the types of anomalies in DBMS?
- Insert Anomaly
- Update Anomaly
- Delete Anomaly
These anomalies arise from redundancy in unnormalized databases.
What is an Insert Anomaly?
Occurs when a new row is added and causes inconsistency
Example: Cannot add a new course without enrolling a student.
What is an Update Anomaly?
Requires updating multiple rows to maintain consistency
Example: Changing an instructor’s phone number requires updates in every row.
What is a Delete Anomaly?
Deleting a row removes important data
Example: Deleting the last student enrolled in a course removes the course details.
What is the concept of functional dependency?
A constraint that specifies the relationship between two sets of attributes
Represented as X → Y, where X determines Y.
What does the notation X → Y signify?
Y is functionally dependent on X
X is the determinant set, and Y is the dependent attribute.
What is decomposition in database normalization?
The process of breaking down a table into various elements or parts
It helps eliminate redundancy.
What is a Primary Key?
A column or group of columns that uniquely identify every row in a table
A table can have only one primary key.
What is a Foreign Key?
Used to establish relationships between two tables
It can have null values and is part of referential integrity.
What is a Super Key?
A single attribute or combination of attributes that can uniquely identify a row in a table
A table can have multiple super keys.
What is a Candidate Key?
An attribute or set of attributes that can uniquely identify a tuple in a relation
Candidate keys can be chosen as primary keys.
What is a Composite Key?
A key that consists of two or more attributes that together uniquely identify a tuple
It is used when a single attribute is not sufficient.
What is a Transitive Dependency?
A dependency where one column depends indirectly on the primary key through another column
Example: If A → B and B → C, then A → C.
What is a Multi-valued Dependency?
Occurs when an attribute in a relation depends on another attribute independently of other attributes
Example: Car model determining multiple independent values of color.
What is the closure of a functional dependency?
The process of finding all functional dependencies that can be derived from the original set
Important for normalization and identifying redundant data.
What is a Trivial Functional Dependency?
When the dependent attribute is already part of the determinant attribute
Example: {Employee_ID, Employee_Name} → Employee_Name.
What is a Non-Trivial Functional Dependency?
When the dependent attribute is not a subset of the determinant
Example: Employee_ID → Employee_Name.
What are the key benefits of normalization?
- No redundant data
- No anomalies
- Better data integrity and consistency
Normalization improves the overall quality of the database.
The relationship between two tables is known as Referential Integrity.
True
Referential integrity ensures that relationships between tables remain consistent.
A single table can have _______ Foreign Key’s.
Multiple
This allows for complex relationships between tables.
A Foreign key can have _______ values.
null
Null values in foreign keys indicate that there is no relationship for that record.
The table consisting of the foreign key is known as the CHILD table and the table that is referred to by the foreign key is called the PARENT table.
True
This terminology is used to describe the relationship between tables in a database.
