5346 final exam review

The number of entity types that participate in a unary relationship is: 0 1 3 2

1

Data Mart

A Data warehouse limited in scope

outer join

A join in which rows that do not have matching values in common columns are nevertheless included in the result table.

cross join

A join in which rows that do not have matching values in common columns are nonetheless included in the result table

Equi-join

A join in which the joining condition is based on equality between values in the common columns. Common columns appear (redundantly) in the result table.

What will be returned when the following SQL statement is executed? SELECT driver_no,count(*) as num_deliveries FROM deliveries group BY driver_no; A listing of each driver and the number of items marked "*" in their deliveries A listing of all drivers, sorted by driver number A listing of each driver as well as the number of deliveries that he or she has made A count of all the deliveries made by all drivers

A listing of each driver as well as the number of deliveries that he or she has made

DML (Data Manipulation Language)

A part of SQL that is used query, insert, update and remove data from a database

DDL (Data Definition Language)

A part of SQL that is used to create and modify objects of a database such as tables, views, functions and stored procedures

DCL (Data Control Language)

A part of SQL that is used to manage permissions to objects in a database

Join

A relational operation that causes two or more tables with a common domain to be combined into a single table or view

In which of the following situations would one have to use an outer join in order to obtain the desired results? A Report is desired that lists all customers and the total of their orders A report is desired that lists all customers who places an order There is never a situation that requires only an outer join A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).

A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).

Referential Integrity Constraint

A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null.

Star Schema

A simple database design in which dimensional data are separated from fact or event data. A dimensional model is another name

Data Warehouse

A subject-oriented, integrated,Time-variant, non-updatable collection of data used in support of management decision-making processes

which of the following about enhanced entity relationship modeling is correct? When subtypes are overlapping, an additional field must be added to the supertype to act as a discriminator A subtype can become a supertype if the subtype has another subtype underneath it In a supertype/subtype relationship, attributes are assigned at the highest logical level that is possible in the hierarchy subtypes at the lowest level of the hierarchy do not inherit attributes from their ancestors Regardless of the business situation, a member of the supertype is always a member of more than one subtype

A subtype can become a supertype if the subtype has another subtype underneath it In a supertype/subtype relationship, attributes are assigned at the highest logical level that is possible in the hierarchy

Relationships

A well-structured database establishes the relationships between entities that exist in organizational data so that desired information can be retrieved.

The SQL command_____ adds one or more new columns to a table. CREATE RELATIONSHIP CREATE VIEW CREATE TABLE ALTER TABLE

ALTER TABLE

A ______ is a set of application routines that programs use to direct the performance of procedures by the computers operating system LAN API MOM RPC

API ( Application Programming Interface)

Domain

All of the values that appear in a column of a relation must be from the same domain

Deadlock Resolution

Allows deadlock to occur

Locking mechanisms

Allows for one user to access the data at a time to prevent this error

Homonyms

An attribute name that may have more than one meaning

Union

An operator that combines two resultsetshaving the same number and types of columns

Improved Data Sharing

Authorized internal and external users are granted permission to use the database, and each user

Which of the following factors drive the need for data warehousing? Data warehouses generally have better security Informational data must be kept together with operational data Businesses need an integrated view of company information Reduce virus and trojan horse threats

Businesses need an integrated view of company information

Improved Data Consistency

By eliminating or controlling data redundancy, you can greatly reduce the opportunities for inconsistency.

Churn

Canceling a service to switch to another

A join operation: causes two disparate tables to be combined into a single table or view Brings together data from two different fields is used to combine indexing operations Causes two tables with a common domain to be combined into a single table or view

Causes two tables with a common domain to be combined into a single table or view

Big Data systems

Collection and storage of data; 'schema on read'

An attribute that can be broken down into smaller parts is called an _____ attribute Associative Complex Simple Composite

Composite

Database Server

Computer responsible for storage, access, and processing in an environment

Fact Tables

Contain factual or quantitative data (measurements that are numerical, continuously valued, and additive) about a business, such as units sold, orders booked, and so forth

Big Data

Data that exists in very large volumes and many different varieties and that need to be processed at a very high velocity

To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL Select command. A) alter B) distinct C) check D) specific

Distinct

A ______ prevents another transaction from reading and therefore updating a record until it is unlocked. Exclusive lock authorization rule shared lock record controller

Exclusive lock

Strong Entity Type

Exists independently of other entity types

Grain and duration have a direct impact on the size of ________ tables. Selection figure fact grain

Fact

Data

Facts concerning objects and events that could be recorded and stored on computer media.

