Database Systems - Module 2: Data Models

Foundations of Data Modeling and Data Models

• Data Modeling: An iterative, progressive engineering process directed toward creating a specific data model for a determined problem domain.

• Data Model: A relatively simple abstraction and visual or structural representation of complex real-world data environments.

• Role and Importance of Data Models:

  • Communication Tool: Bridges structural understandings between application programmers, database administrators, executive management, and end users who perceive data from distinct operational perspectives.

  • Database Blueprint: Serves as the master architectural blueprint for database development, guaranteeing that distinct programmer implementations align with overall organizational needs.

  • Error Reduction: Enables the application of powerful database design constructs and automated design tools, drastically reducing architectural errors prior to physical implementation.

Basic Building Blocks of Data Models

• Entity: A person, place, thing, concept, or event within a business domain about which data is actively collected and stored.

• Attribute: A specific named characteristic or property that describes an entity.

• Relationship: An association or logical linkage among two or more entities. Relationships are categorized into three fundamental structural types:

  • One-to-Many ($1:M$ or $1..*$): A single entity instance in the primary entity type can associate with zero, one, or multiple instances in a related entity type, while each instance in the related entity type links to exactly one instance in the primary entity type.

  • Many-to-Many ($M:N$ or $*..*$): An instance of one entity type can relate to many instances of another entity type, and vice versa.

  • One-to-One ($1:1$ or $1..1$): An instance of one entity type relates to at most one instance of another entity type.

• Constraint: A rule or restriction placed upon data values to guarantee data integrity, correctness, and domain validity.

  • Examples of Constraints:

  • An employee's salary must fall within the range of $6,000$ to $350,000$.

  • A student's Grade Point Average (GPA) must fall within $0.00$ and $4.00$.

  • Each class offering must have exactly one assigned instructor.

Business Rules and Naming Conventions

• Business Rules Definition: Brief, precise, and unambiguous descriptions of policies, procedures, or principles within a specific organizational context.

• Role in Database Design:

  • Standardizes the organization's collective understanding and definition of data.

  • Serves as a primary communication medium between business stakeholders and database system designers.

  • Facilitates comprehensive understanding of business workflows, operational processes, scope, and data roles.

  • Establishes precise relationship participation rules (optionality and cardinality) and constraint thresholds.

• Discovery Sources: Primary sources include executive managers, policy makers, departmental heads, and formal operational documentation (such as standard operating procedure manuals).

• Transformation to Model Components:

  • Nouns: Generally translate into entities or structural objects in the system.

  • Verbs: Generally translate into relationships connecting entities.

  • Example Transformation: The business rule "a customer may generate many invoices" identifies two entities (CUSTOMER and INVOICE) and a verb relationship (generates). Paired with the inverse rule "an invoice is generated by only one customer", this establishes a $1:M$ relationship between CUSTOMER and INVOICE.

• Naming Conventions:

  • Entity names must use descriptive, clear terminology familiar to business end users.

  • Attribute names must clearly reflect the underlying data property and should incorporate an entity-identifying prefix (such as CUS_CREDIT_LIMIT within the CUSTOMER entity).

  • Proper naming conventions produce a self-documenting data model structure.

Chronological Evolution of Data Models

• Hierarchical Model (1960s):

  • Developed to manage vast datasets for complex manufacturing operations.

  • Structured logically as an inverted tree containing ordered levels or segments.

  • Segment: The structural equivalent of a record type in a file system.

  • Establishes strict parent-child relationships where a higher-layer segment acts as parent to subordinate child segments directly beneath it.

• Network Model (1969):

  • Created to model complex multi-parent relationships, optimize access performance, and establish standardized database terminology.

  • Schema: Conceptual organization of the entire database as managed by the Database Administrator (DBA).

  • Subschema: The tailored database subset viewed by specific application programs.

  • Data Manipulation Language (DML): System language defining the runtime environment for querying and managing data.

  • Data Definition Language (DDL): System language enabling DBAs to define structural schema components.

