Transcript Tutorial 4 Slides

Agenda
TMA02
M876 Block 4
1
Model of database development
establishing
requirements
data
requirements
data analysis
conceptual
data model
database design
logical
schema
implementation
schema and
database
2
Steps for Database Design
Use a relational conceptual data
model to give a set of tables for
an initial database design
Do the tables represent the data
in an acceptable way, according
to the given criteria for usability,
efficiency and so on?
Yes
Define constituents of each table
•columns
•primary key
•foreign keys
•constraints
No
Revise tables
Implementation
3
Defining Columns
Choose the most appropriate data type for a column.
Before choosing the data type, we have to know: Characteristics of the expected values for the
column.
 Operations for the column.
Data Type
Characteristics Operations
Numeric
Range,
Precision
+, - , *, /
Character
Length
||
Datetime
4
NOT NULL Constraints
NOT NULL constraints apply to
 Primary key
 Foreign key (mandatory participation condition)
 A row of table which may become meaningless if a
column is NULL
Two extreme forms of policy
 Every column is defined as NOT NULL
 Every row is meaningful and usable
 Allow NULL to occur
 Flexibility of entering incomplete rows of data
5
Default Values
Any NOT NULL column for which there will be
missing values must have a default value.
Two kinds of default value:
Real value

Non-real value
 In formulating a query, you should consider
whether the value should be including in the
processing or not.
6
Codes
Store alternative values instead of the actual values
(e.g. S - Single, M – Married, D - Divorced)
Advantage

Less storage space
Disadvantage


Not immediately understandable
Solution: create an additional code table and
join this table to the other tables so that only the
decodes are visible to users.
7
Domains
SQL Domain  Relational Domain
 Comparison of two SQL columns requires only that
they are of comparable data types, NOT that they
are the same domain
SQL domain provides a common definition, including
both constraints and default values, that can be
shared by a number of columns.
For example, if we define a SQL domain GPA which is
Numeric(3, 2), all columns of this domain will be
rounded to 2 decimal places automatically when
calculating GPA.
8
Defining Tables
Transformation of relations to SQL tables
Three issues shall be considered:
Defining the primary key

Defining the foreign key

Defining constraints
9
Defining Primary Keys
Primary keys should be chosen according to the criteria
of uniqueness, minimality and not being null.
Minimality is important since long primary key will
consume storage space and slow the processing
associated with primary key, such as joining tables.
Sometimes, primary key is replaced by surrogate
(unique values generated by a DBMS) to achieve
minimality.
10
Defining Foreign Keys
When defining a foreign key, it is important to
consider whether any referential action should be
associated when a referenced row is to be deleted.
There are three possible referential actions: RESTRICT
 SET NULL
 SET DEFAULT
 CASCADE
Participation condition at the 'many' end of the
relationship represented by the foreign key
determines the choice of referential action.
Participation Condition Referential Actions
Mandatory
RESTICT or CASCADE
Optional
RESTICT or SET DEFAULT or SET NULL
11
Defining Constraints
Kinds of constraints:



Primary key, expressed as PRIMARY KEY
Alternate key, expressed as UNIQUE
Referential constraint, expressed as FOREIGN KEY
Mandatory participation condition, expressed as NOT
NULL for one end and CHECK clause for the other end
NOT all constraints in a conceptual data model have
to be defined in a database schema. Two reasons for
this: processing inconsistency and processing
inefficiency.
12
Revising Tables
Avoiding NULL to enhance usability.
Denormalization


Normalization, which divide a table into tables, is
to avoid duplication of data and thus prevent
anomalies.
Reverse process which combines tables to provide
more efficient retrieval.
13
Addition Data
Derived data
 Additional column
 Efficient
 Need to maintain consistency of the derived data –
use Trigger
 Create view
 Inefficient
Extra tables
 Snapshot
 Auditing and archiving
 Summary table
14
Usage
Define views and privileges
Reason for defining a view as the means to access
data in a database rather than using the base tables:
 Usability, because specific data required by users
can be defined as a view, providing a simplified
way to access the data;
 Flexibility, because a view enables changes to
be made to base tables without affecting the
users’ view of data, providing data independence;
 Access control, because defining users’
privileges for views, rather than base tables,
allows more precise control on the data made
available to users.
15