Lesson 1, Topic 1

In Progress

Lesson 1.1 – Flat File, Hierarchical and Relational Databases

Lesson 1.1 – Flat File, Hierarchical & Relational Databases (T-SQL)

Learning Objectives

By the end of this lesson, you will be able to:

Define what a database is and explain why databases are useful for organizing information.
Differentiate between flat file, hierarchical, and relational database models.
Identify real-world examples of each type of database and discuss their advantages and limitations.
Understand where Microsoft SQL Server and T-SQL fit in the database world.
Launch the Knowledge Pillars Coding Lab and run your very first T-SQL command.

📚 Key Vocabulary

Term	Definition	SchoolHub Example
Database	An organized collection of data stored in a computer system so it can be easily accessed and managed.	SchoolHub stores student, teacher, class, and enrollment information together.
DBMS	Database Management System — the software that runs databases. SQL Server is a DBMS.	Microsoft SQL Server hosts the SchoolHub database and runs your T-SQL.
Flat File Database	A simple model storing all data in a single table or file, with no formal links between records.	A single spreadsheet with every student's name, grade, class, and teacher in one row each.
Hierarchical Database	A tree-like model where each parent record can have many children, but each child has one parent.	School → Grade → Class → Student, like nested folders.
Relational Database	A model that organizes data into multiple tables and links them through keys.	SchoolHub has separate `Students`, `Classes`, and `Teachers` tables linked by ID.
Table (Relation)	A grid of rows (records) and columns (fields) that stores data about one kind of thing.	The `Students` table holds one row per student.
Data Redundancy	Unnecessary duplication of the same data in multiple places.	The same teacher's name written in 30 student records — change it once, miss the others.
SQL	Structured Query Language — the standard language for talking to relational databases.	`SELECT * FROM Students;` asks the database for every student.
T-SQL	Transact-SQL — Microsoft's dialect of SQL used by SQL Server. Adds variables, transactions, error handling, and more on top of standard SQL.	Every query you write in this course is T-SQL.

💡 Real-World Analogy

Imagine you've been put in charge of your school's student records. You need to store each student's information — name, grade, classes, locker number — in an organized way. There are three very different approaches you could take:

One Big List

Flat File — every student in one giant spreadsheet. Simple but messy, with the same info repeated everywhere.

A Tree of Folders

Hierarchical — School holds Grades, Grades hold Classes, Classes hold Students. Tidy, but rigid.

Linked Tables

Relational — separate lists for Students, Classes, Teachers, linked by ID numbers. Clean, flexible, the modern standard.

🎓 SchoolHub connection: By the end of this unit you'll have chosen the relational model and built the first design of SchoolHub in SQL Server. Every later unit builds on that foundation — so get comfortable with these three models now.

🧱 Concept Explanation

What is a database?

A database is just an organized collection of data. Instead of loose papers or random files, think of it as a structured way to store information so a computer can quickly find, add, change, or remove any piece of it. The software that runs the database is called a DBMS (Database Management System). The DBMS you'll use in this course is Microsoft SQL Server, and the language you'll use to talk to it is called T-SQL (Transact-SQL).

The way data is structured inside a database is called a database model. Three classic models are flat file, hierarchical, and relational — let's look at each one.

1. Flat File Database

The simplest model. All data lives in a single table, often a plain text file or spreadsheet. Each row is one record; each column is one field.

students.csv (flat file)

# StudentName, Grade, Class, Teacher
Alice, 10, Math,    Mr. Smith
Bob,   10, Math,    Mr. Smith
Carol, 10, Math,    Mr. Smith
Dan,   10, History, Ms. Lee
Alice, 10, History, Ms. Lee
Bob,   10, History, Ms. Lee

See the problem? "Mr. Smith" and "Math" appear over and over. That's data redundancy. If Mr. Smith changes his name, you have to fix it everywhere. If you miss one, your data is suddenly inconsistent.

Good for: very small, simple lists — your personal contacts, a single sign-up sheet.
Bad for: anything that grows or has lots of relationships between pieces of data.

2. Hierarchical Database

Data is organized in a tree: each parent record can have many children, and each child has exactly one parent. Like an organizational chart or the folders on your computer.

