Welcome to ???304

1
??? ???? ?????? ??????
2
Lecture (1)
3
Welcome

• Welcome to ???304
• An Introduction to databases.
• We hope you will enjoy it.

4
Information about course

• ??? ?????? ????? ????? ???????? An
  Introduction to databases
• ??? ?????? ???304

5
Database course - People

• ??????? ?.????? ???? ?????.
• Office hours sunday 10-2.
• Email ekambal_at_uofk.edu.
• ?????? ???????

6
Cont.

• ??? ???????
• ?????? ????? ??????? ???? ????? ??? ?????? ? ???
  ???????? ?????? .

????? ?????? 60 ???????? ??????? 15 ????????
??? ?????? 25 ???????????
7
??????? ??????

• ??????? ????? ???? ??????? ????? ????? ????? ???
  ????? ????????.

8
????? ??????

• ?????? ??? ????? ???????? ?????? ??????? ???
  ??????? ??? ????? ???????? ????? ?? ?????
  ????????? ??????? ?? ????? ????? ????? ??????
  ????? ????? ????? ????? ? ?????? ??? ???? ?????
  ??? ????? ????????.

9
??????? ?????????

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

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

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

12
??????? ??????

• 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

13
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

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

15
Cont.

• 4 ????? ???????? ???????? ??????.
• Chapter(4) Enhanced Entity Relationship(EER)
  Model.

16
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

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

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

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

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

21
??? ???? ?????? ??????
22
Lecture(2) Chapter 1

• Introduction Databases and Database Users

23
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
• 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)

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

25
What Is a Database?
Real World
Facts about world and behaviour of
world represented in contents and operations of a
database
Student Student Student Student
LName FName StudId Major
Ahmed Amna 04-12 Comp
Ali Osman 02-34 Math
A/ Alla Esraa 06-20 Stat
Users
Database
26

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

27
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) Ali Ali CompSci dep
Property Age Number of Points
Time Feb 2005 Feb 2005
Value 21 240
28

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

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

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

31
Database systems??? ????? ????????

• DBDBMS DB system

Database System The DBMS software together with
the data itself. Sometimes, the applications are
also included.
32
Example of DB
33
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

34
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

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

36
An example of file based system
File definition
37

• ???? ???? ??? ??????? ??????????
• Separation and isolation of data.
• Duplication of data.
• Data dependence.
• Incompatibility of files.
• Fixed queries.

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

39
DB Approach
DB SYSTEM
Academic office
DB
Academic application programs
Academic office files and account office files
and files defn
DBMAS
Data entry and reports
Account office
Account application programs
40
Comparison between DB approach and TFP approach

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

41
An example of a database catalog
42
Cont.

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

43
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.5)

44
(No Transcript)
45
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.

46
??? ???? ?????? ??????
47
Lecture(3) Chapter 1

• Introduction Databases and Database Users

48
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).

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

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

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

52
Categories of End-users (continued)

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

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

54
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.6)

55
(No Transcript)
56
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 ).

57
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 . (responsibility
  of DBA staff).

58
Cont.

• Providing Multiple User Interfaces -
• to satisfy users with varying levels of
  technical knowledge.
• Interfaces for casual users, form style and menu
  driven (GUIs).
• 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.

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

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

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

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

63
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 object-relational DBMSs (ORDBMSs)
• Extended relational systems add further
  capabilities (e.g. for multimedia data, XML, and
  other data types)

64
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 E-commerce 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.

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

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

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

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

69

• Good bye chapter one

70

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

71
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

72
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 user-defined operations (e.g.
  compute_student_gpa, update_inventory)

73
Categories of data model

1. High-level or conceptual data models
2. Low-level or physical data models
3. Representational or implementation data models

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

75
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

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

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

78
Cont.

• Schema Construct
• A component of the schema or an object within the
  schema, e.g., STUDENT, COURSE.

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

80
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.
• ?? DBMS ?????? ?????? ?? ???? ????? ????????
  ?????? ????? (valid state) ???? ???? ??? ?? ???
  ?? ?????? ??????? ??? ???????.

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

82
Example of a Database Schema
83
Example of a database state