Berkeley Broadcasting Company

1
Berkeley Broadcasting Company

• Database designed by
• Ephrat Bitton
• Calvin Chou
• Shusaku Kawamoto
• Abhay Mumbare

2
BBC in the Making Overview

• About BBC
• Operations
• Cost and revenue sources
• Database Requirements
• Queries/reports
• Future applications/improvements

3
The BBC

• Privately owned TV station
• Airs family-oriented programs for Western half of
  USA
• Programming includes
• News
• TV Series
• Movies

4
Operations

• Produces own morning and evening newscast
• Purchases syndicated programs from agencies
• Requires a fee per airing, depending on
  popularity and season of episode
• Purchases rights to air a movie from its producers

5
Operating Costs

• Aside from overhead, costs include
• Gratuities to producers of syndicated programs
• Employee salaries
• Also pays membership fee for coalition of cable
  broadcasting companies

6
Revenue

• Profit is generated from
• Paying viewers
• Advertising
• Timeslots allotted in 30-second segments
• Cost predefined for particular blocks of time
  each day

7
Competition

• Competitors include
• National cable broadcasting companies
• Local (free) stations

8
Database Requirements

• Up-to-date availability of advertisement
  timeslots and pricing info
• Tracking of customer ratings for future
  programming choices
• Proper maintenance of employee info
• Ability to retrieve data for analysis or to
  generate reports

9
(0,N)
Advertiser
supervises
Employee
(1,N)
Buys time for
(0,N)
d
(0,1)
(1,1)
Newscaster
Staff/Salesperson
Commercial
Has discount
(0,M)
(0,N)
has
gets contract
(1,N)
(1,1)
Subscriber
(0,N)
(0,N)
(0,N)
Makes
Time slot
Interested
(0,N)
(1,1)
Rates
Airs
(0,M)
(1,N)
(1,1)
TV show
TV Series
(1,M)
Movies
d
d
Purchases from
Newscast
Syndicated
(1,N)
Other
Agency
(1,1)
10
Functions of Database

• Electronic Forms for data entry into database.
• Update Query to update information on customer.
• Query/Form to retrieve important information
  for analysis

11
Electronic Forms

• Data entry into specific fields in tables for
  easy retrieval and analysis in the future
• Before Data stored in flat files that violated
  referential integrity constraints. Data also not
  consistent, thus making analysis difficult and
  time-consuming
• After Data entered via electronic forms.
  Referential integrity constraints enforced
  through use of Combo Boxes

12
Update Query

• Subscribers get a rate reduction after being a
  customer for 1 year
• Before a manual search and update is required to
  make sure the most updated subscription rate is
  invoiced to the customer and the invoice is
  generated.
• After an update query is used to simultaneous
  update all the subscription rate and a report
  is used to generate all customer invoices.

13
Queries/Reports

• Applications
• 1) Salesperson receives call from Advertiser who
  wishes to air a commercial in a particular time
  slot
• 2) The CEO wishes to review the revenue
  generation by BBC through the airing of
  commercial
• 3) A customer rating report is required to update
  the pricing of advertisements.

14
Future Improvements (technical)

• Impose data validation rules on the fields in the
  electronic form this imposes consistency in the
  entered data
• Implement a security system to protect data

15
Future Applications

• Establish web interface to allow subscribers to
  choose the program they wish to view. This
  will boost ratings and generate more revenue from
  advertisements.