Advanced Databases

MULUNGUSHI UNIVERSITY

SCHOOL OF SCIENCE, ENGINEERING AND TECHNOLOGY

NAME                         : KUTUMBA DAVID SIKOTA

PROGRAM                 : BIT III

STUDENT NUMBER:  201302606

COURSE                     : ADVANCED DATABASES

CODE                          : ICT 372

LAB NO                      : THREE (3)

LECTURER                : DR KUNDA

DATE DUE                 : 21ST MARCH, 2016

INTRODUCTION

This lab work was meant to introduce students on how to create a database, tables, inserting data into the tables and finally to query specific data from the tables.

There is a tool called SQL Developer that was used in this work to create a connection that can be used to link tables using their primary keys as foreign keys in other tables so as to have access to those tables. This same tool was also used to create tables and enter data into those created tables.

CREATING THE DATABASE, THE TABLES AND INSERTING DATA INTO THE TABLES CREATED.

TASK 1: CREATING A DATABASE

In this task we are asked to create a database to be named in the format StudentNo_Music. Once we are done creating the database we need to create tables as shown in the lab question. Furthermore we are asked to populate the tables with information as provided for in the question. The figures below illustrate the creation of the fore mentioned tables of the database 201302606_Music.

[pic 1]

[pic 2]

[pic 3]

FIG 1.0: The figure above shows the created table TBL_ALBUM_201302606

[pic 4]

FIG1.1: The above screenshot depicts the table TBL_ARTIST_201302606 after creation

[pic 5]

FIG1.2: Shown in the above figure is the table TBLGENRE_201302606 after creation.

[pic 6]

FIG1.3: The captured screenshot above shows a created table TBLLINKARTIST_ALBUM_201302606.

[pic 7] 

FIG 1.4: The table TBLLABEL_201302606 is shown in the above figure after it was created.

TASK 2: EXPRESSING STATEMENTS IN SQL AND RETRIEVING REQUIRED DATA

1. For all albums with the label “Interscope”, list the name of the artist and the name of the album.

INPUT

[pic 8]

OUTPUT

[pic 9]

C. For each kind of music (i.e. genre) list the type description and the total number within each type.

INPUT

[pic 10]

The query used for this question is shown in the figure above.

OUTPUT

[pic 11]

The figure above shows the result after running the query.

1. Retrieve all artists that have more than 1 album

INPUT

[pic 12]

OUTPUT

[pic 13]

1. Retrieve all artists that belong to the label Virgin TV

INPUT

[pic 14]

OUTPUT

[pic 15]

The output of the query produces the names of the artists who belong to the label Virgin TV.

E. Retrieve all artists that released an album in 2002

INPUT

[pic 16]

OUTPUT

[pic 17]

...