Transcript 1294219715database 4.1

DATABASE
By
Mr. Abdalla A. Shaame
1
 What
is a database?
 The
main characters of a database system
 The
basic database design method
 The
entity-relationship data model for
application modeling
2
Types of databases
• traditional
• multimedia - pictures, video, sound
• Scientific database
- GIS: maps, weather, satellite images
- biological database: DNA sequence analysis
• warehouse, OLAP - analysis, decision making
• real-time, active databases - industrial processes
• deductive databases - combination of inference and
databases
3
Database
• collection of related data
• represents some aspect of the real world
• built with a specific purpose in mind
• could be anywhere - notebook, spreadsheet, Access,
Oracle, … could be manual / could be computerized
Data
• known and recordable facts
• name, phone number, address, grade, ...
4
A database system is composed of four
components;
Data
Hardware
Software
Users
5
6
Database Management System (DBMS)
• collection of software facilitating the definition,
construction and manipulation of databases
Definition
•record structure
•data elements
•names
•data types
•constraints
etc
Construction
•create database
files
•populate the
database with
records
Manipulation
•querying
•updating
7
Database Management System (DBMS)
• collection of software facilitating the definition,
construction and manipulation of databases
DBMS
Meta data
Request
manager
Users/
actors
Storage
manager
Stored
database
8
Sample database
file2
file1
Course CName CNo CrHrs Dept
Database 8803 3
CS
C
2606 3
CS
Student Name StNo Class Major
Smith 17
1
CS
Brown
8
2
CS
Grades StNo Sid Grade
17
25 A
17
43 B
Section SId CNo Semester Yr
Instructor
32 8803 Spring 2000 Smith
25 8803 Winter 2000 Smith
43
2606
Spring 2000 Jones
file3
file4
9
Characteristics of the Database approach
• single repository of data
• sharable by multiple users
•concurrency control
•transaction control
• self-describing - system catalogue contains meta data
• program-data independence
• some changes to the database are transparent to
programs/users
• multiple views of data - to support individual needs of
programs/users
10
Database Actors
• Database Administrator (DBA)
-
authorizing access to the database
coordinating and monitoring its use
aquiring software and hardware as needed
solving problems such as breach of security or poor system
performance
• Database Designers/Modelers
-
identifying the data to be stored
choosing appropriate data structure
• End-users
-
access to the database: querying, updating, generating reports
• Software Engineers
-
developing application programs
11
Advantages of the Database approach
(capabilities we would look for in a DBMS)
• controlled redundancy
• database design integrates different user data needs
• performance tuning may lead to replicated data
• Sharing of the data
• controlled/authorized access to data
• Ensure integrity of the data
• backup and recovery
12
Implications of the Database approach
• standards enforcement
• DBA can enforce standard naming conventions, etc
• reduced application development time
• adding new functionality to an existing database is
“easy”
• flexibility
• adding new data and making some types of changes is
“easy”
• up-to-date information - due to sharable characteristic
• economies of scale - due to sharable resources the whole
organization can make one investment
13
When not to use a DBMS
• overhead costs are too great
• high initial investment
• data and applications are simple, well-defined, not expected to
change
• stringent real-time constraints
• multi-user environment not needed
14
Database design
• let’s jump ahead to see this
Course
1
Student
Delivery
M
N
Grades
Section
N
15
Database design
• let’s jump ahead to see this
Course
StNo
Class
1
Student
Major
Delivery
Name
M
N
Grades
Section
N
16
Database design
• let’s jump ahead to see this
CNo
CrHrs
Dept
Course
StNo
Class
CName
1
Student
Major
Delivery
Name
M
N
Grades
Section
N
17
Database design
• let’s jump ahead to see this
CNo
CrHrs
Dept
Course
StNo
Class
CName
1
Student
Major
Delivery
Name
M
N
Grades
Semester
Section
N
Year
SId
Instructor
18
Database design
• let’s jump ahead to see this
CNo
CrHrs
Dept
Course
StNo
Class
CName
1
Student
Major
Delivery
Name
M
N
Grades
Semester
Section
N
grade
Year
SId
Instructor
19
name number
With attributes, etc:
fname
name
ssn
bdate
degree
1
lname
minit
sex
startdate
employee
supervisor
department
works for
N
salary
address
1
location
1
1
controls
manages
1
N hours
N
supervisee
number of
employees
N
M
1
works on
supervision
dependents of
project
name number
location
N
dependent
name
sex
birthdate relationship
20
ER-to-Relational mapping
1. Create a relation for each strong entity type
2. Create a relation for each weak entity type
• include primary key of owner (an FK - foreign key)
• owner’s PK + partial key becomes PK
3. For each binary 1:1 relationship choose an entity and
include the other’s PK in it as an FK. Include any
attributes of the relationship
4. For each binary 1:n relationship, choose the n-side entity
and include an FK of the other entity. Include any
attributes of the relationship
21
5. For each binary M:N relationship, create a relation for the
relationship
• include PKs of both participating entities and any
attributes of the relationship
• PK is the concatenation of the participating entity PKs
6. For each multivalued attribute create a new relation
• include the PK attributes of the entity type
• PK is the PK of the entity type and the multivalued
attribute
7. For each n-ary relationship, create a relation for the
relationship
• include PKs of all participating entities and any attributes
of the relationship
• PK may be the concatenation of the participating entity
PKs
22