Lectures Notes in

Database Management Systems

Lecture (1)
Submitted By:

Dr. Ahmed Mohamed Abd-Elwahab

Lecture in BIS Department,
Faculty of Commerce & Business Administration,
Helwan University, Cairo, Egypt.
 COURSE OUTLINE
❑ Chapter 1. Introduction to DBMS.

❑ Chapter 2. Relational Model.

❑ Chapter 3. Entity Relational Model.
❑ Chapter 4. Enhances Entity Relational Model.
❑ Chapter 5. Relational Database Design (Mapping).
❑ Chapter 6. SQL Basic.
2
 CHAPTER (1)

Introduction to DBMS

3
 Ch 1. Introduction to DBMS
OUTLINE (Part One)
❑ Introduction.
❑ Basic Definitions.
❑ Purpose of Database Systems.
❑ Disadvantages of the File Processing Systems.
❑ Main Characteristics of the Database Systems.
❑ Database Systems Applications.

4
 Ch 1. Introduction to DBMS
OUTLINE (Part Two)
❑ Data Abstraction.
❑ Instances and Schemas.
❑ Data Models Categories.
❑ Database Design.
❑ Data Storage and Querying.
❑ Transaction Management.
❑ Data Mining & Information Retrieval.
❑ Classification of DBMS. 5
 Information System Components

Data Information
(Raw Facts/Materials) (Processed Data)
that can be stored
Information = Decision

Feedback
6
Data Vs. Information

7
 ‫تخزين البيانات بشكل منظم ومرتب‬

‫استرجاع البيانات بسرعة وسهولة ودقة‬

‫‪8‬‬
Flat File Environment
Database System
File Processing
 Basic Definitions
➢ Database (DB):
▪ A Collection of Related Data.
➢ Mini-world:
▪ Some part of the real world about which data is stored in a database.
For example, Student grades and transcripts at a university.
➢ Database Management System (DBMS):
▪ A Software Package/System to facilitate the Creation and
Maintenance of a computerized database.
➢ Database System: (Database + DBMS)
▪ The DBMS Software together with the Data itself.
Sometimes, the applications are also included.
10
11
12
1.5 Database Systems Applications

13
Data Abstraction in DBMS is a
Process of Hiding Irrelevant
Details from Users. Because
database systems are made of
Complex data structures. so, it
makes accessible the user
interaction with the database. 14
1.6 Data Abstraction
The Three Levels of Data Abstraction:

15
16
1.7 Instances and Schemas
❑ Instance of the Database is the collection of
information stored in the database at a
particular moment (Snapshot of the data).

❑ Database Schema is the Overall design of

the database (logical design). A schema is a
collection of Objects (Entities) that provides
a logical classification of objects in the database.
17
18
1.8 Data Models
❑ Data Model is a collection of Conceptual
tools for describing data, relationship
between data and constraints.
❑ Data Models help in describing the
Structure of the Database.

19
1.8 Data Models Categories
The DATA MODELS can be classified into
Four different categories:
1. Relational Model.

2. Entity Relationship Model.

3. Object Based Data Model.

4. Semi Structured Data Model.

20
1.9 Database Design

21
1.12 Data Mining & IR
❑ Data Mining
➢ Is a Process of discovering knowledge from data
also called Machine Learning or Statistical Analysis.
➢ DM attempts to discover rules and patterns from
data; “Knowledge discovery in Databases”.

22
1.12 Data Mining & IR
❑ Information Retrieval (IR)
➢ Is a Software Program/System that deals with the
Organization, Storage, Retrieval, and Evaluation of
information from document repositories;
“Querying of unstructured textual data”.

23
1.13 Classification of DBMS
❑ Database Management Systems can be
Classified based on Several criteria, such as:
1. Data Model,

2. User Numbers,

3. Database Distribution.

24
 CHAPTER (2)
Relational Model

25
 Ch 2. Relational Model
OUTLINE
❑ Introduction.
❑ Relational Model Concepts.
❑ Relational Database Schema.
❑ Relational Model Constraints.
→ Domain Constraints.
→ Key Constraints.
→ Constraints on NULL/ Not NULL Values.
→ Entity Integrity and Referential Integrity Constraints.
❑ Schema Diagrams. 26
A Sample of Relational Database

27
 Definition Summary
Informal terms Formal terms
Table Relation
Association or Correlation Relationship between relations
Column or Field Attribute
Row or Record Tuple = Instance
A set of valid values - atomic values Domain = Constraints
(A set of all possible values for an attribute) (Data types)
Type Definition Relation Schema
A specific set of rows Instance Schema
28
 CHAPTER (3)
