Introduction to Databases

• A Database is an organized collection of information
• Please be aware, this is a more complex subject that the previous two.
• And if you like this, let's talk about CSCI 313 Intro to Database.
  • Normally organized in a series of tables
  • Each table contains records about an individual item.
  • These records are made up of fields
  • Tables are composed of records
  • Connected by relations
  • Information is displayed by running a query
• Microsoft Access is a Database Management System (DBMS)
• But it is sometimes called a database
• Data stored in a DBMS is a database
• The information we looked at in chapter 4 of Excel could be considered a simple one table database.
  • The fields were cells.
  • The records were rows.
  • Attributes were columns.
• A database is really useful when there is more than one table.
• I will be using a01b2NWind.accdb from your data set for the following discussion
  • This represents the food distributor discussed in the chapter.
  • This is a Microsoft example database from the past.
• In this database
  • There is a table of Employees
    • This contains information about every employee
    • Each employee record is composed of a number of fields.
    • 
    • There is one record per employee
    • 
    • Notice how this interface looks like a table in a worksheet
    • Inside of the Employee table there are a number of fields
      • Everything in the City field is a city
      • Everything is the Last name field is a last name.
      • Notice that some Region fields are empty, this is OK.
      • But nothing "strange" is stuck in a field.
        • For example, We don't stick a cell phone number in the Extension field.
    • All of the information in this table is about employees
    • All of the information on an employee is stored in this table.
  • There are other tables in this database
    • The Customer table contains information about customers.
    • The Shippers table contains information about shippers
    • The Suppliers table contains information about suppliers.
  • There are some more complex tables in the database
    • The Product table contains product information
    • But in needs a sub-table Categories
    • Notice that the Product table contains a Category ID,
    • 
    • which matches the Category ID in the Categorytable.
    • 
    • The CategoryID in the Category table is known as a Primary Key
      • This one field uniquely identifies which category an item is in.
      • In the Product table it is know as a Foreign Key because it tells us which field in the category table to use.
    • This is an example of a Relation
    • 
      • In a relation a record in one table needs information from another table to fully represent an entity.
      • In this case, Products are in exactly one category.
      • But we don't want to record the category information over and over again in the product table.
      • So we put the information that would be repeated in the category table.
      • And reference this in the product table.
      • We do this because database people really like to reduce or even eliminate duplicate information.
      • Removing duplicate data from one table is know as normalization
    • This is also the case for the product table and suppliers
      • Each product is supplied by exactly one supplier
    • Orders consist of four different relations across five tables.
      • There is the basic information about an order in the order table
      • Each order has a customer
      • Each order has an employee
      • Each order has a shipper
      • And each order has one or more order items.
        • This last one is a little obtuse, we may talk about it later.
        • But it is really important to database people.
    • 
  • Databases do several good things for us when entering data.
    • They can require data be present
    • They can require data to be in a given range.
    • They can require that a new entity can not be created without a corresponding field in another table.
      • You can not create an order without a customer.
      • You can not create an order without an employee
      • This is know as a referential integrity constraint
• Other items of an Access database
  • A form is a graphical element which allows for data entry (better than just typing in a table)
  • A query is a command written in SQL (structured query language) which selects information from a table or tables.
    • select Title, FirstName, LastName from Employees where Country ="UK" order by LastName desc;
    • 
• They present a series of screen shots that show how to navigate in Access
  • This is somewhat similar to excel/word
  • But it is different as well, because access is radically different from these other two programs.