DAIM

A table where all attributes are dependent on the primary key and are independent of each other, and no row contains two or more multivalued facts about an entity, is said to be in ____. 1NF 2NF 3NF 4NF

1NF

What type of NF are based on functional dependency?

1NF, 2NF, 3NF, BCNF

A table that is in 1NF and includes no partial dependencies is said to be in: 4NF BCNF 2NF 3NF

2NF

What is the type of NF, if the table has previous NF and each attribute functionally depends on all key, not only on part of the key? 1NF 3NF DKNF 2NF BCNF

2NF

When a part of a key determines a non key, it is a violation of ? All of the above 1NF BCNF 2NF

2NF

A table in＿ contains no transitive dependencies. 1NF 2NF 3NF none of the above

3NF

Tables in ____ will perform suitably in business transactional databases. answer choices 0NF 1NF 2NF 3NF

3NF

What is the type of NF, if the table has previous NF and each not key attribute functionally depends on only Primary Key? 1NF 3NF DKNF 2NF BCNF

3NF

Which of the following NF has no transitive functional dependencies? DKNF 1NF 3NF BCNF 2NF

3NF

What is the type of NF, if the table is in previous NF and doesn't have multi-value dependencies? DCNF BCNF 4NF 5NF There is no such NF

4NF

What is an atomic field? A field that contains multiple items of data A field that is repeated A field that contains only one item of data

A field that contains only one item of data

What is a foreign key? A unique identifier in a database A primary key of one table that appears in another table A field that should not be in a table and needs removing

A primary key of one table that appears in another table

What is the model database used for? A test database to test changes to your other database A template for creating all new databases A container for server - wide information A temporary working storage area None of the above

A template for creating all new databases

When is a trigger fired? When the trigger fire statement is executed. Before data modification. Before constraint validation. After the transaction completes. After constraint validation but before the transaction commits.

After constraint validation but before the transaction commits.

In case of a SELECT statement which includes a WHERE clause, where is the GROUP BY clause statement placed?

After the WHERE clause

Normal forms can be used with Relational data model All of the above UML Entity Relationship Model

All of the above

What method can you use to avoid a deadlock?

All of the above

What should you do before backing up a database? Decide where you are backing it up to Determine dump frequency Define the device on which you are backing it up All these actions Decide how often to back it up

All these actions

What can you do while a database is being backed up? (mult) Anything that doesn't affect the log All normal processing All transactions, except non-logged operations Quieres only Absolutely nothing. All users are blocked during a backup

All transactions, except non-logged operations

Which of the following is an attribute eof a key that could have been a primary key but wasn't chosen by it? Foreign Possible /candidate/ Composite No such attribute Alternative

Alternative

The command you can use to see the script of existing userdefined stored procedure: Sp_help Sp_dboption Sp_helptext Sp_helptext proc_name Sp_help proc_name

Sp_help proc_name

Result of sampling a subset from the high-level entity set to form a lower level entity set is: Generalization Denormalization Functional dependence Normalization Specification

Specification

A table is in 2NF if the table is in 1NF and what other conditions is met? There are no functional dependencies. There are no null values in primary key fields. There are no attributes that a not functionally dependent on the relation's primary key. There are no repeating groups.

There are no attributes that a not functionally dependent on the relation's primary key.

A table is in 2NF if the table is in 1NF and what other condition is met? There are no functional dependencies. There are no null values in primary key fields. There are no repeating groups. There are no attributes that are not functionally dependent on the relation's primary key.

There are no attributes that are not functionally dependent on the relation's primary key.

A view can be used: (check all the correct answers) To simplify code As horizontal security As vertical security To hide data To hide underlying database changes

To simplify code As horizontal security As vertical security To hide data To hide underlying database changes

Which of the following means the entity property? Scheme E/R Subtype Attribute Supertype

Attribute

Which of the following are necessary for the communication compatibility between the two tables? Attributes with different data types Attributes with the same data type Attributes with the same data Attributes with unique data

Attributes with the same data type

In which type of below normal forms each determinant in relation is possible key? 1NF 2NF 3NF BCNF 4NF

BCNF

