ISYS 4283 Exam 1

Data Warehouse

- Many sources - Transactional systems ~ 10 databases running in a system but you want to query all at once but you can't because they are in different systems - warehouse takes all databases and combines it so we can query it

entities

-an object of interest to the end user. -refers to the entity set and not to single entity occurrence. -an entity in the ERM corresponds to a table - not row - in the relational environment

surrogate key

A PK created by the DB designer to simplify the identification of entity instances -not intelligent -no meaning -created and incremented by DBMS -usually not shown and should not be searchable -used for just making the relationship

Business Rules

Brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization - database must reflect business rules - used to define entities, attributes, relationships, and constraints

Data vs. Information

Data is the foundation of information, which is the bedrock of knowledge, which leads to making better decisions

Keys to making good decisions

Database is set up correctly - referential integrity, low redundancy, entity integrity, constraints BUSINESS RULES give us this.

In an entity relational model (ERD), what is each row in a table known as? a. An entity set b. An entity relation c. An entity occurrence d. An entity tuple

Not b or d

Foreign key

PK of one table that has been placed in another table -CAN be NULL -creates relationship

Data Redundancy

Presence of duplicate data in multiple files. To reduce - break it into an entity, referential integrity

Data Integrity

To ensure integrity - use constraints. At an entity level - use PK to ensure integrity.

Unstructured Data vs. Structured Data

Unstructured: original (raw) state. Structured: result of formatting unstructured data to facilitate storage and easier use.

Which of the following statements best defines a record? a. A record is a set of logically connected fields b. A record is a collection of data in files. c. A record is sets of data in related files. d. A record is a collection of fields.

a. A record is a set of logically connected fields

Which of the answers below is an example of a single-user database? a. Desktop database b. Office database c. Workgroup database d. Enterprise database

a. Desktop database

Which of the following lists problems with file system data processing? a. Development complexity, administration complexity, and lack of security b. Development complexity, administration complexity, and answers complexity c. Administration simplicity, security complexity, and answers simplicity Administration simplicity, answers simplicity, and lack of security

a. Development complexity, administration complexity, and lack of security

What are the components of database systems? a. Hardware, software, people, procedures, data b. Software, personnel, procedures, data c. Hardware, system, people, programs, data d. System, personnel, programs, data

a. Hardware, software, people, procedures, data

Which of the following is an entity cluster? a. Location b. Professor c. School d. Department

a. Location

What is the best data type for a primary key? a. Numeric b. Text c. Date d. Time

a. Numeric

Which of the following is not a valid statement? a. Raw data is the collection of processed knowledge. b. A database is a shared, integrated computer structure that stores a collection of user data and metadata. c. Database design refers to the activities that focus on the design of the database structure that will be used to store and manage end-user data.

a. Raw data is the collection of processed knowledge.

While a relationship degree indicates the number of entities associated with a relationship, which relationship type is within a single entity type? a. Recursive relationship b. Binary relationship c. Higher-order relationship d. Ternary relationship

a. Recursive relationship

Which statement from the following describes specialization and generalization? a. Specialization is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. Generalization is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes. b. Specialization is the top-down process of identifying lower-level, more specific entity supertypes from a higher-level entity subtype. Generalization is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes. c. Specialization is the top-down process of identifying lower-level, more specific entity supertypes from a higher-level entity subtype. Generalization is the bottom-up process of identifying a higher-level, more generic entity subtype from lower-level entity supertypes. d. Specialization is the top-down process of identifying lower-level, more specific entity supertypes from a higher-level entity subtype. Generalization is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes.

a. Specialization is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. Generalization is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes

A _____ is a primary key created by a database designer to simplify the identification of entity instances. a. Surrogate key b. Compound key c. Composite key d. Natural key

a. Surrogate key

When looking into the extended entity relationship model (EERM), which of the following statements is not valid about entity supertypes and subtypes? a. The entity subtype contains common characteristics. b. Entity supertype and subtype are based on a hierarchical structure. c. The entity supertype contains common characteristics with subtype. d. An entity supertype is a generic entity type related to one or more entity subtypes.

a. The entity subtype contains common characteristics.

Connectivities and cardinalities are established by concise statements known as business rules. a. True b. False

a. True

Data redundancy exists when data is duplicated and stored unnecessarily at different places. a. True b. False

a. True

Which of the following is not a function of a DBMS? a. User management b. Security management c. Data presentation management d. Data storage management

a. User management

Which of the following is not a valid component of object-oriented data models? a. Volume b. Class c. Object d. Attributes

a. Volume

