Transcript ميحرلا نمحرلا الله مسب 1

‫بسم هللا الرحمن الرحيم‬
‫‪1‬‬
Lecture (1)
2
Welcome
Welcome to 304‫حسب‬
An Introduction to databases.
We hope you will enjoy it.
3
Information about course
An Introduction to ‫ مبادئ قواعد البيانات‬: ‫إسم المقرر‬
databases
304‫حسب‬: ‫رمز المقرر‬
4
Database course - People
.‫ايمان محمد الحسن‬.‫ أ‬:‫المحاضر‬
Office hours: sunday 10-2.
Email :[email protected].
:‫مساعدى التدريس‬
5
‫‪Cont.‬‬
‫مدة الدراسة ‪:‬‬
‫محاضرة واحدة أسبوعيا لمدة اربعة عشر أسبوعا ‪ ،‬مدة‬
‫المحاضرة ساعتان ‪.‬‬
‫تقييم المقرر‪:‬‬
‫‪ %60‬االمتحان النهائي‬
‫‪ %15‬االمتحان نصف الفصلي‬
‫‪ %25‬تمارين‪+‬مشروع‬
‫‪6‬‬
‫متطلبات المقرر‬
‫اإللمام بإحدي لغات البرمجة وإحدي برامج إدارة نظم قواعد‬
‫البيانات‪.‬‬
‫‪7‬‬
‫أهداف المقرر‬
‫التعرف على قواعد البيانات ولماذا االتجاه الي استخدام نظم‬
‫قواعد البيانات بدالً عن النظم التقليدية القديمة ثم كيفية تصميم‬
‫قاعدة بيانات جديدة وبأسس علمية صحيحة و التعرف علي‬
‫لغات برامج نظم قواعد البيانات‪.‬‬
‫‪8‬‬
‫المراجع المستخدمة‬
:‫المرجع االساسي‬
Elmasri and Shamkant. "Fundamentals of
Database Systems".
5th edition.
:‫مرجع إضافي‬
Thomas M. Connolly &Carolyn
E.Begg"DataBase Systems,A practical
Approach To Design ,Implementation
,And Management .
Second Edition.
Two references are published by Addison
Wesley .
9
Database Systems in two words
This course is an introduction to database systems
Considers questions how to design, implement and use
data intensive computer applications.
Majority of the practical tasks in industry require applying
relatively not complex algorithms to huge amounts of
well structured data.Accordingly, efficiency of an
application depends on the quality of (logical and
physical) data organization.
10
Why Learn Database Systems
Wherever you go, there are databases:
– Student records
– Airlines and hotels reservations,
– Bank accounts
– One of the first things people will ask you in an job interview is:
“What do you know about databases?”
Databases are a matured area with a sound theoretical
foundation and great practical knowledge.
11
‫محتويات المقرر‬
‫ المفاهيم العامة لقواعد البيانات ومستخدمي قواعد البيانات‬.1
Chapter (1):Databases and Database Users
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
Introduction.
An example.
Characteristics of Database Approach.
Actors on the Scene.
Workers behind the Scene
Advantages of using a DBMS
A Brief History of DB Application
When Not to Use a DBMS
12
Cont.
‫مفاهيم نظم قواعد البيانات ومعماريتها‬.2
Chapter(2) : Database Systems Concepts and
Architecture
2.1Data Models, Schemas, and Instances.
2.2Three-schema Architecture and Data Independence.
2.3 Database Languages and Interfaces.
2.4 The Database system environment
2.5 Centralized and Client /Server Architecture for
DBMSs.
2.6 Classification of Database Management Systems
13
Cont.
‫ نموذج الكائنات المرتبطة‬.3
Chapter(3): Data Modeling Using The Entity Relationship
(ER) Model.
3.1 Using High-Level Conceptual Data Models for Database
Design.
3.2 An Example Database Application
3.3 Entity Types, Entity Sets, Attributes, and Keys.
3.4 Relationships, Relationship Types, Roles, and Structural
Constraints.
3.5 Weak Entity Types.
3.6 Refining the ER Design for the COMPANY Database.
3.7 ER Diagrams, Naming Conventions, and Design Issues.
3.9 Relationship Types of degree higher than two.
14
Cont.
.‫ نموذج الكائنات المرتبطة المحسن‬4
Chapter(4): Enhanced Entity Relationship(EER)
Model.
15
Cont.
‫النموذج العالئقي‬.5
Chapter(5): Relational data Model and
Relational Constraints.
5.1 Relational Data Model Concepts.
5.2 Relational Constraints and Relational Database
Schemas.
5.3 Update Operations ,transaction ,and Dealing with
Constraints Violations
16
‫ الجبر العالئقي‬6
Chapter(6) the Relational Algebra AND relational
calculus
6.1 Unary Relational Operations.
6.2 Relational Algebra Operations from set theory.
6.3 binary Relational Operations .
6.6 The Tuple Relational calculus.
6.7 the Domain Relational Calculus.
17
cont.
‫ تحويل نموذج الكائنات الي النموذج العالئقي‬.7
Chapter (7): ER to Relational Mapping.
7.1 Relational DB Design Using ER –to- Relational
Mapping.
7.2 Mapping EER Model Constructs to Relation.
18
cont.
‫ لغة االستفسارات الهيكلية‬.8
Chapter (8) :Structured Query Language SQL99:schema Definition, constraints , queries, and views.
8.1 SQL Data Definitions , Constraints and data types
8.2 Specifying Constraints In SQL.
8.3 schema change statements in SQL.
8.4 Basic Queries in SQL.
8.5 More Complex SQL Queries.
8.6 Insert . Delete , And Update Statements In SQL.
8.8 Views (Virtual Table) In SQL.
19
Cont.
‫ االعتماد الدالي والتبسيط‬.9
Chapter(10):Functional Dependencies and
Normalization for relational Databases.
10.1 Informal Design Guidelines for Relation
Schemas.
10.2 Functional Dependencies.
10.3 Normal forms Based on Primary Keys.
10.4 General Definitions of Second and Third Normal
Forms.
20
‫بسم هللا الرحمن الرحيم‬
‫‪21‬‬
Lecture(2)
Chapter 1
Introduction: Databases and
Database Users
22
Introduction
‫مكاتب‬,‫ الموؤسسات التعليمية‬, ‫تستخدم نظم قواعد البيانات في معظم الموؤسسات المختلفة كالبنوك‬
‫الخ‬..........‫المصانع‬,‫الحجز الجوي‬
(databases plays a critical rule in almost all areas where computers are
used ,including business ,ecommerce, engineering, medicine,…etc)
.)numbers) ‫) اوارقام‬text) ‫ البيانات إما نصوص‬:‫التطبيقات التقليدية لنظم قواعد البيانات‬
:‫امثلة للتطبيقات الحديثة لنظم قواعد البيانات‬
Multimedia DB systems
Geographical information system (GIS)
Data Warehousing And Data Mining
Interaction Between DB And WWW
Real time and Active DBs
Temporal DBs
•First part of book focuses on
traditional applications.
•A number of recent applications
are described later in the book
(for example, Chapters
24,26,28,29,30)
23
General concepts
Definition of DB : collection of related data.
.(‫)قاعدة البياتات هي عبارة عن مجموعة من البيانات ذات العالقة ببعضها البعض‬
this defn is more general.
Essential database characteristics are:
–
–
–
–
–
–
Represents an aspect of the real world (miniworld, UoD),
Well structured (even has a strict regular structure),
Reflects (or should reflect) current state of the UoD,
Has users and applications,
Stored in a permanent (persistent) computer memory, and
Accessed and manipulated using a DBMS
All these characteristics have to be met
UOD :universe of discourse
Mini-world:
– Some part of the real world about which data is stored in a database.
24
What Is a Database?
Real
World
Facts about world and behaviour of world
represented in
contents and operations of a database
Student
LName
Database
FName
StudId
Major
Ahmed
Amna
04-12
Comp
Ali
Osman
02-34
Math
A/ Alla
Esraa
06-20
Stat
Users
25
Another definition:
A DB is a shared collection
of logically related data (and description of this data
),designed to meet the information needs of an
organization.
26
Definition of Data (Datum)
Data is a value of a property of an individual UoD object
or a relationship (between two UoD objects) at a
particular period of time.
Example
UoD
object(s)
Property
Time
Value
Ali
Age
Feb
2005
21
Ali &
CompSci dep
Number of
Points
Feb 2005
240
27
Another def of data:
Data:
– Known facts that can be recorded and have an implicit meaning
A database can be of any size and complexity, and it may be generated
manually or computerized.
28
Database Management System (DBMS)
‫نظم ادارة قواعد البيانات‬
A collection of programs that enable:
– Defining (describing the structure),
– Constructing (populating by data),
– Manipulating (querying, updating),
– Preserving consistency,
– Protecting from misuse,
– Recovering from failure, and
– Concurrent using
of a database.
29
Another definition
DBMS: A software system that enables
users to define ,create, maintain the
database and provides controlled access
to this database.
DBMS either general purpose or specific
purpose software.
30
Database systems
‫نظم قواعد البيانات‬
DB+DBMS
DB system
Database System:
The DBMS software together with the data itself. Sometimes, the
applications are also included.
31
Example of DB
32
Example of DB
Mini-world for the example:
– Part of a UNIVERSITY environment.
Some mini-world entities:
– STUDENTs
– COURSEs
– SECTIONs (of COURSEs)
– (academic) DEPARTMENTs
– INSTRUCTORs
33
Example of DB
Some mini-world relationships:
–
–
–
–
–
–
SECTIONs are of specific COURSEs
STUDENTs take SECTIONs
COURSEs have prerequisite COURSEs
INSTRUCTORs teach SECTIONs
COURSEs are offered by DEPARTMENTs
STUDENTs major in DEPARTMENTs
34
Characteristics of the DB approach
Difference between DB systems(DB approach) and filebased systems (traditional file processing).
Definition of file-based system: A collection of
application programs that perform services for the
end-users such as production of reports. Each
program defines and manages its own data.
35
An example of file based system
Academic office
Data entry
and
reports
File
handling
routines
File
File
definition
definition
Account office
Data entry
and
reports
Academic office files
File
handling
routines
File
definition
Account office files
36
‫ماهي عيوب نظم الملفات التقليدية؟‬
1.
2.
3.
4.
5.
Separation and isolation of data.
Duplication of data.
Data dependence.
Incompatibility of files.
Fixed queries.
37
DB Approach
Arose because:
– Definition of data was embedded in application programs, rather
than being stored separately and independently.
– No control over access and manipulation of data beyond that
imposed by application programs.
Result:
– the database and Database Management System (DBMS).
38
DB Approach
DB SYSTEM
Academic office
Data entry
and
reports
DB
Academic
application
programs
DBMAS
Account office
Data entry
and
reports
Academic
office files
and
account
office files
and files
defn
Account
application
programs
39
Comparison between DB approach and TFP
approach
1.
•

