Finishing off the database

1
Finishing off the database

• Prototype DB (10) Scripts, DB Written
  Specification
• Individual students will be expected to deliver a
  fully functional populated database and system
  specification including data structures, security
  considerations and any other relevant objects.

2
About physical design

• After you have provided your scripts
• Understand the problems
• Present a template that can be used to report on
  the physical design of a DB
• Context Analysis of the full process of db
  development

3
Earlier stages
ER diagrams
Logical Schema
Organisational Activity
Existing Database
Normalised Model
4
Additional constraints / requirements
Physical Schema
Logical Schema
Volume Usage Transaction Access
Integration Distribution Security Queries Users
Analyses
Create tablespace Create table Create
Cluster Create Index Create Views
5
Volume Analysis

• Estimates of average and max values for object
  sizes
• Number of instances per entity
• Realistic storage requirements
• Day one
• Growth over time
• Headings row max, row avg., record size, table
  max, table avg characters

6
Usage analysis (internal constraints)

• Identify major transactions to hit each table and
  their frequency
• Insertions / updates / retrievals / deletes or
  mixtures of all 4
• Eg register a new student day one or year 1 into
  year 2
• Compute Volatile vs non-volatile tables
• Turnover / population (eg over month or year)
• Bipolar distribution (1 or 100)

7
Transactional analysis (other constraints)

• Documenting critical transactions to hit DB
• Impact on systems resources
• Objects accessed and type of access
• Any constraints placed on processing
• Eg time
• Top 20 most important transactions
• Transactional map
• Load analysis (breakdown of queries)

8
Example students per course Query
Step type of Access No of ref./ transac Per
period 1 Read 1 10 2 Read 40 400 3 Read
1 (40) 10 (400)
1
2
3
Course
Student
Registration
9
Access Requirements

• Type of access type of usage headache
• Eg indexes both increase and decrease
  performance
• Conflict between reporting / retrieval and
  transaction processing (OLTPs)
• Time sharing or avoidance (forbidden queries)

10
Integrity Analysis

• Traditional DB territory
• Entity constraints (primary key no duplicates)
• Referential integrity (foreign key ref is not
  null and valid)
• Domain constraints (credit limit numeric
  accepted value 1000, 5000, 15000)
• Enforcing those big hit on performance
  (cascading updates)

11
Security analysis

• Outside frame of DB physical access to
  building, logon procedures etc
• Difficult to contemplate DB security on its own
• Eg operating systems level
• Large scale DBMSs provide specific utilities
• Preventing unauthorised access to data
• Preventing usage of DBMS functions
• Use usage analysis to derive user profiles with
  associated access rights

12
3 aspects of security

• Secrecy info not disclosed to unauthorised users
• Integrity modifications of data must be
  legitimate
• Availability authorised users should not be
  refused access (e.g. by mistake)

13
External security

• Bear in mind that DBMS security is not only issue
• Operating system security
• Network security
• Logical access (through an application)
• As well as physical access (directly into files)

14
Cost of security measures

• Significant performance impact
• Complexity due to multiple levels
• Possible errors from conflicting policies
• Combination of measures may also have
  unpredictable effects on users queries
• May conflict with constraints set across tables
  (e.g. ref. int.)

15
Creating views

• Virtual table with no data in it structure only
• Provide easier access to user / group of users
• Simplification (eg filter)
• Function / calculation of aggregated data (eg
  age from DOB)
• Security restriction (limit to updatable records)

16
Updating through views

• Can lead to problems if record is not complete in
  view (vertical simplification)
• Especially if primary key not shown
• Some views are un updatable
• because they use a distinct select, a group by or
  order by, or they look at more than one table, or
  they are based on a union

Create view FRED as Select from students where
course BIS Create view FRED as Select name,
address, gender from students where course
BIS
17
Conclusions

• All these will establish the performance of the
  DB
• Denormalisation
• Indexing strategy
• Clustering of key relationships
• Creating views