INF322 - Customer Profile Database System (11/15/2021)

 

Customer Profile Database System

            A complete consumer profile gives companies and marketers insight into the values, needs, and preferences of their customers. Companies can use demographic and geographical information collected by multiple Internet of Things (IoT) devices to assess marketing potential, segment consumers, and direct their marketing efforts more effectively (Guarda, Augusto, & Lopes, 2019). Collecting and analyzing consumer data has the potential to reduce product development time by up to fifty percent and eliminate defects prior to production (Wang, Zhang, & Song, 2020). The study and application of a company’s collected consumer data can create a marketing advantage in many fields.

            Companies obtain a lot of Big Data on their users and consumers, but many are still learning how to analyze and transpose consumer data into meaningful metrics that can improve customer relations, increase sales, and generate leads. The proposed database system will be a customer management database for a small business. The company wants to collect the customer demographics, order history, browsing history, used promotions, and other customer-specific information to create a customer profile. These customer profiles will allow the company to direct personalized marketing efforts effectively to the right customers and improve their marketing campaigns. The end-point users need to access and pull the necessary data from the database quickly. The advertising and marketing team will analyze this data to market the right campaigns, advertisements, and products to interested customers who will likely make a purchase. Other departments and company employees may need to access the information, but this access and editing functions will be limited to maintain data integrity. The company requires scalability to accommodate for the new company’s growing needs. Like object/relational database management systems, the company’s database needs to be easy to use, high-performing, and secure (Coronel & Morris, 2016). The proposed relational database will allow a  growing organization to store and retrieve data quickly and securely.

Entities, Attributes, & Relationships

            A complete consumer profile gives companies and marketers insight into the values, needs, and preferences of their customers. Companies can use demographic and geographical information collected by multiple Internet of Things (IoT) devices to assess marketing potential, segment consumers, and direct their marketing efforts more effectively (Guarda, Augusto, & Lopes, 2019). Collecting and analyzing consumer data has the potential to reduce product development time by up to fifty percent and eliminate defects prior to production (Wang, Zhang, & Song, 2020). The study and application of a company’s collected consumer data can create a marketing advantage in many fields. Companies use an entity relational database to collect, store, and reference essential data on their customers in an organized way, making for easier retrieval and analysis.

            Entities are the objects of interest in a database (Coronel & Morris, 2016). The consumer profile database will include the following entities: customer, order, Facebook, Twitter, and Email promotion. Attributes are characteristics that relate to and define the entity (Coronel & Morris, 2016). The Entity-Relationship Diagram (Figure 1) features the relationships and attributes for the customer entity and order entity. The attributes for the consumer entity are Customer_ID, Gender, Zip_Code, Relationship_Status, Num_of_Children, Education_Level, and Date_of_Birth. The order entity will contain the attributes: Customer_ID, Order_Num, Promo_Code, Total Price, Delivery_Method, Payment_Method, Order_Date, Device_Type, Operating_System, and Minutes Shopped. The Facebook table includes Customer_ID, Facebook_User_ID, Avg_Weekly_Post_Likes, Avg_Comments_Per_Week, and Company_FBPage_Liked. The Twitter table’s attributes are Customer_ID, Twitter_Handle, Avg_Weekly_Post_Likes, Avg_Comments_Per_Week, and Company_TWITPage_Liked. The attributes for the email promotion entity are Email_Address, Email_Sent, Email_Opened, Order_Placed_From_Email, Emails_Allowed, and Email_Preferences.

            Entity relationships can be one-to-many, one-to-one, only one-to-one or many, many-to-many, zero-to-one, or zero-to-many (Coronel & Morris, 2016). The way to determine the relationships is to ask what the minimum and maximum number is that each object or attribute can have in relationship to the other (Lucidchart, 2017). Most of the entity relationships on the data table are one-to-one, except for the relationship between the order and customer table which is one-to-many. For example, a customer can have numerous orders, but each specific order number can only apply to one customer. This relationship is represented differently than the one-to-one blue line on the ER Diagram. The primary keys are set as Customer_Id in the customer table, Order_Num in the order table, Facebook_User_ID in the Facbook table, Twitter_Handle in the Twitter table, and Email_Address in the Email Promotion table because these uniquely identify the data tables. Foreign Keys are Customer_ID in the order, Facebook, and Twitter tables.

Figure 1

Entity Relationship Diagram

 

Constraints

            To ensure that data is uniformly entered from all users, some fields may contain constraints that limit deviation from those set standards. One specific constraint in the database is that the Date_of_Birth in the customer table and the Order_Date in the order table will be limited to a DD/MM/YYYY format. The number of children (Num_of_Children) will be numerical and limited to [0, 20] since this is well above the average range for customers. The acceptable Zip_Code entry is limited to five numeric characters. The social media usernames Twitter_Handle and  Facebook_User_ID will allow all characters. Relationship status will include three options: single, married, or separated. Order numbers (Order_Num) are limited to six numbers and characters. Total_Price under the order table will allow the entry of cents to the hundredth decimal place. The fields that accept characters will recognize lower-case and capital letters as the same, to maintain data integrity and relationship searchability. All the fields are NOT NULL and must have data entered besides the Promo_Code attribute field under the orders table.

Data Normalization

            The purpose of the Design Phase is to maintain the “integrity, security, performance and recoverability” of the database data (Jones, 2021, para. 4). During the Implementation Phase a designer focuses on data constraints, the relationship between foreign and primary keys, and data consistency. Normalization eliminates data redundancies and anomalies in a database (Coronel & Morris, 2016). Some common anomalies are update, insertion, and deletion anomalies in which performing edits to the data tables can impact the data integrity. The first normal form (1NF) assures that each cell of the data tables contains only one value (Sirohi, 2019). The first step towards 1NF is to present all the data in a table and to make sure that there are no repeating groups. The second step is to identify the primary key and dependencies for each table (Coronel & Morris, 2016). Figure 2 shows a breakdown of the database onto its smallest form, thus reducing redundancies and anomalies.

