Each time you submit an assignment you must attach this statement as

the cover page for both the hard copy and the electronic version. If the
statement is missing your work will not be marked.

Student Declaration

I have read and understood NCC Education’s Policy on Academic Dishonesty

and Plagiarism.

I can confirm the following details:

Programme/Qualification Name:   L5DBIT  

Student ID/Registration number:   P00186290   

Name:   IAN NGWALO

Centre Name:   NACIT LILONGWE

Module Name: DATABASE DESIGN AND DEVELOPMENT    

Module Leader:   MR CHINTHOCHI

Number of words:      

I confirm that this is my own work and that I have not plagiarized any part of it.
I have also noted the assessment criteria and pass mark for assignments.

Due Date:     

Student Signature:      

Submitted Date:      

DATABASE DESIGN AND DEVELOPMENT, A CASE OF
TOYOTA MALAWI LIMITED
Table of Contents
Introduction.................................................................................................................................. 4

Task 1 – Description of business.................................................................................................5

Task 2 – ER and Data Dictionary.................................................................................................7

Data Dictionary........................................................................................................................ 8

Task 3 – Normalization..............................................................................................................10

The purpose of normalisation and the justification of entities being in the third normal form.
........................................................................................................................................... 10

How normalisation has been used to ensure the tables are well structured........................10

How normalisation solves the problem of update anomalies..............................................10

Task 4 – Assessment of Design................................................................................................12

Derived Data...................................................................................................................... 12

Derived Data ERD.............................................................................................................. 12

Task 5 – Scripts to create table structures.................................................................................14

Task 6 – Data population...........................................................................................................19

Task 7 – Enhancement using SQL............................................................................................24

Reasons of changing database requirements / features.....................................................24

Task 8 – SQL reports................................................................................................................. 26

Task 9 – Future development of a data warehouse...................................................................29

Task 10 – Evaluation.................................................................................................................30
Introduction
This assignment outlines the procedures that have been taken in order to design a database
system for TOYOTA Malawi LTD, the scripts used in creating the tables and those used to
populate data in the tables. All will be documented together with other necessary queries for
their daily operations.
Task 1 – Description of business
Toyota Malawi limited is the market leader in the motor industry in Malawi, selling new vehicles
and even fairly used vehicles. Toyota Malawi limited is the only official distributor of Toyota
vehicles and Hino trucks, it also offers the 3S facilities (sales, parts and service) in their two
branches, Blantyre and Lilongwe, with Mzuzu branch offering 2S facilities (parts and service).
Not only that but also, the company has parts outlets in Dwangwa and Nchalo, in addition it also
operates a mobile service van in selected districts of the country. (Toyota, 2022)

The company’s business rules or daily operations are as follows; a salesperson may sell many
cars, but each car is sold by only salesperson. A customer may buy many cars, but each car is
purchased by only one customer. A salesperson writes a single invoice for each car he or she
sells. A customer gets an invoice for each car he or she purchases. A customer may come in
just to have his or her car serviced. When a customer brings one or more cars in for repair or
service, one service ticket is written is written for each car. The car dealership maintains a
service history for each of the cars serviced, the service records are referenced by the cars
serial number. A car brought in for service can be worked on by many mechanics, and each
mechanic may work on many cars. A car that is serviced may or may not need parts.
Task 2 – ER and Data Dictionary
Data Dictionary
Salesperson

Attribute Data type Length Constraint

SalespersonID Varchar 50 Primary Key

FirstName Varchar 50

LastName Varchar 50

Car

Attribute Data type Length Constraint

CarID Varchar 50 Primary Key
SerialNumber Varchar 50
Make Varchar 50
Colour Varchar 50
CarForSale Y/N Varchar 50
Year Varchar

Customer

Attribute Data type length Constraint

CustomerID Varchar 50 Primary Key
LastName Varchar 50
FirstName Varchar 50
PhoneNumber Varchar 50
City Varchar 50

Sales Invoice

Attribute Data type Length Constraint

InvoiceID Varchar 50 Primary Key
InvoiceNumber Varchar 50
Date Date
CarID Varchar 50 Foreign Key
CustomerID Varchar 50 Foreign Key
SalesPersonID Varchar 50 Foreign Key
Service Ticket

Attribute Data type Length Constraint

ServiceTicketID Varchar 50 Primary Key
ServiceTicketNumber Varchar 50
CarID Varchar 50 Foreign Key
CustomerID Varchar 50 Foreign Key
DateRecieved Date
DateReturnedToCustomer Date
Comments Varchar

Service

Attribute Data type Length Constraint

ServiceID Varchar 50 Primary Key

Rate Null

serviceTicketID Varchar 50 Foreign key

Mechanic

Attribute Data type Length Constraint

MechanicID Varchar 50 Primary Key
FirstName Varchar 50
LastName Varchar 50
serviceTicketID Varchar 50 Foreign key

Parts

Attribute Data type Length Constraint

