DATABASE C192 OA COMPLETED_Study_Guide - Western Governers University | DATABASE C 192 OA COMPLETED_Study_Guide

Document Content and Description Below

DATABASE C192 OA COMPLETED_Study_Guide - Western Governers University C192 - Study Guide by Lesson Updated 02/09/2017 C192 - Study Guide by Lesson Instructions: Skip Lessons: 3, 9, 10, 11, 14, 15,... 19, 21, 25, 27, 28 Key: Cells in this color contain questions/prompts that were provided by the Course Instructors. Cells in this color contain questions/prompts that I deemed important. Cells in this color contain answers to the questions/prompts. Notes: This study guide should not be used in lieu of the reading, it is not as comprehensive as the text is. This study guide was completed by Zachary Saunders (https://www.linkedin.com/in/zacharydavidsaunders/). I have made a reasonable effort to ensure that this study guide does not contain errors, but I don’t guarantee that it is error free. Good luck! Section Lesson / Objectives Lesson 1: Introduction to Databases 1.6 Identify strengths and weakness of a DBMS 1.6 Strengths: > Control of data redundancy and consistency. > Improved security and integrity via the enforcement of standards > Increased productivity via improved accessibility and responsiveness. Weaknesses: > Complexity > Size > Cost > Risk of failure 1.5 List the three generations of DBMSs. 1.5 What are the three generations of DBMSs? 1. Hierarchical model (where DBs follow a hierarchical structure) 2. Network model (where DBs have a web-like structure of networked records) 3. Relational model (what we have now) Lesson 2: Database Environment 2.1 Explain the three level ANSI-SPARC Architecture. 2.1 What are the 3 levels? 1. External level (the views of the DB / the part of the DB that is relevant to each user) 2. Conceptual level (what data is stored) 3. Internal level (how the data is stored) 2.1 Explain Logical data independence and Physical data independence. 2.1 Logical data independence: The immunity of the external schemas to changes in the conceptual schema. (In other words: changing data doesn’t break views) Physical data independence: The immunity of the conceptual schema to changes in the internal schema. (In other words: changing how data is stored doesn’t change the data itself) 2.2 What are DDL and DML? How are they different? 2.2 DDL: Data Definition Language is used to describe the database’s structure. DML: Data Manipulation Language is used to manipulate the data within a DB. 2.3 List types of Data Management Systems 2.3 See rows 12/13 2.3 Compare database management systems. 2.3 Relational: Based on mathematical relations. Data and relationships are represented as tables. Network: Records become nodes that are linked together. Linked nodes are sets. (Think of a web structure) Hierarchical: A type of network system where all nodes may only have one parent. (Think tree graph) 2.4 Describe the functions that a database management system (DBMS) supports. 2.4 What are the 10 functions of a DBMS? 1. Data storage, retrieval, and update 2. User-accessible catalog 3. Transaction support 4. Concurrency control services (it supports multiple users at the same time) 5. Recovery services 6. Authorization services 7. Support for data communication 8. Integrity services 9. Services that promote data independence 10. Utility services Lesson 4: The Relational Model (**only read section 4.4 - Views) 4.4 Develop access strategies to limit data access.C192 - Study Guide by Lesson Updated 02/09/2017 4.4 How can you limit data access within a relational DB model? Create a view. Views hide part of the database from certain users. Due to this, data aggregation and grouping are not permitted within views. Additionally, updates are only allowed if they contain simple queries based on primary keys or candidate keys. Views are virtual, meaning that they don’t necessarily exist in the database but they can be produced. Online When creating a view, what does the “WITH CHECK OPTION” clause do? It prevents insertion of rows that do not satisfy the WHERE condition. Lesson 5: Relational Algebra and Relational Calculus (**only read section 5.1 - The Relational Algebra) 5.1 Explain closure. 5.1 What is closure? Closure is the property of relational algebra that allows expressions to be nested within each other. 5.1 Identify which operation needs to be performed for the following scenarios: Intersection, Cartesian, join, outerjoin 5.1 Intersection: Results in rows that are common in the two tables Cartesian: Results in total the rows from both tables. Join: Results in rows that share the referenced attribute from the first table. Outerjoin: Results in all rows from both tables, adding nulls where the rows don’t have attribute values. (See this Lynda video for more information about Join (A.K.A. innerjoin) and outerjoin: Lynda Join Video) Lesson 6: SQL: Data Manipulation 6.1.1 Describe the benefits of using Structured Query Language (SQL). 6.1.1 What does SQL allow you to do? 1) Create databases and relation structures 2) Perform basic data management tasks (insertion, modification, and deletion of data) 3) Perform simple and complex queries 6.3.1 Write a select statement to extract data from tables. 6.3.1 Syntax: SELECT <desired column(s)> FROM <table name> WHERE <condition>; Example: SELECT * FROM STUDENTS WHERE UNIVERSITY = “WGU”; 6.3.1 Understand the different search conditions. 6.3.1 What are the 5 types of SQL search conditions? 1. Comparison (Compare the value of one expression to the value of another expression) 2. Range (Test whether the value of an expression falls within a specified range) 3. Set membership (Test whether the value of an expression equals one of a set of values) 4. Pattern match (Test whether a string matches a specified pattern) 5. Null (Test whether a column has a null value) 6.3.2 Sort data in tables. 6.3.2 Syntax: <complete SELECT query> ORDER BY <column> ASC/DESC; Example: SELECT GPA FROM STUDENTS WHERE UNIVERSITY = “WGU” ORDER BY ASC; Note: If you see ORDER BY <column> without ASC/DESC, it is ASC. 6.3.3 Aggregate data in tables. 6.3.3 Syntax: SELECT <columns> <function>(<column>) FROM <table>; Example: SELECT NAME, SUM(SALARY) FROM EMPLOYEE; Functions: COUNT Function: returns the number of values in a specified column. SUM Function: returns the sum of the values in a specified column. AVG Function: returns the average of the values in a specified column. MIN Function: returns the smallest value in a specified column. MAX Function: returns the largest value in a specified column. Online Which aggregate functions can be used on non-numeric fields? MAX MIN COUNT 6.3.4 Group data in tables. 6.3.4 Syntax: <complete SELECT query> GROUP BY <column>; Example: SELECT GPA, NAME FROM STUDENTS WHERE UNIVERSITY = “WGU” GROUP BY GPA; Note: The HAVING clause filters the query result set based on the condition. If it results to true, the data is included. Otherwise, it is removed from the query result set. 6.3.7 Perform multi-table queries. 6.3.7 Syntax: Depends on the type of join Example: SELECT c.clientNo, fname, lname, propertyNo, comment FROM Client c, Viewing v WHERE c.clientNo = v.clientNo; Types of Joins: Natural Join, Outer Join, Left join, Right join. 6.3.10 Insert, Update, Delete data in tables. 6.3.10 INSERT Syntax: INSERT INTO <table> VALUES (column1Value, column2value,…); INSERT INTO <table> <complete SELECT query>; UPDATE Syntax: UPDATE <table> SET <column> = <newValue> WHERE <condition>; DELETE Syntax: DELETE FROM <table> WHERE <condition>; Lesson 7: SQL: Data Definition ? Identify database installation and administration considerationsC192 - Study Guide by Lesson Updated 02/09/2017 ? ? 7.1 Identify data types that are used within a DBMS environment. 7.1 What are the data types that are used within a DBMS environment? Boolean Data: A truth value, “TRUE”or “FALSE”. CharacterData: A string of characters not used for numeric computation. They can either be a fixed or varied length. Bit Data: A binary digit that either has a value of 0 or 1. Exact Numeric Data: Numbers that have precision (the number of significant digits) and scale (the number of decimal places). Approximate Numeric Data: Used for defining numbers that do not have an exact representation (like π [pi]). Datetime Data: Defines points of time to a defined degree of accuracy. Interval Data: Used to represent a period of time. Large Object Data: Holds a large amount of data, such as a long text file or a graphics file. 7.1 Identify the different declarations of each data type that has more than one declaration. 7.1 What are the different declarations of character data types? How are they different? CHAR: Character data of a fixed length of characters. CHAR(2), for example contains 2 characters. VARCHAR: Character data of a variable length. VARCHAR(8), for example can contain any number of characters, up to a max of 8. 7.1 What are the different declarations of bit data types? How are they different? BIT: Bit data of a fixed length of bits. BIT(2), for example contains 2 bits of data. BIT VARYING: Bit data of a varying length. BIT(9), for example can contains any number of bits, up to a max of 9. 7.1 What are the different declarations of exact numeric data types? How are they different? NUMERIC: Used for storing numbers in a decimal notation (interchangeable with DECIMAL). DECIMAL: Used for storing numbers in a decimal notation (interchangeable with NUMERIC). INTEGER: Used for large positive or negative whole numbers. SMALLINT: Used for small positive or negative whole numbers BIGINT: Used for very large positive or negative whole numbers. - - - - - - - - - - - - - - - - - - - - - Identify main concepts associated with data marts. 31.4 What is a data mart? A database that contains a subset of corporate data to support the analytical requirements of a particular business unit (such as a Sales department) or to support users who share the same requirement to analyze a particular business process (such as property sales). Lesson 32: Data Warehousing DesignC192 - Study Guide by Lesson Updated 02/09/2017 32.4 Create star, starflake, and snowflake schemas 32.4 What is a star schema? A dimensional data model that has a fact table in the center, surrounded by denormalized dimension tables. 32.4 What is a starflake schema? A dimensional data models that has a fact table in the center, surrounded by normalized and denormalized dimension tables. 32.4 What is a snowflake schema? A dimensional data model that has a fact table in the center, surrounded by normalized dimension tables. 32.4 Which of the dimensional schemas avoids redundant data in favor of query performance? Snowflake (remember, normalized tables avoids redundant data) 32.5 Which of the dimensional schemas avoids query performance in favor of data redundancy? Star (remember, normalized tables avoids redundant data) 32.4 Determine when dimensionality modeling is appropriate for supporting high-performance access. 32.4.1 When is dimensionality modeling appropriate? Wen intuitive and high-performance retrieval of data is paramount. 32.4.1 Describe the major differences between dimensionality modeling and entity-relationship modeling 32.4 What is dimensionality modeling? When a model contains a fact table, a central table that contains a foreign key for each other table, and dimension tables, tables that contain a primary key that maps to the foreign key in the fact table. Lesson 33: OLAP 33 Describe the OLAP model, formats in which it can be presented, and how it can affect business decisions 33.1 What is OLAP (Online Analytical Processing)? The dynamic synthesis, analysis, and consolidation of large volumes of multidimensional data. 33 Identify main concepts associated with online analytical processing (OLAP) using business intelligence technologies. 33.1 What is the purpose of OLAP? OLAP enables users to gain a deeper understanding and knowledge about various aspects of their corporate data through fast, consistent, interactive access to a wide variety of possible views of the data. OLAP aims to solve the “why?” question, when analyzing data. 33.3.1 Describe appropriate formats for representing multidimensional data. 33.3.1 How do you represent multidimensional data? > relational table (think SQL tables) > matrix > data cube *Read entire chapter. Take good notes. 33.3.3 What analytical operations can be performed on a data cube? > Roll-up : Aggregations > Drill-down: Division (opposite of roll-up) > Slice/Dice: Look at data from different viewpoints (dimensions). > Pivot: Rotate the data to provide an alternate view 33.1 What is Business Intelligence (BI)? A data warehouse together will tools such as OLAP and/or data mining. Lesson 34: Data Mining 34.1 When should data mining be used? 34.1 When should data mining be used? When the enterprise has large volumes of data and 34.2 What types of information are generally presented as a result of a data mine? 34.1 What is the result of a data mine? Discovered patterns of information, formatted in various presentation methods. ? Explain how you would use XML, warehouses, or data mining to extract and present data ? Explain how you can use XML, data warehouses, and data mining. Data warehouses: A collection of historic data. Data Mining: Used to find unknown information from large databases. XML: Used to provide information, with no bearing on how it is presented. *Read entire chapter. Take good notes. 34.1 What is data mining? The process of extracting valid, previously unknown, comprehensible, and actionable information from large databases and using it to make crucial business decisions. 34.2 What are the four main operations associated with data mining techniques? 1. Predictive modeling 2. Database segmentation (separating the data into homogenous clusters) 3. Link analysis 4. Deviation detection Other Objectives (couldn't locate in material) Identify key relational model concepts that can be performed for a complex database. Identify database installation and administration considerations.C192 - Study Guide by Lesson Updated 02/09/2017 When performing a DBMS usability evaluation, which criterion is used? Robustness Describe how to create a database disaster recovery plan that addresses system crashes, media failure, application errors, natural disasters, human errors, and sabotage. See rows 223-228. [Show More]

Last updated: 2 years ago

Preview 1 out of 11 pages