Transcript Document 7546695
ميحرلا نمحرلا الله مسب
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 Chapter (1):Databases and Database Users 1.1 Introduction 1.2 An example 1.3 Characteristics of Database Approach 1.4 Actors on the Scene 1.5 Workers behind the Scene 1.6 Advantages of using a DBMS 1.7 A Brief History of DB Application 1.8 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 –to Relational Mapping.
cont.
ةيلكيهلا تاراسفتسلاا ةغل .
8 Chapter (8) : Structured Query Language SQL 99: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.
نيرامت
.
chapter( 1 ( ةءارق (1) : ) 1 ( يلمعم نيرمت ) 2 (
ثيح بلاط مسإ 30 تانايب نيزختل افلم ئشني C++ ةغلب اجمانرب بتكأ هماع , همقر , بلاطلا مسإ لوقح ةسمخىلع بلاطلل لجس يأ يوتحي 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 Database
Student LName
Ahmed
FName
Amna Ali Osman A/ Alla Esraa Facts about world and behaviour of world
represented in
contents and operations of a database
StudId
04-12 02-34 06-20
Major
Comp Math 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 object(s) Property Time Value
Ali Age Feb 2005 21 Ali & CompSci dep Number of Points Feb 2005 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 file based 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 Academic office files Account office Data entry and reports 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
STUDENT COURSE SECTION GRADE REPORT PREREQUISITE
No-of-columns
4 4 5 3 2 Columns
Column-name
name
Data-type
Character(30) Student-number Class Major Character(4) Integer(1) Major-type
Belong-to relation
STUDENT STUDENT STUDENT 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.
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.4)
ميحرلا نمحرلا الله مسب
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.
1.
2.
3.
4.
End users: people whose jobs require access to 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.
4.
Providing Persistent Storage for Program Objects and Data Structures. Compatibility between ODBMS (object store) and OOPL(C++,JAVA). 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.
8.
Enforcing Integrity Constraints: capability for defining and enforcing constraints (e.g., specifying a data type for each data item). 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.
1.
2.
3.
The following are the situations where the using of a DBMS is not suitable: 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
specifying retrievals and updates on the database. for 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.
2.
3.
High-level
or
conceptual data models Low-level
or
physical data models Representational
or
implementation data models
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).
: ةلثما .) ةيلاحلا DBMS لا مظعم يف مدختسي ( relational model .) ةميدقلا ةمظنلاا يف مدختست ( Hierarchical and network models ) ODBMS لا يف مدختست ( Object models
Schemas, Instances, and Database State
ةدعاق فصو :) database schema ) تانايبلا ةدعاق ةطراخ ( description of database) تانايبلا تا نايبلا ةدعاق ميمصت ءانثا تانايبلا ةدعاق ةطراخ ددحت .
ثيدحت اهب ثدحيام ًاردان يهو مسر نع ةرابع ةطراخلا نوكت تانايبلا جذامن مظعم يف (diagram ( يحيضوت
تانايب ةدعاق ةطراخل لاثم
student Name Studnumber Class Major course Coursename Coursenumber Credithours Department Grade report Studnumber Coursenumber Semno Grade prerequisite Coursenumber Prerequisitenumber
Student
هلاعا ةطراخلل تانايبلا ةدعاق
Name Studnumber Class Ali 04-43 Osman 05-24 4 2 Major cs stat Course Coursename Introd to computer science Data structure Discrete math Into to DB Coursenumb er c102 c202 c201 c406 Credithours 4 4 3 3 Department Cs Cs Cs cs
Cont.
Grade report Studnumber Coursenumber Semno Grade 04-43 05-24 c406 c202 1 1 A B prerequisite Coursenumber c406 c202 Prerequisitenumber c102 c102
Database state
ب ىمست ةنيعم ةظحل ىف تانايبلا ةدعاق يف تانايبلا database state, or database snapshot or current set of occurrences or instances in DB.
اهل ( schema construct ) يأ هلاعا تانايبلا ةدعاق يف .
set of instances
: database schema لاو database state نيب قرفلا نوكت ة ظحللا هذه يف , ةطراخلا ًلاوا ددحت ةديدج تانايب ةدعاق ءاشتا دنع (empty state) ةيلاخ DBstate لا ةدعاق ةلاح لوحتت ةيلولاا تانايبلاب تانايبلا ةدعاق ليمحت درجمب new state ىلا لوحي ثيدحت يأ , initial state ىلا تانايبلا ةحيحص ًامئاد تانايبلا ةدعاق ةلاح نا دكأتلا اهتمهم ىلع ةحضوملا دويقلا نم ديق يأ قرخ مدعب كلذو DBMS لا (valid state) .
ةطراخلا .
حيحص ريغ ثيدحتو حيحص ثيدحتل لاثم تاه