Cooltunes media bmis441 g1 fall 2012
Download
1 / 33

Cooltunes Media BMIS441-G1 Fall 2012 - PowerPoint PPT Presentation


  • 111 Views
  • Uploaded on

Cooltunes Media BMIS441-G1 Fall 2012. Alex Herreid Austin Carrillo Collin Parker William Greenburg. Introduction. CoolTunes Media, Music Distribution Website Used the Systems Development Life Cycle (SDLC). Planning. Familiar topic: Music Develop company that focuses on “Indie” music

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Cooltunes Media BMIS441-G1 Fall 2012' - santos


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Cooltunes media bmis441 g1 fall 2012

Cooltunes MediaBMIS441-G1 Fall 2012

Alex Herreid

Austin Carrillo

Collin Parker

William Greenburg


Introduction
Introduction

  • CoolTunes Media, Music Distribution Website

  • Used the Systems Development Life Cycle (SDLC)


Planning
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


Analysis
Analysis

  • Select necessary entities

  • Analyze the Relationships

  • Select Rules

  • Analyze Cardinalities


Analysis1
Analysis

Relevant Entities

  • Customer

  • BillingInfo

  • ShippingInfo

  • Orders

  • OrderItems

  • Products

  • Artists

  • Labels


Analysis2
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


Analysis3
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


Analysis4
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


Analysis5
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


Analysis6
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


Analysis7
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


Analysis8
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


Analysis9
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


Design
Design

Explain/illustrate:

1. Functional dependency,

2. Normalization process with 3NF


Implementation
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.


Implementation1
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;


Implementation2
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));


Implementation3
IMPLEMENTATION

INSERT INTO LABELS

VALUES(8000,'Island Records', 'Jack Oliver', '[email protected]');

INSERT INTO LABELS

VALUES(8001,'RCA Music', 'Eva Green', '[email protected]');

INSERT INTO LABELS

VALUES(8002,'Sony Music', 'Tony Stark', '[email protected]');

INSERT INTO LABELS

VALUES(8003,'Warner Brothers Group', 'Bradley Johnson', '[email protected]');


Implementation4
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));


Implementation5
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');


Implementation6
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#));


Implementation7
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));


Implementation8
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);


Implementation9
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;


Implementation10
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


Implementation11
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


Implementation12
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.


Implementation13
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';


Implementation14
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


Implementation15
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


Maintenance
Maintenance

  • Routine Checks

  • Proactive in maintenance

  • Efforts to predict future problems


Growth and change
Growth and change

  • May need to create a larger database in the future

  • May expand into other markets (audiobooks, movies, etc.)

  • Other unforeseeable concerns


Conclusion
Conclusion

  • How the project went

  • What we learned

  • Issues we had


ad