data redundancy is used to establish relationships between data but is never used to improve database performance. True False

False

database development begins with the design of the database. True False

False

A client PC that is responsible for processing presentation logic, extensive application, and business rules logic as well as many DBMS functions is called a ___. Server Workstation fat client thin client

Fat Client

The analysis of data or information to support decision making is called: Artificial intelligence operational processing Informational processing data scrubbing

Informational processing

Size of Fact Table

Number of rows = Product of numbers of possible values for each dimension associated with the fact table

OLAP

Online analytical processing the manipulation of information to create business intelligence in support of strategic decision making.

Churn Prediction

Process of using transaction data to identify who are likely to cancel subscription

MetaData

Properties of end-user data, and context of data

Which of the following are not a good characteristic of a data name Readable Relates to business characteristics Relates to a technical characteristic of the system Repeatable

Relates to a technical characteristic of the system

DROP TABLE

Removes a table from your schema

DELETE

Removes rows from a table but keeps table structure

With the database approach, data descriptions are stored in a central location known as a: PC Repository Mainframe Server

Repository

Relational Databases

Represents data as a collection of tables

Traditional Relational Databases

Requires data models to be defined before data are written; 'Schema on write'

the ______ is the structure that contains descriptions of objects such as tables and views created by users Catalog masterview SQL Schema

Schema

A common encryption method to secure data traveling between a client and a server is called: RSA Secure Sockets Layer (SSL) ITT Secure Synchronization Layer (SSL)

Secure Sockets Layer (SSL)

What does the following SQL statement do? SELECT * From Customer WHERE Cust_type = "Best" Selects fields with a "*" in them from the customer table Selects all the fields from the customer table for each row with a customer labeled "*" Selects the field "*" from the customer table for each row with a customer labeled "Best" Selects all the fields from the Customer table for each row with a customer labeled "Best"

Selects all the fields from the Customer table for each row with a customer labeled "Best"

Departmental Multi-Tiered Client/Server Databases is stored on a central device called a: Remote PC Server network Client

Server

Improved Decision Support

Some databases are designed expressly for decision support applications.

Predictive analytics

Statistical and analytical techniques used to develop models that predict future events or behaviors.

Operational

Stores Status Data

Which of the following is an entity that exists independently of other entity types? Variant strong weak Codependent

Strong

The characteristic that indicates that a data warehouse is organized around key high-level entities of the enterprise is: Subject-oriented integrated nonvolatile time-variant

Subject-oriented

Which of the following is a basic method for single-field transformation? Table Lookup Filed-to-field communication cross-link entities cross-linking attributes

Table Lookup

What result set will the following query return? Select Item_No, description from item where weight > 100 and weight < 200; A) The Item_No and description for all items weighing less than 100 B) The Item_No for all items weighing between 101 and 199 C) The Item_No and description for all items weighing between 101 and 199 D) The Item_No for all items weighing more than 200

The Item_No and description for all items weighing between 101 and 199

Determinant

The attribute on the left side of the arrow in a functional dependency

Improved Data Quality

The database approach provides a number of tools and processes to improve data quality.

locking level (lock granularity)

The extent of a database resource that is included with each lock.

Churn Rate

The measure of the amount of individuals moving out of a group over time

which of the following about modeling business constraints in eer modeling is correct? The overlap rule specifies that if an entity instance of the supertype is a member of one subtype, it can simultaneously be a member of two or more subtypes Genarlization is a topdown process A completeness constraint may specify that each entity of the supertype must be a member of some subtype in the relationship A member of a subtype does not necessarily have to be a member of the supertype The disjoint rule specifies that if an entity instance of the supertype is a member of one subtype, it must simultaneously be a member of an other subtype

The overlap rule specifies that if an entity instance of the supertype is a member of one subtype, it can simultaneously be a member of two or more subtypes A completeness constraint may specify that each entity of the supertype must be a member of some subtype in the relationship

Concurrency Control

The process of managing simultaneous operations against a database so that integrity is maintained

Program Data Independence

The separation of data descriptions (metadata) from the application programs that use the data

Seperation of metadata from application programs that use the data is called data independence. True False

True

The external schema contains a subset of the conceptual schema relevant to a particular group of users. True False

True

Because applications are often developed independently in file processing systems: Unplanned duplicate data files are the rule rather than the exception there is a large volume of the file I/O Data can always be shared with others The data is always non-redundant

Unplanned duplicate data files are the rule rather than the exception

Deadlock Prevention

Users must lock all records they require at the beginning of a transaction. Instead of one at a time

A datawarehouse derives its data from: Various operational data sources A data mart on-line transactions Reports

