3.6. Complete Current Physical Model
Contents
This complete current physical model is made up of a collection of leveled data flow diagrams, a data model, and a supporting data dictionary, specifically:
1. A package of data flow diagrams:
• Context diagram (Piccadilly Project at the highest level)
• Diagram 0 (Piccadilly’s current implementation)
• Diagram 1 Computer Department
• Diagram 2 Research Department
• Diagram 3 Sales Department, version 2
• Diagram 3.1 Plan Campaign
• Diagram 3.5 Make Changes to Spots
• Diagram 4 Commercial Booking Department
• Diagram 5 Programme Transmission Department
2. Updated version of the data model.
3. Data dictionary, with the definitions of all terms in the data flow diagrams and data model. The entries are listed in alphabetical order. Each entry has a comment that classifies its type as one of the following:
* Data element grouping *
* Data flow *
* Data store *
* Entity *
* Material store *
* Relationship *
Sometimes, the definition of an entry is followed by one or more comments that define the purpose, define the values, or point to other sources of knowledge about the entry.
Ski Patrol
Your patroller cannot know why you are reading this model; you could have reached this chapter from a number of others, so the only help the Ski Patrol can give is to refer you to the appropriate chapter for each of the model’s components:
• data flow diagrams discussed in Chapters 2.2 and 2.6
• leveled data flow diagrams in Chapter 2.7
• data dictionary described in Chapter 2.9
• data models in Chapter 2.5
Trail Guide
All trails: This chapter is used as reference material for many other chapters. As we have no idea where you came from, our advice is to return whence you came.
Figure 3.6.2: Diagram 0, the first breakdown of the context diagram. This is intended to be a readily recognizable physical model, so the departments have been chosen as the partitioning theme.
Figure 3.6.10: An early version of the data model, containing all the information that you have accumulated by building the current physical model. It may be updated later with the knowledge you gain from doing essential modeling.
Data Dictionary
This data dictionary defines the terms used in the current physical model and in the first-cut data model. You will notice that some of the definitions are not complete and that there are some questions in the dictionary. For example, some of the entities and relationships don’t have a note defining their purpose, which indicates there are still unanswered questions about the relevancy and use of the data. Some of the entities have their unique identifiers underlined; those that don’t need composite identifiers, which will be specified in the mini specifications. When you build your essential requirements models, you will get answers to such outstanding questions as you make changes, additions, and deletions to the definitions in the data dictionary.
Actual Rating = * Entity. A rating measurement taken during transmission. *
Actual Rating Percentage + Rating Time
Actual Rating Percentage = * Data element. The percentage of a given audience type watching Piccadilly Television at the time this rating was measured. *
Advertising = * Relationship. Keeps track of which products are involved in advertising campaigns. Cardinality: for each Product, there are many Advertising Campaigns; for each Advertising Campaign, there is one Product. Participation: Product optional, Advertising Campaign mandatory. *
Advertising Agency = * Entity. Buyer of commercial spots for advertising campaigns. *
Agency Name + Agency Address + Agency Phone Number
Advertising Campaign = * Entity. Records the conditions and aims for a campaign to advertise a product.*
Campaign Number + Campaign Start Date
+ Campaign End Date + Target Audience
+ Target Rating Percentage + Campaign Predicted Rating
+ Campaign Budget Total + Piccadilly Budget Amount
+ Campaign Duration + {Required Spot Duration}
* Work necessary to remove or justify the repeating group *
Agency Accounts File = * Data store. Computerized current system accounts file. *
{Agency Name + Agency Address + Agency Phone Number
+ {Agency Invoice} + {Agency Payment}}
Agency Address = * Data element *
Agency Invoice = * Data flow *
Agency Name + Agency Address + Invoice Number
+ Invoice Date + {Campaign Number + {Spot Number
+ Spot Duration + Rate Moveability + Spot Transmitted Time
+ Spot Transmitted Date + Spot Price}} + Invoice Total
Agency Invoice Record = * Data flow. A transaction in the current computer system.*
Agency Invoice
Agency Name = * Data element. Identifier for an advertising agency. *
Agency Phone Number = * Data element *
Agency Register = * Data store. File of agencies and responsible sales executives kept by the Sales Department. *
{Agency Name + Agency Address + Agency Phone Number
+ Sales Executive Name + Servicing Start Date}
Agreed Campaign = * Data flow. Spots and rates agreed between Piccadilly and an advertising agency. *
Agency Name + Agency Address + Campaign Number
+ Product Name + Campaign Start Date + Campaign End Date
+ {Spot Number + Spot Duration + Spot Price
+ Rate Moveability + Spot Booking Agreement
+ ([Breakchart Date + Break Start Time | {Breakchart Date}])}
+ {Unavailable Slot}
Airtime Analysis = * Data flow *
{Breakchart Date + {Break Start Time + Break Sold Value
+ Break Unsold Value + {Seconds Available
+ (Rate Moveability)}}} + Total Sold Value + Total Unsold Value
Airtime Ratings History = * Data store. Television ratings supplied by audience measurement bureaus. *
{Television Ratings Report}
Allocating = * Relationship. Keeps track of which commercial copy should be transmitted for a commercial spot. Cardinality: for each Commercial Spot, there is one Commercial Copy; for each Commercial Copy, there are many Commercial Spots. Participation: Commercial Copy optional, Commercial Spot
optional. *
Appearing = * Relationship. Keeps track of which performers appear in programmes because this can affect which products can be advertised during a programme. Cardinality: for each Performer, there are many Programmes; for each Programme, there are many Performers. Participation: Programme optional, Performer mandatory. *
Audience Type = * Data element. Used to classify ratings figures. *
[ Homes | Homemakers | Adults | Men | Women | Children ]
Automated Cassette Recording = * An actual cassette containing a recording of a television commercial. This technology is used by the current system. *
Automatic Cassette Recordings = * Data store and material store *
{Commercial Copy Number + Automated Cassette Recording
+ Production Company Name + Agency Name + Product Name}
Available Time = * Data flow *
{Breakchart Date + {Break Start Time + {Seconds Available
+ (Rate Moveability)}}}
Average Break Predicted Rating = * Data element. Derivable: average of all Predicted Ratings for a Target Audience for all Episodes with a Proximity relationship to a given Commercial Break. Integer. *
Billing = * Relationship. Keeps track of which spots have been invoiced. Cardinality: for each Invoice, there are many Commercial Spots; for each Commercial Spot, there is one Invoice. Participation:
Invoice mandatory, Commercial Spot optional. *
Breakchart = * Data store. Board containing hanging files plus breaksheets, used to record available time and sold time. *
{Breakchart Date + {Programme Name + Episode Start Time
+ Episode End Time} + {Break Start Time + Break End Time
+ Break Minimum Rate + {Spot Number + Product Name
+ Spot Duration + Spot Price + Rate Moveability
+ Spot Booking Agreement}}} + {Programming Rule}
Breakchart Date = * Data element. A date on which Piccadilly will transmit programmes and commercials. *
Breakchart Day = * Entity *
Breakchart Date + Breakchart Day Start
+ Breakchart Day End + Daily Revenue
Breakchart Day End = * Data element *
Breakchart Day Start = * Data element *
Break Duration = * Data element. Duration of a commercial break. Units: mins/secs. *
[“1 min” | “1 min 30 secs” | “2 mins” | “2 mins 30 secs” | “3 mins”]
Break End Time = * Data element *
Break Minimum Rate = * Data element. Minimum rate to be charged for a break. *
Break Predicting = * Relationship. Cardinality: for each Commercial Break, there are many Predicted Ratings; for each Predicted Rating, there is one Commercial Break. Participation: Commercial Break optional, Predicted Rating mandatory. *
Break Sold Value = * Data element. Derivable: total of Spot Prices for each Commercial Spot that has an Occupying relationship with a given Commercial Break. *
Break Start Time = * Data element *
Break Transmission Schedule = * Data flow. Commercial Booking Department’s notification of the scheduled transmission time and the spots that will occupy the next day’s breaks. *
Breakchart Date + {Break Start Time + {Spot Number
+ Campaign Number + Spot Duration + Product Name}}
Break Unsold Value = * Data element. Derivable: ((Break Duration x Break Minimum Rate) - Break Sold Value). *
Broadcasting Rule = * Entity *
Programming Rule + Rule Effective Date
Budgeting = * Relationship. Keeps track of the sales target for a number of breakchart days. Cardinality: for each Sales Target, there are many Breakchart Days; for each Breakchart Day, there is one Sales Target. Participation: Sales Target mandatory, Breakchart Day optional. *
Buying = * Relationship. Cardinality: for each Advertising Agency, there are many Commercial Spots; for each Commercial Spot, there is one Advertising Agency. Participation: Advertising Agency optional, Commercial Spot mandatory. *
Campaign Availability = * Data flow. Airtime available for a specific campaign. *
{Breakchart Date + {Break Start Time + {Seconds Available
+ (Rate Moveability)}}}
Campaign Budget Total = * Data element. The total campaign budget that the agency intends to spend on television advertising. Units: pounds sterling. *
Campaign Duration = * Data element. Number of weeks during which spots for a campaign will be transmitted. Integer. Value range: >= 2 and <= 52. *
Campaign End Date = * Data element. Date marking the last day spots will be transmitted for a campaign. *
Campaign File = * Data store. File kept in the Sales Department. *
{Campaign Requirements + Suggested Campaign}
Campaign Make Up = * Relationship. Keeps track of all the spots that make up a campaign plan to achieve the desired target ratings. Cardinality: for each Advertising Campaign, there are many Commercial Spots; for each Commercial Spot, there is one Advertising Campaign. Participation: Advertising Campaign optional, Commercial Spot mandatory. *
Campaign Number = * Data element. Identifier for an advertising campaign. *
Campaign Predicted Rating = * Data element. Derivable: total of the Predicted Ratings for Commercial Breaks that have an Occupying relationship with Commercial Spots in a given Advertising Campaign. *
Campaign Requirements = * Data flow. An agency’s description of requirements for an advertising campaign. *
Agency Name + Product Name + Campaign Budget Total
+ Piccadilly Budget Amount + Target Audience
+ Target Rating Percentage + Campaign Duration
+ Campaign Start Date + Campaign End Date
+ {Required Spot Duration}
Campaign Start Date = * Data element. Date marking the first day spots for a campaign will be transmitted. *
Commercial Break = * Entity. Each commercial break represents a number of seconds that can be sold for the transmission of advertising copy. *
Break Start Time + Break End Time + Break Duration
+ Break Sold Value + Break Unsold Value + Break Minimum Rate
Commercial Copy = * Entity. The material that is transmitted during the time occupied by a commercial spot. *
Commercial Copy Number + Physical Copy
+ {Copy Transmission Date} + Disposal Date
* Note that this contains a repeating group so is not yet fully normalize. *
Commercial Copy Number = * Data element. Unique identifier for commercial copy. *
Commercial Copy Recording = * Data flow plus physical cassette. Commercial copy recording sent by the production company to Piccadilly’s Programme Transmission Department. *
Commercial Copy Number + Automated Cassette Recording
+ Production Company Name + Agency Name + Product Name
Commercial Spot = * Entity. Represents time that has been sold to an advertising agency. *
Spot Number + Spot Duration + Spot Booking Agreement
Competing = * Relationship. Keeps track of which opposition programmes are in competition with the episodes that Piccadilly plans to transmit. Cardinality: for each Episode, there are many Opposition Programmes; for each Opposition Programme, there are many Episodes. Participation: Episode optional, Opposition Programme optional. *
Containing = * Relationship. Keeps track of which commercial breaks are planned for a breakchart day. Cardinality: for each Commercial Break, there is one Breakchart Day; for each Breakchart Day, there are many Commercial Breaks. Participation: Breakchart Day optional, Commercial Break mandatory. *
Copy Disposal Instructions = * Data flow. Instruction from an agency to dispose of an outdated commercial copy recording. *
Commercial Copy Number + Product Name + Disposal Date
Copy Register = * Data store *
{Commercial Copy Number + Product Name + Campaign Number
+ {Copy Transmission Date} + (Disposal Date)}
Copy Transmission Date = * Data element. The date that a piece of copy is scheduled to be transmitted. *
Copy Transmission Instructions = * Data flow. Details of when particular commercial copy is to be transmitted. *
Agency Name + Product Name + Commercial Copy Number
+ {Copy Transmission Date}
Current Rate = * Data element. Derivable: the average rate currently being paid for a 30-second spot within a particular break. Units: pounds sterling. Integer. *
Daily Revenue = * Data element. Revenue made by Piccadilly for all the commercial spots transmitted on one breakchart day. Derivable: total of all Spot Prices for every Commercial Spot having a Transmitting relationship with the Commercial Breaks on today’s date. *
Directing = * Relationship. Cardinality: for each Director, there are many Programmes; for each Programme, there is one Director. Participation: Director mandatory, Programme optional. *
Director = * Entity. The director of a programme. *
Director Name
Director Name = * Data element. Identifies the director of a programme. *
Displaced Spot = * Data flow *
Spot Number + Spot Price + Rate Moveability
+ Spot Booking Agreement
Disposal Copy Material = * Data flow *
Commercial Copy Number + Physical Copy
Disposal Date = * Data element. Date by which a piece of commercial copy must be destroyed. *
Episode = * Entity. An episode is one occurrence of a programme. *
Episode Number + Episode Scheduled Date
+ Episode Start Time + Episode End Time
Episode End Time = * Data element *
Episode Number = * Data element *
Episode Predicting = * Relationship. Cardinality: for each Episode, there are many Predicted Ratings; for each Predicted Rating, there is one Episode. Participation: Episode optional, Predicted Rating
mandatory. *
Episode Scheduled Date = * Data element *
Episode Start Time = * Data element *
Filming = * Relationship. Cardinality: for each Production Company, there are many Commercial Copy(s); for each Commercial Copy, there is one Production Company. Participation: Production Company
mandatory, Commercial Copy mandatory. *
Invoice = * Entity. A record of money owed by an advertising agency. *
Invoice Number + Invoice Total + Invoice Date
Invoice Date = * Data element *
Invoice Number = * Data element *
Invoice Total = * Data element. Derivable: sum of the Spot Price related to each Commercial Spot related to a given Invoice. *
Legal Placement = * Relationship. Keeps track of the ratecard-selling conditions concerning the price of a spot and the segments of time that it can occupy. Cardinality: for each Spot Rate, there are many Ratecard Segments; for each Ratecard Segment, there are many Spot Rates. Participation: Spot Rate mandatory, Ratecard Segment mandatory. *
Likely Spot Preemption = * Data flow. Trends in airtime sales indicate that this spot may be preempted. *
Spot Number + Product Name + Break Start Time
+ Spot Duration + Rate Moveability + Spot Price
+ Recommended Rate
Measuring = * Relationship. Keeps track of the actual ratings for an episode. Cardinality: for each Episode, there are many Actual Ratings; for each Actual Rating, there is an Episode. Participation: Actual Rating mandatory, Episode optional. *
Moveability = * Relationship. Keeps track of the ratecard segments in which a particular commercial spot may be transmitted. Cardinality: for each Commercial Spot, there are many Ratecard Segments; for each Ratecard Segment, there are many Commercial Spots. Participation: Commercial Spot mandatory, Ratecard Segment optional. *
Moveable Spots = * Data flow *
Break Start Time + {Spot Number + Spot Duration + Spot
Price + Rate Moveability + Spot Booking Agreement}
New Agency = * Data flow *
Agency Name + Agency Address + Agency Phone Number
New Agency Form = * Data flow *
Agency Name + Agency Address + Agency Phone Number
New Agency Transaction = * Data flow *
Agency Name + Agency Address + Agency Phone Number
New Breaks = * Data flow. Breaks progressively added to the breakchart for three months in the future. *
{Breakchart Date + Break Start Time + Break End Time
+ Break Duration}
New Programme = * Data flow. Describes a programme offered by an English or overseas programme supplier. *
Programme Name + Programme Type + Programme Description
+ Programme Duration + Programme Price
+ Programme Episodes + {Performer Name} + (Producer Name
+ Director Name) + Supplier Name
New Sales Executive = * Data flow *
Sales Executive Name + Sales Executive Address
+ Sales Executive Start Date
New Spot Rates = * Data flow. A transaction in the current computer system. *
Ratecard
Occupying = * Relationship. Keeps track of which spots are occupying a break so that the spot manipulators can make decisions about moving and slotting spots. Cardinality: for each Commercial Spot, there is one Commercial Break; for each Commercial Break, there are many Commercial Spots. Participation: Commercial Spot optional, Commercial Break optional. *
Occurring = * Relationship. Keeps track of the episodes of a programme. Cardinality: for each Programme, there are many Episodes; for each Episode, there is one Programme. Participation: Programme optional, Episode mandatory. *
Opposition Company = * Entity. Piccadilly competes with the government channels for viewing audiences. Piccadilly also competes with other commercial channels for the advertiser’s money. *
Television Company Name
Opposition Predicted Rating = * Data element. The rating predicted for one of the opposition company’s programmes. *
Opposition Programme = * Entity. Piccadilly’s programme purchasing and scheduling is influenced by the programmes planned for transmission by other commercial channels and by the government channels. *
Opposition Transmission Date + Opposition Transmission Time
+ Opposition Programme Name + Opposition Predicted Rating
Opposition Programme Name = * Data element. The name of a programme transmitted by an opposition company. *
Opposition Schedule = * Data flow *
Television Company Name + {Opposition Transmission Date
+ Opposition Transmission Time + Opposition Programme Name
+ (Opposition Predicted Rating)}
Opposition Transmission Date = * Data element. The date an opposition programme is scheduled for transmission. *
Opposition Transmission Time = * Data element. The time an opposition programme is scheduled for transmission. *
Performer = * Entity. Actor or actress appearing in a programme. *
Performer Name
Performer Name = * Data element. Identifier of an actor or actress appearing in a programme. *
Physical Copy = * A physical copy of the material to be transmitted for a commercial spot. Current system uses ACR. *
Piccadilly Budget Amount = * Data element. The portion of the campaign budget total that the agency intends to spend with Piccadilly. Units: pounds sterling. *
Planning = * Relationship. Cardinality: for each Opposition Company, there are many Opposition Programmes; for each Opposition Programme, there is one Opposition Company. Participation: Opposition Company optional, Opposition Programme mandatory. *
Predicted Rating = * Entity. Predicted ratings are used to plan advertising campaigns and programme scheduling. *
Predicted Rating Percentage + Predicted Rating Date
Predicted Rating Date = * Data element. Date on which a predicted rating was made. *
Predicted Rating Percentage = * Data element. Percentage of an audience type predicted to watch a given programme episode. *
Predicted Ratings = * Data flow. The Research Department’s predictions of programme ratings. *
{Breakchart Date + {Programme Name + Episode Number
+ {Audience Type + Predicted Rating Percentage
+ Predicted Rating Date}}}
Preempted Spot = * Data flow. A spot that has been preempted by another spot and dropped from the breakchart. *
Spot Number + Spot Duration + Rate Moveability
Preemption Replacement = * Data flow. Details of a spot that has been booked to replace a preempted spot. *
Agency Name + Product Name + Campaign Number
+ Preempted Spot + [Replacement Spot | Upgrade Unavailable]
Preemption Warning = * Data flow. A sales executive warning to an agency that a spot is in danger of being preempted. *
Agency Name + Product Name + Campaign Number
+ Spot Number + Breakchart Date + Break Start Time
+ Spot Duration + Recommended Rate
Preliminary Schedule = * Data flow. The Programme Transmission Department’s first cut of a programme transmission schedule. *
{Programme Transmission Date + {Programme Name
+ Episode Start Time + Episode End Time}}
Priced Time = * Data flow *
Agency Name + Product Name + Campaign Start Date
+ Campaign End Date + {Breakchart Date + {Break Start Time
+ Seconds Available + Total Available Price + Current Rate
+ Audience Type + Average Break Predicted Rating}}
Pricing Agreement = * Relationship. Keeps track of ratecard pricing conditions under which a spot is sold. Cardinality: for each Commercial Spot, there is one Spot Rate; for each Spot Rate, there are many Commercial Spots. Participation: Commercial Spot mandatory, Spot Rate optional. *
Producer = * Entity. The producer of a programme. *
Producer Name
Producer Name = * Data element. Identifies the producer of a programme. *
Producing = * Relationship. Cardinality: for each Programme, there are many Producers; for each Producer, there are many Programmes. Participation: Programme optional, Producer mandatory. *
Product = * Entity. Piccadilly needs to record which products are being advertised because some of the broadcasting rules govern the placement of commercials depending on the product. *
Product Name
Production Company = * Entity. Producer of television commercials. *
Production Company Name
Production Company Name = * Data element *
Product Name = * Data element. Identifier for one of the products represented by an advertising agency. *
Product Number = * Data element. Piccadilly’s unique identifier for a product. *
Programme = * Entity. A television programme made by Piccadilly or bought from a programme supplier. *
Programme Name + Programme Type + Programme Description
+ Programme Duration + Programme Price
+ Programme Episodes + Programme Purchase Date
Programme Description = * Data element. Synopsis of the contents of a programme. *
Programme Duration = * Data element. Running time of a programme.
Units: hrs/mins/secs. *
Programme Episodes = * Data element. The number of episodes of a programme covered by an agreement with a supplier or by Piccadilly’s internal production plans. *
Programme Name = * Data element. The name that uniquely identifies this programme, for example, News at Ten, Brideshead Revisited, Coronation Street. *
Programme Price = * Data element. Price paid to the supplier of a programme. Units: pounds sterling. *
Programme Purchase Agreement = * Data flow. Terms negotiated with an external supplier. *
Programme Name + Supplier Name + Programme Price
Programme Purchase Date = * Data element. Date of purchase from a programme supplier. *
Programme Schedule File = * Data store. File kept by the Research Department. *
{Programme Transmission Schedule + Preliminary Schedule
+ Predicted Ratings}
Programme Schedules = * Data store. File kept in the Sales Department. *
{Programme Transmission Schedule} + {Predicted Ratings}
Programme Supplier = * Entity *
Supplier Name
Programme Transmission Date = * Data element. The actual transmission date of a particular episode. *
Programme Transmission Schedule = * Data flow. Piccadilly’s planned transmission for the next quarter. *
{Programme Transmission Date + {Episode Number
+ Episode Start Time + Episode End Time + Programme Name
+ Programme Description + Programme Type
+ Predicted Rating} + {Break Start Time + Break End Time}}
Programme Transmission Time = * Data element. The actual transmission time of an episode. *
Programme Type = * Data element *
[First-Run Film | Sporting Event | Documentary | Talk Show | Old Movie]
* The types of programmes that Piccadilly transmits. Note that there are other programme types to add to these. *
Programming Plan = * Data store. File in the Programme Transmission Department. *
{Programme Name + Programme Type + Programme Description
+ Programme Duration + Programme Price
+ Programme Purchase Date + {Performer Name}
+ (Producer Name) + (Director Name) + Supplier Name
+ {Programme Transmission Date}} + Preliminary Schedule
+ Programme Transmission Schedule + Opposition Schedule
+ Programming Rules
Programming Rule = * Data element. A rule set by the Broadcasting Board. Each rule addresses some aspect of the mixture, content, and placement of programmes and commercial breaks. *
Programming Rules = * Data flow. File in the Programme Transmission Department. *
{Programming Rule}
Proximity = * Relationship. Keeps track of which breaks are within one hour of an episode so that the spot manipulators can apply the appropriate programming rules when moving and slotting spots. Cardinality: for each Commercial Break, there are many Episodes; for each Episode, there are many Commercial Breaks. Participation: Commercial Break mandatory, Episode optional. *
Publicizing = * Relationship. Keeps track of which product is advertised by a particular piece of commercial copy. Cardinality: for each Commercial Copy, there is one Product; for each Product, there are many Commercial Copy(s). Participation: Commercial Copy mandatory, Product optional. *
Purchase Decision = * Data flow *
Programme Name + Programme Type + Programme Description
+ Programme Duration + Programme Price
+ Programme Episodes + {Opposition Programme}
+ {Performer Name} + {Producer Name} + (Director Name)
+ Supplier Name + {Episode Scheduled Date
+ Episode Start Time + Episode End Time}
Ratecard = * Data flow. Prices, moveability, and preemption rules of time available for sale. *
Rate From Date + {Rate Spot Duration + {Rate Segment Day
+ {Rate Segment Start + Rate Segment End
+ {Rate Moveability + Spot Price}}}}
Ratecard File = * Data store. File kept by the Research Department. *
{Ratecard}
Ratecard Period = * Entity. Period during which given rates apply. *
Rate From Date
Ratecards = * Data store. File kept in the Sales Department. There is a duplicate file in the Commercial Booking Department. *
{Ratecard}
Ratecard Segment = * Entity. Continuous band of time defined on ratecard. *
Rate Segment Day + Rate Segment Start + Rate Segment End
Rate From Date = * Data element. The commencement date for a ratecard period. Format: Day/Month/Year. *
Rate Moveability = * Data element. Rate moveability as defined in the ratecard. *
[Fixed: fixed on a nominated day | Broad: moveable within a specified segment on a nominated day | ROD: run-of-day, moveable to any similarly priced segment on a nominated day | ROW: run-of-week, moveable to any similarly priced segment during a week]
Rate Segment Day = * Data element. Day(s) of week on which this segment of time occurs. *
[Weekday | Saturday | Sunday]
Rate Segment End = * Data element. The end time for a ratecard segment. *
Rate Segment Start = * Data element. The start time for a ratecard segment. *
Rate Setting = * Relationship. Keeps track of the ratecard segments applicable to a ratecard period. Cardinality: for each Ratecard Segment, there is one Ratecard Period; for each Ratecard Period, there are many Ratecard Segments. Participation: Ratecard Segment mandatory, Ratecard Period mandatory. *
Rate Spot Duration = * Data element. Duration of spots as defined in the ratecard. Units: seconds. *
[10|20|30|40|50|60]
Rating = * Entity *
Rating Date + Audience Type
Rating Date = * Data element. The date to which a rating refers. *
Rating Time = * Data element. Time to which an actual rating refers. Ratings are taken every minute. Units: hrs/mins/secs. *
Recommended Rate = * Data flow. The rate recommended for a spot that is in danger of preemption. *
Rate Moveability + Spot Price
Replacement Spot = * Data flow. A spot to replace another spot that has been preempted. *
Product Name + Spot Number + Spot Duration + Spot Price
+ Rate Moveability
Representation End Date = * Data element. Date an agency ends representing a product. *
Representation Start Date = * Data element. Date an agency starts representing a product. *
Representing = * Relationship. Keeps track of which agency is responsible for a product. Keeps track of historical relationships between agencies and products for collecting bad debts. Cardinality: for each Advertising Agency, there are many Products; for each Product, there are many Advertising Agency(s). Participation: Advertising Agency mandatory, Product mandatory. *
Representation Start Date + Representation End Date
Required Spot Duration = * Data element. Required length of commercial spots for a campaign. See Spot Duration for values. *
Revenue Reports = * Data flow. Computer reports used by management to help set safes targets. *
Breakchart Date + Daily Revenue + {Spot Number + Spot Rate
+ Product Name + Spot Transmitted Time + Spot Price}
Rule Effective Date = * Data element. The date that a new rule from the Broadcasting Board must be applied to all Piccadilly’s programme and commercial transmissions. *
Sales Executive = * Entity *
Sales Executive Name + Sales Executive Address
+ Sales Executive Start Date
Sales Executive Address = * Data element *
Sales Executive Name = * Data element *
Sales Executive Register = * Data store. File of sales executives’ responsibilities kept by the Sales Department. *
{Sales Executive Name + {Agency Name + Servicing Start Date
+ Servicing End Date}}
Sales Executive Start Date = * Data element. The date a sales executive starts working for Piccadilly. *
Sales Target = * Entity. The revenue that Piccadilly aims to make within a specified period. *
Sales Target From + Sales Target To + Sales Target Amount
Sales Target Amount = * Data element. The amount of a sales target. Units: pounds sterling rounded up to the nearest thousand. *
Sales Target File = * Data store *
{Sales Target Instructions}
Sales Target From = * Data element. The start date for a sales target. *
Sales Target Instructions = * Data flow. Set by Piccadilly management. *
Sales Target From + Sales Target To + Sales Target Amount
Sales Target To = * Data element. The end date of a sales target. *
Scheduling = * Relationship. Keeps track of the scheduling decisions made concerning episodes, commercial breaks, and broadcasting rules. Cardinality: for each instance of one Episode and one Broadcasting Rule, there are many Commercial Breaks; for each instance of one Commercial Break and one Broadcasting Rule, there are many Episodes; for each instance of one Commercial Break and one Episode, there are many Broadcasting Rules. Participation: Broadcasting Rule optional, Commercial Break optional, Episode optional. *
Seconds Available = * Data element. Derivable: Break Duration minus total of Spot Durations for all Commercial Spots that have an Occupying relationship with this Commercial Break. Units: seconds. Value range: 0 to 180. *
Seconds Sold = * Data element. Derivable: sum of Spot Durations for all Commercial Spots that have an Occupying relationship with a Commercial Break. *
Selected Ratings = * Data flow. Selected records from the television ratings report that are stored by the Research Department. *
Television Ratings Report
Selected Spots = * Data flow. Spots selected by an agency as part of a campaign. *
Agency Name + Campaign Number + Product Name
+ Campaign Start Date + Campaign End Date + {Spot Number
+ Spot Duration + Spot Price + Rate Moveability
+ ([Breakchart Date + Break Start Time | {Breakchart Date}])}
Selling = * Relationship. Keeps track of which sales executive is responsible for selling to an advertising agency. Cardinality: for each Advertising Agency, there are many Sales Executives; for each Sales Executive, there are many Advertising Agency(s). Participation: Advertising Agency mandatory, Sales Executive mandatory. *
Servicing Start Date + (Servicing End Date)
Servicing End Date = * Data element. The date a sales executive stops servicing an advertising agency. *
Servicing Start Date = * Data element. The date on which a sales executive starts servicing an advertising agency. *
Slotted Spot = * Data flow. Relocation of a spot due to its place being taken by another spot at a higher rate. *
Breakchart Date + Break Start Time + Spot Number
+ Spot Price + Rate Moveability + Spot Booking Agreement
Spot Booking Agreement = * Data element grouping. The conditions under which a spot is sold. *
[Spot Date Agreed + Spot Break Agreed | Spot Date Agreed + Spot Segment Agreed | Spot Date Agreed | Spot Week Agreed + {Spot Segment Agreed}]
Spot Break Agreed = * Data element. Agreed break in which a spot must be transmitted. *
Spot Cancellation = * Data flow *
Agency Name + Product Name + Campaign Number
+ Spot Number
Spot Date Agreed = * Data element. Agreed date on which a spot must be transmitted. *
Spot Duration = * Data element. Duration of a commercial spot. Units: seconds. *
[ 10 | 20 | 30 | 40 | 50 | 60]
Spot Measuring = * Relationship. Keeps track of the actual ratings for a commercial spot. Cardinality: for each Commercial Spot, there are many Actual Ratings; for each Actual Rating, there is one Commercial Spot. Participation: Actual Rating mandatory, Commercial Spot optional. *
Spot Number = * Data element. Identifier for a commercial spot. *
Spot Price = * Data element. Ratecard price for a spot rate. Units: pounds sterling. Value range: >= 150 and <= 25000. *
Spot Rate = * Entity *
Rate Spot Duration + Spot Price + Rate Moveability
Spot Rate File = * Data store. File in the current computer system. *
{Ratecard}
Spot Segment Agreed = * Data element. Agreed segment in which a spot will be transmitted. *
Spot Sticker = * Data flow *
Spot Number + Spot Duration + Spot Price + Rate Moveability
+ Spot Booking Agreement + Product Name
+ Campaign Number
Spot Transmitted Date = * Data element. The breakchart day on which a commercial spot is transmitted. *
Spot Transmitted Time = * Data element. The time when a commercial spot is transmitted. Units: hrs/mins/secs. *
Spot Upgrade Request = * Data flow. Increase in rate to avoid preemption. *
Agency Name + Product Name + Campaign Number
+ {Spot Number + Spot Duration + Rate Moveability}
Spot Week Agreed = * Data element. Agreed week during which a spot must be transmitted. *
Spots Transmitted = * Data flow *
Spot Transmitted Date + {Agency Name + {Campaign Number
+ {Spot Number + Spot Transmitted Time}}}
Suggested Campaign = * Data flow. Suggestions to an agency about the makeup of an advertising campaign. *
Agency Name + Product Name + Campaign Number
+ Campaign Start Date + Campaign End Date
+ {Required Spot Duration + Spot Price + Rate Moveability
+ ([Breakchart Date + Break Start Time | {Breakchart Date}])}
+ Target Rating Percentage + Campaign Predicted Rating
Suitable Break = * Data flow. Same definition as Moveable Spots. *
Suitable Time = * Data flow *
Agency Name + Product Name + Campaign Start Date
+ Campaign End Date + {Breakchart Date + Break Start Time
+ Seconds Available + Current Rate + Audience Type
+ Average Break Predicted Rating}
Supplier Name = * Data element. Identification for a programme supplier. *
Supplying = * Relationship. Keeps track of the programmes that are supplied by a programme supplier. Cardinality: for each Programme Supplier, there are many Programmes; for each Programme, there is one Programme Supplier. Participation: Programme mandatory, Programme Supplier optional. *
Target Audience = * Data element. The audience at which a campaign is aimed. See Audience Type for values. *
Target Rating Percentage = * Data element. Percentage rating aimed for by a campaign. Integer. Value range: > 0 and <= 100. *
Television Company Name = * Data element *
Television Ratings Report = * Data flow. Statistical analysis of programmes
and types of viewers showing ratings that are measured every
5 minutes during the viewing day. *
{Rating Date + {Programme Name + Rating Time
+ {Audience Type + Actual Rating Percentage}}}
Time Sold = * Data flow *
{Breakchart Date + {Break Start Time + Break Sold Value
+ {Seconds Sold + Rate Moveability}}} + Total Sold Value
Total Available Price = * Data element. Derivable: ((Available Seconds/30) x Spot Price for fixed spot in Ratecard Segment corresponding to Commercial with Spot Duration equal to 30 sees). *
Total Sold Value = * Data element. Derivable: sum of Break Sold Value for all the Commercial Breaks within a specific period. *
Transmission = * Relationship. Keeps track of when an episode of a programme is transmitted. Cardinality: for each Episode, there is one Breakchart Day; for each Breakchart Day, there are many Episodes. Participation: Episode optional, Breakchart Day optional. *
Programme Transmission Time + Programme Transmission Date
Transmission Log = * Data store *
Spot Transmitted Date + {Spot Number
+ Spot Booking Agreement + Spot Price + Rate Moveability
+ Product Name + Spot Transmitted Time}
Transmission Times = * Data flow *
Spot Transmitted Date + {Spot Number
+ Spot Booking Agreement + Spot Price + Rate Moveability
+ Product Name + Spot Transmitted Time}
Transmission Transaction = * Data flow *
Spot Transmitted Date + {Spot Number
+ Spot Booking Agreement + Spot Price + Rate Moveability
+ Product Name + Spot Transmitted Time}
Transmitting = * Relationship. Keeps track of when a spot is transmitted. Cardinality: for each Commercial Spot, there is one Commercial Break; for each Commercial Break, there are many Commercial Spots. Participation: Commercial Spot optional, Commercial Break optional. *
Spot Transmitted Time + Spot Transmitted Date
Unavailable Campaign = * Data flow. Piccadilly has no time available to suit the campaign requirements. *
Agency Name + Product Name + Target Audience
+ Target Rating Percentage + Campaign Duration
+ Campaign Start Date + Campaign End Date
+ Required Spot Duration
Unavailable Slot = * Data element grouping. A suitable slot cannot be found for this spot at this price. The recommendation is to upgrade the spot to the next higher price. *
Spot Number + Spot Price + Spot Duration
Upgrade Confirmation = * Data flow. Agreement with an agency to upgrade the rate of a spot. *
Agency Name + Campaign Number + Product Name
+ Spot Number + Spot Duration + Spot Price
+ Rate Moveability
Upgraded Rate = * Data flow *
Spot Booking Agreement + Spot Price + Rate Moveability
Upgrade Unavailable = * Data flow. Message sent to an agency when it is not possible to upgrade a preempted spot. *
Spot Number + Spot Duration