Database Management

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/60

encourage image

There's no tags or description

Looks like no tags are added yet.

61 Terms

1
New cards

Purpose of ER Modeling

enables the structuring and organizing of the requirements collection process and provides a way to graphically represent the requirements.

2
New cards

Entity (Database)

A person, item, event, for which an organization collects data. An example would be customer or student. Represented by Rectangle.

3
New cards

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.

4
New cards

Unique attribute

attribute whose value is different for each entity instance

5
New cards

Cardinality Constraints

the number of instances of one entity that can or must be associated with each instance of another entity

6
New cards
<p>Mandatory Many</p>

Mandatory Many

Minimum One, Maximum Many

7
New cards
<p>Mandatory One</p>

Mandatory One

Minimum one, maximum one

8
New cards
<p>Optional Many</p>

Optional Many

Minimum None, Maximum Many

9
New cards
<p>Optional One</p>

Optional One

Minimum None, Maximum One

10
New cards
<p>One-to-One Relationship</p>

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.

11
New cards
<p>One-to-Many Relationship</p>

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.

12
New cards
<p>Many-to-Many Relationship</p>

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.

13
New cards
<p>Composite Attribute</p>

Composite Attribute

An attribute that can be further subdivided to yield additional attributes. Name becomes first name and last name.

14
New cards

Candidate Key

When an entity has more than one unique attribute, each unique attribute is also called a candidate key

15
New cards
<p>Derived Attribute</p>

Derived Attribute

An attribute whose values can be calculated from related attribute values.

16
New cards

Binary relationship

A relationship between the instances of two entity types.

17
New cards
<p>Weak Entity</p>

Weak Entity

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

18
New cards
<p>Associative Entity</p>

Associative Entity

An ER diagram construct used as an alternative way of depicting M:N relationships.

19
New cards

Primary Key

a field that uniquely identifies a record in a table

20
New cards
<p>Partial Key</p>

Partial Key

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

21
New cards
<p>SELECT (SQL)</p>

SELECT (SQL)

Used to collect fields from a given table.

22
New cards
<p>FROM (SQL)</p>

FROM (SQL)

Paired with the SELECT statement. Specifies which table the information should come from.

23
New cards
<p>ORDER BY (SQL)</p>

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.

24
New cards
<p>WHERE (SQL)</p>

WHERE (SQL)

Defines a condition, filtering out rows that don't match

25
New cards
<p>GROUP BY (SQL)</p>

GROUP BY (SQL)

Enables summarizations across the groups of related data within tables.

26
New cards
<p>JOIN in SQL</p>

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

27
New cards

What is a data warehouse?

A structured repository of integrated, subject-oriented, enterprise-wide, historical, and time-variant data.

28
New cards

What is the purpose of a data warehouse?

The retrieval of analytical information.

29
New cards

What types of data can a data warehouse store?

Detailed and/or summarized data.

30
New cards

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.

31
New cards

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.

32
New cards

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.

33
New cards

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.

34
New cards

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.

35
New cards

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.

36
New cards

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.

37
New cards

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.

38
New cards

What is the primary purpose of operational databases?

To support an application serving one or more business operations and processes.

39
New cards

What orientation do operational databases have?

Application oriented.

40
New cards

What is the primary purpose of analytical data?

For the analysis of one or more specific business subject areas.

41
New cards

What orientation do analytical data have?

Subject oriented.

42
New cards

Structured Repository

- the data warehouse is a database containing analytically useful information

43
New cards

Integrated Data Warehouse

refers to the process of bringing the data from multiple operational

databases into a single data warehouse.

44
New cards

Subject-oriented

a data warehouse is developed to analyze specific business subject areas.

45
New cards

enterprise-wide

refers to the fact that the data warehouse provides an organization-wide view of the analytically useful information it contains

46
New cards

Historical

refers to the larger time horizon in data warehouses than in operational databases.

Can contain multiple years worth of data

47
New cards

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.

48
New cards

Data Mart

contains a subset of data warehouse information. Designed to serve the specific analytical needs of a single business unit, group, or department.

49
New cards

Dependent Data Mart

a data mart that depends on the existence of a data warehouse

50
New cards

Independent Data Mart

--stand alone data mart, created in the same fashion as the data warehouse

--has its own source systems and ETL infrastructure

51
New cards

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.

52
New cards

ETL

Extract, Transform, Load

53
New cards

Extract

• automatic extraction of relevant data from the operational data sources

54
New cards

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.

55
New cards

Load

• the seamless load of the transformed data into the data warehouse

56
New cards

Major components of data warehousing

Source Systems, ETL infrastructure, data warehouse.

57
New cards

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.

58
New cards

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.

59
New cards

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

60
New cards

Data Warehouse Front End Applications

the design and creation of data warehouse front-end applications can take place in parallel with data warehouse creation

61
New cards

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.