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

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: Tuesday 10-2. Email :ekambal@uofk.edu. • مساعدى التدريس:

5. Cont. • مدة الدراسة : • محاضرة واحدة أسبوعيا لمدة اربعة عشر أسبوعا ، مدة المحاضرة ساعتان . • تقييم المقرر: • 60% الامتحان النهائي • 20% الامتحان نصف الفصلي • 5% الحضور • 15% تمارين

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 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

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.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 AlgebraOperations from set theory 6.3 binary Relational Operations

17. cont. 7. تحويل نموذج الكائنات الي النموذج العلائقي Chapter (7): ER to Relational Mapping 7.1 relational DB design using ER –to- Relational Mapping.

18. 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.

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. تمارين • (1)قراءة ( chapter(1 . • (2) تمرين معملي (1) : أكتب برنامجا بلغة C++ ينشئ ملفا لتخزين بيانات 30 طالب حيث يحتوي أي سجل للطالب علىخمسة حقول إسم الطالب, رقمه,عامه الدراسي , عمره, وعنوانه.وايضاً يقوم هذا الملف بتخزين بيانات 20 مقرر تدرس للطلاب, أي سجل للمقرر يحتوي على ثلاثة حقول إسم المقرر ,رقم المقرر,والسنة الدراسية التي يدرس بها. بعد كتابة البيانات في الملف تأكد من أنه تم حفظها بصورة سليمة عن طريق قراءتها من الملف وإستعراضها على الشاشة على شكل تقرير به اسماء الطلاب وارقامهم واسماء المقررات التي يدرسونها . ثم استعرض ايضاً اسماء طلاب السنة الاولىمرتبة حسب الاعمار.

21. CHAPTER (1)

22. Introduction • تستخدم نظم قواعد البيانات في معظم الموؤسسات المختلفة كالبنوك , الموؤسسات التعليمية,مكاتب الحجز الجوي,المصانع..........الخ (databases plays a critical rule in almost all areas where computers are used ,including business ,ecommerce, engineering, medicine,…etc) • التطبيقات التقليدية لنظم قواعد البيانات: البيانات إما نصوص text)) اوارقام numbers)). • امثلة للتطبيقات الحديثة لنظم قواعد البيانات: • Multimedia DB systems • Geographical information system (GIS) • Data Warehousing And Data Mining • Interaction Between DB And WWW • Active DBs • Temporal DBs

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

24. What Is a Database? Real World Facts about world and behaviour of world represented in contents and operations of a database Users Database

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

27. تختلف أحجام قواعد البيانات كما تختلف درجة تعقيدها.

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

31. Example of DB

32. 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.

33. File handling routines Data entry and reports Academic office File definition Academic office files File handling routines Account office Data entry and reports File definition Account office files An example of file based system File definition

34. ماهي عيوب نظم الملفات التقليدية؟ • Separation and isolation of data. • Duplication of data. • Data dependence. • Incompatibility of files. • Fixed queries

35. Comparison between DB approach and TFP approach • 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)

36. An example of a database catalog Relations Columns

37. Cont. • 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.

38. Cont. • 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)

39. بسم الله الرحمن الرحيم Lecture( 2)

40. 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.

41. 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.

42. 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.

43. Cont. • 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(.

44. 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.

45. Advantages of using DBMS • 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)

46. Cont. • Restricting Unauthorized Access:- the DBA uses security and authorization subsystem (provided by DBMS) in order to specify account restrictions. • 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 ).

47. CONT. • 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 .

48. Cont. • 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.

49. Cont. • Enforcing Integrity Constraints:- capability for defining and enforcing constraints (e.g., specifying a data type for each data item). • Providing Backup and Recovery:- ADBMSmust 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.

50. 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.