Introduction

What You'll Learn This Week

• Introduction to the course
• Introduction to Databases
• File Systems
• Example Database
• Brief History of Database Systems
• Contents of a Database
  • User Data
  • Metadata
  • Indexes
  • Applications Metadata
• Data Modeling and Database Design
• The Database Development Process
• Designing A Database - A Brief Example
• Introduction to the Internet and WWW
• For Next Week

Introduction to the course

• Syllabus
• Computer Labs and Software
• Course Logistics

Introduction to Databases

Connolly, Begg, Holowczak	Pratt/Adamski	Elmasri/Navathe (3rd ed.)	Kroenke Book (7th ed.)	McFadden (5th ed.)	Mata-Toledo / Cushman
Ch. 1 and 2	Ch. 1	Ch. 1 and 2	Chap. 1 and 2	Chap. 1	Schaum's Outlines Ch. 1

• Q: What is a Database ?
  
• Answer from Pratt/Adamski:
  • A Database (DB) is structure that can store information about:
    1. multiple types of entities,
    2. the attributes that describe those entities; and
    3. the relationships among the entities
• Answer from Elmasri/Navathe:
  • A Database (DB) is collection of related data - with the following properties:
    1. A DB is logically coherent and has some relevant meaning
    2. A DB is designed, built and populated with data for a specific purpose
    3. A DB represents some aspect of the real world.
• Answer from Kroenke: An integrated, self-describing collection of related data
  • Integrated: Data is stored in a uniform way, typically all in one place (a single physical computer for example)
  • Self-Describing: A database maintains a description of the data it contains (Catalog)
  • Related: Data has some relationship to other data. In a University we have students who take courses taught by professors
  • By taking advantage of relationships and integration, we can provide information to users as opposed to simply data.
  • We can also say that the database is a model of what the users perceive.
  • Three main categories of models:
    1. User or Conceptual Models: How users perceive the world and/or the business.
    2. Logical Models: Represent the logic of how a a business operates. For example, the relationship between different entities and the flow of data through the organization. Based on the User's model.
    3. Physical Models: Represent how the database is actually implemented on a computer system. This is based on the logical model.

• Database Management System (DBMS)
  A collection of software programs that are used to define, construct, maintain and manipulate data in a database.

• Database System (DBS) contains:
  The Database +
  The DBMS +
  Application Programs (what users interact with)

File Systems

• File System: A collection of individual files accessed by applications programs
• Limitations of a File System:
  • Separated and Isolated Data - Makes coordinating, assimilating and representing data difficult
  • Data Duplication - Wastes space and can lead to data integrity (inconsistency) problems
  • Application Program Dependencies - Changes to a single file can require changes to numerous application programs
  • Incompatible Files
  • Lack of Data Sharing - Difficult to control access to files, especially to individual portions of files

• Advantages of a DBMS
  A DBMS can provide:
  • Data Consistency and Integrity - by controlling access and minimizing data duplication
  • Application program independence - by storing data in a uniform fashion
  • Data Sharing - by controlling access to data items, many users can access data concurrently
  • Backup and Recovery
  • Security and Privacy
  • Multiple views of data

Example Database

An Example Database

• What happens when a customer moves to a new house ?
• Who should have access to what data in this database ?
• What happens if Mr. and Mrs. Builder both try and withdraw $500 from account 3322 ?
• What happens if the system crashes just as Mr. Axe is depositing his latest paycheck ?
• What data is the customer concerned with ?
  What data is a bank manager concerned with ?
• Send a mailing to all customers with checking accounts having greater than $2000 balance
• Let all GA customers know of a new branch location

Brief History of Database Systems

• 1940's, 50's Initial use of computers as calculators. Limited data, focus on algorithms. Science, military applications.

• 1960's Business uses. Organizational data, customer data, sales, inventory, accounting, etc. File system based, high emphasis on applications programs to extract and assimilate data. Larger amounts of data, relatively simple calculations.

