Transcript Berkeley Broadcasting Company

Berkeley Broadcasting
Company
Database designed by:
Ephrat Bitton
Calvin Chou
Shusaku Kawamoto
Abhay Mumbare
BBC in the Making: Overview






About BBC
Operations
Cost and revenue sources
Database Requirements
Queries/reports
Future applications/improvements
The BBC



Privately owned TV station
Airs family-oriented programs for Western
half of USA
Programming includes:



News
TV Series
Movies
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
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
Revenue
Profit is generated from:
 Paying viewers
 Advertising


Timeslots allotted in 30-second segments
Cost predefined for particular blocks of time
each day
Competition
Competitors include:
 National cable broadcasting companies
 Local (free) stations
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
(0,N)
Advertiser
Employee
(1,N)
Buys time for
(0,N)
supervises
d
(0,1)
(1,1)
Commercial
Newscaster
Staff/Salesperson
(0,M)
Has
discount
(0,N)
has
gets contract
(0,N)
(1,N)
(1,1)
Subscriber
(0,N)
(0,N)
Time slot
(1,1)
Interested
Makes
(0,N)
Rates
Airs
(0,M)
(1,N)
TV Series
TV show
(1,1)
(1,M)
Movies
d
d
Syndicated
Other
Newscast
(1,1)
Purchases
from
(1,N)
Agency
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
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”
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.
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.
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
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.