Cooltunes Media BMIS441-G1 Fall 2012

1. Cooltunes MediaBMIS441-G1 Fall 2012 Alex Herreid Austin Carrillo Collin Parker William Greenburg

2. Introduction • CoolTunes Media, Music Distribution Website • Used the Systems Development Life Cycle (SDLC)

3. Planning • Familiar topic: Music • Develop company that focuses on “Indie” music • Develop database that can support a media distribution company and all of its needs

4. Analysis • Select necessary entities • Analyze the Relationships • Select Rules • Analyze Cardinalities

5. Analysis Relevant Entities • Customer • BillingInfo • ShippingInfo • Orders • OrderItems • Products • Artists • Labels

6. Analysis • The CUSTOMER table will hold the basic information for our customers. The attributes will be Customer#, LastName, FirstName, and Email. The Customer# attribute will be the primary key and a unique number for each customer. This is just the baseline data about our customers, the rest of their information will be included in other entities. Customers

7. Analysis • The ORDERS table will include Order#, Customer#, OrderDate, OrderType, and ShipDate. In this entity Order# will be the primary key and customer# will be the foreign key. • Function of OrderType • Keeping track of our customers Orders

8. analysis • The BILLINGINFO table will include Customer#, Address, City, State, Zip, and CardType. The customer# attribute will be a foreign key in the table linking to the CUSTOMERS table. This will also include their billing information in case it is different from their shipping information. BillingInfo

9. Analysis • The SHIPPINGINFO table will include their Customer#, Address, City, State, and Zip Code. This entity is very similar to the BILLINGINFO table in that it is essentially just used to hold their information with the Customer# as a reference. This table allows us not only to have our customers purchase downloadable content but also the physical albums. ShippingInfo

10. Analysis • The ORDERITEMS table will only have one purpose, which is to have a detailed list of what exactly customers have purchased in each of their orders. We will also be able to have a record of what a customer was supposed to receive if there is ever a time were an order is lost or damaged in the shipping process. OrderItems

11. Analysis • The PRODUCTS table keeps track of each product, a product can be a either a song or an album. Item_ID will be the primary key for this entity. The rest of the attributes in this entity will be Artist_ID, title, ItemType, Price, and Track_Qty. Artist_ID is a foreign key from the ARTISTS table that we will discuss next. ItemType will specify whether the product is an individual song or a whole album. Products

12. analysis • The ARTISTS table will include Artist_ID, ArtistName, Genere, and Label_ID. Artist_ID is the primary key for this table and Label_ID is a foreign key. This would just be recording basic information about the artists that will be pulled from when people are search for music from our company. Artists

13. analysis • The LABELS table will mainly be used for internal company purposes, it will include Label_ID, LabelName, ContactName, and ContactEmail. This will list the information for our company contacts at the various labels we are in business with. This will mainly be for communicating any issues with tracks that they have released to us and other administrative issues. Labels

14. Design Explain/illustrate: 1. Functional dependency, 2. Normalization process with 3NF

15. implementation • Three stages • DROP • BUILD • INSERT • Because of the dependencies, we had to enter the tables in the following order: LABELS, ARTISTS, PRODUCTS, CUSTOMERS, BILLINGINFO, SHIPPINGINFO, ORDERS, ORDERITEMS.

16. IMPLEMENTATION DROP TABLE BILLINGINFO CASCADE CONSTRAINTS; DROP TABLE CUSTOMERS CASCADE CONSTRAINTS; DROP TABLE SHIPPINGINFO CASCADE CONSTRAINTS; DROP TABLE ORDERS CASCADE CONSTRAINTS; DROP TABLE ORDERITEMS CASCADE CONSTRAINTS; DROP TABLE PRODUCTS CASCADE CONSTRAINTS; DROP TABLE ARTISTS CASCADE CONSTRAINTS; DROP TABLE LABELS CASCADE CONSTRAINTS; DROP SEQUENCE orders_order#_seq;

