Database Management Systems and Design

1
Database Management Systems and Design

• Instructor Zhiwei Wang, Ph.D. in Computer
  Science
• E-mail zwang1_at_stthomas.edu
• Office OSS309
• Phone [phone]

2
Course Description

• This course is an introductory course to data
  models and database management systems. It covers
  the theoretical and technical concepts of
  database management systems. It is not intended
  as a class where you will learn how to write
  database applications.
• This course focuses on the database management
  software as a system and explores different
  components of it. The course discusses database
  design theories and implementation techniques.
  Topics include semantic modeling and conceptual
  database design, relational model and relational
  design, relational query languages, concurrency
  control, recovery techniques, etc.

3

• After taking this course, the students are
  expected to have a general idea about
  database design theory and implementation
  methodology, as well as a basic
  understanding of database languages.

4
Course Outline

• Basic Concepts
• Database System Concepts and Architecture
• Entity-Relationship Modeling
• Relational Model
• Basic concepts
• Relational Algebra
• SQL
• Design Theory
• Functional Dependencies
• Normalization
• Design Algorithms
• Implementation Techniques
• Indexing
• Query Processing
• Transaction Processing

5
Databases and Database Users
1 Introduction A database system consists of a
database and database management system software.
Database A database is a collection of data
organized for storage and access by computers.
The data may be text, numbers, images, video
clips, or sound messages.
6

• A database has the following properties
• A database represents some aspect of the real
  world, sometimes called the miniworld of the
  Universe of Discourse (UoD). Changes to the
  miniworld are reflected in the database.
• A database is a logically coherent collection of
  data with some inherent meaning.
• A database is designed, built, and populated with
  data for a specific purpose. It has an intended
  group of users and some preconceived applications
  in which these users are interested.

7
An Example Figure 4.2Instance of the STUDENT
relation.
8
Figure 4.5 AA Fragment of a database instances.
9
Figure 4.5 C (continued) STUDENT Relation with
different order of columns and tuples.
10
Database Management System (DBMS) A database
management system is a software system between
the database and the user. A database management
system enables a user to define, construct, and
manipulate a database. Defining a database
involves specifying the data types, structures,
and constraints for the data to be stored.
Constructing the database is the process of
storing the data itself on some storage medium
that is controlled by the DBMS. Manipulating a
database includes such functions as querying the
database to retrieve specific data, updating the
database, and generating reports from the data.
11

• 1.4 Database Users
• Different types of users
• Database Administrators (DBA)
• Database Designers
• End Users
• System Analysts and Application Programmers
  (Software Engineers)

12

• 1. Database administrators
• Database administrators are specialists who
  supervise the DBMS. The DBA is responsible for
  authorizing access to the database, for
  coordinating and monitoring its use, and for
  acquiring software and hardware resources as
  needed.
• The DBA is accountable for problems such as
  breach of security or poor system response time.
• In large organizations, the DBA is assisted
  by a staff that helps carry out these functions.

13
2. Database Designers Database Designers are
responsible for identifying the data to be stored
in the database and for choosing appropriate
structures to represent and store this data.
14
3. End users End users are the people whose jobs
require access to the database for querying,
updating, and generating reports. Casual
users Users accessing the DBMS with SQL queries.
The term casual as used here implies that the
user has changing requirements from one session
to the next, so that it is not economical to try
to write a menu-based program application for
each such casual use. Queries that are formulated
on the spot to answer some immediate need are
known as interactive queries or ad hoc queries
15
Naive or parametric end users A naive user is one
who performs all database applications using
standard types of queries and updates-called
canned transactions-that have been carefully
programmed and tested. Sophisticated end
users Sophisticated end users include engineers,
scientists, business analysts, and others who
thoroughly familiarize themselves with the
facilities of the DBMS so as to implement their
applications to meet their complex
requirements. Stand-alone users Stand-alone
users maintain personal databases by using
ready-made program packages that provide
easy-to-use menu or graphics based interfaces.
16
4. System Analysts and Application Programmers
(Software Engineers) System analysts determine
the requirements of end users and develop
specifications for canned transactions that meet
these requirements. Application programmers
implement these specifications as programs.