Welcome to ???304

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_at_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 Algebra Operations 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-99schema 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??? ????? ????????

• DBDBMS 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
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-
• 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.

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.

51
cont.

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

52

• Good bye chapter one

53

• Chapter(2)
• Database system concepts and architecture.

54

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

55
Categories of data model

• High-level or conceptual data models
• Low-level or physical data models
• Representational or implementation data models

56
High-level or conceptual data models

• ???????? ?? ??? ????? ???? ???? ????????
  ??????(??? ??????? ?? ???????) ???? ?? ???? ???
  ???????? ??????? ?? ???????.
• ??? ????? ??? ??????? ?? ?????? ??????? ??????
  ????? ????????.
• ?????????? ????????((entity relationship model
• ????? ???????? ?????? enhanced entity
  relationship model ) )

57
Low-level or physical data models

• ???????? ?? ??? ????? ????? ???? ?? ???????? ??
  ????? ????? ???????? ?? ???????.
• ???? ??? ???????,????? ??????? ?????? ??????
  ???????
• ((access paths
• ????? unifying model, frame memory

58
Representational or implementation data models

• ???????? ?? ??? ????? ?? ???? ?????? ????????
  ??????. ???? ?????? ?? ?????? ??????? ? ???? ???
  ???????? ??????? ?? ???????.
• ?? DBMS ??? ????? ?????? ??? ???.
• ?????? ?? ??? ??????? ??????? ???? ???????? ???
  ???? ?????
• (Record-based data models).
• ?????
• relational model(?????? ?? ???? ?? DBMS ???????).
• Hierarchical and network models (?????? ??
  ??????? ???????).
• Object models(?????? ?? ??ODBMS)

59
Schemas, Instances, and Database State

• ????? ????? ???????? database schema))??? ?????
  ????????(description of database)
• ???? ????? ????? ???????? ????? ????? ?????
  ???????? ??? ?????? ?????? ??? ????? .
• ?? ???? ????? ???????? ???? ??????? ????? ?? ???
  ??????( (diagram

60
???? ?????? ????? ??????
student
course
Grade report
prerequisite
61
????? ???????? ??????? ?????
Student
Course
62
Cont.
Grade report
prerequisite
63
Database state

• ???????? ?? ????? ???????? ?? ???? ????? ???? ?
  database state, or database snapshot or current
  set of occurrences or instances in DB.
• ?? ????? ???????? ????? ??) schema construct (
  ??? set of instances .

64

• ????? ??? database state ??? database schema
• ??? ????? ????? ?????? ????? ???? ????? ???????
  ,?? ??? ?????? ???? ?? DBstate ????? (empty
  state)
• ????? ????? ????? ???????? ????????? ???????
  ????? ???? ????? ???????? ??? initial state ,??
  ????? ???? ??? new state
• ?? DBMS ?????? ?????? ?? ???? ????? ????????
  ?????? ????? (valid state) ???? ???? ??? ?? ???
  ?? ?????? ??????? ??? ???????.
• ??? ???? ?????? ???? ?????? ??? ????.