•
•
The main characteristics of DB approach versus TFP
approach are:
Self-Describing Nature of a Database System –
In addition to DB ,DB system has a complete definition or description of the
database structure and constraints.
DB definition is stored in the system catalog, which contains information
such as the structure of each file, the type and storage format of each
data item, and various constraints on the data.
This allows the DBMS software to work with different database
applications.
(Fig 1.1).
In TFP .data defn is a part of application program , ie these programs are
constrained to work with only on DB.
40
An example of a database catalog
41
Cont.
2.
•
•
•
•
Insulation between Programs and Data
Called program-data independence.
Allows changing data structures and storage organization
without having to change the DBMS access programs.
The structure of data files is stored in the DBMS catalog
separately from the access programs.
In TFP changes the structure of file may require changing
all access programs.
42
Cont.
3.
4.
Data Abstraction: A data model is used to hide storage
details and present the users with a conceptual view
of the database.
Support of Multiple Views of the Data - Each users
may see a different view of the database, which
describes only the data of interest to that user. (fig 1.5)
43
44
Cont.
Sharing of data and multi-user transaction
processing:
– Allowing a set of concurrent users to retrieve from and to
update the database.
– Concurrency control within the DBMS guarantees that
each transaction is correctly executed or aborted.
– Recovery subsystem ensures each completed transaction
has its effect permanently recorded in the database.
– OLTP (Online Transaction Processing) is a major part of
database applications. This allows hundreds of concurrent
transactions to execute per second.
45
‫بسم هللا الرحمن الرحيم‬
‫‪46‬‬
Lecture(3)
Chapter 1
Introduction: Databases and
Database Users
47
Database Users
Users may be divided into
– Those who actually use and control the
database content, and those who design,
develop and maintain database applications
(called “Actors on the Scene”), and
– Those who design and develop the DBMS
software and related tools, and the computer
systems operators (called “Workers Behind
the Scene”).
48
Actors on the Scene
Database administrator (DBA):
1-Administering the primary resource of DBsystem (DB itself)
and DBMS (secondary resource).
2- responsible for authorizing access to the db, for coordinating
and monitoring its use.
3- responsible for acquiring software and hardware when
needed.
4-Solving the problems of the system such as security or poor
system response time.
49
Cont.
DB designers:
Responsible to define the content, the structure, the
constraints, and functions or transactions against the
database. They must communicate with the end-users
and understand their needs.
.
50
Categories of End-users
End users: -
people whose jobs require access to the
database for querying, updating, and generating reports.
There are several categories of end users :
1.
2.
Casual end users (not formal): access db from time to
time (like managers)
Naive or Parametric: they make up a large section of the
end-user population.
– They use previously well-defined functions in the
form of “canned transactions” against the
database.
– Examples are bank-tellers or reservation clerks.
51
Categories of End-users (continued)
4. Sophisticated:
– These include business analysts, scientists, engineers,
others thoroughly familiar with the system capabilities.
– Many use tools in the form of software packages that
work closely with the stored database.
5. Stand-alone:
– Mostly maintain personal databases using ready-to-use
packaged applications.
– An example is a tax program user that creates its own
internal database.
52
Cont.
System Analysts and Application Programmers
(Software Engineers) - System analysts determine
the requirements of end users, and develop
specifications for transactions. Application programmers
implement these specifications and programs. Such
analysts and programmers nowadays called software
engineers.
read section 1.5.
53
Advantages of using DBMS
1.
Controlling redundancy:
redundancy means that storing the same data multiple times in the DB.

