Preview

02 - ERM and Indexing

 1. The _____________ uniquely identifies each record in the table. It can either be a normal field that is guaranteed to be unique or it can be generated by the DBMS

  key column

  foreign attribute

  primary key

  first row

 2. It is not possible for a primary key to consist of multiple fields in combination

  TRUE

  FALSE

 3. What is the primary key in the Pupil table?
ermIndexing_1.png

  Surname

  first name

  Tutor Group

  Unique Pupil Number

 4. What is a reason for selecting the above answer and key as the primary key?
Note: You have to have got the previous answer correct to get this follow on question right!

  Two pupils cannot have the same unique pupil number

  All of the listed options are valid as reasons for why this was selected as the primary key

  It is a unique identifier

  Pupils may have the same first name and/or last name, but not pupil number

 5. Which two ways could be used for the teacher to link the Pupil details to the Class details?
1. ClassID could be included as a foreign key in the Pupil table.

2. A third table (Pupil_Class) could be created with UniquePupil Number 
and ClassID as the two fields.

3. The Class table could be deleted and replaced by a duplicate Pupil table.
The duplicate table could include an additional Class ID

4. First name and Surname could be made into a composite primary key and added
into the Pupil table as a foreign key. 

  Method 1 and 4

  Only Method 4

  Method 1 and 2

  Method 3 and 4

 6. The following is defining a 'composite key': A primary key that involves more than one attribute (e.g. may be needed where the names of people are being stored without an ID field)

  TRUE

  FALSE

 7. A foreign key is basically a primary key that is stored in another table.

  TRUE

  FALSE

 8. Only flat file databases, and not relational databases, tend to use foreign keys

  FALSE

  TRUE

 9. A database index allows a query to efficiently retrieve data from a database. Indexes are related to specific tables and consist of _________________

  one or more keys

  one or more records

  one or more columns

  one or more tables

 10. A table can have more than one index built from it. The keys are a fancy term for the values we want to look up in the index.

  True

  False

 11. The keys are based on the tables’ columns. By comparing keys to the index it is possible to find ___________________

  one or more database records with the same value.

  any values that have no content (e.g. NULL records)

  any errors

  database primary keys that are not unique

 12. Since an index drastically _________ data retrieval, it is essential the correct indexes are defined for each table.

  speeds up

  constricts

  bottlenecks

  slows down

 13. Missing indexes may not be noticed for _____databases, but rest assured, once your tables ____ in size, queries will take much longer.

  small / grow

  big / shrink

  big / grow

  small / shrink

 14. A real database consultant wrote the following excerpt. What is he trying to demonstrate by telling this story?
I was once working on a database where a series of operations took
 about eight days to complete.  By looking at the longest running 
queries and running them through a query plan generator we realized 
the database could benefit from a new index.  The optimizer estimated 
the query cost would drop from 300,000 operations to 30!  We implemented
 the index and took the entire operation from eight days to two hours.  
Needless to say, we were very happy to get the performance boost.

  That, where possible, a simple query, rather than a complex index, would be preferred

  That it is better not to use database indexes if at all possible

  That databases work better without complex or composite indexes

  The power of a database index!

 15. The structure that is used to store a database index is called a ______. In a ______ the key values are separated into many smaller piles

  Graphical Tree

  A+ Tree

  Cardinal Tree

  B+ Tree

 16. A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional _____________ to maintain the index data structure

  indexing speed

  data structure integration

  trauma

  storage space

 17. Most database software includes indexing technology that enables sub-linear time lookup to improve performance, as linear search is inefficient for large databases.
Note: The users cannot see the indexes, they are just used to speed up searches/queries.

  FALSE

  TRUE

 18. Which of the following guidelines indicate when the use of an index should be reconsidered. (or perhaps not used)
Indexes should not be used on small tables.

Tables that have frequent, large batch updates or insert operations.

Indexes should not be used on columns that contain a high number of NULL values.

Columns that are frequently manipulated should not be indexed.

  Only the first one is a valid reason

  All of them are valid

  None of them

  2 and 3

 19. In SQL, The _____________statement is used to create indexes in tables.

  MOVE INDEX

  INDEX A

  CREATE INDEX

  1 INDEX + ()

 20. An index in a database is very similar to an index in the back of a book. Fill in the blanks for this excerpt that shows the different types and SQL
1 - Single-Column Indexes
=====================
A single-column index is created based on only one table column. 
The basic syntax is as follows.
CREATE INDEX index_name
ON table_name (column_name);

2 - Unique Indexes
======================
Unique indexes are used not only for performance, but also for data integrity. 
A unique index does not allow any duplicate values to be inserted into the table.
 The basic syntax is as follows.
CREATE UNIQUE INDEX index_name
on table_name (column_name);

3 - ????????????????????
========================
A___________is an index on two or more columns of a table. 
Its basic syntax is as follows.
CREATE INDEX index_name
on table_name (column1, column2);

  B-Type Index

  Foreign Index

  Finite index

  Composite Index