SS2 Data Processing First Term Lesson Note

 

 

DATA MODEL

A model is like a blueprint of a more complex real-world object or event.

 

A data model is a visual blueprint that defines the structure and relationships of data within a system, like a database or application. It defines how data is connected to each other and how they are processed and stored inside the system.

 

A data model documents and organizes data, how it is stored and accessed, and the relationships among different parts of data.

Think of it like that of an architect's blueprint for a building; it doesn't build the house itself, but it provides a clear, standardized plan for how everything should be organized and connected

 

 

TYPES OF DATA MODEL

Data models can be classified in various ways, but they are often categorized by their underlying structure and the relationships they represent. Here are some of the most common types:

 

1.      A flat file model is the simplest and oldest type of data model. It stores all data in a single, two-dimensional structure, like a table or a spreadsheet.

Imagine a spreadsheet, table or a simple text file. That is how a flat file model look like.

Key Characteristics of a Flat File Model

The flat file model is essentially a single file where data is organized into rows and columns.

Single Table: All the data is stored in one table. There are no separate linked tables. For example, student details, classes, and grades would all be kept together in the same table.

Rows and Records: Each row is one student’s complete record. For example, one row might include all of the details for a single student.

Columns and Fields: Each column is one type of information, like “Name,” “Age,” “Class,” or “Grade.” Every row uses the same set of columns to keep the information organized.

No Relationships: This is the main feature. A flat file model cannot connect different sets of data. For example, if you want to link a student’s personal details (like name, age, and address) with the classes they take, you would have to repeat all of their personal details for every single class record instead of just storing it once.

 

Example of a flat file

Employee ID	First Name	Last Name	Department
101	John	Doe	Sales
102	Jane	Smith	Marketing
103	John	Doe	Sales
104	Alice	Johnson	Sales

Name	Age	Sex	Class	Grade
John Smith	15	Male	Math	A
Anna Tope	14	Female	Math	A
John Smith	15	Male	Science	B
Anna Tope	14	Female	English	A

Example 1                                                                                                       Example 2

Flat file models are simple, but that’s also their limitation or weakness compared to relational databases. Some of this limitation are

1.      Data redundancy: this means the could be data repetition or data duplication. Because there are no links between data, the same information gets copied many times. For example, a student’s name and address would appear again and again for every class they take. We can see this in the second table above

2.      Lack of integrity: There is data inconsistent. The flat file model has no built-in way to enforce data integrity or consistency. If you update a customer's address in one record but forget to update it in another, your data becomes inconsistent

3.      Limited Querying: it is hard and slow to search for information, because you often have to scan the whole file line by line.

 

2.     Hierarchical Model

The Hierarchical Model is a data model that organizes data in a tree-like structure. It's one of the earliest database models, where data records are arranged into a strict hierarchy of parent-to-child relationships.

Key Characteristics of a Flat File Model

Structure: The structure of a hierarchical model is based on a one-to-many relationship. Data is arranged in a top-down, parent-child relationship. A "parent" record can have multiple "child" records, but each child can only have one parent.

This structure creates a series of branching paths that start from a single root node at the top. To locate any child record, you must follow the path starting from its parent. which is a predefined navigation path.

Example of a Hierarchical Model

A family tree is an excellent example of a hierarchical model.

·         The oldest ancestor is the root of the tree.

·         They have children, who are the direct descendants, or child nodes.

·         Each person in the tree (a child node) has only one set of parents (a parent node), but they can have multiple children themselves.

·         To find a person's information, you must trace their lineage back up the tree to the ancestors and then down through the correct branches to their specific name.

The advantages of hierarchical model are that it simple and easy to use for certain types of data. It is also very efficient for one-to-many relationships.

On the other hand, it has the disadvantages of not being flexible and it struggles to represent complex data, such as many-to-many relationships.

 

3.     Network Model

The network data model is an advanced version of the hierarchical model; it was developed to overcome the limitations of the hierarchical model by allowing for more complex relationships.

It uses a graph-like structure where a child node can have multiple parent nodes, representing many-to-many relationships. This provides greater flexibility in modeling real-world data.

Example 1 of network model                         Example 2 of network model

