Relational database basics in Intro to Computing - Real World Applications

Real World Mode - Relational database basics

Relational Database Basics Analogy

Imagine a large library where books are organized on shelves. Each shelf holds books of a certain category, like history, science, or fiction. Each book has a title, author, and publication year. The library uses a card catalog system where each card lists details about a book and points to its location on the shelves. This system helps librarians quickly find any book by looking up information on the cards.

A relational database works like this library. The shelves are like tables, each holding rows of data (books). Each row has columns (title, author, year) representing attributes. The card catalog is like the relationships between tables, helping connect data across different tables to find exactly what you need.

Mapping Table: Relational Database to Library

Computing Concept	Real-World Equivalent	Description
Table	Shelf	A shelf holds books of a specific category, just like a table holds rows of related data.
Row (Record)	Book	Each book on a shelf is a single item with details, like a row with data fields.
Column (Field)	Book attribute (title, author, year)	Each attribute describes a property of the book, similar to columns describing data fields.
Primary Key	Unique book ID or catalog number	A unique identifier for each book to distinguish it from others.
Foreign Key	Reference card linking to another book	A card that points to a related book or category on another shelf, connecting data across tables.
Relationship	Link between cards and shelves	Connections that help find related books across different shelves.
Query	Asking the librarian to find books	Requesting specific information by searching the catalog or shelves.

A Day in the Library: Using the Analogy

Suppose you want to find all science books written by a certain author. You go to the card catalog and look up the author's name. The cards tell you which shelves have those books and their unique IDs. You then go to the science shelf and find the books by matching the IDs. If you want to know which books are also available in the history section, you check the reference cards that link books across shelves. This process is like running a query in a relational database, where tables are searched and linked to get the exact data you want.

Limits of the Analogy

• The library shelves are physical and fixed, while database tables are digital and can be changed easily.
• Books are large and tangible, but database rows are just data entries without physical form.
• The card catalog is slower and manual, whereas database queries are automated and very fast.
• Relationships in databases can be complex with many-to-many links, which are harder to visualize with simple cards and shelves.

Self-Check Question

In our library analogy, what would the foreign key be equivalent to?

Key Result

A relational database is like a library with shelves (tables), books (rows), and a card catalog linking related books (relationships).

Practice

1. What is the main purpose of a relational database?

easy

A. To run computer programs

B. To store data as plain text files

C. To organize data into tables with rows and columns

D. To create graphics and charts

Solution

1. Step 1: Understand the structure of relational databases

   Relational databases store data in tables made of rows and columns, similar to a spreadsheet.

2. Step 2: Compare options with this structure

   Only 'To organize data into tables with rows and columns' matches the core purpose of relational databases.

3. Final Answer:

   To organize data into tables with rows and columns -> Option C

4. Quick Check:

   Relational database = tables with rows and columns [OK]

Hint: Think of data like a spreadsheet with rows and columns [OK]

Common Mistakes:

• Confusing databases with file storage
• Thinking databases create graphics
• Believing databases run programs

2. Which SQL command is used to add new data into a table?

easy

A. SELECT

B. INSERT

C. CREATE

D. DELETE

Solution

1. Step 1: Recall SQL commands and their purposes

   SELECT retrieves data, CREATE makes tables, DELETE removes data, and INSERT adds new data.

2. Step 2: Match the command to adding data

   INSERT is the command used to add new rows of data into a table.

3. Final Answer:

   INSERT -> Option B

4. Quick Check:

   Adding data = INSERT [OK]

Hint: INSERT means putting new data inside [OK]

Common Mistakes:

• Using SELECT to add data
• Confusing CREATE with INSERT
• Thinking DELETE adds data

3. Given the table Students with columns ID, Name, and Age, what will this SQL query return?

SELECT Name FROM Students WHERE Age > 20;

medium

A. All student names where age is greater than 20

B. All student IDs where age is greater than 20

C. All student ages where age is greater than 20

D. All data from the Students table

Solution

1. Step 1: Analyze the SELECT clause

   The query selects the Name column only, so the output will be student names.

2. Step 2: Analyze the WHERE condition

   The condition Age > 20 filters rows to only those students older than 20.

3. Final Answer:

   All student names where age is greater than 20 -> Option A

4. Quick Check:

   SELECT Name with Age > 20 = student names over 20 [OK]

Hint: SELECT column filters output; WHERE filters rows [OK]

Common Mistakes:

• Thinking SELECT returns all columns
• Ignoring the WHERE condition
• Confusing column names in SELECT

4. Identify the error in this SQL statement:

INSERT INTO Students (ID, Name Age) VALUES (1, 'Alice', 22);

medium

A. Missing comma between column names

B. Incorrect table name

C. VALUES keyword is misspelled

D. Extra parentheses around values

Solution

1. Step 1: Check the column list syntax

   The columns are listed as ID, Name Age without a comma between Name and Age.

2. Step 2: Confirm correct syntax for INSERT

   Column names must be separated by commas. Missing comma causes syntax error.

3. Final Answer:

   Missing comma between column names -> Option A

4. Quick Check:

   Columns need commas between names [OK]

Hint: Check commas between column names carefully [OK]

Common Mistakes:

• Overlooking missing commas
• Assuming VALUES is misspelled
• Thinking parentheses are extra

5. You have two tables: Orders(OrderID, CustomerID, Amount) and Customers(CustomerID, Name). Which SQL query correctly lists all orders with the customer names?

hard

A. SELECT OrderID, Name FROM Orders INNER JOIN Customers ON Orders.OrderID = Customers.CustomerID;

B. SELECT OrderID, Name FROM Orders, Customers WHERE Orders.OrderID = Customers.CustomerID;

C. SELECT OrderID, Name FROM Orders LEFT JOIN Customers ON Orders.Amount = Customers.CustomerID;

D. SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Solution

1. Step 1: Understand the relationship between tables

   Orders and Customers are linked by CustomerID, so join must use this key.

2. Step 2: Check each JOIN condition

   SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID; joins on Orders.CustomerID = Customers.CustomerID, which is correct. Others join on wrong columns.

3. Final Answer:

   SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID; -> Option D

4. Quick Check:

   Join tables on matching CustomerID [OK]

Hint: Join tables using matching keys (CustomerID) [OK]

Common Mistakes:

• Joining on wrong columns
• Mixing OrderID with CustomerID
• Using incorrect JOIN types