Data Management Foundations D426 with complete verified solutions already graded A+

DB Administrator Secures the database from un-authencated access. DB Designer Determines the format and the structure of the database DB Programmer Develops programs or applications that will use the database DB Users Consumers of the data no the Database. Can access the DB through the app or query the database directly. Transactions - Ensures transaction are process completely or not at all - Prevents conflict between concurrent transactions - Ensure transactions results are not lost Query Processor interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. - two main functions: manages connections and compiles queries - Has a Cache Manager that stores reusable information into main memory Storage Manager - Translates the query processor instructions into low-level file-system commands that modify or retrieve data. - All access to the database data must go through the _______ ________ - Query optimization ensures the most efficient instructions are sent to the _______ ________, but the _______ ________ can retrieve the data with efficient or inefficient instructions Transaction Manager 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. Log A file containing a complete record of all inserts, updates, and deletes. Relational Database A database that represents data as a collection of tables in which all data relationships are represented by common values in related tables Structured Query Language (SQL) A language used to create and manipulate databases. Data Types Format of data in a field, such as text, number, or date. - Some data types are followed by 2 numbers and set of (). - Example VARCHAR(10) - DECIMAL(10,3) - 10 digits and 3 after the decimal Database Analysis - Specifies the database requirements - Requirements are represented by entities, relationships, and attributes Logical Design - Database requirements in a specific database system - Converts entities, relationships into tables, keys, columns Physical Design - Adds indexes and specifies how tables are organized on storage media - Logical design affects the results - Physical design affects the performance MySQL - Command Line Client is a text interface that's included in MySQL Download - MySQL Server returns an Error Code and Description when an SQL statement is syntactically incorrect Relational Data Structure A logical data structure in which all data elements within the database are viewed as being stored in the form of simple tables. DBMS packages based on the relational model can link data elements from various tables as long as the tables share common data elements. SELECT - Relational Operation - Selects an subset of rows PROJECT - Relational Operation - Eliminates one or more columns in a Table JOIN - Relational Operation - Combines 2 tables by comparing columns UNION - Relational Operation - Selects all rows - You must have the same amount of columns in both select queries - The results will get merged into the same columns despite the data types SELECT first_name, last_name FROM customer ____________ SELECT address, district FROM address; INTERSECT The SQL _______________ operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the _______________ results. SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] _____________ SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]; DIFFERENCE - Relational Operation - Selects rows that appear in one table BUT NOT another - Similar to a FULL OUTER JOIN in MySQL AGGREGATE - Relational Operation - Compute functions over multiple rows Relational Database Rules: Unique Primary Key All tables have a Primary Key which values cannot repeat Relational Database Rules: Column Names Different column names of the same table have different names Relational Database Rules: No Duplicate Rows No two rows of the same table have identical values Business Rules Rules that are based on business policies and are specific to a database Data Definition Language (DDL) The language that allows a database administrator to define the database structure, schema, and subschema. data query language (DQL) High-level, English-like, DBMS language that contains powerful, easy-to-use commands that enable users to retrieve, sort, order, and display data. Data Control Language (DCL) Commands that control a database, including administering privileges and committing data Data Transaction Language (DTL) - manages database transactions - commits data to a database, rolls back data from a database, and creates save points Rules Governing Tables - Exactly one value per cell - No duplicate column names - No duplicate rows - No row orders data independence A condition in which data access is unaffected by changes in the physical data storage characteristics. - Allows a DB Admin to improve query performance by changing the organization of data on storage devices IN Operator Used in a WHERE clause to determine if a value matches ONE or SEVERAL values BETWEEN Operator provides an alternative way to determine if a value is between two other values. The operator is written value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue. LIKE - Used in a WHERE clause - Matches text using a pattern - uses two wildcard characters % and _ - Case-insensitive pattern matching by default - Case-sensitive if followed by a binary DISTINCT - Used in a SELECT - returns only a unique value ORDER BY A SQL clause that is useful for ord