What is the type of NF, when and only when the determinant in the table is possible key? DKNF 3NF 2NF 1NF BCNF

BCNF

What type of NF aren't based on functional dependency? 2NF, 3NF 5NF, DKNF 1NF BCNF, 4NF All NF

BCNF, 4NF

What functional dependency requires for functional definition of the other attributes? Doesn't require the attribute to be the key Requires the attribute not to be the key Doesn't require the attribute not to be the key There is no any relation between key and functional dependency Requires the attribute to be the key

Doesn't require the attribute to be the key

Which of these objects is a set of values, based on which one or more attributes are retrieve their actual values? Domain Supertype Relation Entity

Domain

Which command(-s) provide stored procedure execution? (mult) F5 sp_help sp_dboption EXECUTE proc_name EXEC proc_name proc_name

EXECUTE proc_name EXEC proc_name

To ensure good quality of the database design you should not adhere to one of the rules: Each table must have a unique name Each table should store data of different entity types Avoid repeating values or column Avoid the NULL column Each table should store data of one entity type

Each table should store data of one entity type

What is the purpose of normalization?(mult) Eliminate redundant data Produce redundant data Reduce data inconsistencies Increase data inconsistencies

Eliminate redundant data Reduce data inconsistencies

In client/server architecture, what is the normal purpose of a server? (Check all the correct answers) Provide the end user with automatic corrections of misspelled words as they are entered Ensure data integrity Formatting data for display to the end user Control the graphical user Handle data storage

Ensure data integrity Handle data storage

When is a table is said to be 3NF :

If and only if it is in 2NF and every non key attribute is Non-transitively dependent on the primary key

Where is information pertaining to the backup stored? (check all the correct answers) In the master database In a flat file In the msdb database In the NT Registry None of the above

In the msdb database

Normalization is: Organising a database to remove repeated entries and increase the accuracy of data Removing all necessary data from a database Putting fields from different tables into one big database

Organising a database to remove repeated entries and increase the accuracy of data

Normalization is: Removing all necessary data from a database Organising a database to remove repeated entries and increase the accuracy of the data Putting fields from different tables into one big database

Organising a database to remove repeated entries and increase the accuracy of the data

Which model is an extension of the relational model and removes restrictions of indivisibility of the data stored in the records in the tables? Object-relational Post-relational Relational Network Multivariate

Post-relational

Some attributes (or set of attributes) that uniquely identifies a row in the table is the key: Foreign Primary Alternative Possible Composite

Primary

What gives the result of the combination of entities? Relation Attribute Supertype E/R scheme

Relation

Which of the data models is the most understandable to the customer and convenient for designing a database from scratch? Post-relational Network Object-oriented Relational Multivariate

Relational

What does the TRUNCATE statement do? Removes all rows from a table Removes the table Removes all columns from a table Shortens the table to 10 rows

Removes all rows from a table

A relation is in 1NF if it doesn't contain any _______? Repeating groups Determinants Null values in primary key fields Functional dependencies

Repeating groups

If Paula is a member of the user-defined database groups Sales and Accounting, which of the following SQL batches will allow her to have SELECT permissions on the table MyStuff? (Check all correct answers) Grant all on MyStuff to sales Grant all on MyStuff to accounting. Revoke select on MyStuff to accounting Revoke select on MyStuff to sales. Grant all on MyStuff to sales Grant all on d2 to sales. Deny select on d2 to accounting Deny select on d2 to accounting. Grant all on d2 to sales

Revoke select on MyStuff to sales. Grant all on MyStuff to sales

What has to be running in order for automated administration to work? (check all the correct answers) :

SQL Server SQL Server agent

Choose right answer. Which database property with TRUE value allows select and coping of the data blocks? Offline Select into/bulkcopy Single user Read only Dbo use only

Select into/bulkcopy

Which of the following statements are true? (check all the correct answers) You can restore only from a full database dump Restoring requires a database that is not in use The msdb database contains data about the backups You can read the dumped headers without actually restoring the full backup The backup command can be used interchangeably with the load command

You can restore only from a full database dump Restoring requires a database that is not in use The msdb database contains data about the backups You can read the dumped headers without actually restoring the full backup The backup command can be used interchangeably with the load command

