csis1/16wk-week-12-14-databases.md

Weeks 12–14: Databases (Microsoft Access)

Hours: 6 (3 lectures)

---

Learning Objectives

• Explain what a database is and why databases exist
• Distinguish between flat files (spreadsheets) and relational databases
• Create a database with tables, define fields and data types
• Enter, edit, and delete records
• Create and use forms for data entry
• Build queries to retrieve specific data
• Generate reports for presenting data
• Understand basic concepts: primary keys, relationships, data types

---

Lecture 12: What Is a Database? / Creating Tables

Key Concepts

Why Databases? Start with the problem spreadsheets can't solve well:

💡 Scenario: A college needs to track students, courses, and enrollments. In a spreadsheet, you'd repeat student info (name, address, phone) on every row where they're enrolled. One student in 5 classes = 5 copies of their address. What if they move? You update 5 rows. Miss one? Data is inconsistent.

A database solves this by storing each piece of information ONCE and linking related data together.

Database Terminology

• Database: An organized collection of structured data
• Table: A collection of related records (like a spreadsheet, but smarter)
• Record (Row): One complete entry (one student, one product, one order)
• Field (Column): One attribute of a record (name, phone number, grade)
• Primary Key: A unique identifier for each record (Student ID, Order #)
  • No two records can have the same primary key
  • Usually a number or auto-generated ID

💡 Analogy: A database is like a well-organized filing system. Each drawer (table) holds a type of document. Each folder (record) is one item. Each tab in the folder (field) is one piece of info. The label on the folder (primary key) is unique — no two folders have the same label.

Flat File vs Relational Database

Feature	Spreadsheet (Flat File)	Database (Relational)
Data stored	All in one table	Multiple linked tables
Redundancy	High (data repeated)	Low (data stored once)
Data integrity	Error-prone	Enforced by rules
Good for	Small, simple datasets	Large, complex, shared data
Example	Personal budget	College enrollment system

Data Types Every field has a data type that controls what can be entered:

Data Type	What It Stores	Example
Short Text	Text up to 255 characters	Name, City, State
Long Text	Longer text (memos, notes)	Description, Comments
Number	Numeric values	Age, Quantity
Currency	Money values	Price, Salary
Date/Time	Dates and/or times	BirthDate, OrderDate
Yes/No	Boolean (true/false)	Enrolled?, Active?
AutoNumber	Auto-incrementing unique ID	StudentID, OrderID

Creating a Table in Access

1. Open Access → Blank Database → Name it
2. Table Design View: Define field names, data types, descriptions
3. Set the Primary Key (usually an AutoNumber field)
4. Save the table with a descriptive name (tblStudents, tblCourses)
5. Switch to Datasheet View to enter data

💡 Naming convention tip: Prefix table names with "tbl", queries with "qry", forms with "frm", reports with "rpt". Keeps the Navigation Pane organized.

Diagram Ideas

1. Spreadsheet vs Database — Left: spreadsheet with repeated student data across rows. Right: two linked tables (Students, Enrollments) with no repetition.
2. Database Anatomy — Visual showing Database → Tables → Records → Fields → Values.
3. Primary Key Concept — Table with highlighted primary key column; show that each value is unique.
4. Data Types Reference Card — Visual grid of each type with icon and example.

Slide Concepts

Slide	Content
1	Title: "Databases: Organized Information"
2	The Problem with Spreadsheets — duplicate data scenario
3	What Is a Database? — key terminology
4	Flat File vs Relational — comparison table
5	Tables, Records, Fields, Primary Keys — visual breakdown
6	Data Types — reference chart
7	Demo: Create a table in Design View
8	Naming Conventions — tbl, qry, frm, rpt

---

Lecture 13: Forms & Queries

Key Concepts

Relationships (brief intro)

• Tables can be related through shared fields
• Example: tblStudents has StudentID. tblEnrollments has StudentID + CourseID. The StudentID field links them.
• One-to-Many: One student → many enrollments. One course → many enrollments.
• Relationships enforce referential integrity — can't enroll a student that doesn't exist.

💡 Visual: Draw two tables on the board with a line connecting the shared field. This is the foundation of relational thinking.

Forms

• A user-friendly interface for entering and viewing data (instead of raw table view)
• Create → Form: Auto-generates a form from a table
• Form Design View: Customize layout, add labels, controls, colors
• Forms can include:
  • Text boxes (bound to fields)
  • Drop-down lists (combo boxes) — limit input to valid choices
  • Buttons (for navigation, saving)
• Why forms? Reduce data entry errors, look professional, can hide complexity.

💡 Teaching idea: Show data entry in Datasheet View vs a Form. The form is immediately more intuitive and less error-prone. "This is what real database applications look like."

Queries Queries ask questions of your data. They're the power of databases.

• Select Query: Retrieve specific records and fields
  • "Show me all students from Gilroy"
  • "Show me all orders over $100"
• Query Design View: Drag fields, set criteria, choose sort order
• Criteria Operators:
  • = "Gilroy" — exact match
  • > 100 — greater than
  • Like "S*" — starts with S (wildcard)
  • Between #1/1/2024# And #12/31/2024# — date range
  • Is Not Null — field is not empty
• Calculated Fields: Create new fields in queries
  • Example: TotalPrice: [Quantity]*[UnitPrice]
• Sorting: A→Z, Z→A, smallest→largest within the query
• Multiple criteria: AND (same row in QBE grid) vs OR (different rows)

💡 Demo: Using the student/enrollment tables, query: "Show me all students enrolled in CSIS 1 who live in Gilroy." Build it step by step in Design View.

Diagram Ideas

1. One-to-Many Relationship — tblStudents (one) linked to tblEnrollments (many) with a line and 1→∞ symbols.
2. Form vs Datasheet — Side-by-side: messy datasheet view vs clean, labeled form.
3. Query Design View Explained — Annotated screenshot: field row, table row, sort row, show checkbox, criteria row.
4. AND vs OR in Criteria — Visual: two conditions on same row = both must be true (AND). Two conditions on different rows = either can be true (OR).

Slide Concepts

Slide	Content
1	Title: "Asking Questions of Your Data"
2	Relationships — linking tables, one-to-many
3	Forms — why they exist, auto-generate demo
4	Form vs Datasheet — side-by-side
5	What Is a Query? — asking questions
6	Query Criteria — operators and examples
7	Wildcards and Ranges — Like, Between
8	Calculated Fields — building expressions
9	Demo: Multi-criteria query

---

Lecture 14: Reports & Database Review

Key Concepts

Reports

• Formatted, printable output from tables or queries
• Create → Report: Auto-generates from selected table/query
• Report Design View: Customize layout, grouping, totals
• Report sections: Report Header/Footer, Page Header/Footer, Detail (repeated per record), Group Header/Footer
• Grouping: Organize records by a field (group by city, by course, by date)
• Summary calculations: Sum, Count, Average in group footers or report footer

💡 Example: Generate a report of all students grouped by major, with a count of students per major and a grand total at the bottom.

Putting It All Together The database workflow:

1. Design tables with proper fields, data types, and primary keys
2. Define relationships between tables
3. Enter data via forms (user-friendly)
4. Ask questions with queries (retrieve/analyze)
5. Present results with reports (formatted output)

💡 Teaching idea: Walk through a complete mini-project: a small business inventory database. Create tables (Products, Categories, Orders), enter data via forms, query for low-stock items, generate an inventory report.

Data Integrity Best Practices

• Always use a primary key
• Use appropriate data types (don't store phone numbers as Number — use Text to preserve leading zeros and formatting)
• Use input masks for formatted fields (phone: (###) ###-####)
• Set required fields where data must be entered
• Use validation rules (e.g., grade must be between 0 and 100)
• Use lookup fields / combo boxes to limit choices to valid entries

Diagram Ideas

1. Database Workflow — Flow diagram: Design Tables → Define Relationships → Enter Data (Forms) → Query Data → Generate Reports.
2. Report Anatomy — Labeled report showing header, page header, group header, detail rows, group footer with subtotal, report footer with grand total.
3. Complete Database Example — Entity diagram showing 3 tables with relationships (e.g., Customers → Orders → Products).

Slide Concepts

Slide	Content
1	Title: "Reports & Big Picture"
2	What Are Reports? — formatted printable output
3	Creating a Report — auto-generate and customize
4	Grouping & Totals — organizing report data
5	The Database Workflow — design → forms → queries → reports
6	Data Integrity — rules and best practices
7	Phone Numbers as Text — a common gotcha
8	Review: Databases vs Spreadsheets — when to use each

---

Vocabulary

Term	Definition
Database	An organized collection of structured data, typically stored and accessed electronically
Table	A structure that stores data in rows (records) and columns (fields)
Record	A single row in a table; one complete entry
Field	A single column in a table; one attribute of a record
Primary Key	A unique identifier for each record in a table
Data Type	The kind of data a field can hold (Text, Number, Date, etc.)
Relational Database	A database where tables are connected through shared fields (relationships)
Relationship	A connection between two tables based on a common field
One-to-Many	A relationship where one record in Table A relates to multiple records in Table B
Referential Integrity	A rule ensuring relationships between tables remain consistent
Form	A user-friendly interface for entering and viewing data in a table
Query	A request for specific data from a database; retrieves records matching criteria
Criteria	Conditions that filter which records a query returns
Wildcard	A character (like *) used in criteria to match any sequence of characters
Calculated Field	A field in a query that computes a value from other fields
Report	A formatted, printable presentation of data from tables or queries
Grouping	Organizing report records by the values in a specified field
Flat File	A database consisting of a single table (like a spreadsheet)
Input Mask	A pattern that controls how data is entered in a field (e.g., phone number format)
Validation Rule	A condition that data must meet to be accepted into a field
AutoNumber	A data type that automatically generates a unique sequential number
Combo Box	A form control that provides a dropdown list of valid choices

---

Activities & Assignments

In-Class

1. Table Design Exercise: Given a scenario (library, pizza shop, or student club), design the tables: identify fields, choose data types, select primary keys, and identify relationships.
2. Query Challenge: Provide a populated database. Students answer 10 questions by building queries (e.g., "Which products cost more than $50?" "How many customers are from California?").
3. Form & Report Walkthrough: Build a form and report together from the same data.

Homework / Projects

1. Mini-Database Project: Design and build a small database for a chosen scenario (personal media collection, recipe book, fitness tracker, small business). Requirements:

   • At least 2 related tables
   • 10+ records per table
   • 1 form for data entry
   • 2 queries (one with criteria, one with a calculated field)
   • 1 grouped report with totals
   • Brief write-up explaining the design decisions

2. Real-World Database Hunt (1 page): Identify 3 databases you interact with in daily life (banking, social media, school enrollment, online shopping). For each: what tables might it have? What would the primary keys be? How are they related?

---

Discussion Questions

1. Your boss asks you to track inventory in a spreadsheet. At what point should you move to a database?
2. Why do we need primary keys? What happens if two records have identical information in every field?
3. A database stores your health records, purchase history, and location data. What are the benefits and risks?
4. Why is it a mistake to store a phone number as a Number data type?