Here they are

Databese 3/CIS275_DB3.docx

Talent Event Agency

Users' World

Scenario

The TalentEventAgency is a small business that connects clients wishing to host a party with an event location and entertainment. They are still a new organization having been formed at the start of 2012 by Scarlet O'Hound. TEA employs ten agents that will represent clients and/or talent. Currently TEA refers customers to event venues at 20 locations around the Portland metropolitan area. The company is still growing and the list of locations is expected to triple in the next six months. Entertainment for the events range from acrobats to xylophone players, and TEA represents 40 local people who entertain on a part-time, as-needed basis. TEA currently has a customer list of 75 clients not all of whom have booked events.

TEA would like to have a computerized system to keep track of their clients, talent, and venues. They want to be able to know which agent represents each client and entertainer. They also want to keep track of the activities by client, location, performer and the dates and times. Because the talent is part-time, it is necessary for TEA to make telephone reminders the day before an event so that the performer knows exactly what is expected and where they are to go.

Agents are not the only employees of TEA but they are the ones important to this system. Employee information includes full name (first, middle, last), home phone or personal cell, Social Security Number, number of dependents, marital status, full address (two address lines plus city, state, zip code), date of birth, date of hire, title (agents have the title of Agent I, Agent II, Senior Agent, Managing Agent, Principal Agent), office extension, date of title (they can be promoted and TEA wants to keep track of this.

Notes:

· People names include first name, middle name, and last name.

· An entertainer will have only one talent that is tracked at this time.

· An event will have one performer at this time.

· After the initial design work in DB2, we'll combine EMPLOYEES with AGENTS for

simplification of our final product. Technically, AGENTS is a subtype of the supertype EMPLOYEES.

· Added agent job titles to show how to handle a many-to-many (M:N) relationship: an agent holds from one to many job titles and a job title applies to zero to many agents.

Forms

Agents are responsible for bringing in the clients and talent to be signed with TEA. They add the new client or talent by way of the following two forms. Once an agent has signed the client or talent, they will receive commission based on events that their client has hosted or for which their talent has performed. These commissions will not be tracked by the database at this time.

Anyone employed by TEA can sign up a new venue or book a new event (TEA is not currently interested in tracking employees arranging events). New events must utilize existing clients, talent, and locations; of course, they can be added at the time of the booking. The forms for new events and venues follow:

Reports

Daily Event List . . .

Daily TEA Events for Saturday, July 14, 2012	Rpt#300714
VENUE	START TIME	END TIME	CLIENT	TALENT
Long Acres	1:00 pm	4:00 pm	Hammond X Benedict	Hollie Wood
	7:15 pm	10:30 pm	Cal Q Layting	Kari Vann
The Galleria	12:00 pm	3:00 pm	Samson Ite	Hugo Guryl
	6:00 pm	8:45 pm	Otto B Shott	Eva D Struckson
Jay Dawg House	1:00 pm	3:30 pm	N. Hance	Cee Weed

Daily Call List to remind entertainers of their scheduled performances . . .

TEA Call List for Events on Saturday, July 14, 2012	Rpt#310714
Talent	Phone	LOCATION	START TIME	END TIME
Cee Weed	503-555-5656	Jay Dawg House	1:00 pm	3:30 pm
Eva D Struckson	503-555-3194	The Galleria	6:00 pm	8:45 pm
Hollie Wood	503-555-7531	Long Acres	1:00 pm	4:00 pm
Hugo Guryl	503-555-9764	The Galleria	12:00 pm	3:00 pm
Kari Vann	503-555-9512	Long Acres	7:15 pm	10:30 pm

On-Demand (as requested, not run on a schedule) Talent Report is a list of all TEA represented talent on the books as available . . .

TEA Talent Report for April 1, 2012	Rpt#120401
Name	Phone	Talent
Lyda Candle	503-555-1234	Balloon Artist
Blu Collar	503-555-4567	Clown
Juan A Dance	503-555-7890	Magician
Ben E. Fitz	503-555-4321	Guitarist
Ed U. Kated	503-555-7654	Clown
Manny Miles	503-555-0987	Xylophone player
Hollie Wood	503-555-7531	Harpist
Kari Vann	503-555-9512	Balloon Artist
Hugo Guryl	503-555-9764	Sings and plays the Guitar
Eva D. Struckson	503-555-3194	Magician
Cee Weed	503-555-5656	Clown

Monthly Agent Report showing each agent and who they represent for events during the month . . .

TEA Agent Mindy Nile Client/Talent Report for month of May, 2012	Rpt#250512
Client	Event Date	Start Time	Location
Bill Payshent	May 3	1:00 pm	The Galleria
Viola Tors	May 15	2:45 pm	The Galleria
Talent	Event Date	Start Time	Location
Juan A Dance	May 3	1:00 pm	The Secret Garden
	May 7	10:00 am	Playtime Barn
Eva D. Struckson	May 3	7:30 pm	Jay Dawg House
	May 17	3:00 pm	The Secret Garden

Mailing Labels . . .

Name

Address1

Address2

City, State Zipcode

Project Plan

Mission Statement

The TalentEventAgency requires a computer system to keep track of their clients, talent, events, and venues so that they can further the company goal of supplying entertainment and locations for their customer base to enjoy quality events.

Copied from the scenario, "a computerized system to keep track of their clients, talent, and venues".

Mission Objectives

TEA needs a system to track agent representations of clients and talent. Agents recruit clients that wish to hold special events or parties. The system requires input of new clients and reports that can be generated to show activity -- the addition of customers as well as the events they request. Agents manage talent which means they will be adding new entertainers as well as matching them to events where they perform.

TEA needs a system to record events for which they provide entertainment and locations. Customers having a party request a location and a performer. Locations can be added to the system and reports must be available to show new locations as well as information about the events held there. Entertainers are scheduled to perform at customer parties so the events need to be entered into the system and reports need to be built showing that information.

Copied from the scenario, "They want to be able to know which agent represents each client and entertainer. They also want to keep track of the activities by client, location, performer and the dates and times."

Mission objectives can (instead) be shown as a bulleted list of activities and deliverables required of the database system to support TEA:

· Agents record new clients

· Agents record new talent (performers)

· TEA employees record new venues (locations)

· TEA employees record new events (parties)

· Produce a daily event list showing the when, where, and who

· Print a daily call list so TEA can telephone performers (reminder calls)

· The talent report is a list of all TEA represented talent on the books

· Produce a monthly report of agents and events (with clients and locations)

· Print mailing labels

· Keep track of agent job titles

· Report of events for each venue

Requirements Gathering - Deliverable is the data model

Data Listing

Origin (subject)	Data (field, property, characteristic)	Notes about the data
EMPLOYEES	FullName	First,Middle,Last
	Phone	home
	Cell	personal
	Ssn
	Dependents	Number of dependents
	Marital Status	List of values
	Address	two lines
	City
	State
	Zipcode
	Date_of_Birth
	Date_of_Hire	TEA began business on 01/01/2012
AGENTS	EMPLOYEE	Agent "IS A" Employee
	OfficeExtension
	Title	List of values
	Date_of_Title	Start date for title
CLIENTS	Date_of_Entry
	Name	? Is this First,Middle,Last ?
	CompanyName
	Address	two lines
	City
	State
	Zipcode
	Phone	up to three
	Email	up to two
	Website	URL to company website
	AGENT	Connects Client to Agent
	Comments	Notes about the client
PERFORMERS	Date_of_Entry
	Name	? Is this First,Middle,Last ?
	Description	Describes the talent
	Address	two lines
	City
	State
	Zipcode
	Phone	up to three
	Email	only one
	AGENT	Connects Performer to Agent
	Comments	Notes about the performer
EVENTS	Date_of_Event
	StartTime
	EndTime
	Date_of_Entry
	CLIENTS Name	Connects Event to Client
	PERFORMERS Name	Connects Event to Performer
	VENUE	Connects Event to Venue
	Comments	Notes about the event
VENUES	Date_of_Entry
	Name
	Description	Describes the Venue
	Address	two lines
	City
	State
	Zipcode
	Phone	one
	Email	one
	Website	URL to venue website
	Comments	Notes about the location

ERD1 - Simple	ERD2 - With Maximum Cardinalities

Statements of relationships

A client is represented by (one and only) one agent.

An agent represents (from zero to) many clients.

A performer is represented by (one and only) one agent.

An agent represents (from zero to) many performers.

An agent has (one to) many job titles.

A job title is given to (zero to) many agents.

A client holds (zero to) many events.

An event is thrown by (one and only) one client.

A performer entertains at (zero to) many events.

An event has (one and only) one performer.

An event is held at (one and only) one venue.

A venue can be the site of (from zero to) many events.

Note that the optional minimum cardinalities are shown inside the parentheses. You are not required to have the minimum cardinalities now - maximum *and* minimum cardinalities are shown on the next ERD.

Continuing with our TEA scenario and design, you have three tasks to complete DB3: (1) Create an ERD (ERD3) showing both minimum and maximum (relationship) cardinalities. (2) Expand your original data listing to contain domain specifications: identifiers, attribute cardinalities, data types, domain names and constraints. (3) Turn your ERD and domain specification into relations.

ERD3 - With Minimum and Maximum Cardinalities (DB3: for 10 points, please develop the ERD showing minimum and maximum cardinalities between entities. The following two ERDs are examples.)

Domain Specification (DB3: for 20 points, please expand your original data list to contain the domain specifications of possible identifier, attribute cardinality, data type, name the domain and list the constraints. ) The following table is an example that gives you everything you need for the AGENTS entity and data items.

ENTITY	Attribute	ID	Cardinality	Data Type	Domain	Constraints
AGENTS	AgentID	ID	1:1	INT	Identity (1,1)	Unique, system generated
	FirstName		1:1	CHAR(20)	Name
	MiddleName		0:1	CHAR(20)	Name	Can be initial (one letter)
	LastName	ID	1:1	CHAR(30)	Name
	SocSecNbr	ID	1:1	INT	Number	Nine digits, no format
	Dependents		0:1	INT	Number	Default 0
	MaritalStatus		0:1	CHAR(1)	List of values	M, S, D, W
	DateBorn		0:1	DATE	Date	> 1/1/1910
	Address1		1:1	CHAR(50)	Address
	Address2		0:1	CHAR(50)	Address
	City		1:1	CHAR(30)	Name
	State		1:1	CHAR(2)	State	Default 'OR'
	Zipcode		0:1	CHAR(12)	Zip	Numbers and/or characters
	HomePhone		0:1	CHAR(12)	Phone	12 digits, no format
	CellPhone	ID	0:1	CHAR(12)	Phone	12 digits, no format
	DateHired		1:1	DATE	Date	>= 1/1/2012
	Updt		1:1	DATETIME	DateTime	System generated timestamp
	PERFORMERS		0:M	OBJECT	PERFORMERS	optional relationship
	CLIENTS		0:M	OBJECT	CLIENTS	optional relationship
	AGENTS_TITLES		1:M	OBJECT	AGENTS_TITLES	mandatory relationship

Database Design - Deliverable is the physical database design

Relations from the ERD and Domain Specification (DB3: for 20 points, please turn the ERD and domain specification into relations. Examples below.)

RELATION (Attribute1, attribute2, attribute3, Attribute4, ..., attributeN)

Where RELATION is the name of the ENTITY, Attribute1 is the identifier (the Primary Key), and Attribute4 makes the relationship (implemented as a Foreign Key). PK is underlined and FK is italicized; an attribute can be both PK and FK.

CUSTOMERS (CustomerNumber, FirstName, LastName, ...)

ORDER (OrderNumber, OrderDate, CustomerNumber)

ORDER_DETAIL ( OrderNumber , LineNumber, PartNumber, Quantity)

Each relation now goes through the normalization process to consider multi-valued attributes and attribute dependencies.

1NF - does relation meet the requirements for a table (one value per cell)?

2NF - does every non-key attribute fully depend on the whole primary key?

3NF - is every non-key attribute non-transitively dependent on the primary key?

BCNF - is each determinate a candidate key?

4NF - are there any multi-valued denpendencies?

5NF - is every join dependency a consequence of the candidate keys?

DKNF - is every constraint on the relationship dependent only on key and domain constraints?

YOUR GOAL: ALL TABLES CONTAIN NON-KEY COLUMNS THAT ARE DEPENDENT ON THE WHOLE KEY AND NOTHING BUT THE KEY.

Here is an example:

AGENTS ( AgentID, FirstName, MiddleName, LastName, SocSecNbr, Dependents, MaritalStatus, DateBorn, Address1,

Address2, City, State, Zipcode, HomePhone, CellPhone, DateHired, Updt )

· AGENTS.AgentID (PK) determines every other attribute in the relation . . . AGENTS is normalized to 3NF.

· SocSecNbr is no longer used to uniquely identify people so it is not considered a candidate key and therefore not a determinate of other fields.

This normalization step is not a requirement for your assignment

and the rest of the normalized relations will be given to you as part of the answer file for DB3.

PERFORMERS

VENUES

AGENTS

EVENTS

CLIENTS

representrepresent

bookentertains at

held at

EMPLOYEES

JOB_TITLES

have

IS A

�

PERFORMERS

VENUES

AGENTS

EVENTS

CLIENTS

represent

represent

book

entertains at

held at

EMPLOYEES

JOB_TITLES

have

PERFORMERS

VENUES

AGENTS

EVENTS

CLIENTS

M:11:M

1:MM:1

M:1

JOB_TITLES

M:N

I decided to incorporate

EMPLOYEES data in with

AGENTS data making one entity

�

PERFORMERS

VENUES

AGENTS

EVENTS

CLIENTS

M:1

1:M

1:M

M:1

M:1

I decided to incorporate EMPLOYEES data in with AGENTS data making one entity

JOB_TITLES

M:N