Google PDF - Database Fundamentals

# Chapter 7 Database Fundamentals ## CompTIA IT Fundamentals+ (ITF+) FC0-U61 Exam Objectives - **5.1 Explain database concepts and the purpose of a database:** - **Usage of Database:** - Create - Import/Input - Query - Reports - **Flat Files VS Database:**...

# Chapter 7 Database Fundamentals ## CompTIA IT Fundamentals+ (ITF+) FC0-U61 Exam Objectives - **5.1 Explain database concepts and the purpose of a database:** - **Usage of Database:** - Create - Import/Input - Query - Reports - **Flat Files VS Database:** - Multiple Concurrent Users - Scalability - Speed - Variety of Data - **Records:** - Storage - Data persistence - **5.2 Compare and Contrast Various Database Structures:** - Structured VS Semi-Structured VS Non-structured - **Relational Databases:** - Schema - Tables - Rows/Records - Fields/Columns - Primary Key - Foreign Key - Constraints - **Non-Relational Databases:** - Key/Value Databases - Document Databases - **5.3 Summarize Methods Used to Interface With Databases:** - **Relational Methods:** - Data Manipulation - Select - Insert - Delete - Update - Data Definition - Create - Alter - Drop - Permissions - **Database Access Methods:** - Direct/Manual Access - Programmatic Access - User Interface/Utility Access - Query/Report Builders - **Export/Import:** - Database Dump - Backup ## Understanding Databases - Databases store data. - Databases are used in many settings: banking, online retailers, government agencies, etc. - Databases can be stored locally, on servers, or in the cloud. - Storage and access methods largely remain consistent across platforms. ## Exploring Database Concepts and Structures - Databases can be thought of as spreadsheet equivalents but are more versatile and powerful. - A single table in a database is referred to as a table, not a spreadsheet. - A flat file is a database that stores data in rows and columns. - Databases offer increased flexibility and scalability compared to spreadsheets. ## When to Use Databases - When data needs to be stored and accessed by multiple concurrent users. - When data needs to be categorized and linked across various tables. - When data needs to be easily scaled and updated. ## Multiple Concurrent Users - Flat files are not suitable for handling multiple users simultaneously. - Locks on flat files are file-wide, hindering concurrency. - Databases allow record-level locks for increased efficiency. ## Scalability - Flat files are ill-equipped to handle large datasets and numerous users, resulting in significant performance degradation. - Enterprise databases are scalable, allowing access to hundreds or thousands of users at a time. - Database software can be stretched across on-site servers and the cloud for optimal scalability. ## Speed - Databases are significantly faster than flat files for processing and accessing large amounts of data due to their robust memory requirements. - Databases rely heavily on RAM, making sufficient capacity crucial. - SSDs are recommended for faster data access compared to HDDs. ## Variety of Data - Databases can store a wide range of data types. - Flat files are limited to text and numbers. - Databases can store images, text files, and other complex data formats. ## Number of Records - Spreadsheets have limitations on the number of records and columns a single cell can hold. - Databases are designed for handling massive datasets. - Databases are better equipped for managing large amounts of data generated by organizations like Amazon and the IRS. ## Data Persistence - Users expect their data to persist without loss, requiring a robust storage solution. - Spreadsheets and databases both rely on hard drives for storage. - Databases offer an additional layer of protection through data logging and recovery mechanisms. ## Security - Databases provide more granular security controls than spreadsheets. - Databases allow for specific access rights based on roles and user accounts. ## Relational Databases - Relational databases are organized and predictable, adhering to a set of rules. - Data is stored in tables with rows, columns, and relationships. - Data is easy to search and access. - Relational databases are managed by a Database Management System (DBMS). - **RDBMS Principles:** - No duplicate rows are allowed. - Column values cannot be arrays or repeating groups of data. - Null values are used to indicate missing data. ## Schema - The schema defines the structure of a database, including tables, fields, relationships, and data types. - Two types of schemas: - **Logical schema**: Outlines the database structure, including tables and relationships. - **Physical schema**: Defines the tables, columns, and relationships as they are physically implemented. ## Tables - Tables resemble spreadsheets and store data in rows and columns. - Tables can be linked to other tables based on shared data. - Table rows are known as records. - Data can be easily organized and linked across different tables. ## Fields - Fields represent columns in a table and store a single data type. - Fields are designed to hold specific types of data. ## Primary Key - A primary key is one or more fields that uniquely identify a record within a table. - Primary keys are crucial for relational database integrity and data retrieval. - Primary keys must be unique and non-null. - Examples of primary keys include customer IDs and phone numbers. ## Foreign Key - A foreign key is one or more columns that reference the primary key of another table. - Foreign keys establish relationships between tables and facilitate data retrieval across different tables. - Foreign keys can contain duplicates and null values. - Example of a foreign key: Instructor_ID in Course Table referencing Instructor_ID in Instructor Table. ## Constraints - Constraints restrict data entry and manipulation to ensure data integrity. - Constraints help to define the relationships between tables and prevent errors. ## Forms - Forms are graphical interfaces that simplify data entry and management. - Forms are not mandatory but enhance database usability. - Example: A form for entering new customer information. ## Queries - Queries are the core of database data retrieval. - Queries allow users to access specific data based on certain criteria. - Queries are essential for managing large datasets. ## Reports - Reports provide formatted, visually appealing summaries of queried data. - Reports are useful for analysis and presentations. - Reports can include charts, graphs, and tables. ## Non-relational Databases - Non-relational databases are used for storing structured and unstructured data. - These databases are suitable for handling large volumes of data with diverse formats. ## Non-structured Data - Accounts for a significant portion of data worldwide. - Examples include images, videos, emails, and social media posts. - Non-structured data has its own internal structure but isn't easily categorized into rows and columns. ## Semi-structured Data - Falls between structured and unstructured data. - Semi-structured data has metadata associated with it, providing additional context and structure. - Examples: Images with tags about their content or location. ## Document Databases - Document databases are a type of non-relational database designed for storing documents as individual units. - Documents don't need to have the same structure. - Data is easy to manage and scale due to the independent nature of individual documents. ## Key/Value Databases - Key/Value databases are another type of non-relational database that store data as key-value pairs. - Data is accessed and retrieved by referencing the unique keys. - Values can be any type of data, including strings, images, and files. ## Real World Scenario: Understanding Big Data - Big Data refers to massive and complex datasets that are analyzed to uncover patterns and trends. - Big Data includes structured, semi-structured, and unstructured data. - Companies are investing in Big Data technologies to gain insights and make better decisions. ## Working With Databases - Databases make managing large quantities of data easier. - Several methods exist for accessing data: - Manual Access: Directly viewing and modifying data within the database itself. - Programmatic Access: Using programming languages like SQL to access data. - User Interface/Utility Access: Accessing data through graphical interfaces or utilities. - Query/Report Builder: Retrieving specific data using query languages or preconfigured reports. - Export/Import: Transferring data between databases or systems. ## Permissions - Permissions regulate user access to databases and data. - Permissions are assigned to users or groups known as roles. - Different DBMSs have different permission models, but common classifications include: - Server permissions: Apply to the entire database server. - Database permissions: Apply to a single database. - Object permissions: Apply to specific database objects, such as tables or forms. ## Creating and Managing a Database - Create a database using the CREATE DATABASE command. - Create a table within the database using the CREATE TABLE command. - Modify database structures using the ALTER TABLE command. - Delete databases or tables using the DROP DATABASE or DROP TABLE command. ## Manipulating Data - Data can be manipulated using four primary commands: - INSERT: Adds records into a table. - UPDATE: Modifies existing records. - DELETE: Removes records from a table. - SELECT: Extracts and retrieves data from a table. ## Extracting Data - Queries are used to efficiently retrieve data using the SELECT command. - Reports are formatted and visually appealing representations of queried data. ## Exam Essentials - **Know When to Use a Database Instead of a Flat File**: Spreadsheets are suitable for small datasets, while databases are preferable for large, complex datasets and concurrent user scenarios. - **Know the Primary Characteristics of a Structured Database**: A structured database features rows, columns, and a predictable schema, enabling efficient data access and retrieval. - **Know the Difference Between Non-Structured and Semi-Structured Data**: Non-structured data lacks a defined format, while semi-structured data exhibits some organization through metadata. - **Know the Key Structures in a Relational Database**: Relational databases are built on schemas, tables, rows, columns, and keys that enforce data integrity and relationships. - **Know the Differences Between a Primary Key and a Foreign Key**: A primary key uniquely identifies a record, while a foreign key establishes a connection between two tables. - **Know Two Different Types of Non-Relational Databases**: Document databases store data as documents while key/value databases use pairs of keys and values for data management. - **Know the Four Commands Used to Manipulate Data**: SELECT retrieves data, INSERT adds records, DELETE removes records, and UPDATE modifies existing records. - **Understand the Commands for Data Definition**: CREATE creates databases or tables, ALTER modifies them, and DROP removes them. Data definition also includes establishing permissions. - **Know the Database Access Methods**: These include direct/manual access, programmatic access, user interface/utility access, and query/report builders. - **Understand the Two Methods Used to Back Up Databases**: Database dumps make a complete copy of the database in a single operation, while backups are incremental and preserve the physical structure of the database. ==End of OCR for page 48== # Chapter 7 Lab: Exploring Northwind - Install and explore the Northwind sample database. - Access various tables, forms, and queries within Northwind. - Use your new database knowledge to answer questions about the Northwind data.