Physical Databases

Question 1

Define the purpose of the physical database… What aspects do the DDL and DML implement?

Answer 1

The PD is the realisation of the designed database. The DDL designs the schemas, the DML implements the application programs and populates the data.

Question 2

What is meant by Physical Database Design?

Answer 2

The PDD is the translation of the logical model into a description of a physical model. This should include type,
structures, relationships, DBMS, constraints, security mechanisms etc.

Question 3

What are the pre-requesites for the PDD?

Answer 3

Logical model that can be translated. Must be in 3NF.
A chosen DBMS.
Completed transactional analysis (frequent, costly, distribution, critical etc).

Question 4

What are the 6 fundamental parts of the Physical Database Design?

Answer 4

1. Translate the logical model for the DBMS.
2. File organisations and indexes.
3. Create views.
4. Create security mechanisms.
5. Add data redundancy.
6. Monitor and tune the operational system

Question 5

Define each of the subcomponents of the ‘Translate Logical Model’ stage…

Answer 5

• Define base relations : Names, attributes, keys etc.
• Represent derived data : New column, create view, triggers, each time.
• Create views
• General constraints on data

Question 6

Define each of the subcomponents of the ‘File Organisations and Indexes’ stage…

Answer 6

• Choose file organisation : Heap (unsorted), Sequential (value sorted), Hash (use hash function).
• Choose indexes : Clustered (One per table, same as physical ordering of rows); Non-clustered (
  more than one per table, maintains logical ordering).
• Estimate disk space required.

Question 7

Explain how the 3 storage structures of a database interact.

Answer 7

• Primary storage : Holds volatile data blocks that is operated on.
• Secondary storage : Holds non-volatile data that is being sent to and from the primary storage.
• Stable storage : Holds backup data.

Question 8

Define the reasons to add data redundancy…

Answer 8

• Denormalisation : Improve read speed at cost of write speed.
• Splitting : Split tables into smaller relations.
• Creating : Creating new tables and views to hold derived data.