DATA FOUNDATIONS MANAGEMENT ACTUAL LATEST GUIDE VERSIONS QUESTIONS & CORRECT DETAILED ANSWERS

DATA FOUNDATIONS MANAGEMENT ACTUAL LATEST GUIDE VERSIONS QUESTIONS & CORRECT DETAILED ANSWERS Database application - ANSWER software that helps business users interact with database systems. Many databases are complex, and most users are not familiar with query languages. Consequently, direct database access is usually not feasible. Instead, programmers write applications to simplify the user experience and ensure data access is efficient and secure. database administrator - ANSWER responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability. database designer - ANSWER determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data. Since these priorities often conflict, database design is technically challenging. Data - ANSWER numeric, textual, visual, or audio information that describes real-world systems. Data is collected and processed to aid in a variety of tasks, such as forecasting weather, analyzing financial investments, and tracking the global spread of pandemics. Scope. - ANSWER The amount of data produced and collected can vary. Ex: A small business might track an inventory of a few thousand items, but a large commerce website might track billions of items. Format. - ANSWER Data may be produced as numbers, text, image, audio, or video. Ex: A phone's proximity sensor generates raw numbers, and a satellite captures images. Access. - ANSWER Some data sources are private while others are made publicly available. Ex: A retail company may use private customer data to discover purchasing behavior patterns, but a government may be required by law to share certain data sets. application programming interface or API - ANSWER Software through which programmers interact with middleware. An API allows the use of generic SQL code, thereby allowing client processes to be database server-independent. attribute - ANSWER A characteristic of an entity or object. An attribute has a name and a data type. balancing - ANSWER A characteristic of an entity or object. An attribute has a name and a data type. Big Data - ANSWER A movement to find new and better ways to manage large amounts of web-generated data and derive business insight from it, while simultaneously providing high performance and scalability at a reasonable cost. Chen notation - ANSWER See entity relationship or ER model. class - ANSWER A collection of similar objects with shared structure or attributes and behavior or methods. A class encapsulates an object's data representation and a method's implementation. Classes are organized in a class hierarchy. class diagram notation - ANSWER The set of symbols used in the creation of class diagrams in UML object modeling. class diagrams - ANSWER A diagram used to represent data and their relationships in UML object notation. class hierarchy - ANSWER The organization of classes in a hierarchical tree in which each parent class is a superclass and each child class is a subclass. See also inheritance. client - ANSWER Any process that requests specific services from server processes in a client/server environment. client node - ANSWER One of three types of nodes used in the Hadoop Distributed File System or HDFS. The client node acts as the interface between the user application and the HDFS. See also name node and data node. complex object - ANSWER An object formed by several different objects in complex relationships. See also abstract data types. conceptual model - ANSWER The output of the conceptual design process. The conceptual model provides a global view of an entire database and describes the main data objects, avoiding details. Analog - ANSWER encoded as continuous variations on various physical media. Ex: Audio was recorded as vibrations impressed on vinyl disks. digital - ANSWER encoded as zeros and ones on electronic and magnetic media. database - ANSWER a collection of data in a structured format database system, also known as a database management system or DBMS - ANSWER software that reads and writes data in a database. Database systems ensure data is secure, internally consistent, and available at all times. These functions are challenging for large databases with many users, so database systems are complex. query - ANSWER a request to retrieve or change data in a database. query language - ANSWER a specialized programming language, designed specifically for database systems. Query languages read and write data efficiently, and differ significantly from general-purpose languages such as Python, Java, and C++. database programmer - ANSWER develops computer programs that utilize a database. Database programmers write applications that combine database query languages and general-purpose programming languages. Query languages and general-purpose languages have significant differences, so database programming is a specialized challenge. database user - ANSWER a consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system. Performance - ANSWER When many users and applications simultaneously access large databases, query response time degrades rapidly. Database systems must maintain fast response times by structuring data properly on storage media and processing queries efficiently. Authorization - ANSWER Many database users should have limited access to specific tables, columns, or rows of a database. Database systems must authorize individual users to access specific data. Security - ANSWER Database systems must ensure authorized users only access permissible data. Database systems must also protect against hackers by encrypting data and restricting access. Rules - ANSWER Database systems must ensure data is consistent with structural and business rules. Ex: When multiple copies of data are stored in different locations, copies must be synchronized as data is updated. Ex: When a course number appears in a student registration record, the course must exist in the course catalog. Recovery - ANSWER Computers, database systems, and individual transactions occasionally fail. Database systems must recover from failures and restore the database to a consistent state without loss of data. transaction - ANSWER a group of queries that must be either completed or rejected as a whole. Execution of some, but not all, queries results in inconsistent or incorrect data. Ex: A debit-credit transaction transfers funds from one bank account to another. When processing transactions, database systems must: - ANSWER Ensure transactions are processed completely or not at all. A computer or application might fail while processing a transaction. When failing to process a transaction, the database system must reverse partial results and restore the database to the values prior to the transaction. Prevent conflicts between concurrent transactions. When multiple transactions access the same data at the same time, a conflict may occur. Ex: Sam selects a seat on a flight. Maria purchases the same seat in a separate transaction before Sam completes his transaction. When Sam clicks the 'purchase' button, his seat is suddenly unavailable. Ensure transaction results are never lost. Once a transaction completes, transaction results must always be saved on storage media, regardless of application or computer failures. architecture of a database system - ANSWER describes the internal components and the relationships between components. At a high level, the components of most database systems are similar: query processor - ANSWER interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. The query processor performs query optimization to ensure the most efficient instructions are executed on the data. The storage manager - ANSWER translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data. The transaction manager - ANSWER ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure. The log - ANSWER a file containing a complete record of all inserts, updates, and deletes processed by the database. The catalog, also known as a data dictionary - ANSWER a directory of tables, columns, indexes, and other database objects. Other components use catalog information to process and execute queries. relational database - ANSWER stores data in tables, columns, and rows, similar to a spreadsheet. All data in a column has the same format. All data in a row represents a single object, such as a person, place, product, or activity. SQL stands for Structured Query Language - ANSWER Includes statements that read and write data, create and delete tables, and administer the database system. sponsored by the American National Standards Institute (ANSI) and the International Standards Organization (ISO) Open source software - ANSWER software that anyone can inspect, copy, and modify with no licensing fee. CRUD operations - ANSWER an acronym for Create, Read, Update, and Delete data. SQL CREATE TABLE - ANSWER statement creates a new table by specifying the table and column names. Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex. Ex: INT stores integer values. DECIMAL stores fractional numeric values. VARCHAR stores textual values. DATE stores year, month, and day. Database Design Process - ANSWER a specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification. For small, simple databases, the database design process can be informal and unstructured. For large, complex databases, the process has three phases: Analysis Logical design Physical design Analysis Phase - ANSWER Specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity. ER diagrams - ANSWER consist of entities, attributes, and relationships. To properly identify entities, attributes, and relationships, database designers first identify the business rules for the particular data model. logical design phase - ANSWER Implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns key - ANSWER a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements. The logical design is depicted in a table diagram - ANSWER Table diagrams are similar to ER diagrams but more detailed: Rectangles represent tables. Table names appear at the top of rectangles. Text within rectangles and below table names represents columns. Solid bullets (●) indicate key columns. Empty bullets (○) and arrows indicate columns that refer to keys. The logical design, as specified in SQL and depicted in a table diagram, is called a database schema. Physical Design Phase - ANSWER adds indexes and specifies how tables are organized on storage media. Ex: Rows of a table may be sorted on the values of a column and stored in sort order. Physical design is specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system. Application programming interface, or API - ANSWER a library of procedures or classes that links a host programming language to a database. Domain - ANSWER a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE. A tuple - ANSWER A finite sequence of values, each drawn from a fixed domain. Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues). An aggregate function - ANSWER operates on numeric values from multiple rows, including only rows selected by the WHERE clause. Referential integrity can be violated in what four ways? - ANSWER A primary key is updated. A foreign key is updated. A row containing a primary key is deleted. A row containing a foreign key is inserted. CREATE VIEW - ANSWER In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition WITH CHECK OPTION - ANSWER used to ensure that no changes to the underlying data can be made through the view that would cause data no to conform to the definitions of the view. relational algebra - ANSWER A set of mathematical principles that form the basis for manipulating relational table contents; the eight main functions are SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE. The SQL language is divided into five sublanguages: - ANSWER Data Definition Language (DDL) defines the structure of the database. Data Query Language (DQL) retrieves data from the database. Data Manipulation Language (DML) manipulates data stored in a database. Data Control Language (DCL) controls database user access. Data Transaction Language (DTL) manages database transactions. GROUP BY clause - ANSWER The clause that groups rows based on the specified column CREATE DATABASE - ANSWER creates a new database ex: CREATE DATABASE bikeStore; DROP DATABASE - ANSWER Deletes an existing SQL database ex: DROP DATABASE petStore; Commonly used SHOW statements include: - ANSWER SHOW DATABASES lists databases available in the database system. SHOW TABLES lists tables available in the currently selected database. SHOW COLUMNS lists columns available in a specific table named by a FROM clause. SHOW CREATE TABLE shows the CREATE TABLE statement for a given table. USE statement - ANSWER selects a database and is required to show information about tables within a specific database query execution plan - ANSWER specifies precisely how to process an SQL statement. Query optimizers use equivalent expressions to optimize query execution. The query optimizer: - ANSWER Converts a query to a relational algebra expression. Generates equivalent expressions. Estimates the 'cost' of each operation of each expression. Determines the optimal expression with the lowest total cost. Converts the optimal expression into a query execution plan. Cost of an operation - ANSWER a numeric estimate of processing time DROP TABLE statement - ANSWER allows you to remove tables from your schema ALTER TABLE statement - ANSWER The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ADDAdds a columnALTER TABLE TableName ADD ColumnName DataType; CHANGEModifies a columnALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType; DROPDeletes a columnALTER TABLE TableName DROP ColumnName; PRIMARY KEY constraint - ANSWER uniquely identifies each record in a database table. The primary key must contain unique values and it cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.