Examples of real word application include:

Banking Systems: A customer can have multiple accounts (checking, savings, etc.), and a single account can be linked to multiple customers (e.g., a joint account).

University Databases: A single student can be enrolled in multiple courses, and each course can have many students. This many-to-many relationship is a natural fit for the network model.

The Advantages of a network model is that it can model more complex, real-world relationships than the hierarchical model.

The disadvantage is that the  structure can be complex and difficult to navigate and manage, often requiring a deep understanding of the data's physical storage.

 

4.     Relational Model

The relational model is the most widely used data model today, forming the basis for almost all modern relational database management systems (RDBMS) like MySQL, PostgreSQL, and Oracle

 

·         Structure: Data is organized into two-dimensional tables (called "relations"). Each table consists of rows (called "tuples") and columns (called "attributes").³

·         Relationships: The tables are related to each other which are established using foreign keys.

Foreign keys are columns in one table that reference the primary key of another table.

Primary Key: A column (or set of columns) that uniquely identifies each row in a table. For instance, StudentID

 

How it works

Imagine you have several specialized filing cabinets instead of one big one.

·         Each Filing Cabinet = A Table: One cabinet for "Customers," another for "Products," another for "Orders."

·         Each Drawer = A Column: In the "Customers" cabinet, you might have drawers for "Customer Names," "Addresses," and "Customer IDs."

·         Each File Folder = A Row: A single file folder in the "Customers" cabinet contains all the information for one specific customer.

·         Customer ID on each Folder = Primary Key: A unique number on each customer's folder (e.g., "C-001") that ensures no two customers have the same ID.

·         "Order Form" with a "Customer ID" Field = Foreign Key: When you fill out an order form (which goes into the "Orders" cabinet), instead of rewriting all the customer's details, you just write down their unique "Customer ID." If you want to know who placed the order, you simply look up that Customer ID in the "Customers" cabinet.

Example of relational model

Examples of real word application are

·         E-commerce Website Database:

·         Social Media Platform:

Relational model have the following Advantages:

o    Data Integrity: Enforces strict data integrity through constraints and keys.

o    Flexibility: Allows for complex queries and joins to retrieve data in many different ways.

o    Maturity: A well-understood and mature technology with powerful query languages like SQL.

5.     Entity-Relationship (ER) Model

The ER model is a way to plan and design a database before building it. It looks at the real-world objects (entities), their details (attributes), and how they are connected (relationships).

Example: A Student (entity) has a Name, Age, and Address (attributes), and a Student can enroll in a Class (relationship).

These connections are shown in a picture called an ER diagram (ERD). In ERDs:

o    Rectangles = entities

o    Ovals = attributes

o    Diamonds = relationships

It serves as a blueprint for the database, making it easier for people to understand and then build the actual system.

6.     Dimensional Model

Dimensional Model

·         This model is mainly used in data warehouses for reporting and analysis (like business intelligence).

·         Structure: It organizes data into "fact" tables and "dimension" tables.

o    Fact tables store the numbers you want to measure called quantitative data (e.g., total marks, number of students enrolled).

o    Dimension tables store details that describe those numbers called descriptive data (e.g., student name, subject, semester).

Dimensional model is built to make searching and analyzing data fast. To do this, it keeps fewer tables (compared to relational models), so the computer doesn’t need to jump between many tables when answering questions.

 

Object-Oriented Data Model
This model organizes data as objects, similar to how object-oriented programming (OOP) works. Unlike relational databases (which separate data and rules), here both the data and the actions on that data are combined into one unit called an object.

7.     NoSQL Data Models

NoSQL Data Models
NoSQL (Not Only SQL) databases are non-relational models created to handle the limits of traditional databases. They work well with large, fast-changing, or unstructured data (like web apps, social media, or big data). Unlike table-based databases, NoSQL has different types, each with its own way of organizing data.

Types of NoSQL Models (with simple 20-word explanations):

1. Document Model – Stores data as flexible JSON-like documents, making it easy to keep complex information in one place.
2. Key-Value Model – Stores information as simple key–value pairs, great for fast lookups when you know the exact key.
3. Column-Family Model – Organizes data into rows and groups of columns, good for handling massive amounts of data efficiently.
4. Graph Model – Stores data as nodes and relationships, best for showing connections like friends, networks, or linked items.

 