Which of the following models operate at the lowest level of abstraction and is both hardware and software dependent? a. physical b. conceptual c. internal d. external

a. physical

If one shop manager can only be in charge of a single shop, what kind of relationship do the entities ship manager and shop have? a. M:1 b. 1:1 c. M:N d. 1:M

b. 1:1

Which of the following entity relationship (ER) connectivity statements is invalid? a. PAINTER paints many PAINTINGs b. A CUSTOMER gender is MALE or FEMALE c. An EMPLOYEE learns many SKILLs d. An EMPLOYEE manages a STORE

b. A CUSTOMER gender is MALE or FEMALE

Which of the following statements describe a fan trap in ERD design? a. A fan trap occurs when there is one entity in two 1:1 relationships to other entities. b. A fan trap occurs when there is one entity in two 1:M relationships to other entities. c. A fan trap occurs when there are more than two entities in 1:M relationships. d. A fan trap occurs when there is one entity in two 1:1 relationships.

b. A fan trap occurs when there is one entity in two 1:M relationships to other entities.

A database management system (DBMS) can be likened to__? a. A computer file b. A very well-organized electronic filing cabinet c. A kitchen pantry d. A book cabinet

b. A very well-organized electronic filing cabinet

In an entity relationship model (ERM), attributes are characteristics of entities. Which of the following statements best describe an optional attribute? a. An optional attribute is an attribute that requires a value and can be an optional key. b. An optional attribute is an attribute that does not require a value and can be left empty. c. An optional attribute is an attribute that does not require a value and can be an optional key. An optional attribute is an attribute that requires a value and can be left empty

b. An optional attribute is an attribute that does not require a value and can be left empty.

Which of the following is not an advantage of a DBMS? a. Data consistency b. Complexity c. Data retrieval d. Data security

b. Complexity

_____ are important because they help to ensure data integrity. a. Attributes b. Constraints c. Relationships d. Entities

b. Constraints

What type of entity can exist in the database only when it is associated with another related entity? a. Regular entity b. Existence-dependent c. Strong entity d. Existence-independent

b. Existence-dependent

A subtype contains attributes that are common to all of its supertypes. a. True b. False

b. False

A table is a three dimensional structure composed of depth, width, and height. a. True b. False

b. False

The primary key is possibly the most essential characteristic of an entity; which of the following is not a desirable characteristic of a primary key? a. Security-compliant b. Intelligent c. No change over time d. Unique value

b. Intelligent

A video game store has a table with its users. What type of data type is best for the attribute of being an adult? a. [0,1] b. Logical c. Date d. Numeric

b. Logical

Which of the following is not a valid statement? a. The data warehouse is a specialized database that stores data in a format optimized for decision support. b. Production databases focus primarily on storing historical data and business metrics used exclusively for tactical or strategic decision making. c. Online analytical processing is a set of tools that work together to provide an advanced data analysis environment for retrieving, processing, and modeling data from the data warehouse. d. A database that is designed primarily to support a company's day-to-day operations is classified as an operational database.

b. Production databases focus primarily on storing historical data and business metrics used exclusively for tactical or strategic decision making.

Which of the following technologies is not used in Big Data? a. Hadoop b. SQL c. NoSQL d. MapReduce

b. SQL

What type of attribute allows for no more than one value? a. Simple attribute b. Single-valued attribute c. Composite attribute d. Multivalued attribute

b. Single-valued attribute

Identify the statement that best defines time-variant data. a. Time-variant data refers to data that does not change over time. b. Time-variant data refers to data that changes over time; its history of data changes must be maintained. c. Time-variant data refers to data that changes over time, with no history of data change tracking. d. Time-variant data refers to data that was created redundantly.

b. Time-variant data refers to data that changes over time; its history of data changes must be maintained.

In a COURSE and CLASS relationship if the CLASS object is given a cardinality of (0,N) then which of the following would be true? a. a CLASS is mandatory b. a COURSE is optional c. a CLASS is optional d. a COURSE is mandatory

b. a COURSE is optional

Which of the following statements is true? a. A DBMS is a collection of programs that manage metadata data. b. A DBMS is knowledge database management software. c. A DBMS is a set of processes that manage and control access to data stored in the database. d. A DBMS allows the user access to the database's internal complexity.

c. A DBMS is a set of processes that manage and control access to data stored in the database.

What primary key is created by the database designer to simplify the identification of entity instances? a. The secondary key b. A composite key c. A surrogate key d. The candidate key

c. A surrogate key