• Relational Model (1970):

  • Introduced based on mathematical set theory and relation theory.

  • Relation: A two-dimensional grid composed of intersecting rows and columns.

  • Tuple: A single horizontal row within a relation, corresponding to a record.

  • Attribute: A single vertical column within a relation, corresponding to a field.

  • Relational Database Management System (RDBMS): Complex software engine executing structural tasks and query processing behind the scenes while exposing clean tables to users.

  • Tripartite Relational Application Architecture:

  1. End User Interface: Renders interaction layers for data manipulation.

  2. Collection of Tables: Stores data as structured two-dimensional relations.

  3. SQL Engine: Parses, optimizes, and executes data access queries.

• Entity Relationship (ER) Model (1976):

  • Introduced by Peter Chen to provide a high-level, visually intuitive modeling paradigm.

  • Entity Relationship Diagram (ERD): Uses geometric shapes to illustrate entities, attributes, and relationships.

  • Core Constructs:

  • Entity Type: Conceptual set or collection of entities (corresponds to a database table).

  • Entity Instance: An individual instance of an entity type (corresponds to a database row).

  • Attribute: Characteristics describing an entity type or relationship.

  • Relationship Type: Categorical linkage between entity types.

  • Relationship Instance: Specific association connecting specific entity instances (implemented via Primary Key / Foreign Key links).

  • Detailed Entity Type and Instance Structure (EMPLOYEE):

  • Employee Number: Data type CHAR (10), Example Instances: 64217836, 53410197

  • Name: Data type CHAR (25), Example Instances: Michelle Brady, David Johnson

  • Address: Data type CHAR (30), Example Instances: 100 Pacific Avenue, 450 Redwood Drive

  • City: Data type CHAR (20), Example Instances: San Francisco, Redwood City

  • State: Data type CHAR (2), Example Instances: CA, CA

  • Zip Code: Data type CHAR (9), Example Instances: 98173, 97142

  • Date Hired: Data type DATE, Example Instances: 03-21-1992, 08-16-1994

  • Birth Date: Data type DATE, Example Instances: 06-19-1968, 09-04-1975

• ER Modeling Notations:

  • Chen Notation: Uses rectangles for entities, diamonds for relationships, and explicit numeric ratios ($1$, $M$, $N$).

  • Crow's Foot Notation: Uses rectangular blocks with split header sections and graphical end symbols (forked feet for many, perpendicular bars for one) to denote cardinality and optionality.

  • UML Class Diagram Notation: Uses divided rectangles containing class names, attributes, and methods, along with numerical multiplicity expressions ($1..1$, $1..*$).

Advanced Data Models and Big Data Innovations

