DBS201

1
DBS201

• Database Design and Introduction to SQL

2
Introduction to Database Management
3
Terminology

• Application programs, data, procedures used for
  some purpose in an organization (eg financial,
  payroll, equipment maintenance, project
  management, )
• Database an organized collection of related data
  for an application
• DBMS (DataBase Management System) set of
  programs that manage (create, modify, secure,
  backup, restore, ) one or more databases (eg
  Access, DB2, Oracle, SQL Server)
• Database Design determining the structure (ie.
  schema) of the database required for the
  application

4
File Based Approach

• Used in all manual systems and in early
  computerized systems
• Duplicates data between applications (eg customer
  information stored in files for Sales, Customer
  Service, Billing, )
• Increased data maintenance
• Can create data inconsistencies
• Very difficult to fulfill requirements that
  involve data from more than one system

5
File Based (Non-database) Approach
6
Database Approach

• Data for each application is stored in a database
  and managed by the DBMS
• Data can only be accessed through the DBMS and
  not directly through the OS

7
Database Approach Advantages

• Easier to combine data from more than 1
  application
• Ability to share data between applications can
  result in reduced data redundancy (reduced
  duplication and improved consistency of data)
• Concurrency Allows access to data by many
  different users at the same time
• Centralized control of data by DBMS and DBA

8
Advantages of Database (ctd)

• Improved security and recovery of data
• Data integrity can be maintained through
  constraints
• DBMS development tools reduce programming
  required
• Application Flexibility and Data independence
  Structure of data can be changed without having
  to change programs

9
Disadvantages of Database Approach

• Complexity of DBMS software requires developers
  to have specialized skills
• DBMS overhead software cost, data storage
  requirements, processing requirements, DBA
  responsibilities
• Greater reliance on reliability of 1 software
  product (as opposed to many different file-based
  applications)
• Applications take more time to design
• More complex to recover data

10
Sharing of Data between Applications using a DBMS
11
History of DBMSs

• Mainframe DBMSs have been used since the 1960s
• Since the mid-1980s, DBMSs on PCs possess many
  of the features of their mainframe counterparts
• Evolution of DBMS models Hierarchical, Network,
  Relational, Object-oriented, Object-relational

12
Hierarchical Database Model

• Data is seen as a tree structure and can only be
  retrieved by navigating through hierarchy
• A parent record can have many children but a
  child record can have only 1 parent record
• Navigation is achieved through use of pointers
  physically stored with each record

13
Hierarchical Database Model

• IBMs IMS was one of first hierarchical DBMSs
• Still used by many large legacy OLTP(online
  transaction processing) applications
• Good at handling large volumes of data that only
  need to be accessed using only predefined paths

14
Network Database Model

• Next database model product examples IDMS,
  I-D-S
• Data is seen as a network of records and
  relationships between these records
• Data can only be accessed by navigating through
  defined relationships
• Pointers physically stored with each record
  permit navigation

15
Relational Database Model

• Developed after network model
• Based on mathematical set theory that ensures
  that data can be retrieved in any way required by
  an application
• Data is stored in the form of 2 dimensional
  tables with no physical pointers
• Data is related through common columns in
  separate tables
• Product Examples Oracle, DB2, SQL Server