Importance/Significance of Data Models

1. Enhanced Communication

2. Improved Data Quality and Integrity by

·         Preventing Redundancy

·         Ensuring Consistency:

·         Validating Accuracy

3. Optimized Performance and Efficiency by

·         Reducing Storage Costs:

·         Improving Query Speed:

·         Simplifying Maintenance:

 

 

DATA MODELLING

Data modeling is the process of creating a data model. While a data model is the blueprint that defines how data is structured and related, data modeling refers to the activity of creating this blueprint. It involves analyzing and defining the data a business collects and produces, as well as the relationships between those data points.

In a school for example data modeling is the process of deciding what information (students, teachers, courses, grades) needs to be tracked by the school and how they relate. While data model is the final blueprint, a diagram showing those information and how they relate

 

 

Approaches/Levels of Data Model

There are three main levels or stages in data modeling: these are

1.      Conceptual Data Model

2.      Logical Data Model

3.      Physical Data Model

1. Conceptual Data Model

This is the first step in designing a database. It shows a big-picture view of the main entities (like Customer, Product, and Order) and how they relate, but without technical details. Its main purpose is to communicate clearly with non-technical stakeholders, focusing only on what data is important to the business.

It key processes are:

·         Identify the main entities and their relationships

·         Do not include attributes (details about the entities)

·         Do not include primary keys

Example: In a school, we focus on Students enrolling in Classes and the Teachers who teach those Classes.

2. Logical Data Model

This is the second step, where we add more detail. It describes the data as clearly as possible but still without worrying about how it will be stored in a real database. It defines how the data should be structured, focusing on the data elements and their relationships.

It key processes are

·         Specify primary keys for all entities

·         Define relationships among entities

·         List all attributes for each entity

·         Resolve many-to-many relationships

·         Apply normalization to reduce redundancy

Example: For a Student: We can now add Student_ID, Name, and Age.

For a Class: We can now assign Class_ID and Subject.

For a Teacher: We can now assign Teacher_ID and Subject_Taught.

We can now determine relationship: A Student can enroll in many Classes, and a Class can have many Students. This requires a linking entity called Enrollment, which contains the Student_ID and Class_ID to link the two entities.

 

3. Physical Data Model

This is the final step, where the database design is turned into the real database. It shows exactly how the data will be stored in a specific system (like Oracle or MySQL). It includes the actual tables, the columns inside them, the data types (like numbers or text), and the rules that connect the tables. This model is what database developers and administrators use to build the working database.

It process are

·         Convert entities into tables

·         Convert relationships into foreign keys

·         Convert attributes into columns

·         Adjust the design based on database constraints or requirements

Example: A Students table with columns like student_id (INT) and name (VARCHAR), and a Classes table with class_id. Foreign keys are used to link Students and Classes.

 

CREATING SCHOOL DATABASE

Table: A table Store raw data in rows and columns, like spreadsheets.

Creating Tables (With Microsoft Access):

Step 1: Open Microsoft Access

• Launch Access and choose Blank Database.
• Give your database a name (e.g., SchoolDB.accdb) and click Create.

Step 2: Create a Table (Design View is best)

1. Go to the Create tab → Table Design.
2. Define your first table, for example: Students.
• Add fields (columns):
• student_id → Data Type: AutoNumber (Primary Key)
• name → Short Text
• age → Number
• email → Short Text

Step 3: Set Primary Key

• Highlight student_id → Right-click → Primary Key.
• This ensures each student record is unique.

Step 4: Save the Table

• Save as Students

Step 5: Create More Tables

Repeat the process for other entities:

Classes Table

• class_id (AutoNumber, Primary Key)
• class_name (Short Text)
• teacher_id (Number, Foreign Key → Teachers)

Teachers Table

• teacher_id (AutoNumber, Primary Key)
• name (Short Text)
• department (Short Text)

Enrollments Table (to handle many-to-many between Students and Classes)

• enrollment_id (AutoNumber, Primary Key)
• student_id (Number, Foreign Key → Students)
• class_id (Number, Foreign Key → Classes)

