DATABASE MANAGEMENT

SYSTEMS
COMPLETE EXAM PREPARATION STUDY GUIDE
Level 200 | 2nd Semester | UPSA

Based on: Modern Database Management, 13th Edition (Pearson)

CHAPTERS COVERED:
• Chapter 1: The Database Environment & Development Process
• Chapter 2: Modeling Data in the Organization (E-R Model)
• Chapter 3: The Enhanced E-R Model (EER)
• Chapter 4: Logical Database Design & Relational Model
• Chapter 5: Introduction to SQL
• Chapter 8: Physical Database Design & Infrastructure
• Chapter 9: Data Warehousing & Data Integration
• Chapter 10: Big Data Technologies & NoSQL
CHAPTER 1: THE DATABASE ENVIRONMENT &
DEVELOPMENT PROCESS
1.1 Core Definitions
Understanding the basic terminology is the absolute foundation of this course. Exams frequently test
definitions.

TERM DEFINITION
Database An organized collection of logically related data stored and
accessed electronically.
Data Stored representations of meaningful objects and events. Can
be structured (numbers, text, dates) or unstructured (images,
video, documents).
Information Data that has been processed or organized in a way that
increases knowledge in the person using it.
Metadata Data that describes the properties and context of user data
(e.g., data type, field size, allowable values, source). "Data
about data."
DBMS Database Management System — software that manages the
creation, maintenance, and use of a database.
Repository A centralized storehouse for all metadata in an organization.

⚠ EXAM TRAP: Do NOT confuse "data" with "information." Data is raw facts; information is
data that has been processed to add meaning. A list of sale amounts = data. "Total sales for Q3
= GHS 50,000" = information.
⚠ EXAM TRAP: "Metadata" is NOT the actual data itself — it describes the data. For example,
"StudentID is a 10-character field" is metadata about StudentID.

1.2 Problems with Traditional File Processing Systems

Before databases, organizations stored data in separate files for each application. This caused severe
problems:

