ICT211 Database Design Task 2: Report on Database Optimization

This report delves into various aspects of database design, including data removal, de-normalization, and constraints, specifically within the context of the azwh_diagnosis_aetiology and related tables. It explores normal forms (1NF, 2NF, 3NF) with detailed examples and solutions, emphasizing the minimization of transitive overflow and increased data connectivity. The report also covers indexing techniques for efficient data access and presents an ER Diagram using Crow's Foot notation, along with a relational schema for My Computer Specialist Pty Ltd (MCS). Furthermore, it details the creation of stored procedures for data reuse and sharing, illustrating specific procedures with SQL commands. Desklib offers a wide range of study tools and solved assignments to aid students in understanding complex database concepts.

Table of Contents
Database Design..............................................................................................................................3
1. Removal of data....................................................................................................................3
2. De-Normalization.................................................................................................................3
3. Constraints............................................................................................................................9
4. Normal Form......................................................................................................................12
5. Create Indexing...................................................................................................................15
6. ER Diagram: Crow's Foot Notation....................................................................................16
7. Relational Schema..............................................................................................................17
8. Create Procedures...............................................................................................................18
2

Database Design
1. Removal of data
There is no duplicate data.
2. De-Normalization
De normalization is the process of improving the speed for the read-based performance of
the Database structure and it is used to decrease the some problem in the Database queries with
the help of adding some redundant copies of information as well as reduce the schema layer of
normalization.
3

Queries
1
2
4

3a (i)
3a (ii)
5

3a (iii)
3a (iv)
6

3b
3c.
7

4.
8

3. Constraints
In azwh_diagnosis_aetiology table, the id has the PRIMARY and NOT NULL
constraints.
In azwh_patients table, the id has the PRIMARY and NOT NULL constraints and
animal_id has the NOT NULL constraints.
9

In azwh_diagnosis_anatomical table, id has the PRIMARY and NOT NULL constraints.
In azwh_breeds table, id has the NOT NULL and PRIMARY constraints and
azwh_accession_id has the NOT NULL constraints.
10

In azwh_diagnosis_categories table, id has the NOT NULL and PRIMARY constraints.
In azwh_types table, id has the PRIMARY and NOT NULL constraints and
azwh_accessions_id has the NOT NULL constraints.
11

In Works_on table, essn has the PRIMARY and NOT NULL constraints and pno also has
the PRIMARY and NOT NULL constraints.
4. Normal Form
Normal forms are 1NF, 2NF, 3NF and BCNF.
1NF
It contains only single value, because it cannot hold multiple values.
For example
pname pnumber plocation dnum
Computerization 10 Stafford 4
Reorganization 20 Houston 1
New benefits 30 Stafford 4
Operating
Systems
61 Jacksonville 6
Database 62 Birmingham 6
12