Databases

• What is a relational database?

  A database that stores data in multiple tables
• What is a flat file?

  A database that stores data in a single table
• What is a primary key?

  A unique identifier for each record
• What is a foreign key?

  A copy of a primary key in another table
• What is a secondary key?

  A field indexed but not a primary key
• What is the purpose of indexing?

  To make sorting and searching quicker
• What is data redundancy?

  Data stored more than once in a system
• What is data inconsistency?

  Contradictory copies of the same data
• What does data integrity refer to?

  The correctness and rightness of data
• What are the formats for exporting data from a database?

  • CSV (Comma Separated Value)
  • XML (eXtensible Markup Language)
  • SQL (Structured Query Language)
• What is normalization in databases?

  Analyzing a database design for efficiency
• What is 1NF in normalization?

  No repeated fields in a database
• What is 2NF in normalization?

  Must be in 1NF with no partial key dependencies
• What is 3NF in normalization?

  Must be in 2NF with no non-key dependencies
• What are methods of exchanging data with other computer systems?

  • CSV
  • JSON
  • XML
  • SQL
  • APIs
  • EDI
  • RSS
  • SOAP
• What does the SELECT statement do in SQL?

  It selects data from a database
• What does the FROM clause specify in SQL?

  Which table to select or delete data from
• What does the WHERE clause do in SQL?

  Filters results based on specified conditions
• What does the LIKE operator do in SQL?

  Searches for a specified pattern in a column
• What does the wildcard '%' represent in SQL?

  Zero, one, or multiple characters
• What does the wildcard '_' represent in SQL?

  A single character
• What does the AND operator do in SQL?

  Includes rows where both conditions are true
• What does the OR operator do in SQL?

  Includes rows where either condition is true
• What does the DELETE statement do in SQL?

  Deletes existing records in a table
• What does the INSERT statement do in SQL?

  Inserts new rows into a table
• What does the DROP statement do in SQL?

  Deletes a whole database or table
• What does the JOIN clause do in SQL?

  Returns rows with matching values in both tables
• What is referential integrity in databases?

  • Ensures consistent changes across a database
  • Removes all references if a record is deleted
  • A foreign key must correspond to a primary key
• What does ACID stand for in database transactions?

  • Atomic: Transaction fully completes or not
  • Consistency: Affects entire database
  • Isolation: Prevents simultaneous changes
  • Durability: Changes remain once made
• What are the benefits and drawbacks of a flat file database?

  Benefits:

  • Easy to set up
  • All records in one place

  
  

  Drawbacks:

  • More redundant data
  • Slower searching
  • Harder to manage and expand
• What are the benefits and drawbacks of a relational database?

  Benefits:

  • Less redundant data
  • Easier to manage and expand
  • Faster searching
  • More secure access

  
  

  Drawbacks:

  • More difficult to set up
  • Data spread across multiple tables
  • More expensive due to DBMS