Deborah Armstrong
10270840
CIS 64A
Lab 1 final
ExtendedEntity Relationship Model (text-only description)
October 26, 2018
 
Note1: CloudTV is a fictional construction invented specifically for this assignment.

CloudTV's mission is to provide streaming television programming to U.S. residents via the internet. It competes directly with XFinity, Uverse, and DirectTV. It also indirectly competes with Amazon video services, Netflix and Hulu.

						Requirements

Subscribers have different membership levels. Each level gives them access to an increasing number of channels. A channel can be local broadcast, national (such as CNN) or created by CloudTV to furnish content from different distributors. Subscribers can pay extra to view channels live and/or record programs on their own to the cloud. CloudTV also maintains recordings and streams for every show it airs.

Subscribers log in to a web-based client that queries the database to build a user-friendly front end to enable the subscriber to enjoy the entertainment.

CloudTV maintains a constantly updating guide with available shows. Subscribers can search for favorite actors or series. Show descriptions tell subscribers about the program. Subscribers can watch individual shows on demand or just let a particular channel play. They can also channel surf so the database needs to tell them what is on when live.

CloudTV also needs to track licensing restrictions for each show. For example, some programs can only be aired twice a week under their contract with the distributor, and in other contracts a maximum number of subscribers can have simultaneous access. Some shows can only air live, while others can only, according to contract terms be viewed on demand and expire after a certain period. Some shows cannot be recorded by subscribers, others can, but the recordings expire after a certain date.

With other contracts, subscriber access is dependent on the subscriber's physical location. For example, people on the west coast won't want to see local news for New York. Subscribers can also delete channels from their profile so they won't have to deal with ponography or religion.

Distributors range from traditional television networks to those who only offer web-based programming. Some movie studios also distribute programming directly to CloudTV. In my conceptual design I call these distributors "providers" a superclass which encapsulates networks and distributors.

CloudTV also tracks statistics; for example, it's useful to know which zip code has the most subscribers and whether  many are ponying up for a higher tier. Who is watching which shows is also valuable data for marketing.

To limit the scope of this assignment, advertisers are not discussed.

This outline defines the conceptual model of the CloudTV database which is at the heart of the business.

Note2: In the below outline, parenthesis describe in English the purpose of the entity, attribute or relationship. Square brackets denote the technical terms identifying the item. Curly braces indicate items belonging to another item (E.G. a subclass belongs to a superclass, an attribute belongs to an entity.)

						MAIN ENTITY TYPES