• Object-Oriented Data Model (OODM - 1985):

  • Encapsulates both data structures and behavioral operations (methods) inside a unified construct called an Object.

  • Described as a semantic data model because it directly incorporates rich domain semantics.

  • Core OODM Constructs:

  • Object: Abstraction of a real-world entity encapsulating state and behavior.

  • Attributes: State properties defining an object.

  • Class: Collection of similar objects sharing structural attributes and executable methods.

  • Method: Executable programmatic procedure representing real-world operational actions (such as retrieving a person's name, changing an address, or computing pay).

  • Class Hierarchy: Inverted tree structure defining superclasses and subclasses.

  • Inheritance: Mechanism permitting subordinate classes to inherit attributes and methods from parent classes above them.

• Extended Relational Data Model (ERDM / O/R DBMS - 1990):

  • Extends relational database engines by adding key object-oriented capabilities (such as complex data types and inheritance) while preserving SQL and table-based relational structures.

  • Combines relational benefits (declarative SQL queries, structural integrity, high availability, transaction performance, security) with object versatility.

• XML (Extensible Markup Language):

  • Emergent standard for facilitating flexible data representation and cross-platform exchange across structured, semi-structured, and unstructured data types.

• Big Data and NoSQL Systems (2009 - Present):

  • Driven by the rapid growth of the Internet of Things (IoT), where billions of connected devices generate roughly $2.5 \times 10^{18}\,\text{bytes}$ ($2.5$ quintillion bytes) of data per day.

  • The 3 Vs of Big Data:

  1. Volume: Massive bulk datasets exceeding traditional database processing capacities.

  2. Velocity: Rapid stream ingestion rates required to process continuous real-time data flow.

  3. Variety: Highly diverse structural formats (structured, semi-structured, completely unstructured audio/video/text).

  • Big Data Technological Ecosystem:

  • Hadoop: Java-based, open-source framework facilitating fault-tolerant, highly scalable distributed storage and computation across computer clusters.

  • Hadoop Distributed File System (HDFS): High-performance, fault-tolerant distributed file storage system designed for massive dataset throughput.

  • MapReduce: Programming paradigm and API delivering fast parallelized analytical processing across HDFS nodes.

  • NoSQL Databases:

  • Distributed non-relational database architectures designed for horizontal expansion.

  • Schema-less structures supporting sparse, highly unstructured data arrays.

  • Optimized for dynamic scaling, fault tolerance, and raw read/write throughput, prioritizing operational performance over strict ACID transaction consistency.

Levels of Data Abstraction and Framework Design

• ANSI/SPARC Architectural Framework:

  • Defines database organization across increasing degrees of data abstraction to decouple user views from physical storage hardware.

• 1. External Model:

  • Definition: The end user's operational view of the data environment.

  • Characteristics: Business units focus specifically on their operational domains; each domain views its data subset (external schema) independently of other units.

  • Advantages: Tailors data structures to specific departmental needs, simplifies application programming, enhances security by restricting access, and provides direct feedback on model adequacy.

• 2. Conceptual Model:

  • Definition: A global, enterprise-wide view of the complete database (conceptual schema) incorporating all entities, relationships, and constraints.

  • Characteristics: Completely independent of both DBMS software and underlying hardware infrastructure. Represents the highest overarching level of conceptual design.

  • Logical Design: The system engineering phase dedicated to creating the conceptual data model.

• 3. Internal Model:

  • Definition: The database schema representation as constructed within a specific DBMS engine (internal schema).

  • Characteristics: Software dependent (tailored to specific relational engine dialects) but hardware independent (operates identically across server hardware architectures).

  • Logical Independence: The structural property allowing modifications to the internal schema (such as altering table structures or data types) without impacting the overarching conceptual schema.

  • SQL Implementation Constructs (Tiny College Internal Model):

  • PROFESSOR Table Construction:

    • Primary Key: PROF_ID NUMBER

    • Attributes: PROF_LNAME CHAR(15), PROF_INITIAL CHAR(1), PROF_FNAME CHAR(15)

  • CLASS Table Construction:

    • Primary Key: CLASS_ID NUMBER

    • Foreign Keys: CRS_ID CHAR(8) REFERENCES COURSE, PROF_ID NUMBER REFERENCES PROFESSOR, ROOM_ID CHAR(8) REFERENCES ROOM

  • ROOM Table Construction:

    • Primary Key: ROOM_ID CHAR(8)

    • Attribute: ROOM_TYPE CHAR(3)

  • COURSE Table Construction:

    • Primary Key: CRS_ID CHAR(8)

    • Attributes: CRS_NAME CHAR(25), CRS_CREDITS NUMBER

• 4. Physical Model:

  • Definition: The lowest level of data abstraction, detailing physical storage device configurations, data striping, page allocation, and physical indexing access paths.

  • Characteristics: Both software dependent and hardware dependent.

  • Physical Independence: The structural capability allowing physical storage device configurations, disk partitioning, or access method adjustments to be modified without affecting the logical internal schema.

Questions & Discussion

• Question: What is a relationship, and what three types of relationships exist?

  • Answer: A relationship is a logical association among two or more entities. The three fundamental types of relationships are:

  1. One-to-One ($1:1$)

  2. One-to-Many ($1:M$)

  3. Many-to-Many ($M:N$ or $M:M$)

• Question: What does the term "3 Vs" refer to?

  • Answer: The term "3 Vs" refers to the three defining characteristics of Big Data databases: Volume, Velocity, and Variety.

• Question: What is logical independence?

  • Answer: Logical independence is the architectural property achieved when the internal model can be modified (such as updating schema tables or data types) without requiring changes to the conceptual model.