Entity Relationship Modeling (ERD)

29
 Ch 3. Entity Relationship Modeling
OUTLINE (Part One)
❑ Introduction.
❑ The Entity Relationship Model Concepts.
→ Entities.
→ Attributes of Entities.
→ Relationship between Entities.
❑ Cardinality of Relationship (Constraints).
→ One-to-One (1:1).
→ One-to-Many (1:N)
→ Many-to-Many (N:N) or (N:M). 30
 Ch 3. Entity Relationship Modeling
OUTLINE (Part Two)
❑ Degree of Relationship:
→ Unary Relationship (degree 1)
→ Binary Relationship (degree 2)
→ Ternary Relationship (degree 3)
❑ Attributes of Relationship.
❑ Associative Entities.
❑ Strong & Weak Entity Types.
❑ Example COMPANY Database.
❑ Some of the Automated Database Design Tools. 31
 Entity Relationship Model
❑ Entity Relationship Model (ER) describes data in
terms of the following.
1. Entities
2. Attributes of Entities
3. Relationship between Entities

32
3.2 The Entity Relationship Model

33
3.2 The Entity Relationship Model
1. Entities
➢ An entity is an Object that exists in real world, and which
is distinguishable from other Objects.
➢ An entity can be a Person, an Object, a Place, an Event, or
a Concept about which an organization wishes to maintain
data. The following are some examples of entities:
Person: STUDENT, EMPLOYEE, CLINT, CUSTOMR…
Object: MOBILE, CAR, MACHINE, AIRPLANE…
Place: CITY, NATIONAL PARK, ROOM, WAREHOUSE…
Event: MARRIGE, LEASE, WAR…
Concept: COURSE, PROJECT, ACCOUNT…
34
 Entity Relationship Model
1. Entities
Entity type: Is a collection of Entities that have the Same attributes.

Entity Instance: Is a Single item (Record = Tuple) in this Collection.

Entity Set: Set of Entity Instance.

35
 Entity Relationship Model
2. Attributes of Entities
➢ An entity is represented by a set of attributes.
➢ Attributes
Attributes are Property or Characteristic used to describe an entity.

STUDENT = {Student_ID, SSN, Name, Address, Phone, Email}

ORDER = {Order_ID, Order_Date, Order_Amount}

ACCOUNT = {Account_Number, Account_Type, Date, Balance}

CITY = {City_Code, City_Name, State}

36
 The Entity Relationship Model
❑ Types of Attributes of Entities: FName LName

1. Simple and Composite Attributes Name

2. Single-Valued and Multi-Valued Attributes

3. Stored and Derived Attributes

ID

4. Key (Identifier) Attribute STUDENT

37
3.2 The Entity Relationship Model
2. Attributes of Entities

38
3.2 The Entity Relationship Model
3. Relationship between Entities
➢ A Relationship relates two or more Distinct Entities with
a Specific Meaning.

39
3.3 Cardinality of Relationship
❑ The term Cardinal Number refers to the number
used in Counting. This mean that the ability to
Count the number of entities (Instance) involved in
a relationship.
1. One-to-One
2. One-to-Many
3. Many-to-One
4. Many-to-Many
40
3.3 Cardinality of Relationship
1. One-to-One (1:1)

2. One-to-Many (1:N)

3. Many-to-One (N:1)

4. Many-to-Many (M:M)
41
3.3 Cardinality of Relationship

42
3.4 Degree of Relationship
❑ The term Degree of Relationship refers to the
number of Participating Entity types.
❑ The three most common degrees of a relationship
in a database are:
1. Unary Relationship (degree 1)
2. Binary Relationship (degree 2)
3. Ternary Relationship (degree 3)
43
3.5 Attributes of Relationship
❑ Attributes on Relationships are like Attributes on
Entity types.
❑ An Attributes on a Relationship Stores information
related to the relationship.
❑ In ER Diagram, the attributes DataofOrder Collects
data for the relationship CUSTOMER places ORDER.

44
3.6 Associative Entities
❑ An Associated Entity is an Entity type that connects
the instances of one or more Entity types and
contains attributes particular to this association.
❑ An Associated Entity is a Relationship that has been
turned into an entity because it meet one of the
following conditions:
1. It is a Many-to-Many Binary Relationship.
2. It is a Ternary Relationship or a Relationship of an
even higher degree.
45
3.7 Weak Entity Types
❑ Entity Types can be classified into two categories:
1. Strong Entity Types 2. Weak Entity Types
➢ Independent of any Other Entity Types. ➢ Dependent on a Strong Entity Type.