17. IMPLEMENTATION CREATE TABLE LABELS (LABEL_ID NUMBER(4), LABELNAME VARCHAR2(40), CONTACTNAME VARCHAR2(25) NOT NULL, CONTACTEMAIL VARCHAR2(40), CONSTRAINT LABELS_LABEL_ID_PK PRIMARY KEY(LABEL_ID));

18. IMPLEMENTATION INSERT INTO LABELS VALUES(8000,'Island Records', 'Jack Oliver', 'jack@islandrecords.com'); INSERT INTO LABELS VALUES(8001,'RCA Music', 'Eva Green', 'E_Green@RCAMusic.com'); INSERT INTO LABELS VALUES(8002,'Sony Music', 'Tony Stark', 'T.Stark@Sonymusic.com'); INSERT INTO LABELS VALUES(8003,'Warner Brothers Group', 'Bradley Johnson', 'Johnson.Bradley@WarnerBrothers.com');

19. IMPLEMENTATION CREATE TABLE ARTISTS (ARTIST_ID NUMBER(4), LABEL_ID NUMBER(4) NOT NULL, ARTISTNAME VARCHAR2(30), GENRE VARCHAR2(15), CONSTRAINT ARTISTS_ARTIST_ID_PK PRIMARY KEY(ARTIST_ID), CONSTRAINT ARTISTS_LABEL_ID_FK FOREIGN KEY (LABEL_ID) REFERENCES LABELS (LABEL_ID));

20. IMPLEMENTATION INSERT INTO ARTISTS VALUES(6000, 8004, 'Pink', 'Pop'); INSERT INTO ARTISTS VALUES(6001, 8004, 'Johan Flozart', 'Classical'); INSERT INTO ARTISTS VALUES(6002, 8005, 'Rihanna', 'Pop'); INSERT INTO ARTISTS VALUES(6003, 8005, 'Avicii', 'Electronic');

21. IMPLEMENTATION CREATE SEQUENCE orders_order#_seq INCREMENT BY 1 START WITH 4000 NOCACHE NOCYCLE; CREATE TABLE ORDERS (ORDER# NUMBER(4), CUSTOMER# NUMBER(4) NOT NULL, ORDERTYPE CHAR(4) NOT NULL, ORDERDATE DATE NOT NULL, SHIPDATE DATE, CONSTRAINT ORDERS_ORDERTYPE_CK CHECK (ORDERTYPE IN ('DIGI', 'PHYS')), CONSTRAINT ORDERS_ORDER#_PK PRIMARY KEY(ORDER#));