What is the object which is not grantable: application role login on the SQL server level login on the database level endpoint table on the scheme level

application role

A ____ key is an artificial PK introduced by the designer with the purpose of simplifying the assignment of primary keys to tables. surrogate composite candidate foreign

composite

The Conflicts Between Design Efficiency, Information Requirements, And Processing Speed Are Often Resolved Through ____. conversion from 2NF to 3NF conversion from 1NF to 2NF conversion from 3NF to 4NF compromises that include denormalization

compromises that include denormalization

The operator creates all possible combinations of tuples, taken one by one from each of the two relations: union selection cross join intersection left, right, full join

cross join

Attribute A _____ attribute B if all of the rows in the table that agree in value for attribute A also agree in value for attribute B. owns determines derives from controls

determines

The normalization process involves assigning ____ to tables based on the concept of determination. data files attributes relations

files

The opposite method for specification in ER is: normalization generalization denormalization identification simplification

generalization

Identification of the ____ will let you know where you are in the normalization process. normal form primary key attributes repeating groups

normal form

Logical database design is:

normalization and E/R

When designing a database you should ____. make sure entities are in normal form before table structures are created create table structures then normalize the database only normalize the database when performance problems occur consider more important issues such as performance before normalizing

only normalize the database when performance problems occur

__________ specifies that a non-prime attribute is functionally dependent on part of a candidate key. transitive dependency partial dependency repeating group atomic attribute

partial dependency

Choose the right answer. Stored procedure: cannot be definitely called or executed cannot retrieve data to user reduces network congestion prevents incorrect, unauthorized and uncoordinated changes in database is started automatically each time when any DML or DDL operator is started

reduces network congestion

"Entity -Relationship" scheme is an example of a data model: object-oriented multivariate post-relational object-relational relational

relational

The ____ model views the data as part of a table or collection of tables in which all key values must be identified. relational object-oriented conceptual external

relational

A _____ derives its name from the fact that a collection of multiple entries of the same type can exist for any single key attribute occurrence. repeating group transitive dependency partial dependency primary key

repeating group

The operator selects the sample tuples or rows from relation, based on the condition: selection intersection cross join division union

selection

The existence in the database of the procedure is checked using command: sp_helptext sp_help proc_name sp_helptext proc_name sp_help

sp_help proc_name

To find information about all files used id the current database, you would use which of the following SQL statements?

sp_helpfile

Replacing the optional attributes by subtypes is the method:

specification

Choose the right answer. A group or set of T-SQL instructions and data manipulation language which are saved by one name and are used ... whole, is: (mult) stored procedure view trigger cursor

stored procedure trigger

The operator generates relation of tuples belonging to the first, but absent in the second of the two relations: projection intersection subtraction (or exception) division cross join

subtraction (or exception)

Which stored procedures have the names started with "sp_"? They provide different administrative tasks and help in SQL Server management. They are stored in msdb database temporary system extended user-defined

system

Which stored procedures you can get access from different servers if the users have the necessary permissions? temporary system extend user-defined

system

Which stored procedures have the names, starting with "#" symbol and are deleted automatically by the connection termination of client with server? system temporary user-defined extended

temporary

Granularity refers to _____. the number of attributes represented in a table the level of detail represented by the values in a table's row the size of a table the number of rows in a table

the level of detail represented by the values in a table's row

What relational operator(-s) requires(-s) a common type of attributes of the two relationships? subtraction division cross join union projection

union

Which operator can collect the data of two tables into one table, provided that the number of columns is the same and the data types in these columns are the same? left, right, full join intersection cross join union selection

union

Which of the following statements fully describes data in 2NF:

When all repeating entries of data are removed and the fields in each table are directly related to the primary key and no fields are present that are not related to each other

If A attribute in relation to B attribute is functionally depends on it , what is the name of B attribute?

Determinant

Integrity control of the relationships is maintained automatically in the data model: Post-relational Multivariate Object-oriented Network Hierarchical

Hierarchical

M: M connection type is not available in the data model: Object-oriented Network Multivariate Post-relational Hierarchical

Hierarchical

