QuestionQuestion

Music-Shop has many CDs and its CD listing in a database (named "song.db") with sqlite3. The database maintains three tables of artist, cd and track of each cd. Each cd has many tracks. Each track has a song (song title) of an artist. The schema and sample listings of these tables are shown below.

create table cd (
id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(70) NOT NULL,    
artist_id INTEGER NOT NULL,
catalogue VARCHAR(30) NOT NULL
);

create table artist (
id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);

create table track (
cd_id INTEGER NOT NULL,
track_id INTEGER NOT NULL,
title VARCHAR(70),
PRIMARY KEY(cd_id, track_id)
);

One day the database was damaged and unrecoverable. Fortunately, someone saved a file of all the records a few days before the crash (as shown below where each field is separated by "|").

sqlite> select * from artist;
1|Pink Floyd
2|Genesis
3|Einaudi
4|Melanie C

sqlite> select * from cd;
1|Dark Side of the Moon|1|B000024D4P
2|Wish You Were Here|1|B000024D4S
3|A Trick of the Tail|2|B000024EXM
4|Selling England By the Pound|2|B000024E9M
5|I Giorni|3|B000071WEV
6|Northern Star|4|B00004YMST

sqlite> select * from track;
1|1|Speak to me
1|2|Breathe
1|3|On the run
1|4|Time
1|5|Great gig in the sky
1|6|Mooney

The shop-manager wants you (IT consultant) to do a project, to create a software system (using C++ with sqlite3 database) to restore the lost database. Your job is to write a C++ program to do the following tasks.

Your C++ program will do (interacting with sqlite3 database, named "song.db" which is initially empty). This is done by "touch song.db" to create an empty file.

Part 1.
(A1) You need to create the files of artist, cd, and track (as shown above).
To read the file and tokenize each line to get each field data.

(A2) Use it (from (1)) to create sql statement(s) to be used to call the database, to create the tables and then to insert each record (one by one, or all at once for each table). For each record, for example, the first record for track 1|1|Speak to me should be a sql insert statement to be used for the program to interact with the database, to insert a record
insert into track(cd_id, track_id, title) values(1, 1, 'Speak to me');

(A3) To get the records from the database tables, and to output (print) each record to a file, after (p2) is done. The output should include a proper heading for each table and formatting for each record to be printed into an output file. You may use select statement to get all the records of a table (for example, "select * from artist;" to get all the records from artist table).

(A4) After all done, someone discovered one typo in the record.
You will write a small quick-fix C++ program, to correct this typo in the record in the database as shown below. With update sql call, the song title should be updated to "Money" (not "Mooney"). After it is corrected, get the record from the database and print it. You may use select statement to get all the records of the track table (for example, "select * from track;" to get all the records from track table) to show the record has been corrected.
1|6|Mooney which should be 1|6|Money. You may need to call sqlite3 to do this update: update track set title = 'money' where cd_id=1 and track_id=6;

(A5) To delete all the records in the tables of the database, to clean up.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

#include "Table.h"

Table::Table() {
    attrs = 0;
    this->size = 0;   
}


Table::~Table() {
    delete [] attrs;
}

void Table::set(string* input, int size, string name, const string* input_name){
    attrs = new string[size];
    attr_name = new string[size];
   
    this->size = size;
    for (int i = 0; i < size; i++) {
       attrs[i] = input[i];
       attr_name[i] = input_name[i];      
    }
    this->name = name;
}

string Table::getInsertString() {
    string s = "insert into " + name + " (";
   
    for (int i = 0; i < size - 1; i++) {
       s += attr_name[i] + ", ";
    }
    s += attr_name[size - 1] + ") values (";
   
    for (int i = 0; i < size - 1; i++) {
       s += "'" + attrs[i] + "', ";
    }
    s += "'" + attrs[size - 1] + "');";   
    return s;
}...
$50.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available C-Family Programming Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats