Database Management: Final Exam
A domain definition consists of the following components:
- domain name - meaning - data type - size (or length) - allowable values or - allowable range (if applicable)
Physical database design decisions must be made carefully because of impacts on:
1) Data accessibility 2) Response times 3) Security
An integrity control supported by a DBMS is:
1) Default Value 2) Range Control 3) Null Value Control 4) Referential Integrity
Advantages of partitioning are:
1) Efficiency 2) Local Optimization 3) Security 4) Recovery and Uptime 5) Load Balancing
Factors to consider when choosing a file organization are:
1) Fast data retrieval 2) security 3) efficient storage
Disadvantages of partitioning are:
1) Inconsistent Access Speed 2) Complexity 3) Extra Space and Update Time
main goals of normalization:
1) Minimize data redundancy, thereby avoiding anomalies and conserving storage space. 2) Simplify the enforcement of referential integrity constraints. 3) Make it easier to maintain data (insert, update, and delete). 4) Provide a better design that is an improved representation of the real world and a stronger basis for future growth
Requirements to begin designing physical files and databases is:
1) Normalized Data 2) Definitions of each attribute 3) Technology Descriptions
Objectives in selecting a data type:
1) Represent all possible values. 2) Improve data integTity. 3) Support all data manipulations. 4) Minimize storage space.
A candidate key must satisfy all of the following conditions:
1) Unique identification: For every row, the value of the key must uniquely identify that row. 2) Non-redundancy: No attribute in the key can be deleted without destroying the property of unique identification.
A method for handling missing data is to:
1) substitute and estimate for the missing data. 2) track missing data with special reports. 3) perform sensitivity testing.
All of the following are common denormalization opportunities:
1) two entities with a 1:1 relationship 2) a M:M relationship with non-key attributes 3) Reference data
Which of the following are properties of relations?
1. Each relation (or table) in a database has a unique name. 2. An entry at the intersection of each row and column is atomic (or single valued). There can be only one value associated with each attribute on a specific row of a table; no multivalued attributes are allowed in a relation. 3. Each row is unique; no two rows in a relation can be identical. 4. Each attribute (or column) within a table has a unique name. 5. The sequence of columns (left to right) is insignificant. The order of the. columns in a relation can be changed without changing the meaning or use of the relation. 6. The sequence of rows (top to bottom) is insignificant. As with columns, the. order of the rows of a relation may be changed or stored in any sequence.
E. F. Codd developed the relational model in the:
1970's
ADD a text field in the Customer table named "Customer Email"
ALTER TABLE Customer ADD COLUMN Cust_Email VARCHAR2(30);
CREATE an INDEX
CREATE INDEX indexname_IDX ON tablename(column)
CREATE a table for Salesman
CREATE TABLE Salesman (Salesman_ID NUMBER(5) NOT NULL, S_Name VARCHAR2(30) NOT NULL, S_City VARCHAR2(30), S_Commission DECIMAL(5,2), CONSTRAINT Salesman_PK PRIMARY KEY (Salesman_ID), CONSTRAINT Salesman_FK FOREIGN KEY (other_key) REFERENCES OtherTable (other_key));
DELETE records from Customer table if they were served by Salesman #5001
DELETE FROM Customer WHERE Salesman_ID = 5001;
DELETE all rows from Customer table
DELETE * FROM Customer;
REMOVE an INDEX
DROP INDEX indexname
REMOVE Customer table
DROP TABLE Customer;
________ 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
The relational data model consists of which components?
Data structure Data integrity Data manipulation
The value a field will assume unless the user enters an explicit value for an instance of that field is called a(n):
Default Value
Database development begins with ________, which establishes the range and general contents of organizational databases
Enterprise Data Modeling
(T/F) A business rule is a statement of how a policy is enforced or conducted
FALSE
(T/F) An example of a term would be the following sentence: "A student registers for a course."
FALSE
(T/F) Most systems developers believe that data modeling is the least important part of the systems development process.
FALSE
(T/F) The intent of a business rule is to break down business structure
FALSE
(T/F) When systems are automatically generated and maintained, quality is diminished.
FALSE
A file organization is a named portion of primary memory. (T/F)
False
Indexes are most useful on small, clustered files. (T/F)
False
When transforming a weak entity, one should create one relation with both the attributes of the strong entity and the attributes of the weak entity. (T/F)
False
A relation is in first normal form if it has no more than one multivalued attribute. (T/F)
False (1ST NF = no repeating groups and a PK has been established)
An anomaly is a type of flaw in the database server. (T/F)
False (Anomaly: An error or inconsistency that may result when a user attempts to update a table that contains redundant data)
The entity integrity rule states that a primary key attribute can be null. (T/F)
False (a PK always needs a value, it's the f*cking PK)
In the relational data model, associations between tables are defined through the use of primary keys. (T/F)
False (associations between tables is a Foreign Key)
When transforming a unary many-to-many relationship to relations, a recursive foreign key is used. (T/F)
False (both take their values from the primary key)
Reduced uptime is a disadvantage of partitioning. (T/F)
False (it's an advantage)
When transforming a one-to-one relationship, a new relation is always created. (T/F)
False (it's optional)
Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields (T/F)
False (it's the process of transforming normalized relations into non-normalized physical record specifications)
Unlike columns, the rows of a relation may not be interchanged and must be stored in one sequence. (T/F)
False (row is the data going across, like an individual Employee's info. so the order they're in doesn't matter either)
A key is a data structure used to determine the location of rows in a file that satisfy some condition. (T/F)
False (that's an index)
A transversal dependency is a functional dependency between two or more non-key attributes. (T/F)
False (transversal isn't a thing)
Anomalies do not generally arise out of transitive dependencies. (T/F)
False (you have to remove transitive dependencies to make it 3rd NF, therefore they must exist in there in the first place)
A foreign key is a primary key of a relation that also is a primary key in another relation (T/F)
False (not the PK of both)
When all multi-valued attributes have been removed from a relation, it is said to be in:
First Normal Form
INSERT a record of information (remember the order you put this matters)
INSERT INTO Customer VALUES (3002, 'Nick Rimando', 'NY', 100, '[email protected]');
Which of the following is NOT a cost and/or risk of the database approach
Improved responsiveness
A form of database specification that indicates all the parameters for data storage that are then input to database implementation is:
Physical Database
Which of the following is NOT a good characteristic of a data name?
Relates to a technical characteristic of the system
DISPLAY the sum of two numbers 82 and 15 as "sum"
SELECT (82+15) AS Sum; (fyi: "AS" gives the column a name, with the answer underneath it. otherwise the default is ¿Column1?)
DISPLAY all the information from the customer table
SELECT * FROM Customer;
DISPLAY all information for those customers with a grade of 200
SELECT * FROM Customer WHERE Grade = 200;
DISPLAY customer ids and customer names
SELECT Cust_ID, Cust_Name FROM Customer;
RETRIEVE the values of customer cities without any repeats
SELECT DISTINCT C_City FROM Customer;
DISPLAY salesman id, salesman name, order date, order no, and purchase amount (fyi: all info is in the Salesman table except for salesman id, that's on the Order table)
SELECT Order.Salesman_ID, S_Name, Ord_Date, Ord_No, Purch_Amt FROM Salesman, Order;
DISPLAY the description and price of the products with a price less than $275
SELECT Product_Desc, Product_Price FROM Product WHERE Product_Price < 275
A relation that contains no multivalued attributes and has non-key attributes solely dependent on the primary key but contains transitive dependencies is in which normal form?
Second Normal Form
The traditional methodology used to develop, maintain and replace information systems is called the
Systems Development Life Cycle (SDLC)
(T/F) A business rule is a statement that defines or constrains some aspect of the business.
TRUE
(T/F) A business rule should be internally consistent.
TRUE
(T/F) A good data definition is always accompanied by diagrams, such as the entity-relationship diagram.
TRUE
(T/F) A single occurrence of an entity is called an entity instance.
TRUE
(T/F) An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data
TRUE
(T/F) Data names should always relate to business characteristics.
TRUE
(T/F) Enforcement of business rules can be automated through the use of software tools that can interpret the rules and enforce them.
TRUE
(T/F) One of the roles of a database analyst is to identify and understand rules that govern data.
TRUE
(T/F) The E-R model is used to construct a conceptual model.
TRUE
(T/F) When choosing an identifier, choose one that will not change its value often.
TRUE
(T/F) While business rules are not redundant, a business rule can refer to another business rule.
TRUE
Which of the following is NOT an objective that drove the development and evolution of database technology?
The desire to require programmers to write all file handling functionality
(T/F) Table names HAVE TO be singular
True
A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. (T/F)
True
A cascading delete removes all records in other tables associated with the record to be deleted. (T/F)
True
A partial functional dependency is a functional dependency in which one or more non-key attributes are functionally dependent on part (but not all) of the primary key. (T/F)
True
A range control limits the set of permissible values that a field may assume. (T/F)
True
A referential integrity constraint is a rule that maintains consistency among the rows of two relations. (T/F)
True
A well-structured relation contains minimal redundancy and allows users to manipulate the relation without errors or inconsistencies. (T/F)
True
All values that appear in a column of a relation must be taken from the same domain. (T/F)
True
Clustering allows for adjacent secondary memory locations to contain rows from several tables. (T/F)
True
If an identifier is not assigned, the default primary key for an associative relation consists of the two primary key attributes from the other two relations. (T/F)
True
Keeping the zip code with the city and state in a table is a typical form of denormalization. (T/F)
True
Security is one advantage of partitioning. (T/F)
True
The allowable range of values for a given attribute is part of the domain constraint. (T/F)
True
The columns of a relation can be interchanged without changing the meaning or use of the relation. (T/F)
True
When normalizing, the goal is to decompose relations with anomalies to produce smaller, well-structured relations. (T/F)
True
In the figure below, each employee has exactly one manager. (T/F)
True EMPLOYEE --|o------o<--(back to itself)
MODIFY standard price of product id #7 to 775
UPDATE Product_T SET ProductStandardPrice = 775 WHERE Product_ID = 7;
CHANGE the commission rate for salesman Pit Alex to .24
UPDATE Salesman SET S_Commission = .24 WHERE S_Name = 'Pit Alex';
All of the following are valid datatypes in Oracle 11G:
VARCHAR2 CHAR CLOB NUMBER DATE BLOB
The SDLC phase in which the detailed conceptual data model is created is the ________ phase.
analysis
In a file processing environment, descriptions for data and the logic for accessing the data are built into:
application programs
An entity type name should be all of the following EXCEPT
as short as possible
A property or characteristic of an entity type that is of interest to the organization is called a(n):
attribute
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
Sensitivity testing involves:
checking to see if missing data will greatly impact results
An attribute that can be broken down into smaller parts is called a(n) ________ attribute
composite
A rule that CANNOT be violated by database users is called a:
constraint
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.
A graphical system used to capture the nature and relationships among data is called a(n):
data model
The number of entity types that participate in a relationship is called the:
degree
The attribute on the left-hand side of the arrow in a functional dependency is the:
determinant
Horizontal partitioning makes sense when:
different categories of rows of a table are processed separately
Customers, cars, and parts are examples of:
entities
A person, place, object, event, or concept about which the organization wishes to maintain
entity
The ________ states that no primary key attribute may be null.
entity integrity rule
The logical representation of an organization's data is called a(n):
entity-relationship model
The most common source of database failures in organizations is
failure to implement a strong database administration function.
The smallest unit of application data recognized by system software is a:
field
Program-data dependence is caused by:
file descriptors being stored in each application.
Distributing the rows of data into separate files is called:
horizontal positioning
A(n) ________ is the relationship between a weak entity type and its owner.
identifying relationship
Which of the following is NOT a characteristic of a good business rule?
inconsistent
The three-schema approach includes which of the following schemas?
internal
Older systems that often contain data of poor quality are called ________ systems.
legacy
A database is an organized collection of ________ related data
logically
Business policies and rules govern all of the following EXCEPT
managing employees
Data that describe the properties of other data are:
metadata
The entity integrity rule states that:
no primary key attribute can be null
Which organizational function should set database standards?
none
While Oracle has responsibility for managing data inside a table-space, the tablespace as a whole is managed by the:
operating system
A(n) ________ is a field of data used to locate a related field or record.
pointer
All of the following are properties of metadata EXCEPT
processing logic
One of the most popular RAD methods is:
prototyping
All of the following are primary purposes of a database management system (DBMS) EXCEPT:
providing an integrated development environment.
Which of the following is NOT an advantage of database systems
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.
Relational databases establish the relationships between entities by means of common fields included in a file called a(n):
relation
________ are established between entities in a well-structured database so that the desired information can be retrieved.
relationships
A centralized knowledge base of all data definitions, data relationships, screen and report formats, and other system components is called a(n):
repository
A knowledge base of information or facts about an enterprise is called a(n):
repository
With the database approach, data descriptions are stored in a central location known as a:
repository
Which of the following is an objective of selecting a data type?
representing organizational data
An attribute of an entity that must have a value for each entity instance is a(n):
required attribute
An attribute that must have a value for every entity (or relationship) instance is a(n):
required attribute
A workgroup database is stored on a central device called a:
server
The most common types of entities are:
strong
Which of the following is an entity that exists independently of other entity types?
strong entity
A fact is an association between two or more:
terms
A simultaneous relationship among the instances of three entity types is called a ________ relationship.
ternary
Database access frequencies are estimated from:
transaction volumes
A functional dependency between two or more non-key attributes is called a:
transitive dependency
In an E-R diagram, there are/is ________ business rule(s) for every relationship
two
A relationship between the instances of a single entity type is called a ________ relationship
unary
Because applications are often developed independently in file processing systems:
unplanned duplicate data files are the rule rather than the exception.
A(n) ________ is often developed by identifying a form or report that a user needs on a regular basis.
user view
An entity type whose existence depends on another entity type is called a ________ entity
weak entity
A good data definition will describe all of the characteristics of a data object EXCEPT:
who can delete the data
Understanding the steps involved in transforming EER diagrams into relations is important because:
you must be able to check the output of a CASE tool