Various operational data sources

According to the course lecture, Which of the following terms are used to chaaracterize "Big Data"? Select all that apply. Velocity Viscosity Variety Viability variability Volume

Velocity Variety Volume

Given a table names store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? INSERT INTO store values ('234 Park Street') You must specify the fields to insert if you are only inserting some of the fields INSERT INTO should be INSERT to There is no table keyword It would work just fine

You must specify the fields to insert if you are only inserting some of the fields

Transitive Dependency

a functional dependency between the primary key and one or more attributes that are dependent on the primary key via another attribute

Composite Key

a primary key that consists of more than one attribute

Business Rule

a statement that defines or constrains some aspect of the business

Completeness Constraint

addresses the question of whether an instance of a supertype must also be a member of at least one subtype.

Disjointnes Constraint

addresses whether an instance of a supertype may simultaneously be a member of two (or more) subtypes

the following code would include: SELECT Customer_T.CustomerID,CustomerName,OrderID FROM Customer_T RIGHT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID; all rows of the order_T table regardless of matches with the customer_T table only rows that match both Customer_T and Order_T tables all rows of the customer_T Table regardless of matches with the order_T table only rows that dont match both customer_T and Order_T Tables

all rows of the order_T table regardless of matches with the customer_T table

Man-in-the-loop techniques

allow human guidance to tune parameters and select decision variables

Shared locks (S locks or read locks)

allow other transactions to read (but not update) a record or other resource

Versioning

allows all updates and assumes that simultaneous updates will be infrequent, Rejects an update when it senses conflict. Uses Rollback and Commit

Foreign Key

an attribute (possibly composite) in a relation that serves as the primary key of another relation

Primary Key

an attribute or a combination of attributes that uniquely identifies each row in a relation

Natural (inner) join

an equi-join in which one of the duplicate columns is eliminated in the result table

Deadlock

an impasse that results when two or more transactions have locked a common resource and each must wait for the other to unlock that resource

In order for a transaction to be consistent: it must run using the same amount of memory it must tell the truth it must run the same way all the time any database constraints that must be true before the transaction must also be true after the transaction

any database constraints that must be true before the transaction must also be true after the transaction

Thick Client

application and business logic processing occurs entirely on the client

______ is the process of assigning pieces of application code to clients or servers? Code distribution application partitioning program breakup modularizing programs

application partitioning

Relationship type

association between entity types

Subtype Discriminators

attribute of a supertype whose values determine the target subtype or subtypes

determinant

attribute on left side of functional dependency

Simple Attribute(Atomic)

attribute that cannot be broken down into smaller components that are meaningful for the organization.

Optional Attribute

attribute that may not have a value

MultiValued Attribute

attribute that may take on more than one value for a given entity instance. "{}"

Derived Attribute

attribute whose values can be calculated from related attribute values. "[]"

Data Modeling and Design Tools

automated tools used to design databases and application programs.

Composite attribute

can be broken into component parts

Data Warehouses

collect content from the various operational databases, including personal, work group, department, and ERP databases. (Structured)

entity type

collection of similar entity instances

Conceptual

combines the different external views into a single, coherent, and comprehensive definition of the enterprise's data.

Functional dependency

constraint between two attributes

Well-structured relation

contains little redundancy

Well-Structured Relation

contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies

Composite key

contains two (or more) attributes

Application Programs

create and maintain the database and provide information to users

Data modeling may be the most important part of the systems development process because: it is the easiest data characteristics are important in the design of program and other system components data are less stable than the processes the data in a system are generally less complex than processes and play a central role in development

data characteristics are important in the design of program and other system components

Repository

data descriptions are stored in a central location

Information

data that has been processed in such a way that the knowledge of the person who uses the data is increased

Enforcement of Standards

database administration function should be granted single-point authority and responsibility for establishing and enforcing data standards.

Weak entity

depends on the existence of another entity type

Entities

describes a person, a place, an object, an event, or a concept in the business environment for which information must be recorded and retained.

Physical

describes how data are to be represented and stored in secondary storage using a particular DBMS

the attribute on the left hand side of the arrow in a functional dependency is the: candiate key foreign key primary key determinant

determinant

Planned Data Redundancy

each primary fact is recorded in only one place in the database

Improved Data Accessibility and Responsiveness

end users without programming experience can often retrieve and display data

Database development begins with_____, which establishes the range and general contents of organizational databases. Crossfunctional analysis departmental data modeling enterprise data modeling database design

enterprise data modeling

A person, place, object, event, or concept about which the organization wishes to maintain data is called an: object relationship attribute entity

