Docstoc

U10A1

Document Sample
U10A1 Powered By Docstoc
					ABC Music DBMS

1

ABC Music DBMS Jeannine Kennison TS3130 - Section 1 December 4, 2006 Dr. William Burkett

ABC Music DBMS Table of Contents: Vision and Purpose……………………………………….…3 Conceptual Design……………………………………….….4 Fig 1.1…………………………………………….…4 Fig 1.2………………………………………….……5 Fig 1.3………………………………………….……5 Fig 1.4………………………………………….……6 Logical Design………………………………………………7 Implementation and Testing Components………..…………9 Fig 1.5……………………………………………….9 References……………………………………………….….10

2

ABC Music DBMS Vision and Purpose:

3

My database has been designed for ABC Music so that they can store information about CDs, including number of tracks, artist, and price. This database will also be able to track daily sales, monthly sales, and year-to-date sales for comparison purposes so that they can see what sells and what does not sell over a given time. They will know if they should raise or lower a CD’s cost based on sales. They can project prices based on an artist’s track record with sales. There will be four tables, CD, TRACK, ARTIST, and SALES. It is my vision to create this database in a simple and concise manner. I wish to create a database that does not contain redundant data and is as efficient as possible, hence the importance of simplicity.

ABC Music DBMS Conceptual Design: This is a conceptual design for a database for ABC Music. This database will keep a record of all the CDs, the title, artist, price, number of tracks, the length and title of each track and its number on the CD. For inventory purposes, the quantity in stock will be in the database as well as the daily sales, month-to-date and year-to-date sales of a CD.

4

The CD_NUM has a UNIQUE constraint so that each CD is assigned a unique number for inventory and identification purposes. This may not be null and must be ten characters long. CD_QTY, SALES_DAILY, SALES_MTD, and SALES_YTD will be derived values based on sales transaction input. The ERD below explains and illustrates the entities and their attributes. The ARTIST table contains certain information about the CD, such as the name of the artist, the CD title and the CD ID number. The CD table contains information about the CD such as the unique ID number, the price of the CD, the quantity of the CDs in stock, the title of the CD, and the number of tracks on a given CD. The TRACK table contains the track title, the CD title, the number assigned to the track on a given CD, and the length of a track on a given CD. The SALES table contains the order_num, or invoice number, the CD ID number, the daily sales of that CD, the month to date sales of the CD, and the year to date sales of that CD. Fig. 1.1
CD A R T IS T PK PK C D _ A R T IS T C D _ T IT L E CD_NUM FK1 PK C D _N U M C D _ P R IC E C D _Q TY C D _ T IT L E C D _TR AC KS

SALES T ra c k PK P K ,F K 1 T R A C K _ T IT L E C D _ T IT L E TRACK_NUM TR AC K_LEN G TH PK FK1 O RDER _NUM CD _NUM S A L E S _ D A IL Y SALES_M TD SALES_YTD

ABC Music DBMS

5

The ERD below explains the entities and relationships. The relationships are orange diamonds and the entities are green rectangles. One artist records one or many tracks, one artist has one or many CDs, and one CD earns zero or many sales. Fig. 1.2
1 A R T IS T 1 HAS 1 M CD

RECORDS

EARNS

M

M

TRACKS

SALES

The diagram below illustrates the Data Normalization. Fig 1.3

C D _ A R T IS T

C D _ T IT L E

C D _N U M

C D _N U M

C D _ P R IC E

C D _Q TY

C D _ T IT L E

C D _A R TIS T

C D _TR A C K S

O R D ER _N U M

C D _N U M

S A LE S _D A ILY

S A LE S _M TD

S A LE S _Y TD

T R A C K _ T IT L E

C D _ T IT L E

T R A C K _LE N G T H

TR A C K _N U M

ABC Music DBMS

6

The table below illustrates the Data Dictionary, which explains the content of the database and its requirements. Fig 1.4
Table Attribute Contents
Type
Format

Req

PK or FK

FK Table Ref.

ARTIST

CD_ARTIST CD_TITLE CD_NUM

CD Artist CD Title CD ID Number CD ID Number CD Price Stock quantity CD Title Number of tracks on the CD

VARCHAR(25) VARCHAR(35) CHAR(10) CHAR(10) NUMBER(3,2) VARCHAR(5) VARCHAR(35) CHAR(2)

Xxxxxxx Xxxxxxx XXXXXXXXXX XXXXXXXXXX ###.## XXXXX Xxxxxxxx XX

Y Y Y Y Y Y Y Y

PK PK

CD

CD_NUM CD_PRICE CD_QTY CD_TITLE CD_TRACKS

PK

FK

ARTIST

SALES

ORDER_NUM CD_NUM SALES_DAILY

Invoice Number CD ID Number Daily sales based on lookup date Month to date Year to date Song title The track’s number on the CD Length of the track CD title

CHAR(7) CHAR(10) NUMBER(9,2)

XXXXXXX XXXXXXXXXX ####.##

Y Y Y

PK FK
CD

SALES_MTD SALES_YTD
TRACKS

NUMBER(9,2) NUMBER(9,2)

####.## ####.## Xxxxxxxx XX

Y Y Y Y PK

TRACK_TITLE TRACK_NUM

VARCHAR(25)

CHAR(2) VARCHAR(6) VARCHAR(35)

TRACK_LENGTH CD_TITLE

Xxx:xx Xxxxxxxx

Y Y

PK, FK

ARTIST

