Company logo of Crocus Information LtdCrocus Information Ltd

Business Requirements Analysis consultancy to blue chip companies

Exercise 3 Answer

(orginal exercise is here)

This exercise was to give you practice in drawing a partial logical data structure diagram and defining the data items / attributes for any new / changed entites (together these make a partial Logical Data Model).

Here's our Logical Data Structure diagram for you to compare yours to. There is no single right answer because we're working at a high level with business entities but even so, yours should be similar to:

click to see at full size in a new window

Entity Descriptions

In this section we give our list of the data items / attributes for each of the new entities. No amendments are needed to existing entities.

New entity: Campaign

A Campaign is the name Marketing gives to one of its weekly / seasonal sales drives, targeted at specific groupings of Customers as defined by a Mailing List.

Data items Comments / Description
Mailing List ID
Campaign Number
Unique within Mailing List.
Campaign Name

Notes

New entity: Campaign Email

A Campaign Email is a Marketing Message to a specific Contact. The Contact has to have opted in to the Mailing List that the Campaign is for.

Data items Comments / Description
Mailing List ID this and..
Campaign Number this and..
Marketing Message ID
..this are the keys of the Marketing Message master entity.
Customer Account Number this and..
Contact ID
this are the keys of the Contact master entity.

New entity: Contact

A Contact works for a Customer and can either be the primary sales contact with them or a backup contact, in case one is needed.

Data items Comments / Description
Customer Account Number
Contact ID
Unique within Customer
First Name

Last Name

Email Address

Notes

New entity: Mailing List

A Mailing List is a grouping of customers that are to be treated the same in terms of offers and promotions.

Data items Comments / Description
Mailing List ID
Mailing List Name

Description
Used to describe the types of Customer that are to be marketed to as a group by this Mailing List.

New entity: Marketing Message

A Marketing Message is the creative text that describes an offer or promotion. There may be a series of marketing messages within the one Campaign. Each marketing message is sent to all Contacts who have opted in to the Mailing List the Campaign is for.

Data items Comments / Description
Mailing List ID this and...
Campaign Number this are the keys of the Campaign master entity.
Marketing Message ID
Unique within Campaign.
Date Sent
Date the emailshot was sent.

New entity: Opt-in

An Opt-in records that a particular Contact has opted in to a particular Mailing List.

Data items Comments / Description
Mailing List ID
Customer Account Number
Contact ID

Date Opted In

Observations about our answer

Entity names

We've used the same terminology as Lou Skannon and Winnie Warmlede used in their answers during the interview. Although they used the terms quite loosely, you could tell from the context what they were talking about.

Entity keys and data items / attributes

Because things are presently done quite manually, most keys were only implied. We've used common sense in naming them. Other obvious attributes, like Date Sent and Date Opted In can be inferred as well.

In the real word, we'd arrange a session to walk through this first cut of both the partial Logical Data Structure diagram and the Entity Descriptions. We'd confirm everything with the business representatives, including the assumptions / inferred stuff.

As we've now found out that the Master Sales Order spreadsheet is central to the emailshotting, we definitely need to see a copy. We'd also want to watch Lou Skannon using it to create and send out the emails.

There's far more to this than meets the eye!



Exercise 1 | Exercise 2 | Exercise 3

Go to Top

Related

First things first...

To do this exercise, you'll need to have read through our Acme Fashion Supplies case study.


Get the Case Study as an ebook

Now you can carry it with you on your phone, tablet or e-reader and learn when it's convenient to you!

Client-site Business Analyst Essentials

All common ebook formats are included in the download.

If you have a discount code, enter it here:

Buy right now - only £4.50 (approx $6.50) - perfect for the daily commute!

...or you can peek inside via the ebook on Amazon

Companion Briefing Study

Our briefing study web pages, covering Requirements Analysis and Specification, are a complete guide to using a proven method and approach for business analysts. It's also available as a handy reference you can carry with you on your phone, tablet or kindle!

Client-site Business Analyst Essentials

All common ebook formats are included in the download.

If you have a discount code, enter it here:

Buy right now - only £4.50 (approx $6.50) - perfect for the daily commute!

...or you can peek inside via the ebook on Amazon