Database Exam 2

What are entity integrity constraints in SQL?

Constraints that ensure each entity in a database is unique and identifiable.

What are referential integrity constraints in SQL?

Constraints that ensure relationships between tables remain consistent.

What is the purpose of the NOT NULL constraint?

To ensure that a column cannot have a NULL value.

What does the DEFAULT constraint do?

Specifies a default value for a column when no value is provided.

What is the CHECK constraint?

Ensures that all values in a column satisfy a specific condition.

What is a FOREIGN KEY in SQL?

A key used to link two tables together, referencing the primary key of another table.

What does the RESTRICT action do in referential integrity?

Prevents the deletion of a referenced row in a parent table.

What is the purpose of the CASCADE action?

Automatically deletes or updates rows in child tables when the corresponding row in the parent table is deleted or updated.

What does SET NULL do in referential integrity?

Sets the foreign key value to NULL when the referenced row is deleted.

What is SET DEFAULT in referential integrity?

Sets the foreign key value to a default value when the referenced row is deleted.

What is a nested query in SQL?

A query within another query, used for fetching values for comparisons.

What is a JOIN operation in SQL?

Combines rows from two or more tables based on a related column.

What is a NATURAL JOIN?

A join that automatically matches columns with the same name in both tables.

What is an OUTER JOIN?

A join that includes all rows from one table and matched rows from another, padding unmatched rows with NULLs.

What is a LEFT OUTER JOIN?

Keeps all tuples from the left table and matches with the right table.

What is a RIGHT OUTER JOIN?

Keeps all tuples from the right table and matches with the left table.

What is a FULL OUTER JOIN?

Keeps all tuples from both tables, matching where possible.

What is a CROSS JOIN?

Creates a Cartesian product of two tables, combining all possible tuples.

What are aggregate functions in SQL?

Functions that summarize data from multiple rows into a single value.

What does the COUNT function do?

Counts the number of rows in a specified dataset.

What does the SUM function do?

Adds up all the values in a specified numeric column.

What does the MAX function do?

Returns the maximum value in a specified column.

What does the MIN function do?

Returns the minimum value in a specified column.

What does the AVG function do?

Calculates the average of numeric values in a specified column.

What is the GROUP BY clause used for?

To group rows that have the same values in specified columns into summary rows.

What is the HAVING clause used for?

To filter records that work on summarized GROUP BY results.

What is the purpose of the WITH clause in SQL?

It defines a temporary table used in a single query and is dropped afterward.

What does the SELECT statement retrieve for departments with an average salary over $30,000?

It retrieves the department name and the number of employees working for that department.

How can we count male employees making over $30,000 in SQL?

By using a SELECT statement with COUNT(*) and filtering by Sex = 'M' and AVG(Salary) > 30000.

What query retrieves names and majors of straight-A students?

It uses a JOIN between STUDENT and GRADE_REPORT and filters with HAVING SUM(CASE WHEN Grade = 'A' THEN 1 ELSE 0 END) = 0.

What query retrieves names and majors of students without any A grades?

It uses a JOIN and filters with HAVING SUM(CASE WHEN GRADE IS NOT 'A' THEN 0 ELSE 1 END) = 0.

How do you retrieve names of employees in the department with the highest paid employee?

By using a nested query that selects Dno from Employee where Salary is the maximum.

What is the SQL query to find employees whose supervisor's supervisor has a specific SSN?

It uses a nested IN query to filter employees based on their supervisor's supervisor's SSN.

What does the query to find employees making at least $10,000 more than the lowest paid employee do?

It compares each employee's salary to the minimum salary plus $10,000.

What information does the Department_Info view contain?

It includes department name, manager name, and manager salary for every department.

What does the Employee_Info view display?

It shows employee name, supervisor name, and employee salary for each employee in the Research department.

What does the ProjectInfo view provide?

It includes project name, controlling department name, number of employees, and total hours worked per week.

What are insertion anomalies in databases?

They occur when you cannot add data because some other data is missing.

What are deletion anomalies in databases?

