Introducing Relational Database Management Systems

1
Introducing Relational Database Management
Systems

• Introduction to MIS
• BUS 391
• Professor Barry Floyd

2
Agenda

• Files why not just use them?
• What is a Database Management System?
• What is modeled in a DBMS?
• How do we design databases?
• What is the relational database model?

3
Objectives

• Understand data modeling and role of data
  organization and its impact on functionality.
• Be able to create, modify, and populate an Access
  table in a manner which maintains entity and
  domain integrity.

4
The way we store our data is important!
5
Suppose President Baker said ...

• Our volleyball team has the highest average GPA
  of any CSU team!
• Is this true???

6
Lets assume the following ...

• MASTER STUDENT FILE AT CAL POLY ADMIN
• MASTER STUDENT FILE AT CAL POLY REC CENTER

Name Address ID GPA Credits ....
Name Address CENTER ID INTRAMURAL SPORT ...
7
Files and Programs
Relationships among data files were not
maintained!
Savings Acct.
Loan Acct.
Checking Acct.
8
Problems with file systems ...

• UNCONTROLLED REDUNDANCY
• INCONSISTENT DATA
• INFLEXIBILITY
• LIMITED DATA SHARING
• POOR ENFORCEMENT OF STANDARDS
• LOW PROGRAMMER PRODUCTIVITY
• EXCESSIVE PROGRAM MAINTENANCE

9
Solution .... DBMS

• 1. UNDERLYING PHILOSOPHY...
• DONT ORGANIZE DATA TO SUPPORT A PARTICULAR
  APPLICATION. LOOK AT ALL THE DATA (WELL, ALMOST
  ALL THE DATA) OF THE ORGANIZATION AND STRUCTURE
  IT LOGICALLY WITHOUT REGARD TO ANY SPECIFIC
  APPLICATION.
• 2. USING TECHNOLOGY...
• PROVIDE THE MEANS FOR EACH PROGRAM TO ACCESS ONLY
  THE DATA IT NEEDS TO PERFORM ITS TASK.

10
Agenda

• Files why not just use them?
• What is a Database Management System?
• What is modeled in a DBMS?
• How do we design databases?
• What is the relational database model?

11
What is a Database Management System?

• A Database is ...
• A shared collection of logically related data,
  designed to meet the information needs of
  multiple users.
• A Database Management System is ...
• A software application system that is used to
  create, maintain, and provide controlled access
  to user databases.

12
Application Program 1
Application Program 2
DBMS
A
B
C
Data Elements
DATABASE
E
F
G
D
User 1
User 3
User 2
13
Agenda

• Files why not just use them?
• What is a Database Management System?
• What is modeled in a DBMS?
• How do we design databases?
• What is the relational database model?

14
Databases are abstractions which model ...

• Entities (usually things, but ...)
• Relationships
• Attributes
• Business rules

15
Entities as abstractions

• The world is full of like things, we abstract
  like things and call them entities.

PERSON
16
Purpose ... drives choice of entity and attributes

• EMPLOYEE
• STUDENT
• STAFF
• FACULTY

17
Attributes

• There are three main types of attributes
• Descriptive
• Brown, 3 yrs old, 20 lbs
• Naming
• Rover
• Referential
• 383-55-4321

The term Domain refers to the set of allowable
values an attribute may have.
18
Relationships

• A relationship is the abstraction of a set of
  associations that hold systematically between
  different kinds of things in the real world.

My doggie
PERSON OWNS DOG
STUDENT ASSIGNED TO A CLASS SEAT
19
Binary Relationships

• For the most part, we will work with Binary
  Relationships
• Note Degree refers to the number of entities
  that participate in a relationship (e.g., binary
  or two)
• one to one
• one to many
• many to many
• Note Cardinality refers to the number of
  instances of one entity that can be associated
  with each instance of another entity (e.g., one
  to many).

20
Types of Relationships

• One to one

21
Types of Relationships

• One to many

22
Types of Relationships

• Many to many
• A student may be enrolled in many classes, a
  class may have many students

MIS 321
FIN 301
23
Business Rules

• Want to ensure entity of the data ...
• Entity integrity
• Referential integrity
• Domain integrity
• Other ....
• Departure date must be later than arrival date.
• Amount of withdrawal may not exceed current
  account balance.

24
Benefits of the Database Approach

• Mimimal data redundancy
• Consistency of data
• Integration of data
• Sharing of data
• Enforcement of standards
• Ease of application development
• Uniform security, privacy, and integrity controls
• Data accessability and responsiveness
• Reduced program maintainance

25
Agenda

• Files why not just use them?
• What is a Database Management System?
• What is modeled in a DBMS?
• How do we design databases?
• What is the relational database model?

26
Database Design

• Is a process that begins with developing an
  understanding of user requirements (functional
  and performance) and using those requirements to
  create a system that effectively manages data
• Three major steps
• Conceptual design
• Logical design
• Physical design

27
Conceptual Data Modeling

• Entity Relationship Attribute Diagrams
• Modeling doesnt focus on a particular technology

1
m
Dog Owner
Dogs
28
Logical Data Modeling

• In this step we choose a modeling environment
• Relational
• Object
• Hierarchical
• Network

29
Physical Data Modeling

• In this step, the organization of data on the
  physical devices of the computer system are laid
  out
• Were not going there!

30
Ultimately modeled in a DBMS!

• The DBMS must provide functionality.
• Model the Es, Rs, As, and BRs
• Manipulate data
• Get just the data we want
• Control access to the data
• Maintain data integrity

31
Agenda

• Files why not just use them?
• What is a Database Management System?
• What is modeled in a DBMS?
• How do we design databases?
• What is the relational database model?

32
The Relational Data Model
In a Relational Model, the primary modeling
object is the table (also called a relation
which represents an entity.
33
The Table Faculty
TWO DIMENSIONS
NAMED TABLE
COLUMN IS AN ATTRIBUTE
ROW IS AN INSTANCE
34
Properties of tables

• Entries in Columns are atomic
• Entries in columns are from the same domain
  (domain integrity)
• Each row is unique (entity integrity)
• candidate key, primary key
• The sequence of columns is insignificant
• The sequence of rows is insignificant

35
The Relational Data Model
Primary Key - attribute or group of attributes
which uniquely identify an instance of the
entity. This maintains Entity Integrity.
36
Is this a Table
37
Consider
38
What is a good table?

• Insertion anomalies
• Deletion anomalies
• Update anomalies

39
Modeling Relationships

• One to many relationships are modeled by placing
  the primary key from one table and putting it
  into many table.

Mary 23 123 Highway
Spot 3 20lbs Mary Kitty 2 8lbs Mary
40
How to model the teach relationship in a
Relational DBMS?
41
Key Points

• The major construct of the Relational Data Model
  is the Relation (table). You should know the
  characteristics of a table!
• Relationships are modeled via data values stored
  in the table
• There is a notion of a good table design.

42
Summary

• In this session we have laid the foundation for
  understanding relational database management
  systems
• Well build on this understanding in the lab
  today, especially modeling relationships.