Exam 3

C) functional dependency.

A constraint between two attributes is called a(n): ________ A) functional relation. B) attribute dependency. C) functional dependency. D) functional relation constraint.

B) drop

Any CREATE command may be reversed by using a(n) ________ command. A) undo B) drop C) delete D) unpack

A set of schemas that constitute the description of a database

Catalog

B) Physical design

DDL is typically used during which phase of the development process? A) Implementation B) Physical design C) Analysis D) Evaluation

C) create schema.

The command for creating a database is: A) create table. B) create view C) create schema. D) create authorization.

B) Changes the unit price of Product 7 to 775

What does the following SQL statement do? Update Product_T Set Unit_Price = 775 Where Product_ID = 7; A) Changes the price of a unit called Product_T to 7 B) Changes the unit price of Product 7 to 775 C) Changes the length of the Unit_Price field to 775 D) Updates the Product_T table to have a unit price of 775

C) primary key.

An attribute (or attributes) that uniquely identifies each row in a relation is called a: ________ A) column. B) foreign field. C) primary key. D) duplicate key.

-Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data -The process of decomposing relations with anomalies to produce smaller, well-structured relations -Normalization, based on what we will call normal forms and functional dependencies, defines rules of the business, not data usage

Data normalization

Tables (relations), rows, columns

Data structure

are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).

Foreign keys

The value of one attribute (the determinant) determines the value of another attribute

Functional dependencies

B) You must specify the fields to insert if you are only inserting some of the fields.

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? insert into store values ('234 Park Street'); A) It would work just fine. B) You must specify the fields to insert if you are only inserting some of the fields. C) There is no table keyword. D) Insert into should be INSERT to.

Insertion Anomaly - adding new rows forces user to create duplicate data Deletion Anomaly - deleting rows may cause a loss of data that would be needed for other future rows Modification Anomaly - changing data in a row forces changes to other rows because of duplication

Goal is to avoid anomalies

C) Where

In an SQL statement, which of the following parts states the conditions for row selection? A) Select B) From C) Where D) Group By

B) create table

In the SQL language, the ________ statement is used to make table definitions. A) create session B) create table C) create index D) select

D) An associate entity

In the figure below, what is depicted? A) A-one-to-one relationship B) A unary relationship C) A one-to-many relationship D) An associate entity

• It must have a unique name. • Every attribute value must be atomic (single-valued, not multivalued, not composite). • Every row must be unique (can't have two rows with exactly the same values for all their fields). • Attributes (columns) in tables must have unique names. • The order of the columns must be irrelevant. • The order of the rows must be irrelevant. -No multivalued attributes are allowed in a relation

Properties of relations

Rules that maintain consistency between the rows of two related tables. ----------------------------------------- This rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)

Referential Integrity

C) Primary and Foreign keys.

Referential Integrity Constraints are generally established between: A) Referential and Integrity keys. B) Primary and Secondary keys. C) Primary and Foreign keys. D) Foreign and Domestic keys

C) create view

The SQL command ________ defines a logical table from one or more tables or views. A) create table B) alter table C) create view D) create relationship

A) No primary key attribute can be null

The entity integrity rule states that: A)No primary key attribute can be null B)Referential integrity must be maintained across all entities C) Each entity must have a primary key D) A primary key must have only one attribute

A table should not pertain to more than one entity type.

Well-structured relations (General Rule of Thumb)

A) Deletes all records from customer_t where the state is equal to HI

What does the following SQL statement do? Delete from Customer_T where state = 'HI'; A) Deletes all records from customer_t where the state is equal to HI B) Removes the Customer_T table from the database C) Deletes all records from the Customer_T table D) Doesn't delete anything because of a syntax error

from the Customer table for each row with a customer labeled "Best"

What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best"'; A) Selects all the fields from the Customer table for each row with a customer labeled "Best" B) Selects the "*" field from the Customer table for each row with a customer labeled "Best" C) Selects fields with a "*" in them from the Customer table D) Selects all the fields from the Customer table for each row with a customer labeled "*"

A) The Item_No of all orders that had more than 10 items

What result set will the following query return? Select Item_No from Order_V where quantity > 10; A) The Item_No of all orders that had more than 10 items B) The Order_Id of all orders that had more than one item C) The Order_Id of all orders that had more than 10 items D) The Item_No of all orders that had 10 or more items

B) create two new relations, one containing the multivalued attribute

When a regular entity type contains a multivalued attribute, one must: ________ A) create a single relation with multiple lines for each instance of the multivalued attribute. B) create two new relations, one containing the multivalued attribute. C) create two new relations, both containing the multivalued attribute. D) delete the relation and start over.

A) first normal form

When all multivalued attributes have been removed from a relation, it is said to be in: ________ A) first normal form. B) second normal form. C) Boyce-Codd normal form. D) third normal form.

B) No two rows in a relation are identical

Which of the following are properties of relations? A) Each attribute has the same name B) No two rows in a relation are identical C) There are multivalued attributes in a relation D) All Columns are numeric

TRUE

[T/F] SQL is both an American and international standard for database access.

FALSE

