DBMS

• What is the program discussed in the document?

  Bachelor of Science in Information Technology
• What are the three clauses discussed in the Declarative Knowledge section?

  Union Clause, Group by Clause, Having Clause
• What is the intended learning outcome regarding MySQL syntax?

  Apply and use MySQL syntax of clauses
• What is the purpose of the Having Clause in MySQL?

  • Filters result data rows on a Group By Clause
  • Used for aggregated data rows
  • Similar to WHERE Clause but for groups
• What does the Group By Clause do?

  Aggregates data rows with the same values
• How is the Group By Clause used with aggregate functions?

  It collects values of similar types and displays them
• What is the syntax for the Group By Clause?

  SELECT column1, ... FROM tableName GROUP BY columnName;
• What does the sample command using Group By Clause display?

  Total number of customers grouped by membership status
• What is the purpose of the Having Clause?

  Filters results after aggregation in Group By
• What is the syntax for the Having Clause?

  SELECT ... FROM tableName GROUP BY columnName HAVING condition;
• What does the sample command using Having Clause on COUNT() display?

  Total customers grouped by datePurchase with > 10
• What does the sample command using Having Clause on SUM() display?

  Sum of prices grouped by datePurchase > 10000
• What does the MySQL Union Operator do?

  Unites query results from multiple SELECT statements
• What are the requirements for using the Union Operator?

  Same column number, data types, and order
• What is the syntax for the Union Operator?

  SELECT ... FROM tableName1 UNION SELECT ... FROM tableName2;
• What does the sample command using Union Operator display?

  Unique itemName values from tblSales and tblDispose
• What is Database Normalization?

  • Eliminates data redundancy
  • Ensures logical data storage
  • Organizes data for flexibility
• What are the three normal forms of database normalization?

  1st, 2nd, and 3rd Normal Form
• What is the rule of the First Normal Form (1NF)?

  No duplicate rows and single values in cells
• What is the solution to ensure unique rows in 1NF?

  Add an order ID as a primary key
• What must each cell contain in 1NF?

  Only a single value, not lists
• What is the solution for multiple values in a cell in 1NF?

  Create a separate table for order items
• What does atomic data mean in 1NF?

  Data must be non-divisible
• What is the solution for non-atomic data in 1NF?

  Create separate columns for first and last names
• What is the rule of the Second Normal Form (2NF)?

  Must be in 1NF and have no partial dependencies
• What is an example of a partial dependency issue in 2NF?

  Course fee dependent on primary key
• What is the solution for partial dependency in 2NF?

  Create separate tables for student courses and course fees
• What is the rule of the Third Normal Form (3NF)?

  Must be in 1NF and 2NF with no transitive dependencies
• What is a transitive dependency in 3NF?

  Non-prime attributes depend on other non-prime attributes
• What is required for a database to be in 3NF?

  Must be in 1NF and 2NF
• What are the steps of database normalization?

  1. First Normal Form (1NF)
  2. Second Normal Form (2NF)
  3. Third Normal Form (3NF)
• What is the definition of Third Normal Form (3NF)?

  A relation in 2NF with no transitive dependencies
• What is required for a relation to be in Third Normal Form?

  It must be in 2NF without transitive dependencies
• What is the rule for a relation to be in Third Normal Form?

  Must be in First and Second Normal Form
• What must all fields be determinable by in Third Normal Form?

  By the primary or composite key only
• In the Singing Competition example, what is the problem with the Winner's Date of Birth?

  It can be determined by other keys
• What is the solution to the problem in the Singing Competition table?

  • Create two tables:
  • Singing Competition Winners
  • Winners DOBs
• What does the Singing Competition Winners Table contain?

  Year and Winner of the competition
• What does the Winners DOBs Table contain?

  Winner and Winner's Date of Birth
• Why can't the winner be determined by just looking at the year?

  Multiple competitions occur every year