CHANNEL (analogous to TV channel) [superclass strong, Role1: groups shows logically, role2: identifies  groups of shows broadcasting live]
	{
	LOCAL CHANNEL (for subscriber's region) [subclass of channel]
	NATIONAL CHANNEL (all regions) [subclass of channel specialization with Local channel and CloudTV Channel with partial overlap]
	CLOUDTV CHANNEL (created by CloudTV to show content that's not on other services) [subclass of channel, specialization with national channel and local channel with partial overlap.]
	}

SHOW (analogous to TV program) [superclass, strong]

Series (group of shows) [superclass, strong]

ACTOR (person connected with show's celebrity) [superclass, strong]
	{
	PRODUCER (human who produces show) [subclass of actor, see Note3]
	}

PROVIDER (creates and/or distributes shows, such as Paramount) [role1: manufactures shows, role2: purchases and resells shows, role3: manages show licensing, superclass, strong]
	{
	NETWORK (such as NBC or Fox) [subclass of provider, disjoint specialization with distributor]
	DISTRIBUTOR (middle-man who arranges for the shows to stream, such as Hulu) [subclass of provider, disjoint specialization with network]
	}

RECORDING (created by subscriber or CloudTV service) [superclass, weak, depends on show]

STREAM (details of stream) [superclass, weak, depends on show]

LOCALE (generalizes state, county, city and cip for purposes of both locating subscribers and defining license terms) [strong]

SUBSCRIBER (user who pays for service) [superclass, strong]
TIER (membership level) [superclass, weak depends on subscriber]

Note3: There's nothing special about producer; it's just another celebrity, with the same attributes,  thus it can be a subclass of actor.

						Entity Attributes
[Note4: single value attributes unless specified otherwise]

CHANNEL
	{
		{
		Channel ID (unique provided by database) [key]
		User_Number (identifies specific channel for end user, [not unique]
		Channel Description (may give number of channel on competing network or broadcast) [multi-valued]
	License restrictions (codes for identifying what can and cannot be streamed/recorded by and for subscribers) [multi-valued]
		}
	LOCAL CHANNEL
		{ [inherits channel attributes]  }
	NATIONAL CHANNEL
		{ [inherits channel attributes]}
	CLOUDTV CHANNEL
		{ [Inherits Channel attributes]}
	}

SHOW
	{
	Show ID (unique provided by database) [key]
	Show Name (User friendly name for subscriber) [not unique]
	Show description (user friendly for subscriber)
	Show genre (such as commedy or drama) [multi-valued]
	Show original air date (when show first aired)
	Show Expiration Date (when license restrictions prevent it from no longer airing)
		}

SERIES
	{
	Series ID (unique provided by database) [key]
	Series Name (User friendly name for subscriber) [not unique]
	Series description (user friendly for subscriber)
	Series genre (such as commedy or drama) [multi-valued]
	Series original air date (when show first aired)
	Series  Expiration Date (when license restrictions prevent it from no longer airing)
	Series-License-restrictions (codes to indicate these to supercede show if required) [mnulti-valued]
}
	
ACTOR
	{
		{
		Actor ID (unique provided by database) [key]
		Actor Name (user friendly for subscriber search)
	}
	PRODUCER
		{ [Inherits from actor] }
	}

PROVIDER
	{
		{
		Provider ID (unique provided by database) [key]
		Provider name (I.E. Amazon video services) [multi-valued]
		}
	NETWORK
		{
		Network Id (unique provided by database) [key]
		Network name (user friendly such as CBS)
		}
	DISTRIBUTOR
		{
		Distributor ID (unique provided by database) [key]
		Distributor name (user friendly, such as Netflix)
		}
	}

Licensee
	{
	Licensee ID (provided by database) [key, unique]
	Licensee Codes (determines restrictions for all its content) [multi-valued] 
	}

RECORDING
	{
	Recording ID (unique provided by database) [key]
	Filename full path (of recording)
	Filename server (where recording is stored)
	Expiration date (when to remove) [can be null]
	}

STREAM
		{
	Stream ID (unique provided by database) [key]
	Stream URL (for invoking stream) 
	Stream server (where stream lives)
	Stream Expiration date (when to remove) [can be null]
	}

LOCALE
	{
	Time zone (for local time in that locale)[set]
	State [set]
	County [set]
	City
	Zip [set]
	}

SUBSCRIBER
	{
	Subscriber ID (unique provided by database) [key,]
	Subscriber Name (user's official name) [composite derived)
	Subscriber First Name
	Subscriber Last Name
	Subscriber address house number, street name)[multi-valued]
	Subscriber email
	Subscriber Full Address (from address plus city state zip) [composit and derived from relationship to locale]
	}

TIER
	{
	Tier ID (provided by database) [key]
	Tier name (used for marketing friendly name for subscriber) [not unique]
	Tier restrictions (codes to indicate what can and cannot be played at this subscription level) [multi-valued)
	}

						Relationships

Note5: these relationships *DO* have names, which are not indented

Channel-Collection
	{
	Channel
				[Relates 1:M to provider, total participation]
		[Relates 1:N to licensee, partial participation]
		[subclasses Local Channel, National Channel and CloudTV Channel inherit relationships]
		}

	TV Program
	{
	Show
		{
		[Relates 1:n to channel, total participation]
		[Relates 1:n to actor, total participation]
		[Producer inherits relationship of actor]
		[Relates 1:N to provider, total participation]
		[Relates 1:N to recording, total participation]
		[Relates 1:n to licensee, partial participation]
		[Relates 1:N to series, partial participation]
		[Relates 1:n to tier, total participation]
		}
	Series
		{
		[Relates 1:M to show, total participation]
		}
		}

People
	{
		Actor
		{
		[Relates 1:N to show, total participation]
		}
	Producer
		{
		[Relates 1:1 to show, total participation]
		}
	}

Membership -- subscriber access
	{
		Subscriber
		{
		[Relates 1:1 to locale,  total participation]
		[Relates 1:1 to tier, total participation]
		[Relates 1:M to channel, partial participation]
		[Relates 1:M to recording, partial participation ]
		}
	Tier
		{
		[Relates 1:M to subscriber, total participation]
		[Relates 1:N to Channel, total participation]
[		Relates 1:N to Provider, partial participation]
		}
}

Distribution
	{
		Provider
		{
		[Relates 1:N to show, total participation]
		[Relates 1:N to channel, total participation]
		[relates 1:N to recording, total participation]
		[Relates 1:N to licensee, total participation]
		[Network and distributor inherit relationships]
		}
	Show
		{
		[Relates 1:N to channel, total participation]
		[Relates 1:N to Provider, total participation]
		[Relates 1:N to tier, total participation]
		[ Relates 1:1 to series, total participation]
	[Relates 1:1 to licenseee, total participation]
		}
	Series
		{
		[Relates 1:M to show]
		}
	}

Recording-Info
	{
		Recording
		{
		[Relates 1:N to subscriber, total participation]
		[relates 1:n to licensee, partial participation]
		[Stream inherits relationships]
	}
	Subscriber
		{
		[Relates 1:N to recording]
		}
	}

** End of lab **