Comp. Sci and Programming Basics: Databases Relational Databases (Tables, Rows, Columns, Keys – Primary, Foreign)

Concept Summary

• A relational database is a type of database that stores data in one or more tables, with each table having rows and columns.
• Each row in a table represents a single record or entry, while each column represents a field or attribute of that record.
• A primary key is a unique identifier for each row in a table, used to distinguish it from other rows.
• A foreign key is a field in a table that references the primary key of another table, establishing a relationship between the two tables.
• Relational databases use keys to maintain data consistency and integrity, ensuring that data is accurate and up-to-date.

Questions

WHAT (definitional)

1. What is a primary key in a relational database?
2. Answer: A primary key is a unique identifier for each row in a table, used to distinguish it from other rows.
3. Real-world example: A primary key could be a student ID number in a student database.

4. Misconception cleared: A primary key is not the same as a unique identifier, although it is often used as one.

5. What is a foreign key in a relational database?

6. Answer: A foreign key is a field in a table that references the primary key of another table, establishing a relationship between the two tables.
7. Real-world example: A foreign key could be a customer ID number in an order database that references the customer ID number in a customer database.

8. Misconception cleared: A foreign key is not a primary key, although it references a primary key.

9. What is a table in a relational database?

10. Answer: A table is a collection of related data, with each row representing a single record or entry and each column representing a field or attribute of that record.
11. Real-world example: A table could be a list of students, with columns for name, age, and grade level.
12. Misconception cleared: A table is not the same as a spreadsheet, although it can be used to store similar types of data.

WHY (causal reasoning)

1. Why are primary keys used in relational databases?
2. Answer: Primary keys are used to maintain data consistency and integrity, ensuring that data is accurate and up-to-date.
3. Real-world example: Without primary keys, it would be difficult to distinguish between different records in a database, leading to errors and inconsistencies.

4. Misconception cleared: Primary keys are not used solely for convenience, but rather to ensure the accuracy and reliability of data.

5. Why are foreign keys used in relational databases?

6. Answer: Foreign keys are used to establish relationships between tables, allowing data to be linked and referenced across multiple tables.
7. Real-world example: Without foreign keys, it would be difficult to link orders to customers in a database, leading to errors and inconsistencies.

8. Misconception cleared: Foreign keys are not used solely to create complex relationships between tables, but rather to establish meaningful connections between data.

9. Why are tables used in relational databases?

10. Answer: Tables are used to organize and structure data, making it easier to store, retrieve, and manipulate.
11. Real-world example: Without tables, data would be scattered and disorganized, making it difficult to manage and analyze.
12. Misconception cleared: Tables are not used solely to store data, but rather to create a logical and structured framework for data management.

HOW (process/application)

1. How are primary keys created in a relational database?
2. Answer: Primary keys are typically created by assigning a unique identifier to each row in a table, such as a serial number or a unique code.
3. Real-world example: A primary key could be created by assigning a unique student ID number to each student in a database.

4. Misconception cleared: Primary keys are not created randomly, but rather through a deliberate and systematic process.

5. How are foreign keys established in a relational database?

6. Answer: Foreign keys are established by referencing the primary key of another table, typically through a database query or a data import process.
7. Real-world example: A foreign key could be established by referencing the customer ID number in a customer database from an order database.

8. Misconception cleared: Foreign keys are not established through a simple copy-and-paste process, but rather through a deliberate and structured process.

9. How are tables created in a relational database?

10. Answer: Tables are created through a database management system, such as SQL or a graphical user interface.
11. Real-world example: A table could be created by using a SQL query to define the structure and columns of the table.
12. Misconception cleared: Tables are not created through a simple spreadsheet or document, but rather through a database management system.

CAN (possibility/conditions)

1. Can a table have multiple primary keys?
2. Answer: No, a table can only have one primary key.
3. Real-world example: Having multiple primary keys would create ambiguity and inconsistencies in the data.

4. Misconception cleared: Having multiple primary keys is not a common practice, and is generally avoided.

5. Can a foreign key reference a primary key in a different database?

6. Answer: Yes, a foreign key can reference a primary key in a different database, as long as the databases are connected and accessible.
7. Real-world example: A foreign key could reference a primary key in a customer database that is hosted on a different server.

8. Misconception cleared: Foreign keys can reference primary keys in different databases, as long as the databases are properly connected and configured.

9. Can a table have no primary key?

10. Answer: Yes, a table can have no primary key, although this is generally not recommended.
11. Real-world example: A table could have no primary key if it is not necessary to distinguish between rows, such as a log file.
12. Misconception cleared: Having no primary key is not always a bad thing, although it can lead to data inconsistencies and errors.

TRUE/FALSE (misconception testing)

1. Statement: A primary key is the same as a unique identifier.
2. Answer: FALSE
3. Real-world example: A primary key is a unique identifier, but it is not the same as a unique identifier, although it is often used as one.

4. Misconception cleared: A primary key is a specific type of unique identifier that is used to distinguish between rows in a table.

5. Statement: A foreign key is a primary key in another table.

6. Answer: FALSE
7. Real-world example: A foreign key is a field in a table that references the primary key of another table, establishing a relationship between the two tables.

8. Misconception cleared: A foreign key is not a primary key, although it references a primary key.

9. Statement: Tables are used solely to store data.

10. Answer: FALSE
11. Real-world example: Tables are used to organize and structure data, making it easier to store, retrieve, and manipulate.
12. Misconception cleared: Tables are used to create a logical and structured framework for data management, in addition to storing data.