Section 1
(ERD)
Eng:Mohamed eldawansy
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Systems Development Life Cycle
Problem identification
Analysis
Logical Design
Physical Design
Mapping
Implementation
Testing & Maintenance
File Based System
Data Redundancy (Duplication of data)
Ø Different systems/programs have separate copies of the
same data
Ø When data changes in one file, could cause inconsistencies
Ø No Database integrity
Limited Data Sharing
Ø No centralized control of data
Separation & Isolation Of data (each user has a copy)
Incompatible File Formats
1
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Basic Definitions
Database: A collection of related data.
Database Management System (DBMS): A software package
system to facilitate the creation and maintenance of a
computerized database.(model introduced in 1970 IBM but
RDBMS appears in1980)
Database System: The DBMS software together with the data
itself. Sometimes, the applications are also included.
(Software + Database)
Database System
2
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
DBMS Advantages
Minimal Data Redundancy
Leads to increased data integrity/consistency
Standardization and better Data accessibility and response (SQL)
Sharing data.
Different users get different views of the data
Improved Data Quality
Constraints, data validation rules
Inconsistency can be avoided.
Restricting Unauthorized Access.
Providing Backup and Recovery.
Disaster recovery is easier
DBMS Disadvantages
It needs expertise to use
DBMS itself is expensive
The DBMS may be incompatible with any
other available DBMS
3
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Entity Relationship Modeling
Entity-Relationship Diagram (ERD)
identifies information required by the business by displaying the
relevant entities and the relationships between them.
Basic constructs of the E-R model:
1. Entities - person, place, object, event, concept (often
corresponds to a real time object that is distinguishable from
any other object)
2. Attributes - property or characteristic of an entity type (often
corresponds to a field in a table)
3. Relationships – link between entities (corresponds to
primary key-foreign key equivalencies in related tables)
4
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
ER Diagram: Starting Example
Strong Entity Vs Weak Entity
A Strong Entity- An Entity set that has a primary key.
A Weak Entity- An entity set that do not have sufficient attributes
to form a primary key.
5
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Partial key:
§ A set of attributes that can be associated with P.K of an owner
entity set to distinguish a weak entity.
Types of Attributes ﻣﮭم:
1. Composite Attribute
2. Multi-valued Attribute
3. Derived Attribute
4. Complex Attribute
5. Simple Attribute
Simple Attribute
access-date
cust-name number
cust-id
customer has account
cust-street
cust-city balance
6
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Composite Attribute
Derived Attribute
7
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Multi-valued
Complex Attribute
8
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Relationship
A Relationship is an association among several entities.
Relation has three Properties:
Ø Degree of Relationships
Ø Cardinality Constraint
Ø Participation Constraint
1-Degree of Relationships
Ø Degree: number of entity types that participate in a relationship
Ø Three cases
Ø Unary: between two instances of one entity type
Ø Binary: between the instances of two entity types
Ø Ternary: among the instances of three entity types
9
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
1.1Recursive Relationship (Unary)
§ Recursive Relationships - A relationship in which the same entity
participates more than once.
1.2 binary relations
A binary relationship set is of degree 2.
10
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
1.3 Ternary Relationship
ternary relationship set is of degree 3.
2-Cardinality
How many instances of one entity will or must be connected to a
single instance from the other entities.
Ø One-One Relationship
Ø One-Many Relationship
Ø Many- Many Relationship
11
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
CONSTRAINT (total vs partial)
An employee MUST work for a department
An employee entity can exist only if it participates in a
WORKS_FOR relationship instance So this participation is TOTAL
Only some employees manage departments
The participation is PARTIAL
12
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Summary of notation for ER diagrams
13
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Keys
Different Types of Keys:
1. Candidate Key
2. Primary Key
3. Foreign Key
4. Composite Key
5. Partial Key
6. Alternate key
7. Super Key
1-Candidate key:
§ is a set of one or more attributes whose value can uniquely
identify an entity in the entity set
Ø Any attribute in the candidate key cannot be omitted without
destroying the uniqueness property of the candidate key.
Example:
Ø (SSN, Name) is NOT a candidate key .
Ø “SSN” is a candidate key of customer.
Ø Candidate key could have more than one attributes.
14
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Primary Key
Example: Both “SSN” and “License #” are candidate keys of Driver
entity set.
Primary Key: is the candidate key that is chosen by the database
designer as the unique identifier of an entity.
[Unique & Not Null]
Primary key May be Composite
15
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
Case study
College has decided to store information about its Students and
curriculums in a database. College has wisely chosen to hire you as a
database designer. Prepare an E-R diagram for College that describes
its activities according to The following Description:
• College has a number of students in different
departments(tracks), each student has
St_id(unique),studentName(Fname,Lname),St_age,st_address
• Each department has dept_id(unique), dep_name
• Students takes many Courses, each course has crs_id, crs_name,
crs_duration and crs_Description
• Student has a grade in each course
• Each department contains a set of instructor and each instructor
in located in one department, each instructor has ins_id,
ins_name, ins_salary, ins_hourRate, ins_bouns,ins_address
• For each Department, there is always one instructor assigned to
manage that Department and each manager has a hiring Date
• Instructor may teach many courses and each course may be
conducted by many instructors, each instructor has evaluation in
each course
• Course is classified under one topic, each topic may have many
courses, and each topic has top_name and top_id
16
DATABASE SECTION 1 ENG:MOHAMED ELDAWANSY
17