• 1970's The relational model. Data separated into individual tables. Related by keys. Initially required heavy system resources. Examples: Oracle, Sybase, Informix, Digital RDB, IBM DB2.

• 1980's Microcomputers - the IBM PC, Apple Macintosh. Database program such as DBase (sort of), Paradox, FoxPro, MS Access. Individual user can crate, maintain small databases.

• Late- 1980's Local area networks. Workgroups sharing resources such as files, printers, e-mail.
  Client/Server Database resides on a central server, applications programs run on client PCs attached to the server over a LAN.

• 1990's Internet and World Wide Web make databases of all kinds available from a single type of client - the Web Browser. Data warehousing and Data Mining also emerge.

• Other types of Databases:
  • Object-Oriented Database Systems. Objects (data and methods) stored persistently.
  • Distributed Database Systems. Copies of data reside at different locations for redundancy or for performance reasons.

Appropriate Use for a Database

• In addition to the advantages already mentioned:
  • Performance
  • Expendability, Flexibility, Scalability
  • Reduced application development times
  • Standards enforcement

• However, keep in mind:
  • DBMS has High initial cost (although falling)
  • DBMS has High Overhead - requires powerful computers
  • DBMS are not special purpose software programs

    e.g., contrast a canned accouting software package like Quicken or QuickBooks with DBMS like MS Access.

• When is a DBMS Not Appropriate?
  • Database is small with a simple structure
  • Applications are simple, special purpose and relatively static.
  • Applications have real-time requirements
    Examples: Traffic signal control
    ECU patient monitoring
  • Concurrent, multi-user access to data is not required.

Contents of a Database

• User Data
• Metadata
• Indexes
• Application metadata

User Data

• Data users work with directly by entering, updating and viewing.
• For our purposes, data will be generally stored in tables with some relationships between tables.
• Each table has one or more columns. A set of columns forms a database record.
• Recall our example database for the bank. What were some problems we discussed ?
• Here is one improvement - split into 2 tables:

Accounts Table

CustomerID	Acct_Number	Balance
123	9987	4000
123	9980	2000
124	8811	1000
125	4422	6000
125	4433	9000
127	3322	500
127	1122	800

• The customer table has 4 records and 5 columns. The Accounts table has 7 records and 3 columns.
• Note relationship between the two tables - CustomerID column.
• How should we split data into the tables ? What are the relationships between the tables ?
  There are questions that are answered by Database Modeling and Database Design.

Metadata

• Recall that a database is self describing
• Metadata: Data about data.
• Data that describe how user data are stored in terms of table name, column name, data type, length, primary keys, etc.
• Metadata are typically stored in System tables or System Catalog and are typically only directly accessible by the DBMS or by the system administrator.

  Have a look at the Database Documentor feature of MS Access (under the tools menu, choose Analyze and then Documentor).
  This tool queries the system tables to give all kinds of Metadata for tables, etc. in an MS Access database.

Indexes

• In keeping with our desire to provide users with several different views of data, indexes provide an alternate means of accessing user data. Sorting and Searching:
• An index for our new banking example might include the account numbers in a sorted order.
• Indexes allow the database to access a record without having to search through the entire table.
• Updating data requires an extra step: The index must also be updated.
• Example: Index in a book consists of two things:
  1) A Keyword stored in order
  2) A pointer to the rest of the information. In the case of the book, the pointer is a page number.

Applications Metadata

• Many DBMS have storage facilities for forms, reports, queries and other application components.
• Applications Metadata is accessed via the database development programs.
• Example: Look at the Documentor tool in MS Access. It can also show metadata for Queries, Forms, Reports, etc.

Data Modeling and Database Design

• Database Design: The activity of specifying the schema of a database in a given data model

• Database Schema: The structure of a database that:
  • Captures data types, relationships and constraints in data
  • Is independent of any application program
  • Changes infrequently

• Data Model:
  • A set of primitives for defining the structure of a database.
  • A set of operations for specifying retrieval and updates on a database
  • Examples: Relational, Hierarchical, Networked, Object-Oriented
  In this course, we focus on the Relational data model.

