Tables and Queries in RDBMS

• This is tougher than other products, please read the associated text part of the chapter.
• In this chapter, we will be working with a database that is related to banking
  • A bank auditor has found mishandled funds at a bank.
  • They want to build a database that allows them to explore
    • Customers
    • Accounts
    • Branches
• Remember a table contains information about a collection of entities.
  • A row in the table is one entity.
  • It should be cohesive or contain only information about the entity
  • It should not contain repeated data, that goes in another table.
  • Tables are linked by relations (or fields in two different tables employee-id for example.)
• In the scenario there are three different entities
  • Customers, probably a basic entity
  • Branches, again probably a basic entity
  • Accounts, probably more complex, link customers to branches
• These entities probably represent tables in the final database.
  • Start your design by listing all the tables you see.
  • Don't worry if you don't have everything at this point, we need a place for all of the data we will be working with.
• For each table, list the data associated with an item in the table.
  • For customers, we probably have
    • Name, first and last
    • An address
      • Street
      • City
      • State
      • ZIP
    • A phone
    • And probably a fake field, the customer ID.
      • This will be unique to each customer
      • So it can serve as the primary key
      • And will identify the customer
      • Even if they have the same name or address.
      • It is called a surrogate key because it is sort of fake
      • This will be used as in a relation when we need to link a customer to anything.
    • 
  • For a Branch we will probably have
    • A branch ID, which will be a primary key for branch, see discussion above.
    • A Manager name, This will be just a name in our example, but would probably be an employ id, which would be a key into an employee table in a bigger database.
    • Location, in this example it is just a word, but could be an entire address.
    • StartDate : this is undefined but used in the discussion
  • 
  • An account will then be:
    • A unique account id, the primary key
    • A customer id
      • This must match an id (and customer) in the customer table.
      • It is called a foreign key in this table because it refers to a key in another table.
      • We will not add an account unless we have a customer in the customer table that will own that account.
      • This will form the basis of a relation.
    • A branch id: see discussion for customer id above.
    • A Open date, the date the account was opened.
  • 
  • 
  • We might ask:
    • if we know a customer id, can we find all the accounts the customer has?
    • Yes, just search the account table for all accounts for a customer id that matches that of the given customer.
    • If we know an account number, can we find the corresponding customer
    • Yes, just use the customer ID from the accounts table to find the customer in the customer table.
    • If we know an account number, can we find the corresponding Branch?
    • Yes, same answer.
    • Can we find if a customer has multiple accounts at a branch or across several branches (Given Customer ID, can we find related branch IDs?)
• In general you want to design tables that
  • Include all required data.
  • Link using common fields
  • Store data in the smallest units.
  • Are not redundant.
• Creating and Modifying Tables
  • We tend to use CamelCase for naming things
    • Multiple words that describe what is being stored.
    • Start each word with an upper case to make it easier to read.
  • They give us a list of data types and where we might use them in the book.
    • You need to select the type that is closest to your fields when designing the table.
    • But you need the type that supports any operations on that field.
  • Every table will need a primary key.
    • These will need to be unique.
    • Auto Number is a good choice for a surrogate key.
  • Use foreign keys when required.
    • You will need to match the type of the key in the other table.
• In the first hands on exercise, you will create the branch table.
• In the second hands on exercise
  • You will import two more tables, one from excel, one from another database.
  • You will format fields
  • You will establish relations between tables.
  • You will see a referential Integrity Check in action, it will not allow you to enter an invalid branch.
  • You will learn about many to one and one to many relationships.
    • An account will have only one customer.
    • But a customer may have many accounts.
    • Same for accounts and Branches.
  • Try adding
    • A line to Accounts with an invalid customer id
    • A line to Accounts with an invalid branch id
    • A line to Accounts with a duplicate Account ID
• In the third exercise, you will create a query on a single table.
  • This is reasonably straight forward.
  • You will save this query so it can be run again easily.
  • You could (sort of ) do this in excel.
• In the final exercise, you will create a multi table query
  • Just a little more complex but not bad.
  • This demonstrates the power of access.
  • You could not do this in excel.