[T/F] The CREATE SCHEMA DDL command is used to create a table.

TRUE

[T/F] When a regular entity type contains a multivalued attribute, two new relations are created.

FALSE

[T/F] When transforming a unary many-to-many relationship to relations, a recursive foreign key is used.

B) Physical

________ database specification indicates all the parameters for data storage that are then input to database implementation. A) Logical B) Physical C) Schematic D) Conceptual

B) Data integrity

________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated. A) Business rule constraint B) Data integrity C) Business integrity D) Data structure

A)DML

________ is a set of commands used to update and query a database. A) DML B) DDL C) DCL D) DPL

B) the key must indicate the row's position in the table.

A candidate key must satisfy all of the following conditions EXCEPT: ________ A) the key must uniquely identify the row. B) the key must indicate the row's position in the table. C) the key must be nonredundant. D) each nonkey attribute is functionally dependent upon it.

A) logical specifications.

A form of database specification which maps conceptual requirements is called: A) logical specifications. B) response specifications. C) security specifications. D) physical specifications.

C) descriptor.

A nonkey attribute is also called a(n): ________ A) column. B) unimportant datum. C) descriptor. D) address.

B) Composite Key

A primary key that consists of more than one attribute is called a: A) Foreign Key B) Composite Key C) Multivalued Key D) Cardinal Key

C) enterprise key

A primary key whose value is unique across all relations is called a(n): ________ A) global primary key. B) inter-table primary key. C) enterprise key. D) foreign global key.

B) Second

A relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies is in which normal form? A) First B) Second C) Third D) Fourth

D) relation

A two-dimensional table of data sometimes is called a: ________ A) group. B) set. C) declaration. D) relation.

A) Composite

A) Composite B) Foreign C) Standard D) Grouped

D) Recursive foreign

A) Primary B) Recursive primary C) Composite D) Recursive foreign

B) One-to-many

A) Strong entity/weak entity B) One-to-many C) Ternary D) Many-to-many

C) maximize storage space

All of the following are the main goals of normalization EXCEPT: ________ A) minimize data redundancy. B) simplify the enforcement of referential integrity. C) maximize storage space D) make it easier to maintain data.

A unique identifier. One of the candidate keys will become the primary key Each non-key field is functionally dependent on every candidate key

Candidate Key

attributes.

Columns correspond with

Commands that control a database, including administering privileges and committing data

Data Control Language (DCL)

Commands that define a database, including creating, altering, and dropping tables and establishing constraints

Data Definition Language (DDL)

Commands that maintain and query a database

Data Manipulation Language (DML)

Mechanisms for implementing business rules that maintain integrity of manipulated data

Data integrity

B) tables

Data is represented in the form of: ________ A) data trees. B) tables. C) data notes. D) chairs.

Powerful SQL operations for retrieving and modifying data

Data manipulation

Allowable values for an attribute Domain definitions enforce domain integrity constraints

Domain Constraints

No primary key attribute may be null. All primary key fields MUST contain data values.

Entity Integrity

Table with no multivalued attributes and unique rows Every attribute value is atomic

First normal form (1NF)

A) Strong entity/weak entity

In the figure below, what type of relationship do the relations depict? A)Strong entity/weak entity b) Multivalued C) Composite Foreign Key D) One-to-one

C) provide rapid random and sequential access to base-table data.

Indexes are created in most RDBMSs to: A) provide a quicker way to store data. B) decrease the amount of disk space utilized. C) provide rapid random and sequential access to base-table data. D) increase the cost of implementation.

are unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique.

Primary keys

A relation is a named, two-dimensional table of data. A table consists of rows (records) and columns (attribute or field).

Relation

entity types and with many-to- many relationship types.

Relations (tables) correspond with

entity instances and with many-to-many relationship instances.

Rows correspond with

The structure that contains descriptions of objects created by a user (base tables, views, constraints)

Schema

1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key Every non-key attribute must be defined by the entire key, not by only part of the key No partial functional dependencies

Second normal form (2NF)

B) alter table

The SQL command ________ adds one or more new columns to a table. A) create table B) alter table C) create view D) create relationship

B) Schema

The ________ is the structure that contains descriptions of objects such as tables and views created by users. A) SQL B) schema C) catalog D) master view

B) entity integrity rule

The ________ states that no primary key attribute may be null. A) referential integrity constraint B) entity integrity rule C) partial specialization rule D) range domain rule

D) Ternary

The figure below is an example of mapping which type of relationship? A) First B) Second C) Unary D) Ternary

B) identify each attribute and its characteristics.

The first in a series of steps to follow when creating a table is to: A) identify columns that must be unique. B) identify each attribute and its characteristics. C) create an index. D) identify columns that must be null.

B) fourth normal form.

The normal form which deals with multivalued dependencies is called: ________ A) fifth normal form. B) fourth normal form. C) Boyce-Codd normal form. D) sixth normal form.

2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes) -Note: This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third

Third normal form (3NF)

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

Well-structured relations

C) DCL

________ is a set of commands used to control a database, including security. A) DML B) DDL C) DCL D) DPL