Database basics

Report as inappropriate Your report has been received

Shared by: HC120831131558

Tags

Stats

views:: 3
posted:: 8/31/2012
language:: English
pages:: 39

Public Domain

Document Sample

DATABASE LOGICAL
DESIGN - I

Chandra S. Amaravadi

1
DATABASE DESIGN
The objective of database design is to develop a set of
well structured tables so that:

Data is in the most efficient form
No uncontrolled redundancies
Queries/reporting facilitated
Database can be easily implemented

The output of design are a set of well structured/normalized tables.

2
ILL STRUCTURED TABLES
EXAMPLE OF

EMPLOYEE
EID Name Title Dt. promoted
2356 Armstrong Analyst 4/14/09
3286 Nickerson Sr. Analyst 5/1/11;
2/5/12

Ill structured tables are problematic.
why?

3
DESIGN CONCEPTS
ANOMALY:
An inconsistency in the database that results from either
adding records, deleting them or updating them. Three
types of anomalies:

Insertion
Deletion
Update

4
DESIGN CONCEPTS..

INSERTION ANOMALY:
An insertion anomaly occurs if when trying to add a record, or an
attribute, it : cannot be added without additional information or it
may need to be added in multiple places instead of in one location.

DELETION ANOMALY:
A deletion anomaly occurs, if when we try to delete a record,
we have to perform the deletion a number of times, or if we
lose information we did not intend to lose.

5
DESIGN CONCEPTS..

UPDATE ANOMALY/MODIFICATION ANOMALY:
An update anomaly occurs, if when we try to update a record,
instead of making the update in one location, we need to update
in multiple locations.

Are anomalies common in the file processing approach?

6
ILLUSTRATION OF ANOMALIES
EMPLOYEES

EmpID Name Dept. Salary Course Dt completed
100 Jeff Simpson Mktg. 48K SPSS 6/19/2010
100 Jeff Simpson Mktg. 48K Survey 10/07/2011
140 Alan Beeton Acctg. 52K Tax Acct 12/08/2010
110 Chris Lucero IS 43K SPSS 1/22/2010
110 Chris Lucero IS 43K C++ 4/22/2011
190 Lorenzo Davis Fin. 55K
150 Susan Martin Mktg. 42K SPSS 6/19/2010
150 Susan Martin Mktg. 42K Java 8/12/2011

7
ANOMALIES..

1. Insert Tom White, ID 130, Finance, 60K

2. Delete Employee with ID 140

3. Employee with ID 100 gets a 10% salary increase

8
WELL STRUCTURED TABLE

A Well structured table may be defined in a number of ways:
no repeating groups
redundancies minimized
anomalies minimized
all attributes dependent on pkey
full functional dependency

9
9
METHODS OF DESIGN
Design can be carried out with:
ER diagrams (using thumb rules)
Normalization theory
FD approach
brute force

10
DESIGN FROM ER CHARTS,
USING THUMB RULES

11
DESIGN FROM ER:
Following are the rules for converting an ER diagram into a design

1. In the case of 1:1, put each eclass into a separate
table, with a cross-reference key in either.

2. In the case of 1:M, put each eclass into separate
tables; Include the Pkey from the one side
as a foreign key on the M side.

3. In the case of M:N, put each eclass into a separate table;
put the relationship itself into a third table with Pkey
consisting of Pkeys from the M and N sides.

12
DESIGN FROM ER..

PRODUCT HAS WARRANTY

Prod# Descr War# Eff_Dt

Products Warranty
Prod# Descr ??? War# Eff_dt ???

In the case of 1:1, put each eclass into a separate
table and ____________________.

13
DESIGN FROM ER..
Cust# Name
Customer

CUSTOMER Cust# Name

Orders
Places
Ord# Ord_dt ????

ORDERS Are for PRODUCTS

Ord# Ord_dt In the case of 1:M, put each eclass into
a separate table and ___________.
14
DESIGN FROM ER.. Qty

ORDERS Are for PRODUCTS

Ord# Ord_dt Prod# Descr.

Orders Products
Ord# Ord_dt Prod# Descr

Orders for Products
??? ???? Qty

In the case of 1:M, put each eclass into a separate table and
___________.
15
DISCUSSION
DEVELOP DESIGNS FOR THE FOLLOWING SITUATIONS

SS# Name Proj# Mgr S# Locn.

DRIVER PROJECT SUPPLIER

Assigned Has Supplies

CAR EMPLOYEES COMPANY

Car# Model SS# Name
Title Addr.

16
THREE WAY AND HIGHER
d# name
How can we do design
DEPT with degree >= 3?

proj#
HAS PROJECTS
cost

EMPLOYEES

emp# ti

17
DESIGN WITH
NORMALIZATION

18
DESIGN USING NORMALIZATION
Normalization: The process of designing well-structured tables.

Use normalization theory if:
Data relationships complex
No planning/ER done
Maintenance
Alternative to ER approach

19
DESIGN CONCEPTS
FUNCTIONAL DEPENDENCIES

20
DESIGN CONCEPTS..

Functional dependency:
A relationship between two or more attributes such that if we know one
attribute we can uniquely determine other attributes.

a --> b, c.. ; “a determines b, c..”,
; “b,c.. are dependent on a”

FD test: For each value of a there is one and only one value of b.

21
DESIGN CONCEPTS..

Functional dependency (FD):
relationship between attributes (L -> R unless specified)
a  b, c, d is referred to as a functional dependency diagram
Each value of a is associated with one value of b; one c…
For a given value of “b” (or “c”..) there can be many values of “a”

a b : a determines b, b is dependent on a
a b : a does not determine b, no relationship (normally omitted)
a b : there are multiple values of b for each a.

