Lecture 6

Data Types, Databases, and Keys

Byeong-Hak Choe

bchoe@geneseo.edu

SUNY Geneseo

October 7, 2026

🗂️ Understanding Different Types of Data

🧭 Start with the data and the question

• Suppose a retailer wants to decide which products to stock next month.
• Its evidence may include sales tables, customer reviews, and product photos.
• Before analysis, ask:
  • What does one row represent?
  • What does each variable mean?
  • How can information from different sources be connected?

Discuss: Which source would help you count purchases? Which could help explain why customers liked a product?

🗃️ Data can be organized in different ways

Structured

Defined rows and columns.

Example: an order table with an ID, date, and total.

·

Semi-structured

Labels or tags organize information, but records can contain different fields.

Example: one app record has a phone-number field, while another does not.

·

Unstructured

The main content does not follow a fixed table layout.

Example: review text, photos, or videos.

• A review can have a structured date and rating alongside unstructured text.
• To analyze text or images in a table, we first identify useful features, such as a topic or image category.

Different fields means different sets of labeled information can be present in the records. A fixed table with a blank phone-number cell is still structured: its shared phone-number column remains.

🏷️ A variable’s meaning determines useful comparisons

Kind of variable	What the values mean	Useful comparison
Nominal	Categories without a ranking	Same or different
Ordinal	Categories with a ranking	Higher or lower
Interval	Equal differences; no meaningful zero	How much higher or lower
Ratio	Equal differences and a meaningful zero	How much; how many times as much

• These describe measurement, not R’s storage types such as character, numeric, or integer.
• A column of numbers may contain quantities, ratings, or ID labels. Read its definition before doing arithmetic.

🏷️ Nominal data names categories

Example: preferred shopping channel

Customer	Channel
C01	Website
C02	Store
C03	App

• Nominal data puts observations into categories with no natural ranking.
• Website, store, and app are different channels; none is automatically “higher.”
• We can count customers in each category, but we cannot calculate an average channel.

Discuss: If we label these channels 1, 2, and 3, does that make “average channel = 2” meaningful?

🪜 Ordinal data has an order

Example: customer satisfaction

Response	Order
Dissatisfied	Lower
Neutral	Middle
Satisfied	Higher

• Ordinal data has categories we can rank.
• The order is meaningful, but equal gaps between categories are not guaranteed.
• Coding responses as 1, 2, and 3 preserves their order; it does not prove that each step represents the same change in satisfaction.

Discuss: On a 1–5 satisfaction scale, does 4 mean “twice as satisfied” as 2?

🌡️ Interval data supports differences

Example: temperature

Day	Temperature (°F)
Monday	70
Tuesday	80
Wednesday	90

• Interval data has equal-sized measurement steps.
• A change from 70°F to 80°F and from 80°F to 90°F is the same 10°F increase.
• But 0°F does not mean “no temperature.” So 80°F is not twice as hot as 40°F.

Check: Which comparison works here—“10°F warmer” or “twice as hot”?

Clock time is a less straightforward example: 23:00 to 01:00 crosses midnight. A numerical rating is not automatically interval; equal gaps require an assumption about what the scale measures.

⏱️ Ratio data has a meaningful zero

Example: time spent shopping

Customer	Minutes
C01	0
C02	15
C03	30

• Ratio data supports meaningful differences and ratios.
• Zero minutes means no time spent shopping during the measured period.
• Thirty minutes is 15 minutes longer than 15 minutes, and twice as long.
• Counts, durations, and distances are common examples.

🔢 Numbers do not always measure quantities

Variable	Example	How to interpret it
Customer ID	101, 102, 103	Labels identifying people
Satisfaction score	1–5	Ordered responses; gaps may differ
Number of orders	0, 1, 2, 3	A count with a meaningful zero

• Context matters: “101” can be an ID, a count, or a measurement.
• A clock time wraps around at midnight; a rating scale may not have equal gaps.

Discuss: Why would an average customer ID be unhelpful, even if R can calculate it?

🧪 Classwork 8: Taxonomy of Data

Classwork 8 · Taxonomy of Data →

Categories and rankings Differences and ratios Interpret values in context

• Use each variable’s description, not just its appearance, to choose a measurement scale.
• Be ready to explain why your comparison makes sense.

🗄️ Databases, Tables, and Keys

🗄️ A database stores data; software manages it

• A database (DB) is an organized collection of data stored electronically.
• A database management system (DBMS) is software that manages that data.
  • It supports storage, updates, access permissions, and retrieving information.
  • PostgreSQL and MySQL are examples.
• A query is a request for data—for example, “show orders from Rochester.”

Note

SQL (Structured Query Language) is commonly used to query database tables. In this course, we use R to practice working with tables.

Reference: IBM · Relational databases

📁 Where data is stored vs. where we analyze it

• In our class: We imported custdata_rev.csv into R as custdata and transformed it with dplyr. We have not used a database yet.