Redundancy problems:
1-Duplication of efforts.
2-storage space is wasting
3-Inconsistency of data

In DB approach: store each logical data item (such as a student's
name) in only one place.

In some cases redundancy is necessary to improve system
performance and DBMS has capability to control such
redundancy. FIGURE(1.6)
54
55
Cont.
2. Restricting Unauthorized Access:the DBA uses security and authorization subsystem
(provided by DBMS) in order to specify account
restrictions.
3. Providing Persistent Storage for Program Objects
and Data Structures. Compatibility between ODBMS (object
store) and OOPL(C++,JAVA).
4.
Permitting Inferencing and actions Using Rules :capability for defining deduction rules for inferencing new information
from the stored database facts. (active DB system ).
56
CONT.
5. Providing Storage Structures For Efficient Query
•
•
•
•
Processing:
DB is stored on disk, so DBMS must provide specialized
data structures to speed up disk search.
DBMS often has a buffering module that maintains parts
of the DB in memory buffers.
Query processing and optimization module of the
DBMS is responsible for choosing an efficient query
execution plan for each query.
Choosing appropriate data structure is a part of physical
DB design and tuning . (responsibility of DBA staff).
57
Cont.
6.
Providing Multiple User Interfaces :-
to satisfy users with varying levels of technical knowledge.
Interfaces for casual users, form style and menu driven
(GUIs).
7.
Representing Complex Relationships Among
Data:A DBMS must have the capability to represent a
variety of complex relationships among the data as
well as to retrieve and update related data easily and
efficiently.
58
Cont.
7.
Enforcing Integrity Constraints:capability for defining and enforcing constraints
(e.g., specifying a data type for each data item).
8.
Providing Backup and Recovery:A DBMS must provide facilities for recovering from
hardware or software failures.
The backup and recovery subsystem o f a DBMS is
responsible for recovery.
59
Additional Implications of Using the Database
Approach
Potential for enforcing standards:
– This is very important for the success of database
applications in large organizations. Standards refer to
data item names, display formats, screens, report
structures, meta-data (description of data), Web page
layouts, etc.
Reduced application development time:
– Incremental time to add each new application is reduced.
60
Additional Implications of Using the Database
Approach (continued)
Flexibility to change data structures:
– Database structure may evolve as new requirements are
defined.
Availability of current information:
– Extremely important for on-line transaction systems such
as airline, hotel, car reservations.
Economies of scale:
– Wasteful overlap of resources and personnel can be
avoided by consolidating data and applications across
departments.
61
Historical Development of Database
Technology
Early Database Applications:
– The Hierarchical and Network Models were introduced in
mid 1960s and dominated during the seventies.
– A bulk of the worldwide database processing still occurs
using these models, particularly, the hierarchical model.
Relational Model based Systems:
– Relational model was originally introduced in 1970, was
heavily researched and experimented within IBM
Research and several universities.
– Relational DBMS Products emerged in the early 1980s.
62
Historical Development of Database
Technology (continued)
Object-oriented and emerging applications:
– Object-Oriented Database Management Systems
(OODBMSs) were introduced in late 1980s and early
1990s to cater to the need of complex data processing in
applications.
– Many relational DBMSs have incorporated object database
concepts, leading to a new category called objectrelational DBMSs (ORDBMSs)
– Extended relational systems add further capabilities (e.g.
for multimedia data, XML, and other data types)
63
Historical Development of Database
Technology (continued)
Data on the Web and E-commerce Applications:
– Web contains data in HTML (Hypertext markup language)
with links among pages.
– This has given rise to a new set of applications and Ecommerce is using new standards like XML (eXtended
Markup Language). (see Ch. 27).
– Script programming languages such as PHP and
JavaScript allow generation of dynamic Web pages that
are partially generated from a database (see Ch. 26).
Also allow database updates through Web pages.
64
Extending Database Capabilities
New functionality is being added to DBMSs in the following areas:
– Scientific Applications
– XML (eXtensible Markup Language)
– Image Storage and Management
– Audio and Video Data Management
– Data Warehousing and Data Mining
– Spatial Data Management
– Time Series and Historical Data Management
The above gives rise to new research and development in
incorporating new data types, complex data structures, new
operations and storage and indexing schemes in database systems.
65
When Not To Use a DBMS
Disadvantages Of DBMS:
High initial investment in hardware, software, and
training.
Generality that a DBMS provides for defining and
processing data.
Overhead for providing security, concurrency control,
recovery, and integrity functions.
66
cont.
The following are the situations where the
using of a DBMS is not suitable:1.
2.
3.
The database and applications are simple, well
defined, and not expected to change.
Real-time requirements for some program may not be
met because of DBMS overhead.
Multiple-user access to data is not required.
67
cont.
When no DBMS may suffice:
– If the database system is not able to handle the complexity
of data because of modeling limitations.
– If the database users need special operations not
supported by the DBMS.
68
Good bye
chapter one
69
Chapter(2) :
Database system concepts
and architecture.
70
Data Models
Database approach provides some level of data abstraction
by hiding details of data storage.
Data Model:
– A set of concepts to describe the structure of a database,
the operations for manipulating these structures, and
certain constraints that the database should obey
)‫– (مجموعة من المفاهيم تستخدم لوصف بنية قاعدة البيانات‬
Data Model Structure and Constraints:
– Constructs are used to define the database structure
– Constructs typically include elements (and their data
types) as well as groups of elements (e.g. entity, record,
table), and relationships among such groups
– Constraints specify some restrictions on valid data; these
constraints must be enforced at all times
71
Data Models (continued)
Data Model Operations:
– These operations are used for specifying database
retrievals and updates by referring to the constructs of the
data model.
– Operations on the data model may include basic model
operations (e.g. generic insert, delete, update) and userdefined operations (e.g. compute_student_gpa,
update_inventory)
72
Categories of data model
1.
High-level or conceptual data models
2.
Low-level or physical data models
Representational or implementation data models
3.
73
High-level or conceptual data models
‫المفاهيم في هذا النوع اقرب لفهم المستخدم العادي(غير المتخصص في الحاسوب) ولذا‬
.‫ال تطبق هذه المفاهيم مباشرة ً فى الحاسوب‬
– Provide concepts that are close to the way many users
perceive data.
(Also called entity-based or object-based data
models.)
.‫يتم تحديد هذا النموذج في الخطوة الثانية لتصميم قاعدة البيانات‬
(entity relationship model(‫نموذج الكائنات‬:‫امثلة‬
)enhanced entity relationship model ) ‫نموذج الكائنات المحسن‬
74
Low-level or physical data models
‫المفاهيم في هذا النوع تتعلق بوصف كل التفاصيل عن كيفية تخزين‬
.‫البيانات في الحاسوب‬
‫تنظيم السجالت وممرات الوصول للسجالت‬,‫يوصف نسق السجالت‬
(access paths(
– Provide concepts that describe details of how data is stored in
the computer. These are usually specified in an ad-hoc
manner through DBMS design and administration manuals
unifying model, frame memory: ‫امثلة‬
75
‫‪Representational or implementation data‬‬
‫‪models‬‬
‫المفاهيم في هذا النوع قد تفهم بواسطة المستخدم العادي‪ .‬ولكن التخفي كل تفاصيل‬
‫التخزين و تطبق هذه المفاهيم مباشرة ً فى الحاسوب‪.‬‬
‫اي ‪ DBMS‬لها نموذج تطبيقي خاص بها‪.‬‬
‫تستخدم في هذه النماذج السجالت لوصف البيانات ولذ يطلق عليها‬
‫‪(Record-based data models).‬‬
‫‪Provide concepts that fall between the above two, used by many‬‬
‫‪commercial DBMS implementations .‬‬
‫امثلة‪:‬‬
‫‪(relational model‬يستخدم في معظم ال ‪DBMS‬الحالية)‪.‬‬
‫‪(Hierarchical and network models‬تستخدم في االنظمة القديمة)‪.‬‬
‫‪(Object models‬تستخدم في ال‪)ODBMS‬‬
‫‪76‬‬
Schemas, Instances, and Database State
‫وصف قاعدة‬:)database schema) ‫– خارطة قاعدة البيانات‬
(description of database) ‫البيانات‬
Includes descriptions of the database structure, data types,
and the constraints on the database.
‫تحدد خارطة قاعدة البيانات اثناء تصميم قاعدة البيانات وهي نادرا ً مايحدث بها تحديث‬
.
(diagram(‫في معظم نماذج البيانات تكون الخارطة عبارة عن رسم توضيحي‬
Schema Diagram:
– An illustrative display of (most aspects of) a database
schema.
77
Cont.
Schema Construct:
– A component of the schema or an object within the
schema, e.g., STUDENT, COURSE.
78
Schemas versus Instances
Database State:
– The actual data stored in a database at a particular
moment in time. This includes the collection of all the
data in the database.
.‫– البيانات في قاعدة البيانات فى لحظة معينة‬
– Also called database instance (or occurrence or snapshot).
The term instance is also applied to individual
database components, e.g. record instance, table
instance, entity instance.
79
Database Schema
vs. Database State
Database State:
– Refers to the content of a database at a moment in
time.
Initial Database State:
– Refers to the database state when it is initially loaded
into the system.
Valid State:
– A state that satisfies the structure and constraints of
the database.
(valid state) ‫ مهمتها التأكد ان حالة قاعدة البيانات دائما ً صحيحة‬DBMS ‫– ال‬
.‫وذلك بعدم خرق أي قيد من القيود الموضحة على الخارطة‬
80
Database Schema
vs. Database State (continued)
Distinction
– The database schema changes very infrequently.
– The database state changes every time the database is
updated.
Schema is also called intension.
State is also called extension.
81
Example of a Database Schema
82
Example of a database state
83