Getting Started with Data Management

Data governance is the set of roles and policies that enforce proper data management and oversight, enabling value realization and competitive advantage while reducing privacy, regulatory, and security risks.

Discover how data stewardship supports data governance by clarifying roles, balancing HR data ownership, and deploying data stewards to manage confidentiality, integrity, availability, and legal obligations.

Master data management is a data governance discipline maintaining a single source of truth for an organization's core data elements by aligning processes and technology for cross-system consistency.

Develop data definitions to ensure data quality and a shared understanding of terms. Build a data dictionary with terms, definitions, owners, and possible values, and align stakeholders across the organization.

Standardization drives data governance by guiding how data is labeled, described, and categorized, with examples like state designations in databases and its impact on retrieval.

Explore tabular data with rows and columns and how csv files store records as comma-separated fields. Use Python to perform simple dataset processing with a powerful library.

Install and launch Jupyter notebooks across Windows and Linux using the Anaconda distribution, with Mac-adaptable steps, installer prompts, license acceptance, and initialization of Jupyter from the binary.

Load the US baby names csv with pandas in notebook, create dataframe, fix file paths, then filter 2014, sort by count descending, and extract four names with loc and iloc.

Load the state baby names CSV, build 2014 and California pandas series, filter with loc, sort by count descending, and reveal the top four names.

Explore how to measure name popularity using the US baby names data set since 2010, creating name and year series and visualizing frequency with a horizontal bar plot.

Identify nominal, ordinal, and numerical data types, with examples like party affiliations, shoe sizes, and salaries, and learn how data type choices guide meaningful operations in data management.

Visualize qualitative data with bar charts in Python using seaborn. Explore Airbnb listings in New York City 2019, comparing listings by neighborhood_group and average price.

Explore how database management systems use a database engine and front-end GUI to perform CRUD, manage maintenance, and support relational tables.

Explore the database development life cycle from needs assessment to maintenance, translating requirements into logical and physical designs, construction, user access, training, and ongoing upkeep.

Eliminate duplicate data to improve performance and reduce storage and maintenance costs by placing repeating fields in separate tables, such as students and professors, for consistent, easier data management.

Learn how data governance and standardization remove inconsistent data caused by human entry, and how creating unique identifiers in a separate professor table enables reliable, relational databases.

Break data into smaller components with descriptive columns to enable flexible queries in a relational database, avoiding multipart fields and enabling easy reports on customers by state.

Enforce proper database design to preserve data integrity by requiring essential fields, such as billing address, credit card, and shipping address, and by validating numeric fields greater than zero.

Designate a primary key to uniquely identify every row, ensuring uniqueness, nonempty, and typically immutability. It may be a natural cue or a composite key for many-to-many relationships.

Explore how a one to many relationship links a room type to many rooms, with the parent primary key and child foreign key enforcing valid records.

Explore many-to-many relationships using a join table with a composite primary key from student and course keys, including session year to prevent duplicate enrollments.

Enforce business rules at the database level with integrity constraints such as not null, unique, primary key, foreign key, and check constraints, ensuring the database itself prevents data corruption.

Learn how indexing speeds data retrieval by targeting search-heavy columns and foreign keys, avoiding full table scans, and remember primary keys are indexed by default.

Highlight the importance of SQL in data management, preview SQL basics, download Oracle express edition after creating an account, and note Mac limitations with a path to Oracle Live SQL.

Install Oracle XE by extracting the zip into a folder, running the setup, accepting the license, creating a password, and allowing the Windows Security Alert.

Open the Oracle XE database from the command prompt, connect with the created password, unlock the HR schema, and download Oracle's SQL Developer with JDK for future setup.

Extract the SQL developer file, launch the exe, and connect to the database with service name xcpdb1 using your credentials; test by selecting all columns from departments.

Follow along with Oracle Live SQL on any OS by creating a free Oracle account; examine the department's table and fix a missing skimmer name.

View the employees table in Oracle Live SQL under the HR schema, select specific columns, alias net salaries, identify one-to-many relationships, and retrieve distinct departments.

Restricting and sorting data using filters such as in, not, between, and like, and ordering by salary in descending order and by hire date to show most recently hired.

Explore single-row functions for text formatting—lower, first letter capitalized, upper—and length, plus concatenation with aliases; compute net income after 20% tax, then use nvl to replace nulls with zero.

Explore multi-row aggregate functions such as average, minimum, maximum, sum, and count, and use group by and having to analyze departments, salaries, and managers.

Learn to display data from multiple tables with inner joins and aliases to pull department names, job titles, and locations, and generate payroll reports by country.

Use subqueries to answer multi-step questions by combining inner and outer queries, such as calculating who earns above the average or who reports to a manager.

Set up an Ubuntu Linux virtual machine using a hypervisor such as VMware Workstation Pro or VirtualBox, download Ubuntu 20 from OSBoxes, and extract with WinRAR.

Set up an Ubuntu virtual machine with a hypervisor, attach the existing vmdk disk, boot, log in, and explore the Linux terminal for basic connectivity and commands.

Connect to a linux host via sftp using cyberduck, locate the vm ip with ifconfig, start ssh, and drag and drop files between the Ubuntu virtual machine and the host.

Explore data integration basics, including extraction, transformation, and loading, while setting up Postgres on Ubuntu, creating a database user and database, and testing with psql.

Connect to a Postgres database named github warehouse and set up a movies table with a serial id primary key; then implement ratings table to illustrate one-to-many and many-to-many relationships.

Download Python, verify the installation, install the Python package manager, and download PySpark to get started with data management.

Install Java and the JDBC driver for Postgres, using the required command, then download the driver to complete the Postgres setup.

Learn an ETL pipeline that extracts data from a database, transforms it for analytics, and loads it back, using Python and Spark with a JDBC driver.

Transform data by creating a spark session, extracting the movie and user tables, applying group by and avg on ratings, joining on the movie id, and printing the results.

Finish ETL process by loading transformed data into the database with load.py, wrapping routines in functions, creating an average ratings table that overwrites if it exists, then verify in Postgres.

Explore how big data drives insights through volume, velocity, and variety, and learn why parallel processing, storage advances, and data veracity matter in data management.

Explore the three data types: structured, semi-structured, and unstructured, and learn how each uses organization, markers, and metadata to influence management, search, and analysis.

Examine why relational systems struggle with big data scalability, real-time ingest, and machine-learning workloads. Compare relational limits to Hadoop, a distributed file system, and NoSQL options like Redis and MongoDB.

Explore Hadoop, the first open source big data project built as an implementation of MapReduce and the Google File System; learn its MapReduce and HDFS components and cloud service options.

Set up Hadoop with the Bitnami virtual machine, selecting the right image format and importing it into your VM. Enable SSH password authentication, restart ssh, and verify the VM IP.

Power up the virtual machine, connect to the Bitnami VM with the prior lecture’s IP as the Bitnami user, accept the fingerprint, and upload airbnb_nyc_2019.csv via Cyberduck.

Interact with big data on Hadoop using Hive to issue SQL-like queries, import CSV to HDFS, and create an external table that points to the file for quick data access.