222 Final Exam

How many row(s) are required to hold information specific to each entity?

1

Security

A database's ___________________ features help to ensure that there is no accidental/intentional deletion of records from the database.

Entity

A distinct object in a database is a/an _______________.

Denormalization

A good design can achieve a balance between accuracy and performance with a little _________________ at the end of the design process.

What is a flat file?

A large table containing a lot of information

one-to-many

A many-to-many relationship can be resolved into a resolution entity with two _______________ relationships for clarity.

Design mode

A method of interacting with the database directly.

unique constraint

A one-to-one relationship needs both a foreign key and a ______________ on the foreign key for physical implementation.

trigger

A program code that fires when a specific action occurs.

Constraint

A set of allowable values for one or more attributes.

All of the following are relationship types except:

A. 1-1 B. 1-M C. 2-2 D. M-N Answer: C

Columns without _________ are considered null.

A. Attributes B. Data C. Operators D. Row Answer: B

Which is a disadvantage of the DBMS?

A. Complexity B. Cost of conversion C. A & B D. None of the above Answer: C

Connecting two pieces of text is called _________/

A. Concatenation B. Null value C. String D. Union Answer: A

Which of the following is an advantage of the DBMS?

A. Control of data redundancy B. Sharing of data C. Data consistency D. All of the above Answer: D

This represents an object or an activity:

A. DML B. Attribute C. Primary Key D. None of the above

Who is responsible for the physical design of the database as well as its security and integrity control?

A. End User B. Application programmers C. Database Administrator D. Database Designer Answer: C

This is not a building block of the relational database:

A. Entity B. Attribute C. Entity set D. None of the above Answer: C

Which types of constraints ensure that each row in a table is unique? Select two

A. Foreign key B. Primary key C. Unique key D. Check constraint Answer: B & D

The following are all relational keys except:

A. Foreign keys B. Secondary keys C. Primary keys D. Tertiary keys Answer: D

The four DML basic operations are:

A. Insert, delete, update, select B. Delete, join, update, insert C. Union, update, select, add D. All of the above Answer: A

How does defining constraints help in designing databases?

A. It increases security B. It reduces human error C. It simplifies data input D. It improves database accuracy Answer: B

Which is not an entity?

A. Jack Hammond B. Lancaster C. Thaddeus Stevens College of Technology D. Sophomore students Answer: A

In a client-server environment which of the following is not a function of the client:

A. Manages the user interface B. Accepts and checks the syntax of the user input C. Passes response back to the user D. All of the above Answer: D

Which is not part of the system catelogue?

A. Names of network B. Names of authorized users C. Names of relationships D. Names, types and sizes of data items Answer: A

All except __________ is a major component of a DBMS:

A. Query processor B. DBMS Checker C. Database manager D. File Manager Answer: D

To ensure that proper values are entered into a database, one could use:

A. Roles B. Privileges C. Constraints D. SQL Answer: C

Which is another term for record?

A. Row B. Tuple C. Relation D. Both A & B E. A, B and C are terms for records Answer: D

All of the following are characteristics of relations except?

A. The relation has a name that is distinct from all other relation names B. Each attribute has a distinct name C. The order of the attributes is very important D. Each cell in the relation contains one atomic value Answer: C

Where will the names of authorized users of the database be stored?

A. User Table B. System Catalog C. Master Database D. None of the above Answer: B

Some of the functions of a DBMS are:

A. Voice recognition B. Transaction support C. Data storage, retrieval and update D. A user-accessible catalog Answer: All 4

Which of the following is a valid condition?

A. WHERE 'Smith' B. WHERE Job_description = 'Manager' C. WHERE salary '>10000' D. WHERE LAST_NAME BETWEEN 'K' AND '9' Answer: B

The number of users that are allowed to access a database simultaneously is _________.

A. fixed B. dependent on the database design C. Unlimited D. One Billion Answer: B

Temporary table takes place on client Reduces network traffic Operations can be done on a specific table Data can be loaded in temporary table before modifying it

Advantages of temporary tables

Concurrency

Allowing Many users to access the same file (database object) simultaneously.

transaction-initiating

An SQL transaction automatically begins with a ______________________ SQL statement.

What are the major characteristics of a DBMS

An integrity system A data manipulation language A data definition language A concurrency system

software application

Anomalies usually have to be caught and corrected in the ____________ that uses the database

Data quality is ensured by using ________________.

Constraints

Columns without __________ are considered null.

Data

SQL is an industry-standard language for querying ______________.

Databases

The number of users that are allowed to access a database simultaneously is _____________.

Dependent on the database design