Step 6: Define Relationships

• Go to Database Tools → Relationships.
• Drag student_id in Students to student_id in Enrollments.
• Drag class_id in Classes to class_id in Enrollments.
• Drag teacher_id in Teachers to teacher_id in Classes.

 

Creating Forms

Forms: Forms are used to make data entry and viewing easier — instead of typing directly into tables, you create a form with text boxes, drop-downs, and buttons. This is especially useful for non-technical users.

Steps to Create a Form in Access

1. Choose the Table

·         In the Navigation Pane, click the table you want to create a form for (e.g., Students).

2. Create the Form Automatically

·         Go to the Create tab → click Form.

·         Access will instantly generate a form for that table.

·         You can now enter, view, and edit student records using the form.

3. Customize the Form (Optional)

·         Switch to Design View or Layout View to edit.

·         You can:

o    Rearrange fields (e.g., move “Name” above “Age”).

o    Change field types (e.g., make a drop-down list for “Class”).

o    Add titles, colors, or instructions.

4. Save the Form

·         Give it a clear name, e.g., frm_Students.

 

Forms for Relationships

·         You can also create forms that show related data.

·         Example: A Class Form that shows Class details and a subform listing all enrolled Students..

Why you Need It

·         The Enrollments table connects Students and Classes (many-to-many relationship).

·         Instead of entering IDs manually, we’ll use a form with drop-down lists so the user can pick a student and a class easily.

 

Steps to Create the Student Enrollment Form

1. Create Lookup Fields in Enrollments Table

1.      Open the Enrollments table in Design View.

2.      For the field student_id, change Data Type to Lookup Wizard →

o    Choose Students table → select student_id and name.

o    This way, users can pick a student by name, but Access will store the ID.

3.      Do the same for class_id using the Classes table (class_id, class_name).

Now Enrollments has drop-downs for Students and Classes.

2. Create the Enrollment Form

1.      In the Navigation Pane, click the Enrollments table.

2.      Go to Create tab → click Form Wizard.

3.      Select fields from Enrollments (e.g., student_id, class_id).

4.      Access will generate a form where you can pick a Student and a Class from drop-downs.

3. Customize the Form

·         Switch to Layout View or Design View.

·         Rename labels (e.g., instead of student_id, call it Student Name).

·         Add a title like Student Enrollment Form.

4. Save the Form

·         Save as frm_Enrollments.

·         Now you can open it anytime to enroll a student into a class by selecting from the drop-downs.

At this point, you’ve built a working Enrollment Form. Users won’t need to know IDs — they’ll just pick names from lists.

Creating Class Form with a Subform.

How it work

·         The main form shows details of one class (from the Classes table).

·         The subform shows related records (students enrolled in that class, from the Enrollments + Students tables).

Steps to Create a Class Form with Subform

1. Start the Form Wizard

1.      Go to the Create tab → click Form Wizard.

2.      First, select the Classes table.

o    Choose fields: class_id, class_name, teacher_id.

2. Add the Related Enrollments

1.      In the same wizard, add fields from the Enrollments table.

o    Choose student_id.

o    (Since you already made it a lookup field, it will show student names instead of IDs.)

2.      Access recognizes the relationship and will ask:

o    Do you want to view Classes with related Enrollments?

o    Choose this option.

3. Choose the Form Layout

·         Pick Form with Subform (datasheet or tabular view works best for the subform).

·         The main form = Classes info.

·         The subform = list of enrolled Students for that class.

4. Customize the Form

·         Switch to Design View or Layout View.

·         Rename labels (e.g., “student_id” → “Student Name”).

·         Add a nice title like Class Enrollment Form.

·         Resize the subform so it’s easy to read.

5. Save and Test

·         Save as frm_Classes_With_Students.

·         Open it → You’ll see one class at a time, with a subform listing all students enrolled in that class.

·         You can even add new students to the class directly from the subform.

 Now you have both:

·         Enrollment Form (frm_Enrollments): Assign students to classes directly.

·         Class Form with Subform (frm_Classes_With_Students): View each class and see all enrolled students.

CREATING QUERIES

Queries let you ask questions about your data — like filtering, sorting, or combining information from multiple tables.