Which of the following statements is not valid when describing entity clustering? a. An entity cluster is formed by combining multiple interrelated entities into a single, abstract entity object. b. An entity cluster is a "virtual" entity type used to represent multiple entities and relationships in the ERD. c. An entity cluster is a permanent entity used to represent multiple entities and relationships, intended to simplify the ERD, and thus enhance its readability. d. An entity cluster is considered "virtual" or "abstract" because it is not an actual entity in the final ERD.

c. An entity cluster is a permanent entity used to represent multiple entities and relationships, intended to simplify the ERD, and thus enhance its readability.

Which of the following is a valid business rule? a. An invoice may have many items b. A customer must reside in the USA c. An invoice must be paid in a reasonable amount of time d. An invoice can only be paid to one customer

c. An invoice must be paid in a reasonable amount of time

A CEO of a company wants to find a new and improved way to manage and extract business insights from large amounts of web- and sensor-generated data. Which of the following could they use to achieve this goal? a. Hadoop b. The 3Vs c. Big data d. The internet of Things (IoT)

c. Big data

What element expresses the specific number of entity occurrences associated with an occurrence of a related entity? a. Derived attributes b. Existence dependence c. Cardinality d. Connectivity

c. Cardinality

Metadata is ___? a. Refined data b. Filtered data c. Data about data d. Raw data

c. Data about data

What is the result of redundant data in the database? a. Increased data security that leads to data access b. Lack of data sharing among database users c. Data anomalies that lead to data inconsistencies d. Enforced data integrity that lead to data anomalies

c. Data anomalies that lead to data inconsistencies

In an extended entity relationship, which of the following statements is not valid about inheritance? a. Entity subtypes inherit all relationships in which the supertype entity participates. b. Entity subtypes inherit their primary key attribute from their supertype. c. Inheriting the relationships of their supertypes means subtypes cannot have relationships of their own. d. Inheritance enables an entity subtype to inherit the attributes and relationships of the supertype.

c. Inheriting the relationships of their supertypes means subtypes cannot have relationships of their own.

A _____ key is a real-world, generally accepted identifier used to uniquely identify real-world objects. a. Surrogate b. Primary c. Natural d. foreign

c. Natural

A relationship degree indicates the number of entities or participants associated with a relationship. Which is not a valid relationship that's specifically named? a. Unary relationship b. Ternary relationship c. Quarterly relationship d. Binary relationship

c. Quarterly relationship

Which of the following is a disadvantage of various database models? a. Data sharing b. Visual modeling c. System complexity d. Database security

c. System complexity

In the extended entity-relationship model (EERM), which of the following statements best describes the completeness constraint? a. Partial completeness constraint means that every supertype occurrence is a member of a subtype. b. Total completeness constraint means that every subtype occurrence must be a member of at least one supertype. c. The completeness constraint specifies whether each entity supertype occurrence must also be a member of at least one subtype. d. The completeness constraint specifies whether each entity subtype occurrence must also be a member of at least one supertype.

c. The completeness constraint specifies whether each entity supertype occurrence must also be a member of at least one subtype.

A derived attribute is an attribute whose value is calculated (derived) from other attributes. What is not an advantage of storing derived attributes in the database? a. Can keep track of historical data b. Saves data access time c. Uses CPU processing cycles d. Data value is readily available

c. Uses CPU processing cycles

A database designer set the CRS_CODE attribute as the primary key to the CLASS table. If no other changes are made, how many composite identifiers does the table have? a. Three b. One c. Zero d. Two

c. Zero

A noun in a business rule translates to a(n) _____ in the data model. a. constraint b. attribute c. entity d. relationship

c. entity

Entity integrity

condition in which each row in the table has its own known, unique value enforced with the PK ex: can't allow the same student ID twice

referential integrity

condition where a dependent table's FK entry must have either a NULL entry or a matching entry in the PK of the related table enforced with FK - FK enables the relationship. it is the common field

subtype

contains unique characteristics of each entity subtype has UNIQUE attributes

In the relationship where "PROFESSOR teaches CLASS," and a class can have only one professor teaching, how is the PROFESSOR cardinality notated if PROFESSOR is mandatory? a. (0,1) b. (0,N) c. (1,N) d. (1,1)

d. (1,1)

When working with entity relationship models (ERM), which of the following is not a valid statement regarding an entity? a. The ERM refers to a table row as an entity instance or entity occurrence. b. An entity refers to the entity set and not to a single entity occurrence. c. In the Chen, Crow's Foot, and UML notations, an entity is represented by a rectangle that contains the entity name. d. An entity is an object of interest to the end designer.

d. An entity is an object of interest to the end designer.

