Database Systems FINAL
A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key is said to be in _____.
1NF
From a structural point of view, 2NF is better than _____.
1NF
Some very specialized applications may require normalization beyond the _____.
4NF
The order of the rows and columns is important to the DBMS. (T or F)
False
Evaluation, maintenance, and enhancement are done during the __ phase of the Systems Development Life Cycle (SDLC).
Maintenance
You are creating a transaction management database and the currency control called __________ ensures that database operations do not conflict.
Optimistic Approach
A database language enables the user to perform complex queries designed to transform the raw data into useful information. (T or F)
True. correct using database languages a user can extract the precise information required for a given task
The term database system refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment.
True: Correct. Database system refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment.
Thorough database planning and design is important to ensure that data is properly used to give the organization the most benefit. (T or F)
True: Correct. Thorough database planning and design is important for a DBMS to perform well and to give the organization the most benefit.
Data redundancy exists when data is duplicated and stored unnecessarily at different places.
True: Data redundancy exists when data is duplicated and stored unnecessarily at different places.
While designing a query you determine that the data needs to be preprocessed. Which of the following is most appropriate?
Use a subquery to preprocess.
Which of the following is NOT required in terms of syntax when a user wants to copy data from one table to another?
Use the * command to select all values.
Which of the following is true of NoSQL databases? a. They do not support distributed database architectures. b. They are not based on the relational model. c. They are geared toward transaction consistency rather than performance. d. They do not support very large amounts of sparse data.
b. They are not based on the relational model. Correct. NoSQL databases are not based on the relational model, using a schema-less key-value model instead.
When examining an associative entity with a primary key and two foreign keys, what can a database designer review as well to better understand the overall design? a. two attributes b. two parent entities c. primary key d. foreign keys
b. two parent entities Correct. Two parent entities because each of the two foreign keys are derived from its associated parent entity.
Unnormalized tables yield no simple strategies for creating virtual tables known as _____.
views
A noun in a business rule translates to a(n) _____ in the data model. a. entity b. attribute c. relationship d. constraint
A. entity Correct. An entity is a basic building block of a data model that represents a real-world person, place, or thing (noun) about which data will be collected and stored in the database.
All changes in the table structure start by entering the ____ command first, followed by a keyword that produces the specific change.
ALTER 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. Entity B. Atrributes C. Relationships D. Constraints
B. Attributes Correct. Attributes are the characteristics of an entity, usually represented as a value. In the context of relational databases, the columns in a relational table correspond to attributes of an entity.
The special operator used to check whether a subquery returns any rows is _____. A. BETWEEN B. EXISTS C. LIKE D. IN
B. EXISTS Correct. The operator EXISTS returns True when the subquery is not empty.
How would you define data that is verifiable? a. The data is stored in different places within the database. b. The data cannot be changed or manipulated. c. The data always yields consistent results. d. The data is obtained from trusted sources.
C Correct. Data is said to be verifiable if the data always yields consistent results
One of the advantages of database systems is that ___ a. Database systems maximizes data inconsistency b. Database systems decreases end user productivity c. Database systems enable the data in the database to be shared among multiple applications or users. d. Database systems do not integrate many different users' data into a single all-encompassing data repository.
C. Correct. One of the advantages of database systems is that Database systems enable the data in the database to be shared among multiple applications or users.
What command should be used to save permanently all changes made to a database. a. SAVE b. SAVE ALL c. COMMIT d. WRITE ALL
C. COMMIT Correct. After executing the COMMIT command all the changes are permanently saved. There are no commands such as SAVE or WRITE ALL.
Business rules that are properly written will define entities, attributes, relationships, and _____. A. Fields B. Objects C. Constraints D. Classes
C. Constraints Correct. Constraints are intentional restrictions to the data, and allow us to ensure the data is accurate and reliable. Specifically, constraints enforce rules that were defined at the time of model creation as the data is manipulated.
Which of the following is true of business rules? a. They allow the designer to set company policies with regard to data. b. They allow the designer to develop business processes. c. They can serve as a communication tool between the users and designers. d. They provide a framework for the company's self-actualization.
C. They can serve as a communication tool between the users and designers. Correct! They can serve as a communication tool between the users and designers.
When creating a multi-user database ANSI has defined standards that govern SQL database transactions. There are two SQL statements __________________ and ROLLBACK that are utilized in transaction support.
COMMIT
In the detailed systems design phase, the designer __?
Completes the design of the system's processes
The first stage in the database design process is __?
Conceptual Design
Business rules use simple language to establish definitions of entities, attributes, relationships, and constraints. For instance, a business rule establishing that entities can have multiple attributes, and that each available attribute may be associated with only one entity, might be stated as "An employee may be assigned multiple devices" and "Each device may be assigned to only one employee". Classify the relationship between EMPLOYEES and DEVICES in this example using shorthand notation. A. 1:1 B. *..* C. M:N D. 1:M
D. 1:M Correct. 1:M signifies a one-to-many relationship, as described in the example. This relationship can also be described according to database convention as EMPLOYEE is assigned DEVICES.
The RDBMS automatically creates the ____ tables in which to store metadata.
Data Dictionary
As a database designer, which of the options below would you not include in the implementation phase of a database design?
Designing the database
One of the advantages of a database system over previous data management approaches is reduced costs. (T or F)
FALSE: Correct. Database systems require sophisticated hardware and software and highly skilled personnel. This increases costs.
A process that ensures a database is immediately updated by operations during the transaction, even before the transaction commit point is called write-through update.
False
A subquery will automatically overwrite an existing table if it has the same name.
False
Character data can contain any character or symbol intended for mathematical manipulation. (T or F)
False
Normalization is a process that is used for changing attributes to entities.
False
Only a single attribute, not multiple attributes, can define functional dependence. (T or F)
False
Serializability means that data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
False
The DBMS guarantees that the semantic meaning of a transaction truly represents the real-world event.
False
The Systems Development Life Cycle (SDLC) traces the history of an organization's system.
False
The designer does not need to have a thorough understanding of the company's data types, their extent, and uses to be able to develop an accurate data model.
False
The one-to-many (1:M) relationship is easily implemented in the relational model by putting the foreign key of the "1" side in the table of the "many" side as a primary key. (T or F)
False
The overall purpose of the database initial study is to create the conceptual design.
False
The row's range of permissible values is known as its domain. (T or F)
False
When creating a table with a SELECT statement, the CREATE TABLE command statement represents the subquery.
False
Comparison operators cannot be used to place restrictions on character-based attributes. (T or F)
False Correct. Comparison operators can be placed on character-based attributes; string base comparison is made from left to right.
The entity relationship model (ERM) is dependent on the database type. (T or F)
False Correct. The ERM is independent of the database type.
A database design can deviate from normal standards when the database size is less than 100 MB. (T or F)
False Correct. The database design must conform to design standards.
All SQL commands must be issued on a single line. (T or F)
False SQL commands can use several lines, to write queries that are easier to understand using several lines is the best practice.
Data is the result of interpreting facts to reveal meaning. (T or F)
False, Data is the result of processing RAW DATA to facts to reveal their meaning
Rows can be grouped into smaller collections quickly and easily using the COUNT clause within the SELECT statement. (T or F)
False. The rows are grouped by using the GROUP BY clause.
A good Database Management System (DBMS) will perform well even with a badly designed database. (T or F)
False: Correct. A well designed database is essential for a DBMS to perform well.
Inserted data will, in most cases, place a value that is meant for the primary key column into the ____ part of the VALUES statement.
First
The Systems Development Life Cycle (SDLC) is divided into __ phases.
Five
A user can insert only required information for a new product for columns P_CODE, P_DESCRIPTION, P_PRICE by entering the SQL statement ____.
INSERT INTO PRODUCT(P_CODE, P_DESCRIPT) VALUES ('BRT-345','Titanium drill bit'),35.99;
IF a SQL subquery included the statement that reads SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE, VENDOR_CODE, then the new columns would have been created for specific information from a more overall table that was most likely called ____.
PRODUCT
When re-evaluating the current ERD design, what is the best possible action a database designer can take to improve the current design in order to account for new operational processes in the company? a. Review the connectivity lines and symbols b. Review the designated private and foreign keys c. Review the relationship text between entities d. Correct! Review previous design documentation
Review previous design documentation Correct. Should review previous design documentation because every ERD should include a detailed documentation to understand why things were done, which can also help to find ways to make changes later.
A transaction is a logical unit of work that must be entirely completed or removed.
True
A view is a virtual table based on a SELECT query.
True
Authentication is the process the DBMS uses to verify that only registered users access the database.
True
Denormalization produces a lower normal form.
True
Dependency diagrams are very helpful in getting a bird's-eye view of all the relationships among a table's attributes.
True
Each table in a relational database must have a primary key. (T or F)
True
In a page-level lock, the DBMS will lock an entire diskpage.
True
In most modern relational DBMSs, a new database implementation requires the creation of special storage-related constructs to house the end-user tables.
True
In order to meet performance requirements, portions of the database design may need to be occasionally denormalized.
True
Normalization works through a series of stages called normal forms.
True
RDBMSs enforce integrity rules automatically. (T or F)
True
Relational algebra defines the theoretical way of manipulating table contents using relational operators.(T or F)
True
Relational models view data as part of a table or collection of tables in which all key values must be identified.
True
Repeating groups must be eliminated by ensuring that each row defines a single entity.
True
The objective of normalization is to ensure that each table conforms to the concept of well-formed relations.
True
The practical significance of taking the logical view of a database is that it serves as a reminder of the simple file concept of data storage. (T or F)
True
You can think of a table as a persistent representation of a logical relation. (T or F)
True
You must first enter the command ALTER TABLE in order to issue a MODIFY command to change a columns datatype.
True
The COUNT function is designed to tally the number of non-null "values" of an attribute and is often used in conjunction with the DISTINCT clause. (T or F)
True Correct. The count function is often used with the DISTINCT clause to count the number of different entities not just the total number of rows.
Connectivities and cardinalities are established by concise statements known as business rules. (T or F)
True Correct. Connectivities and cardinalities are established by concise statements known as business rules.
High processing speeds are often a high priority in database design when generating large numbers of transactions. (T or F)
True Correct. High processing speed means minimal access time which is often times a high priority.
Unstructured data is data that exists in the format in which it was collected (raw state). While Structured data is the result of formatting unstructured data to enable storage, use, and generation of information. (T or F)
True. Correct. Unstructured data is in its raw state while structured data is formatted to enable storage and generation of meaningful information.
When reading the database diagram with entities, management has a hard time understanding the implementation of these tables with just solid lines. Their relationships are still questionable. Using the tables ROOM and BUILDING, what is the most appropriate way a database designer can conceptualize their implementation in the diagram? a. Insert the phrase "located in" in between b. Insert the phrase "connected to" in between c. Insert the phrase "many in" in between d. Insert the phrase "one in many" in between
a. Insert the phrase "located in" in between Correct. Implementation conceptualizes how the database is designed in comparison to how the organization or company operates. Using the phrase "located in" is the most realistic way of describing the two tables' relationship.
After looking at the database design, another designer found that the primary keys were not established with a simple underline. Knowing that single-valued attribute can almost always be labeled as primary identities, which of the following attributes would be a good candidate as a primary key for a table? a. SS_NUMBER b. CAR_COLOR c. COLLEGE_DEGREE d. CAR_PART
a. SS_NUMBER Correct. A single-valued attribute is an attribute that can have only a single value. SS_NUMBER or social security number is a unique number that can be assigned to a STUDENT table, for example.
A user gets an error when entering new information into a table. The error suggests the wrong values are entered in. What can the user review about the table's attributes to ensure the right values are entered in? a. The attribute's domain b. The name of the entity c. The tables primary identifier d. The candidate key
a. The attribute's domain Correct. A domain is the set of possible values for a given attribute.
Within the database environment, a data model represents data structures and their attributes, relations, constraints, and transformations with the purpose of supporting a specific problem domain. Define "problem domain" in the context of data models. a. a blueprint containing all necessary instructions for building a database that will meet all end-user requirements b. a clearly defined area within a real-world environment that will be systematically addressed c. a relatively simple and usually graphical representation of complex real-world objects or events d. the process of creating a specific data model for a particular set of real-world data to be addressed
b. a clearly defined area within a real-world environment that will be systematically addressed Correct. A problem domain is a real-world set of circumstances, objects or events that serve as data points and represent the boundaries and scope of the problem(s) to be addressed.
In practice which of the following is more common to find? a. Well designed databases. b. Best practice application. c. Cryptic, confusing and nonstandardized attribute names. d. Patients' names and ids
c. Cryptic, confusing and nonstandardized attribute names. Correct. Real-world databases can be messy, the attribute names of the tables might not follow the best practices and as a result be cryptic, and confusing.
A(n) _____ is bidirectional. a. Attribute b. entity c. relationship d. constraint
c. Relationship: Correct. A relationship between entities flows in both directions.
A verb associating two nouns in a business rule translates to a(n) _____ in the data model. a. entity b. attribute c. relationship d. constraint
c. relationship Correct. A verb associating two nouns in a business rule translates to a relationship in the data model because the verb between the two nouns defines the relationship between them.
An atomic attribute _____.
cannot be further subdivided
An administrator can resolve the requirement for gathering multiple values from a SELECT statement to evaluate complex problems, for example, by processing those SELECT statements with ____.
cursors
When using a cross join between a table with 8 rows and another with 5 rows how many rows are in the result? a. 10 b. 13 c. 20 d. 40
d. 40 Correct. . The cross join is equivalent to the product of the two relations thus the result has 8 x 5 = 40 rows.
Which comparison operator indicates a value is not equal? a. < b. <= c. >= d. <>
d. <> Correct. The comparison operator for not being equal means that it is greater or lesser than <>.
Which of the answers below is one of the challenges of managing a database system? a. Infrequent Upgrade b. Management Simplicity c. Reduced costs d Vendor Dependence
d. Vendor dependende: Vendor dependence is one of the challenges of managing a database system.
When a database designer determines that two entities share the same primary keys in their relationship, what can the designer infer between the two? a. optional relationship b. Unary relationship c. weak relationship d. strong relationship
d. strong relationship Correct. Strong relationship because a strong (identifying) relationship exists when the primary key of the related entity contains a primary key component of the parent entity.
Business rules must describe the main and distinguishing features of the data as viewed by _____. a. the application programmer b. the end users c. the designer d. the company
d. the company Correct. Business rules must describe the main and distinguishing features of the data as viewed by the company. Business rules must be simple to grasp and broadly distributed in order for everyone in the company to have the same understanding of the rules.
Selecting database management system (DBMS) software is part of the _____ phase of the Database Life Cycle (DBLC).
database design
Lock granularity indicates the level of lock use which can take place at which levels:
database, table, page, row, or field (attribute)
According to the ERD data-modeling checklist, all entities in a relational table represent a single set of distinguishable sets of _____.
entity instances
Database recovery restores the database from current state to previous state which is triggered by a critical error caused by ___________ or ____________.
hardware error, software error
Critical events can render a database useless, there are three types of critical events __________, ____________, and ___________,
hardware/software failure, human-caused incidents, natural disasters
An administrator lost data on a new table called PRODUCT after making a few modifications on each product's information. VENDOR and CUSTOMER were also scheduled for modification. A manager found that the data was lost because ____.
the WHERE command was not specified
According to a standard data-modeling checklist, the non-key entities in an entity-relationship (ER) data model must be fully dependent on _____.
the primary key
Consider the following table: What is the result of the query below? SELECT * FROM TABLE1 WHERE val2 BETWEEN (100, 400) AND val3 IN (10, 20)
val 1 val2 val3 3 129 10 5 129 20