PartsID Varchar 50 Primary key
PartNumber Varchar 50
PurchasePrice float
Description Varchar 50
ServiceTicketID Varchar 50 Primary key
Task 3 – Normalization
The purpose of normalisation and the justification of entities being in the third normal form.
The main purpose of normalisation is to overcome potential anomalies that might occur when
data is replicated, such anomalies include (insertion, update, and deletion). Normalisation can
also be used to improve the existing data structures or modifying it, and it also ensures data
dependencies. (Guru99, 2022)

All the entities have been fully normalised to the third normal form. To warrant this, all the
attributes were brought to the third normal form and had no transitive dependencies as the third
normal form rule states it.

How normalisation has been used to ensure the tables are well structured
To warrant that tables have been well structured, the rules of normalisation have been followed
to collect data into a set of relations that are free of anomalies ensuring that they might not
cause any errors in the database system, for instance, data loss.

How normalisation solves the problem of update anomalies

Document 2 Service

ServiceID Rate ServiceTicket

SE001 MK 80 000 ST001
SE002 MK 100 000 ST002
SE003 MK 50 000 ST003
SE004 MK 65 000 ST004
SE005 MK 80 000 ST005

The diagram above shows the data of the entity service, which includes; serviceID a unique
identifier for every single service that takes place, Rate and serviceTicket. Looking at the data
that has been presented above, it clearly indicates that the rate is dependent on the type of the
service being undertaken. Therefore, if there was a need to update the rate for a service, it
would require going through the whole list making the changes manually. After successful
normalisation of the data above, it gives us two entities; service and serviceTicket both in third
normal form.

Service

serviceID Rate serviceTicketID

SE001 MK 80 000 STD001
SE002 MK 100 000 STD002
ServiceTicket

serviceTicketID serviceTicketNumber
STD001 STN001
STD002 STN002

Having data normalised in this way, it will simplify the processes of updating the rate, and hence
making the database administrator have less work to do.
Task 4 – Assessment of Design
Through the research that was undertaken, by observing how the company operates in their
daily business processes and by having to ask some of the personnel’s, a set of information
was gathered, and this information it is the one that helped in mapping a logical database
design to a physical design. Having looked at the flow of data at the company, the
requirements, business descriptions, it made it simple to understand what type of data would be
required to be held in the system, and what relationships were to be introduced between the
entities. This was achieved through the processes of Normalisation, ensuring that there is no
data redundancy, and finally an authentic database design was prepared.

Derived Data
Having looked at the information gathered, analyzing the requirements from the data given and
also collected, 2 things were identified; parts used and service mechanic as derived data, and a
representation on how these would be presented is shown below, in the ERD, showing entities
and their attributes.

Derived Data ERD

Here is an extended ERD for derived data.
Service Ticket

Attribute Data type Length Constraint

ServiceTicketID Varchar 50 Primary Key
ServiceTicketNumber Varchar 50
CarID Varchar 50 Foreign Key
CustomerID Varchar 50 Foreign Key
DateRecieved Date
DateReturnedToCustomer Date
Comments Varchar

Service Mechanic

Attribute Data type Length Constraint

serviceMechanicID Varchar 50 Primary key
serviceTicketID Varchar 50 Foreign key
serviceID Varchar 50 Foreign key
mechanicID Varchar 50 Foreign key
Comments Varchar 50

Parts used

Attribute Data type Length Constraint

partsusedID Varchar 50 Primary key
partsID Varchar 50 Foreign key
serviceTicketID Varchar 50 Foreign key
numberUsed Varchar 50
PriceMK float
Task 5 – Scripts to create table structures
In this section I demonstrate how the tables were created in a database environment, the scripts
show table creation beginning with the entities that have no foreign keys, followed by those with
foreign key. All the table creation scripts are shown below including those of the derived entities.

Create database

Create table salesperson

Create table car

Create table customer

Create table sales invoice

Create table service Ticket

Create table service

Create table mechanic

Create table parts

Create table parts used

Create table service mechanic
Task 6 – Data population
The data that has been used to populate the created tables in this database has mainly been
imaginary, and a few has been taken from the document presented in the business description.

In order to execute the scripts, the table creation order has been followed in the data population
process, and the outline of data population process is shown below.

Insert into salesperson

Insert into car

Insert into customer

Insert into sales invoice

Insert into service ticket

Insert into service

Insert into mechanic

Insert into parts

Insert into parts used

Insert into service mechanic

Task 7 – Enhancement using SQL
This section explains any potentials changes that could be introduced to the database after
implementation, and providing their rationale. Having developed a database system for the
company, knowing that the company is still growing the database might require introducing
some new tables, modifying tables or even adding some relevant features.

Reasons of changing database requirements / features

There are many reasons why database require nets could be changed, which some of them are
listed; addition of new features, error fixing, code restructuring (SQL codes).

For example, in this case of TOYOTA it has a couple of branches country wide, so they might
be a need of introducing a column ‘Branch’ which will be indicating which branch transactions
have taken place.

