L.08 Flashcards
Finishing up SQL and Normalization
What are aggregate functions in SQL?
Functions like COUNT, SUM, MAX, MIN, and AVG that summarize multiple tuples into a single value.
How do you find the sum, max, min, and average salary from an EMPLOYEE table?
SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM EMPLOYEE;
What SQL clause is used to group rows based on a specific attribute?
GROUP BY clause.
Write a query to find the number of employees and their average salary per department.
SELECT Dno, COUNT(*), AVG(Salary)
FROM EMPLOYEE
GROUP BY Dno;
What SQL clause filters groups instead of individual rows?
HAVING clause.
Why is redundancy a problem in database design?
It wastes space and can cause update anomalies.
What are functional dependencies (FDs)?
A relationship where one attribute uniquely determines another, written as X → Y.
Give an example of a functional dependency.
SSN → ENAME (Social Security Number determines Employee Name).
What is normalization?
The process of refining database schemas to remove redundancies and ensure proper keys.
What is the First Normal Form (1NF)?
It eliminates composite, multivalued, and nested attributes.
What is the Second Normal Form (2NF)?
It ensures that non-key attributes are fully functionally dependent on the entire primary key.
What is the Third Normal Form (3NF)?
It prevents non-key attributes from depending on other non-key attributes.
What are acceptable functional dependencies?
Those involving primary or foreign keys.
What key takeaways should you remember about normalization?
Define meaningful relations, avoid redundancy, and properly define keys.
What is the purpose of aliasing in SQL aggregate functions?
Aliasing renames result columns for better readability.
Example:
SELECT SUM(Salary) AS Total_Sal, AVG(Salary) AS Avg_Sal
FROM EMPLOYEE;
What is a common mistake when using GROUP BY?
Including non-aggregate attributes in the SELECT clause that are not part of GROUP BY.
What is the order of execution in a SQL query with WHERE, GROUP BY, and HAVING?
WHERE (filters individual rows)
GROUP BY (groups rows)
HAVING (filters entire groups)
What is an update anomaly?
A data inconsistency that occurs when redundant data is updated incorrectly.
What is a deletion anomaly?
Loss of useful data when a tuple is deleted.
Example: Deleting the last employee working on a project removes project details.
What is an insertion anomaly?
Difficulty in inserting new data due to unnecessary dependencies.
Example: Unable to add a new project until at least one employee is assigned to it.
How do foreign keys help in normalization?
They link related tables, reducing redundancy and ensuring referential integrity.
What is a bad database design example related to foreign keys?
Removing Super_ssn from EMPLOYEE makes it impossible to track managers.
What are redundant functional dependencies?
FDs that store information already available through joins.
Example:
WORKS_ON(Emp#, Proj#, Ename, Pname, No_hours)
Here, Ename and Pname are redundant since they can be derived from EMPLOYEE and PROJECT.
What is the trade-off between redundancy and performance?
Redundancy can improve query performance but risks data inconsistencies.
