Transcript DBS201: Database Design and Introduction to SQL

DBS201
Database Design and
Introduction to SQL
Introduction to Database
Management
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
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
File Based (Nondatabase) Approach
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
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
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
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
Sharing of Data between
Applications using a DBMS
History of DBMS’s
• Mainframe DBMS’s have been used
since the 1960s
• Since the mid-1980s, DBMS’s on PCs
possess many of the features of their
mainframe counterparts
• Evolution of DBMS models:
Hierarchical, Network, Relational,
Object-oriented, Object-relational
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
Hierarchical Database Model
• IBM’s IMS was one of first hierarchical
DBMS’s
• 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
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
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