BUS-S 307 - Midterm/Final Master study set
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 as well as the number of deliveries that he or she has made
In the figure shown below, which is the following rule? _____________________________________ | | | | o is married to | | - | Person | - | | | o | |---------------------------
A person can marry at most one person
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 during the most recent month, and includes customers who did not place an order during the month (their total will be zero)
What is this attribute: Part_Type(Manufactured?, Purchased?)
A subtype discriminator
The SQL command ______ adds one or more new columns to an existing table
ALTER TABLE
A(n) _______ is a set of application routines that programs use to direct the performance of procedures by the computer's operating system.
API
What does the following SQL command do? INSERT INTO Customer_T values(002, 'Mary Jones', '422 Kirkwood Ave', 'Bloomington', 'IN', '47405');
Adds a new record to the Customer_T table
The MERGE command:
Allows one to combine the INSERT and UPDATE operations
______ is the process of assigning pieces of application code to clients or servers.
Application partitioning
In a file processing environment, descriptions for data and the logic for accessing the data are built into:
Application programs
At Indiana University, the Active Directory Service is used to:
Assign users to groups to control access authorization
All of the following are well-accepted characteristics of transactions EXCEPT:
Automatic
An appropriate datatype for adding a sound clip would be:
BLOB
A preferred method of fixing an aborted transaction is:
Backing out the transaction
A join in which the joining condition is based on equality between values in the common columns is called a(n):
Both equi-join and natural join
A join operation:
Causes two tables with a common domain to be combined into a single table or view
What does the following SQL statement do? UPDATE Product_T SET Unit_Price = 85 WHERE Product_ID = 8
Changes the unit price of Product 8 to 85
The actions that must be taken to ensure data integrity is maintained during multiple simultaneous transactions are called ________ actions.
Concurrency control
A materialized view is/are:
Copies or replica of data based on queries
_____ use the result of the inner query to determine the processing of the outer query.
Correlated subqueries
In the SQL language, the ______ statement is used to make table definitions.
Create table
At IU, the role that is responsible for developing overall policies and governance for a particular type of data is:
Data Steward
________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated.
Data integrity
A graphical system used to capture the nature and relationships among data is called a(n):
Data model
A form of denormalization where the same data are purposely stored in multiple places in the database is called:
Data replication
The role responsible for administering database security is:
Database Adminstration
Which organizational function should set database standards?
Database administration
Which of the following is software used to create, maintain, and provide controlled access to a database?
Database management system (DBMS)
A computer that provides database storage and access in a client/server environment is called a(n):
Database server
The value a field will assume unless the user enters an explicit value for an instance of that field is called a:
Default value
Given the following entities, which of the choices below would be the most complicated? Automobile: VIN, EngineSize, NumberOfDoors, NumberOfPassengers, FuelType, Transmission SUV: VIN, EngineSize, NumberOfPassengers, NoWheelDrive, FuelType, Transmission Truck: VIN, EngineSize, NoWheelDrive, FuelType, Transmission, Payload
Define one vehicle entity type to hold all entities
The number of entity types that participate in a relationship is called the:
Degree
What does the following SQL statement do? DELETE FROM Customer_T WHERE state = 'IN';
Deletes all records from Customer_T where the state is equal to IN
The most important challenge of customizing a purchased data model is:
Determining the business rule that will be established through the data model
In the following diagram, which of the answers below is true? Patient--||-----------has recorded--------------|<Patient history
Each patient has one or more patient histories
The coding or scrambling of data so that humans cannot read them is called:
Encryption
A(n) ________ prevents another transaction from reading and therefore updating a record until it is unlocked.
Exclusive lock
A fat client does most of its processing on the server.
False
A member of a subtype does NOT necessarily have to be a member of the supertype. (T/F)
False
A procedure is a set of commands that are automatically executed when a data modification occurs
False
A shared lock prevents another transaction from reading a record.
False
A subtype is a generic entity that has a relationship with one or more entities at a lower level (T/F)
False
Databases were developed as the first application of computers to data processing (T/F)
False
Horizontal partitioning means distributing the columns of a table into several separate physical tables.
False
Indexes generally slow down access speed in most RDMS. (T/F)
False
Organizations that utilize the file processing approach spend only 20 percent of development time on maintenance (T/F)
False
Redundancy increases the risk of inconsistent data. (T/F)
False
The Number datatype would be the best choice for a telephone number.
False
The columns of a relation may NOT be interchanged and must be stored in a fixed sequence (T/F)
False
The following two SQL statements will produce different results. SELECT last_name, first_name FROM customer WHERE state = 'IN' OR state = 'IL' OR state = 'OH' OR state = 'KY' OR state = 'MI'; SELECT last_name, first_name FROM customer WHERE state IN ('IN', 'IL', 'OH', 'KY', 'MI');
False
Unlike columns, the rows of a relation may not be interchanged and must be stored in one sequence.
False
In the figure below, each employee may have more than one manager. (T/F) EMPLOYEE >o-- - | o | is managed by | | ---- manages ---
False`
Distributing the rows of data into separate files is called:
Horizontal partitioning
Which of the following is NOT a cost and/or risk of the database approach? specialized personnel cost of conversion improved responsiveness organizational conflict
Improved responsiveness
One major advantage of the outer join is that:
Information is not lost
A data-interchange format that is easy for humans to read and machines to parse and process is called:
JavaScript Object Notation (JSON)
A database is an organized collection of ________ related data.
Logically
Business policies and rules govern all of the following EXCEPT:
Managing employees
A relationship where the minimum and maximum cardinality are both one is a(n) _______ relationship.
Mandatory one
_________ is/are any of several classes of software that allow an application to interoperate with other software without requiring the user to understand all software involved.
Middleware
A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n):
Natural join
Which of the following are properties of relations? -Each attribute has the same name - No two rows in a relation are identical - All columns are numeric - There are multivalued attributes in a relation
No two rows in a relation are identical
A requirement to begin designing physical files and databases is:
Normalizing relations
The last part of a SQL query to be read is the ______ statement.
ORDER BY
In a supertype/subtype hierarchy, each subtype has:
Only one supertype
To get all the customers from Indiana sorted together, which of the following would be used?
Order by
The need for consensus on data definitions is an example of which type of risk in the database environment? specialized personnel needs organizational conflict conversion costs legacy systems
Organizational conflict
All of the following are key components of a web application EXCEPT:
Processing-logic server
Indexes are created in most RDBMs to:
Provide rapid random and sequential access to base-table data
Sarbanes-Oxley Act was enacted to ensure the integrity of:
Public companies' financial statements
An iterative methodology that rapidly repeats the analysis, design, and implementation phases of the SDLC is called:
RAD
When a foreign key has a relationship with the primary key in a relation, it is called a:
Recursive foreign key
Which of the following is NOT an advantage of database systems? redundant data program-data independence better data quality reduced program maintenance
Redundant data
A rule that states that each foreign key value must match a primary key value in the other relation is called the:
Referential integrity constraint
_____ are established between entities in a well-structured database so that the desired information can be retrieved.
Relationships
Which of the following is true of packaged data models? - Relationships are connected to the highest-level entity type in an order that makes sense - all weak entities are considered strong - all subtype/supertype relationships follow the total specialization and disjoint rules - no entities on the many sides of a relationship can be weak
Relationships are connected to the highest-level entity type in an order that makes sense
A centralized knowledge base of all data definitions, data relationships, screen and report formats, and other system components is called a(n):
Repository
At IU, what is the data classification of immunization records?
Restricted
Which of the following with produce the minimum of all standard prices? -SELECT Standard_Price FROM min(Product_V); -SELECT Standard_Price FROM Product_V WHERE Standard_Price = min; -SELECT MIN(Standard_Price) FROM Product_V; -SELECT MIN(Standard_Price) FROM Product_V WHERE Standard_Price = min(Standard_Price)
SELECT MIN(Standard_Price) FROM Product_V
Which of the following is NOT a reason to create an instance of a relational schema with sample data?
Sample data can reverse database implementation errors.
A common encryption method to secure data traveling between a client and a server is called:
Secure Sockets Layer (SSL)
What does the following SQL statement do? SELECT * FROM Customer WHERE Cust_Type = "Best"
Selects all the fields from the customer table for each row with a customer labeled "Best"
An operation to join a table to itself is called a(n):
Self-join
The traditional methodology used to develop, maintain, and replace information systems is called the:
Systems Development Life Cycle
In the following diagram, what type of relationship is depicted? Part V | | Vendor >-----------------------------------------------<Warehouse supplies \ \ Shipping_Mode Unit_Cost
Ternary
What result set will the following query return? SELECT Item_No, description FROM item WHERE weight > 10 and weight < 20;
The Item_No and description for all items weighing more than 10 but less than 20
What result set will the following query return? SELECT Item_No FROM Order_T WHERE quantity < 10;
The Item_No of all orders that had less than 10 items
A major difference between data warehouses and transactional systems as compared to big data is:
The data is usually unstructured in big data systems
Which of the following is NOT an objective that drove the development and evolution of database technology? - The need to provide greater independence between programs and data - The desire to manage increasing complex data types and structures - The desire to require programmers to write all file handling functionality - The need to provide ever more powerful platforms for decision support applications
The desire to require programmers to write all file handling functionality
When the circle in a specialization is d then:
The entity must be one of the subtypes, and cannot be both at the same time.
What results will be produced by the following SQL query? SELECT SUM(Standard_Price) as Total_Price FROM Product_V WHERE Product_Type = 'WOOD';
The total price of all products that are type wood
Subtypes should be used when: A) there are attributes that apply to some but not all instances of an entity type. B) supertypes relate to objects outside the business. C) the instances of a subtype do not participate in a relationship that is unique to that subtype. D) a recursive relationship is needed.
There are attributes that apply to some but not all instances of an entity type.
A PC configured to handle user interfaces with limited local storage is called a(n):
Thin client
A method for handling missing data is to:
Track missing data with special reports
A sequence of steps that constitute a well-defined business activity is called a:
Transaction
Database access frequencies are estimated from:
Transaction volumes
A named set of SQL statements that are executed when a data modification occurs are called:
Triggers
A DBMS must provide journalizing facilities to provide an audit trail of transactions and database changes.
True
A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. (T/F)
True
A completeness constraint may specify that each entity of the supertype must be a member of some subtype in the relationship (T/F)
True
A database table is defined using the data definition language (DDL) (T/F)
True
A join in which the joining condition is based on equality between values in the common column is called an equi-join:
True
A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated.
True
A range control limits the set of permissible values that a field may assumes.
True
A relational database establishes the relationships between entities by means of a common field
True
A view can be built to present only the data to which a user requires access.
True
A view is a way to create a structure that acts like a table.
True
A well-structured relation contains minimal redundancy and allows users to manipulate the relation without errors or inconsistencies (T/F)
True
All transactions must have the ACID characteristics.
True
All values that appear in a column of a relation must be taken from the same domain. (T/F)
True
An SQL query that implements an outer join will return rows that do not have matching values in common columns
True
An associative entity has composite foreign keys related to two separate relations (T/F)
True
Application partitioning gives developers the opportunity to write application code that can later be placed on either a client workstation of a server, depending upon which location will give the best performance.
True
Authorization rules are controls incorporated in the data management system that restrict access to data and also restrict the actions that people may take when they access the data.
True
Client/server describes a networked computing model that distributes processes between computers that request services and computers that provide services.
True
Enforcement of business rules can be automated through the use of software tools that can interpret the rules and enforce them.
True
Expressions are mathematical manipulations of data in a table that may be included as part of the SELECT statement. (T/F)
True
Figuring out what attributes you want in your query before you write the query will help with query writing.
True
In a sequential file, the records are stored in sequence according to a primary key value.
True
In a supertype/subtype hierarchy, attributes are assigned at the highest logical level that is possible in the hierarchy (T/F)
True
In order to find out what customers have not placed an order for a particular item, one might use the NOT qualifier along with the IN qualifier.
True
In the subtype hierarchy, the bottom tiers inherit all the attributes from the above entities (T/F)
True
Information is processed data (T/F)
True
Joining tables or using a subquery may produce the same result.
True
Loss of confidentiality is a threat that involves outside parties using information to embarrass a company.
True
ODBC is an application programming interface that provides a common language for application programs to access and process an SQL database independent of the particular RDBMS that is accessed.
True
One of the major challenges in data modeling is to recognize and clearly represent entities that are almost the same (T/F)
True
One of the original purposes of the SQL standard was to provide a vehicle for portability of database definition and application modules between conforming DBMSs. (T/F)
True
Packaged data models are meant to be customized.
True
Requirements for response time, data security, backup, and recovery are all requirements for physical design.
True
Restricting access to the Web server is one method of Web security.
True
Sample data are useful for developing prototype applications and for testing queries. (T/F)
True
Security is one advantage of partitioning.
True
The FROM clause is the first statement processed in a SQL command (T/F)
True
The LAMP stack includes Apache, Linux, and MySQL.
True
The first line of defense for authentication is the use of passwords, which is a one-factor authentication scheme.
True
The following query totals sales for each salesperson. SELECT salesperson_id, sum(sales) FROM salesperson GROUP BY salesperson_id;
True
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. (T/F)
True
The primary key of the one side migrates to the many side when transforming a one-to-many relationship (T/F)
True
The rows of a relation can be interchanged without changing the meaning or use of the relation (T/F)
True
When creating tables, it's important to decide which columns will allow null values before the table is created (T/F)
True
When the specialization circle has d, the entity can have either one of the subtypes, or none (T/F)
True
The _______ DBA view shows information about all tables of the database in Oracle Live SQL
USER_TABLES
All of the following are characteristics of cloud technology EXCEPT:
Unlimited bandwidth
All of the following are guidelines for better query design EXCEPT: - understand how indexes are used in query processing - retrieve only the data that you need - use a lot of self-joins - write simple queries
Use a lot of self-joins
All of the following are objectives when selecting a data type EXCEPT:
Use a lot of storage space
Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by:
Using the CASE key word in a statement
An optimistic approach to concurrency control is called:
Versioning
In a SQL statement, which of the following parts states the conditions for row selection?
WHERE
Given a table named supplier_t with 5 fields: supplier_id, address, city, state, zipcode, why would the following insert command not work? INSERT INTO supplier_t VALUES ('1608 Lincoln Street')
You must specify the fields to insert if you are only inserting some of the fields
For the relationship represented in the figure below, which of the following is true? Employee >o--------------works in----------------------|< department
a department can have more than one employee
The following figure shows an example of: EMPLOYEE--------------carries----------------< Dependent
a strong entity and its associated weak entity
The SDLC phase in which every data attribute is defined, every category of data is listed, and every business relationship between data entities is defined is called ______ phase.
analysis
The property by which subtype entities possess the values of all attributes of a supertype is called:
attribute inheritance
A person's name, birthday, and social security number are all examples of:
attributes
A _______ defines or constrains some aspect of the business
business rule
A _____ specifies the number of instances of one entity that can be associated with each instance of another entity.
cardinality constraint
A(n) ________ constraint is a type of constraint that addresses whether an instance of a subtype must also be an instance of at least one subtype.
completeness
An attribute that can be broken down into smaller parts is called a(n) ________ attribute
composite
An attribute that uniquely identifies an entity and consists of a composite attribute is called a(n):
composite identifier
When a relation table has two primary keys, it is called:
composite key
A rule that CANNOT be violated by database users is called a:
constraint
When a regular entity type contains a multivalued attribute, one must:
create two new relations, one containing the multivalued attribute
Data modeling may be the most important part of the systems development process because:
data characteristics are important in the design of programs and other systems components
The total quiz points for a student for an entire semester is a(n) ________ attribute
derived
Database management involves all of the following EXCEPT: collecting data organizing data design web pages managing data
design web pages
A primary key whose value is unique across all relations is called a(n):
enterprise key
Customers, cars, and parts are examples of:
entities
The logical representation of an organization's data is called a(n):
entity-relationship model
Using a packaged data model, projects take less time and cost because:
essential components and structures are already defined
An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:
foreign key
The first in a series of steps to follow when creating a table is to:
identify each attribute and its characteristics
The SDLC phase in which database processing programs are created is the _________ phase.
implementation
Which of the following is NOT a characteristic of a good business rule? inconsistent expressible atomic declarative
inconsistent
A domain definition consists of all of the following components EXCEPT: data type integrity constraints domain name size
integrity constraints
Metadata typically describes all of the following EXCEPT: data definitions length allowable values location on disk
location on disk
A student can attend five classes, each with a different professor. Each professor has 30 students. The relationship of students to professors is a _______ relationship.
many-to-many
All of the following are advantages of packaged data models EXCEPT: - more one-to-one relationships give the data model more flexibility - projects take less time and cost less - packaged data models can be built using proven components evolved from cumulative experiences - the data model is easier to evolve
more one-to-one relationships give the data model more flexibility
Which of the following does Indiana University use for two-factor authentication?
passphrase + Duo
An attribute (or attributes) that uniquely identifies each row in a relation is called a:
primary key
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 environment
Requiring a CustomerID to exist in a Primary table before it can exist in a foreign table is an example of:
referential integrity
A two-dimensional table of data is sometimes called a:
relation
An attribute that must be present for every entity (or relationship) instance is a(n):
required attribute
The ________ is the structure that contains descriptions of objects such as tables and views created by users.
schema
In the figure below, which attribute is multivalued? EMPLOYEE Employee_ID (PK) Employee_Name Address Date_Employed (skill) [Years_Employed]
skill
The process of defining one or more subtypes of a supertype and forming relationships is called:
specialization
Which of the following is an entity that exists independently of other entity types? variant weak strong codependent
strong
In a relational database model, data is represented in the form of:
tables
When the specialization has an o it is:
total specialization
In an E-R diagram, there are ________ business rule(s) for every relationship.
two
A relationship between the instances of a single entity type is called a __________ relationship.
unary
A generic template data model that can be reused as a starting point for a data modeling project is called a(n):
universal data model
Languages, menus, and other facilities by which users interact with the database are collectively called a(n):
user interface
A(n) ________ is often developed by identifying a form or report that a user needs on a regular basis.
user view
All of the following are steps to using a packaged data model EXCEPT: - utilize all business rules that come with the packaged data model - rename the identified data elements - identify the parts of the data model that apply to your data modeling situation - map the data to be used in packages with existing data in the current databases
utilize all business rules that come with the packaged data model
A data warehouse derives its data from:
various operational data sources
A relation that contains minimal redundancy and allows easy use is considered to be:
well-structured
A good data definition will describe all of the characteristics of a data object EXCEPT: sublteties who can delete the data who determines the value of the data examples
who can delete the data
