Transcript slides

Introduction
Database Management
Systems
Database Management
System (DBMS)

Collection of
– interrelated data and
– set of programs to access the data


Convenient and efficient processing
of data
Database Application Software
Farkas
CSCE 824
2
Evolution of Database
Systems


Farkas
Early days: customized applications built on top
of file systems
Drawbacks of using file systems to store data:
– Data redundancy and inconsistency
– Difficulty in accessing data
– Atomicity of updates
– Concurrency control
– Security
– Data isolation — multiple files and formats
– Integrity problems
CSCE 824
3
Abstraction

View level: different perspectives
– application programs hide irrelevant data

Logical level: data models
– Logical representation of data
– type employee = record
name : string;
address : string;
salary: real;
end;
– Data independence principle

Farkas
Physical level: how data is stored
CSCE 824
4
Database Users

Users are differentiated by the way they
interact with the system




Farkas
Database Administration: responsible for the
structure or schema of the database (DDL),
coordinates all activities regarding the database
Application programmers – interact with system
through DML calls
Sophisticated users – form requests in a database
query language
Naive users – invoke one of the permanent
application programs that have been written
previously
CSCE 824
5
Data Models

A collection of tools for describing
–
–
–
–



Farkas
Data
Relationships among data items
Semantics of stored data
Database constraints
Entity-Relational Model
UML
Etc.
CSCE 824
6
Database Management
Systems

Smaller and smaller systems
– Past: large and expensive DBMS
– Present: DBMS in most personal
computers

More and more data stored
– Past: few MB
– Present: terabyte (1012 bytes), petabyte
(1015 bytes)


Farkas
Functionality: from physical to view
level
Optimization
CSCE 824
7
Data Definition Language
(DDL)



Farkas
Defines the database schema
and constraints
DDL compiler  data dictionary
Metadata – data about data
CSCE 824
8
Data Manipulation
Language (DML)


Accessing and manipulating the data
Query Languages
– Procedural – user specifies what data is
required and how to get those data
– Nonprocedural – user specifies what
data is required without specifying how
to get those data
Farkas
CSCE 824
9
MODELING DATA
SEMANTICS
Farkas
CSCE 824
10
Entity Sets and
Relationship Sets




Farkas
Database: collection of entities and
relationship among entities
Entity: object that exists and
distinguishable from other objects
Entity set: collection of similar objects
Attribute: property of an entity and
relationship sets
CSCE 824
11
Attributes


Domain: set of permitted values
for each attributes
Attribute types:
– Simple vs. composite
– Single-valued vs. multi-valued
– Derived
Farkas
CSCE 824
12
Example E/R Diagram
Name
Age
Weight
Breed
Dog
Name
Boards
Owns
Kennel
Pays
License #
Address
Phone
Owner
Name
Farkas
Phone
CSCE 824
13
Degree of Relationship
Sets




Number of entity sets participating in a
relationship set
Binary relationship set: two entity sets
(most common)
Multiway relationship set: connects more
than two entity sets
E.g., An owner frequents certain kennels
for certain dogs
– Binary relationship can’t represent these
requirements
– Need 3-way relationship
Farkas
CSCE 824
14
Example 3-Way Diagram
Name
Age
Breed
Name
Dog
Kennel
Must
Board
Weight
License #
Address
Phone
Owner
Name
Farkas
Phone
CSCE 824
15
Mapping Cardinality

Number of entities to which
another entity can be associated
via a relationship set
– One-one
– Many-one (One-many)
– Many-many
Farkas
CSCE 824
16
Roles



Entity set may appear more then once in a
relationship
Label the edges between the relationships
and the entity set with names called roles.
E.g., relationships among the dogs:
Dog
Farkas
ancestor
descendant
CSCE 824
Related
to
17
Subclasses in E/R




Farkas
Special case, fewer entities, more
properties
E.g., show dog is a dog, but not all dogs
are show dogs. It also have properties,
type of competition, rank, etc.
Assume subclasses form a tree (no
multiple inheritance)
ISA relationship
CSCE 824
18
Example Subclass
Name
Age
Weight
Breed
Dog
Special case, fewer entities,
ISA
more properties
• Subclasses form a tree
(no multiple inheritance) Show-Dog
•
Type
Rank
Farkas
CSCE 824
19
Keys


Farkas
Set of attributes for one entity set
such that no two entities in the
set has the same value for all the
attributes of the key
Each entity set must have a key
CSCE 824
20
Keys



Farkas
Super key: set of one or more
attributes whose value iniquely
determine each entity
Candidate key: minimal super
key
Primary key: a selected candidate
key
CSCE 824
21
Example Multi-Attribute Key for
Owner
There are more than
One person with the
Same name, but they
Live at different
Places.
Owner
Name
Farkas
Address
Phone
CSCE 824
22
Converting ER Model
into Relations
Farkas
CSCE 824
23
Example Relation
Dog
Name
Age
Weight
(lb)
Breed
Pepper
10
75
Joker
4
83
German
Shepherd
Mix
Bruno
null
51
Boxer
Farkas
CSCE 824
24
Relational Data Model



Farkas
Set of relation names:
R
Set of attribute names: A
Relation schema: S=(r,{a1, …,an})
– r relation name in R
– {a1, …,an} subset of A
e.g., (Dog,{Name, Age, Weight, Breed})
CSCE 824
25
Relational Data Model

Tuple over a relation scheme S is
a mapping
t: {a1, …,an}  dom(a1  …  an)
e.g.,
t(Dog-Name)=Pepper
t(Dog-Age)=10
t(Dog-Weight)= 75
t(Dog-Breed)=German Shepherd
Farkas
CSCE 824
26
Relational Data Model


Farkas
Relation over schema S is a set of
tuples over the scheme
Database: set of relations
CSCE 824
27
Query Languages

Relational Algebra
– Set operations

SQL
– Bag operations
Farkas
CSCE 824
28
Relational Algebra







Farkas
Select ()
Project ()
Set difference (-)
Union ()
Rename (Px(r))
Set intersection ()
Natural join ()
CSCE 824
29
Structured Query Language
SQL

Typical SQL query form:
SELECT A1, A2, ..., An
FROM r1, r2, ..., rm
WHERE C
– Ais represent attributes to be
returned
– ris represent relations
– C is a condition
Farkas
CSCE 824
30
Next Class
Overview of Information Security
(from CSCE 522 slides)
Farkas
CSCE 824
31