Docstoc

03

Document Sample
03 Powered By Docstoc
					             Object
• Knowing Definition of Database
• Using SQL Creating a database
• Database contains artists and
  songs we like
• Creating a database: easy to use,
  easy to read
                Introduction

•Content: Artist, Songs and related
  information
•Tables: 1. Music (ID No, title, artist)
            2. Country (ID No, State, City)
            3. Type ( ID NO, Genre, Year)
            4. Artist ( Artist, DDB, DOD,
                Hometown, Grp/single)
                  Music Table
CREATE TABLE Music (
     IdNo        NUMBER(5) PRIMARY KEY,
     Title     VARCHAR2(20),
     Artist     VARCHAR2(20),
     CONSTRAINT Music_Artist
     FOREIGN KEY (Artist) REFERENCES Artist (Artist)
 );
INSERT INTO Music (id, Title, Artist)
  VALUES (1, 'Wannabe', 'Spice Girls');
INSERT INTO Music (id, Title, Artist)
  VALUES (2, 'Alright', 'Darius Rucker');
INSERT INTO Music (id, Title, Artist)
  VALUES (3, 'Do not Think I Do not think about it', 'Darius
    Rucker');
              Country Table
CREATE TABLE Country (
    idNo        NUMBER(5) PRIMARY KEY,
    state      VARCHAR2(30),
    city       VARCHAR2(30)
 );
INSERT INTO Country (idNo, state, city)
  VALUES (1, 'England', 'London');
INSERT INTO Country (idNo, state, city)
  VALUES (2, 'South Carolina', 'Charleston');
INSERT INTO Country (idNo, state, city)
  VALUES (3, 'South Carolina', 'Charleston');
INSERT INTO Country (idNo, state, city)
  VALUES (4, 'South Carolina', 'Charleston');
                  Type Table
CREATE TABLE Type (
    IdNo        NUMBER(5) PRIMARY KEY,
    Genre        VARCHAR2(30),
    Year        NUMBER(5)
 );
INSERT INTO Type (idNo, genre, year)
  VALUES (1, 'pop', 1996);
INSERT INTO Type (idNo, genre, year)
  VALUES (2, 'country', 2008);
INSERT INTO Type (idNo, genre, year)
  VALUES (3, 'country', 2008);
INSERT INTO Type (idNo, genre, year)
  VALUES (4, 'country', 2008);
INSERT INTO Type (idNo, genre, year)
  VALUES (5, 'country', 2006);
                     Artist Table
CREATE TABLE Artist (
    Artist      VARCHAR2(30) PRIMARY KEY,
    DOB         DATE,
    DOD          DATE,
    Hometown        VARCHAR2(30),
    Group/Singular VARCHAR2(30),
 );
INSERT INTO Artist (Artist, DOB, DOD, Hometown, Group/Singular)
  VALUES ('Victoria Beckham', '04/17/1974','','Harlow, Essex, England','Group');
INSERT INTO Artist (Artist, DOB, DOD, Hometown, Group/Singular)
  VALUES ('Melanie Brown', '05/29/1975','','Harehills, Leeds, West Yorkshire,
    England','Group');
INSERT INTO Artist (Artist, DOB, DOD, Hometown, Group/Singular)
  VALUES ('Emma Bunton', '01/21/1976','','Finchley,England','Group');
INSERT INTO Artist (Artist, DOB, DOD, Hometown, Group/Singular)
  VALUES ('Melanie Chisholm ', '04/17/1974','','','Group');
                                                               Country                  M               Has
                                                                              Country
                Type
                               Idno
                                                                   City                 Idno
                                                                               State
year                                                                      M                         1
                          1

                                                          In
       genre                     Has                                                    DOD
                                                      M
                                                                               Artist
                                              M
                                                          M
                                                                     Has                            Hometown
                                                                                  1
                                              MUSIC                                     Artist
                 M
                       Title                                                   DOB


               Has                     Idno               Artist
                                                                                         Group/
                                                                                         singular


                                                      M
                                Dependent
 TABLE        Dependent            on

 MUSIC         Title, Artist      IDno

                  Title           Artist

COUNTRY        State, City        IDno

                   City           State

 TYPE         Genre, Year         IDno
          DOB, DOD, Hometown,
ARTIST      Group/Singular        Artist

                 DOD              DOB
             What is Artist name? Jay Chou
 ADD         What is the DOD? 2/14/2010

DELETE
             UPDATE Artist
             SET DOD = 2/14/2010
             WHERE Artist = ‘Jay Chou’;
MODIFY   x
         NORMALIZATION

                     What is Normalization?
  Normalization is the process of efficiently organizing data in a
   database. There are two goals of the normalization process:
eliminating redundant data (for example, storing the same data in
   more than one table) and ensuring data dependencies make
   sense (only storing related data in a table). Both of these are
   worthy goals as they reduce the amount of space a database
        consumes and ensure that data is logically stored.
•   So to unsure the efficiency we really took care to not repeat data.
•   So if you select * with all the different table name you wont find a table
    giving you the same data. This also helps when you combine different
    tables and are looking for some data.
   Example:
Select artist.genre, artist.year, type.genre from artist, type where artist.id =
    type.id
   IDNO GENRE                                 YEAR Genre
    ---------- ------------------------------ --------- ---------
           23 rap                             2008          rap
Bad example:
Select artist.genre, artist.year, type.genre from artist, type where artist.id =
    type.id
   IDNO GENRE                                 YEAR Genre
    ---------- ------------------------------ --------- ---------
           23 rap                             2008 slow/ rnb
            Danger
As we all know the enemy of what you
created is the user. So a user can modify
a database and create more data of the
same name in different tables and thus
messing around with our effort of having
our table normalized.
By using an update function the user can
modify our table and mess up with our
normalization.
                   Conclusion
• SQL allowed us to design and implement a database
  that is usable and informative. Our group learned how to
  use SQL to choose information that we need in order to
  complete a certain task. The project informed us that
  creating a normalized user friendly database wasn’t as
  easy as we thought, especially when working in a group
  environment. We found the hardest part of the project
  was finding the functional dependencies and normalizing
  our E-R diagram. The only enemy of our data base is
  the user because they can modify the data base and its
  efficiency using the update function.
• Completing this project expanded our knowledge of
  database systems and we feel very comfortable using
  and implementing databases.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:11/20/2012
language:English
pages:14