Download Script of Database Warehousing and more Schemes and Mind Maps Family and Consumer Science in PDF only on Docsity!
PART 1: THE HOOK – PAIN POINTS (30 minutes)
Opening Scenario (5 minutes) "Good evening, students. Welcome to your first day at DataCity Solutions. I'm your lead architect. Before we proceed to main topic we will discuss first, Data and Information and how are they integrated to each other.
- Data, Information, Process Knowledge
- Database, DBMS
- Data Normalization, Data Dictionary
- ERD (Introduction)
- Symbols (Rectangle, Oblong, Diamond, Line)
- Entity, Attributes
- Relationship
- Relationship Symbols
- Introduction to MS Access
- Tables, Columns, Data type
- Primary Key, Foreign Key
- Basic SQL Queries
- Reports What is Data? Data is a collection of raw, unorganized facts, figures, or symbols that, by themselves, have little or no significance or context. Think of it as the basic, isolated ingredients of a meal. Definition: Raw facts or figures without context or organization. Characteristics: Unstructured, unrefined, and usually not useful for decision-making on its own. Example: o A list of numbers: 85, 92, 78, 95 o A list of words: "Banana," "Apple," "Orange," "Mango," "Apple" o A temperature reading: 25.
What is Information? Information is data that has been processed, organized, structured, and presented within a context to make it meaningful and useful. It's the finished, cooked meal that you can actually eat and enjoy. Information helps us answer questions and make decisions. Definition: Processed data that is meaningful, relevant, and organized. Characteristics: Structured, refined, provides context, and is suitable for decision-making. Example: o The average test score for the class was $\mathbf{87.5}$ (derived from the numbers 85, 92, 78, 95). o Apple is the most popular fruit sold , with twice the sales of the next closest fruit (derived from the list of words). o The air conditioning system should be activated because the average room temperature has reached $25.5^\circ\text{C}$ (derived from the temperature reading).
Connecting Data and Information
Data is the input, and Information is the output. The connection
between them is a transformation process.
Imagine a ladder:
1. Bottom Rung: Data (Raw facts)
2. Middle Rung: Information (Processed, meaningful data)
3. Top Rung: Knowledge (Understanding and applying information)
Information depends entirely on data—you can't have information without raw data, just like you can't have a cake without ingredients. How Data Transforms into Information The transformation from raw Data to meaningful Information happens through a process often called Data Processing or Data Transformation.
Tool: Students receive a messy Excel file with 10,000 rows of simulated sales data (no consistent formatting, duplicates, missing dates) Timer: 5 minutes to find answers Debrief: "How did that feel? What frustrated you? What would help?"
Transition to Real-World (10 minutes)
Show actual statistics: "Jollibee processes 2 million transactions daily" "BDO handles 15TB of new banking data monthly" "When data is messy, it costs Philippine businesses ₱4.2 billion annually in inefficiencies" Key Question: "If finding one file on your laptop is frustrating, imagine finding one transaction among billions?"
Relationship to BSMA/BSAIS Programs
For future Management Accountants (BSMA) and Information System Auditors (BSAIS), data warehousing is the central nervous system of modern business intelligence. It transforms raw transactional data into structured, historical information that enables: BSMA: Strategic financial analysis, cost behavior tracking, performance measurement, and predictive budgeting BSAIS: Audit trail maintenance, compliance monitoring, fraud detection, and information systems control assessment Think of data warehousing as building a corporate library where, instead of books, you organize years of business transactions so professionals can find patterns, tell financial stories, and predict future chapters. History and Introduction of DBMS (The Operational System) The Database Management System (DBMS) is the software that allows users to create, maintain, and interact with a database. It's the engine for your day-to-day business.
Evolution: Data handling moved from simple file-based storage (prone to errors and duplication) to sophisticated systems. The major breakthrough was the introduction of the Relational Model (pioneered by E.F. Codd in 1970). This model organizes data into tables (relations) with strict rules, using Primary Keys and Foreign Keys to link them. A Database Management System (DBMS) is software that helps you create, organize, and control access to a database. It makes storing, updating, and retrieving data efficient and reliable, allowing many users or programs to work with the same information without causing confusion or loss. Example: MySQL, PostgreSQL, Oracle, and Microsoft SQL Server are popular DBMSs. Analogy: Think of a DBMS as the librarian in a large library. The librarian doesn’t just store books (data); they organize the shelves, help you find what you need, keep track of who borrows what, and make sure nothing gets lost or mixed up. What is a Database? A database is an organized collection of information that is stored and managed so it can be easily accessed, updated, and analyzed by computers. Common examples include customer lists for a business or student records for a school. For instance, an online store keeps a database of all customer orders and product inventory. Analogy: Think of a database as a giant digital filing cabinet. Each drawer (table) contains folders (rows) with information organized by category (columns). This makes it easy to quickly find, update, or analyze information as needed. Who Edgar F. Codd was Edgar Frank Codd (1923–2003) was trained as a mathematician and worked at IBM in the 1960s and 1970s. In his 1970 paper “A Relational Model of Data for Large Shared Data Banks,” he proposed storing data in relations (tables) that can be queried using formal logic instead of navigating complex pointer structures. His work led directly to relational database management systems (RDBMS) and inspired SQL as the dominant language for querying and managing data.
Atomicity: Ensuring a transaction either fully completes or doesn't happen at all. Example: Bank transfer
Imagine a transaction to transfer $500 from John's account to Tom's
account. This requires two steps:
1. Debit $500 from John's account.
2. Credit $500 to Tom's account.
With atomicity :
1. If the debit is successful but the credit fails (e.g., due to a system
error), the transaction is rolled back, and John's account is not
debited. The database returns to its original state.
2. If both the debit and credit operations are successful, the
transaction is committed, and the new balances are saved.
Importance of atomicity
Ensures data integrity : It prevents the database from being left in
an inconsistent or corrupted state where only part of a transaction has
been applied.
Maintains data consistency : By ensuring transactions are
indivisible, atomicity prevents logical errors that could arise if, for
example, money was deducted from one account but not added to
another.
Provides reliability : It guarantees that a transaction is either fully
completed or not completed at all, making transactions dependable.
PART 2: HISTORY & INTRODUCTION TO DBMS (60 minutes) 1960s-1970s: The Paper Crisis
Storytelling: "NASA's Apollo missions generated 2,000 miles of printed data. When they needed specific telemetry, technicians literally ran through hallways of filing cabinets." Relatable Analogy: "Like your old family photo albums – no search function, chronological only, duplicates everywhere." 1970s: The First Databases – Hierarchical & Network Models Real Philippine Example: PLDT Telephone Service (1980s) What Happened: You move from Quezon City to Makati Old PLDT System Reaction: QC Office: Keeps your old record (marked inactive but still there) Makati Office: Creates BRAND NEW customer record for you Result: Two "yous" in the system Consequences: Billing Confusion: QC office might send final bill to old address Makati office starts new billing cycle You might get two bills or none Customer Service Nightmare: Call about connection problem Agent asks for account number Which one? QC account or Makati account? They see different information in each Credit History Lost: Your 5-year payment history in QC New Makati record shows you as "new customer" Good payment history doesn't transfer
"Instead of one giant spreadsheet, we create connected tables" BSMA Connection: "This is why your accounting systems have separate General Ledger, AR, AP – but they relate through account numbers" DATA WAREHOUSING A data warehouse (DW) is a centralized repository designed for analytical processing, supporting managerial decision-making by storing integrated historical data from multiple sources, unlike operational databases that handle real-time transactions for daily operations. While operational databases, or OLTP systems, focus on quick, high-volume inserts, updates, and deletes—such as recording a customer's purchase at a supermarket checkout—they prioritize current data with normalized schemas to ensure consistency and speed, answering questions like "What's the current inventory?" In contrast, data warehouses enable OLAP (Online Analytical Processing), optimizing for complex, read-heavy queries that aggregate large datasets over time, such as "How have sales trends evolved across regions over the past five years?" This separation allows businesses to analyze patterns without disrupting live operations, with DWs using denormalized structures like star schemas for faster reporting and insights into "what has happened and why," rather than "what's happening now."
OLTP and OLAP
So, OLTP is like a supermarket cashier: quick, many small tasks—scanning groceries, taking payment, updating stock. OLAP is like a business analyst: sitting with months of receipts and documents, drawing up charts and questioning “How did holiday sales compare to regular days, and why?”
Difference Between Data Warehouse and Database
A database is any structured place to store data (for apps, websites, banking systems, etc.). A data warehouse is a special kind of database built mainly for analysis and reporting, not for running day-to-day operations.
Purpose:
Database: supports daily transactions (OLTP) – e.g., placing orders, updating inventory. Data warehouse: supports analysis (OLAP) – e.g., “sales by region over 5 years.”
Analogy:
A database is like a shop’s live cash register and product list – it must be fast and always up to date. A data warehouse is like the company’s archive of all past receipts and reports – slower to update, but ideal for studying trends and making strategic decisions. --------END OF LESSON 1-------- Lesson 2 3-Tier Architecture Explained Bottom Tier (Data Warehouse Server): This is the foundation where all cleaned, transformed, historical data is stored— usually in a relational database. Data flows here from multiple operational sources via ETL/ELT (Extract, Transform, Load/Extract, Load, Transform) processes, which are responsible for collecting raw data, cleaning it, converting it into a standard format, and loading it into the warehouse so it’s ready for analysis. Analogy: Think of this like a well-organized warehouse where goods (data) from various suppliers are quality-checked, repackaged, and shelved, waiting to be picked for delivery (analysis) later. Middle Tier (OLAP Server): The analytical engine sits in this tier. It takes the prepped data from the bottom tier and organizes it into multi-dimensional structures (“data cubes”). These cubes let users quickly retrieve, filter, and aggregate large volumes of data (e.g., looking at sales by region, year, and product). OLAP servers come in types: MOLAP: Stores cubes in a special database for very quick responses. ROLAP: Uses the regular relational database for analysis. HOLAP: Combines both approaches. Analogy: Imagine a chef prepping ingredients (data) into handy bowls for each dish (query)—so when it’s time to cook (analyze), everything is already sliced and ready. Top Tier (Front-end Client):
- Query Language: DBMS provides users with a simple Query language, using which data can be easily fetched, inserted, deleted and updated in a database.
- Security: The DBMS also takes care of the security of data, protecting the data from un-authorised access. In a typical DBMS, we can create user accounts with different access permissions, using which we can easily secure our data by restricting user access.
- DBMS supports transactions , which allows us to better handle and manage data integrity in real world applications where multi-threading is extensively used. Advantages of DBMS Segregation of the application program. Minimal data duplicity or data redundancy. Easy retrieval of data using the Query Language. Reduced development time and maintenance needs. With Cloud Datacenters, we now have Database Management Systems capable of storing almost infinite data. Seamless integration into the application programming languages, which makes it very easy to add a database to almost any application or website. Disadvantages of DBMS It's Complexity Except MySQL, which is open source, licensed DBMSs are generally costly. They are large in size. Basic Concepts of ER Model in DBMS TABLE(FOUNDATION) “Tables are like Organized List” UNORGANIZED TABLE(SAMPLE)
Asked Students: What’s wrong with the table? Can you tell me Maria’s grade in Science Is Pedro in Grade 9 or Grade 10? What Teacher teaches in Room 201? As you can see unorganized data table is confusing and hard to use! Let’s organized this into a table – a grid with rows and columns where similar information goes together.
Instructions: In groups of 4, create a table to track your group's birthdays. Columns they should identify: Full Name Birth Month Birth Day Age Zodiac Sign (bonus!) After 5 minutes: Have each group share their table on the board. Ask the student: What’s repetitive in the table? Juan's name and address are repeated Laptop price is repeated Solution hint: "Maybe we need separate tables? One for Customers, one for Products?"
Ask: "What's messy here?" Student info, subject info, teacher info, and club info are all in one table If a teacher teaches multiple subjects, their email repeats Key insight: "When tables get messy, we might need to split them into multiple, connected tables."
Part 4: Simple Rules for Good Tables
"Table Rules" Cheat Sheet One Theme Per Table : A table should be about one thing (students, OR teachers, OR products, not all mixed). Unique Rows : Each row should be unique (no exact duplicates). Consistent Columns : Each column should contain the same type of information (all names, all dates, all prices). Meaningful Headers: Column names should clearly explain what's in them. No Blank Cells: Try to have information in every cell (or use "N/A" if truly not applicable).
Part 5: Transition to ERD
The natural bridge: After students understand tables, say: "Great! Now you know how to organize information into tables. But what if we have lots of related information? Like a whole school system with students, teachers, classes, grades, clubs?"
An Entity is a distinct, identifiable "thing" in your system that you want to store data about. Real-World Analogy: In a university, the obvious "things" are Students, Professors, and Courses. In a hospital, they're Patients, Doctors, and Appointments. Key Insight: An entity is a type or category, not a single instance. "Student" is the entity. "John Smith, ID 12345" is an instance of that entity. In the Diagram: It's a rectangle.
Attributes: The "Details" (The Adjectives)
Attributes are the properties or characteristics that describe an entity. They answer the question: "What do I need to know about this thing?" Real-World Analogy : For a Student entity, the attributes are: Student_ID, Name, Date_of_Birth, Major, Enrollment_Year. Key Insight : Some attributes are special: Key Attribute (Identifier) : This uniquely identifies each instance. For a Student, it's the Student_ID. No two students can have the same ID. In diagrams, it's underlined. Composite Attribute: One attribute made of smaller parts. Name can be split into First_Name, Middle_Initial, Last_Name. Derived Attribute: Can be calculated from another. Age can be derived from Date_of_Birth. In the Diagram: They are listed as ovals connected to their entity.
Relationships: The "Connections" (The Verbs)
This is where the magic happens. Relationships capture how entities interact with each other. They represent a meaningful association. Real-World Analogy: A Student enrolls in a Course. A Doctor treats a Patient. Key Insight: Relationships have a degree (cardinality) that defines the rules of the association. This is crucial and often the trickiest part to get right. The main patterns are: One-to-One (1:1): A single instance of Entity A is associated with a single instance of Entity B. Example: One Country has one Capital City. One-to-Many (1:M): A single instance of Entity A is associated with many instances of Entity B. Example: One Mother has many Children. (The classic, natural example). Database Example: One Department employs many Employees. Many-to-Many (M:N): Many instances of Entity A are associated with many instances of Entity B. Example: Students enroll in Courses. One student takes many courses, and one course has many students. In the Diagram: It's a diamond connecting the entity rectangles. The "degree" is shown with notations (1, M, N) on the connecting lines. ER Model: Attributes If a Student is an Entity, then student's roll no. , student's name , student's age , student's gender etc will be its attributes. An attribute can be of many types, here are different types of attributes defined in ER database model:
- Simple attribute: The attributes with values that are atomic and cannot be broken down further are simple attributes. For example, student's age.
- Composite attribute: A composite attribute is made up of more than one simple attribute. For example, student's address will contain, house no. , street name , pincode etc.