Item	Role	Example
CSV file	Stores one table as plain text	custdata_rev.csv
Database	Stores organized data, often in related tables	A retailer’s customers and orders
R data frame	Holds a table in R for analysis	custdata

• Retailer example: R could retrieve data directly from a database managed by PostgreSQL into a data frame.
• Changing an R data frame does not automatically update its CSV file or database source.

Our class downloaded custdata_rev.csv, imported it into R as custdata, and used dplyr to transform the data frame. Reading a CSV into R does not mean we have used a database.

CSV files and databases are alternative sources of stored data for our R workflow. A CSV saves one table as plain text. A database stores organized data, potentially across several related tables. PostgreSQL is a DBMS, the software that manages a database.

The retailer is a hypothetical example. Instead of downloading a CSV export, we could connect R to its database and retrieve selected data into an R data frame for analysis. No database connection or SQL code is required for this lesson.

Changes to that R object do not automatically write back to the source. To retain a transformed result outside the session, we must explicitly save or export it. Saving the R script records the instructions, rather than the resulting data.

📐 A schema describes a table’s structure

• A schema specifies the columns, their data types, and rules for the stored data.
  • A database schema helps prevent structurally invalid data.
• For our retailer’s orders, we could define:

Column	Meaning	Example rule
order_id	Identifies an order	Required and unique
customer_id	Identifies the customer	Uses the agreed ID format
total	Order amount in dollars	Numeric; unit documented

Discuss: A new order repeats an existing order_id and stores total as the text "twenty-five". Which two schema rules does it break?

Give pairs 30 seconds. The order_id must be unique, so the new order cannot reuse another order’s ID. The total must be numeric, so the text “twenty-five” does not fit its specified data type. A schema defines rules to check before adding a record to the table.

🧩 Related tables store different kinds of records

• A relational database organizes data as tables with rows and columns. Shared identifiers can connect information across tables.
• First, define what one row represents.

orders: one row per order

order_id	customer_id	total
101	C01	25
102	C02	40
103	C01	15
104	C03	30

customers: one row per customer

customer_id	city
C01	Rochester
C02	Buffalo
C04	Albany

Discuss: Which column could connect an order to its customer’s city?

All retailer data in this lecture are fictional. Notice that customers and orders have different units of observation. Keeping customer details in their own table avoids repeatedly storing the same details for every order.

🔑 A key variable connects records between tables

• A key variable is a column used to identify records or match records between tables.
• In our example, the shared key variable is customer_id.

In orders

customer_id tells us which customer placed each order.

C01 appears in two orders because the same customer ordered twice.

In customers

customer_id identifies the customer whose city is recorded.

C01 matches the customer record for Rochester.

• To connect the tables, match the same customer ID, rather than matching row positions.

Key values must refer to the same kind of record in both tables. The customer ID can repeat in the orders table because one customer can place multiple orders. These practice data deliberately include C03 without a customer match to demonstrate the left join later.

🛠️ Create the practice tables in R

library(dplyr)

orders <- data.frame(
  order_id = c(101, 102,
               103, 104),
  customer_id = c("C01", "C02",
                  "C01", "C03"),
  total = c(25, 40, 15, 30)
)

customers <- data.frame(
  customer_id = c("C01", "C02",
                  "C04"),
  city = c("Rochester",
           "Buffalo", "Albany")
)

• orders is our main table; we want to keep its orders.
• customers supplies the city to attach to each matching order.
• The tables can have different row counts and row orders. We match ID values, not row positions.

🔗 A left join attaches matching information

orders_with_city <- orders |>
  left_join(customers, by = "customer_id")

• Start with orders, the table on the left.
• by = "customer_id" tells R which column’s values to match.
• left_join() keeps every order and adds city from matching customer records.
• If there is no match, the added value is NA.

🧾 Read the join result one order at a time

order_id	customer_id	total	city
101	C01	25	Rochester
102	C02	40	Buffalo
103	C01	15	Rochester
104	C03	30	NA

• Orders 101 and 103 both match C01 and receive Rochester.
• Order 104 remains, but its city is NA because C03 has no customer match.

Check: Why does C04’s city, Albany, not appear in this result?

C04 has no order in the main table. A left join adds matches to the left table; it does not add a new row just because the reference table has an unused key. NA is missing information, not zero and not proof that the customer has no city.

✅ Check a join before using its result

1. Meaning: Does the matching key identify the same thing in both tables?
2. Matches: Are IDs spelled and stored consistently? Which orders have no match?
3. Row count: Does each order match at most one customer record? Did the join add unexpected rows?

Predict: If we start with customers instead, which records would we be trying to keep?

The answer is customers, including C04. Reversing the tables changes the question and may produce a different result. Keep the main table aligned with the intended unit of analysis.

🧪 Classwork 9: Combining Tables with left_join()

Classwork 9 · Combining Tables with left_join() →

Main and lookup tables Matching key left_join()

• Practice matching students to majors across two tables.
• Identify the matching key, then complete and run the left_join() code.