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

  primary key

  foreign attribute

  first row

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



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


  Unique Pupil Number

  Tutor Group

  first name

 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!

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

  Two pupils cannot have the same unique pupil number

  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 2

  Only Method 4

  Method 3 and 4

  Method 1 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)



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



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



 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 records

  one or more tables

  one or more columns

  one or more keys

 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.



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

  any errors

  one or more database records with the same value.

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

  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.


  slows down

  speeds up


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

  small / shrink

  small / grow

  big / grow

  big / 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

  The power of a database index!

  That databases work better without complex or composite indexes

 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

  Cardinal Tree

  A+ 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

  storage space

  indexing speed


  data structure integration

 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.



 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.

  All of them are valid

  Only the first one is a valid reason

  2 and 3

  None of them

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

  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.
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);

  Composite Index

  B-Type Index

  Finite index

  Foreign Index