1/60
Looks like no tags are added yet.
Purpose of ER Modeling
enables the structuring and organizing of the requirements collection process and provides a way to graphically represent the requirements.
Entity (Database)
A person, item, event, for which an organization collects data. An example would be customer or student. Represented by Rectangle.
Attribute (database)
an individual data item stored for an entity, for example, for a person, attributes could include name, address, date of birth. Represented by oval.
Unique attribute
attribute whose value is different for each entity instance
Cardinality Constraints
the number of instances of one entity that can or must be associated with each instance of another entity

Mandatory Many
Minimum One, Maximum Many

Mandatory One
Minimum one, maximum one

Optional Many
Minimum None, Maximum Many

Optional One
Minimum None, Maximum One

One-to-One Relationship
In databases, a relationship in which each record in Table A can have only one matching record in Table B, and vice versa.

One-to-Many Relationship
A relationship between two tables in a database in which one record in the primary table can match many (zero, one, or many) records in the related table.

Many-to-Many Relationship
In databases, a relationship in which one record in Table A can relate to many matching records in Table B, and vice versa.

Composite Attribute
An attribute that can be further subdivided to yield additional attributes. Name becomes first name and last name.
Candidate Key
When an entity has more than one unique attribute, each unique attribute is also called a candidate key

Derived Attribute
An attribute whose values can be calculated from related attribute values.
Binary relationship
A relationship between the instances of two entity types.

Weak Entity
An ER diagram construct used to depict entities that do not have a unique attribute of their own. Double lines around entity.

Associative Entity
An ER diagram construct used as an alternative way of depicting M:N relationships.
Primary Key
a field that uniquely identifies a record in a table

Partial Key
attribute of a weak entity that combined with the unique attribute of the owner entity uniquely identifies the weak entity's instances

SELECT (SQL)
Used to collect fields from a given table.

FROM (SQL)
Paired with the SELECT statement. Specifies which table the information should come from.

ORDER BY (SQL)
Shows what to order data by, such as age. This will be ascending by default, but you can put a DESC after it to put it in descending order.

WHERE (SQL)
Defines a condition, filtering out rows that don't match

GROUP BY (SQL)
Enables summarizations across the groups of related data within tables.

JOIN in SQL
Joins allow you to link data from two or more tables together into a single query result--from one single SELECT statement.
Forms relationships between tables
What is a data warehouse?
A structured repository of integrated, subject-oriented, enterprise-wide, historical, and time-variant data.
What is the purpose of a data warehouse?
The retrieval of analytical information.
What types of data can a data warehouse store?
Detailed and/or summarized data.
Why is a data warehouse created as a separate data store?
It is so that it can focus more specifically on data analysis rather than doing both operational processes
and analysis processes.
Data time-horizon difference
Operational systems often have shorter time-horizons than analytical systems. Operational systems
usually have a data time-horizon of 60-90 days while analytical systems often span years of data.
Data Level-of-Detail Difference
Operational data typically reflects detailed data. Summations of data are not physically stored but
implemented as derived attributes using formulas. Analytical databases can contain both detailed and
summarized data that is stored physically instead of derived.
Data Time-Representation Difference
Operational data typically represents the current situation in the real world, while analytical data can
represent both the current situation and snapshots from the past.
Describe the difference in queried amounts of data and frequency of queries between operational
and analytical data.
Operational queries typically process much smaller amounts of data than analytical queries. On the other
hand, operational queries are typically issued much more often and by more users than analytical queries.
Data Update Difference
Data in operational systems is regularly updated by the users. Users of analytical databases are only able
to get data and cannot modify or update data.
Data Redundancy Difference
The goal of eliminating redundancy in databases is not as important in analytical databases as it is in
operational databases. This is because analytical data cannot be modified or updated by end users, and so
aren't at risk of update anomalies like operational data is.
Data Audience Difference
Operational data is used by all kinds of employees, customers, and users while analytical data is used by
fewer users for more decision-making purposes.
What is the primary purpose of operational databases?
To support an application serving one or more business operations and processes.
What orientation do operational databases have?
Application oriented.
What is the primary purpose of analytical data?
For the analysis of one or more specific business subject areas.
What orientation do analytical data have?
Subject oriented.
Structured Repository
- the data warehouse is a database containing analytically useful information
Integrated Data Warehouse
refers to the process of bringing the data from multiple operational
databases into a single data warehouse.
Subject-oriented
a data warehouse is developed to analyze specific business subject areas.
enterprise-wide
refers to the fact that the data warehouse provides an organization-wide view of the analytically useful information it contains
Historical
refers to the larger time horizon in data warehouses than in operational databases.
Can contain multiple years worth of data
Time-variant
Refers to the fact that a data warehouse contains slices or snapshots of data from
different periods of time across its time horizon.
Data Mart
contains a subset of data warehouse information. Designed to serve the specific analytical needs of a single business unit, group, or department.
Dependent Data Mart
a data mart that depends on the existence of a data warehouse
Independent Data Mart
--stand alone data mart, created in the same fashion as the data warehouse
--has its own source systems and ETL infrastructure
Iterative Nature of Warehouse Requirements
the process is not collecting,
defining, and visualizing all requirements in one go but instead refining and adding requirements after
each step of the process as needed. It can change and adapt.
ETL
Extract, Transform, Load
Extract
• automatic extraction of relevant data from the operational data sources
Transform
Transforming
the extracted data so that its quality is assured and the structure conforms to the structure of the modeled
and implemented data warehouse.
Load
• the seamless load of the transformed data into the data warehouse
Major components of data warehousing
Source Systems, ETL infrastructure, data warehouse.
Source system
Operational databases and other sets of data used for operational purposes that provide analytically useful information for the data warehouse's subjects of analysis.
Steps of Data Warehouse Development
Requirements collection, development, and visualization, data warehouse modeling, creating the data
warehouse, creating ETL infrastructure, developing front-end applications, data warehouse deployment,
data warehouse use, data warehouse administration and maintenance.
Creation of a data warehouse
This step involves using database management software and implementing the data warehouse model as
the actual data warehouse that is initially empty
Data Warehouse Front End Applications
the design and creation of data warehouse front-end applications can take place in parallel with data warehouse creation
Data Warehouse Deployment
This step involves releasing the data warehouse and its front-end applications for use by the end users. Typically, prior to this step, an initial load is executed to populate the implemented data warehouse with an initial set of data from the operational data sources via the ETL infrastructure.