• Database Instance or State: The actual data contained in a database at a given time.

The Database Development Process

1. Top-Down: Design systems from an overall organization perspective
2. Bottom-Up: Design systems from a specific perspective - one system at a time.

The following is a very brief outline describing the database development process.

• User needs assessment and requirements gathering: Determine what the user's are looking for, what functions should be supported, how the system should behave.

• Data Modeling: Based on user requirements, form a logical model of the system. This logical model is then converted to a physical data model (tables, columns, relationships, etc.) that will be implemented.

• Implementation: Based on the data model, a database can be created. Applications are then written to perform the required functions.

• Testing: The system is tested using real data.
• Deployment: The system is deployed to users. Maintenance of the system begins.

Designing A Database - A Brief Example

• Tables
  CUSTOMERS
  Customer_Id, Name, Street, City, State, Zip
  

  ACCOUNTS
  Customer_Id, Account_Number, Account_Type, Date_Opened, Balance

  Note that we use an artificial identifier (a number we make up) for the customer called Customer_Id. Given a Customer_Id, we can uniquely identify the remaining information. We call Customer_Id a Key for the CUSTOMERS table.
  

  • Customer_Id is the key for the CUSTOMERS table.
  • Account_Number is the key for the ACCOUNTS table.
  • Customer_Id in the ACCOUNTS table is called a Foreign Key

  Notice that when naming columns in the tables we always use an underscore character and do not use any other punctuation. even though Access allows you to use spaces, etc. it is not a good idea.

• Relationships
  The relationship between CUSTOMERS and ACCOUNTS is by Customer_Id.
  Since a customer may have more than one account at the bank, we call this a One to Many relationship. (1:N).

• Domains
  A domain is a set of values that a column may have. Domain also includes the type and length or size of data found in each column.
  

  CUSTOMERS
  

  Column	Domain
  	Data Type	Size
  Customer_Id (Key)	Integer	20
  Name	Character	30
  Street	Character	30
  City	Character	25
  State	Character	2
  Zip	Character	5

  ACCOUNTS
  

  Column	Domain
  	Data Type	Size
  Customer_Id (FK)	Integer	20
  Account_Number (Key)	Integer	15
  Account_Type	Character	2
  Date_Opened	Date
  Balance	Real	12,2

• We use the above information to build a logical model of the database.
• This logical model is then converted to a physical model and implemented as tables.

• The following is some example data for the Accounts and Customers tables:
  Customer Table
  

  Customer_Id	Name	Address	City	State	Zip
  123	Mr. Smith	123 Lexington	Smithville	KY	91232
  124	Mrs. Jones	12 Davis Ave.	Smithville	KY	91232
  125	Mr. Axe	443 Grinder Ln.	Broadville	GA	81992
  127	Mr. & Mrs. Builder	661 Parker Rd.	Streetville	GA	81990

  Accounts Table
  

  Customer_Id	Account_Number	Account_Type	Date_Opened	Balance
  123	9987	Checking	10/12/89	4000.00
  123	9980	Savings	10/12/89	2000.00
  124	8811	Savings	01/05/92	1000.00
  125	4422	Checking	12/01/94	6000.00
  125	4433	Savings	12/01/94	9000.00
  127	3322	Savings	08/22/94	500.00
  127	1122	Checking	11/13/88	800.00

• Business Rules
  Business rules allow us to specify constraints on what data can appear in tables and what operations can be performed on data in tables. For example:
  1. An account balance can never be negative.
  2. A Customer can not be deleted if they have an existing (open) account.
  3. Money can only be transferred from a "Savings" account to a "Checking" account.
  4. Savings accounts with less than a $500 balance incur a service charge.

  How do we enforce business rules ?
  

  • Constraints on the database
  • Applications

For Next Week

• Check for formal assignments
• Chapters to Read:
  Connolly, Begg, Holowczak Chapters 4 and 9
• Start forming your groups (pair of students) and discussing ideas for the group projects.