entity

Program-data dependence is caused by:

file descriptions being stored in each database application

Program-data dependence

file descriptions being stored in each database application.

Data Lakes

for storing large quantities of heterogeneous data for purposes that are often not predefined. (Not structured)

transitive dependency

functional dependency between the primary key and a non-key attribute via another non-key attribute

Supertype

generic entity type that has a relationship with one or more subtypes

Increased Productivity of Application Development

greatly reduces the cost and time for developing new business applications

Dimensional Table

hold descriptive data (context) about the subjects of the business

Composite Identifier

identifier that consists of a composite attribute.

User Interface

includes languages, menus, and other facilities by which users interact with various system components

anomaly

inconsistency or error

Database environment

integrated system of hardware, software, and people, designed to facilitate the storage, retrieval, and control of the information resource and to improve the productivity of the organization.

Internal

internal schema today really consists of two separate schemas: a logical schema and a physical schema.

Data Models

made up of entities, attributes, and relationships, and the most common data modeling representation is the entity-relationship model.

entity instance

may include person, place, object, concept, or event

1NF

multivalued attributes removed

Required Attribute

must be present for each entity instance

Relation

named two-dimensional table of data

The entity integrity rule states that: Referential integrity must be maintained across all entities A primary key must have only one attribute each entity must have a primary key no primary key attribute can be null

no primary key attribute can be null

Degree

number of participating entity types in relationship

Database

organized collection of logically related data

the subtype discriminator is a composite attribute when there is an? Partial specialization overlap rule Full specialization disjoint rule

overlap rule

2NF

partial functional dependencies removed

Data and Database Administrator

personnel responsible for maintaining the database

End Users

persons throughout the organization who add, delete, and modify data in the database and who request or receive information from it.

Exclusive locks (X locks or write locks)

prevent another transaction from reading (and therefore updating) a record until it is unlocked

Entity Integrity

primary key cannot be null

Generalization

process of defining a more general entity type from a set of more specialized entity types. (Bottom-Up Process)

Specialization

process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. (Top-Down Process)

Optimization

process of selecting values of decision variables that minimize or maximize some quantity of interest

Normalization

process of successively reducing relations with anomalies

Thin Client

processing occurs primarily on the server

Attribute Inheritance

property by which subtype entities inherit values of all attributes and instance of all relationships of the supertype

attribute

property of an entity type

all of the following are primary purposes of a database management system(DBMS) except? creating data updating data storing data providing an integrated development environment

providing an integrated development enviroment

Recursive foreign key

references the primary key in the same relation

unary relationship

relates instances of a single entity type

Associative Entity

relationship modeled as an entity type (Rounded corners)

ternary relationship

relationship of degree 3

Logical

representation of data for a type of data management technology

Operational System

runs in real time with current data

Entity Clustering

set of one or more entity types and associated relationships grouped into a single abstract entity type

Golden records

single source of truth, ALL up-to-date and relevant facts about a subject

Cardinality constraint

specifies maximum and minimum number of instances in a relationship

Overlap Rule

specifies that an entity instance can simultaneously be a member of two (or more) subtypes

Partial Specialization

specifies that an entity instance of the supertype is allowed not to belong to any subtype.

Total Specialization

specifies that each entity instance of the supertype must be a member of some subtype in the relationship. Represented by Double-Line

Disjoint

specifies that if an entity instance (of the supertype) is a member of one subtype, it cannot simultaneously be a member of any other subtype

Subtype

subgrouping of the entities in an entity type that is meaningful to the organization

Pivot Table

summarize data in a multi-dimensional format

Which of the following is a generic entity type that has a relationship with one or more subtypes? Class megatype supertype subgroup

supertype

Informational system

supports decision making with historical data

Analytics

systematic analysis and interpretation of data, typically using mathematical, statistical, and computational tools to improve our understanding of a real world domain

System Developers

systems analysts and programmers, who design new application programs.

Prescriptive analytics

techniques that create models indicating the best decision to make or course of action to take

Descriptive analytics

techniques that describe past performance and history

External

the view (or views) of managers and other employees who are the database users

3NF

transitive dependencies eliminated

A functional dependency between two or more nonkey attributes is called a: Patrial transitive dependency Partial nonkey dependency partial functional dependency transitive dependency

transitive dependency

Synonyms

two (or more) attributes may have different names but the same meaning

Identifier

uniquely identifies entity instances

DBMS

used to create, maintain, and provide controlled access to user databases.

Reduced Program Maintenance

you can change either the data or the application programs that use the data without necessitating a change in the other factor