ABC Music DBMS Logical Design: Below is the logical design and SQL code of the tables. The following four groups of SQL create the tables TRACK, ARTIST, CD, and SALES.
CREATE TABLE TRACK ( TRACK_TITLE VARCHAR(25) NOT NULL, CD_TITLE VARCHAR(35) NOT NULL, TRACK_NUM CHAR(2) NOT NULL, TRACK_LENGTH VARCHAR(6) NOT NULL, PRIMARY KEY (TRACK_TITLE, CD_TITLE) ); CREATE TABLE ARTIST ( CD_ARTIST VARCHAR(25) NOT NULL, CD_TITLE VARCHAR(35) NOT NULL, CD_NUM CHAR(10) NOT NULL UNIQUE, PRIMARY KEY (CD_ARTIST, CD_TITLE) ); CREATE TABLE CD ( CD_NUM CHAR(10) NOT NULL UNIQUE, CD_PRICE NUMBER(3,2) NOT NULL, CD_QTY VARCHAR(5) NOT NULL, CD_TITLE VARCHAR(35) NOT NULL, CD_TRACKS CHAR(2) NOT NULL, PRIMARY KEY (CD_NUM) ); CREATE TABLE SALES ( ORDER_NUM CHAR(7) NOT NULL, CD_NUM CHAR(10) NOT NULL, SALES_DAILY NUMBER(9,2), SALES_MTD NUMBER(9,2), SALES_YTD NUMBER(9,2), PRIMARY KEY (ORDER_NUM) );

7

The statements below put data into the tables that were created:
INSERT INTO TRACK (TRACK_TITLE, CD_TITLE, TRACK_NUM, TRACK_LENGTH) VALUES (‘My Little Shirtwaist Fire’, ‘Thanks for the Ether’, 01, ‘2:33’); INSERT INTO TRACK (TRACK_TITLE, CD_TITLE, TRACK_NUM, TRACK_LENGTH) VALUES (‘Stumpside’, ‘Thanks for the Ether’, 02, ‘4:21’); INSERT INTO TRACK (TRACK_TITLE, CD_TITLE, TRACK_NUM, TRACK_LENGTH) VALUES (‘Babooshka’, ‘Never for Ever’, 01, ‘3:19’);

ABC Music DBMS
INSERT INTO TRACK (TRACK_TITLE, CD_TITLE, TRACK_NUM, TRACK_LENGTH) VALUES (‘Delius (Song of Summer)’, ‘Never for Ever’, 02, ‘4:17’); INSERT INTO ARTIST (CD_ARTIST, CD_TITLE, CD_NUM) VALUES (‘Kate Bush’, Never for Ever, 5371111798); INSERT INTO ARTIST (CD_ARTIST, CD_TITLE, CD_NUM) VALUES (‘Rasputina’, ‘Thanks for the Ether’, 1002232110); INSERT INTO CD (CD_NUM, CD_PRICE, CD_QTY, CD_TITLE, CD_TRACKS) VALUES (5371111798, 7.98, 4, ‘Never for Ever’, 11); INSERT INTO CD (CD_NUM, CD_PRICE, CD_QTY, CD_TITLE, CD_TRACKS) VALUES (1002232110, 10.98, 6, ‘Thanks for the Ether’, 11); INSERT INTO SALES (ORDER_NUM, CD_NUM, SALES_DAILY, SALES_MTD, SALES_YTD) VALUES (0000001, 1002232110, 0, 109.8, 549.00); INSERT INTO SALES (ORDER_NUM, CD_NUM, SALES_DAILY, SALES_MTD, SALES_YTD) VALUES (0000002, 537111798, 7.98, 31.92, 319.20); INSERT INTO SALES (ORDER_NUM, CD_NUM, SALES_DAILY, SALES_MTD, SALES_YTD) VALUES (0000003, 1002232110, 32.94, 142.74, 581.94); INSERT INTO SALES (ORDER_NUM, CD_NUM, SALES_DAILY, SALES_MTD, SALES_YTD) VALUES (0000004, 537111798, 0, 31.92, 319.20);

8

ABC Music DBMS Implementation and Testing Components: Below are statements in SQL that query the database created and what the results are for those queries: Fig 1.5 Statements
select cd_num from sales where sales_mtd > 200;

9

Result This brings back the CD ID number that has sold more than $200.00 month-to-date.

select cd_title from cd where cd_price <7;

This returns all CD titles whose price is less than $7.00.

select cd_artist, cd_title from artist where cd_num LIKE ‘%0’;

This returns a list of CDs and its artist where the CD ID number ends in zero.

select artist.cd_artist, artist.cd_title, sales.sales_daily, sales.sales_mtd, sales.sales_ytd from artist, sales where artist.cd_num = sales.cd_num;

This returns a list of the name and CD name and the daily, monthly, and yearly sales from two different tables. This string made me realize that my cd_num in one table was incorrect and didn’t match the other table.

select artist.cd_artist, artist.cd_title, cd.cd_price from artist, cd where artist.cd=cd.cd_num;

This gave me a list of CDs, their artist, and the price from two different tables.

ABC Music DBMS 11 References A Gentle Introduction to SQL. (nd). Retrieved from http://sqlzoo.net/ on November 4, 2006. Frequently Asked Questions. 2006. Retrieved from http://www.sqlite.org/faq.html November 21, 2006. Table Joins, a Must. 2006. Retrieved from http://www.sqlcourse2.com/ November 21, 2006.


				
DOCUMENT INFO
Description: College document, essay, form, or assignment from an Information Technology class.