➢ Always have a Primary Key. ➢ Does Not have any Primary Key.

➢ Represented by a Single Rectangle. ➢ Represented by a Double Rectangle.

➢ The relationship of two Strong Entities ➢ The relation between One Strong and
is represented by a Single Diamond. One Weak Entity is represented by a
S R S Double Diamond. S R w

➢ Strong Entity Type have either total ➢ Weak Entity Type always has total
participation or Not. participation.
46
3.7 Weak Entity Types

1 N

47
Summary of Notation for ER Diagrams

48
 CHAPTER (4)
Enhanced Entity
Relationship Modeling
(EERD)
49
Ch 4. Enhanced Entity Relationship Modeling
OUTLINE
❑ Introduction.

❑ Superclasses/Subclasses.

❑ Generalization/Specialization.

❑ Participation & Disjoint Constraints.

❑ Superclass/Subclass Inheritance (Hierarch-Lattice).

❑ Case Study: Conceptual Design for UNIVERSITY Database.

50
4.2 Superclasses/Subclasses
❑Superclass is an Entity Type that has one or more distinct
Subgroups (Subclasses) with Unique attributes.

❑Subclass is an Entity Type that has Shares common attributes

or Relationships distinct from other Subclasses.

51
4.3 Generalization/Specialization

(a) STAFF, FACULTY, and STUDENT entities before generalization

52
4.3 Generalization/Specialization

(b) PERSON superclass and FACULTY, STAFF, and STUDENT subclasses after generalization

53
4.3 Generalization/Specialization

(a) LIBRARY ITEM entity before specialization;

54
4.3 Generalization/Specialization

(b) LIBRARY ITEM superclass and BOOK, JOURNAL, and VIDEOCD subclasses after specialization
55
4.4 Participation & Disjoint Constraints
1. Participation (Completeness) Constraints
Total Participation Rule Partial Participation Rule
▪ Every member or instance of a ▪ Member of a Superclass does not have
Superclass must be (Mandatory) a to be member of any Subclass.
member of at least one Subclass. ▪ Membership is Optional.

56
4.4 Participation & Disjoint Constraints
2. Disjoint Constraints
‫ واحد فقط أم ال يف نفس الوقت؟‬Subclass ‫ الزم يكون يف‬Superclass ‫ الموجود يف‬Member ‫هل ال‬

Disjoint Rule Overlap Rule

▪ An instance of Superclass that is a ▪ An instance of a Superclass that is a
member of any Subclass, Cannot be an member of any Subclass, Can also
instance of any other Subclass at the belong to any other Subclass at the
same time. same time.

Letter “D” Letter “O”

57
4.5 Superclass/Subclass Inheritance
Hierarchy ‫بناء هرىم‬ Lattice ‫شبكة‬
▪ Hierarchy has a constraint that every ▪ A Subclass can be Subclass of more
Subclass has Only One Superclass; than One Superclass
.‫ واحد فقط‬Superclass ‫ بيكون له‬Subclass ‫بمعن أن كل‬ .‫ واحد أو أكث‬Superclass ‫ ممكن يكون له‬Subclass ‫بمعن أن كل‬

▪ (Called Single Inheritance); ▪ (Called Multiple Inheritance)

58
4.5 Superclass/Subclass Hierarchy

59
Enhanced Entity Relationship Modeling (EER)

60
CASE STUDY: Conceptual Design for UNIVERSITY Database

61
 CHAPTER (5)
Relational Database Design (Mapping)

62
 Ch 5. Relational Database Design
(Mapping)
❑ Introduction.
❑ ER-to-Relational Mapping algorithm:
Step 1. Mapping of Regular (Strong) Entity types.
Step 2. Mapping of Multi-Valued attributes.
Step 3. Mapping of Weak Entity types.
Step 4. Mapping of Binary 1 : 1 Relationship types.
Step 5. Mapping of Binary 1 : N Relationship types.
Step 6. Mapping of Binary M : N Relationship types.
Step 7. Mapping of N-ary Relationship types.
❑ Summary of Notation for Relational Mapping. 63
ER Model for COMPANY Database

64
Relational Database Design (Mapping)

65
Relational Database Design (Mapping)
Summary of Notation for Relational Mapping

66
 CHAPTER (6)
Structure Query Language
(SQL)
67
68
69
Thanks!

Ahmed Mohamed Abd-Elwahab

70