By doing this, it will benefit the company in a couple of ways listed below;

 It will help in improving the quality and consistency of information

 It could also help in data analysis with several ways
 Introducing of the new columns or tables will enhance the database relations, for easy
query returning
 It will help in monitoring business trends for all the business when linked to one central
database system.

Below is a screenshot showing a change initiated in the database by adding a new column
using the ALTER TABLE COMMAND

above screenshot shows table mechanic before initiating a change.

The above screenshot shows the table mechanic after adding a new column, which has been
displayed at the far right of the table.
Task 8 – SQL reports
1. A query that retrieves all parts with the price range of greater than 7000 but less than
26500, ordering by purchasePrice DESC

Query result

2. A query that retrieves all cars whose colour is white

Query result

3. A query that retrieves invoice numbers for the customers who their invoices where
released by date ascending order.

Query result

4. A query that retrieves prices of parts used in a particular service Ticket

 Query result

5. A query that counts parts whose partNumber is SUJ08

Query result

6. A query that retrieves all invoice number, and customer phone Number generated on
2021-09-03

Query result

7. A query that counts mechanics whose service ticket ID is STD003

Query result
8. A query that retrieves customers invoice numbers who are based in Lilongwe.

Query result

9. A query that retrieves parts whose price is less than 20000 ordering them in Ascending
order.

Query result

10. A query that retrieves serial numbers for cars whose make is Hino.

Query result
Task 9 – Future development of a data warehouse
Data Warehouse Meaning

‘A data warehouse is a type of data management system that is designed to allow and provision
business actions, especially analytics.’ (Oracle, 2022). A data warehouse merges and combines
large amounts of data from multiple sources. its analytical competences allow companies to
drive valuable business understandings from their data to advance decision making.

Components of A Data Warehouse

A data warehouse is made up of fur main components in it; a central database, ETL (extract,
load) tools, metadata and access tools. All these components of a data warehouse are planned
for speed so that results are taken quickly.

Factors That Might Lead to The Introduction of Data Warehouse

1. CONSISTENCY
Since data warehouses aim at ensuring consistency, the company might need to build a
data warehouse to maintain consistency. Data warehouses are programmed to apply
uniform format to all the data collected, which simplifies the work of decision making to
analyze and share data visions. This would help Toyota Malawi ltd to maintain data
consistency in all their outlets across the country.
2. IMPROVING THE BOTTOM LINE
Data warehouses platforms allows business leaders or top managerial levels to quickly
access their organisations history of activities and evaluate whether operations have
been successful or not. By doing so this will allow the company executives to adjust their
strategy to improve the bottom line.
3. BETTER DECISION MAKING
Data warehousing improves the speed and efficiency at which different data sets are
accessed and it makes it easier to make corporate decisions and hence introducing it to
the company will help in corporate decision making.

Uses of A Data Warehouse

Data warehouses can be used for product promotions, making distribution decisions, analyzing
data trends in a company, intelligence gathering and many more. Used in various sectors from;
telecommunications, public sector, retain chain and others.
Task 10 – Evaluation
In this reflection I am going to use the Rolfe, G., Freshwater, D. and Jasper, M. (2001) model.
This model is based on answering three questions which are, what?, so what? And now what? .
these questions help to come up with the experience through the assignment and what should
be added to add value to the assignment.

What?

The database assignment was given so that the knowledge acquired in this module is
demonstrated by designing and developing a database for a company of my choice (TOYOTA
MALAWI LTD). The assignment was not that tough as it challenged me to do more research
and gather the required information about my organisation in order to come up with a complete
functional database for the chosen organisation.

So what?

During this period of research, I have learnt how to do more research so that I develop a
database system that will be fully functional and executes all database activities, according to
the user needs. I have learnt how to work within the specified period of time given so that I don’t
work under pressure.

Coming up with the whole database, I had to consult some people from the company, on how
their daily operations are done, and consultations on how I might develop a well-structured
database based on the information provided.

Now what?

There is a need of advancing the database with time, since the company is growing and its
having more customers across the country, for example introducing a data warehouse that will
simplify the daily business activities, helping the managerial level positions to closely monitor
the business and data trends for the company.
Conclusion
In conclusion this assignment, has demonstrated all the processes that have been undertaken
in the designing and development for a database system for Toyota Malawi LTD by using the
knowledge acquired throughout the module lesson. MySQL workbench was used to write
scripts and Microsoft word for report writing and ice cream screen recorder for taking the
screenshots.
References

Guru99, 2022. Guru99. [Online]

Available at: http://www.guru99.com./database-normalization.
[Accessed 4 january 2022].

Oracle, 2022. Oracle. [Online]

Available at: http://www.orcale.com/database/what-is-a-data-warehouse/
[Accessed 3 january 2022].

Toyota, 2022. toyota malawi. [Online]

Available at: http://www.toyotamalawi.com/about-us
[Accessed 18 january 2022].