Submit:
1. Your Word document for Parts 1 & 2 with documentation.
2. Your working DB with
saved
sql query views.
Part 1
If you choose to build a
SQLite DB, make certain that you
submit the .db file and NOT a file with another extension.
If you use
Access,
make certain to submit the
.accdb
and NOT the locked DB (in other words, save and close the DB before submitting).
For the selected list of data (playlists_export.csv) included, you must:
1) (15 points) normalize the list into a multiple related tables design
2) (points for submission) build
either
an MS-Access
OR
SQLite3 database
Note: You must use SQL statements to create your tables.
You may use the Insert statement, or import Excel/CSV files, to populate your tables.
3) (10 points) validate data is built correctly, i.e., use both equi-join and inner-join test queries
Name these queries ‘equi-join’ and inner-join’ respectively.
4) (15 points) solve these specific analysis questions
using SQL queries:
· top 3 artists (by playlist tracks; by played tracks);
· bottom 3 playlist tracks (by track duration or length);
· Best Album (by playlist tracks; by played tracks)
5) Document your work in a single Word document with
screenshots and descriptions
6) (15) Document your success by validating the DB works as intended,
i.e. run (3) additional queries. Provide an explanation of the query (what you were attempting to accomplish), the code, and the results. Make certain you SAVE the query in the DB.
7) Do not encrypt or lock the database with username/password.
8) (25 points) Submit working database (including the saved sql queries) AND the Word document
Part 2 Queen Anne DB from Activity 2
U. (5 points) Given your assumptions about cascading deletions in your answer to part B of the Queen Anne case study, write the fewest number of DELETE statements possible to remove all the data in your database, but leave the table structures intact. Do not run these statements if you are using an actual database!
If you decide to run the code, make a copy of the DB and use it.
T.
(15 points) Chapter 2 discussed multivalued dependencies and the associated multivalue, multicolumn problem and how to resolve it (pages 96-98). Does the VENDOR table have the multivalue, multicolumn problem?
If so, use the discussion on pages 114-118 as the basis for solving it for the QACS database.
Create a new table named PHONE_NUMBER, link it to the VENDOR table, populate the PHONE_NUMBER table, and finally alter the VENDOR table to remove any unneeded columns. Hint: Read the additional discussion of the SQL ALTER TABLE statement in online Extension B, “Advanced SQL”.
Do not run these SQL statements if you are using an actual database!
If you decide to run the code, make a copy of the DB and use it.
Write your SQL code here:
PART 1
1. My normalized tables as done in A2:
ALBUM
|
ARTIST
|
SONG
|
PLAYLIST
|
AlbumID(PK, int)
|
ArtistID (PK, int)
|
SongID(PK, int)
|
PlayListID(PK, int)
|
AlbumName (txt)
|
ArtistName (txt)
|
ArtistID(FK, int)
|
PlayListName(txt)
|
Year (int)
|
|
AlbumID(FK, int)
|
SongID (FK, int)
|
ArtistID (FK)
|
|
ID (txt)
|
|
|
|
IDType (int)
|
|
|
|
Title (txt)
|
|
|
|
Track (int)
|
|
|
|
Duration (int)
|
|
|
|
Playcount (int)
|
|
|
|
Rating (txt)
|
|
|
|
Genre (txt)
|
|
|
|
Notes (txt)
|
2. I built an Access db.
3. I validated my data by creating two queries, equi join and inner join.
Although I saved the code in the DB,
the code is:
equi join:
SELECT ALBUM.*, ARTIST.*, SONG.*,PLAYLIST.*
FROM ALBUM, ARTIST, SONG, PLAYLIST
WHERE ALBUM.ArtistID= ARTIST.ArtistID AND
SONG.PlayListID=PLAYLIST.PlayListID AND
ALBUM.AlbumID=SONG.AlbumID
ORDER BY SONG.AlbumID;
inner join:
SELECT ALBUM.*, ARTIST.*, SONG.*,PLAYLIST.*, SONG.SongID
FROM (ARTIST INNER JOIN album ON ARTIST.AlbumID = ALBUM.AlbumID)
INNER JOIN
(SONG INNER JOIN PLAYLIST ON SONG.PlayListID = PLAYLIST. PlayListID)
ON ARTIST.ArtistID = SONG.ArtistID
ORDER BY SONG.SongID;
4) (15 points) solve these specific analysis questions using SQL queries:
• top 3 artists (by playlist tracks; by played tracks);
select
artist, album, count(*)
as
tracksCount
from
tracks
where
artist =
'requested_artist'
group
by
artist, album;
• bottom 3 playlist tracks (by track duration or length);
• Best Album (by playlist tracks; by played tracks)
5) Document your work in a single Word document with screenshots and descriptions
6) (15) Document your success by validating the DB works as intended, i.e. run (3) additional queries. Provide an explanation of the query (what you were attempting to accomplish), the code, and the results. Make certain you SAVE the query in the DB.
a. I Attempted to remove all the data in your database but leave the table structures intact.
DELETE FROM “ALBUM”;
DELETE FROM “ARTIST”;
DELETE FROM “SONG”;
DELETE FROM “PLAYLIST”;
b.
7) Do not encrypt or lock the database with username/password.
8) (25 points) Submit working database (including the saved sql queries) AND the Word document
PART 2
T.
U.
DELETE FROM “CUSTOMER”;
DELETE FROM “EMPLOYEE”;
DELETE FROM “VENDOR”;
DELETE FROM “ITEM”;
DELETE FROM “SALE”;
DELETE FROM “SALE_ITEM”;
|