22. IMPLEMENTATION CREATE TABLE ORDERITEMS (ORDER# NUMBER(4), ITEM_ID NUMBER(4), CONSTRAINT ORDERITEMS_ORDER#_FK FOREIGN KEY (ORDER#) REFERENCES ORDERS (ORDER#), CONSTRAINT ORDERITEMS_ITEM_ID_FK FOREIGN KEY (ITEM_ID) REFERENCES PRODUCTS (ITEM_ID));

23. IMPLEMENTATION INSERT INTO ORDERS VALUES (orders_order#_seq.NEXTVAL,1000,'DIGI',TO_DATE('10/20/2012','MM/DD/YYYY'),NULL); INSERT INTO ORDERITEMS VALUES (orders_order#_seq.CURRVAL,3015); INSERT INTO ORDERITEMS VALUES (orders_order#_seq.CURRVAL,3018); INSERT INTO ORDERS VALUES (orders_order#_seq.NEXTVAL,1005,'PHYS',TO_DATE('10/15/2012','MM/DD/YYYY'),TO_DATE('10/17/2012','MM/DD/YYYY')); INSERT INTO ORDERITEMS VALUES (orders_order#_seq.CURRVAL,3000); INSERT INTO ORDERITEMS VALUES (orders_order#_seq.CURRVAL,3005);

24. IMPLEMENTATION • Query 1: Display the Artist name, album name and price for albums that are greater than 10$. Please list them in descending order. SQL> SET LINESIZE 75 SQL> SET PAGESIZE 45 SQL> COLUMN Artistname FORMAT A15 HEADING 'Artist Name' SQL> COLUMN title FORMAT A35 HEADING 'Album Title' SQL> COLUMN Price FORMAT $99.99 HEADING 'Price' SQL> SELECT a.ARTISTNAME, p.Title, p.Price 2 FROM ARTISTS a JOIN PRODUCTS p USING (Artist_ID) 3 WHERE Price > 10 4 ORDER by Price DESC;

25. IMPLEMENTATION Artist Name Album Title Price --------------- ----------------------------------- ------- Johan Flozart Shadows $15.99 Johan Flozart Nature $15.99 Johan Flozart Volcano $15.99 Swag Worldwide Swag $15.99 Swag All Up In It $13.99 Swag Me And Your Sister $13.99 Pink This Is Real Life Vol. 2 $13.99 The Eagles Heart On $13.99 Muse Origin Of Symmetry $12.99 Katy Perry Essentially $12.99 Muse Absolution $12.99 23 Rows Selected

26. IMPLEMENTATION • Query 2: Display the average time it takes for the order to ship in hours. SQL> SELECT (AVG(shipdate-orderdate)*24) "Shipping_Delay_Hours" 2 FROM orders 3 WHERE (shipdate-orderdate) IS NOT NULL; Shipping_Delay_Hours -------------------- 81.6

27. IMPLEMENTATION • Query 3: Create a view that only displays digital orders. In this view please include the Order#, Item_ID, ORDERDATE, and price. SQL> CREATE VIEW Digital_Orders 2 AS SELECT Order#, Item_ID, Price, Orderdate, 3 ROUND( 4 Price/ 5 (SELECT SUM(Price) 6 FROM ORDERS JOIN ORDERITEMS USING (ORDER#) 7 JOIN Products USING (ITEM_ID) 8 WHERE ORDERTYPE = 'DIGI'),3) AS "% of Revenue" 9 FROM ORDERS JOIN ORDERITEMS USING (ORDER#) 10 JOIN Products USING (ITEM_ID) 11 WHERE ORDERTYPE = 'DIGI' 12 GROUP by Order#, Item_ID, Orderdate, Price; View created.

28. IMPLEMENTATION • Query 4: Display all the orders that were placed during the summer. Please include the title of the album, orderdate, and the full name of the customer. COLUMN Full_Name FORMAT A15 SQL> COLUMN title FORMAT A25 HEADING 'Album Title' SQL> COLUMN Orderdate FORMAT A15 SQL> SELECT title, orderdate, Firstname ||' '|| Lastname "Full_Name" 2 FROM Customers JOIN Orders USING (Customer#) 3 JOIN Orderitems USING (Order#) 4 JOIN Products USING (ITEM_ID) 5 WHERE orderdate BETWEEN '21-JUN-12'and'21-SEP-12';

29. IMPLEMENTATION Album Title ORDERDATE Full_Name ------------------------- --------------- --------------- This Is Real Life 01-AUG-12 Collin Parker Levels 25-AUG-12 Ronald Ojeda Escape 22-JUL-12 Brandi Salyer Whole Lotta Love 25-AUG-12 Ronald Ojeda Apple Sauce 22-JUL-12 Brandi Salyer

30. IMPLEMENTATION • Query 5: How much revenue has come from Customer# 1006? SQL> COLUMN SUM(Price) FORMAT $99.99 HEADING 'Revenue' SQL> (SELECT SUM(Price) 2 FROM ARTISTS a JOIN PRODUCTS p USING (Artist_ID) 3 JOIN Orderitems USING (Item_ID) 4 JOIN Orders USING (Order#) 5 JOIN Customers USING (Customer#) 6 WHERE Customer# = '1006'); Revenue ------- $37.97

31. Maintenance • Routine Checks • Proactive in maintenance • Efforts to predict future problems

32. Growth and change • May need to create a larger database in the future • May expand into other markets (audiobooks, movies, etc.) • Other unforeseeable concerns

33. Conclusion • How the project went • What we learned • Issues we had