Types of Queries in Access

1.      Select Query → Pulls and displays data (most common).

2.      Action Queries → Change data (e.g., update, delete).

3.      Aggregate Queries → Do calculations (e.g., count, average).

We’ll focus first on Select Queries.

 

Examples of select Queries for Our School Database

1. Show All Students in a Class

1.      Go to the Create tab → Query Design.

2.      Add the Students, Enrollments, and Classes tables.

3.      Drag these fields:

o    From Students: name, email

o    From Classes: class_name

4.      In the Criteria row under class_name, type the class you want (e.g., "Mathematics").

5.      Run the query → It will list all students in Mathematics.

2. Show All Classes a Student Is Enrolled In

·         Do the same, but this time select:

o    From Students: name

o    From Classes: class_name

·         In the Criteria row under name, type "John Doe".

·         Run it → You’ll see all classes that student is enrolled in.

3. Count How Many Students Are in Each Class

1.      Create a new Query Design.

2.      Add Classes and Enrollments.

3.      Select class_name.

4.      On the ribbon, click Totals (Σ).

5.      Add student_id and set it to Count.

6.      Run it → It shows each class with the number of enrolled students.

Creating Reports

Reports let you take the raw data (from tables or queries) and present it in a formatted, printable layout — perfect for teachers, administrators, or stakeholders who need clean summaries.

Steps to Create a Report

1. Base the Report on a Query or Table

·         First, decide what you want in your report.

·         Example: A list of all Students enrolled in each Class.

·         It’s best to build a query first (e.g., “Students in Classes”) and then base the report on that query.

2. Create the Report

1.      Go to the Create tab → Report Wizard.

2.      Select your table/query (e.g., Students with Classes query).

3.      Choose the fields you want (e.g., class_name, student_name, email).

4.      Choose how to group the data:

o    Group by class_name → so each class lists its students.

5.      Choose sort order (e.g., sort students by name).

6.      Pick a layout and style → Finish.

3. Customize the Report

·         Switch to Design View or Layout View.

·         You can:

o    Add titles and headings.

o    Resize columns so they fit nicely.

o    Add totals (e.g., count of students per class).

4. Save and Use the Report

·         Save it as rpt_ClassEnrollments.

·         Now, whenever you run it, Access will pull fresh data.

·         You can print it, save as PDF, or email it.

Example of other Reports You Can Build

1.      Class Enrollment Report → Shows each class with a list of students enrolled.

2.      Student Report → Shows a student with all their enrolled classes.

3.      Teacher Report → Shows each teacher and the classes they teach.

 

 

Normalization

Meaning of Normalization: Normalization is a systematic process in database design used to organize data in a way that reduces redundancy and improves data integrity.

Key functions

• Eliminates duplicate data.
• Ensures data dependencies make sense.
• Makes database efficient and consistent.

Normal Forms: Normal forms are standards or levels to which a table is normalized. The most common ones are:

1. First Normal Form (1NF) – No repeating groups; atomic (indivisible) data.
2. Second Normal Form (2NF) – 1NF + no partial dependency (every non-key attribute fully depends on the primary key).
3. Third Normal Form (3NF) – 2NF + no transitive dependency (non-key attributes do not depend on other non-key attributes).

There are higher forms (BCNF, 4NF, 5NF), but 1NF, 2NF, and 3NF cover most practical database needs.

Determinants: A determinant is an attribute (or set of attributes) that uniquely determines another attribute.

• Example: In a student database:
• StudentID → StudentName
  Here, StudentID is the determinant because knowing it tells you exactly the StudentName.

Determinants can be:

1. Unique determinant – The attribute that is a candidate key or primary key (uniquely identifies a row).
• Example: EmployeeID → EmployeeName
2. Non-unique determinant – Attribute that determines another attribute but is not unique across the table.
• Example: DepartmentID → DepartmentName
• Multiple employees may have the same DepartmentID.
• So, DepartmentID is a determinant but not unique.

Think of determinants like “master keys”:

• Unique determinants = master keys that open exactly one door.
• Non-unique determinants = keys that can open multiple doors, so you need to organize carefully to avoid confusion.