22
FD DIAGRAM EXAMPLES

pp# name of issuing country
flt# name of captain
student id GPA
player team name

pp# visa#’s
GPA student id
prod descr. prod price

price descr.
gpa descr.

Please note that FD is valid only in the first group; second group indicates need for
additional keys (may result in partial functional dependencies)
23
FULL FD RULE

relational Database Rule (Full functional dependency):

All attributes must be fully dependent on the primary key

a b c

24
FD WHEN DATA IS GIVEN

When data is given, perform FD test:
EMPLOYEES

Employee Phone Title Dependents
Casey 621-2876 Engineer Tracy, Tom
Hugh 621-3875 Manager Null
Chris 621-4534 Secretary Ann, Angie
Franklin 621-2876 Engineer Pat

Employee --> Phone#?
Phone# --> Employee?
Employee  Title?
Employee  Dependents?

25
FD WHEN DATA IS GIVEN..

a b c
a1 b1 c1
a1 b1 c2
a2 b2 c3

a --> b?
a c?
b  c?

26
FD WHEN DATA IS NOT GIVEN

When data is not given, make case by case assumptions
and perform FD test:
A flight (flt#) arrives or departs at one gate (gate#)
A flight (flt#) can have one captain (captain name)
A flight (flt#) can have one or more co-pilots (co-pilot name)
A flight (flt#) can go to multiple destinations (dest. name)
A flight (flt#) uses one or more altitudes (alt)
A flight (flt#) has one or more attendants (attdt_name)
A flight (flt#) has many crew members (cr_name)
A flight (flt#) lands at one or more airports (a_code)

Note: FDs are split into pairs here for explanatory purposes
27
FD WHEN DATA IS NOT GIVEN..
When data is not given, make case by case assumptions
and perform FD test:

 Cust places multiple orders
 A flight has multiple pilots, but a single captain.
 Each pet has a single owner
 relationship between ss# and user name for an online stores?
 User name, ss#, web site, company name, cust. credit card#

28
DETERMINANTS & CANDIDATE KEYS

Examples of FDs:
SS# ---> Name, age, sex etc.
The LHS of the FD is called a
determinant, and is a candidate key.
Distance,Class --> Airfare.
ISBN# --> Book title, price etc.
A candidate key is a key that can be
used a pkey (usually LHS)

Suppose
ab What are the determinants?
c -> b candidate keys? design?

29
THE FD APPROACH

Suppose we have

A --> B, C, D

What will normalization yield?
What is the foreign key here?

30
FUNCTIONAL DEPENDENCY RULES
1. Reflexive rule
x -- > y
e.g. ISBN# --> ISBN#
2. Union rule
If x -> y and x --> z then
x--> yz
e.g. ISBN# --> title and ISBN# --> price then
ISBN# --> title, price
3. Transitivity rule
If x--> y and y --> z then x--> z
e.g. if VIN --> Model and Model --> Engine
size then
VIN --> Engine size;

31
FUNCTIONAL DEPENDENCY RULES..

4. Substitution rule
If x -> y and yz --> w then
xz --> w
e.g. if model --> processor and
processor, buswidth --> speed then
model, buswidth --> speed

equip#, p#  equip#, p# What rule is this?

p#  descr What rule is this?
P#  price

=> p#  descr, price
32
FUNCTIONAL DEPENDENCY RULES..

Suppose the following FDs hold,

a e  b, c, d
f a

Then what is the primary key of the table
b, c, d, e, f?

33
FUNCTIONAL DEPENDENCY DIAGRAMS

DRAWING FD DIAGRAMS
The first step in design using FD approach is to draw an FD diagram:

List all attributes (horizontally) placing candidate key leftmost
Take each attribute (after candidate key) and find out its determinant
if data is given, do the FD test with the data.
if no data is given, make assumptions on a case by case basis.
For each value of an attr, how many of the other attr. are there?
If FD exists, draw single arrow
if no FD do not draw the relationship
if there are multiple values for each a, then draw a double arrow

34
NORMALIZATION PROCESS

THE FUNCTIONAL DEPENDENCY APPROACH

Identify and diagram the functional dependencies
Group functional dependencies according to their determinants
Place each set of FDs (along with determinants) in a separate table.
Use determinants as the pkeys.
Add appropriate foreign keys based on the application.

35
NORMALIZATION PROCESS..
THE FUNCTIONAL DEPENDENCY APPROACH

F.D. diagram:

bid  locn, mgr, # of emp., mgr join dt.

Group attr. according to FD:

bid  locn, mgr, # of emp.
Mgr  mgr join_dt

Design

Bank(bid, locn, mgr, #of emp)
Manager(mgr, mgr join_dt)

Are cross reference keys required?
36
REVIEW OF CONCEPTS

Primary key: An attribute whose value is
unique within an entity class (table)
e.g. SS#, Part# etc.

Candidate key: A key that can serve as
the primary key

Composite key: A key that consists of
more than one attribute
e.g. E#, P#
Non key: A key that is not a pkey

Foreign key/ A key that serves as reference
Cross-reference between two tables
key:
37
REVIEW OF CONCEPTS..
Ill Structured: one that is poorly designed; or has redundancies
or more than one value at row-column intersection
Well Structured: one that is well designed; has no redundancies
and atomic values at row-column intersection
Anomaly: An inconsistency during a database activity
insert, delete or update
Determinant: LHS of a Functional Dependency (taken as candidate key)
Functional Dependency: A relationship between two or more attributes
such that if we know a, we can uniquely determine b
FD Test: For each value of a there is one and only one b
Full FD: All attributes are fully functionally dependent on pkey

38
39