Transcript Principles of Database Design

Principles of
Database Design,
Conclusions
AIMS 2710
R. Nakatsu
Integrity of a Database
It is important that the data in a database is
correct and consistent.
Data integrity problems can occur due to:
 incorrect data entry
 data redundancy
 poor database design
Ways to Maintain Data Integrity







Entity Integrity (primary key must be unique
and must not be null)
Referential Integrity
Field types (e.g., text, numeric, date, yes/no)
Input masks (e.g., ISBN numbers, dates)
Field validation rules (What are some
examples?)
Good design of input screens (user interfaces)
Check digits
User Interface Design
© 2000 Prentice Hall
Check Digit Example
 Add a check digit to validate a clerk’s data
entry.
 The check digit is determined by some
mathematical algorithm.
Example:
Multiply the first digit by 2; multiply the second
digit by 3; multiply the third by 4; add the
results; divide by 10 and take the remainder.
127 yields (1*2 + 2*3 + 7*4) / 10 = 36 / 10 for a
check digit of 6.
Concurrency Control
Concurrency control is the management of
concurrent transaction execution.
Why is it important?
The simultaneous execution of transactions over
a shared database may create several dataintegrity and consistency problems.
Lost Updates:
An Example
Time
1
2
3
4
5
6
Transaction
Step
Stored Value
T1
Read QOH
50
T2
Read QOH
50
T1
QOH = 50 + 20
T2
QOH = 50 - 30
T1
Write QOH
70
T2
Write QOH
20
Note that the first transaction has not been
permanently recorded when the second
transaction is executed.
Concurrency Control
with Locking
A lock guarantees exclusive use of a data item
to a current transaction.
In the previous example, transaction T2 will not
have access to the data item that is currently
used by transaction T1.
The lock is released when the transaction T1 is
completed.
Data Warehouse
stores data that have been extracted from the
various operational, external, and other
databases of an organization
Data warehouses...


support OLAP (online analytical processing)
support data mining (data in a data warehouse are analyzed
to reveal hidden patterns and trends in historical business
activity)
Data Warehouse
Recap








Why do we need a database?
What is a database?
What are the main functions of a DBMS?
Entity-Relationship Modeling
Linking two tables: 1:1, 1:many, many:many
Data Integrity
Concurrency Control
Data Warehouses