PROBLEM EXPLANATION
Program-Data Dependence Every program had to store its own metadata about the files it
used. If the file structure changed, ALL programs using that
file had to be rewritten.
Data Duplication/Redundancy The same data (e.g., a customer's address) was stored in
multiple separate files. This wasted storage and caused
inconsistency.
Limited Data Sharing No centralized control of data. Different departments had their
own isolated files that couldn't easily communicate.
 Lengthy Development Times Programmers had to design their own file formats for every
new application — slow and costly.
Excessive Program Maintenance Because of all the above problems, up to 80% of information
systems budgets went to just maintaining existing programs.

💡 MNEMONIC: Remember the 5 file processing problems with: "People Demand Large
Exams Badly" → Program-data dependence, Duplication, Limited sharing, Extensive dev time,
Bad maintenance.

1.3 The Database Approach & Its Advantages

The database approach solves file processing problems through centralized, controlled data
management.

ADVANTAGE HOW IT HELPS

Program-Data Independence The database structure (metadata) is stored separately from
programs. Programs don't need to know the physical
structure.
Planned Data Redundancy Instead of accidental duplication, only intentional (planned)
redundancy is allowed — like foreign keys.
Improved Data Consistency Because data is stored once and shared, it stays consistent
across the organization.
Improved Data Sharing Multiple users and applications can access the same data
through controlled interfaces.
Increased Productivity Common data access tools (SQL) mean developers don't
reinvent the wheel for each application.
Enforcement of Standards A DBA can enforce naming conventions, data formats, and
business rules centrally.
Improved Data Quality Integrity constraints and validation rules catch bad data at
entry time.
Reduced Program Maintenance Changes to the database structure don't require rewriting all
programs.
Better Decision Support Consolidated data enables better analysis and reporting.

Costs & Risks of the Database Approach

Nothing is free — the database approach also has costs:
• New, specialized personnel needed (DBA, data modelers)
• Installation and management cost and complexity
• Conversion costs (migrating from old file systems)
• Need for explicit backup and recovery planning
• Organizational conflict (who "owns" the data?)
 ★ KEY POINT: For every exam question asking "what are the advantages of databases?" —
aim for at least 5 points with brief explanations.

1.4 Components of the Database Environment

A complete database environment has 9 key components:

COMPONENT DESCRIPTION
Data Modeling & Design Tools Automated tools (like MySQL Workbench, Visio) for designing
databases and application programs.
Repository Centralized storehouse of metadata about the database.
DBMS Software that manages the database (e.g., MySQL, Oracle,
SQL Server).
Database The actual storehouse of the data.
Application Programs Software applications that use the data.
User Interface How users interact — text forms, GUIs, web interfaces.
Data/Database Administrators Personnel responsible for maintaining database integrity,
security, and performance.
System Developers Personnel responsible for designing databases and
application software.
End Users People who use the applications and databases for their jobs.

1.5 Three-Schema Architecture

This is a CRITICAL concept — expect it in every exam. It explains how different people see different
views of the same database.

SCHEMA LEVEL DESCRIPTION

External Schema (View Level) User views — each user/application sees only the subset of
data relevant to them. A salesperson sees customer data;
payroll sees salary data. These are subsets of the conceptual
schema.
Conceptual Schema (Logical The complete, unified logical view of the entire database.
Level) Described using Entity-Relationship (E-R) models.
Independent of physical storage.
Internal Schema (Physical Level) Describes the actual physical storage of data — file
structures, indexes, storage locations. Closest to the
hardware.

⚠ EXAM TRAP: The three schemas are: External (user views), Conceptual (logical ER model),
Internal (physical storage). Many students mix up "conceptual" with "physical" — remember:
Conceptual = the design blueprint; Internal = the actual storage on disk.
 💡 MNEMONIC: "Every Cat Is Different" → External, Conceptual, Internal (top to bottom in the
architecture).

1.6 SDLC vs Prototyping

APPROACH CHARACTERISTICS
SDLC (Systems Development Life 5-step methodology: Planning → Analysis → Design →
Cycle) Implementation → Maintenance. Methodical, structured, time-
consuming, comprehensive. Best for large, well-defined
projects.
Prototyping / RAD (Rapid Fast, iterative approach. Build a prototype, test it, refine it.
Application Development) Less upfront planning. Best when requirements are unclear or
change rapidly. Includes Agile, Scrum, XP, DSDM.

1.7 Database Application Types

TYPE CHARACTERISTICS
Personal Databases Size: megabytes. Single user. Example: Microsoft Access on
one computer.
Departmental Multi-Tiered Size: gigabytes. Up to ~100 users department-wide.
Client/server architecture.
Enterprise Applications Size: gigabytes to petabytes. Very large user base, company-
wide. Includes ERP, CRM, Supply Chain.

Types of Enterprise Applications

TYPE DESCRIPTION
Enterprise Systems ERP, CRM, Supply Chain, HR Management. Typically use
relational databases.
Data Warehouses Integrate historical data from multiple sources for analysis and
trend identification.
Data Lakes Large repositories for internal and external data that don't
follow a predefined schema.

1.8 Database Team Roles

ROLE RESPONSIBILITIES
Business Analysts Analyze business situation, establish requirements (no deep
technical expertise required).
Systems Analysts Like business analysts but with technical IS expertise.
Database Analysts / Data Modelers Focus specifically on database design and ER modeling.
Database Architects Establish data standards across business units.
Data Administrators Maintain existing databases; ensure data integrity and
consistency.
Programmers Write the code for programs that interact with the database.
Project Managers Oversee projects, manage personnel and timelines.
End Users The "customers" who communicate their data needs.

1.9 Chapter 1 — MCQ Practice

MCQ 1: Which of the following best describes "metadata"?
A. Raw facts about business transactions
B. Data processed to increase knowledge

✓ C. Data that describes properties and context of user data

D. An organized collection of logically related data
Explanation: Metadata is "data about data" — it describes properties like data type, field size, and
allowable values. A is just "data"; B is "information"; D is a "database."

MCQ 2: Which problem of file processing systems consumed about 80% of IS budgets?
A. Program-data dependence
B. Data duplication

✓ C. Excessive program maintenance

D. Limited data sharing
Explanation: The slides specifically state that excessive program maintenance consumed 80% of
information systems budgets — because any change to a file structure required updating all programs
using it.

MCQ 3: In the Three-Schema Architecture, the CONCEPTUAL schema corresponds to:

A. Individual user views of the database
B. The physical storage of data on disk

✓ C. The complete logical view of the entire database

D. The DBMS software layer
Explanation: The Conceptual Schema is the complete logical view — typically represented by E-R
models. External = user views; Internal = physical storage.

MCQ 4: Which database development approach emphasizes speed and iteration over
comprehensive upfront planning?
A. SDLC
✓ B. RAD/Prototyping
C. Three-Schema Architecture
D. Normalization
Explanation: RAD (Rapid Application Development) and prototyping prioritize fast iteration. SDLC is
the methodical, time-consuming alternative.

1.10 Chapter 1 — Key Takeaways & Cheat Sheet

★ KEY POINT: Database = organized, logically related collection of data. Information =
processed data. Metadata = data about data.
★ KEY POINT: 5 File Processing Problems: Dependence, Duplication, Limited Sharing, Long
Dev Time, High Maintenance.
★ KEY POINT: 3 Schema Levels: External (user views) → Conceptual (ER model) → Internal
(physical storage).
★ KEY POINT: SDLC is methodical (5 steps); RAD/Prototyping is fast and iterative.
★ KEY POINT: Enterprise app types: Personal (MB, 1 user) → Departmental (GB, ~100 users)
→ Enterprise (TB+, company-wide).
CHAPTER 2: MODELING DATA IN THE ORGANIZATION (E-
R MODEL)
2.1 Why Data Modeling Matters
Data modeling is the process of creating a visual representation of data and how it relates within a
system. A good data model is the blueprint for the entire database — if the model is wrong, the
database will be wrong. You model first, then build.

2.2 E-R Model Constructs

The Entity-Relationship (E-R) model uses three main building blocks:

CONSTRUCT DESCRIPTION
Entity A person, place, object, event, or concept about which the
organization wishes to maintain data. Examples: STUDENT,
COURSE, EMPLOYEE. Think of it as a "thing" that matters to
the business.
Entity Type A collection (category) of entities that share common
properties. In a database, this corresponds to a TABLE.
Entity Instance A single occurrence (row) of an entity type. E.g., the specific
student "Sammy Mensah" is an instance of the STUDENT
entity type.
Relationship Type A category of relationship linking two entity types. E.g.,
STUDENT "Enrolls In" COURSE.
Relationship Instance A specific link between two entity instances. E.g., student
#200412 is enrolled in course DBMS.
Attribute A property or characteristic of an entity or relationship. E.g.,
StudentID, Name, GPA are attributes of STUDENT.

⚠ EXAM TRAP: In E-R modeling, a "relation" (used in relational theory) is NOT the same as a
"relationship" (used in E-R modeling). A relation = a table; a relationship = an association
between two entities.

2.3 Types of Attributes

ATTRIBUTE TYPE DESCRIPTION
Simple (Atomic) Cannot be broken down further. E.g., StudentID, Age.
Composite Made up of smaller sub-attributes. E.g., Address can be
broken into Street, City, State, ZIP.
Single-Valued Only one value per entity instance. E.g., DateOfBirth.
 Multivalued Can have multiple values for one entity. E.g., an Employee's
Skills (can have many). Drawn with double oval in E-R
notation.
Derived Value calculated from other stored attributes. NOT stored in
the database. E.g., Age derived from DateOfBirth + today's
date. Drawn with dashed oval.
Stored The actual stored attribute (opposite of derived). E.g.,
DateOfBirth is stored; Age is derived.
Required Must have a value for every entity instance.
Optional May be null (empty) for some instances.
Identifier (Key) Uniquely identifies each instance of an entity type. Underlined
in E-R diagrams.

⚠ EXAM TRAP: Derived attributes are NOT physically stored in the database. If asked "why not
store Age?", the answer is: because Age changes every year, but DateOfBirth never changes.
Calculating Age from DateOfBirth is always accurate.

2.4 Strong vs Weak Entities

CONCEPT DESCRIPTION
Strong Entity Exists independently of other entities. Has its own unique
identifier. Drawn with a single-line rectangle. Example:
EMPLOYEE (has EmployeeID).
Weak Entity Cannot exist without a related strong entity ("owner"). Has no
complete identifier of its own — only a partial identifier. Drawn
with double-line rectangle. Example: DEPENDENT (child of
an employee — cannot exist if the employee doesn't exist).
Partial Identifier The identifying attribute of a weak entity — underlined with a
dashed line. It's only unique within the context of the owning
entity.
Identifying Relationship The relationship between a strong entity and its weak entity.
Drawn with a double diamond.

⚠ EXAM TRAP: A weak entity's primary key in a relational database is a COMPOSITE KEY
made up of: (1) its partial identifier + (2) the primary key of the strong entity. Don't forget this
when mapping weak entities to tables.

2.5 Relationships — Degree

The DEGREE of a relationship is the number of entity types participating in it.
DEGREE DESCRIPTION
 Unary (Degree 1) An entity related to itself. Also called a recursive relationship.
E.g., EMPLOYEE "manages" EMPLOYEE. One employee
manages other employees.
Binary (Degree 2) Two different entity types are related. Most common type.
E.g., STUDENT "enrolls in" COURSE.
Ternary (Degree 3) Three entity types all participate in the same relationship
simultaneously. E.g., VENDOR "supplies" PART to
WAREHOUSE — you can't describe this with just binary
relationships.

2.6 Cardinality of Relationships

Cardinality defines HOW MANY instances of one entity can be related to instances of another entity.
CARDINALITY DESCRIPTION
One-to-One (1:1) Each entity instance on both sides relates to exactly one on
the other side. Rare in practice. E.g., EMPLOYEE "is
assigned" one COMPANY_CAR, and each car is assigned to
one employee.
One-to-Many (1:N) One entity instance on one side can relate to MANY on the
other side, but each instance on the many side relates to only
ONE. Most common. E.g., one DEPARTMENT has many
EMPLOYEES.
Many-to-Many (M:N) Instances on both sides can have many related instances.
E.g., STUDENT "enrolls in" many COURSES, and each
COURSE has many STUDENTS.

Cardinality Constraints: Minimum and Maximum

Each side of a relationship has both a MINIMUM cardinality and a MAXIMUM cardinality.
• Minimum = 0 → Relationship is OPTIONAL (entity doesn't HAVE to participate)
• Minimum = 1 → Relationship is MANDATORY (entity MUST participate)
• Maximum = 1 → Entity relates to at most ONE
• Maximum = N → Entity can relate to MANY

Example: DEPARTMENT (1,1) — has — (0,N) EMPLOYEE

• A department must have at least 1 employee (mandatory) and can have many (max N)
• An employee can be in 0 departments (optional — maybe not yet assigned) but at most 1

2.7 Associative Entities

An associative entity (also called a junction entity or bridge table) is a relationship that also has its own
attributes. It is simultaneously a RELATIONSHIP and an ENTITY.

When to use an associative entity instead of just a relationship:

1. All relationships for the associative entity should be "many" (M:N at minimum)
 2. The associative entity could have meaning independent of the other entities
3. The associative entity has its own identifier and other attributes
4. The associative entity participates in other relationships
5. Always convert ternary relationships to associative entities

Example: STUDENT and COURSE have a M:N relationship. But each enrollment has a GRADE and
DATE. So ENROLLMENT becomes an associative entity with attributes Grade and Date.

⚠ EXAM TRAP: When a M:N relationship has attributes of its own, you MUST convert it into an
associative entity. Simply putting the attributes on the relationship line is not sufficient for
database implementation.

2.8 Business Rules

Business rules define or constrain aspects of the business. In data modeling, they guide how entities
and relationships are structured.

A good business rule is:

QUALITY MEANING
Declarative States WHAT, not HOW. (What must be true, not how to
enforce it.)
Precise Has a clear, agreed-upon meaning — no ambiguity.
Atomic Is a single statement — one rule, one concept.
Consistent Does not contradict other rules.
Expressible Written in structured natural language.
Distinct Non-redundant — doesn't duplicate another rule.
Business-Oriented Understood by business people, not just IT.

💡 MNEMONIC: For business rule qualities: "D-PACED B" → Declarative, Precise, Atomic,
Consistent, Expressible, Distinct, Business-Oriented.

2.9 Time Stamping

A time stamp is a time value associated with a data value, often indicating when an event occurred that
affected the data.
Example: A product's PRICE changes over time. Instead of overwriting the old price, you use a
composite multivalued attribute "Price History" = {Date, Price} to track all historical prices.
This allows you to answer questions like "What was the price on January 1, 2020?"

2.10 Chapter 2 — MCQ Practice

MCQ 5: Which type of attribute is calculated from other stored attributes and is NOT physically
stored in the database?
A. Composite attribute
B. Multivalued attribute

✓ C. Derived attribute
D. Required attribute
Explanation: A derived attribute's value is calculated from other attributes (e.g., Age from
DateOfBirth). It is represented with a dashed oval in E-R diagrams and is NOT stored.

MCQ 6: A DEPENDENT entity cannot exist without its related EMPLOYEE entity. This makes
DEPENDENT a:
A. Strong entity

✓ B. Weak entity
C. Associative entity
D. Derived entity
Explanation: A weak entity depends on a strong (identifying owner) entity for its existence and
identification. It has no complete identifier of its own — only a partial identifier.

MCQ 7: A STUDENT can enroll in many COURSES, and each COURSE can have many
STUDENTS. This relationship is:
A. One-to-One (1:1)
B. One-to-Many (1:N)

✓ C. Many-to-Many (M:N)
D. Unary
Explanation: When instances on BOTH sides can relate to MANY instances on the other side, it is a
Many-to-Many (M:N) relationship.

MCQ 8: An EMPLOYEE entity has a "manages" relationship with other EMPLOYEE entities. This
is an example of a:
A. Binary relationship
B. Ternary relationship

✓ C. Unary (recursive) relationship

D. Identifying relationship
Explanation: A unary (or recursive) relationship is one where an entity is related to itself. Here,
employees manage other employees — all within the same EMPLOYEE entity type.

2.11 Chapter 2 — Key Takeaways & Cheat Sheet

★ KEY POINT: Entity Type = Table | Entity Instance = Row | Attribute = Column | Relationship
= Association between tables.
★ KEY POINT: Attribute types: Simple, Composite, Single-Valued, Multivalued (double oval),
Derived (dashed oval), Stored, Required, Optional, Identifier (underlined).
★ KEY POINT: Strong entity = independent, has own key. Weak entity = dependent, partial
identifier, double rectangle, double diamond relationship.
★ KEY POINT: Relationship degrees: Unary (1 entity type), Binary (2 types), Ternary (3 types).
★ KEY POINT: Cardinality: 1:1, 1:N (most common), M:N. Always specify minimum (0=optional,
1=mandatory) and maximum cardinality.
★ KEY POINT: M:N relationships with their own attributes MUST become associative entities.
CHAPTER 3: THE ENHANCED E-R (EER) MODEL
3.1 Why EER? Extending the Basic E-R Model
The basic E-R model (Chapter 2) is powerful but lacks some modeling features needed for complex
real-world systems. The Enhanced E-R (EER) model adds:
• Supertypes and Subtypes (generalization/specialization)
• Attribute Inheritance
• Constraints (completeness and disjointness)
• Entity Clusters

3.2 Supertypes and Subtypes

CONCEPT DESCRIPTION
Supertype A generic entity type that has a relationship with one or more
subtypes. It contains the attributes COMMON to all subtypes.
Example: EMPLOYEE is a supertype of FULL-TIME, PART-
TIME, and CONTRACT employees.
Subtype A subgrouping of entities within an entity type that has
attributes DISTINCT from other subgroupings. Example:
FULL-TIME has attributes like Salary and Benefits; PART-
TIME has HoursPerWeek.
Attribute Inheritance A subtype automatically inherits ALL attributes and
relationships of its supertype. An instance of a subtype is also
considered an instance of the supertype.

Example — VEHICLE supertype with subtypes CAR, TRUCK, MOTORCYCLE:

• Common attributes (go in VEHICLE supertype): VehicleID, Make, Model, Year, Color,
LicensePlate
• CAR-specific attributes: NumberOfDoors, SeatCapacity
• TRUCK-specific attributes: PayloadCapacity, TowingCapacity
• If MOTORCYCLE has NO unique attributes, it doesn't need its own subtype!

3.3 Generalization vs Specialization

TECHNIQUE DESCRIPTION & DIRECTION
Generalization (Bottom-Up) Start with several specific entity types and identify their
COMMON attributes, then group them under a new, more
general supertype. You go from specific → general. Example:
You have CAR, TRUCK, MOTORCYCLE — you notice they
all share attributes, so you create a VEHICLE supertype.
Specialization (Top-Down) Start with a general entity type and identify SUBGROUPS that
have unique attributes or relationships. You go from general
→ specific. Example: You have EMPLOYEE — you notice
some are HOURLY, some SALARIED, some CONTRACT —
so you specialize.
 ⚠ EXAM TRAP: Generalization = Bottom-Up (combining specific into general). Specialization =
Top-Down (breaking general into specific). These are OPPOSITE directions!
💡 MNEMONIC: "Gen goes UP to heaven, Spec goes DOWN to specifics." Generalization =
bottom-UP. Specialization = top-DOWN.

3.4 Completeness Constraints

Completeness constraints answer: "Must every supertype instance belong to at least one subtype?"
CONSTRAINT TYPE DESCRIPTION & NOTATION
Total Specialization (Complete) YES — every instance of the supertype MUST belong to at
least one subtype. Represented with a DOUBLE LINE
between supertype and the circle/oval in EER notation.
Example: Every EMPLOYEE must be either FULL-TIME,
PART-TIME, or CONTRACT. There is no "plain employee"
that isn't one of these.
Partial Specialization (Incomplete) NO — some instances of the supertype may NOT belong to
any subtype. Represented with a SINGLE LINE. Example: A
VEHICLE might just be a vehicle (like a trailer) that doesn't fit
into CAR, TRUCK, or MOTORCYCLE subtypes.

⚠ EXAM TRAP: Double line = TOTAL (every supertype instance must be in a subtype). Single
line = PARTIAL (some supertype instances may not belong to any subtype). Students often mix
these up.

3.5 Disjointness Constraints

Disjointness constraints answer: "Can a supertype instance belong to MORE THAN ONE subtype at
the same time?"
CONSTRAINT TYPE DESCRIPTION & NOTATION
Disjoint Rule (d) NO — an instance can belong to AT MOST ONE subtype at a
time. Represented with "d" inside the circle in EER notation.
Example: A VEHICLE cannot be both a CAR and a TRUCK at
the same time.
Overlap Rule (o) YES — an instance can belong to MULTIPLE subtypes
simultaneously. Represented with "o" inside the circle.
Example: A PERSON can be both an EMPLOYEE and a
CUSTOMER of the same organization. Or a PART can be
both MANUFACTURED and PURCHASED (dual-sourced).

⚠ EXAM TRAP: "Disjoint" means an entity can ONLY be ONE subtype at a time — like being
either male OR female (in a simple binary system). "Overlap" means it can be multiple subtypes
— like being both a student AND an employee.
3.6 Combining the Constraints — The Four Combinations
Every supertype/subtype relationship has BOTH a completeness constraint AND a disjointness
constraint. The four possible combinations are:
COMBINATION MEANING
Total + Disjoint Every supertype instance must be in exactly one subtype.
Example: EMPLOYEE must be either FULL-TIME or PART-
TIME (not both, and not neither).
Total + Overlap Every supertype instance must be in at least one subtype,
possibly more. Example: Every WORKER is either
EMPLOYEE or CONTRACTOR, or possibly both.
Partial + Disjoint Supertype instances may or may not be in a subtype; if they
are, they're in only one. Example: Some VEHICLES are
classified (CAR, TRUCK) but others aren't; no vehicle is both
car AND truck.
Partial + Overlap Supertype instances may or may not be in subtypes; if they
are, they can be in multiple. Example: Some PEOPLE are
classified as EMPLOYEE and/or CUSTOMER; others aren't
classified at all.

3.7 Subtype Discriminator

A subtype discriminator is an attribute of the supertype whose values determine which subtype(s) an
instance belongs to.
TYPE DESCRIPTION
Disjoint Discriminator A simple attribute with alternative values — one value per
possible subtype. E.g., EmployeeType = "Full-Time" or "Part-
Time" or "Contract".
Overlap Discriminator A composite attribute where each sub-part is a Boolean
indicating membership in a subtype. E.g., For EMPLOYEE
with overlap: {IsFullTime: Y/N, IsManager: Y/N, IsTechnician:
Y/N}.

3.8 Entity Clusters

When an EER diagram gets very large (many entities and relationships), it becomes hard to read.
Entity clusters are the solution.
An entity cluster is a set of one or more entity types and their associated relationships grouped into a
single abstract entity type — like a "folder" that hides complexity.
Benefits: Dramatically improves readability. You can expand/drill into a cluster when needed, but the
high-level diagram stays clean.

3.9 Packaged (Universal) Data Models

A packaged data model (also called a universal or template data model) is a predefined data model that
can be reused as a starting point for a new project.
Advantages:
• Use proven, tested model components
• Save time and cost
• Less likelihood of data model errors
• Easier to evolve and modify over time
• Supertype/subtype hierarchies promote reuse
• Aid in requirements determination

3.10 Chapter 3 — MCQ Practice

MCQ 9: In the EER model, what does a DOUBLE LINE between a supertype and the subtype
circle represent?
A. Disjoint rule
B. Overlap rule

✓ C. Total specialization
D. Partial specialization
Explanation: Double line = Total Specialization (also called the total completeness constraint). It
means every instance of the supertype MUST belong to at least one subtype.

MCQ 10: A person can be both an EMPLOYEE and a CUSTOMER of the same company. What
constraint applies?
A. Total specialization with disjoint rule

✓ B. Partial specialization with overlap rule

C. Total specialization with overlap rule
D. Partial specialization with disjoint rule
Explanation: Since a person might not be either (partial), but if they are classified, they can be both
employee AND customer (overlap), this is Partial + Overlap.

MCQ 11: Identifying common attributes among CAR, TRUCK, and MOTORCYCLE and
combining them into a VEHICLE supertype is an example of:
A. Specialization (top-down)

✓ B. Generalization (bottom-up)
C. Partial decomposition
D. Entity clustering
Explanation: Generalization is bottom-up: starting with specific entities (CAR, TRUCK,
MOTORCYCLE) and moving UP to a general supertype (VEHICLE).

3.11 Chapter 3 — Key Takeaways

★ KEY POINT: Supertype = general entity with common attributes. Subtype = specific subgroup
with unique attributes. Subtypes INHERIT all supertype attributes.
★ KEY POINT: Generalization = Bottom-Up (specific → general). Specialization = Top-Down
(general → specific).
★ KEY POINT: Completeness: Total (double line) = must be in a subtype. Partial (single line) =
may not be in any subtype.
★ KEY POINT: Disjointness: Disjoint (d) = only ONE subtype at a time. Overlap (o) = can be in
MULTIPLE subtypes.
★ KEY POINT: Subtype discriminator tells the database which subtype an instance belongs to.
CHAPTER 4: LOGICAL DATABASE DESIGN & THE
RELATIONAL MODEL
4.1 The Relational Model
The relational model organizes data into TABLES (called relations). It is the foundation of SQL
databases like MySQL, Oracle, and SQL Server.

Components of the Relational Model

COMPONENT DESCRIPTION
Data Structure Tables (relations), rows (records/tuples), columns
(attributes/fields).
Data Manipulation SQL operations for retrieving and modifying data.
Data Integrity Mechanisms (constraints) for enforcing business rules.

4.2 Properties of a Relation (Table)

For a table to qualify as a RELATION, it must satisfy ALL 6 of these properties:
6. It must have a UNIQUE NAME — no two tables can have the same name.
7. Every attribute value must be ATOMIC — no multivalued or composite values in a cell.
8. Every ROW must be UNIQUE — no two rows can be completely identical.
9. Attribute (column) names must be UNIQUE — no two columns with the same name.
10. The ORDER OF COLUMNS is irrelevant — column order doesn't matter.
11. The ORDER OF ROWS is irrelevant — row order doesn't matter.

★ KEY POINT: ALL relations are automatically in First Normal Form (1NF), because a key
property of a relation is that every attribute value is atomic.
⚠ EXAM TRAP: If a table has multivalued attributes (like a cell containing multiple values), it is
NOT a relation and NOT in 1NF.

4.3 Keys in Relational Databases

KEY TYPE DESCRIPTION
Primary Key (PK) The unique identifier for each row in a table. No null values
allowed. Every table must have one. Can be simple (single
field) or composite (multiple fields). Example: StudentID is the
primary key of the STUDENT table.
Foreign Key (FK) A field in a "child/dependent" table that references the primary
key of a "parent" table. Establishes relationships between
tables. Can be null (unless the entity is mandatory). Example:
CourseID in ENROLLMENT is a foreign key pointing to
COURSE.
 Candidate Key Any attribute (or set of attributes) that COULD serve as the
primary key — it uniquely identifies rows and has no null
values. There may be multiple candidates; one is chosen as
the primary key.
Composite Key A primary key made up of TWO OR MORE attributes
together. Neither attribute alone is unique, but together they
are. Common in associative tables (M:N relationships).
Surrogate Key An artificially generated key (like an auto-incremented integer)
used when no natural key exists or when natural keys are too
complex. Has no real-world meaning.

⚠ EXAM TRAP: Entity integrity rule: PRIMARY KEY attributes can NEVER be NULL. Foreign
keys, on the other hand, CAN be null (unless the relationship is mandatory).

4.4 Integrity Constraints

CONSTRAINT TYPE DESCRIPTION
Domain Constraints Restrict the allowable values for an attribute. E.g., Grade must
be between 0 and 100; Gender must be "M" or "F".
Entity Integrity No primary key attribute may be null. ALL primary key fields
MUST have values. This ensures every row is uniquely
identifiable.
Referential Integrity Any foreign key value in the child/dependent table must either
(a) match a primary key value in the parent table, OR (b) be
null. This prevents "orphan records" — rows pointing to non-
existent parent rows.

Referential Integrity — Delete Rules

When a parent record is deleted and child records reference it, one of three rules applies:
DELETE RULE BEHAVIOR
RESTRICT Do NOT allow the delete of the parent if related child rows
exist. The system will throw an error. SAFEST option.
CASCADE Automatically delete ALL child rows that correspond to the
deleted parent row. DANGEROUS — can cause data loss.
SET-TO-NULL Set the foreign key in child rows to NULL when the parent is
deleted. NOT allowed for weak entities (whose existence
depends on the parent).

⚠ EXAM TRAP: SET-TO-NULL delete rule cannot be used for WEAK ENTITIES. A weak entity
requires its parent to exist; setting its FK to null would violate that dependency.

4.5 Transforming EER Diagrams to Relations — The 7 Mappings

This is one of the most tested topics. You must know how to convert each type of EER construct into
relational tables.

Mapping 1: Regular (Strong) Entity

• Each regular entity becomes a TABLE.
• Simple attributes become COLUMNS.
• Composite attributes: use only the simple sub-attributes (not the composite attribute itself).
• Multivalued attribute: becomes a SEPARATE TABLE with a foreign key back to the original
entity.

Mapping 2: Weak Entity

• Becomes a separate TABLE.
• Includes a FOREIGN KEY from the owning strong entity.
• Primary key = COMPOSITE KEY of (partial identifier + PK of strong entity).

Mapping 3: Binary Relationships

RELATIONSHIP TYPE MAPPING RULE
One-to-Many (1:N) PK of the "one" side becomes a FOREIGN KEY in the "many"
side table. No new table needed.
Many-to-Many (M:N) Create a NEW ASSOCIATIVE TABLE. Its primary key is a
composite of the PKs of both entities. Add any relationship
attributes to this new table.
One-to-One (1:1) PK of the MANDATORY side becomes a foreign key on the
OPTIONAL side. (Place the FK where participation is optional
to allow nulls.)

Mapping 4: Associative Entities

CASE MAPPING RULE
No Identifier Assigned Default PK = composite of the PKs of the two related entities
(same as M:N mapping).
Identifier Assigned The assigned identifier becomes the PK. The entity's own PKs
are treated as regular foreign keys. Used when the composite
key may not be unique or when a simpler identifier is more
natural.

Mapping 5: Unary Relationships

CASE MAPPING RULE
Unary 1:N Add a RECURSIVE FOREIGN KEY to the same table. E.g.,
EMPLOYEE table gets a ManagerID column that references
the same table's EmployeeID.
 Unary M:N Two tables: (1) the entity table, (2) an ASSOCIATIVE TABLE
where both columns reference the entity table's PK.

Mapping 6: Ternary Relationships

• Create one relation for each of the three entity types.
• Create ONE ADDITIONAL ASSOCIATIVE TABLE for the relationship itself.
• The associative table has foreign keys pointing to all three entity tables.

Mapping 7: Supertype/Subtype Relationships

• Create ONE table for the SUPERTYPE (includes identifier + common attributes + discriminator).
• Create ONE table for EACH SUBTYPE (includes subtype-specific attributes + PK of the
supertype as its own PK).
• Each subtype table has a 1:1 relationship with the supertype table.

⚠ EXAM TRAP: In supertype/subtype mapping, the SAME attribute (the supertype's PK)
becomes the primary key in BOTH the supertype table AND each subtype table. The subtype
doesn't get a new PK — it shares the supertype's PK.

4.6 Data Normalization

Normalization is the process of organizing database tables to minimize data redundancy and eliminate
anomalies. It involves decomposing tables with problems into smaller, well-structured ones.

Why Normalize? The Three Anomalies

ANOMALY TYPE DESCRIPTION
Insertion Anomaly You cannot add new data without also including other data
that shouldn't be required. Example: You can't add a new
product to the system unless it's already on an order.
Deletion Anomaly Deleting one record accidentally destroys other valid
information. Example: Deleting the last order containing a
certain product makes you lose all information about that
product.
Modification (Update) Anomaly Changing a data value requires updating it in MULTIPLE
rows. Example: If a customer's address changes, you must
update it in every order record for that customer.

Functional Dependencies
A functional dependency means: the value of attribute A DETERMINES the value of attribute B. We
write this as A → B (A determines B, or B is functionally dependent on A).
• Example: StudentID → StudentName (knowing the StudentID tells you the student's name)
• The "determinant" is the attribute on the LEFT side of the arrow.
 • In a well-designed table, ALL non-key attributes should be functionally dependent on the
ENTIRE primary key.

First Normal Form (1NF)

A relation is in 1NF if:
• There are NO multivalued attributes (every cell has exactly one value).
• Every attribute value is ATOMIC (indivisible).
Note: By definition, all properly defined relations are in 1NF. A table with multivalued attributes (like
multiple phone numbers in one cell) is NOT a relation.

Second Normal Form (2NF)

A relation is in 2NF if:
• It is in 1NF, AND
• Every non-key attribute is FULLY FUNCTIONALLY DEPENDENT on the ENTIRE primary key
(no partial dependencies).
Partial dependency: A non-key attribute depends on only PART of a composite primary key.
2NF issues only arise when the primary key is COMPOSITE. If the PK is a single attribute, 1NF implies
2NF.
To fix 2NF violations: Move the attribute and its partial determinant into a NEW separate table.

Third Normal Form (3NF)

A relation is in 3NF if:
• It is in 2NF, AND
• There are NO TRANSITIVE DEPENDENCIES (no non-key attribute determines another non-
key attribute).
Transitive dependency: A → B → C where A is the PK, B is a non-key attribute, and C is another non-
key attribute. The PK indirectly determines C through B.
Example: OrderID → CustomerID → CustomerName. CustomerName is transitively dependent on
OrderID through CustomerID.
To fix: Move the transitively dependent attribute and its determinant into a new table. The determinant
stays as a FOREIGN KEY in the original table.

NORMAL FORM REQUIREMENT

1NF No multivalued attributes; all values atomic.
2NF 1NF + no partial dependencies (all non-key attributes depend
on ENTIRE PK).
3NF 2NF + no transitive dependencies (no non-key attributes
depend on other non-key attributes).

💡 MNEMONIC: "The KEY, the WHOLE KEY, and NOTHING BUT the KEY" → 1NF ensures
atomic values (key); 2NF ensures full dependency on whole key; 3NF ensures nothing but the
key determines non-key attributes.
 ⚠ EXAM TRAP: 2NF is ONLY relevant when the primary key is COMPOSITE. A table with a
single-attribute PK that is in 1NF is automatically in 2NF. Many students try to check for 2NF
violations on tables with single PKs — not needed.

4.7 Chapter 4 — MCQ Practice

MCQ 12: Which property of a relation states that every attribute value must be atomic?
A. Every row must be unique

✓ B. Every attribute value must be atomic

C. Column order is irrelevant
D. The relation must have a unique name
Explanation: This is the key property that distinguishes a proper relation from a raw table with
multivalued or composite cell values. It is also the definition of First Normal Form.

MCQ 13: In a relation, a FOREIGN KEY can be null UNLESS:

A. The relationship is one-to-many

✓ B. The entity is a weak entity

C. The attribute is composite
D. The parent table has more than 100 rows
Explanation: A weak entity's foreign key cannot be null because its very existence depends on the
owning strong entity. The primary key of a weak entity includes the FK from the parent, so it can't be
null.

MCQ 14: If deleting a customer record automatically deletes all their order records, which
delete rule is being applied?
A. RESTRICT
B. SET-TO-NULL

✓ C. CASCADE
D. DEFER
Explanation: CASCADE delete automatically removes all related child records when the parent record
is deleted. RESTRICT would prevent the deletion. SET-TO-NULL would null out the FK.

MCQ 15: A table has composite PK (OrderID, ProductID). ProductDescription depends only on
ProductID. This is a violation of:
A. First Normal Form

✓ B. Second Normal Form

C. Third Normal Form
D. Referential Integrity
Explanation: A partial dependency exists when a non-key attribute (ProductDescription) depends on
only PART of the composite primary key (ProductID alone). This violates 2NF.

MCQ 16: OrderID → CustomerID → CustomerAddress means CustomerAddress is _____

dependent on OrderID.
A. Partially
B. Fully

✓ C. Transitively
D. Non-functionally
Explanation: Transitive dependency: the PK (OrderID) determines CustomerID (a non-key attribute),
which in turn determines CustomerAddress (another non-key attribute). This violates 3NF.

4.8 Chapter 4 — Key Takeaways

★ KEY POINT: 6 properties of a relation: Unique name, Atomic values, Unique rows, Unique
column names, Column order irrelevant, Row order irrelevant.
★ KEY POINT: PK = unique identifier, cannot be null. FK = references PK of another table, can
be null.
★ KEY POINT: Delete rules: RESTRICT (safest), CASCADE (dangerous), SET-TO-NULL (not
for weak entities).
★ KEY POINT: 1NF = atomic values. 2NF = 1NF + full functional dependency on entire
composite PK. 3NF = 2NF + no transitive dependencies.
★ KEY POINT: M:N mapping = new associative table with composite PK. Weak entity mapping
= composite PK of (partial id + parent PK).
CHAPTER 5: INTRODUCTION TO SQL
5.1 What is SQL?
SQL (Structured Query Language, often pronounced "Sequel") is the standard language for Relational
Database Management Systems (RDBMS). Almost every major database system — MySQL, Oracle,
SQL Server, PostgreSQL — uses SQL.

Brief History of SQL

YEAR MILESTONE
1970 E. F. Codd develops the relational database concept.
1974-79 System R with Sequel (later SQL) created at IBM Research
Lab.
1979 Oracle markets the first commercial RDBMS with SQL.
1986 ANSI SQL standard released. Updated in 1989, 1992, 1999,
2003, 2006, 2008, 2011, 2016.
Today SQL is supported by virtually all major database vendors.

5.2 SQL Sub-Languages

SUB-LANGUAGE PURPOSE & COMMANDS
DDL — Data Definition Language Defines the structure of the database. Commands: CREATE,
ALTER, DROP. Used to create tables, modify them, or delete
them.
DML — Data Manipulation Maintains and queries data. Commands: SELECT, INSERT,
Language UPDATE, DELETE, MERGE.
DCL — Data Control Language Controls access and permissions. Commands: GRANT,
REVOKE. Used by the DBA.

5.3 Creating Tables — DDL

The CREATE TABLE statement defines a new table, its columns, data types, and constraints.

General syntax:
CREATE TABLE TableName ( ColumnName DataType [NOT NULL] [DEFAULT value]
[UNIQUE], ColumnName DataType, PRIMARY KEY (ColumnName), FOREIGN KEY
(ColumnName) REFERENCES OtherTable(OtherColumn) ON DELETE [RESTRICT |
CASCADE | SET NULL] );

Common SQL Data Types

DATA TYPE DESCRIPTION & USE CASE
 VARCHAR2(n) / VARCHAR(n) Variable-length text string, up to n characters. Only uses
space needed. Best for variable-length text like names.
CHAR(n) Fixed-length text string, always n characters. Best for fixed
codes like state abbreviations (e.g., "GH").
NUMBER / INT Numeric values. NUMBER(5) = 5-digit integer. NUMBER(5,2)
= 5 digits with 2 decimal places.
DATE Stores date and time. Can represent dates from 4712 BC to
9999 AD.
TIMESTAMP Like DATE but can include fractional seconds and time zones.
BLOB Binary Large Object — up to 4GB. Used for photos, videos,
audio files.
CLOB Character Large Object — up to 4GB. Used for large text
fields like documents.
BOOLEAN True or False values.

Steps in Creating a Table

12. Identify the data type for each attribute.
13. Identify which columns can and cannot be null.
14. Identify columns that must be unique (candidate keys).
15. Identify primary key–foreign key relationships.
16. Determine default values.
17. Identify domain constraints.
18. Write the CREATE TABLE statement and create indexes.

5.4 Modifying and Removing Tables

COMMAND PURPOSE & SYNTAX
ALTER TABLE Modifies the structure of an existing table. Can add columns,
change data types, add/remove constraints. E.g., ALTER
TABLE CUSTOMER_T ADD CustomerType VARCHAR2(10)
DEFAULT 'Commercial';
DROP TABLE Removes an entire table from the database. All data is
permanently deleted. E.g., DROP TABLE Customer_T;

5.5 DML — Manipulating Data

INSERT Statement
Adds one or more rows to a table.
-- Insert with all values INSERT INTO Customer_T VALUES (001, 'Contemporary
Casuals', '1355 S. Himes', 'Gainesville', 'FL', '32601'); -- Insert with
specific columns (for NULLs in other columns) INSERT INTO Product_T
(ProductID, ProductDescription, ProductFinish) VALUES (1, 'End Table',
 'Cherry'); -- Insert from another table INSERT INTO CaCustomer_T SELECT *
FROM Customer_T WHERE CustomerState = 'CA';

UPDATE Statement
Modifies existing data in a table.
UPDATE Product_T SET ProductStandardPrice = 775 WHERE ProductID = 7;

DELETE Statement
-- Delete specific rows DELETE FROM Customer_T WHERE CustomerState = 'HI'; -
- Delete ALL rows (table structure remains) DELETE FROM Customer_T;

5.6 SELECT Statement — The Power of SQL

The SELECT statement is used for ALL queries. It can retrieve data from one or multiple tables.
CLAUSE PURPOSE
SELECT List the columns (and expressions) to return. Use * for all
columns.
FROM Specify the table(s) or view(s) to query.
WHERE Filter rows based on conditions.
GROUP BY Categorize results into groups (used with aggregate
functions).
HAVING Filter groups (like WHERE but for GROUP BY results).
ORDER BY Sort the results.

SQL Processing Order

CRITICAL: SQL clauses are processed in a specific order — NOT the order they are written:
STEP CLAUSE
Step 1 FROM — identifies the tables to use.
Step 2 WHERE — filters individual rows.
Step 3 GROUP BY — groups the remaining rows.
Step 4 HAVING — filters groups.
Step 5 SELECT — selects the columns and applies expressions.
Step 6 ORDER BY — sorts the final result.

⚠ EXAM TRAP: You CANNOT use WHERE to filter aggregates (like WHERE COUNT(*) > 5)
— you must use HAVING for that. WHERE filters rows BEFORE grouping; HAVING filters
groups AFTER grouping.
SELECT Examples
-- Basic query SELECT ProductDescription, ProductStandardPrice FROM Product_T
WHERE ProductStandardPrice < 275; -- Using aliases SELECT [Link]
AS Name, [Link] FROM Customer_T AS Cust WHERE Name = 'Home
Furnishings'; -- COUNT aggregate function SELECT COUNT(*) FROM OrderLine_T
WHERE OrderID = 1004; -- Sorting SELECT CustomerName, CustomerCity,
CustomerState FROM Customer_T WHERE CustomerState IN ('FL', 'TX', 'CA', 'HI')
ORDER BY CustomerState, CustomerName; -- GROUP BY and HAVING SELECT
CustomerState, COUNT(CustomerState) FROM Customer_T GROUP BY CustomerState
HAVING COUNT(CustomerState) > 1;

Boolean Operators and Precedence

The WHERE clause can use AND, OR, and NOT. Default processing order is:
19. NOT (processed first)
20. AND (processed second)
21. OR (processed last)
Use PARENTHESES to override this default order. Without parentheses, AND binds more tightly than
OR.

Example without parentheses (potentially wrong result):

WHERE ProductDescription LIKE '%Desk' OR ProductDescription LIKE '%Table' AND
ProductStandardPrice > 300; -- This means: (Desk) OR (Table AND price > 300)
— probably not what you want!
Example with parentheses (correct):
WHERE (ProductDescription LIKE '%Desk' OR ProductDescription LIKE '%Table')
AND ProductStandardPrice > 300; -- This means: (Desk OR Table) AND price >
300 — correct!

Comparison Operators in SQL

OPERATOR MEANING
= Equal to
> / >= Greater than / Greater than or equal to
< / <= Less than / Less than or equal to
<> or != Not equal to
LIKE Pattern matching. % = any characters; _ = single character
IN Value is in a list. E.g., WHERE State IN ('FL', 'TX')
BETWEEN Value is within a range. E.g., WHERE Price BETWEEN 100
AND 500
IS NULL / IS NOT NULL Checks for null values
Aggregate Functions
FUNCTION DESCRIPTION
COUNT(*) Counts the number of rows.
COUNT(column) Counts non-null values in a column.
SUM(column) Adds all values in a column.
AVG(column) Calculates the average value.
MAX(column) Returns the highest value.
MIN(column) Returns the lowest value.

Creating Indexes
Indexes speed up data retrieval. The DBMS automatically creates indexes for primary keys.
-- Unique index (for PK or unique fields) CREATE UNIQUE INDEX CustIndex_PK ON
Customer_T(CustomerID); -- Non-unique index (for frequently searched fields)
CREATE INDEX DescIndex ON Product_T(ProductDescription);

5.7 Chapter 5 — MCQ Practice

MCQ 17: Which SQL command is used to modify the structure of an existing table (e.g., add a
column)?
A. UPDATE
B. MODIFY

✓ C. ALTER TABLE
D. CREATE TABLE
Explanation: ALTER TABLE is the DDL command for modifying an existing table structure. UPDATE
is DML and changes data values, not structure.

MCQ 18: In SQL processing order, which clause is executed FIRST?

A. SELECT
B. WHERE

✓ C. FROM
D. GROUP BY
Explanation: The FROM clause is always processed first — SQL must know which table(s) to work
with before it can filter, group, or select. The written order (SELECT...FROM...) is different from the
processing order.

MCQ 19: You want to find states with more than 3 customers. Which clause do you use to filter
the grouped results?
A. WHERE
✓ B. HAVING
C. AND
D. FILTER
Explanation: HAVING is like a WHERE clause but operates on GROUP BY results
(groups/aggregates). WHERE filters individual rows BEFORE grouping; HAVING filters groups AFTER
grouping.

MCQ 20: Which SQL operator allows pattern matching using the % wildcard?
A. IN
B. BETWEEN

✓ C. LIKE
D. MATCH
Explanation: The LIKE operator is used for pattern matching. % represents any number of characters;
_ represents exactly one character. Example: WHERE Name LIKE 'Sam%' finds names starting with
"Sam".

5.8 Chapter 5 — Key Takeaways

★ KEY POINT: SQL sub-languages: DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT,
UPDATE, DELETE), DCL (GRANT, REVOKE).
★ KEY POINT: CREATE TABLE defines structure. ALTER TABLE modifies structure. DROP
TABLE removes the table.
★ KEY POINT: SELECT processing order: FROM → WHERE → GROUP BY → HAVING →
SELECT → ORDER BY.
★ KEY POINT: WHERE filters rows. HAVING filters groups (after GROUP BY). You cannot
filter aggregates with WHERE.
★ KEY POINT: AND has higher precedence than OR. Use parentheses to control evaluation
order.
CHAPTER 8: PHYSICAL DATABASE DESIGN &
DATABASE INFRASTRUCTURE
8.1 What is Physical Database Design?
Physical database design is the process of translating the logical database design (E-R model,
normalized relations) into TECHNICAL SPECIFICATIONS for how data will actually be stored on
physical storage media (hard disks, SSDs).
ASPECT DESCRIPTION
Purpose Translate logical description of data into technical storage
specifications.
Goal Create a design that provides adequate performance while
ensuring integrity, security, and recoverability.
Contrast with Logical Design Logical design = WHAT data and relationships exist. Physical
design = HOW and WHERE data is stored.

8.2 Critical Decisions in Physical Design

22. Choose the storage format (data type) for each attribute.
23. Group attributes into physical records (clustering).
24. Choose how to arrange records in storage (file organization).
25. Select structures for efficient retrieval (indexes).
26. Prepare strategies for query optimization.

8.3 Denormalization
Denormalization is the DELIBERATE process of introducing some redundancy back into a normalized
database to IMPROVE PERFORMANCE.

ASPECT DESCRIPTION
Benefits Faster query performance by reducing the number of JOIN
operations needed. Fewer table lookups = faster responses.
Costs 1. Wasted storage space (due to duplicate data). 2. Data
integrity/consistency threats (same data in multiple places can
become inconsistent).
When to Use Only when performance problems have been clearly identified
and other optimization methods (indexing, query tuning) are
insufficient.

⚠ EXAM TRAP: Denormalization is NOT the same as poor database design — it is a

DELIBERATE, CONTROLLED trade-off of some data integrity for better performance. But use it
cautiously — it can reintroduce anomalies.
8.4 Partitioning
TYPE DESCRIPTION
Horizontal Partitioning Distributing ROWS of a table into separate physical tables.
Useful when different user groups need different subsets of
rows. Three types: Key Range, Hash, Composite.
Vertical Partitioning Distributing COLUMNS of a table into separate physical
tables. Useful when different users need access to different
columns. The PRIMARY KEY must be repeated in every
partition.

ASPECT PARTITIONING CONSIDERATIONS

Advantages Efficiency (data used together stays together), local
optimization, security (separate sensitive columns), faster
backup, load balancing.
Disadvantages Slower retrievals when data spans multiple partitions, added
complexity, duplicate data in vertical partitioning.

8.5 File Organizations

File organization determines how records are physically arranged on storage media.
TYPE DESCRIPTION
Heap (Unordered) Records stored in no particular order — just appended as they
arrive. Simple but slow for searching. Good for bulk loading.
Sequential (Ordered) Records stored in physical order based on a key field. Fast for
sequential reading (reports), but slow for random access and
inserts.
Indexed Records stored with an index that allows quick location of
specific records. The index is like a book's table of contents.
Supports both sequential and random access.
Hashed A hash function maps the key value to a storage address
directly. Extremely fast for exact-match queries (like finding a
specific CustomerID). Poor for range queries.

8.6 Indexes
An index is a data structure (separate from the main table) that stores key values and pointers to the
physical locations of records. Like an index at the back of a textbook.
INDEX TYPE WHEN TO USE
Primary Keys Automatically indexed by the DBMS.
Secondary Keys Fields that are frequently used in WHERE clauses can also be
indexed.
 Unique Index For primary keys or other unique fields. Guarantees no
duplicate values in the indexed column.
Non-Unique Index For fields used for grouping (e.g., zip code, product category).
Allows duplicate values.

When to Use Indexes

27. Use on LARGE tables — small tables don't benefit much from indexing.
28. Index the PRIMARY KEY of every table.
29. Index fields frequently used in WHERE clauses (search fields).
30. Index fields used in ORDER BY and GROUP BY.
31. Use when column has 100+ distinct values (not worth it for <30 values).
32. Avoid indexing fields with very LONG values — consider compression first.
33. Be careful with NULL values — many DBMSs won't recognize nulls in index searches.
34. Be aware of DBMS limits on indexes per table and bytes per indexed field.

8.7 Database Security

Security Features
SECURITY FEATURE DESCRIPTION
Views/Subschemas Limit users to seeing only a subset of the database. A user
can have SELECT privilege on a view but no access to the
underlying tables.
Authorization Rules Matrix of subjects (users, roles), objects (tables, views),
actions (read, insert, modify, delete), and constraints.
Integrity Controls Domains (allowable values), Assertions (conditions that must
always be true), Triggers (automatic responses to certain
events).
Encryption Scrambles data so it's unreadable without the decryption key.
SSL (Secure Sockets Layer) is a common encryption scheme
for database connections over the internet.
Authentication Verifying user identity. Passwords (first line of defense), two-
factor authentication (smart card + PIN), three-factor (smart
card + biometric + PIN).

8.8 Database Recovery

Recovery mechanisms restore the database to a correct state after failure or data loss.

Recovery Facilities
FACILITY DESCRIPTION
Backup Facilities Periodic complete copy of the database. Cold backup (DB
shut down during backup) or Hot backup (partial shutdown).
Stored off-site.
Journalizing (Transaction Log) Audit trail of all transactions. Records before-images (data
before change) and after-images (data after change).
Essential for recovery.
Checkpoint Facility DBMS periodically refuses new transactions and synchronizes
the database and logs. Recovery only needs to go back to the
last checkpoint.
Recovery Manager DBMS module that restores the database using logs and
backups when a failure occurs.

Recovery Techniques
TECHNIQUE DESCRIPTION
Backward Recovery (Rollback) Uses BEFORE-IMAGES to undo changes. Restores database
to state BEFORE the failed transaction began. Used for
aborted transactions.
Forward Recovery (Roll Forward) Uses AFTER-IMAGES to redo changes. Reapplies all
committed transactions after a system failure. Preferred over
restore/rerun.
Disk Mirroring Maintaining an identical duplicate database. Switch instantly
to the mirror if the primary fails.
Restore/Rerun Reprocess all transactions against the backup copy. Last
resort — very slow.

⚠ EXAM TRAP: Rollback = uses BEFORE images = undoes transactions = goes BACKWARD.
Roll Forward = uses AFTER images = redoes transactions = goes FORWARD. These are exact
opposites.
💡 MNEMONIC: "Roll Back → Before images → Undo. Roll Forward → After images → Redo."

8.9 Cloud-Based Database Management Services

SERVICE TYPE DESCRIPTION
IaaS (Infrastructure as a Service) Cloud provider offers hardware and basic infrastructure.
Customer manages OS, middleware, and database software.
PaaS (Platform as a Service) Cloud provider offers hardware + systems software platform.
Customer manages applications and data.
SaaS (Software as a Service) Cloud provider offers complete software applications.
Customer just uses the software.
DBaaS (Database as a Service) Cloud provider manages the entire database infrastructure.
Customer just uses the database.
ASPECT CLOUD DATABASE CONSIDERATIONS
Benefits No upfront hardware investment, lower expertise needed,
flexible capacity, easy to adopt new technologies, high
availability from mature providers.
Downsides Limited automatic capacity adaptation, consistency challenges
in distributed environments, difficult migration, hard to monitor
SLA compliance, ACID support challenges.

8.10 Chapter 8 — MCQ Practice

MCQ 21: Deliberately introducing redundancy into a database to improve query performance is
called:
A. Normalization

✓ B. Denormalization
C. Partitioning
D. Indexing
Explanation: Denormalization is the intentional process of adding redundancy back to a normalized
database to reduce the number of joins needed for frequently run queries, thus improving performance.

MCQ 22: Which recovery technique uses BEFORE-IMAGES to undo changes and restore the
database to a prior state?
A. Forward Recovery (Roll Forward)

✓ B. Backward Recovery (Rollback)

C. Disk Mirroring
D. Checkpoint
Explanation: Backward Recovery (Rollback) uses before-images to undo committed or uncommitted
changes. Roll Forward uses after-images to redo transactions.

MCQ 23: Distributing the ROWS of a table into multiple physical files based on a key range is
called:
A. Vertical partitioning

✓ B. Horizontal partitioning
C. Denormalization
D. Hash organization
Explanation: Horizontal partitioning splits rows into separate physical files. Vertical partitioning splits
columns. The three types of horizontal partitioning are Key Range, Hash, and Composite.

8.11 Chapter 8 — Key Takeaways

★ KEY POINT: Physical design = HOW data is stored. Goal: performance + integrity + security
+ recoverability.
★ KEY POINT: Denormalization = deliberate redundancy for performance. Use cautiously —
can reintroduce anomalies.
★ KEY POINT: Horizontal partitioning = rows. Vertical partitioning = columns (PK must be
repeated in each).
★ KEY POINT: File organizations: Heap (unordered), Sequential (ordered), Indexed, Hashed.
★ KEY POINT: Recovery: Rollback (before images, undo), Roll Forward (after images, redo).
Checkpoint reduces recovery time.
★ KEY POINT: Cloud services: IaaS, PaaS, SaaS, DBaaS. Benefits: flexibility, no hardware
cost. Risks: consistency, SLA monitoring.
CHAPTER 9: DATA WAREHOUSING & DATA
INTEGRATION
9.1 What is a Data Warehouse?
A data warehouse is a specialized database designed for DECISION SUPPORT and ANALYSIS, not
for day-to-day operations. It has four defining characteristics:
CHARACTERISTIC DESCRIPTION
Subject-Oriented Organized around key business subjects (customers,
products, sales, students) rather than business processes.
Integrated Combines data from MULTIPLE sources (different operational
databases) with consistent naming conventions and formats.
Time-Variant Contains HISTORICAL data — allows you to study trends
over time. Includes timestamps and date dimensions.
Non-Updatable (Read-Only) Data in the warehouse is READ-ONLY. It is periodically
refreshed from operational systems but not updated in real
time.

⚠ EXAM TRAP: A data warehouse is NON-UPDATABLE (read-only). You do not update

warehouse data in real time like an operational database. It is periodically REFRESHED from
source systems.

Data Warehouse vs Data Mart

FEATURE DATA WAREHOUSE vs DATA MART
Data Warehouse Enterprise-wide, application-independent, multiple subjects,
centralized, planned, large, contains historical + detailed +
summarized data, lightly denormalized.
Data Mart Limited scope (one department or subject area),
decentralized, may not be planned, starts small, highly
denormalized, few data sources.

9.2 Operational Systems vs Informational Systems

CHARACTERISTIC |
OPERATIONAL | INFORMATIONAL
Primary Purpose Run the business in real time
Type of Data Current state of the business
Users Clerks, salespersons, operators
Queries Simple, narrow, planned (e.g., add an order)
Design Goal Performance, throughput, availability
Data Volume Many small, constant updates
9.3 Data Warehouse Architectures
ARCHITECTURE TYPE DESCRIPTION
Independent Data Mart Separate ETL process for each data mart. Simple but leads to
data inconsistency and redundant ETL.
Dependent Data Mart + ODS Single Enterprise Data Warehouse (EDW) as the central
store. Dependent data marts are loaded FROM the EDW.
Operational Data Store (ODS) provides near-real-time
transformed data.
Logical Data Mart + Real-Time DW ODS and data warehouse are unified. Data marts are logical
views (not separate databases) of the DW. Easy to add new
marts.
Three-Layer Architecture Reconciled Layer (normalized, current data) → Enterprise DW
Layer (historical, consolidated) → Data Mart Layer (subject-
specific, denormalized).

9.4 The Star Schema

The star schema is the most common data model used in data warehouses. It is specifically designed
for fast query performance on analytical (decision support) queries.

COMPONENT DESCRIPTION
Fact Table The CENTRAL table in the star schema. Contains
QUANTITATIVE (numeric, measurable) data — the "facts" of
the business. E.g., SalesAmount, Quantity, Revenue. Also
contains foreign keys to all dimension tables.
Dimension Tables Tables SURROUNDING the fact table. Contain
DESCRIPTIVE attributes about the business subjects. E.g.,
Product dimension has ProductName, Category, Color. Time
dimension has Year, Quarter, Month, Day.

Example Star Schema: SALES fact table with dimensions:

• Time Dimension (date, year, quarter, month)
• Product Dimension (productID, name, category, price)
• Store Dimension (storeID, city, region, manager)
• Customer Dimension (customerID, name, segment, region)

⚠ EXAM TRAP: The star schema is excellent for ad-hoc analytical queries but BAD for OLTP
(Online Transaction Processing). Do not confuse a star schema with a normalized operational
database design.

Calculating Fact Table Size

Number of rows = product (multiplication) of the number of possible values for each dimension.
Example from the slides:
• 1,000 stores × 10,000 products × 24 months = 240,000,000 potential rows
• But if only 50% of products have sales in a given month: 1,000 × 5,000 × 24 = 120,000,000
actual rows

Surrogate Keys in Dimension Tables

Dimension table keys should be SURROGATE KEYS (artificially generated, non-intelligent keys like
auto-increment integers) because:
• Business keys (natural keys) can change over time
• Surrogate keys are simpler, shorter, and consistent in format
• They allow tracking historical changes when natural keys change

Grain of the Fact Table

Grain = the level of detail in each row of the fact table.
GRAIN TYPE DESCRIPTION
Transactional Grain Finest level — one row per transaction. Most flexible for
analysis. Example: one row per individual sale.
Aggregated Grain More summarized — one row per day per store, for example.
Faster queries but less flexible.
Finer grain = more rows, more dimensions, better analysis. Coarser grain = fewer rows, faster queries,
less flexible.

Slowly Changing Dimensions (SCD)

How should the data warehouse handle when a dimension attribute changes? E.g., a customer moves
to a new city.
SCD TYPE APPROACH
Type 1 (Overwrite) Simply replace the old value with the new one. LOSE
historical data. Simplest approach.
Type 2 (Add new row) Create a NEW ROW in the dimension table each time a
dimension attribute changes, recording all attributes at that
point in time. MOST COMMON approach. Preserves full
history.
Type 3 (Add new column) Add a "previous value" column to the dimension table. Limited
history (only current + one previous value).

9.5 ETL — Extract, Transform, Load

ETL is the process of moving data FROM operational source systems INTO the data warehouse.
ETL STEP DESCRIPTION
 Extract (Capture) Identify and pull changed data from source systems. Changed
Data Capture (CDC) identifies which records have changed
since the last extract.
Transform (Scrub/Cleanse) Clean the data: fix inconsistencies, standardize formats,
resolve synonym/homonym conflicts, fill missing values. This
is the hardest and most time-consuming step.
Load (& Index) Insert the cleaned, transformed data into the warehouse.
Build/update indexes. This is typically done in large batch
operations.

Data Integration Techniques

TECHNIQUE DESCRIPTION
ETL/Consolidation Physically move and transform data into a central database.
Provides the best query performance since all data is local.
EII — Data Federation Create a VIRTUAL view of data across multiple sources
without actually moving it. Data stays in place; queries are
sent to each source and results combined. Lower latency but
slower queries.
EAI/EDR — Data Propagation Replicate data across databases in near-real-time. Good for
keeping multiple systems synchronized.

9.6 Chapter 9 — MCQ Practice

MCQ 24: A data warehouse is described as "non-updatable." This means:
A. Data cannot be loaded into it

✓ B. Users can only read data; updates happen via periodic refresh from source systems
C. The data model cannot be changed
D. Only the DBA can access it
Explanation: Non-updatable means end users READ data from the warehouse; they don't update it
directly. The warehouse is refreshed periodically (e.g., nightly) from operational source systems via
ETL.

MCQ 25: In a star schema, which table contains the quantitative (measurable) business data
and foreign keys to all surrounding tables?
A. Dimension table

✓ B. Fact table
C. Lookup table
D. Surrogate table
Explanation: The FACT table is at the center of the star schema and contains the
numeric/measurable data (facts) along with foreign keys pointing to all dimension tables.
MCQ 26: The ETL step where data inconsistencies are fixed and formats are standardized is
called:
A. Extract
B. Load

✓ C. Transform (Scrub/Cleanse)
D. Index
Explanation: The Transform step (also called scrubbing or data cleansing) is where data quality
issues are resolved — fixing formats, resolving naming conflicts, filling missing values, etc.

9.7 Chapter 9 — Key Takeaways

★ KEY POINT: Data warehouse: Subject-Oriented, Integrated, Time-Variant, Non-Updatable
(SITN).
★ KEY POINT: Operational systems = run the business (real-time, OLTP). Informational
systems = support decisions (historical, OLAP).
★ KEY POINT: Star schema = central Fact table + surrounding Dimension tables. Designed for
fast analytical queries.
★ KEY POINT: Fact table rows = product of dimension values (stores × products × time
periods).
★ KEY POINT: SCD Type 2 (add new row) is the most common approach for handling slowly
changing dimensions.
★ KEY POINT: ETL = Extract → Transform → Load. Transform is the hardest step.
CHAPTER 10: BIG DATA TECHNOLOGIES & NoSQL
10.1 What is Big Data?
Big Data refers to extremely large datasets that cannot be efficiently managed by traditional relational
databases. The 5 V's define it:
V DESCRIPTION
Volume Much larger quantity of data than typical for relational
databases (petabytes to exabytes).
Variety Lots of different data types and formats — structured, semi-
structured (JSON, XML), and unstructured (text, images,
video).
Velocity Data arrives at very high speed — social media streams, IoT
sensors, web clickstreams generating data continuously.
Veracity Traditional data quality methods don't apply well. Uncertainty
about data accuracy and relevance.
Value Big data is meaningless if it doesn't provide business value.
The goal is to extract actionable insights.

💡 MNEMONIC: The 5 V's of Big Data: "Very Very Verbose Volumes Vary" → Volume, Variety,
Velocity, Veracity, Value.

Schema on Write vs Schema on Read

APPROACH DESCRIPTION
Schema on Write (Traditional The data model is defined BEFORE data is loaded. All data
RDBMS) must conform to the schema. Any data that doesn't fit is
rejected. Example: MySQL, Oracle.
Schema on Read (Big Data) Data is loaded first WITHOUT a predefined structure. The
schema is applied when you READ the data, based on how
you want to use it. Much more flexible. Example: Hadoop,
Data Lakes.

Data Lake: A large integrated repository that stores all types of internal and external data without a
predefined schema. "Capture everything, dive in anywhere, flexible access."

10.2 NoSQL Databases

NoSQL = "Not Only SQL." A category of database technologies that move beyond the relational model
to handle big data needs.

CHARACTERISTIC NOSQL FEATURE

Scaling Model Scale OUT (add more commodity servers) rather than Scale
UP (add more power to one server). Better for cloud
environments.
Schema Schema on read — flexible, don't need predefined structure.
ACID NOT fully ACID compliant! Uses BASE instead.
Source Largely open source.

BASE vs ACID
MODEL DESCRIPTION
ACID (Traditional RDBMS) Atomicity (all or nothing), Consistency (database stays valid),
Isolation (transactions don't interfere), Durability (committed
changes persist).
BASE (NoSQL) Basically Available (system is usually available), Soft State
(data may change even without input due to eventual
consistency), Eventually Consistent (all replicas will eventually
become consistent, but not immediately).

⚠ EXAM TRAP: NoSQL databases are NOT ACID compliant — they use BASE semantics.
This is a major trade-off: NoSQL gains scale and flexibility but sacrifices strict consistency.
Traditional RDBMS are ACID; NoSQL is BASE.

10.3 NoSQL Database Categories

NOSQL TYPE DESCRIPTION & EXAMPLE
Key-Value Stores Simplest type. Stores key-value pairs. Database has no
knowledge of the structure of values. Extremely fast for
lookups by key. Example: Redis. Use case: session
management, caching.
Document Stores Like key-value but the "value" is a structured DOCUMENT
(JSON or BSON format). Specific elements of the document
can be queried and updated individually. Example: MongoDB.
Use case: content management, user profiles.
Wide-Column Stores Like a table with rows and columns, but columns can vary per
row and are grouped into "column families." Highly scalable.
Example: Apache Cassandra. Use case: IoT data, time-series
data.
Graph-Oriented Stores data as NODES (entities) connected by EDGES
(relationships). Both nodes and edges can have properties.
Best for highly interconnected data. Example: Neo4j. Use
case: social networks, fraud detection, recommendation
engines.

MongoDB — A Document Store

 CONCEPT DESCRIPTION
Collections Equivalent to TABLES in a relational database. A set of
documents stored together.
Documents Equivalent to ROWS. Individual JSON/BSON records.
Documents in the same collection do NOT need to have the
same structure (unlike rows in an RDBMS table).
_id field Every document has a unique _id field — equivalent to a
primary key.
Relationships Represented by embedding one document inside another, or
by storing an _id reference in one document pointing to
another (like a FK).
Storage Format BSON — Binary JSON. More efficient than plain JSON for
storage and retrieval.

⚠ EXAM TRAP: In MongoDB, documents in the same collection do NOT have to have the
same structure. This is fundamentally different from an RDBMS where all rows in a table must
have the same columns.

10.4 Hadoop Ecosystem

Hadoop is the dominant open-source framework for processing big data. It implements the MapReduce
algorithm.
COMPONENT DESCRIPTION
Hadoop The overarching open-source framework. Core components:
HDFS + MapReduce + YARN.
HDFS (Hadoop Distributed File A file system (NOT a DBMS) that breaks data into blocks and
System) distributes them across many nodes in a cluster. NOT
relational.
NameNode The "master server" in HDFS — manages the metadata
(where all the blocks are stored).
DataNodes The "slave" servers — actually store the data blocks.
YARN "Yet Another Resource Allocator" — manages and schedules
computational resources across the cluster.
MapReduce The algorithm for massively parallel processing of data across
many nodes.

How MapReduce Works

Core idea: Divide a large problem so many nodes can work on parts simultaneously.
35. Map Stage: Each node processes its LOCAL data and produces intermediate key-value pairs.
36. Reduce Stage: Results from all map processes are collected and integrated
(aggregated/combined).
Classic example — Word Count:
• Map: For each word in each chunk of text, emit (word, 1)
• Reduce: Sum all the 1s for each word → (word, totalCount)

Other Hadoop Components

COMPONENT DESCRIPTION
Pig A scripting language and execution environment that
simplifies writing MapReduce programs. FILTER is like SQL
WHERE; FOREACH/GENERATE is like SQL SELECT. Useful
development tool.
Hive SQL-like language (HiveQL) for querying and managing large
datasets stored in HDFS. Translates SQL-like queries into
MapReduce jobs. Excellent for ETL tasks.
HBase A wide-column NoSQL database that runs ON TOP of HDFS.
Provides random, real-time read/write access to big data
stored in Hadoop.

10.5 NoSQL vs Relational — When to Use What

DATABASE TYPE WHEN TO USE
Relational Database (RDBMS) Use when: Data is structured and consistent in format. Need
full ACID transactions. Data fits well in tables with clear
relationships. Applications involve complex queries and joins.
Compliance/regulatory requirements demand ACID.
NoSQL Database Use when: Handling massive scale and high velocity data.
Data is variable in structure (semi- or unstructured). Need to
scale horizontally across many servers. Eventual consistency
is acceptable. Use cases: social media, IoT, real-time
analytics.

10.6 Chapter 10 — MCQ Practice

MCQ 27: Which of the 5 V's of Big Data refers to the speed at which data is generated and must
be processed?
A. Volume
B. Variety

✓ C. Velocity
D. Veracity
Explanation: Velocity = how FAST data arrives (e.g., Twitter feeds, IoT sensors, web clickstreams).
Volume = how MUCH data. Variety = how many TYPES. Veracity = data quality/accuracy. Value =
business usefulness.
MCQ 28: Which NoSQL database type is best suited for storing and querying HIGHLY
INTERCONNECTED data like social networks?
A. Key-Value Store
B. Document Store
C. Wide-Column Store

✓ D. Graph-Oriented Database

Explanation: Graph databases store data as nodes and edges (relationships), making them ideal for
social networks, recommendation systems, and fraud detection where relationships between entities
are central.

MCQ 29: In Hadoop's MapReduce, the Map stage:

A. Integrates and aggregates results from all nodes

✓ B. Processes local data on each node and produces intermediate key-value pairs
C. Stores data blocks across the cluster
D. Manages resource allocation across the cluster
Explanation: The Map stage processes LOCAL data on each individual node and produces
intermediate key-value pairs. The REDUCE stage then integrates and aggregates these results.

MCQ 30: NoSQL databases use BASE semantics. What does "Eventually Consistent" mean in
BASE?
A. The database is always consistent at all times

✓ B. All replicas will eventually reach the same consistent state, but may be temporarily
inconsistent
C. Only one node maintains the correct data
D. Consistency is not guaranteed at all
Explanation: "Eventually Consistent" means all replica nodes will EVENTUALLY agree on the same
data values, but there may be a short window of inconsistency immediately after an update
propagates. This is the trade-off NoSQL makes for high availability and scalability.

10.7 Chapter 10 — Key Takeaways

★ KEY POINT: 5 V's of Big Data: Volume, Variety, Velocity, Veracity, Value.
★ KEY POINT: Schema on Write (RDBMS) = schema before data. Schema on Read (Big Data)
= schema applied when reading.
★ KEY POINT: NoSQL = Not Only SQL. Uses BASE (not ACID). 4 types: Key-Value,
Document, Wide-Column, Graph.
★ KEY POINT: MongoDB = Document Store. Collections ≈ Tables. Documents ≈ Rows. _id =
Primary Key. Documents can have different structures.
★ KEY POINT: Hadoop = HDFS (storage) + MapReduce (processing) + YARN (resource
management). Map = divide and process locally. Reduce = combine results.
★ KEY POINT: Pig = scripting for MapReduce. Hive = SQL-like language for Hadoop data.
HBase = wide-column store on HDFS.
COURSE-WIDE SUMMARY: CONNECTIONS BETWEEN
TOPICS
How the Chapters Flow Together
Understanding how the chapters connect helps you see the "big picture" of database management:

CHAPTER ROLE IN THE COURSE

Ch. 1: Foundation Establishes WHY we need databases (file processing
problems) and the overall database environment and
development process.
Ch. 2: E-R Modeling The FIRST step in database design — modeling real-world
data as entities, attributes, and relationships.
Ch. 3: EER Modeling EXTENDS E-R modeling with advanced constructs
(inheritance, specialization) for complex real-world scenarios.
Ch. 4: Logical Design TRANSLATES the E-R/EER model into relational tables
(relations) and normalizes them to eliminate anomalies.
Ch. 5: SQL IMPLEMENTS the logical design using SQL — creates tables,
loads data, and queries it.
Ch. 8: Physical Design OPTIMIZES the implementation — how data is stored,
indexed, secured, and recovered.
Ch. 9: Data Warehousing EXTENDS databases beyond operational use to
ANALYTICAL use — supporting management decision
making with historical data.
Ch. 10: Big Data EXTENDS beyond traditional relational databases to handle
extreme scale, variety, and velocity of modern data.

High-Priority Exam Areas

🔔 TOP PRIORITY: 3-Schema Architecture (External/Conceptual/Internal), Advantages of
databases over file processing, Types of keys (PK, FK, candidate, composite)
🔔 TOP PRIORITY: E-R constructs (entities, attributes, relationships, cardinality, degree),
Weak entities and identifying relationships
🔔 TOP PRIORITY: EER constraints: completeness (total/partial) and disjointness
(disjoint/overlap). Generalization vs specialization
🔔 TOP PRIORITY: Normalization (1NF, 2NF, 3NF) — definitions and how to apply them.
Functional dependencies. Anomalies.
🔔 TOP PRIORITY: EER-to-Relation mappings (especially weak entities, M:N,
supertype/subtype)
🔔 TOP PRIORITY: SQL SELECT statement — all clauses, processing order, WHERE vs
HAVING, GROUP BY, aggregate functions
 🔔 TOP PRIORITY: Data warehouse characteristics (SITN), Star schema (fact vs dimension
tables), ETL steps
🔔 TOP PRIORITY: Big Data 5 V's, NoSQL types (Key-Value, Document, Wide-Column,
Graph), ACID vs BASE

Final Cheat Sheet — Master List of Key Terms

TERM DEFINITION/DESCRIPTION
Database Organized collection of logically related data
Metadata Data about data
DBMS Software that manages the database
3-Schema Architecture External (user views) + Conceptual (ER model) + Internal
(physical storage)
Entity Person, place, object, event, or concept the org tracks
Weak Entity Depends on another entity; double-line rectangle; partial
identifier
1:1, 1:N, M:N Cardinality — how many instances relate to each other
Supertype/Subtype Generalization (bottom-up) / Specialization (top-down)
Total Specialization Every supertype instance must be in a subtype (double line)
Disjoint Entity can be in only ONE subtype (d)
Overlap Entity can be in MULTIPLE subtypes (o)
1NF No multivalued attributes; all values atomic
2NF 1NF + no partial dependencies (needs composite PK)
3NF 2NF + no transitive dependencies
Primary Key Unique identifier; cannot be null
Foreign Key References PK of another table; can be null
Referential Integrity FK must match PK in parent or be null
CASCADE delete Delete child rows when parent is deleted
Rollback Undo transactions using before-images
Roll Forward Redo transactions using after-images
Data Warehouse Subject-oriented, integrated, time-variant, non-updatable
Star Schema Fact table (center) + Dimension tables (surrounding)
ETL Extract, Transform, Load — moves data to warehouse
Big Data 5 V's Volume, Variety, Velocity, Veracity, Value
NoSQL BASE Basically Available, Soft State, Eventually Consistent
MongoDB Document store; Collections=Tables; Documents=Rows
MapReduce Map (local processing) + Reduce (aggregate results)

★ ★ ★ GOOD LUCK ON YOUR EXAMS! ★ ★ ★

Study hard, understand the concepts, and write in your own words as per UPSA regulations.