Preview

13 - More on ERM and Indexing

 1. Read the following excerpt on ERM and fill in the blanks
An entity–relationship model (ER model for short) describes 
interrelated things of interest in a specific domain of knowledge. 

A basic ER model is composed of ___________ (which classify the things
 of interest) and specifies relationships that can exist between
 instances of those ______________

  row and column fields

  entity types

  field types

  primary key types

 2. Entity Relationship Modeling (ER Modeling) is a _______________________________. It uses Entity/Relationship to represent real world objects

  binary tree related approach to design

  textual and written text approach to database design

  minimalistic and sparse approach to database design

  graphical approach to database design

 3. An employee of an organization is an _________. If "Peter" is a programmer (an employee) at Microsoft, he can have __________(properties) like name, age, weight, height, etc. It is obvious that those do hold values relevant to him.

  attribute / method

  entity / attributes

  model / properties

  attribute / entities

 4. In ER diagrams, rectangles are used to denote:

  individual columns

  entity types

  up to 10 rows

  models

 5. Entities can not have relationships with each other. They can only have relationships with properties

  TRUE

  FALSE

 6. In Entity Relationship Modeling, we model entities, their attributes and relationships among entities.
ER Modelling helps provide a conceptual view of the database. Designers can use this to think about their implementation and also notice errors early on in the process

  TRUE

  FALSE

 7. In a university database we might have entities like:

  student IDs, student names and university name

  lecturer name, student name, university name

  course name, course ID, course teacher

  students, lecturers, modules

 8. Analyse the following 'Flight database' ER diagram. Which of the following statements is true?
flight_database_ERM.png

  A passenger has various attributes/properties including: Flight, airplane and Flight No.

  A Flight cannot be uniquely identified by its FlightNumber alone, so we could not use the flight number as the primary key

  Several passengers can book several seats on several flights.

  An airplane can be involved in any number of flights, while each flight uses exactly one airplane

 9. Fill in the blanks for the third type of relationship
One to one (1:1)
=================
Each lecturer has a
unique office


One to many (1:M)
=================
A lecturer may tutor
many students, but each
student has just one
tutor

??????????????????
=================
Each student takes
several modules, and
each module is taken by
several students

  many to one

  one to many and many to one (both)

  one to one

  many to many (m:m)

 10. 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

  models

  tables

  rows

 11. Consider an index at the back of a book. The index entries consist of the ______________________________________
Note: The keys would usually be in alphabetical order, which makes really easy for us to scan the index, find an entry, note the pages, and then flip the book to the correct pages

  key and page number

  row and column of each key

  book title and name

  field and field index

 12. The attribute name could be structured as an attribute consisting of first name, middle initial, and last name. This type of attribute is called ______________

  a foreign key

  a composite attribute

  a primary attribute

  a secondary field

 13. In the following example, 'Name' is a __________________ and 'Address' is a ___________________. The entity here is __________________.
ERM_example_2_simple_composite.png

  simple attribute / composite attribute / entity

  composite attribute / simple attribute / entity

  entity 1 / entity 2 / entity 3

  one to one attribute / many to many attribute / entity

 14. The power of the index is that is allows you to more or less __________ access the book’s pages you’re interested in seeing. Practically speaking, this saves hours of page flipping

  automatically

  directly

  manually

  indirectly

 15. A table can never 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

 16. The keys are a fancy term for the values we want to look up in the index. The keys are typically based on the tables’ _____

  columns

  relationships

  notes

  rows

 17. By comparing keys to the ______ it is possible to find one or more database records with the same value

  index

  foriegn key

  first field

  primary key

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

  slows down

  speeds up

  duplicates

  caches

 19. Missing indexes may not be noticed for small databases, but once your tables _____________________________________________

  grow in size, queries will be extra fast to execute

  shrink in size, queries will take longer

  grow in size, queries will take much longer

  shrink in size, queries will fail to execute

 20. Read through the following excerpt and fill in the blanks for the option that would help reduce the average flip and find time.
Consider that you have a deck of 52 cards: four suits, 
Ace through King.  If the deck is shuffled into random 
order, and I asked you to pick out the 8 of hearts, 
to do so you would individually flip through each card 
until you found it.  

On average you would have to go through half the deck, 
which is 26 cards

You could seperate the cards into ________________
___________________________________________________.

Now, to pick out the 8 of 
hearts you would first select the hearts pile, which 
would take on average two to find, and then flip through 
13 cards. This is the power of an index.  By segregating and sorting our 
data on keys, we can use a piling method to drastically reduce
 the number of flips it takes to find our data.
card_sorting_index.png

  three piles -randomly shuffled

  eight different piles, randomly shuffled

  two piles by number (e.g. 1 to 9 and then Jack, Queen, King)

  four piles by suit, each pile randomly shuffled

 21. The structure that is used to store a database index is called a___________. A __________ works similar to the card sorting strategy

  C+ Tree

  B+ Tree

  B+ Sorting Algorithm

  A+ Graph

 22. In a B+ Tree the key values are separated into many smaller piles. As the name implies, the piles, technically called nodes, are connected in tree like fashion

  TRUE

  FALSE

 23. An index is any data structure that _______________________________________

  An index is any data structure that improves the performance of lookup

  

  

  

 24. To find the Name for ID 13, an index on (ID) is useful, but the record must still be read to get the Name. However, an index on _____________ contains the required data field and eliminates the need to look up the record
index_example1.png

  (13, ID)

  (ID, Name)

  (Name)

  (Name, 13)

 25. Indexes are useful for slowing down data access and therefore making the search procedure more stable

  FALSE

  TRUE