|
Getting your Trinity Audio player ready…
|
create a 100 record data base of telephone company information that is a record of customer service – do not format it – just show the raw data
RecordID,CustomerID,CustomerName,PhoneNumber,PlanType,ServiceIssue,ContactDate,ContactChannel,RepID,ResolutionStatus,ResolutionDate,CallDurationMinutes,CustomerSatisfaction,Notes
1,C1001,Alan Reed,555-201-0001,Mobile Unlimited,Billing question,2026-01-03,Phone,R014,Resolved,2026-01-03,12,5,Explained taxes and fees
2,C1002,Marsha Lane,555-201-0002,Home Internet,Slow internet,2026-01-04,Chat,R022,Resolved,2026-01-04,18,4,Router reboot and speed test completed
3,C1003,Victor Chen,555-201-0003,Mobile Basic,Dropped calls,2026-01-05,Phone,R006,Escalated,,24,3,Network ticket opened
4,C1004,Nina Brooks,555-201-0004,Fiber Internet,Installation delay,2026-01-05,Email,R031,Pending,,9,3,Technician appointment rescheduled
5,C1005,Frank Miller,555-201-0005,Mobile Unlimited,Device activation,2026-01-06,Store,R011,Resolved,2026-01-06,35,5,SIM activated successfully
6,C1006,Elaine Torres,555-201-0006,Landline,No dial tone,2026-01-07,Phone,R017,Resolved,2026-01-08,16,4,Line repair completed
7,C1007,George Patel,555-201-0007,Business Voice,Voicemail setup,2026-01-07,Chat,R020,Resolved,2026-01-07,11,5,Mailbox configured
8,C1008,Linda Harris,555-201-0008,Mobile Family,Add line request,2026-01-08,Phone,R009,Resolved,2026-01-08,20,5,New line added
9,C1009,Samir Khan,555-201-0009,Home Internet,Outage report,2026-01-09,App,R026,Resolved,2026-01-09,7,4,Area outage confirmed and restored
10,C1010,Joan Fields,555-201-0010,Mobile Basic,Payment arrangement,2026-01-10,Phone,R004,Resolved,2026-01-10,14,4,Arrangement created
11,C1011,Robert Hayes,555-201-0011,Fiber Internet,Equipment return,2026-01-10,Email,R018,Resolved,2026-01-11,8,5,Return label sent
12,C1012,Maria Gomez,555-201-0012,Mobile Unlimited,International roaming,2026-01-11,Chat,R023,Resolved,2026-01-11,19,5,Roaming package added
13,C1013,Peter Young,555-201-0013,Landline,Static on line,2026-01-12,Phone,R016,Escalated,,22,2,Outside line inspection requested
14,C1014,Denise Cole,555-201-0014,Home Internet,Password reset,2026-01-13,App,R027,Resolved,2026-01-13,6,5,Account password reset
15,C1015,Anthony Ross,555-201-0015,Mobile Family,Data overage,2026-01-13,Phone,R012,Resolved,2026-01-13,17,4,Plan upgrade reviewed
16,C1016,Helena Price,555-201-0016,Business Internet,Static IP request,2026-01-14,Email,R030,Pending,,10,3,Awaiting business approval
17,C1017,Kevin Wright,555-201-0017,Mobile Basic,Lost phone,2026-01-15,Phone,R005,Resolved,2026-01-15,21,5,Service suspended and replacement options given
18,C1018,Paula Stone,555-201-0018,Fiber Internet,WiFi coverage,2026-01-15,Chat,R021,Resolved,2026-01-15,23,4,Extender recommended
19,C1019,Omar Malik,555-201-0019,Mobile Unlimited,Port-in issue,2026-01-16,Store,R010,Escalated,,31,3,Carrier port delay
20,C1020,Grace Nolan,555-201-0020,Home Internet,Billing dispute,2026-01-17,Phone,R015,Resolved,2026-01-18,28,4,Credit applied
21,C1021,Eric Bennett,555-201-0021,Mobile Family,Caller ID issue,2026-01-18,Chat,R024,Resolved,2026-01-18,13,5,Feature refreshed
22,C1022,Rita Walsh,555-201-0022,Landline,Service cancellation,2026-01-18,Phone,R003,Resolved,2026-01-18,18,4,Account closed
23,C1023,Henry Adams,555-201-0023,Business Voice,Call forwarding,2026-01-19,Email,R032,Resolved,2026-01-19,12,5,Forwarding enabled
24,C1024,Susan Blake,555-201-0024,Mobile Unlimited,Upgrade eligibility,2026-01-20,Phone,R013,Resolved,2026-01-20,15,5,Upgrade date confirmed
25,C1025,Thomas Grant,555-201-0025,Fiber Internet,Missed appointment,2026-01-21,Phone,R007,Pending,,19,2,Dispatch follow-up requested
26,C1026,Olivia Martin,555-201-0026,Home Internet,Modem replacement,2026-01-21,Chat,R025,Resolved,2026-01-22,16,4,Replacement shipped
27,C1027,Brian Cox,555-201-0027,Mobile Basic,Text messaging failure,2026-01-22,App,R028,Resolved,2026-01-22,9,4,Messaging settings reset
28,C1028,Rachel Kim,555-201-0028,Mobile Family,Autopay setup,2026-01-23,Phone,R008,Resolved,2026-01-23,11,5,Autopay enrolled
29,C1029,Charles Evans,555-201-0029,Business Internet,Intermittent service,2026-01-24,Phone,R019,Escalated,,26,3,Signal monitoring started
30,C1030,Monica Lewis,555-201-0030,Mobile Unlimited,Insurance claim,2026-01-24,Chat,R022,Resolved,2026-01-24,20,4,Claim link provided
31,C1031,David Perry,555-201-0031,Home Internet,Speed upgrade,2026-01-25,Phone,R014,Resolved,2026-01-25,14,5,Upgrade completed
32,C1032,Irene Scott,555-201-0032,Landline,Directory listing,2026-01-26,Email,R029,Resolved,2026-01-26,7,5,Listing updated
33,C1033,Jason Ward,555-201-0033,Mobile Basic,SIM card issue,2026-01-27,Store,R011,Resolved,2026-01-27,22,5,SIM replaced
34,C1034,Karen Bell,555-201-0034,Fiber Internet,New installation,2026-01-27,Phone,R017,Pending,,18,4,Install scheduled
35,C1035,Leo Simmons,555-201-0035,Mobile Unlimited,Hotspot problem,2026-01-28,Chat,R020,Resolved,2026-01-28,15,4,Hotspot feature reset
36,C1036,Amy Foster,555-201-0036,Home Internet,Account login,2026-01-29,App,R026,Resolved,2026-01-29,6,5,Login restored
37,C1037,Patrick Long,555-201-0037,Business Voice,Hunt group setup,2026-01-30,Email,R031,Pending,,13,3,Awaiting configuration team
38,C1038,Julie Turner,555-201-0038,Mobile Family,Plan comparison,2026-01-31,Phone,R006,Resolved,2026-01-31,24,5,Customer changed plan
39,C1039,Steven Bryant,555-201-0039,Mobile Basic,Late fee waiver,2026-02-01,Phone,R004,Resolved,2026-02-01,10,4,One-time waiver applied
40,C1040,Angela Rivera,555-201-0040,Fiber Internet,Router lights red,2026-02-02,Chat,R023,Escalated,,17,3,Possible equipment failure
41,C1041,Donald Murphy,555-201-0041,Landline,Move service,2026-02-02,Phone,R016,Resolved,2026-02-03,19,4,Transfer scheduled
42,C1042,Tanya Reed,555-201-0042,Mobile Unlimited,Spam calls,2026-02-03,App,R027,Resolved,2026-02-03,8,5,Call filter enabled
43,C1043,Philip Moore,555-201-0043,Home Internet,Frequent disconnects,2026-02-04,Phone,R012,Escalated,,27,2,Technician visit required
44,C1044,Melissa Diaz,555-201-0044,Mobile Family,Child line controls,2026-02-05,Chat,R021,Resolved,2026-02-05,18,5,Parental controls enabled
45,C1045,Edward Hughes,555-201-0045,Business Internet,Invoice copy,2026-02-06,Email,R030,Resolved,2026-02-06,5,5,Invoice sent
46,C1046,Carolyn Wood,555-201-0046,Mobile Basic,Number change,2026-02-06,Phone,R005,Resolved,2026-02-06,21,4,New number assigned
47,C1047,Raymond Gray,555-201-0047,Fiber Internet,Installation billing,2026-02-07,Phone,R015,Resolved,2026-02-08,23,4,Charge corrected
48,C1048,Patricia Barnes,555-201-0048,Mobile Unlimited,5G access issue,2026-02-08,Chat,R024,Resolved,2026-02-08,16,4,Network settings updated
49,C1049,Sean Cooper,555-201-0049,Home Internet,Cancel appointment,2026-02-09,App,R028,Resolved,2026-02-09,4,5,Appointment canceled
50,C1050,Deborah Kelly,555-201-0050,Landline,Emergency service address,2026-02-10,Phone,R003,Resolved,2026-02-10,12,5,E911 address updated
51,C1051,Nathan Powell,555-201-0051,Mobile Family,Shared data question,2026-02-11,Phone,R018,Resolved,2026-02-11,13,5,Usage explained
52,C1052,Laura Sanders,555-201-0052,Business Voice,Conference calling,2026-02-11,Email,R032,Resolved,2026-02-11,9,4,Feature enabled
53,C1053,Benjamin Russell,555-201-0053,Fiber Internet,Slow upload speed,2026-02-12,Chat,R025,Escalated,,25,3,Line test abnormal
54,C1054,Rebecca Hall,555-201-0054,Mobile Unlimited,Trade-in credit,2026-02-13,Phone,R009,Pending,,28,3,Trade-in review opened
55,C1055,Gregory Clark,555-201-0055,Home Internet,Seasonal hold,2026-02-14,Phone,R007,Resolved,2026-02-14,15,5,Seasonal hold applied
56,C1056,Sandra Lopez,555-201-0056,Mobile Basic,Voicemail PIN,2026-02-15,App,R026,Resolved,2026-02-15,5,5,PIN reset
57,C1057,Martin Cook,555-201-0057,Mobile Family,Device payoff,2026-02-15,Phone,R014,Resolved,2026-02-15,11,4,Payoff balance provided
58,C1058,Janet Bailey,555-201-0058,Landline,Long distance charges,2026-02-16,Phone,R017,Resolved,2026-02-17,20,4,Charges explained
59,C1059,Arthur Flores,555-201-0059,Business Internet,Service level issue,2026-02-17,Email,R031,Escalated,,14,2,SLA review requested
60,C1060,Nicole Rogers,555-201-0060,Mobile Unlimited,eSIM activation,2026-02-18,Chat,R020,Resolved,2026-02-18,19,5,eSIM activated
61,C1061,Harold Morgan,555-201-0061,Home Internet,WiFi name change,2026-02-19,App,R027,Resolved,2026-02-19,7,5,SSID changed
62,C1062,Christina Peterson,555-201-0062,Fiber Internet,Equipment pickup,2026-02-20,Phone,R006,Pending,,12,4,Pickup window assigned
63,C1063,Wayne Coleman,555-201-0063,Mobile Basic,Prepaid refill,2026-02-20,Store,R011,Resolved,2026-02-20,9,5,Refill processed
64,C1064,Diane Jenkins,555-201-0064,Mobile Family,Duplicate charge,2026-02-21,Phone,R004,Resolved,2026-02-22,26,4,Refund submitted
65,C1065,Keith Butler,555-201-0065,Business Voice,Main number routing,2026-02-22,Chat,R023,Resolved,2026-02-22,21,4,Routing updated
66,C1066,Pamela Simmons,555-201-0066,Home Internet,New customer promo,2026-02-23,Phone,R012,Resolved,2026-02-23,17,5,Promotion added
67,C1067,Fred Griffin,555-201-0067,Landline,Call blocking,2026-02-24,Email,R029,Resolved,2026-02-24,8,5,Blocking enabled
68,C1068,Sharon Edwards,555-201-0068,Mobile Unlimited,Battery replacement info,2026-02-25,Chat,R021,Resolved,2026-02-25,10,4,Repair options provided
69,C1069,Jeremy Collins,555-201-0069,Fiber Internet,Outage credit,2026-02-25,Phone,R015,Resolved,2026-02-26,18,4,Credit approved
70,C1070,Brenda Stewart,555-201-0070,Mobile Basic,Account transfer,2026-02-26,Phone,R005,Pending,,22,3,Transfer form required
71,C1071,Lawrence Morris,555-201-0071,Mobile Family,Upgrade order status,2026-02-27,App,R028,Resolved,2026-02-27,6,5,Tracking number provided
72,C1072,Kathleen Rivera,555-201-0072,Home Internet,Technician feedback,2026-02-28,Email,R030,Resolved,2026-02-28,5,5,Feedback recorded
73,C1073,Roger Cooper,555-201-0073,Business Internet,Firewall compatibility,2026-03-01,Phone,R019,Escalated,,29,3,Advanced support requested
74,C1074,Teresa Ward,555-201-0074,Mobile Unlimited,Family plan discount,2026-03-02,Chat,R024,Resolved,2026-03-02,13,5,Discount verified
75,C1075,Albert Richardson,555-201-0075,Landline,Repair status,2026-03-02,Phone,R016,Resolved,2026-03-03,12,4,Repair completed
76,C1076,Gloria Brooks,555-201-0076,Fiber Internet,Install address correction,2026-03-03,Phone,R007,Resolved,2026-03-03,20,4,Address corrected
77,C1077,Russell James,555-201-0077,Mobile Basic,International calling,2026-03-04,Chat,R022,Resolved,2026-03-04,15,5,Calling add-on added
78,C1078,Evelyn Long,555-201-0078,Home Internet,Modem offline,2026-03-05,Phone,R014,Escalated,,23,3,Replacement likely needed
79,C1079,Willie Bryant,555-201-0079,Mobile Family,Line suspension,2026-03-06,Phone,R003,Resolved,2026-03-06,14,5,Line suspended
80,C1080,Anne Diaz,555-201-0080,Business Voice,Billing contact update,2026-03-07,Email,R032,Resolved,2026-03-07,7,5,Contact updated
81,C1081,Philip Russell,555-201-0081,Fiber Internet,Service move,2026-03-08,Phone,R017,Pending,,19,4,Move order created
82,C1082,Megan Hayes,555-201-0082,Mobile Unlimited,No mobile data,2026-03-08,Chat,R020,Resolved,2026-03-08,16,4,APN reset
83,C1083,Howard Scott,555-201-0083,Home Internet,Streaming buffering,2026-03-09,App,R026,Resolved,2026-03-09,11,4,Channel congestion explained
84,C1084,Judith Perry,555-201-0084,Landline,Caller ID missing,2026-03-10,Phone,R006,Resolved,2026-03-10,13,5,Caller ID provisioned
85,C1085,Samuel Bailey,555-201-0085,Mobile Basic,Final bill question,2026-03-11,Phone,R004,Resolved,2026-03-11,17,4,Final bill explained
86,C1086,Victoria Hughes,555-201-0086,Business Internet,Contract renewal,2026-03-12,Email,R031,Pending,,10,3,Sales follow-up requested
87,C1087,Joe Murphy,555-201-0087,Mobile Family,Device return,2026-03-13,Store,R011,Resolved,2026-03-13,18,5,Return accepted
88,C1088,Cynthia Kelly,555-201-0088,Fiber Internet,Mesh WiFi setup,2026-03-13,Chat,R023,Resolved,2026-03-13,22,5,Mesh nodes configured
89,C1089,Daniel Coleman,555-201-0089,Home Internet,Data cap question,2026-03-14,Phone,R012,Resolved,2026-03-14,9,5,Policy explained
90,C1090,Frances Jenkins,555-201-0090,Mobile Unlimited,Travel pass charge,2026-03-15,Phone,R015,Resolved,2026-03-16,21,4,Charge adjusted
91,C1091,Jonathan Flores,555-201-0091,Landline,Senior discount,2026-03-16,Email,R029,Resolved,2026-03-16,6,5,Discount form sent
92,C1092,Marie Rogers,555-201-0092,Business Voice,After-hours greeting,2026-03-17,Chat,R021,Resolved,2026-03-17,14,5,Greeting updated
93,C1093,Carl Peterson,555-201-0093,Fiber Internet,Construction delay,2026-03-18,Phone,R007,Escalated,,25,2,Engineering review pending
94,C1094,Janice Butler,555-201-0094,Mobile Basic,Payment failed,2026-03-19,App,R028,Resolved,2026-03-19,8,4,Payment method updated
95,C1095,Adam Griffin,555-201-0095,Mobile Family,Military discount,2026-03-20,Phone,R009,Pending,,16,4,Verification requested
96,C1096,Kimberly Edwards,555-201-0096,Home Internet,Cancel service,2026-03-21,Phone,R003,Resolved,2026-03-21,20,4,Cancellation completed
97,C1097,Jack Collins,555-201-0097,Business Internet,Backup connection,2026-03-22,Email,R030,Resolved,2026-03-23,12,5,Quote sent
98,C1098,Lois Stewart,555-201-0098,Mobile Unlimited,Network complaint,2026-03-23,Phone,R019,Escalated,,30,3,Coverage investigation opened
99,C1099,Peter Morris,555-201-0099,Fiber Internet,Router replacement,2026-03-24,Chat,R024,Resolved,2026-03-25,17,4,Replacement shipped
100,C1100,Barbara Richardson,555-201-0100,Landline,Account name correction,2026-03-25,Phone,R018,Resolved,2026-03-25,11,5,Name corrected
_________________________________
normalize this into an rdms
Below is a normalized relational database design for that 100-record customer-service dataset.
CUSTOMERS
customer_id
customer_name
phone_number
PLANS
plan_id
plan_type
SERVICE_ISSUES
issue_id
service_issue
CHANNELS
channel_id
contact_channel
REPRESENTATIVES
rep_id
RESOLUTION_STATUSES
status_id
resolution_status
SERVICE_RECORDS
record_id
customer_id
plan_id
issue_id
contact_date
channel_id
rep_id
status_id
resolution_date
call_duration_minutes
customer_satisfaction
notes
CREATE TABLE customers (
customer_id VARCHAR(10) PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
phone_number VARCHAR(20) NOT NULL
);
CREATE TABLE plans (
plan_id INT PRIMARY KEY,
plan_type VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE service_issues (
issue_id INT PRIMARY KEY,
service_issue VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE channels (
channel_id INT PRIMARY KEY,
contact_channel VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE representatives (
rep_id VARCHAR(10) PRIMARY KEY
);
CREATE TABLE resolution_statuses (
status_id INT PRIMARY KEY,
resolution_status VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE service_records (
record_id INT PRIMARY KEY,
customer_id VARCHAR(10) NOT NULL,
plan_id INT NOT NULL,
issue_id INT NOT NULL,
contact_date DATE NOT NULL,
channel_id INT NOT NULL,
rep_id VARCHAR(10) NOT NULL,
status_id INT NOT NULL,
resolution_date DATE NULL,
call_duration_minutes INT,
customer_satisfaction INT,
notes TEXT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (plan_id) REFERENCES plans(plan_id),
FOREIGN KEY (issue_id) REFERENCES service_issues(issue_id),
FOREIGN KEY (channel_id) REFERENCES channels(channel_id),
FOREIGN KEY (rep_id) REFERENCES representatives(rep_id),
FOREIGN KEY (status_id) REFERENCES resolution_statuses(status_id),
CHECK (customer_satisfaction BETWEEN 1 AND 5)
);
Example lookup-table data:
INSERT INTO plans VALUES
(1,'Mobile Unlimited'),
(2,'Home Internet'),
(3,'Mobile Basic'),
(4,'Fiber Internet'),
(5,'Landline'),
(6,'Business Voice'),
(7,'Mobile Family'),
(8,'Business Internet');
INSERT INTO channels VALUES
(1,'Phone'),
(2,'Chat'),
(3,'Email'),
(4,'Store'),
(5,'App');
INSERT INTO resolution_statuses VALUES
(1,'Resolved'),
(2,'Pending'),
(3,'Escalated');
Example customer records:
INSERT INTO customers VALUES
('C1001','Alan Reed','555-201-0001'),
('C1002','Marsha Lane','555-201-0002'),
('C1003','Victor Chen','555-201-0003'),
('C1004','Nina Brooks','555-201-0004'),
('C1005','Frank Miller','555-201-0005');
Example service records after normalization:
INSERT INTO service_records VALUES
(1,'C1001',1,1,'2026-01-03',1,'R014',1,'2026-01-03',12,5,'Explained taxes and fees');
INSERT INTO service_records VALUES
(2,'C1002',2,2,'2026-01-04',2,'R022',1,'2026-01-04',18,4,'Router reboot and speed test completed');
INSERT INTO service_records VALUES
(3,'C1003',3,3,'2026-01-05',1,'R006',3,NULL,24,3,'Network ticket opened');
INSERT INTO service_records VALUES
(4,'C1004',4,4,'2026-01-05',3,'R031',2,NULL,9,3,'Technician appointment rescheduled');
INSERT INTO service_records VALUES
(5,'C1005',1,5,'2026-01-06',4,'R011',1,'2026-01-06',35,5,'SIM activated successfully');
The main change is that repeated text values such as PlanType, ServiceIssue, ContactChannel, and ResolutionStatus are moved into separate lookup tables. The central service_records table then stores keys instead of repeated text. This puts the data roughly into third normal form, making it easier to query, update, and expand.
provide a view of the normalized RDMS
CREATE VIEW vw_customer_service_records AS
SELECT
sr.record_id,
c.customer_id,
c.customer_name,
c.phone_number,
p.plan_type,
si.service_issue,
sr.contact_date,
ch.contact_channel,
r.rep_id,
rs.resolution_status,
sr.resolution_date,
sr.call_duration_minutes,
sr.customer_satisfaction,
sr.notes
FROM service_records sr
JOIN customers c
ON sr.customer_id = c.customer_id
JOIN plans p
ON sr.plan_id = p.plan_id
JOIN service_issues si
ON sr.issue_id = si.issue_id
JOIN channels ch
ON sr.channel_id = ch.channel_id
JOIN representatives r
ON sr.rep_id = r.rep_id
JOIN resolution_statuses rs
ON sr.status_id = rs.status_id;
Then you can query the normalized RDBMS as if it were the original flat customer-service table:
SELECT *
FROM vw_customer_service_records
ORDER BY record_id;
Example output view:
record_id | customer_id | customer_name | phone_number | plan_type | service_issue | contact_date | contact_channel | rep_id | resolution_status | resolution_date | call_duration_minutes | customer_satisfaction | notes
1 | C1001 | Alan Reed | 555-201-0001 | Mobile Unlimited | Billing question | 2026-01-03 | Phone | R014 | Resolved | 2026-01-03 | 12 | 5 | Explained taxes and fees
2 | C1002 | Marsha Lane | 555-201-0002 | Home Internet | Slow internet | 2026-01-04 | Chat | R022 | Resolved | 2026-01-04 | 18 | 4 | Router reboot and speed test completed
3 | C1003 | Victor Chen | 555-201-0003 | Mobile Basic | Dropped calls | 2026-01-05 | Phone | R006 | Escalated | NULL | 24 | 3 | Network ticket opened
4 | C1004 | Nina Brooks | 555-201-0004 | Fiber Internet | Installation delay| 2026-01-05 | Email | R031 | Pending | NULL | 9 | 3 | Technician appointment rescheduled
5 | C1005 | Frank Miller | 555-201-0005 | Mobile Unlimited | Device activation | 2026-01-06 | Store | R011 | Resolved | 2026-01-06 | 35 | 5 | SIM activated successfully
This view reconstructs the original customer-service record from the normalized tables without duplicating the repeated text values inside the main service_records table.
Leave a Reply