Intercars Leicester is an ambitious car dealership based in Leicester.
They are very active in auctions in the east and west midlands.
They have basic data collection techniques which could be significantly
improved to enable them to gain greater insight from the data they collect
which would in turn drive profitable decisions.

Intercars Leicester were approached with the concept of migrating all their
data which at the time was kept in paper form into an on premises MySQL database
with a dedicated server laptop which could be connected to from any other laptops
via ssh or via a GUI. Note, the GUI is the second part of this discontinued project.

To kick start the project, a few weeks were spent understanding how the
business operates and what data they collect. From this a general picture emerged
of organising the data into 4 distinct types of tables:

• Info: These types of tables store information
  about the stakeholders of the business
• Operations: These types of tables are for
  the day to day business activities/operations
• Vehicle Finance: These types of tables are for vehicle transactions and vehicle receipts
• Vehicle History: These types of tables hold historical records for the vehicle.

For each table all of it's variables and variable characteristics were considered
After this period, the database structure/architecture was drawn using the EER diagram
i.e. the tables, table types, variables, variable types and characteristics;
primary keys, foreign keys etc see 1. Documents Intercars DB Concept_Sep2021.xlsx
under Documents and or the EER diagram 2. EER

A Data Dictionary for the database consisting of 6 columns was then created:

• Type: The table type/group as described above
• Table: The table name
• Variable: The variable name
• Type: The variable type e.g. Varchar, INT or BIGINT
• Miscellaneous: The variable characteristics e.g. Primary key,
  Foreign key, not null, unique, auto-increment etc
• Description: A short explanation of what the variable is
  giving context to the variable

This can also be found under 1. Documents.

At this point it was decided not to index the database, as that can be done later.

Various tasks were undertaken to ensure that the eventual database was a summarised reflection of the business.
Once the database setup was created, 3. DB setup the combination of procedures 4. Procedures
and triggers 5. Triggers were used for trafficking data to it's final destination.

Manual testing of data entry into the various tables was carried out to ensure the data transportation
to its final destination was accurate and successful. You can find the testing at 6. Testing

Note: The db backup files here are for testing only. There is no live db backups in this repo.
However, the backup script, that would have been used for backing up the database is included.
You can find the backup folder in 8. DB Backup

Since the project was discontinued and Intercars did not object to the publishing of
this body of work to illustrate my capabilities, in additions to me owning this project,
I have decided to publish it publicly.

Note: This project is part 1 of the discontinued project.
You can find the second part, The Graphical User Interface (GUI) built with python and kivy here Intercars-DB-GUI

The whole project was certainly an enjoyable experience: I learned a great deal, in a very short period of time.