School
├── 10th Grade
│   ├── Math (Mr. Smith)
│   │   ├── Alice
│   │   └── Bob
│   └── History (Ms. Lee)
│       ├── Alice
│       └── Dan
└── 11th Grade
    └── Biology (Mr. Brown)
        └── Carol

Notice Alice has to appear twice — once under Math, once under History. The hierarchical model struggles when something belongs to more than one parent, which happens all the time in real life (students take multiple classes, books have multiple authors, products belong to multiple categories).

Good for: data that naturally fits a tree — file systems, XML documents, the Windows Registry.
Bad for: complex relationships where one record needs to relate to many others in many ways.

3. Relational Database

The big winner — used by virtually every modern app. Data is split into multiple tables, and tables are linked together using keys (unique IDs). Each thing — student, teacher, class — gets its own table. No more repetition.

Students table
StudentID | Name
1         | Alice
2         | Bob
3         | Carol
 
Classes table
ClassID | ClassName | TeacherID
101     | Math      | 10
102     | History   | 11
 
Teachers table
TeacherID | Name
10        | Mr. Smith
11        | Ms. Lee
 
Enrollments table (the link)
StudentID | ClassID
1         | 101
1         | 102
2         | 101
3         | 102

Now each fact lives in exactly one place. Mr. Smith's name is in the Teachers table, once. Math's details are in Classes, once. Need to know who's in Math? Look up ClassID 101 in Enrollments and you get the list. Need Math's teacher? Follow TeacherID 10 to Teachers. The data is connected through these key references, like a web of post-it notes pointing to each other.

Good for: just about everything modern — school systems, online stores, banking, social networks, payroll, healthcare.

Where does SQL Server fit?

Microsoft SQL Server is a relational DBMS — one of the most widely used in the business world. It's the engine that runs the database. To talk to it, you use a language called T-SQL (Transact-SQL). T-SQL is Microsoft's enhanced version of the SQL standard, with extra features for variables, transactions, error handling, and more. You'll use T-SQL for every query in this course.

Other relational DBMSs you may have heard of include Oracle, MySQL, PostgreSQL, and SQLite. The core SQL syntax is similar across all of them — but T-SQL is the specific dialect that SQL Server speaks.

Quick side-by-side

Model	Structure	Strengths	Weaknesses
Flat File	One table	Super simple; easy to view in Excel.	Lots of duplicate data; hard to update.
Hierarchical	Tree	Fast for one-to-many; clear parent-child.	Can't handle one item belonging to many parents.
Relational	Multiple linked tables	Minimal redundancy; flexible queries; industry standard.	Needs upfront design; queries can get complex.

🎯 The bottom line: Almost every database you'll work with in school, at home, or at a job is relational. That's why this course — and every following unit — focuses on the relational model and the language used to talk to SQL Server: T-SQL.

✍️ Best Practices

🎯 Pick the Right Model

Tiny personal list → flat file. Tree-shaped data → hierarchical. Real-world app → relational. Don't over-engineer a grocery list.

🚫 Avoid Duplication

If the same fact appears twice, you've created two places where it can disagree. Store each piece of data once.

📐 Plan Before You Build

Sketch your tables and how they connect before typing T-SQL. A whiteboard or napkin saves hours of rework.

🧷 Keep It Simple

Use as many tables as you need — and no more. Splitting data is a means to clarity, not a goal by itself.

🏷️ Name Things Clearly

A table called Students beats one called tbl_data1. Your future self will thank you.

💭 Think in Entities

Ask: "What things am I tracking?" Each answer (student, teacher, class) usually becomes its own table.

🧪 Hands-On Practice

Warm-Up

Spot the Database Model

1. Identify the Model

For each scenario, decide whether it's a flat file, hierarchical, or relational database, and give a one-sentence reason.

A single Excel spreadsheet stores the contact information of 200 club members, with columns for name, phone, email, and address.
A company's internal phonebook is stored in an XML file where Departments contain Teams, and Teams contain Employees nested inside.
A university uses Microsoft SQL Server to manage student data, with separate tables for Students, Courses, and Enrollments, linked by IDs.
The folders on your laptop, with Documents containing School, which contains Math, which contains assignments.

2. Find the Redundancy

Look at this flat file. Circle (or list) every piece of information that is duplicated. How many distinct teachers are there really? How many distinct classes?

enrollments.csv

