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