Database Management Quiz

Chapter 7 - Database Fundamentals

• Objectives: Explain database concepts and purpose, compare/contrast database structures, and summarize database interface methods.
• Database Usage: Includes creating, importing/inputting data, querying, generating reports, and comparing flat files with databases. Also considers the need for multiple concurrent users, scalability, speed, variety of data types, records, storage, and data persistence.
• Database Structures: Compares structured vs. semi-structured vs. non-structured databases. Describes relational databases, schemas, tables, rows/records, fields/columns, primary keys, foreign keys, and constraints. Also describes non-relational key/value databases and document databases.
• Database Interface Methods: Details relational methods (data manipulation like select, insert, delete, update, data definition like create, alter, drop, and permissions), Database access methods (direct, manual, programmatic), user interface/utility access, query/report builders, and database management tasks like export/import, database dump, and backup.
• Understanding Big Data: Big data is described as large, complex data sets spanning various sources. Companies use data scientists and algorithms to understand patterns.
• Understanding Databases: Databases store data in structured formats like spreadsheets, with rows and columns (data points). Databases differ from flat files in terms of handling multiple concurrent users. Databases handle more complex data relationships, allowing for greater scalability and performance compared to flat files.
• Exploring Database Concepts and Structures: Discusses flat files and how Excel spreadsheets are examples of flat-file structures. Clarifies how in a database, the structured layout is called a table, and how data is structured in rows & columns.
• When to Use Databases: Emphasizes the importance of databases when multiple concurrent users need to access and/or edit data at once, pointing out the limitations of flat files.
• Database Security: Permissions and role-based access control are important considerations for protecting sensitive data, emphasizing that data integrity should be maintained. Permissions are generally hierarchic.
• Data Persistence: Databases offer better data persistence than spreadsheets and can recover data even from power outages. Database backups for maintaining data, when dealing with data storage and retrieval.
• Relational Databases: Relational databases are structured, with tables containing data in rows and columns. Data conforms to rules, for example to prevent duplicate rows or allow null values.
• Database Schema: Logical and Physical schemas explain the structure of the database, such as tables, columns, and their relationships.
• Tables and Fields: Tables store data in rows and columns. Fields represent columns. Tables and fields in databases allow for efficient data organization and retrieval compared to how data is organized in flat files.
• Primary and Foreign Keys: A primary key uniquely identifies each record in the table and cannot have null values. A foreign key refers to the primary key in another table, linking different table records.
• Forms, Queries, and Reports: Forms are user-friendly ways to input data, queries are used to find specific data from the database, and reports provide formatted data for output.
• Database Macros and Modules: Macros and Modules allow the automation of actions to enhance database functionality.
• Non-relational Databases: Semi-structured data and unstructured data are considered non-relational data types, and have not the same structure as that in relational databases. Document databases handle large amounts of semi structured data, such as metadata. Key/Value databases store data as key-value pairs, enabling flexible data organization.
• Document Databases: Describes document databases and how they store documents, allowing varied data structures within one database.
• Key/Value Databases: Key/value stores data as paired key values, simplifying data organization that might not lend itself otherwise to row and column structure.
• Database Dump and Backup: Methods for backing up a database are discussed(database dump, and use of a backup program), stressing data security considerations and the potential importance of data recovery procedures.
• Data Extraction: Queries allow retrieval of specific information from large databases. The SELECT command is used to identify and display specific data. This is done using the criteria like conditions and clauses, such as WHERE, AND, OR, NOT, and using command like ORDER BY.

Database Access Methods

• Manual Access: Directly manipulating data in tables.
• Direct/Manual Access: Accessing database tables via network connections using methodologies like ODBC, APIs, or Java connections, which let remote computers interface with data.
• Programmatic Access: Using programming languages like SQL or program interfaces to retrieve and manipulate data from the database using a programming language or other application.
• User Interface/Utility Access: Using built-in or separate user interface programs that allow users to input or edit data in a database without manually interacting with the database's internal tables directly.
• Query/Report Builders: Use of tools to search for structured data in databases.

Database Permissions

• Server Permissions: Apply to the entire server.
• Database Permissions: Apply to a single database.
• Object Permissions: Apply to specific database objects (like tables, views, etc.).

Database Data Definition Commands

• CREATE: Create a database or table(s) structure.
• ALTER: Modify database or table structure.
• DROP: Delete/Remove a database or table.

Database Commands for Data Manipulation

• SELECT: Retrieving data.
• INSERT: Inserting data.
• UPDATE: Changing existing data.
• DELETE: Removing data.

Chapter 7 Lab Instructions

• The provided information outlines how to install, manipulate, and use a sample database called Northwind.

Review Questions

• Questions to test knowledge of database concepts, practical operations, and various types of databases.

Test your knowledge of key concepts in database management, including primary keys, foreign keys, and data structures. This quiz covers fundamental topics related to relational and non-relational databases, as well as the functions of queries and reports.