StudentName, Class,   Teacher,    TeacherEmail
Alice,       Math,    Mr. Smith,  smith@school.edu
Bob,         Math,    Mr. Smith,  smith@school.edu
Carol,       Math,    Mr. Smith,  smith@school.edu
Alice,       History, Ms. Lee,    lee@school.edu
Bob,         History, Ms. Lee,    lee@school.edu
Dan,         Science, Mr. Doe,    doe@school.edu

3. Real Examples

For each model, name a real system you've used or heard of:

Flat file: (Hint: a list you keep in one document.)
Hierarchical: (Hint: anything that looks like a tree.)
Relational: (Hint: any big app with a login — probably running on SQL Server, Oracle, or MySQL.)

4. Thought Experiment

If your school wanted to track which courses each student takes, sketch how you'd handle it in each of the three models. What goes wrong with flat file when a student takes 5 classes? What goes wrong with hierarchical when a student is in 3 clubs and the school orchestra?

💻 Coding Lab — Activity 1.1

Coding Challenge

Run Your First T-SQL Command

Time to type T-SQL. We won't be writing complex queries yet — that's for later units. The goal here is to open the Coding Lab, create a file, run a tiny T-SQL command on SQL Server, and prove your environment works. By the end you'll have officially executed your first line of T-SQL.

Open the Knowledge Pillars Coding Lab. It should open as a SQL Server query editor with a results pane below.
Create a new file named lesson1_1.sql. The .sql extension tells the editor this is a SQL script.
Click into the new file and type the T-SQL command shown below. Don't copy-paste — typing builds muscle memory.
Click the Execute button (or press F5, the SQL Server keyboard shortcut). The lab will run your command and display the result in the grid below.
Confirm the output matches the expected result.
Experiment: change the text inside the quotes, try SELECT 2 + 3;, try SELECT GETDATE(); (a built-in T-SQL function), and re-run to see how the output changes.
Save your file as lesson1_1_(FirstName_LastName).sql.

lesson1_1.sql

-- My very first T-SQL command!
-- SELECT asks SQL Server to return something.
-- Here we're just asking it to return a piece of text.
 
SELECT 'Hello, T-SQL!';

Expected Output

+-----------------+ | (No column name)| +-----------------+ | Hello, T-SQL! | +-----------------+ (1 row affected)

🔑 What just happened? SELECT is the T-SQL keyword for "give me this." The semicolon ; tells SQL Server "that's the end of my command." Almost every T-SQL statement you'll write follows this pattern: a keyword, what you want, and a semicolon. (Older SQL Server code sometimes leaves off the semicolon — but using it is a best practice that the certification expects.)

⚠️ Common gotchas if your command won't run: a missing ; at the end · using "smart quotes" (‘ ’) instead of plain quotes (' ') · spelling SELECT wrong · forgetting to click Execute · the Coding Lab not yet connected to SQL Server. Read the red error message — SQL Server tells you the exact line and what went wrong.

🔁 Step 8 — Experiment & Modify

Try SELECT 'My name is your-name'; — replace with your actual name.
Try SELECT 2 + 3; — yes, T-SQL can do math.
Try SELECT 'Hello', 'T-SQL', '!'; — what happens when you SELECT multiple things?
Try SELECT GETDATE(); — this is a built-in T-SQL function that returns the current date and time.
Try forgetting the semicolon. What does SQL Server's error look like? (Bonus: read the error message carefully — it tells you exactly what it's expecting.)

✅ Self-Check

I successfully launched the Knowledge Pillars Coding Lab.

I created a new file named lesson1_1.sql.

I typed (not pasted) the SELECT 'Hello, T-SQL!'; command.

I executed the query and saw Hello, T-SQL! in the output.

I experimented by changing the text, trying SELECT 2 + 3;, and running SELECT GETDATE();.

I saved the file as lesson1_1_(FirstName_LastName).sql.

0 of 6 complete

🎤 Questions to Think About

Discuss with your class or jot down your answers:

Why do you think the relational model became so dominant, given that the hierarchical model came first?
Can you think of a situation where a flat file is actually the best choice — better than a relational database?
Your phone's contacts app: is that more like a flat file, a hierarchical database, or a relational database? Why?
What kinds of mistakes might happen if a hospital used a flat file to store all patient records?
Why might a company specifically choose SQL Server over other relational databases like MySQL or PostgreSQL?