CRM Databases

Understand the purpose and features of Customer Relationship Management (CRM) databases.

- CRM databases consolidates customer information into a single repository so users can better organise and manage relationships, automates common processes, and provide tools to monitor performance and productivity.

- Purpose: easy access to customer data, streamlined processes through automation, actionable insights into business performance and customer behaviour.

- Features: email integration, document management, quotes/proposal management, pipeline management, workflow automation, reporting/analytics, forecasting, contact management, interaction tracking, lead management.

- CRM is based relational database management systems (RDBMS):

• Relational data structures: relation, attribute, domain, tuple, degree, cardinality, relational database.
• Relational algebra sets: symbols, union, intersect, join and select.
• Database relations, e.g. entity relationship, generic, semantic.
• Relational keys: super key, candidate key, primary key, foreign key.
• Integrity constraints: entity integrity, referential integrity.
• Entity relationships: one-to-one, one-to-many, many-to-many.

- Manipulating data structures and data in relational databases: defining, modifying and removing data structures and data: updating, inserting, modifying and deletion, retrieval of data for queries and reports, administration of users, security, integrity and recovery.

- Normalisation: anomalies (update, insertion, deletion), primary keys, foreign keys, composite keys, indexing, referential integrity, data dictionary, cascading update, deletion techniques, joins, unions and intersects

- Stages of normalisation: un-normalised form (UNF), first normal form (1NF), second normal form (2NF), third normal form (3NF), Boyce-Codd normal form (BCNF).

Assessment Criteria

• 1.1

  Explain the purpose and features of CRM databases and how they are used in business.

• 1.2

  Explain the role and stages of normalisation when developing data structures.

Be able to design, develop and populate a CRM database to meet requirements.

Design specification to include:

- Requirements of the brief (audience, purpose and client requirements), legal and ethical considerations.

- Data structure designs: data dictionaries, e.g. tables, field names, data types and validation, data flow diagrams, entity relationship diagrams, normalisation.

- Application (user interface) design: data entry/input (verification, validation, calculated fields, masks, directed input), reports (queries, presentation of data, layouts), task automation (imports, updates, deletions), queries using multiple criteria, form values and wild cards, action queries, calculated queries, hardware, software and other resources required, test plans to check correctness of data, functionality, accessibility and usability, implementation, maintenance, and support plans (including training schedule to users), timescales, technical constraints, e.g. data stores, capacity, performance of hardware/software.

- Use of an appropriate software to develop a CRM database solution to meet requirements including:

• Creating, setting up and maintaining data tables
• Creating links/relationships between data tables
• Applying data validation rules
• Generating outputs, e.g. user-generated queries, automated queries, reports
• Application and user interface, e.g. navigation, data entry forms, subforms
• Automated functions
• Populating the database
• SQL statements to extract, manipulate and modify data
• Applying security measures to control access to data, e.g. user access levels, policies.

Assessment Criteria

• 2.1

  Design a CRM database to meet business objectives.

• 2.2

  Develop and populate a CRM database using data from an external source.

• 2.3

  Develop features in data entry forms to ensure validity and integrity of data

• 2.4

  Perform queries using multiple tables and multiple criteria.

• 2.5

  Output CRM data in suitable formats for different purposes and audiences.

• 2.6

  Implement an automated function into the CRM database to enhance productivity.

Be able to test and evaluate a CRM database to meet requirements.

- Testing the CRM database: referential integrity, functionality, security, stability, selection and use of appropriate test data, e.g. erroneous data, extreme data, selecting suitable test users, athering feedback from users, producing appropriate test documentation, making use of testing outcomes to improve and/or refine the database solution.

- Reviewing the CRM database (criteria): quality of the database, fitness for purpose, suitability against the original requirements, legal and ethical constraints, technology constraints, strengths and improvements, platforms and compatibility.

- Optimising the CRM database: data types, data sizes, e.g. size on disk, many tables, e.g. overheads for many tables, query optimising, e.g. select specific columns.

Assessment Criteria

• 3.1

  Use appropriate techniques to test a CRM database to ensure fitness for purpose.

• 3.2

  Evaluate how effectively the CRM database meets requirements, making suggestions for improvements.