BDT T4

1
OM
IS

Designing Database
WE ARE
BUSINESS
Applications
How Do I Design
My Database?
2
Database Modeling
Definition
? the process of establishing structured and
logical relationships between distinct data
items to facilitate the efficient storage and
access of the data.
Process
? it is the responsibility of business
professionals that own the data sometimes in
concert with IS professionals responsible for
managing the organizationsinformation resources
(Database Manager) to organize and structure
thedata to meet their specific business needs.

Entity Relationship Diagrams (ERD)
? a technique for graphically documenting
entities in a database environment.
Entity
? a person, place, thing, transaction, or event
about which information is stored. ? it should
have specific characteristics that define or
describe it
Attribute
? characteristics or properties of a specific
entity.? example the entity CUSTOMER has the
attributes gt CUSTOMER NUMBER,CUSTOMER FIRST
NAME, CUSTOMER LAST NAME, etc.
3
Entities and Attributes
4
Entities and Attributes
Entity Identifier
? ensures that each entity has a unique attribute
value that distinguishes itfrom every other
entity instance. ? example SSN could be the
entity identifier for a company EMPLOYEE. ? in a
relational database model it is called the
Primary Key.
Constraint
? rules established by owners of the data
regarding how data will bestored within the
database environment
Single-Value Attribute
? means having only a single value of each
attribute of any entity at a given time. ?
example the entity CUSTOMER may have the
constraint that it may only haveone PHONE NUMBER
as an attribute
Multi-Valued Attribute
? means the possibility of an attribute attached
to an entity to have more thanone possible
value. ? a relational database model does NOT
allow multi-valued attributes thus,another
entity must be used to hold the data.
5
Entities and Attributes
6
Chen Diagrams
Definition
? a graphical notation to depict ERDs. ? logical
relationships are depicting using only THREE
symbols
Entity
Attribute
Relationship
7
Chen Diagrams
FIRSTNAME
LASTNAME
NUMBER
CUSTOMERNUMBER
HAS
ORDER
CUSTOMER
CITY
STREET
DATE
8
Relationships
One-to-One (11)
? between two entities in which an instance of
entity A can be related to only oneinstance of
entity B and entity B can be related to only one
instance of entity A.
1
1
COURTHOUSE
HAS
TOWN
One-to-Many (1M)
? between two entities, in which an instance of
entity A, can be related to zero,one, or more
instances of entity B and entity B can be related
to only one instanceof entity A.
M
1
M
1
HAS
ITEM
CUSTOMER
HAS
ORDER
9
Relationships
Many-to-Many (MM)
? between two entities in which an instance of
entity A can be related to zero,one, or more
instances of entity B and entity B can be related
to zero, one, ormore instances of entity A.
M
M
CUSTOMER
ORDERS
ITEMS
Cardinality (Optional)
? expresses the specific number of entity
occurrences associated with oneoccurrence of the
related entity.
M
1
CUSTOMER
PLACES
ORDER
(0,M)
(1,1)
? one CUSTOMER can have zero or many orders.?
cardinality helps establish constraints
graphically.
10
Relationships
Many-to-Many Relationships
1) the relationship model does not allow for
many-to-many relationships from every other
entity instance. ?one-to-many and one-to-one
are the only options ?many-to-many
relationships must be replaced with a series of
one-to-many relationships 2)
relationships cannot have attributes only
attributes
Composite Entities
? entities that exist to represent between two
other entities.
M
1
1
M
LINEITEM
PLACES
ORDER
HAS
CUSTOMER
M
CONTAINS
1
PRODUCT
11
Relational Models and Tables
1) Entities gt depicted by Table
2) Instance gt rows of data included in the Table
?Only one value at the intersection of a column
and row - a relation does not allow
multi-valued attributes.?Uniqueness - there are
no duplicate rows in a relation.
3) Tuple gt columns of data included in the Table
?a tuple or groups of tuples that uniquely
identify a specific instance in a entity gt
Primary Key
4) Primary Key
?makes it possible to uniquely identify every row
in a table.?allows user to retrieve every single
piece of data put into a database.?user needs
only THREE pieces of information in order to
retrieve any specific bit of data 1 name
of the relation (table), 2 name of the
tuple (column), 3 name of the primary key of
the instance (row). ?key qualities of primary
keys 1) should contain some value that is
highly unlikely ever to be null. 2) should
never change.
12
Building Relationships
Linking Relations
?each relation must have a primary key comprised
of one or more tuples (columns).?to link
relations (tables) - in order to access
information from both relations both
relations share a common tuple ?the strong
entitys primary key will be repeated in the
weak entity ?the repeated tuple in the weak
entity is called a foreign key.
Foreign Key
?a primary key in one relation (table) that
appears as a tuple in another relation
(table) and acts to provide a logical
relationship among the two relations.
Referential Integrity
?the relational database model enforces a concept
called referential integrity.?every non-null
foreign key MUST match an existing primary key
value. ?example the entity ORDER will have the
tuple customer number as a foreign keyin order
to establish a relationship with the with
CUSTOMER which has customernumber as a primary
key. Referential integrity means the customer
number inORDER MUST match with a customer number
in CUSTOMER.
13
Modeling Example
Customer Information
Order Information
Product Information
1) Can you draw the ERD?2) Can you show design
notation?
14
Modeling Example
1)
M
1
1
M
PLACES
ORDER
HAS
CUSTOMER
ITEM
M
CONTAINS
1
PRODUCT
2)
CUSTOMER (Customer Number, First Name, Last Name,
Address, City, State, Zip)
ORDER (Order Number, Customer Number, Order
Date, Date Order Filled, Shipped)
_______________
ITEM (Order Number, Product Number, Quantity)
____________
_____________
PRODUCT (Product Number, Description, Unit Price,
Sales Price)
15
Data Dictionary
Definition and Characteristics
?a file or database about data ?the dictionary
may contain the following information -
definitions of information types -
identifies the primary and foreign keys -
and maintains the relationships among relations
(tables) - identifies integrity constraints
on relations - may have security information
(view, update, delete, add, etc)
SAMPLE DATA DICTIONARY ENTRY
ATTRIBUTE Customer Number DESCRIPTION Contains
the system provided number which uniquely
identifieseach retail customer. TYPE
Character LENGTH 10FORMAT X(10) RANGE
NoneREQUIRED Yes KEY PrimaryREFERENCED
TABLE? None
16
Data Dictionary
17
Normalization
Definition and Characteristics
? the process of converting complex data
structures into simple stable structures.
Normal Forms
? the theoretical rules that the design must
meet. ? each normal form represents an
increasingly stringent set of rules regarding
the design and structure of data.? the higher
the normal form gt the better the design of the
relation.
First Normal Form (1NF)
? make a separate relation (table) for each set
of related attributes? eliminate repeating
groups in individual relations.? give each
relation a primary key.
Second Normal Form (2NF)
? make a separate relation (table) for sets of
values that apply to multiple relations.? relate
these relations with a foreign key.
Third Normal Form (2NF)
? make a separate relation (table) for attributes
that do not depend on the key.
18
Normalization Example
SAMPLE DATA
1NF
19
Normalization Example
STUDENT Relation
2NF
REGISTRATION Relation
20
Normalization Example
STUDENT Relation
FACULTY Relation
3NF
21
Any Questions?