WGU C175 Data Management Foundations Study Guide

son 1 Introduction to Databases, Information and Data (7 Questions) Competency 4017.1.1: Introduction to Information and Data Explain how data, databases, and data management is used in today's organization Explain what data is and how is it stored Explain the different types of data Define file structures Explain how data was stored and retrieved before the use of database management systems 1.1Why Databases •Best way to store and manage data •Makes data persistent and shareable in a secure way •Essential for organizations to know information about Customers, Products, Employees, Debtors / Creditors •Helps organizations use data / information as a resource to make decisions 1.2File Structures •File-Based (Prior to DBMS) oAttempt to computerize old manual filing system oWas easy to retrieve small amounts of information from oAs data grew, it became difficult to locate and retrieve information oRetrieval speed depended on file structure and how data was organized within the structure •File types utilized in a file-based system oFlat Files •No internal hierarchy •Plain text or Binary files •Data that is unstructured •Uses a lot of computer memory to access, edit, and rewrite files after use oHeap Files •Unsorted set of records •Uniquely identified by record ID o Allows Heap file to be inserted or deleted using ID oIndex Files •File that stores a list of Look Up Field values from data file that includes the location (address) in the data file of the corresponding record •Smaller than the entire record, usually fits in main memory for quick look up •Lists keywords and phrases that are in a particular file •Becomes a pointer to the location in the file where the information can be found when stored with keywords and phrases oHashed Files •Uses hash functions to decide where records should be placed on a disk •Allows for faster data look up without the use of an index file 1.3Data versus Information •Data oData is the Foundation of Information oConsists of Raw Facts not yet processed to reveal meaning oRaw facts / data will need to be transformed to better understand what it is that you have oMust be properly formatted for storage, processing, and presentation •Information oInformation is the Bedrock of Knowledge oProduced by Processing Raw Data to reveal its meaning •Processing may be as simple as organizing it to reveal patterns or complex as making forecast / drawing inferences using system statistical modeling oKey to good decision making and business survival is accurate, relevant, and timely information •Knowledge oInformation and facts about a specific subject oImplies familiarity awareness and understanding of Information oKey Characteristic is that New Knowledge can be derived from Old Knowledge •Data Management oA discipline that focuses on the proper generation, storage, and retrieval of data oIs it core activity for any business, government agency, service organization, or charity 1.4Introducing the Database •Database oShared, integrated computer structure that stores: •End User Data o Raw facts of interest to the end user •Metadata (Also called: A collection of Self-Describing Data) oData about data through which data is integrated and managed oDescribes data characteristics and set of relationships that links data found in the database oStores information like: •Name of each data element •Type of values (numeric, dates, or text) on each data element •Whether the data element can be left empty oComplements and expands the value and use of the data •Role of the DBMS oAn intermediary between user and database oPresents the end user with a single integrated view of data in a database oReceives all application requests and translates them into complex operations oHides database complexities from the application programs and users oProvides a framework facilitate data quality initiatives oMay be written using Visual Basic, NET, JAVA, C#, or through a DBMS utility program •Advantages of a DBMS oEnables data sharing across multiple applications / users oIntegrates different user views of data into a single repository oMakes data management more efficient and effective oImproves Data Sharing •Creates an environment in which users have better access two more and better-managed data oImproves Data Security •Provides framework for better enforcement of data / security policies oBetter Data Integration •Promotes integrated view of operations and the ability to see data relations or impact actions have in other areas oMinimizes Data Inconsistency •Data Inconsistency exists when different versions of the same data appear in different places •Greatly reduced in a properly designed database oImproves Data Access •Easier to produce quick answers to a query / ad hoc query oQuery is a specific request / question oAd Hoc Query is a “Spur-or-the-Moment” question oQuery Result Set is a collection of data rows returned by a query oImproves Decision Making •Better managed, access and quality or data facilitates better decision-making through data quality initiatives o Data Quality is a comprehensive approach to promoting the accuracy, validity, and timeliness of data oIncreases End-User Productivity •The availability of data, combined with the tools that transforms data into usable information, empowers end users to make quick informed decisions that make a difference between success or failure •Type of Databases oNumber of Users •Single User oSupports one user at a time oRuns on a personal computer oCalled a Desktop Database •Multiuser oSupports multiple users at the same time oWorkgroup •Used by multiple people at the same time and supports a small number of users (usually fewer than 50) or a specific department oEnterprise •Used by the entire organization and supports many users (more than 50, usually hundreds) across many departments oLocation •Centralized oData located at a single site oRequires well-defined infrastructure oInfrastructure owned / maintained by organization •Distributed oData distributed across several sites oRequires well-defined infrastructure oInfrastructure owned / maintained by organization •Cloud oProvided by third-party vendors (Microsoft Azure or Amazon AWS) oVendors, provide defined performance measures (data storage capacity, required throughput, and availability) oAs business demands or requirements grow, performance capabilities can be renegotiated with the cloud provider oType of Data Stored •General Purpose oWide variety of data used in multiple disciplines •Discipline Specific oData focused on specific subject areas oDatabase Use / Time Sensitivity of Information •Most popular way of classifying databases •Operational Database oSupports and reflects critical day-to-day operations that must be recorded accurately and immediately •Transactions (Sales, Payments or Purchases) oAlso known as Online Transaction Processing Database (OLTP), Transactional Database or Production Database •Analytical Database oStores historical data and business metrics for tactical or strategic decision making oEnd users can perform advanced analysis of data using sophisticated tools •Main components: oData Warehouse •Specialized data that stores data in format optimized for decision support •Contains historical data obtained from operational databases as well as data from other external sources oOnline Analytical Processing (OLAP) •Tools that work together to provide advanced data analysis for retrieving, processing, and modeling data from the data warehouse •Business Intelligence oApproach to capture / process business data, generating information that supports business decision-making oData Structure •Corporations use unstructured and semi structured and structured data •Unstructured (unorganized) oData in its original raw state or format in which it was collected oIn a format that does not lend itself to the processing that yields information •Structured (organized based on a thing / characteristic) oResult of formatting unstructured data to facilitate storage, use, and the generation of information •Semi Structured Data oData that has already been processed to some extent oExtensible Markup Language (XML) Database •Uses special language to represent and manipulate data elements in a textual format •Supports the storage and management of Semi Structured XML data oSpecialized Database •NoSQL (Not only SQL) oNew generation of DBMS not based on traditional relational database model oDesigned to handle unprecedented volumes, variety types, structure of data IOT support the velocity of data operations required in businesses 1.5Why Database Design is Important •A well-designed database facilitates data management and generates accurate invaluable information •Proper database design requires an understanding of it use oTransactional databases require accurate and consistent data and operational speed oData warehouse •A poorly designed database is likely to become a breeding ground for difficult- to-trace errors that may lead to poor decision making—and poor decision making can lead to the failure of an organization 1.6Evolution of File System Data Processing •Manual File Systems oHistorically manual paper and pencil systems oPapers organized by expected use of the data oTypically organized into file folders and filing cabinets oWorked well for small collections of data and users had few reporting requirements oAs organizations grew and reporting requirements became more complex, tracking data became more difficult oReport generation was slow and cumbersome •Computerized File Systems oData Processing Specialist hired to create processes that would better leverage computer capabilities to track data and produce reports required oInitially mirrored manual file system structures (file folders and filing cabinets) oRequire the creation of specialized vocabulary oToo many data files that contained related—often overlapping—data with no means of controlling or managing the data consistently •Each file used its own application program to store, retrieve, and modify data •Each file was owned by the individual or department that created it. 1.7Problems with File System Data Processing •Lengthy Development Time oSimplest data-retrieval task requires extensive programming oOlder file systems required programmers to specify what must be done and how to do it •Difficulty Getting Quick Answers oPrograms had to be written even for the simplest reports oProducing Ad Hoc Queries was impossible •Complex System Administration oAs number of files in the system expanded, system administration become more difficult oSimple file systems required creating and maintaining several file management programs to add, modify, or delete records; to list file contents; and to generate reports •Lack of Security and Limited Data Sharing oSharing data among multiple geographically dispersed users introduces security risks oRudimentary security options of some programs / files may not be used or are insufficient for robust data sharing among users oData management and reporting program security and data-sharing features difficult to program oData Redundancy possible resulting in wasting computer memory oData Inconsistency is possible due to possible data redundancy oLimited Data Sharing may result in data isolation making it difficult to share data / files amount different applications (Data / files stored in different folders, from different departments, I different formats oIntegrity Problems data files in a database must meet rules / constraints •Extensive Programming oDifficult to make changes to an existing file structure oChanging just one field in the original would require: •Reading the original file •Transforming original data to conform to the new structure’s storage requirements •Writing transformed data into the new file structure •Repeating preceding steps for each record in the original file •Structural Dependence o Data characteristic in which a change in the database schema, affects database access, requiring changes to all access programs file changes •Structural Independence o Data characteristic in which changes in the database schema DOES NOT affect database access •Data Dependence oA data collection in which data representation and manipulation are dependent on physical data storage characteristics affecting access to the data oFile systems are extremely cumbersome in programming and management •Data Independence o A data collection in which data representation and manipulation IS NOT dependent on physical data storage characteristics and DOES NOT affect access the data •Data Redundancy oSame data stored unnecessarily in multiple places (scattered in different locations “Islands of Information”) oIf not controlled creates: •Poor Data Security oBy increasing the chances of a copy of data being susceptible to unauthorized access •Data Inconsistency