Relational Databases

Question 1

How does a relational database store data and what are the basic components?

Answer 1

Stores data in tables with rows (individual data records/entities) and columns (data attributes/properties). Data is parsed into multiple tables connected using keys

Question 2

What are primary and foreign keys and how do they create connections between tables?

Answer 2

Primary keys uniquely identify each record in a table. Foreign keys reference primary keys in other tables, creating relationships and connections between different tables

Question 3

What two key principles govern relational database table design?

Answer 3

1. Each table contains only minimal information with every informational theme (business concept) having its own table. 2. All attributes in a table must depend only on the primary key and nothing else

Question 4

Why must all attributes in a table depend only on the primary key? Give an example of what violates this rule.

Answer 4

Prevents data redundancy and inconsistency. Violation example: A table with CustomerID (primary key), CustomerName, OrderDate, and ProductPrice - ProductPrice depends on the product, not the customer, so it belongs in a separate table

Question 5

What is a Relational Database Management System (RDBMS) and what role does it play?

Answer 5

Software that allows interaction with databases - like the operating system of the database. It interprets queries and commands and executes database operations

Question 6

What are the 4 main categories of operations possible on a relational database through an RDBMS?

Answer 6

1. Database Design (planning structure, tables, relationships), 2. Construction (creating and populating database), 3. Data manipulation (insert, delete, update, query), 4. Reporting (summarizing data for analysis)

Question 7

Distinguish between Database Design and Construction operations with examples.

Answer 7

Database Design: Planning structure, designing tables, setting relationships (conceptual work). Construction: Actually creating the database, building tables, populating with initial data (implementation work)

Question 8

Categorize these operations: SELECT queries, CREATE TABLE, INSERT records, generating sales reports, UPDATE customer info, DELETE old records.

Answer 8

Data Manipulation: SELECT queries, INSERT/UPDATE/DELETE records. Database Design-Construction: CREATE TABLE. Reporting: generating sales reports

Question 9

What is the relationship between SQL and relational databases?

Answer 9

SQL is the programming language specifically used to communicate with and control relational databases - it’s the standard interface for all relational database operations

Question 10

You have customer data: names, addresses, orders, and products. How should this be organized in a relational database and why?

Answer 10

Separate tables: Customers (names, addresses), Orders (order details), Products (product info). Each represents a distinct business concept. Tables connected via keys - CustomerID in Orders table, ProductID linking to Products table