Global Athletics Apparel Manufacturer (GAAM): Relational Database Proposal

Records keeping problem and solution proposal

Global Athletics Apparel Manufacturer (GAAM) is a manufacturer specializing in the manufacturing of athletics apparel of both men and women. GAAM sells its products by distributing it to suppliers who are its direct customers and then the suppliers can sell to the real customers. The customers of GAAM buy product by placing an order to GAAM where by an order can have many items. Each customer is represented by a sales representative in GAAM to make it easier for the customer to place orders to the business. GAAM has a few warehouses where a product can be stored in any of the warehouses but a product of the same type cannot be distributed to many warehouses, thus one product type is only stored in one warehouse. When orders are placed by customers, the parts have to be collected from the respective warehouses.

Currently, GAAM is using an a system of recording information generated from the business activities of the business. This method of record keeping is the use of spreadsheets to store different types of data for the business. Use of spreadsheets by GAMM is a solution to their record keeping problem buts its not a very effective solution. This is because use of spreadsheets is becoming cumbersome for records retrieval as the data of the business grows in size thus the more the data grows the harder it becomes to retrieve records for GAAMS. TO help solve this problem a proposal for coming up with a relational database has been brought up. A relational database will help in better record keeping of the data of the business and it will also solve all the current problems being experienced as a result of using spreadsheets. A relational database is the best priority for the business right now because of the following reasons;

• Relational databases are easy to understand and implement. This means that GAAMs will not have to go through a lot of trouble and use of many resources for it to implements relational database because of the simplicity associated with relational databases.
• In relational databases data is only stored once meaning that data redundancy is eliminated at the table level. This concept leas to more efficient use of storage and easy modification and deleting of records stored in the database (Homan, 2016).
• Relational databases allow complex queries because of the relational nature of the database. A relational database means that the tables making up the database are related thus it’s easy to write complex queries by taking advantage of the relational nature of the database.
• Relational databases are also secure compared to spreadsheets where by access of the different tables and actions in the database can be controlled by use of access rights where by different users are given rights according to their departments and responsibilities in the company (Soltez, 2015). Relational databases also support event logging where all events happening in the database so more sophisticated analysis of the event logs can provide accountability in the business.
• Relational databases also enforce the concept of data integrity where by there are measures in place to make sure that the data stored falls within its accepted range and that all data that is required must be preset.
• Flexibility- Relational databases are very flexible because data is stored in separate tables which are related. This means that the it’s easy for a business to scale the database upwards or downwards depending on the business requirements.
• Relational databases have normalization which is a systematic methodology that ensures a relational database is free of anomalies that could have an impact on the accuracy and integrity of the database. Normalization ensures data consistency in a database (Soltez, 2015).
• Relational databases support multiuser access where by the database can be accessed simultaneously by more than one user. Apart from multi-user access, relational databases allow network access where by the database runs on a server and it’s accessed through a software program acting as a listener for all incoming connections. Thus relational databases can be used as the core of distributed systems.

Above are the reasons why GAAMS needs to upgrade to a relational database from their current system of records keeping which is use of spreadsheets.

 

Figure 1; Entity relationship diagram

Normalization is done up to 3NF which is the level which tables are compact enough to be implemented as tables. Normalization takes place through the following steps of stages

• 1NF- At the first normal form stage all repeating groups in each entity are eliminated to make sure there are no repeating groups of data in any of the entities. If all repeating groups are eliminated then the relations are said to be in 1NF.
• 2NF- At second normal stage the relations gotten in 1NF are taken and analyzed to eliminate all partial groups existing in the relations. Removing partial dependencies means making all non-candidate keys in a relation to fully dependent on the candidate key (Rosenberg, 1999). This means that the relation should have only one candidate key and no other combination of two attributes to form the candidate key. When all this is done, the relation is said to be in 2NF.
• 3NF- at third normal stage the relations gotten in 2NF are analyzed to eliminate all transitive dependencies (Rumbaugh, 1999). Elimination of transitive dependencies means that the attribute key is the only attribute that should determine the other attributes in the relation as it is the primary key.

After applying all the three steps on the relations derived from the entoty relationship diagram, the following relations were gotten.