They happen when unrelated information is lost while deleting a specific record.

What are modification anomalies in databases?

They occur when data is changed in one place but not others, leading to inconsistency.

Why should NULLs in a relation be avoided?

They complicate joins, waste storage, and cause ambiguous results in aggregate functions.

What is a functional dependency?

It is a constraint between two sets of attributes where the left side must equal the right side for any two tuples.

Why can't we infer a functional dependency from a single relation state?

It is a property of the relation schema, not a specific state, and must be defined by a designer.

What is the definition of first normal form (1NF)?

A relation is in 1NF if all attributes consist of single atomic (indivisible) values.

What is the definition of second normal form (2NF)?

A relation is in 2NF if every nonprime attribute has a full functional dependency on the primary key.

What undesirable dependencies does 2NF avoid?

It avoids partial dependencies where a nonprime attribute depends on only part of a composite key.

What undesirable dependencies does 3NF avoid?

It avoids both partial and transitive dependencies, ensuring higher data integrity.

How do the generalized definitions of 2NF and 3NF extend beyond primary keys?

They consider all candidate keys, not just the primary key, for dependency checks.

What is the definition of 2NF?

Second Normal Form (2NF) extends the definition of normalization beyond primary keys by considering all candidate keys.

What is the definition of 3NF?

Third Normal Form (3NF) is a database normalization form that requires that all non-prime attributes are not only dependent on the primary key but also independent of each other.

What is Boyce-Codd Normal Form (BCNF)?

A relation is in Boyce-Codd Normal Form (BCNF) if, for every nontrivial functional dependency A → B, A is a superkey of the relation.

How does BCNF differ from 3NF?

BCNF differs from 3NF by omitting the option that allows B to be a prime attribute in a nontrivial functional dependency.

Why is BCNF considered a stronger form than 3NF?

BCNF is stronger than 3NF because there are relations that are in 3NF but not in BCNF.

What are insertion anomalies?

Insertion anomalies occur when adding a new record requires the re-entry of information, potentially leading to inconsistency.

What are deletion anomalies?

Deletion anomalies occur when deleting a record results in the loss of additional information that should be retained.

What are modification anomalies?

Modification anomalies occur when changing a value requires updates to multiple records, increasing the risk of inconsistency.

What is the functional dependency for a student's information?

{Ssn} → {Sname, Snum, Sc_addr, Sc_phone, Sp_addr, Sp_phone, Bdate, Sex, Class, Major_code, Minor_code, Prog}

What is the functional dependency for a department's information?

{Dname} → {Dcode, Doffice, Dphone, Dcollege}

What is the functional dependency for a course's information?

{Cnum} → {Cname, Cdesc, Credit, Level, Cdept}

What is the functional dependency for a section's information?

{Sec_course, Semester, Year, Sec_num} → {Iname}

What is the functional dependency for a grade record?

{Ssn, Sec_course, Semester, Year, Sec_num} → {Grade}

What is the relational schema for STUDENT?

STUDENT (Ssn, Snum, Sname, Sc_addr, Sc_phone, Sp_addr, Sp_phone, Bdate, Sex, Class, Major_code, Minor_code, Prog)

What is the relational schema for DEPARTMENT?

DEPARTMENT (Dcode, Dname, Doffice, Dphone, Dcollege)

What is the relational schema for COURSE?

COURSE (Cnum, Cname, Cdesc, Credit, Level, Cdept)

What is the relational schema for SECTION?

SECTION (Sec_course, Semester, Year, Sec_num, Iname)

What is the relational schema for GRADE_RECORD?

GRADE_RECORD (Ssn, Sec_course, Semester, Year, Sec_num, Grade)

What is the first normal form (1NF)?

A relation is in first normal form if it contains no multivalued attributes or nested relations.

What is the second normal form (2NF) based on primary key?

A relation is in second normal form based on primary key if every nonprime attribute is fully functionally dependent on the primary key.

What is the third normal form (3NF) based on primary key?

A relation is in third normal form based on primary key if there are no transitive dependencies among nonprime attributes.