University of Arkansas

Walton College

The Sam M. Walton College of Business

HALLUX PRODUCTIONS

Hallux Productions (HP) is a small music publishing and management company focusing on local and regional artists in the Mid-South. HP aims to be a "one-stop shop" for bands and individual music artists by offering recording contracts, live booking services, marketing, and album sales and distribution. Income is generated through two primary sources: album sales and booking services. To assist in this process, HP employs agents who search for talent, execute and manage recording contracts, and schedule live appearances. HP also maintains relationships with venues in the areas it serves to provide a steady source of talent for the locations and a stream of events for the artists. In return, HP earns a percentage of ticket sales or cover charges for each performance.


The Hallux database contains information that provides a snapshot of the company over all of the ideas discussed above. The Contract table specifies the terms of the contract, which may be one of many possibilities: duration, a certain number of albums, a certain number of live performances, or perpetual. It also specifies the percentage to be earned by the client on album sales and live performances. An agent's contact information, hire date, and status is stored in the Agent table. Album information is recorded in the Album table and live bookings are tracked in the Performance table. The tables containing the most amount of data are the Order tables: Order_Detail, Order_Header, and Order_Source. These tables, in aggregate, detail orders that HP has managed and contain the source, date, total, and the contents of the order. Finally, HP keeps a record of the clients in the Band table. A contact person, status, members, members' contact information, and instruments are all stored.

Entity Relationship Diagram

Hallux ERD

Metadata

Attribute Name Description Possible Values
Agent_ID Agent_ID is a Person_ID. Uniquely identifies an Agent Person_ID
Agent_Status_Code Whether an agent is active or inactive "A"=Active, "I"=Inactive
Album_Count Number of albums to be produced under a contract  
Album_ID Uniquely identifies an album.  
Album_Name The name of an album  
Album_Rev_Pct The percentage the band makes on each album "0.00-1.00" i.e. 0.20 (20%)
Band_ID Uniquely identifies a band.  
Band_Name The name of a Band  
Band_Status_Code Whether a band is active or not "A"=Active, "I"=Inactive
Begin_Date The date a contract begins  
Birthdate The birthdate an individual customer provides for their online profile  
City The city associated with a certain ZIP code  
Commission The percentage an agent gets from his or her sales "0.0000-1.0000" i.e. 0.0650 (6.5%)
Contact_Phone The phone number for the contact person at a Venue  
Contract_ID Uniquely identifies a contract  
Customer_ID Uniquely identifies a customer  
Duration_Seconds The length of the song in seconds i.e. 180, 220, 167
Email The e-mail address for a customer or person  
End_Date The date a contract ends  
Favorite_Album This is an Album_ID representing a customer's favorite album Album_ID
Favorite_Band This is a Band_ID representing a customer's favorite band Band_ID
Favorite_Song This is a Song_ID representing a customer's favorite song Song_ID
First_Name The first name of an individual customer or person  
Formation_Date The date a band officially formed  
Gender Whether the customer is male or female "M"=Male, "F"=Female
Genre A genre of music  
Genre_ID Uniquely identifies a genre  
Hire_Date When an agent was hired  
Instrument_ID Uniquely identifies an instrument  
Instrument_Name The name of the instrument Vocals, Piano, Guitar, etc
Item_Description A description of the item  
Item_ID This is either an Album_ID, Song_ID, or Video_ID Album_ID, Song_ID, or Video_ID
Item_Type What type of item this is Album, Song, or Video
Item_Type_ID Uniquely identifies an item type  
Join_Date The date a band member joined the band  
Last_Name The last name of an individual customer or person  
Latitude The latitude of the location represented by the ZIP code  
Line_Number The line number of that item on an order.   
Live_Count The number of live performances in a contract  
Live_Rev_Pct The percentage the band makes on each live performance "0.00-1.00" i.e. 0.20 (20%)
Longitude The longitude of the location represented by the ZIP code  
Member_ID Member_ID is a Person_ID. This uniquely identifies a member of a band.  
Member_Status_Code Whether a member is active or inactive "A"=Active, "I"=Inactive
Name The full name of a customer. Either a company name or the full name of an individual  
Order_Date The date and time an order is placed i.e. 9/1/2007 3:26 PM
Order_ID Uniquely identifies an order  
Order_Source_ID Uniquely identifies the source of an order.  
Performance_Date The date of the performance  
Performance_ID Uniquely identifies a performance  
Person_ID Uniquely identifies a person. Value is used for Agent_ID, Member_ID, and Primary_Contact_ID  
Phone_Number The phone number for a person or customer i.e. 479-555-5555
Primary_Contact_ID Primary_Contact_ID is a Person_ID. Identifies the primary contact person for a band.  
Producer_ID Uniquely identifies a video production company  
Producer_Name The name of a video production company  
Production_Cost The total cost to produce an album i.e. 1000.00
Profile_ID Profile_ID is a Customer_ID. Uniquely identifies a customer profile and associates it with a customer  
Promise_Date The date of arrival promised for an order.  
Quantity The number of the particular item ordered on that line  
Release_Date The date an album is released  
Revenue The amount brought in from a performance  
Salary The salary received by an agent  
Sequence The track number the song appears on the album "1-99"
Song_ID Uniquely identifies a song  
Song_Name The name of the song  
Source_Description Description of the source for orders.  
Source_Name Name of a source  
State_Abbr The 2 letter abbreviation for a State  
State_Name The full name of a State  
Street_Address The street address of a person, customer, or venue  
Subtotal_Amount The total amount of an order before tax is added to the order  
Tax_Amount The amount of tax to be applied to the order  
Total_Amount The total amount with tax to be charged to the customer for this order  
Unit_Price The price of the item on that line of the order i.e. 9.00 or 11.50
Venue_ID Uniquely identifies a venue  
Venue_Name The name of the venue  
Video_ID Uniquely identifies a video  
Video_Name The title of the video  
ZIP_Code Uniquely identifies a location in the U.S. in the ZIP_Code table.  
ZIP_Code_Ext The last 4 numbers in an extended 9-digit ZIP code  

Sample

The Hallux database is comprised on 25 tables with over one million rows. It contains sales data from 1990 to 2006 from 5000 customers and 400 bands.

Band_Id Number of Albums Zip Code
1000 11 90001
1001 3 39577
1002 11 00924
1003 1 11694
1004 3 75681
1005 2 17046
1006 5 37211
1007 5 92243
1008 4 11375
1009 11 21532
1010 2 32773
1011 9 40013
1012 7 19139
1013 4 91340
1014 8 27502
1015 6 42001
1016 2 95117
1017 9 01040
1018 10 42223
1019 9 33920
1020 12 97520
1021 4 45014
1022 11 32401
1023 1 29150
1024 5 70510
1025 11 55364
1026 10 29728
1027 3 53202
1028 9 04102
1029 5 98024
1030 11 35957
1031 12 98626
1032 1 23233
1033 12 21742
1034 4 30004
1035 8 31406
1036 5 38128
1037 1 13021
1038 8 50266
1039 8 80526
1040 5 19355
1041 10 62521
1042 7 03102
1043 6 13617
1044 9 60621
1045 3 90006
1046 11 11362
1047 5 90012
1048 9 50554
1049 12 53209
1050 7 11213
1051 7 32207
1052 4 18018
1053 4 30080
1054 7 11377
1055 12 28083
1056 1 12958
1057 3 11718
1058 3 64133
1059 6 90026
1060 10 11220
1061 1 06385
1062 4 82520
1063 12 45653
1064 12 15024
1065 11 99005
1066 6 62703
1067 10 42081
1068 10 60548
1069 3 32068
1070 11 95446