Record - "Descendant" can't have many of records, "ancestors" in the data model: Post-relational Multivariate Object-oriented Network Hierarchical

Hierarchical

Which of the following are true about normalization? (Check all correct answers) Normalization is used to reduce the number of tables in a database Normalization can remove redundant data from the database Normalization can be used to speed up maintenance tasks Normalization simplifies select statements Most databases are in fifth normal form

Normalization can remove redundant data from the database Normalization can be used to speed up maintenance tasks

Which of these models is based not on records? Relational Network Object-oriented Hierarchical Multivariate

Object-oriented

When would you restore a database? When prompted to do so by server Wackly When it is required because a disaster ruined existing database During a database migration Daily

When it is required because a disaster ruined existing database During a database migration

When do you need to backup the transaction log? (mult) When it starts to get too big Never When you need to recover the space it is taking up Weekly When the database gets too big

When it starts to get too big When you need to recover the space it is taking up

Of the following normal forms,____, is mostly of theoretical interest 3NF 1NF BCNF DKNF

DKNF

What is the type of Normal Form, which is based on defining the keys and attribute domains? DKNF BCNF There is no such NF 2NF 3NF

DKNF

Choose the wrong answers. DML trigger runs each time when the data in the base table is changed by any operator: (mult) INSERT DROP DELETE UPDATE ALTER

DROP ALTER

Who is responsible for the design, maintenance and database security? Database manager Administrator User Database administrator Application developer

Database administrator

Which database maintenance tasks can be automated? Database backups Updating of index statistics Transaction log backups Index rebuilds None of these actions

Database backups Updating of index statistics Transaction log backups Index rebuilds

What is the last stage in relational database design? Conceptual design Deployment Database testing and maintenance Determine requirements

Database testing and maintenance

What user is owner of the database? It is automatically created for login, which created the database? Guest Public Dbo Sys INFORMATION_SCHEMA

Dbo

Choose right answer. Which database property with TRUE value allows only owner usage? Offline Select into/bulkcopy Single user Read only Dbo use only

Dbo use only

What is used for normalization reducing? E/R scheme Primary and foreign keys Decomposition without redundancy Bottom-up method Generalization

Decomposition without redundancy

Can a software design tool identify functional dependencies? Select one: True False

False

Normalization produces a lower normal form. True or False? True False

False

Which functional component controls matches in the database? File manager Administrator Database manager Database administrator Disk manager

File manager

This special user is for access rights granting to all logins, which doesn't have associated users in the database. This user doesn't have login permissions to the database by default; therefore it will not be working. This user is often used for permission granting to logins for some educational/test databases or read-only reference-databases: INFORMATION_SCHEMA Guest Public Dbo Sys

Guest

The operator creates a relation consisting of tuples belonging to both relations: Intersection Subtraction Union Selection Cross join

Intersection

How often should you issue the dump database command? It depends on the size of the database and the frequency of database changes Weekly Never Monthly Annually Daily

It depends on the size of the database and the frequency of database changes

Which of the following statements about this INSERT statement is true? (Check all correct answers) INSERT DISCOUNTS (store_id, lowqty, discount, discount_type) VALUES (6380, 10, 20, 'MyDiscount') It will insert one row into the table discount if the store_id 6380 exists in the stores table The insert will fail because store_id is a character column, and you are trying to insert an integer into it The column highqty will be set to NULL If a discounttype 'MyDiscount' already exists, the row will not be added None of the above

It will insert one row into the table discount if the store_id 6380 exists in the stores table The column highqty will be set to NULL

The operator forms the relation from two relations, taken in pairs from each relation and satisfied the condition: Union Selection Left, right, full join Cross join Intersection

Left, right, full join

When you normalize a relation by breaking it into two smaller relations, what must you do to maintain data integrity?(mult) Link the relations by a common field Remove any functional dependencies from both relations Assign both relations the same primary key field(s) Create a primary key(s) for the new relation

Link the relations by a common field Create a primary key(s) for the new relation

What is the relation type of functional dependencies? 1 : M M : 1 1 : 1 M : M There is no any relation

M : 1

In which system database all information about specific server configuration is stored, particularly about data or registered users, databases, configuration options of the system and remote servers: Master Model Msdb Purpose tempDB

