DATA MANAGEMENT
What does SQL stand for?
SQL stands for structured query language..
What is critical to support significant data mining or online analytical processing?
Selecting an appropriate data warehouse system.
What are subject databases?
Subject databases are designed around the essential entities of a business, an example is customer, rather than applications, an example is auto insurance.
The CHECK constraint does what?
The CHECK constraint defines a set of valid values and comes in three forms: table, column, or domain constraint.
Which aggregate function does not distinguish between null and non-null values in a column?
The COUNT aggregate function does not distinguish between null and non-null values in a column.
Who has the highest privilege or is king of the heap.
The DBA, database administrator, is king of the heap and has the highest privilege. The DBA can perform any legal operation. The creator of an object, say a base table, has full privileges for that object.
Which statement deletes one or more rows in a table?
The DELETE statement erases one or more rows in a table. The general format is DELETE FROM table [WHERE condition];
The GOAL of Normalization is what?
The GOAL of normalization is a high-fidelity design; same as data modeling.
Which clause is associated with GROUP BY?
The HAVING clause is often associated with the GROUP BY.
The HAVING is used to eliminate what for a GROUP BY condition?
The HAVING is used to eliminate rows for a GROUP BY condition.
When is the LIKE clause used?
The LIKE clause is used to support pattern matching to find a defined set of strings in a character column, CHAR or VARCHAR.
REVOKE statement has this action.
The REVOKE removes privileges.
Which statement is the most interesting and challenging of the four DML statements?
The SELECT statement is by far the most interesting and challenging of the four DML statements.
UPDATE clause is used to do what?
The UPDATE clause is used to modify values in a table.
The WITH GRANT OPTION clause allows what?
The WITH GRANT OPTION command allows a user to transfer his privileges to another user.
What does CREATE TABLE do?
It specifies a table's name, provides details of its columns, and provides integrity checks.
After data are extracted, transformed, and cleaned are loaded by three types. What are they?
1. Archival: Historical data that is loaded once. 2. Current: Data from current operational systems. 3. Ongoing: Continual revision of the warehouse as operational data are generated
What are some reasons why data may need cleaning?
1. Duplicate records must be deleted. 2. Multiple records for a company exist because of an acquisition. 3. Multiple entries for the same entity exist because there are no corporate data entry standards. 4. Data entry fields are missed.
When using the DROP statement what happens?
A base table can be deleted at any time by using the DROP statement.
What is a base table?
A base table is an autonomous, named table. It is autonomous because it exists in its own right; it is physically stored within the database.
What is a centralized data warehouse?
A centralized data warehouse gives processing efficiency and lowers support costs.
What is a composite key?
A composite key is an ordered set of columns of the same table. In other words, the primary key of lineitem is always the composite of saleno, lineno in that order. The order cannot be changed.
What is a constraint?
A constraint is a rule defined as part of CREATE TABLE that defines valid sets of values for a base table by placing limits on INSERT, UPDATE, and DELETE operations.
What is metadata?
A data dictionary is a reference repository containing metadata or data about data. It includes a description of each data type, its format, coding standards, and the meaning of the field.
What is a data warehouse also called?
A data warehouse is a snapshot of an organization at a particular time.
An identifier, usually called a key when discussing normalization is what?
A determinant is called a key when discussing normalization and is an identifier.
A distributed architecture can be what 2 types?
A distributed architecture can be either federated or tiered.
When is a foreign key null?
A foreign key is null when any one of the columns in the row constituting the foreign key is null.
A full outer join consists of what?
A full outer join is an inner join plus those rows from the far right and far left not included in the inner join.
What returns a value when invoked within a SQL statement?
A function is SQL code that returns a value when invoked within a SQL statement.
What is functional dependency a property of?
A functional dependency is a property of a relation's data.
What is the concept of a key in SQL?
A key is one or more columns identified as such in the description of a table, an index, or a referential constraint. The same column can be part of more than one key.
What is a procedure in SQL?
A procedure is SQL code that is dynamically loaded and executed by a CALL statement, usually within a database application.
A simple subquery is.
A query within a query.
When is a relation in first normal form?
A relation is in first normal form if and only if all columns are single-valued. In data modeling terms, this means that an attribute must have a single value.
Second normal form consists of what?
A relation is in second normal form if and only if it is in first normal form, and all nonkey columns are dependent on the key.
Third normal form is described as what?
A relation is in third normal form if and only if it is in second normal form and has not transitive dependencies. A nonkey column has to be fully functionally dependent on the primary key.
What is a right join?
A right join is an inner join plus those rows from the far right not included in the inner join.
The primary key constraint block specifies what?
A set of columns that constitute the primary key.
What is a DATA WAREHOUSE?
A subject-oriented, integrated, time-variant, and nonvolatile set of data that supports decision making and has emerged as the key device for harnessing organizational memory.
A SELECT statement nested inside another SELECT statement is what?
A subquery is a SELECT statement nested inside another SELECT statement.
Can a table be given a temporary name?
A table or view can be given a temporary name, or alias, that remains current for a query.
What is a tiered architecture?
A tiered architecture houses highly aggregated data on an analyst's workstation, with more detailed summaries on a second server, and most detailed data on a third server.
Normalization is also what?
A useful crosscheck for ensuring the soundness of a data model.
What are the two purposes of the ALTER TABLE statement?
ALTER TABLE can add a single column to an existing table. Second, ALTER TABLE can add, drop, activate, or deactivate primary and foreign key constraints
ASC means what?
ASC means the values are arranged in ascending order.
What increases SQL's retrieval power?
Aggregate functions such as SUM, AVG, MAX, MIN, and COUNT increase its retrieval power.
Can UPDATE be used to modify all rows in a table at once?
All rows in a table can be updated by simply omitting the WHERE clause. For example UPDATE emp SET empsalary = empsalary * 1.05;
What is a determinant?
An attribute, or set of attributes, that fully functionally determines another attribute is called a determinant.
Data Modeling is defined as what?
An efficient and easily learned approach to database design.
An identifier functionally determines what?
An identifier functionally determines all the attributes in an entity.
What is an index?
An index is an ordered set of pointers to rows of a base table.
What does an inner join do?
An inner join reports those rows where the primary and foreign keys match.
The BOOLEAN data type can have what values?
BOOLEAN data types can have values of true or false.
Why does a data warehouse contain nonvolatile data?
Because a data warehouse is updated with a bulk upload, rather than as transactions occur, it contains nonvolatile data.
The nonnumeric columns are stored as what strings?
CHAR and VARCHAR are used to store character strings.
What does the COUNT statement do?
COUNT is used to count the number of rows in a table.
CREATE TABLE is used to define what?
CREATE TABLE is used to define a new base table. The statement specifies a table's name, provides details of its columns, and provides integrity checks.
CREATE VIEW does what?
CREATE VIEW creates only a virtual view of a table.
Why should a CREATE VIEW be used?
CREATE VIEW should be used to restrict access to certain rows or columns, derived data such as computed columns of two or more columns, and a third reason for defining a view to avoid writing common SQL queries. Data conversion is a fourth reason for a view.
CHAR and VARCHAR are used to store what type of string?
Character strings are stored in CHAR and VARCHAR string types.
How are dates stored?
DATE data types are used to store dates.
What is the most convenient form of storing data for business applications?
DECIMAL data type is convenient for business applications because data storage requirements are defined in terms of the maximum number of places to the left and right of the decimal point.
What does deleting multiple records look like?
DELETE FROM stock WHERE natcode IN SELECT natcode FROM nation WHERE natname = 'Australia';
Deleting a single record looks like?
DELETE FROM stock WHERE stkcode = 'BE';
What does deleting all records look like?
DELETE FROM stock;
What does DESC mean?
DESC is a clause that specifies that the values are arranged in descending order.
How can you drop a view?
DROP VIEW is used to delete a view from the system catalog.
What did Data Modeling first emerge as?
Data Modeling first emerged as entity-relationship (E-R) modeling in a paper by Peter Pin-Shan Chen.
What is a federated data warehouse?
Data are distributed by function. For example financial, marketing, and manufacturing are all on a different DBMS server at the same location.
What has to happen in order to create this snapshot?
Data must be extracted from existing systems, transformed, cleaned, and loaded into the data warehouse.
How does data get extracted?
Data must be extracted from operational systems on an ongoing basis so that analysis can work with current data.
What are the types of transformation?
Encoding, unit of measure, field, and date all must be converted to a common system.
How many primary keys can a table have?
Every table can have only ONE primary key.
How many binary digits in length is SMALLINT?
Fifteen binary digits is the length of the SMALLINT data type.
What two key database design concepts did Chen introduce?
First identify entities and the relationships between them. Second a graphical representation improves design communication.
Full functional dependency means what?
Full functional dependency means that one attribute can be determined only from both attributes. For example stock dividend, stock price --> yield. This is read as stock dividend, stock price fully functionally determines yield.
The definition of functional dependency is what?
Functional dependency is a relationship between attributes in an entity. It simply means that one or more attributes determine the value of another.
SQL provided two types of routines. What are they?
Functions and procedures that are created, altered, and dropped using standard SQL.
What does comparing composite keys actually mean?
It means that corresponding components of the keys are compared. Thus the value of a foreign key must be equal to a value of the primary key. This means that each component of the foreign key must be equal to a corresponding component of a composite primary key.
Multivalued dependency means what?
It means that functional dependencies are multivalued.
Those with privileges can use GRANT and REVOKE to do what?
GRANT and REVOKE commands included in SQL's data control language DCL are used to extend privileges to or rescind the from other users.
The GRANT command does what?
GRANT defines a user's privileges.
What is the general format for the GRANT command?
GRANT privileges ON object TO users {WITH GRANT OPTION};
The DISTINCT statement does what?
Gets rid of duplicate rows.
What can be thought of as the WHERE clause of GROUP BY?
HAVING clause can be thought of as the WHERE clause of the GROUP BY.
The INSERT clause is used for what?
INSERT is used in two formats. The first is used to insert one row into a table. The second is used to insert rows when operating in conjunction with a subquery.
How do you get a normalized database?
If the principles of data modeling are followed faithfully, then the outcome should be a high-fidelity model. Thus if you model data correctly, you create a normalized design.
Indexes are used for what?
Indexes are used to accelerate data access and ensure uniqueness.
Too many companies are data rich but what?
Information poor.
Normalization is the what?
Initial approach to database design.
Integrated implies what?
Integrated implies consistency in naming conventions, keys, relationships, encoding, and translation, an example is gender, is always coded as m or f in all relevant fields.
What can the key be part of?
It can be part of a primary key and a foreign key.
What is SQL?
It is a complete database language
The left outer join does what?
It is a inner join plus those rows from far left not included in the inner join.
For many organizations what is true about their memory or data?
It is a major untapped resource an underused intelligence system containing undetected by facts about customers.
What is a GROUP BY clause ?
It is an elementary form of control break reporting and supports grouping of rows that have the same value for a specified column and produces one row for each different value of the grouping column.
What is searching an index like?
It is like searching a phone book by a person's last name.
If a nonkey column is part of the composite key in second normal form what is said to be wrong with it?
It is not FULLY FUNCTIONALLY DEPENDENT on the primary key.
How is a base table referenced?
It is referenced by the name that is chosen when the base table is generated using the CREATE statement.
What is SQL used as?
It is widely used as a relational database language.
What does the SELECT statement do?
It reveals a major benefit of the relational model--powerful interrogation capabilities.
The NOT NULL clause specifies what?
It specifies that the particular column must have a value whenever a new row is inserted.
The clause REFERENCES table-name specifies what?
It specifies the name of the existing table, and its primary key, that contains the primary key, which cannot be the name of the table being created.
What is the purpose of a unique key?
Its purpose is to ensure that no two values of a key are equal. This constraint is enforced by the DBMS during the execution of INSERT and UPDATE statements. A unique key is part of the index mechanism.
Join is a powerful and frequently used operation that does what?
Join creates a new table from two existing tables by matching on a column to both tables.
Why is the SELECT statement challenging?
Mastering the power of the SELECT statement requires considerable practice with a wide range of queries.
Is it mandatory to define primary key?
NO it is not mandatory to define a primary key but it is good practice always to define a table's primary key.
Is a view autonomous?
No a view is a virtual table.
Is it a complete programming language?
No it is used in conjunction with PHP and Java.
What is normalization?
Normalization is a method for increasing the quality of database design. It is also a theoretical base for defining the properties of relations.
Normalization is two things. What are they?
Normalization is both a database design technique and a theoretical foundation.
Normalization is both a what and what?
Normalization is both a method and a theory.
Normalization is also what?
Normalization is the application of a series of rules. For example the normal forms that gradually improve the design.
Null means what?
Null means no value.
In the case of aggregate functions when are nulls in the column ignored?
Nulls in the column are ignored in the case of SUM, AVG, MAX, and MIN.
FLOAT is used to store what type of numbers?
Numbers that are very large or very small use the FLOAT data type.
ON DELETE clause defines.
ON DELETE clause defines the action taken when a row is deleted from the table containing the primary key.
Each table can have how many primary keys?
ONE primary key for each table is all that is allowed.
What is ORGANIZATIONAL INTELLIGENCE?
ORGANIZATIONAL INTELLIGENCE is the outcome of an organization's efforts to collect, store, process, and interpret data from internal and external sources.
What is a major advantage of a RDBMS?
One of the major advantages of a relational database management system is that DDL statements can be executed at any time. New tables, views, and indexes can be created without interfering with current operational use.
What is the format for REVOKE?
REVOKE privileges ON object FROM users;
What is meant by scheduling?
Refreshing the warehouse, must be scheduled as part of a data center's regular operations.
Routines add what across applications?
Routines add flexibility, improve programmer productivity, and facilitate the enforcement of business rules and standard operating procedures.
SQL supports four DML statements they are.
SELECT, INSERT, UPDATE, and DELETE are four DML statements.
What does SQL allow for?
SQL allows for rows to be inserted, updated, and deleted.
In database terminology what is it?
SQL is a data definition language (DDL), a data manipulation language (DML), and a data control language (DCL).
What type of language is SQL?
SQL is a declarative language, because you declare the desired results. The SQL programmer can focus on defining what is required rather than detailing the process to achieve what is required.
What is SQL used for?
SQL is used for defining a relational database, creating views, and specifying queries.
What is the first stage of decision making?
The conclusions or clues gleaned from an organization's data stores enable it to identify problems or opportunities.
What reasons are there for data to be cleaned?
The data may contain errors, inconsistencies, or redundancies.
The data type and length of the foreign key columns must match what exactly?
The data type and length of the primary key columns must match the foreign key columns exactly.
What handles the database processing?
The embedded SQL statements handle the database processing.
What is the simplest form of join?
The equijoin is the simplest form of join. In this case columns are matched on equality.
What does the foreign key consist of?
The foreign key consists of one or more columns in the table that together must match a primary key of the specified table (or else be null).
The referential constraint block defines what?
The foreign key is defined by the referential constraint block.
How can an index be dropped?
The general form of this statement is DROP INDEX index;
How does a correlated subquery differ from a simple subquery?
The inner query must be evaluated more than once.
The situation where a given value determines multiple values is called what?
The multidetermination property is a situation where a given value determines multiple values.
When did the need for normalization come about?
The need for normalization seems to have arisen from the conversion of file systems into database format.
How can you tell if you need to write a correlated subquery?
The requirement to compare a column against a function, e.g. average or count, of some column of specified rows of is usually a clue that you need to write a correlated subquery.
What is a data mart?
The second tier is a data mart, a local, single-subject database.
What are the three key building blocks of a data warehouse?
The three key building blocks of a data warehouse are the overall warehouse architecture, the server architectures, and the DBMS.
How can you think of the INSERT with a subquery.
Think of it as a way of copying a table. You can select the rows and columns of a particular table that you want to copy into an existing or new table.
INTEGER can be how many binary digits in length?
Thirty-One binary digits is the length of the INTEGER data type.
What is Time-variant?
Time-variant means that data are organized by various time periods, an example is by months.
How do you join a table to itself?
To do this, make two copies of the table first and give each of these copies a unique name. Rename the copies in the FROM clause. For example FROM emp work, emp boss.
What is part of the extraction process?
Transformation is part of the extraction process.
How do you create an index?
Use the CREATE UNIQUE INDEX format.
What is a trigger to use a GROUP BY clause in a query?
When you use an aggregate function in the SELECT statement such as COUNT then use GROUP BY after the WHERE clause.
Can UPDATE be used to modify more than one row.
Yes UPDATE is used to modify single or multiple rows in a table.
