Database Modeling I

1
Database Modeling I
The cautious seldom err. Confucius
2
Class Outline

• What are the steps in designing databases?
• How does one collect information necessary to
  design a database?
• Why is modeling important?
• What are the basic elements of a database model?
• How are the following represented in a database
  model?
• entity
• attribute
• degree of relationship
• connectivity
• cardinality
• binary MN relationships
• participation

3
Characteristics of a Database designer

• Knowledge of the problem you are trying to solve
• Communication skills - extensive discussions with
  users
• Analytical aptitude - keep in mind the broad
  goals even while poring over the smallest details
• Impertinence - question everything!
• Impartiality - find best solution
• Relax constraints - assume anything is possible
• Pay attention to details and definitions
• Reframing - iteratively analyze in new way - be
  creative!

A good designer combines the art of design with
the science of design.
4
Conceptual Design Methodology
1. Define the problem and define database
objectives 2. Analyze current database, assess
user requirements, and create data
model 3. Design data structures (tables, fields,
field specifications, establish
keys) 4. Establish table relationships 5. Clarify
business rules critical to database design (e.g.,
required fields, validation rules) 6. Determine
and establish user views of data 7. Review data
integrity and reiterate design methodology
5
Statement of Purpose

• 1. Declare a specific purpose for the database to
  focus and guide its development
• e.g., The purpose of the All-Star Talent
  database is to maintain the data we use in
  support of the entertainment services we provide
  to our clientele.
• 2. Articulate goals objectives that define
  specific tasks
• We need to maintain complete entertainer
  information.
• We need to maintain complete customer
  information.
• We need to track all customer-entertainer
  bookings.
• We need to maintain financial records of both
  payments from customers and payments to
  entertainers.

6
Assessment of User RequirementsWhat is analyzed?

• interview transcripts
• meeting minutes
• observational notes
• business mission and strategy statements
• questionnaire results

• document analyses
• business forms
• reports
• flow charts
• presentations
• computer-generated output
• training manuals
• consultant reports
• job descriptions

7
Assessment of User RequirementsSpecific
requirements
Goals of analysis of user requirements collect a
list of business goals, entities to track, a
database schema, and sample report outputs.

• What are subjects/objects for the business?
• What characteristics describe each object?
• What unique characteristic distinguishes each
  object from other objects of the same type?
• How do you use this data (e.g, summary reports)?
• Over what period of time are you interested in
  this data?
• Are all instances of each object the same?
• What events occur that imply associations between
  various objects?
• Is each activity or event always handled the same
  way or are there special circumstances?

8
Rules for Conducting User Interviews

• Create a quiet, stress-free environment set a
  limit of six people
• Have an agenda - provide it to participants ahead
  of time
• Focus on the problem at hand maintain control of
  the interview
• Conduct separate interviews for users and
  management
• Identify the decision maker
• Avoid technical jargon
• Show concern for user needs
• Give everyone equal and undivided attention
• Write down everything where it can be seen by
  participants
• Encourage blue sky thinking
• Arbitrate disputes
• Keep the pace of the interview moving
• Dont foreclose your options too soon

9
Data Modeling

• A model is a simplified representation (usually a
  graphic) of a complex object in reality to make
  it understandable
• If the elements in the model are correctly
  associated with elements in reality, the model
  can be used to solve problems in reality (e.g.,
  engineers model to determine a bridges weight
  tolerance if the model is incorrect...)
• an ER model is integrated set of concepts that
  describes data, relationships between data, and
  the constraints on the data as they are used
  within a specific organization a data model
  renders organizations (users) view of objects
  and/or events and their associations
• ER model is a blueprint from which a
  well-structured database is created
• ER models are independent of details of
  implementation

10
E-R Modeling Concepts
11
Entities

• Entity
• Something that can be identified in the users
  environment about which we want to store data
  typically is a noun
• Entities or objects must have occurrences that
  can be uniquely identified
• Identified by an organization or its users
• Consists of tangible or intangible objects or
  events
• Entity Instance
• A single entity occurrence or instance within a
  collection of entities

e.g., STUDENT is an entity Annie Abel is an
entity instance as are Bob Brown and Cathy Chen.
STUDENT
12
Attributes

