week 8: Databases (Part 2) Flashcards
DATA INTEGRATION
ETL: extract, transform, load
extract data from source systems
transform them
and load them into a data mart or warehouse
how can one ETL?
performed by hand-written code (e.g., SQL queries)
by commercial data- integration software
“a single version of the truth”
central enterprise data warehouse without data marts
The most common architecture
Independent data marts
store data for a single or a few applications
Hub and spoke
stores data in a central data warehouse
maintains dependent data marts that obtain their data from the central repository
helps to maintain a single view of the data since it is centrally organized
metadata
Data about data
The quality of the data in the warehouse must be adequate to?
to satisfy users’ needs
what does data governance require?
requires that people, committees, and processes be in place to plan and control the development and use of the data warehouse
data users
IT developers
front-line workers
analysts
information workers
managers and executives
suppliers
customers
regulators
Knowledge management (KM)
a process that helps organizations manipulate important knowledge that is part of the organization’s memory
Knowledge
information that is contextual, relevant, and useful,
developed with the assistance of expertise
also known as Intellectual capital (or intellectual assets)
Explicit knowledge
objective, rational, technical knowledge that has been documented
Examples: policies, procedural guides, reports, products, strategies, goals, core competencies
Tacit knowledge
cumulative store of subjective or experiential learning
Examples: experiences, insights, expertise, know-how, trade secrets, understanding, skill sets, and learning
Knowledge management systems (KMSs)
systematize, enhance and expedite knowledge management
goal is to make the most productive use of knowledge
the KMS cycle
- create
- capture
- refine
- store
- manage
- disseminate
- turns into knowledge
- create
Query Languages
search for information in databases or data warehouses
Normalization
optimize the tables in a relational database
Joins
link relational database tables with common attributes
SQL
Structured Query Language
Typical key words from SQL
SELECT (what to locate)
FROM (specify the source files)
WHERE (provides conditions for the search, like an “if” statemen)
QBE
Query by Example
Uses a form (template) to provide a sample or description of the desired information
entity-relationship (ER) modeling
the database design in a process
consists of entities, attributes and relationships organized using business rules
Entities are shown as rectangles, with relationships shown on the lines between them
what do business rules in ER modeling describe?
describe how the organization uses its data to run its operations
cardinality
the number of times a single record/instance of one entity can be associated with a single record/instance of another entity
types of caridnalities
mandatory single
optional single
mandatory many
optional many
optional single (cardinality)
ex: employee wage rate with pay cheque (employees may not be paid if they are on vacation or leave)
mandatory many (cardinality)
ex: epartment details with employee details
optional many (cardinality)
ex: ustomer details with customer sales details/invoices (not all customers purchase every month)
mandatory single (cardinality)
ex: inventory quantity on hand with its sale price
normalization
analyzing and reducing a relational database to its most streamlined form
provide minimum redundancy (minimize duplicated attributes)
reduce non-key attributes
Improve processing efficiency of the database
