1
Database Application
SAK 3408
CHAPTER 2
DATABASE DESIGN
Learning Objectives (W2)
2
Describe the database design phases.
Explain on how to construct E-R diagram.
Transform ERD into Relation.
Use normalization to decompose a relation with
anomalies into well-structured relations.
Introduction
3
The goal of any information system is to add value
for the user.
Reduce costs
Increase sales/revenue
Provide competitive advantage
The need for database design:
Flexible enough to handle future needs
Future developers to understand the system
To communicate with users and IT staff
The important step of all methodologies is to build
models of the system.
Program
Design
SQL
Best:
Spend your time
on design and SQL.
Database System Design
4
Customer(CustomerID, Name, Address, …)
SalesPerson(EmployeeID, Name, Commission, … )
Order(OrderID, OrderDate, CustomerID, EmployeeID, … )
OrderItem(OrderID, ItemID, Quantity, Price, … )
Item(ItemID, Description, ListPrice, …)
User views Conceptual Implementation Physical
of data. data model. (relational) data
data model. storage.
Class diagram List of nicely- Indexes and
that shows behaved tables. storage methods
business Use data to improve
entities, normalization to performance.
relationships, derive the list.
and rules.
Design Stages
5
Initiation Physical Design
Scope Table definitions
Feasibility Application development
Cost & Time estimates Queries
Forms
Requirements Analysis Reports
User Views & Needs Application integration
Forms Data storage
Reports Security
Processes & Events Procedures
Objects & Attributes
Implementation
Conceptual Design Training
Models Purchases
Data flow diagram
Data conversion
Entity Relationships
Objects Installation
User feedback Evaluation & Review
Initial Steps of Design
6
1. Identify the exact goals of the system.
2.Talk with the users to identify the basic forms
and reports.
3.Identify the data items to be stored.
4.Design the classes (tables) and relationships.
5.Identify any business constraints.
6.Verify the design matches the business rules.
The E-R Model
7
E-R model – a logical representation of the data
for an organization or for a business area
E-R diagram – a graphical representation of an
entity-relationship model
Modeling Styles
8
Entity name Chen model
attribute
Crow’s foot model
PATIENT
PAT_birthdate
PAT_idnumber PAT_firstname PAT_city
PAT_lastname PAT_address PAT_state
8
E-R Model Constructs
9
Entity - person, place, object, event, concept
Attribute - property or characteristic of an entity
type (often corresponds to a field in a table)
Relationship – link between entities (corresponds to
primary key-foreign key equivalencies in related
tables)
Entity Example
10
Entity Identity Example
Person STAF, STUDENT, LECTURER
Place DISTRICT, TOWN, STATE
Object BUILDING, TOOL, PRODUCT
Event REGISTRATION, APPLICATION
Concept COURSE, ACCOUNT
Example of Entity types according to its identity
Sample of E-R Diagram
11
Sample of E-R Diagram
12
entity relationship
identifier
Basic E-R Notation
13
A special entity
that is also a
relationship
Entity
symbols
Attribute
symbols
Relationship
symbols
Entity
14
Entity Type vs. Entity Instance
Entity Type - collection of entities (often corresponds to a
table)
Entity instance - a single occurrence of an entity type
(often corresponds to a row in a table)
Strong vs. Weak Entity Types
Strong Entity Type – entity that exists independently of
other entity types
Weak Entity Type – entity type whose existence depends
on some other entity type
Example of Entity Type vs. Entity Instance
15
Entity type: STUDENT
75115 Farazila Yusof
75794 Wan Haniza Wan Hassim
76740 Norwani Mohd Zain
77388 Zarawahida Zakaria
77512 Rozita Bt Bahamad
77558 Armamorhana Ahmad
Rows represented 77655 Siti Amrah Zakaria
entity instances of 78436 Siti Nor Haiza Mad Janapi
the entitytype 78447 Noriati Jaminah
84781 Emilza Fuad
84817 Azli Kamarudzaman
84858 Faddly Mazlan
84904 Balkhis Diba Mansor
85013 Aina Najmi Shari
85042 Ku Irdawaty Ku Yaakob
Naming and Defining Entity
16
Use singular noun.
Should be descriptive and specific.
For example: PURCHASE_ORDER and CUSTOMER_ORDER
cannot be named as ORDER
Should be concise.
For example: REGISTRATION_FOR_STUDENT_IN_CLASS
Should be named for the result of the event, not the
activity or process of the event. For example: an
event of a project manager assigning an employee to
work on a project
Attribute
17
Simple vs. Composite Attribute
Simple attribute – cannot broken into smaller components
Composite attribute – can broken into component parts
Single-Valued vs. Multivalued Attribute
Single-Valued – each of the attributes has one value
Multivalued – attribute more than one value
Stored vs. Derived Attributes
Stored attribute – data input or set
Derived Attribute – attribute whose values can be calculated
from related attribute values.
Identifier Attributes
An Example of Composite Attribute
18
An attribute
broken into
component parts
Entity with a multivalued attribute (Skill) and derived
attribute (Years_Employed)
19
Multivalued:
Derived an employee can have
from date employed and current date
more than one skill
Identifier (Key)
20
Identifier (Primary Key) - An attribute (or
combination of attributes) that uniquely identifies
individual instances of an entity type
Composite Identifier – an identifier that consists
of a composite attribute.
Candidate Key – an attribute that could be a
key…satisfies the requirements for being a key
Characteristic of Identifier
21
Will not change in value
Will not be null
No intelligent identifiers whose structure indicates
classification, location etc. (e.g. containing
locations or people that might change)
Substitute new, simple keys for long, composite
keys
Simple Key Attribute
22
The key is underlined
Composite Key Attribute
23
The key is composed
of two subparts
Relationship
24
Relationship Types vs. Relationship Instances
The relationship type is modeled as the diamond and lines
between entity types…the instance is between specific entity
instances
Relationships can have attributes
These describe features pertaining to the association
between the entities in the relationship
Two entities can have more than one type of
relationship between them (multiple relationships)
Associative Entity = combination of relationship and
entity
Relationship type
25
Entity and Relationship instances
Degree of Relationship
26
Degree of a Relationship is the number of entity
types that participate in it
Unary Relationship
Binary Relationship
Ternary Relationship
Degree of Relationship ..cont.
27
Entities of
One entity two different
related to types related
Entities of three
another of to each other
different types
the same
related to each
entity type
other
Cardinality of Relationship
28
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
Degree of relationships and Cardinality
29
(a) Unary relationships
(b) Binary relationships
30
(c) Ternary relationships
31
Note: a relationship can have attributes of its own
Cardinality Constraints
32
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
Cardinality Mandatory and Optional
33
Cardinality Constraints
34
(a) Basic relationship with only maximum cardinalities showing
(b) Mandatory minimum cardinalities
Examples of multiple relationships
35
Employees and departments – entities can be related to one
another in more than one way
Strong vs. Weak Entities, and
Identifying Relationships
36
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
Strong and Weak Entity
37
Identifying relationship
Strong entity Weak entity
Associative Entity
38
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
An Example of Associative Entity -
39
CERTIFICATE
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
(b)Ternary relationship as an associative entity
40
ERD Exercise (1)
41
For each pair of sentences, identify entities
and the relationship representing the
sentences.
"Each Student must take one or more Modules"
"Each Module must be taken by one or more
Students"
"Each Customer must receive at least one
Delivery"
"Each Delivery must be for only one Customer"
"A Client my have an Account Manager"
"Each Account Manager has only one Client"
ERD Exercise (3): Relationship
Add in the ratio.
ERD Exercise (3)
43
An automobile insurance company needs to keep
track of information about vehicle policyholders.
The company has to store information of customer,
car and accident. Customer information is
License_no, name and address. Customers can own
one or more cars, where the car information is
Plate_no, model and year. A car not involved or
involved in many accidents. If the car is involved in
an accident the information that will be stored is
Report_number, location and date. Each car that is
involved in accident the damage amount will be
estimate.
Draw the ERD.
Answer
44
"Each Student must take one or more Modules"
"Each Module must be taken by one or more
Students"
"Each Customer must receive at least one
Delivery"
"Each Delivery must be for only one Customer"
"A Client my have an Account Manager"
"Each Account Manager has only one Client"