• properties that describe characteristics of an
  entity - assumed all instances of a given entity
  have the same attributes
• use atomic attributes, those that cannot be
  divided further (e.g., not composite attributes
  (e.g., use last name first name, not name)
• do not use derived attributes (attributes that
  can be calculated using other attributes e.g.,
  age)
• use single value attributes not multi-valued
  (e.g., medication1, medication2, etc.)
• multi-valued attributes, if they have their own
  important attributes should be elevated to
  entities

last name
e.g., attributes of the entity STUDENT might
include name, address, etc.
photo
phone
STUDENT
birth date
first name
13
Identifier

• Each entity occurrence has a unique identifier
• The identifier is an attribute (or group of
  attributes) that describes or identifies each
  entity occurrence
• An identifier should be unique to each occurrence
• Referred to as a primary key in relational
  models

e.g., in the list of potential attributes of the
entity STUDENT, the identifier could be Student
Number.
STUDENT
StudentID
14
Relationships

• Association or connection between two or more
  entities
• Usually a verb
• HAS-A is also a common relationship(EMPLOYEE-has
  a-DEPENDENT)
• E-R model also contains relationship classes

takes
STUDENT
COURSE
StudentID
CourseID
15
Degree of Relationship Binary

• In a binary relationship, two entities are
  associated.
• This is the most common degree of relationship.

16
Degree of Relationship Ternary

• In a ternary relationship, three entities are
  associated

WRITER
ILLUSTRATOR
CUSTOMER
WAREHOUSE
create
order
DESIGNER
ITEM
17
Degree of Relationship Unary (Recursive)

• In a recursive relationship, one entity is
  associated with itself

TEAM
COURSE
plays
requires
18
Connectivity

• Connectivity describes constraints on
  relationship (also referred to as maximum
  cardinality)
• Number of instances of entity B that can (or
  must) be associated with each instance of entity
  A

1
M
rents
Child
Toy
One-to-Many
1
1
has
Employee
Office
One-to-One
M
N
sings
Musician
Song
Many-to-Many
19
Representing MN binary relationships

• MN relationships are represented by two 1M
  relationships.
• the relationship is itself an entity, called a
  composite entity (rectangle around the diamond)
• The composite entity often has its own attributes

M
N
enrolls in
STUDENT
CLASS
M
M
1
1
enrolls in
STUDENT
CLASS
Date
Mark
20
Cardinality

• Cardinality is the specific number of entity
  occurrences associated with one occurrence of the
  related entity
• often referred to as business rules because
  cardinality is usually determined by
  organizational policy

e.g., at a toy lending library, a given child may
not borrow any toys at all or borrow more than
one (up to 3) toys. A toy may not be borrowed by
anyone, or it may be borrowed by one child.
1
M
Child
Toy
borrows
(0,3)
(0,1)
21
Occurrences Diagram

• Pictorial mapping of the occurrences between two
  entities assists in understanding connectivity,
  cardinality

C1 T1 C2 T2 C3 T3 C4 T4 C5 T5
C6 T6
A child may rent between 0 and 3 toys a toy may
only be rented by 0 or 1 child. One child may
rent many toys (1M)
22
Relationship Participation

• Also referred to as minimum cardinality
• Mandatory Participation
• An instance of a given entity must definitely
  match an instance of a second entity
• e.g., each student must enroll in exactly one
  course
• Optional Participation
• An instance of a given entity does not
  necessarily participate in the relationship
• lower bound of cardinality is zero
• e.g., a faculty member teaches zero, one, or two
  courses

1
N
makes
DONATION
MEMBER
(0,N)
(1,1)
OPTIONAL
MANDATORY
a member may or may not make a donation but a
donation must be associated with a member
23
Example Customers Orders

• From the CUSTOMER perspective
• a customer may make many orders (M orders of 1M
  connectivity)
• a customer does not necessarily make orders
  (optional participation of orders, cardinality is
  (0,N))
• From the ORDER perspective
• an order is made by (associated with) one and
  only one customer (1 customer of 1M
  connectivity)
• an order must be made by (associated with) a
  customer (mandatory participation, cardinality is
  (1,1))

1
M
makes
CUSTOMER
ORDER
(0,N)
(1,1)
24
Example Customers Orders
common field
parent table
related table