Msdb

Which database(s) contains system tables in SQL Server?(mult) Model Msdb Tempdb Master

Msdb Master

What is the state which requires duplication to provide mutual independence of multi-value attributes? Concretization Multi-value independency Denormalization Generalization Multi-value dependency

Multi-value dependency

Which of data models is designed for online analytical processing? Object-relational Relational Object-oriented Post-relational Multivariate

Multivariate

The online database option performs which of the following It makes the database read - only It is used to make the database available for users None of the above It makes a complete file group read - only It is used to stop users from being able to access a database

None of the above

Which of the following stored procedures will add a new server - wide role

None of the above

Which of the following stored procedures will assign an application role to a user? Sp_addappmember Sp_addrolemember Sp_addsrvrolemember None of the above

None of the above

Which of the following statements is true: Normal forms can be derived by inspecting the data in various tables Normal forms speed up all queries Normal forms reduce the risk of inconsistent update of replicated data-items Normal forms completely eliminate all redundant storage of data-items

Normal forms reduce the risk of inconsistent update of replicated data-items

The scientific method of splitting complex table structures to simple table structures according to certain rules is: The concretization Normalization Generalization Denormalization The functional dependence

Normalization

The operator selects the attributes or columns from the relation Intersection Division Projection Subtraction Cross join

Projection

Which operator can split the data of one table into the data of another table? Subtraction Cross join Projection Division Intersection

Projection

What is not built-in user? Dbo Guest Public INFORMATION_SCHEMA Sys

Public

What is the reason of the anomalies insertion in the database? Concretization Functional dependency Redundancy Normalization Relations between entities

Redundancy

Which of the actions in the design of a relational database is not included in the stage of deployment? Creating of the Data Dictionary DBMS selection and purchase The transformation of the conceptual model to the real Testing and maintenance of the database

Testing and maintenance of the database

A foreign key constraint can be created provided that: (mult) No correct answers Not enough correct answers The constraint name is unique within the database It's referenced column(s) have a primary key or unique constraint The references table already exists

The constraint name is unique within the database It's referenced column(s) have a primary key or unique constraint The references table already exists

A disadvantage of normalisation is: The data loses its integrity as some of it is removed in the normalisation process The process of searching the database may be slower due to a higher demand on the central processing unit (CPU) Removing redundant data means that links cannot be created between tables

The data loses its integrity as some of it is removed in the normalisation process

Which method of selection the primary key isn't optimal: The primary key should not be changed with time Primary key must be meaningless The primary key must not be numeric The primary key must consist of a single column The primary key must be numeric

The primary key must not be numeric

An advantage of normalisation is: The storage space needed for a normalised database is likely to be smaller The process of normalising a database is very easy and doesn't require any skill It is possible to see previous details that have been changed, such as a customer's address, as we can look back at an older entry

The storage space needed for a normalised database is likely to be smaller

A 2NF table can exhibit _______ dependency. Partial Complete Transitive Indirect

Transitive

Choose the right answer. Part of program which consists of TSQL instructions, activated in response to definite actions, is Cursor View Stored procedure Trigger

Trigger

Choose the right answer. Type of stored procedure which is automatically called in the database: Trigger Table View Stored procedure

Trigger

Choose the right answer: Type of stored procedure which automatically called definite cases in the database Table procedure Trigger

Trigger

A table is in BCNF if every determinant in the table is a candidate key. True or False? True False

True

How long does a trigger persist in a database? Until the session is terminated. Until it is dropped. Until a new trigger is created without the append option. Until another trigger replaces it. None of the above.

Until it is dropped. Until a new trigger is created without the append option. Until another trigger replaces it.

Which of the following options can be used to remove an installation of SQL Server

Use the Uninstall option from the SQL Server 7 menu Use the Add/Remove Services application in the Control Panel Use the sql70rem.exe to perform an unattended uninstallation

Who is the primary user of the database or who is the database intended for? Database manager User Database administrator Application developer Database developer

User

Choose the right answer. Virtual table which gives the access to column subset from one or more tables, is: Cursor View Stored procedure Trigger

View