Which of the following data modeling component, helping ensure data integrity? a. Relationship b. Entity c. Attribute d. Constraint

d. Constraint

Which of the following is not a disadvantage of DBMS? a. Vendor dependency b. Increased costs c. Management complexity d. Data integration

d. Data integration

Database designers must often compromise design due to conflicting goals. What are examples of contradictory goals in database design? a. Chen notation, Crow's Foot notation, and UML notations b. Attributes, domains, and entities c. Primary key, composite key, and foreign key d. Design standards, processing speed, and information requirements

d. Design standards, processing speed, and information requirements

Why is identifying and documenting business rules essential to database design? a. It allows the designer to manage business processes. b. It allows the user to understand relationship participation rules and constraints. c. It can be a communication tool between users and managers. d. It helps to standardize the company's view of data.

d. It helps to standardize the company's view of data.

Which of the following two-dimensional structures is known as a relation? a. File b. Entity c. Tuple d. Table

d. Table

In the context of relational databases, a column in a relational table is associated with the _____(s) of a real-world person, place, thing, or event. a. constraints b. relationships c. entity d. attributes

d. attributes

If a company generates reports by city or state from its CUSTOMER table, a database designer should be cautious of attributes like ADDRESS because it does not separate the different details of a full address such as a city or state. What type of attributes would a database designer be cautious of and work on decomposing further for better querying? a. multivalued attributes b. simple attributes c. single-valued attribute d. composite attributes

d. composite attributes

If a design has logical independence, it means the _____ can be changed without affecting the conceptual model. a. operating system b. logical design c. external schema d. internal model

d. internal model

Character data, also known as _____ data, can contain any character or symbol not intended for mathematical manipulation. a. Content b. Unsigned integer c. Signed integer d. string

d. string

Metadata

data about data

relationship

entities and their common attributes and where those intersect, bidirectional 1:1 - rare bc it would just be an attribute to the entity 1:M - most common M:M - conceptual idea, to implement you have to use a bridge entity

weak entity

entity is existence-dependent and has a PK that is partially or totally derived from parent entity in the relationship

attribute

field, column characteristic of an entity

composite attribute

fields that can be broken down ex: address field - can go into state, city, zipcode, street, etc.

supertype

generic entity type related to one or more entity subtypes has the COMMON attributes

Information is produced by processing _____. a. Scripts b. Information c. Data d. Context

i think c

Desktop Database

is single-user database on a personal computer

External model

is the end user's view of the data environment. - based on the BUSINESS RULES

data dictionary

list of all entities, attributes of entities, type of data, range, etc. stores definitions, such as data types for fields, default values, and validation rules for data in each field

Which of the following statements best describes the essential characteristic of an entity's primary key? a. The main function of the primary key is to guarantee entity integrity and to "describe" the entity. b. A primary key is the attribute or combination of attributes uniquely identifying an entity's instance in an entity set. c. It has no intrinsic meaning; values for it can be generated by the DBMS to ensure that unique values are always provided. d. The main function of the primary key is to guarantee entity integrity, not to "describe" the entity.

not a or b

The CUSTOMER entity includes the attributes CUST_LNAME, CUST_FNAME, CUST_PHONE, and CUST_PHONE_TYPE. The CUST_PHONE_TYPE attribute stores the string "HOME, WORK, MOBILE, OTHER". Which term best describes CUST_PHONE_TYPE? a. Simple attribute b. Multivalued attribute c. Composite attribute d. Single-valued attribute

not a or c

cardinality

optional vs mandatory (1,1) HAS to be one and ONLY one (0,N) optional (1,4) mandatory and can be between 1 and 4

natural key

real-world identifier used to uniquely ID real-world objects -SSN, student ID#, DL#

Conceptual Model

represents a global view of the entire database by the entire organization - is the basis for the ID and high-level description of the main data objects - not dependent on hardware/software -logical design=creating a conceptual data model

Constraints

restrictions placed on the data - help ensure data integrity expressed in rules ex: GPA 0 to 4.0

domain

set of allowable values for an attribute

Workgroup database

supports a small number of users or a specific department

enterprise database

supports many users across many departments

Multiuser Database

supports multiple users at the same time

Single-User Database

supports one user at a time

entity

table a person, place, thing, concept, or event about which data will be collected and stored

Primary Key

the attribute or combination of attributes that uniquely identifies a specific row in a table -best to be numerical -most important thing in entity -CANT be NULL

strong entity

when PK of the related entity contains a PK component of the parent entity

Composite Relationships (BRIDGE)

when you have a M:M, put a bridge between them. ex: "MovieGenre" bridge between "Movie" and "Genre"