Figure 2

Normal Form 3

SQL Create, Insert, and View

Create the Table:

Use AdventureWorks2008

CREATE TABLE Customer([id] int NOT NULL,

[Customer_ID] nvarchar(25) NOT NULL primary key,

[Gender] nvarchar(25) NOT NULL,

[Zip_Code] nvarchar(25) NOT NULL,

[Relationship_Status] nvarchar(25) NOT NULL,

[Education_Level] nvarchar(25) NOT NULL,

[Date_of_Birth] date NOT NULL, Primary key (Customer_ID)) GO

To Insert Rows/Attributes into the Table:

Use AdventureWorks2008

INSERT INTO Customer (Customer_ID, Gender, Zip_Code, Relationship_Status, Education_Level, Date_of_Birth)
values (12345, ‘Female’, ‘78015’, ‘single, high school diploma’, ‘06-20-2000’)
GO
INSERT INTO Customer (Customer_ID, Gender, Zip_Code, Relationship_Status, Education_Level, Date_of_Birth)
values (12346, ‘male’, ‘22301’, ‘married’, ‘bachelors degree’, ‘10-11-1997’)
GO

INSERT INTO Customer (Customer_ID, Gender, Zip_Code, Relationship_Status, Education_Level, Date_of_Birth)
values (12347, ‘Female’, ‘63006’, ‘separated’, ‘masters degree’, ’03-28-1989’)
GO

INSERT INTO Customer (Customer_ID, Gender, Zip_Code, Relationship_Status, Education_Level, Date_of_Birth)
values (12348, ‘Female’, ‘90210’, ‘married’, ‘high school diploma’, 05-25-1986’)
GO

INSERT INTO Customer (Customer_ID, Gender, Zip_Code, Relationship_Status, Education_Level, Date_of_Birth)
values (12349, ‘male’, ‘37216’, ‘separated’, ‘bachelors degree’, ’08-01-2001’)

To View the Table and Data:

Use AdventureWorks2008

Go

SELECT * from Customer

GO

Database Security and Globalization

            According to Coronel & Morris information systems security refers to “activities and measures that ensure the confidentiality, integrity, and availability of an information system and its main asset, data” (2016, section 16.6). Confidentiality includes providing necessary access to authorized users. Data availability means that the database developer allows users to access certain parts of the database that are necessary for their job functions (Coronel & Morris, 2016). Data integrity is the part of security that would involve someone who has access to the database schema. The developer can prevent unauthorized access by assigning only essential users IDs and enforcing complex password guidelines and multifactor authentication. The database manager can decrease system vulnerabilities by preventing or limiting the copying, downloading, or storing of unnecessary data, performing system backups, testing for bugs and weaknesses, and creating patches (Coronel & Morris, 2016). The data officer and developer can create a plan of action that it will take to minimize the impact of any future data incident. To maintain that integrity the database manager can place constraints so that the data is uniformly entered, reduce data redundancy by normalizing the data, and preventing data table and fields from being deleted that depend on other items in the database.

            Companies accumulate extensive data from online sources through sales channels and social media channels, so they have increasing data-storage needs, but they have many options to meet those needs, which is likely to continue growing even more. For organizations to be competitive in their field, companies require data that is “readily available, anywhere and anytime, to all business users and in all types of formats: a desktop spreadsheet, a Visual Basic application, a web front end, and using newer technologies such as smartphones and tablets” (Coronel & Morris, 2016, section 15.1). To meet their data needs, companies have a variety of database options to share information reducing data redundancy, increasing efficiency, and lowering company costs (Lund, Manyika, & Bughin, 2016). For data sharing, companies can purchase cloud computing services from an online supplier like Amazon or Microsoft and quickly create a relational database for business purposes rather than building and storing the information in-house (Coronel & Morris, 2016).  For the proposed database a centralized cloud-based system will meet the company’s needs for scalability, costs, and access. 

References

Coronel, C., & Morris, S. (2016). Database systems: Design, implementation, & management. (12th ed.). Retrieved from https://www.vitalsource.com

Guarda, T., Augusto, M., & Lopes, Isabel. (2019). Geographic market intelligence as a competitive advantage. 2019 14^th Iberian Conference on Information Systems and Technologies. 1-5. https://ieeexplore-ieee-org.proxy-library.ashford.edu/document/8760856?arnumber=8760856

Jones, M. (2021, November 2). INF322 week 4 instructor guidance [Instructor guidance]. https://ashford.instructure.com/courses/92599/pages/week-4-weekly-lecture

Lucidchart. (2017, March 6). Entity relationship diagram [Video]. YouTube. https://www.youtube.com/watch?v=QpdhBUYk7Kk

Lund, S., Manyika, J., & Bughin, J. (2016, March 14). Globalization is becoming more about data and less about stuff. Harvard Business Review. https://hbr.org/2016/03/globalization-is-becoming-more-about-data-and-less-about-stuff

Sirohi, K. (2019, November 27). Databases: Explaining data normalization, data anomalies and DBSM Keys. Towards Data Science.  https://towardsdatascience.com/softly-explained-data-normalization-data-anomalies-and-dbms-keys-f8122aefaeb3

Wang, Y., Zhang, H., Song, M. (2020). Does big-data-embedded new product development influence project success? Research Technology Management, 63(4), 35-42. https://doi.org/10.1080/08956308.2020.1762447