Transcript Entity - WordPress.com

Database systems-Systems Development Overview
Week 7: Slide 1 of 46
Database Design Overview
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Overview - important points
Week 7: Slide 2 of 46
•
•
•
•
Some introductory information
ERD diagrams
Normalization
Other stuff
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
SDLC - Where are we?
Week 7: Slide 3 of 46
1.
2.
3.
4.
5.
Systems planning phase
Data design
Systems analysis phase
Systems design phase
Systems implementation phase
Systems operation, support, and security phase
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Overview of Database Design
Week 7: Slide 4 of 46
– What are the entities and relationships in the
enterprise?
– What information about these entities and
relationships should we store in the database?
– What are the integrity constraints or business
rules that hold?
– A database `schema’ in the ER Model can be
represented pictorially (ER diagrams).
– Can map an ER diagram into a relational schema.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Data modeling versus Data design
Week 7: Slide 5 of 46
• Data modeling - DFD diagrams
– What data is needed
• Data design
– How will the data be stored and retrieved
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Database Design
Week 7: Slide 6 of 46
• Three phases of database design:
– Conceptual database design
– Logical database design
– Physical database design.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Conceptual Database Design
Week 7: Slide 7 of 46
• Process of constructing a model of the
data used in an enterprise, independent of
all physical considerations.
• Data model is built using the information
in users’ requirements specification.
• Conceptual data model is source of
information for logical design phase.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Logical Database Design
Week 7: Slide 8 of 46
• Process of constructing a model of
the data used in an enterprise based
on a specific data model (e.g.
relational), but independent of a
particular DBMS and other physical
considerations.
• Conceptual data model is refined
and mapped on to a logical data
model.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Physical Database Design
Week 7: Slide 9 of 46
• Process of producing a description of the
database implementation on secondary
storage.
• Describes base relations, file organizations,
and indexes used to achieve efficient
access to data. Also describes any
associated integrity constraints and
security measures.
• Tailored to a specific DBMS system.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Overview of Database Design
Week 7: Slide 10 of 46
• Conceptual design: (ER Model is used for this.)
– What are the entities and relationships we need?
• Logical design:
– Transform ER design to Relational Schema
• Schema Refinement: (Normalization)
– Check relational schema for redundancies and related
anomalies.
• Physical Database Design and Tuning:
– Consider typical workloads; (sometimes) modify the
• database design; select file types and indexes.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Storage choices
Week 7: Slide 11 of 46
• File based
– Text-based - XML
– Binary
• Database systems
– Relational
– Object-based/oriented
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Databases
Week 7: Slide 12 of 46
• Textbook says a few strange things
– Most databases are relational
– Most OO systems use an OO/relational mapping
– DMBSs can run on fairly standard computers
• Microsoft Access - is not really a DBMS
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Database design a skill
Week 7: Slide 13 of 46
• Data design usually involves the design
of the database tables and fields
• Not always a straight forward task
• We'll use entity relationship diagrams
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Introduction
Week 7: Slide 14 of 46
• Entity Relationship Modelling (ERM)
– a technique used to analyze & model the data in
organizations using an Entity Relationship (E-R)
diagram.
• Entity-Relationship Model( E-Model)
– A logical representation of the data for an
organization or for a business area.
• Entity-Relationship diagram (E-R diagram)
– A graphical representation of an entity-relationship
model.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Why ERDs?
Week 7: Slide 15 of 46
• Entity Relationship Diagrams are a major
data modeling tool and will help organize the
data in your project
• This process has proved to enable the
analyst to produce a good database structure
so that the data can be stored and retrieved
in a most efficient manner.
• By using a graphical format it may help
communication about the design between the
designer and the user and the designer and
the people who will implement it.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
ER diagram of Branch user views of DreamHome
Week 7: Slide 16 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Week 7: Slide 17 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Entity relationship diagram (ERD)
Week 7: Slide 18 of 46
A data model utilizing several notations
to depict data in terms of the entities
and relationships described by that
data.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Background
Week 7: Slide 19 of 46
• Introduced by Peter Chen in ‘75
• Used to describe the element in the
system and their relationships
• now widely used
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
ERD Concepts - Entity
Week 7: Slide 20 of 46
• Anything real or abstract about which
we will store data
• Five classes:
– Roles
– events
– Locations
– tangible things
– concepts
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Entity in ERD
Week 7: Slide 21 of 46
• Entities
– An entity is anything real or abstract about which we want to store data.
In short, anything, which an organization needs to store data about
– Entity types fall into five classes
• Roles
e.g. Employee, Student
• Events
e.g. Payment, Borrow
• Locations
e.g. Campus, City
• Tangible things or concepts
e.g. Department, Book
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Data Modeling Concepts: Entity
Week 7: Slide 22 of 46
Entity – a class of persons, places, objects, events, or concepts
about which we need to capture and store data.
– Named by a singular noun

Persons: agency, contractor, customer,
department, division, employee,
instructor, student, supplier.

Places: sales region, building, room,
branch office, campus.

Objects: book, machine, part, product, raw material, software
license, software package, tool, vehicle model, vehicle.

Events: application, award, cancellation, class, flight, invoice,
order, registration, renewal, requisition, reservation, sale, trip.

Concepts: account, block of time, bond, course, fund,
qualification, stock.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
What Should an Entity Be?
Week 7: Slide 23 of 46
• SHOULD BE:
– An object that will have many instances in the
database
– An object that will be composed of multiple
attributes
– An object that we are trying to model
• SHOULD NOT BE:
– A user of the database system
– An output of the database system (e.g. a report)
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Concepts of the ER Model
Week 7: Slide 24 of 46
• Entity types
• Relationship types
• Attributes
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Entity Type
Week 7: Slide 25 of 46
• Entity type
– Group of objects with same properties, identified by
enterprise as having an independent existence.
– A name/label assigned to items/objects that exist in
an environment and that have similar properties
• Entity Instances/occurrence
– A single occurrence of an entity type
– Uniquely identifiable object of an entity type.
• Entity set
– A collection of similar entities. E.g., all employees
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Data Modeling Concepts: Entity
Week 7: Slide 26 of 46
Entity instance – a single occurrence of an entity.
entity
Entity Type STUDENT with instances
Student ID Last Name First Name
instances
Naveed Anwer Butt @ UOG -April 2009
2144
Arnold
Betty
3122
Taylor
John
3843
Simmons
Lisa
9844
Macy
Bill
2837
Leath
Heather
2293
Wrench
Tim
Database systems-Systems Development Overview
Examples of Entity Types
Week 7: Slide 27 of 46
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Entity Instance & Set
Week 7: Slide 28 of 46
• A particular object belonging to a
particular entity type
• Entity Type: Employee
• Entity Instance: M. Sharif
• Entity Set: All employees
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Data Modeling Concepts: Attributes
Week 7: Slide 29 of 46
Attribute: Entities are further described by
their attributes (also called data elements)
– A characteristics common to all or most
instances of an entity
– property or characteristic of an entity type
– a descriptive property or characteristic of
an entity. Synonyms include element,
property, and field.
• Just as a physical student can have
attributes, such as hair color, height, etc.,
data entity has data attributes
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Classifications of attributes:
Week 7: Slide 30 of 46
– Simple versus Composite Attribute
– Single-Valued versus Multivalued Attribute
– Stored versus Derived Attributes
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Attributes
Week 7: Slide 31 of 46
• Simple Attribute
– Attribute composed of a single component
with an independent existence.
• Composite Attribute
– Attribute composed of multiple components,
each with an independent existence.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
31
Database systems-Systems Development Overview
Attributes
Week 7: Slide 32 of 46
• Single-valued Attribute
– Attribute that holds a single value for each
occurrence of an entity type.
• Multi-valued Attribute
– Attribute that holds multiple values for each
occurrence of an entity type.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
32
Database systems-Systems Development Overview
Attributes
Week 7: Slide 33 of 46
• Derived Attribute
– Attribute that represents a value that is
derivable from value of a related attribute,
or set of attributes, not necessarily in the
same entity type.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
33
Database systems-Systems Development Overview
Relationships in ERD
Week 7: Slide 34 of 46
• Relationships
– A data relationship is a natural association that exist
between one or more entities
Example
EMPLOYEE works in DEPARTMENT
EQUIPMENT is allocated to PROJECT
ESTEEM is a type of CAR
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Relationship Types
Week 7: Slide 35 of 46
• Relationship type
– A meaningful associations among entity
types.
• Relationship occurrence
– Uniquely identifiable association, which
includes one occurrence from each
participating entity type.
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
ERD Concepts - Relationship
Week 7: Slide 36 of 46
• Relationships have cardinality
– The number of occurrences of one entity
for a single occurrence of the related entity
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
One-to-one Relationships
Week 7: Slide 37 of 46
• Three types of relationship exist between two
different entities
• It is also called the cardinality (the number of
occurrences of one entity for a single occurrence of
the related entity)
– One-to-one relationship
– One-to-many relationship
– Many-to-many relationship
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Cardinality of Relationships
Week 7: Slide 38 of 46
• One – to – One
– Each entity in the relationship will have exactly
one related entity
• One – to – Many
– An entity on one side of the relationship can have
many related entities, but an entity on the other
side will have a maximum of one related entity
• Many – to – Many
– Entities on both sides of the relationship can have
many related entities on the other side
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
One-to-one Relationships
Week 7: Slide 39 of 46
• This type of relationship takes place when a single occurrence
of an entity is related to just one occurrence of a second entity
Example:
PERSON owns a CAR
CAR is owned by a PERSON
Person
Person
owns
own by
Car
Naveed Anwer Butt @ UOG -April 2009
Car
Database systems-Systems Development Overview
One-to-many Relationships
Week 7: Slide 40 of 46
• This type of relationship takes place when a single occurrence
of an entity is related to many occurrence of a second entity
Example:
DEPARTMENT has EMPLOYEE
EMPLOYEE works in DEPARMENT
Department
Department
has
works in
Employee
Naveed Anwer Butt @ UOG -April 2009
Employee
Database systems-Systems Development Overview
Many-to-many Relationships
Week 7: Slide 41 of 46
• This type of relationship takes place when many occurrence of
an entity are related to many occurrence of a second entity
Example:
STAFF work in PROJECT
PROJECT assigned to STAFF
Staff
Staff
works in
assigned to
Project
Naveed Anwer Butt @ UOG -April 2009
Project
Database systems-Systems Development Overview
Key Constraints (Contd.)
Week 7: Slide 42 of 46
• Several types of key-constraints:
1-to-1
1-to Many
Naveed Anwer Butt @ UOG -April 2009
Many-to-1
Many-to-Many
Database systems-Systems Development Overview
Cardinality Constraints
Week 7: Slide 43 of 46
• Cardinality Constraints - the number of
instances of one entity that can or must be
associated with each instance of another
entity.
• Minimum Cardinality
– If zero, then optional
– If one or more, then mandatory
• Maximum Cardinality
– The maximum number
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Data Modeling Concepts: Degree
Week 7: Slide 44 of 46
Degree – the number of entities that
participate in the relationship.
A relationship between two entities is called
a binary relationship.
A relationship between three entities is
called a 3-ary or ternary relationship.
A relationship between different instances
of the same entity is called a recursive
relationship.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Degree of relationships –
Week 7: Slide 45 of 46
One entity
related to
another of
the same
entity type
Entities of
two
different
types
related to
each other
Naveed Anwer Butt @ UOG -April 2009
Entities of
three different
types related
to each other
Database systems-Systems Development Overview
Unary relationships
Week 7: Slide 46 of 46
Naveed Anwer Butt @ UOG -April 2009
Binary relationships
Week 7: Slide 47 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Database systems-Systems Development Overview
Ternary relationships
Week 7: Slide 48 of 46
Note: a relationship can have attributes of its own
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Quaternary relationship called Arranges
Week 7: Slide 49 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Relationship Types
Week 7: Slide 50 of 46
• Recursive Relationship
– Relationship type where same entity type
participates more than once in different roles.
• Relationships may be given role names to
indicate purpose that each participating
entity type plays in a relationship.
Naveed Anwer Butt @ UOG -April 2009
50
Database systems-Systems Development Overview
Recursive Relationships
Week 7: Slide 51 of 46
• Relationship can exist between different occurrences of the
same type of entity
Example:
EMPLOYEE manager EMPLOYEE
NETWORK makes NETWORK
Employee
Network
manager
makes
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Recursive relationship called Supervises with role names
Week 7: Slide 52 of 46
© Pearson Education Limited 1995, 2005
Naveed Anwer Butt @ UOG -April 2009
52
Database systems-Systems Development Overview
Week 7: Slide 53 of 46
Basic relationship with only maximum cardinalities
Mandatory minimum cardinalities
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Optional cardinalities with unary degree, one-to-one relationship
Week 7: Slide 54 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
A ternary relationship with attributes
Week 7: Slide 55 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Week 7: Slide 56 of 46
A unary relationship with an attribute. This has a
many-to-many relationship
Representing a bill-of -materials structure
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Examples of multiple relationships – entities can be related to
one another in more than one way
Week 7: Slide 57 of 46
Employees and departments
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
What is a Good Data Model?
Week 7: Slide 58 of 46
• A good data model is simple.
– Data attributes that describe any given entity should describe
only that entity.
– Each attribute of an entity instance can have only one value.
• A good data model is essentially nonredundant.
– Each data attribute, other than foreign keys, describes at
most one entity.
– Look for the same attribute recorded more than once under
different names.
• A good data model should be flexible and adaptable
to future needs.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Key constraints: Examples
Week 7: Slide 59 of 46
• Example Scenario 1: An inventory database
contains information about parts and
manufacturers. Each part is constructed by
exactly one manufacturer.
• Example Scenario 2: A customer database
contains information about customers and
sales persons. Each customer has exactly
one primary sales person.
• What do the ER diagrams look like?
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Week 7: Slide 60 of 46
Entity Relationship Diagram Methodology
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 61 of 46
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Identify entities
Find relationships
Draw rough ERD
Fill in cardinality
Define primary keys
Draw key-based ERD
Identify attributes
Map attributes
Draw fully attributes
Check results
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Example
Week 7: Slide 62 of 46
A company has several departments. Each
department has a supervisor and at least one
employee. Employees must be assigned to at
least one, but possibly more departments. At
least one employee is assigned to a project, but
an employee may be on vacation and not
assigned to any projects. The important data
fields are the names of the departments,
projects, supervisors and employees, as well as
the supervisor and employee number and a
unique project number.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 63 of 46
1.
2.
3.
4.
5.
6.
Identify entities
Find relationships
Identify
roles,
events, locations,
Draw
rough
ERD
tangible things or concepts about
Fillwhich
in cardinality
the end-users want to
storeprimary
data
Define
keys
Draw key-based ERD
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Example - entities
Week 7: Slide 64 of 46
A company has several departments. Each department
has a supervisor and at least one employee.
Employees must be assigned to at least one, but
possibly more departments. At least one employee is
assigned to a project, but an employee may be on
vacation and not assigned to any projects. The
important data fields are the names of the departments,
projects, supervisors and employees, as well as the
supervisor and employee number and a unique project
number.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 65 of 46
1.
2.
3.
4.
5.
6.
Identify entities
Find relationships
Draw rough ERD
natural associations between
FillFind
in cardinality
pairs of entities using a
Define primary keys
relationship matrix
Draw key-based ERD
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Relationship Matrix
Week 7: Slide 66 of 46
Department Employee
Department
Employee
Supervisor
Project
Naveed Anwer Butt @ UOG -April 2009
Supervisor
Project
Database systems-Systems Development Overview
Relationship Matrix
Week 7: Slide 67 of 46
Department Employee
Department
is assigned
Employee
belongs to
Supervisor
runs
Project
Naveed Anwer Butt @ UOG -April 2009
Supervisor
Project
run by
works on
uses
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 68 of 46
1.
2.
3.
4.
5.
6.
Identify entities
Find relationships
Draw rough ERD
Fill in cardinality
Put entities in rectangles and
Define
primary
keys
relationships on line segments
Draw
key-based
connecting
the ERD
entities
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
ERD Components
Week 7: Slide 69 of 46
Entity1
Entity2
Relationship
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 70 of 46
1.
2.
3.
4.
5.
6.
Identify entities
Find relationships
Draw rough ERD
Fill in cardinality
Put entities in rectangles and
Define
primary
keys
relationships on line segments
Draw
key-based
connecting
the ERD
entities
Naveed Anwer Butt @ UOG -April 2009
Department
Supervisor
run by
is assigned
Employee
Project
works on
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 72 of 46
Determine the number of
1. Identify entities
occurences of one entity for
2. Find relationships
a single occurrence of the
related
entity
3. Draw rough
ERD
4. Fill in cardinality
5. Define primary keys
6. Draw key-based ERD
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Cardinality
Week 7: Slide 73 of 46
• For each entity X and every entity Y it
has a relationship with
– Each X has NUMBER Y
• For example,
– Each department has exactly one
supervisor
Naveed Anwer Butt @ UOG -April 2009
Symbol
Meaning
One and only one
One or more
Zero or more
Zero or one
Database systems-Systems Development Overview
Sample E-R Diagram
Week 7: Slide 75 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Summary of multiplicity constraints
Week 7: Slide 76 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Basic E-R Notation
Week 7: Slide 77 of 46
Entity
symbols
Relationship
symbols
Naveed Anwer Butt @ UOG -April 2009
A special
entity that is
also a
relationship
Attribute
symbols
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 78 of 46
Determine the number of
1. Identify entities
occurences of one entity for
2. Find relationships
a single occurrence of the
related
entity
3. Draw rough
ERD
4. Fill in cardinality
5. Define primary keys
6. Draw key-based ERD
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Cardinality "list"
Week 7: Slide 79 of 46
• Each department has exactly one supervisor.
• A supervisor is in charge of one and only one
department.
• Each department is assigned at least one
employee.
• Each employee works for at least one
department.
• Each project has at least one employee working
on it.
• An employee is assigned to 0 or more projects.
Naveed Anwer Butt @ UOG -April 2009
Department
Supervisor
run by
is assigned
Employee
Project
works on
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 81 of 46
Identify the data attributes that
1. Identify entities
uniquely identify one and only
2. Find relationships
one occurrence of the related
entity
3. Draw rough
ERD
4. Fill in cardinality
5. Define primary keys
6. Draw key-based ERD
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Example - primary keys
Week 7: Slide 82 of 46
A company has several departments. Each
department has a supervisor and at least one
employee. Employees must be assigned to at least
one, but possibly more departments. At least one
employee is assigned to a project, but an employee
may be on vacation and not assigned to any
projects. The important data fields are the names
of the departments, projects, supervisors and
employees, as well as the supervisor and
employee number and a unique project number.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Attributes
Week 7: Slide 83 of 46
Entity1
Entity2
Relationship
Attribute
Naveed Anwer Butt @ UOG -April 2009
Primary Key
Department
Supervisor
run by
Department
Name
Supervisor
Number
is assigned
Employee
Number
Employee
Project
Number
Project
works on
Database systems-Systems Development Overview
An ERD design methodology
Week 7: Slide 85 of 46
1.
2.
3.
4.
5.
6.
Eliminate many-to-many
Identify entities
relationships and include
Find relationships
primary and foreign keys
Draw rough ERD
Fill in cardinality
Define primary keys
Draw key-based ERD
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Many-to-many = bad
Week 7: Slide 86 of 46
• Cannot be represented in the relational
model and pose some other problems
• Solution
– Replace it with an association entity
– Each entity forms a relation with it
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Verbal Example
Week 7: Slide 87 of 46
• Many to many
– A department has one or more employees
– An employee belongs to one or more
departments
• Associative entity
– A department has one or more EmployeeDepartment
– An employee has one or more EmployeeDepartment
Naveed Anwer Butt @ UOG -April 2009
Department
is assigned
Employee
Department
is assigned
EmployeeDepartment
Employee
involves
Database systems-Systems Development Overview
Strong vs. Weak Entities, and Identifying Relationships
Week 7: Slide 90 of 46
• Strong entities
– exist independently of other types of entities
– has its own unique identifier
– represented with single-line rectangle
• Weak entity
– dependent on a strong entity…cannot exist on its own
– Does not have a unique identifier
– represented with double-line rectangle
• Identifying relationship
– links strong entities to weak entities
– represented with double line diamond
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Figure : Strong and weak entities
Week 7: Slide 91 of 46
Strong entity
Naveed Anwer Butt @ UOG -April 2009
Identifying relationship
Weak entity
Database systems-Systems Development Overview
Associative Entities
Week 7: Slide 92 of 46
• It’s an
entity – it has attributes
• AND it’s a
relationship – it links entities together
• When should a relationship with attributes instead be an
associative entity?
– All relationships for the associative entity should be many
– The associative entity could have meaning independent of the other
entities
– The associative entity preferably has a unique identifier, and should
also have other attributes
– The associative may be participating in other relationships other
than the entities of the associated relationship
– Ternary relationships should be converted to associative entities
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Figure : An associative entity (CERTIFICATE)
Week 7: Slide 93 of 46
Associative entity involves a rectangle with a diamond inside.
Note that the many-to-many cardinality symbols face toward
the associative entity and not toward the other entities
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Repeating groups, normalisation
Week 7: Slide 94 of 46
• Normalization
– Eliminate redundancy
– Organise data efficiently
– Reduce potential for anomalies
• Normal form - "how normalised" a
relational database is
• There are 6 normal forms
• Text limited to 3
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Conclusions
Week 7: Slide 95 of 46
•
•
•
•
Just a small start
An important process
Get lots of practice
Ask lots of questions
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
7. Identify Attributes
Week 7: Slide 96 of 46
Objective: Name the information details (fields), which are essential to the
system under development
The only attributes indicated are the names of
A company has several departments.
Each department has a supervisor and at least one employee.
Employees must be assigned to at least one, but possibly more
departments.
At least one employee is assigned to a project, but an employee
may be on vacation and not assigned to any projects.
The important data fields are the names of the departments,
projects, supervisors, and employees, as well as, the supervisor
numbers, employee numbers and project numbers.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
8. Map Attributes
Week 7: Slide 97 of 46
Objective: For each attribute, match it with exactly one entity that it
describes
Attribute
Entity
Department Name
Department
Employee Number
Employee
Employee Name
Employee
Supervisor Number
Supervisor
Supervisor Name
Supervisor
Project Name
Project
Project Number
Project
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
9. Draw Fully Attributed ERD
Week 7: Slide 98 of 46
Objective: Adjust the ERD from Step 6 to account for entities or
relationships
discovered in Step 8.
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
9. Draw Fully Attributed ERD
Week 7: Slide 99 of 46
Naveed Anwer Butt @ UOG -April 2009
Database systems-Systems Development Overview
Data Dictionary Sample
Week 7: Slide 100 of 46
An example for a Banking System
Middle Name
Last Name
First Name
Name
PAN
Date
Transaction No
Account No
Account Type
Customer No
Gender
Address
Telephone
Account
Transaction
Customer
TType
TP Account
Customer No
Amount
Account No
Naveed Anwer Butt @ UOG -April 2009
Balance