FC0-U61 Objective 5.2: Compare and Contrast Various Database Structures

Structured Data Characteristics:

• Predefined schema: Data structure is defined before data is stored
• Consistent format: All records follow the same structure
• Fixed fields: Each record has the same fields/columns
• Data types: Each field has a specific data type
• Relationships: Clear relationships between different data entities
• Validation: Data must conform to predefined rules
• Query optimization: Optimized for complex queries and joins
• ACID compliance: Ensures data consistency and reliability

Non-Structured Data Characteristics:

• No predefined schema: Data structure is flexible and can vary
• Variable format: Records can have different structures
• Flexible fields: Records can have different fields
• Dynamic schema: Schema can evolve over time
• Nested data: Can contain complex nested structures
• Schema-less: No rigid data validation rules
• Scalability: Designed for horizontal scaling
• Flexibility: Can handle diverse data types and formats

Schema Components:

• Table definitions: Structure of each table in the database
• Field specifications: Data types and constraints for each field
• Relationships: How tables are related to each other
• Indexes: Performance optimization structures
• Views: Virtual tables based on queries
• Stored procedures: Predefined database operations
• Triggers: Automated responses to database events
• Security rules: Access control and permissions

Table Characteristics:

• Two-dimensional structure: Organized in rows and columns
• Consistent structure: All rows have the same columns
• Named columns: Each column has a specific name and data type
• Unique rows: Each row represents a unique record
• Primary key: One or more columns that uniquely identify each row
• Constraints: Rules that ensure data integrity
• Indexes: Structures that improve query performance
• Relationships: Can be related to other tables through foreign keys

Primary Key Types:

-- Single Column Primary Key
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,  -- Single column primary key
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Composite Primary Key
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    Semester VARCHAR(20),
    Grade CHAR(2),
    PRIMARY KEY (StudentID, CourseID, Semester)  -- Composite primary key
);

-- Auto-incrementing Primary Key
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,  -- Auto-incrementing
    CustomerID INT,
    OrderDate DATE
);

-- UUID Primary Key
CREATE TABLE Products (
    ProductID CHAR(36) PRIMARY KEY,  -- UUID primary key
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

Database Constraints:

-- Comprehensive Constraint Example
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    DateOfBirth DATE CHECK (DateOfBirth < '2010-01-01'),
    GPA DECIMAL(3,2) CHECK (GPA >= 0.0 AND GPA <= 4.0),
    Major VARCHAR(50) DEFAULT 'Undeclared',
    EnrollmentDate DATE DEFAULT CURRENT_DATE,
    Status VARCHAR(20) CHECK (Status IN ('Active', 'Inactive', 'Graduated'))
);

-- Table-level Constraints
CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    Semester VARCHAR(20) NOT NULL,
    Grade CHAR(2),
    EnrollmentDate DATE DEFAULT CURRENT_DATE,
    
    -- Foreign key constraints
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
    
    -- Unique constraint
    UNIQUE(StudentID, CourseID, Semester),
    
    -- Check constraint
    CHECK (Grade IN ('A', 'B', 'C', 'D', 'F', 'P', 'NP', NULL))
);

Key/Value Database Characteristics:

• Simple structure: Data stored as key-value pairs
• Fast access: Very fast read and write operations
• Scalable: Easy to scale horizontally
• Schema-less: No predefined data structure
• Limited querying: Primarily key-based access
• Memory-based: Often stored in memory for speed
• Distributed: Can be distributed across multiple nodes
• Use cases: Caching, session storage, real-time data

Document Database Characteristics:

• Document storage: Data stored as self-contained documents
• Flexible schema: Each document can have different structure
• Nested data: Support for complex nested structures
• Rich querying: Support for complex queries on document fields
• JSON/BSON format: Typically use JSON or BSON format
• Indexing: Can index on any field within documents
• Aggregation: Support for aggregation operations
• Use cases: Content management, user profiles, catalogs

Key Differences:

• Schema: Structured has predefined schema, non-structured is flexible
• Consistency: Structured enforces consistency, non-structured allows variation
• Querying: Structured optimized for complex queries, non-structured for simple access
• Scalability: Structured scales vertically, non-structured scales horizontally
• ACID compliance: Structured supports ACID, non-structured may not
• Use cases: Structured for transactional systems, non-structured for big data
• Development: Structured requires upfront design, non-structured allows rapid development
• Performance: Structured optimized for complex operations, non-structured for simple operations

Sample Exam Questions:

1. What is the main difference between structured and non-structured data?
2. What database component uniquely identifies each record in a table?
3. What type of database stores data as key-value pairs?
4. What is the purpose of a foreign key in a relational database?
5. What type of database is best suited for storing JSON documents?
6. What constraint ensures that a field cannot contain null values?
7. What is the main advantage of non-relational databases over relational databases?
8. What database structure is best for handling complex relationships between data?
9. What type of database is most suitable for caching and session storage?
10. What is the primary benefit of using constraints in a database?