• Sales_rep(repNO,last_name,first_name, address, total_commission, commission_rate)

This relation is in 3NF because;

The relation has no repetitive groups thus making it to be already in 1NF. IT also doesn’t have any partial dependencies existent among its attribute thus its already in 3NF. And finally the relation qualifies to be in 3NF because it the key attribute is one in the relation and it determines all the other attributes in the relation thus making the relation to be in 3NF.

• Customer(customerNO, name, address, current_balance, credit_limit)

Normalization steps

This relation is in 3NF because;

The relation has no repetitive groups thus making it to be already in 1NF. IT also doesn’t have any partial dependencies existent among its attribute thus its already in 3NF. And finally the relation qualifies to be in 3NF because it the key attribute is one in the relation and it determines all the other attributes in the relation thus making the relation to be in 3NF.

• Customer_sales_rep(customerNO, repNO) Foreign key (cusotmerNO) references Customer (customerNO), foreign key (repNO) References sales_rep (repNO)

This relation is in 3NF because;

The relation has no repetitive groups thus making it to be already in 1NF. IT also doesn’t have any partial dependencies existent among its attribute thus its already in 3NF. And finally the relation qualifies to be in 3NF because it the key attribute is one in the relation and it determines all the other attributes in the relation thus making the relation to be in 3NF.

• Parts(partNO, description, units_on_hand, item_class, warehouseNO, unit_price)

This relation is in 3NF because;

The relation has no repetitive groups thus making it to be already in 1NF. IT also doesn’t have any partial dependencies existent among its attribute thus its already in 3NF. And finally the relation qualifies to be in 3NF because it the key attribute is one in the relation and it determines all the other attributes in the relation thus making the relation to be in 3NF.

• Order(orderNO, date, customerNO,repNO) Foreign key (cusotmerNO) references Customer (customerNO), foreign key (repNO) References sales_rep (repNO)

This relation is in 3NF because;

The relation has no repetitive groups thus making it to be already in 1NF. IT also doesn’t have any partial dependencies existent among its attribute thus its already in 3NF. And finally the relation qualifies to be in 3NF because it the key attribute is one in the relation and it determines all the other attributes in the relation thus making the relation to be in 3NF.

• Order_items(partNO, quantity, quotedPrice,orderNO) Foreign key (orderNO) references order (orderNO)

This relation is in 3NF because;

The relation has no repetitive groups thus making it to be already in 1NF. IT also doesn’t have any partial dependencies existent among its attribute thus its already in 3NF. And finally the relation qualifies to be in 3NF because it the key attribute is one in the relation and it determines all the other attributes in the relation thus making the relation to be in 3NF.

MAAMs is planning on deploying a new relational database as modelled in the entity relationship diagram shown below and as implemented using LibreOffice Base. Use of this new relational database will affects IT controls of the business. The IT controls will be affected because the business will have to acquire a database administrator who sole purpose is to manage and administrator the database to make sure that the database is always online as it will be the core system of the business.

The company will also face ethical issues because implementation of the new database system will mean that some job redundancies will be created as the new system will automate a lot of business processes. This leaves the business with a choice to retrench those workers or to transfer them in other department’s where they can continue working.

The new implemented database will hold a lot of information of the business so security is a very crucial factor after the database is implemented. Measures to keep the database secure should be implemented because if the information were to fall in the wrong hands this can cost the company a huge deal as it has been demonstrated by other companies which have fallen because their information fell into the wrong hands.

5. References

Homan, J. (2016). Relational vs. non-relational databases: Which one is right for you? Retrieved August 26, 2017, from

https://www.pluralsight.com/blog/software-development/relational-non-relational-databases

Rosenberg, D. (1999). Use case driven object modeling with UML.

Rumbaugh, J. (1999). The unified modeling language reference manual.

Soltez, D. L. (2015, March 31). The Advantages of a Relational Database Management System. Retrieved August 26, 2017, from https://www.techwalla.com/articles/the-advantages-of-a-relational-database-management-system

Soltez, D. L. (2016). What are the Advantages of a Relational Database Model? Retrieved August 26, 2017, from

https://www.techwalla.com/articles/what-are-the-advantages-of-a-relational-database-model