Introduction to Databases by Dr. Soper

august 2015

Chapter 1. Introduction to Databases

Dr. Soper gives an introductory lecture on database technologies. Topics covered include the reasons for using a database, the components of a database system, the elements of a database, the purpose of database management systems (DBMS), and the functions of a database application.

Topic Objectives

  • Know the potential problems with lists
  • Understand the reasons for using a database
  • Understand how related tables avoid the problems associated with lists
  • Learn the components of a database system
  • Learn the elements of a database
  • Learn the purpose of database manadgement system (DBMS)
  • Understand the function of database application

Purpose of a DataBase

  • The purpose of a database is:
    • To store data
    • To provide an organizational structure for data
    • To provide a mechanism for quering, creating, modifying, and deleting data
  • A database can store information and relationships that are more complicated than a simple list

Problems with Lists: Redundancy

  • In a list, each row is intended to stand on its own. As a result, the same information may be entered several times
    • For example, a list of projects may include the project manager's name, ID, and phone extention.
      • If a particular person is currently managing 10 progects, his/her information would appear in the list 10 times

Problems with Lists: Multiple Themes

  • In a list, each row may contain information on more than one theme or business concept
  • As a result, certain information might appear in the list only if information about other themes or business concepts is also present
    • For example, a list of projects may include project manager information (Name, ID, and Phone Extention) and project information (ProjectName, ID, StartDate, Budget) in the same row

List Modification Issues

  • Redundancy and multiple themes in lists create modification problem
    • Deletion problems
    • Update problems
    • Insertion problems
List Modification Issues

Addressing Information Complexities

  • Relational databases are designed to address many of the information complexity issues that arise business

Relational Databases

  • A relational databases stores information in table. Each informational theme (business concept) is stored in its on table
  • In essence, a relational database will break-up a list into several parts.
    • One part for each theme in the list
    • For example, a Project List might be divided into a CUSTOMER Table, a PROJECT Table, and a PROJECT_MANAGER Table
relational databases

relationship in databases

Putting the Pieces Back Together

  • In our relational database example, we broke apart our list into several tables. Somehow the tables must be joined back together
  • In relational database, tables are joined together using matched pairs of values
    • For example, if a PROJECT has CUSTOMER, the CUSTOMER_ID can be stored as column in the PROJECT table. Whenever we need information about a customer, we can use Customer_ID to look up the customer information in the CUSTOMER table
join table

Sounds like More Work, Not Less

  • A relational databases is more complicated than a list
  • However, a relational database minimizes data redundancy, preserves complex relationships among topic, and allows for partial data (null values)
  • Furthermore, a relational databese provides a solid foundation for creating user interface forms and reports

The Structured Query Language (SQL)

  • The Structured Query Language (SQL) is an international standart language for creating, processing, and querying databases and their tables
  • The vast majority of data-driven applications and websites use SQL to retrieve, format, report, insert, delete, and/or modify data for users
sql example

SQL Example

  • We can use SQL to combine the data in the three tables in the Art Course Database to recreate the original list structure of the data
    • We do this using a SQL SELECT statement
SELECT Customer.customerLastname,
             Customer.customerFirstName,
             Customer.phone,
             Course.courseDate,
             Enrollment.amountPaid,
             Course.course,
             Course.fee
FROM Customer, Enrollment, Course
WHERE Customer.customerNumber=Enrollment.customerNumber
AND Course.courseNumber = Enrollment.courseNumber
sql example join list

Database Systems

  • The four components of a database system are:
    • Users
    • Database Application(s)
    • Database Management System (DBMS)
    • Database
database components

Users

  • A user of a database system will:
    • Use a database application to keep track of information
    • Use different user interface forms to enter, read, delete, and query data
    • Produce report

The Database

  • A database is a self-describing collection of related records
  • Self-describing:
    • The database itself contains the definition of its structure
    • Metadata are data describing the structure of the data in the database
  • Tables within a relational database are related to each other in some way
database contents

Database Management System (DBMS)

  • A Database management system (DBMS) serves as an intermediary between database applications and database
  • The DBMS manages and controls database activities
  • The DBMS creates, processes, and administers the databases it control

Functions of a DBMS

  • Create databases
  • Create tables
  • Create supporting structures
  • Read database data
  • Modify database data (insert, update, delete)
  • Maintain database structures
  • Enforse rules
  • Control concurency
  • Provide security
  • Perform data backup and recovery

Referential Integrity Constraints

  • A DBMS can enforse many constraints...
  • Referential integrity constraints ensure that the values of a column in one table are valid based on the values in another table
    • For example, if a 5 was entered as CustomerId in the PROJECT table, a Customer having a CustomerID value of 5 must exist in the CUSTOMER table
DBMS constraints

Database Applications

  • A database application is a set of one or more computer programs or websites that serve as a intermediary between the user and the DBMS

Personal Database Systems

  • Personal database systems typically:
    • Support one aplication
    • Have only a few tables
    • Are simple in design
    • Involve only one computer
    • Support one user at a time
personal database system

Enterprise-Level Database System

  • Enterprise-Level database systems typically:
    • Support several users simultaneously
    • Support more than one application
    • Involve multiple computers
    • Are complex in design
    • Have many tables
    • Have many databases
organizational database system

Commercial DBMS Products

  • Example Desctop DBMS Products
    • Microsoft Access
  • Example Organizational DBMS Products
    • SQL Server
    • Oracle
    • MySQL
    • DB2




Copyright by Dr.Daniel Soper