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

‫بسم هللا الرحمن الرحيم‬
Welcome
Welcome to 304‫حسب‬
An Introduction to databases.
We hope you will enjoy it.
Information about course
An Introduction to ‫ مبادئ قواعد البيانات‬: ‫إسم المقرر‬
databases
304‫حسب‬: ‫رمز المقرر‬
Database course - People
.‫ايمان محمد الحسن‬.‫ أ‬:‫المحاضر‬
Office hours: Tuesday 10-2.
Email :[email protected].
:‫مساعدى التدريس‬
‫‪Cont.‬‬
‫مدة الدراسة ‪:‬‬
‫محاضرة واحدة أسبوعيا لمدة اربعة عشر أسبوعا ‪ ،‬مدة‬
‫المحاضرة ساعتان ‪.‬‬
‫تقييم المقرر‪:‬‬
‫‪ %60‬االمتحان النهائي‬
‫‪ %20‬االمتحان نصف الفصلي‬
‫‪ %5‬الحضور‬
‫‪ %15‬تمارين‬
‫متطلبات المقرر‬
‫اإللمام بإحدي لغات البرمجة وإحدي برامج إدارة نظم قواعد‬
‫البيانات‪.‬‬
‫أهداف المقرر‬
‫التعرف على قواعد البيانات ولماذا االتجاه الي استخدام نظم‬
‫قواعد البيانات بدالً عن النظم التقليدية القديمة ثم كيفية تصميم‬
‫قاعدة بيانات جديدة وبأسس علمية صحيحة و التعرف علي‬
‫لغات برامج نظم قواعد البيانات‪.‬‬
‫المراجع المستخدمة‬
:‫المرجع االساسي‬
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 .
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.
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
‫محتويات المقرر‬
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
‫ المفاهيم العامة لقواعد البيانات ومستخدمي قواعد البيانات‬.1
Chapter (1):Databases and Database Users
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
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.6 Classification of Database Management
Systems
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.
Cont.
.‫ نموذج الكائنات المرتبطة المحسن‬4
Chapter(4): Enhanced Entity Relationship(EER)
Model.
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
‫ الجبر العالئقي‬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
cont.
‫ تحويل نموذج الكائنات الي النموذج العالئقي‬.7
Chapter (7): ER to Relational Mapping
7.1 relational DB design using ER –toRelational Mapping.
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.
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.
‫تمارين‬
‫)‪ (1‬قراءة ( ‪. chapter( 1‬‬
‫(‪ )2‬تمرين معملي (‪: )1‬‬
‫أكتب برنامجا بلغة ‪ C++‬ينشئ ملفا لتخزين بيانات ‪ 30‬طالب حيث‬
‫يحتوي أي سجل للطالب علىخمسة حقول إسم الطالب‪ ,‬رقمه‪,‬عامه‬
‫الدراسي ‪ ,‬عمره‪ ,‬وعنوانه‪.‬وايضا ً يقوم هذا الملف بتخزين بيانات ‪20‬‬
‫مقرر تدرس للطالب‪ ,‬أي سجل للمقرر يحتوي على ثالثة حقول إسم‬
‫المقرر ‪,‬رقم المقرر‪,‬والسنة الدراسية التي يدرس بها‪.‬‬
‫بعد كتابة البيانات في الملف تأكد من أنه تم حفظها بصورة سليمة عن‬
‫طريق قراءتها من الملف وإستعراضها على الشاشة على شكل تقرير‬
‫به اسماء الطالب وارقامهم واسماء المقررات التي يدرسونها ‪ .‬ثم‬
‫استعرض ايضا ً اسماء طالب السنة االولى مرتبة حسب االعمار‪.‬‬
CHAPTER (1)
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
Active DBs
Temporal DBs
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
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
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.
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
Ali
Ali &
object(s)
CompSci dep
Property Age
Number of
Points
Time Feb
Feb 2005
2005
Value
21
240
‫تختلف أحجام قواعد البيانات كما تختلف درجة تعقيدها‪.‬‬
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.
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.
Database systems
‫نظم قواعد البيانات‬
DB+DBMS
DB system
Example of DB
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.
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
‫ماهي عيوب نظم الملفات التقليدية؟‬
1.
2.
3.
4.
5.
Separation and isolation of data.
Duplication of data.
Data dependence.
Incompatibility of files.
Fixed queries
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 - it has a
complete definition or description of the database structure
and constraints. This 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 information stored in
the system catalog is called, Meta-data and it describes the
structure of the primary database. This allows the DBMS
software to work with different databases (Fig 1.1)
An example of a database catalog
Relations
Relation name
No-of-columns
STUDENT
4
COURSE
4
SECTION
5
GRADE REPORT
PREREQUISITE
3
2
Columns
Column-name
Data-type
Belong-torelation
name
Character(30)
STUDENT
Student-number
Character(4)
STUDENT
Class
Integer(1)
STUDENT
Major
Major-type
STUDENT
Cont.
2.
Insulation between Programs and Data and Data
Abstraction - Called program-data independence.
Allows changing data storage structures and
operations without having to change the DBMS
access programs. The structure of data files is
stored in the DBMS catalog separately from the
access programs.
Cont.
Data Abstraction: A data model is used to hide
storage details and present the users with a
conceptual view of the database.
4. 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.4)
3.
‫بسم هللا الرحمن الرحيم‬
‫)‪Lecture( 2‬‬
Cont.
5- Sharing of Data and Multiuser Transaction
Processing :
These types of application is called online
transaction processing applications(OLTP)
The DBMS must include concurrency control
software to ensure that the result of multiuser
access is correct.
Database system users
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.
Cont.
DB designers:
responsible for identifying the data to be stored and for choosing
appropriate structures to represent and store this data. This done
after communicate with all db users in order to understand their
requirements.
Cont.
End users: - people whose jobs require access to
1.
2.
3.
4.
the database for querying, updating, and generating
reports.
There are several categories of end users :
Casual end users (not formal): access db from time to
time (like managers)
Naïve or Parametric end users :querying and updates
db using standard types of queries (called canned
transaction).
Sophisticated end users like engineers, scientists.
Stand alone users: using ready made program
packages(for example tax package(.
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.
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.5)
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 ).
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 .
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.
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.
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.
Read section 1.7.
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.
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.
Good bye
chapter one
Chapter(2) :
Database system concepts
and architecture.
Database approach provides some level of data
abstraction by hiding details of data storage.
Data Model: A data model is a collection of concepts that
can be used to describe the structure of a database. By
structure of a database we mean the data types,
relationships, and constraints that should hold on the
data.
)‫(مجموعة من المفاهيم تستخدم لوصف بنية قاعدة البيانات‬
Some Data models include a set of basic operations for
specifying retrievals and updates on the database.
Data models also allows the database designer to
specify a set of valid user defined operations on the
database objects.
Categories of data model
1.
High-level or conceptual data models
2.
Low-level or physical data models
Representational or implementation data models
3.
‫‪High-level or conceptual data models‬‬
‫المفاهيم في هذا النوع اقرب لفهم المستخدم العادي(غير‬
‫المتخصص في الحاسوب) ولذا ال تطبق هذه المفاهيم مباشرة ً‬
‫فى الحاسوب‪.‬‬
‫يتم تحديد هذا النموذج في الخطوة الثانية لتصميم قاعدة‬
‫البيانات‪.‬‬
‫امثلة‪:‬نموذج الكائنات(‪(entity relationship model‬‬
‫نموذج الكائنات المحسن ) ‪)enhanced entity relationship model‬‬
‫‪Low-level or physical data models‬‬
‫المفاهيم في هذا النوع تتعلق بوصف كل التفاصيل عن كيفية تخزين‬
‫البيانات في الحاسوب‪.‬‬
‫يوصف نسق السجالت‪,‬تنظيم السجالت وممرات الوصول للسجالت‬
‫(‪(access paths‬‬
‫امثلة ‪unifying model, frame memory:‬‬
‫‪Representational or implementation data‬‬
‫‪models‬‬
‫المفاهيم في هذا النوع قد تفهم بواسطة المستخدم العادي‪ .‬ولكن التخفي كل‬
‫تفاصيل التخزين و تطبق هذه المفاهيم مباشرة ً فى الحاسوب‪.‬‬
‫اي ‪ DBMS‬لها نموذج تطبيقي خاص بها‪.‬‬
‫تستخدم في هذه النماذج السجالت لوصف البيانات ولذ يطلق عليها‬
‫‪(Record-based data models).‬‬
‫امثلة‪:‬‬
‫‪(relational model‬يستخدم في معظم ال ‪DBMS‬الحالية)‪.‬‬
‫‪(Hierarchical and network models‬تستخدم في االنظمة القديمة)‪.‬‬
‫‪(Object models‬تستخدم في ال‪)ODBMS‬‬
‫‪Schemas, Instances, and Database State‬‬
‫خارطة قاعدة البيانات )‪:)database schema‬وصف قاعدة‬
‫البيانات )‪(description of database‬‬
‫تحدد خارطة قاعدة البيانات اثناء تصميم قاعدة البيانات‬
‫وهي نادرا ً مايحدث بها تحديث ‪.‬‬
‫في معظم نماذج البيانات تكون الخارطة عبارة عن رسم‬
‫توضيحي(‪(diagram‬‬
student
‫مثال لخارطة قاعدة بيانات‬
Name Studnumber
Class
Major
course
Coursename
Coursenumber
Credithours
Grade report
Studnumber
Coursenumber Semno Grade
prerequisite
Coursenumber
Prerequisitenumber
Department
‫قاعدة البيانات للخارطة اعاله‬
Student
Name
Ali
Osman
Course
Studnumber
Class
Major
04-43
4
cs
05-24
2
stat
Coursename
Coursenumb Credithours
er
Department
Introd to computer c102
science
4
Cs
Data structure
c202
4
Cs
Discrete math
c201
3
Cs
Into to DB
c406
3
cs
Cont.
Grade report
Studnumber Coursenumber Semno Grade
04-43
05-24
prerequisite
c406
1
1
c202
Coursenumber
Prerequisitenumber
c406
c102
c202
c102
A
B
Database state
‫البيانات في قاعدة البيانات فى لحظة معينة تسمى ب‬
database state, or database snapshot or current
set of occurrences or instances in DB.
‫ (لها‬schema construct )‫في قاعدة البيانات اعاله أي‬
. set of instances
‫الفرق بين ‪ database state‬وال ‪: database schema‬‬
‫عند اتشاء قاعدة بيانات جديدة تحدد اوالً الخارطة ‪,‬في هذه اللحظة تكون‬
‫ال ‪ DBstate‬خالية)‪(empty state‬‬
‫بمجرد تحميل قاعدة البيانات بالبيانات االولية تتحول حالة قاعدة‬
‫البيانات الى ‪, initial state‬أي تحديث يحول الى ‪new state‬‬
‫ال ‪ DBMS‬مهمتها التأكد ان حالة قاعدة البيانات دائما ً صحيحة‬
‫)‪ (valid state‬وذلك بعدم خرق أي قيد من القيود الموضحة على‬
‫الخارطة‪.‬‬
‫هات مثال لتحديث صحيح وتحديث غير صحيح‪.‬‬