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

Systems
2NF
2NF is contain the major condition is it never dependent with the non-primitive attribute
in the table and it must be use the first normal form table.
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 Systems 62 Birmingham 6
Solution
pname pnumber
Computerization 10
Reorganization 20
New benefits 30
Operating Systems 61
Database Systems 62
pname plocation dnum
Computerization Stafford 4
Reorganization Houston 1
13

New benefits Stafford 4
Operating Systems Jacksonville 6
Database Systems Birmingham 6
3NF
The main process of 3NF is minimizing the transitive overflow as well as increases the
data connectivity.
For example
pname pnumber plocation
Computerization 10 Stafford
Reorganization 20 Houston
New benefits 30 Stafford
Operating Systems 61 Jacksonville
Database Systems 62 Birmingham
Solution
pname dnum
Computerization 4
Reorganization 1
New benefits 4
Operating Systems 6
Database Systems 6
pname pnumber plocation
Computerization 10 Stafford
14

Reorganization 20 Houston
New benefits 30 Stafford
Operating Systems 61 Jacksonville
Database Systems 62 Birmingham
5. Create Indexing
Index means data is stored in the form of tuples. Each and every tuples contains a key
field, which is used for identifying the information in the unique way. It is one of the important
data structure approach used to access the information based on some attribute on indexing has
been done in an efficient manner.
15

6. ER Diagram: Crow's Foot Notation
The below listed diagram comprises of all the Crow's Foot notation. This diagram
contains boxes and are denoted as entities, then the lines between the boxes denotes the
relationships. At the ends of these lines, the cardinality of the relationship is represented in
different shapes.
16

7. Relational Schema
The below diagram represents the Relational Schema of My Computer Specialist Pty Ltd (MCS).
17

8. Create Procedures
Stored procedure is a collection of SQL command combined together with the assigned
name. And it is saved in the RDBMS as a team. So it can provide the reuse as well as sharing of
information by the multiple users in the data structure. The given database procedures are listed
below.
Procedure_1
18

Procedure_2
19

Procedure_3
20

21