What are the 3 major limitations of a file based system?

Duplication of data Incompatibility of Files Separation and isolation of data

Use # prefixed to the name of the table

How do you set up a temporary table?

three

If a database is not normalized at least to ____________________ normal forms, then there are chances of incomplete data insertions.

saved state, resubmitted

If a transaction fails during execution, then the database will not be in a ____________. All the changes that had been made must be ___________.

fourth

Implementation of the ________________ and fifth normal forms depend on whether the business rules require them.

Table

In a database, information about a single category of items is always stored in the form of a ______________.

What is the most important thing to remember about information contained in tables?

It is information about a single category of items

Normalized

It is necessary for a database to be ________________ to provide greater accuracy while editing data.

Read-only

Most data warehouses are designed to be _______________ and therefor do not need normalization.

parent

One of the most common forms of denormalization in a database is to include summary data in the _________________ entity.

multivalued

One single column or attribute group that repeats in the database is sometimes called a ___________________ attribute.

Relationships

Records in one table can be connected to relevant records in other tables by defining ____________________.

Enforce data integrity Consistent implementation of complex business rules and constraints Modular design Maintainability reduced network traffic Faster execution Security enforcement

Role of stored procedures in DB apps

COMMIT, ROLLBACK

SQL defines transaction model based on _______ and __________.

attributes

Some anomalies occur due to incorrect use of optional relationships and _________________, as well as from redundant data.

Insert, update, and delete

The 3 different kinds of triggers

COUNT, SUM, AVG, MIN, MAX

The 5 main aggregate functions

Subselect

The _________ is known as the defining query.

null

The columns in the foreign key, in an optional relationship, can have _________ values.

View

The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessarily exist in the database but can be produced upon request by a particular user at the time of request.

Redundancy

The efficiency and accuracy of a database can be ensured by eliminating ______________________ of data.

Degree

The number of attributes in a relation.

deletion anomalies

The result of deleting data, in a way that other data is also lost, is called ________________.

insert anomaly

The result of entering incomplete data into a database is a(n) __________________.

normal forms

The rules that ensure normalization of databases are referred to as ___________________.

key

The rules to apply the first three normal forms are summarized as "Each attribute must be a fact about the whole_________________."

database

The shared collection of logically related data and it's description, designed to meet the information needs of an organization.

Data mode

The sub-language that is used to specify the database schema.

User-defined System Extended Temporary Global Temporary Remote CLR (Common Runtime Language) - handles execution of managed application code

Types of stored procedures

redundant data

Update anomalies generally occur when there is __________________.

Automates repetitive tasks Server generates and maintains an execution Execution plan is stored as part of database, when used there is no need for the system to regenerate execution plan. (Executes efficiently) Ability to grant permission for execution

What are the advantages of stored procedures?

Data independence Currency Improved security Reduced complexity Convenience Customization Data integrity

What are the advantages of views?

Hardware (PCs, servers, etc.) Software (DBMS operating system, network operating system and any application programs) Data Procedures (Instructions and rules that should be applied to the design and use of the database and DBMS) People

What are the components of the DBMS environment?

Boolean Character Bit Exact Numeric Approx. Numeric Datetime Interval Large Object

What are the different SQL data types?

Required data Domain constraints Entity integrity Referential integrity General constraints

What are the different types of integrity constraints?

Update restriction Structure restriction Performance

What are the disadvantages of views?

Max name length is 128 characters May contain up to 2100 input/output characters Max size of body is 128 MB.

What are the limits of stored procedures?

Domain

What defines what kind of value an attribute can contain?

CHAR data has a fixed length, and when information doesn't meet the length requirement blank are added to the right of the input to match the requirement. VARCHAR can have varying length strings, which uses less space.

What is the difference between CHAR and VARCHAR?

Buffer to hold intermediate table data in a PL/SQL statement Held in memory until it disconnects from the server Write transaction does not apply Generally accessible only to the user who created it

What reasons are there for having a temporary table?

entity

When the first normal form rule is applied, each repeating group of attributes is separated into another _______________.

size

While choosing a database engine, the ______________ of the database is the deciding factor.

View resolution

With ______________, any operations on view are automatically translated into operations on relations from which it is derived.

View materialization

With _______________, the view is stored as a temporary table, which is maintained as the underlying base tables are updated.

Database

______________ integrity is a constraint that states that in a base relation no attribute of a primary key can be null.

Tuple

a row in a table that contains data about a specific item in a database table

Why is a relational database so called?

it relates to records

In a database with a large number of users, privileges are grouped in the form of _________ to assign a set of privileges to a certain category of users.

roles