Program 5 Perl Solution

Objective




Work with databases




Assignment




This program will use a database to store a list of songs. The program will allow the user to add, remove, and search for songs from the collection in a variety of different ways.




The required structure of the database is available in the examples link in Moodle. You can use the restore feature in MySQL to create the database and table. You may also create the database and table yourself using the following fields:




Database: fall2018

Table name: songs




Fields:

songid – auto increment integer

artist - varchar(60)




title - varchar(100)

album - varchar(60)

time - varchar(10)




The username and password combination of ‘root’ and ‘password’ must allow read/write access to the database and table. All fields will required information; adding a record with a null value (other than songid) will generate an error from the database.




Specifics




Create a menu that allows the user to select an option. The user must be able to add a new song, update the information for an existing song, print a listing of all songs, and allow for a search by either artist, title, or both.




All menu options should allow for the user to exit without being forced to perform the specific task of the function, meaning if I accidently select “add” from the menu I shouldn’t be forced to add a song, I should be able to gracefully exit back to the menu.




Adding a song: Ask the user for the artist, title, album, and time of the song. Time will a string, with the minutes and seconds separated by a colon (“M:SS” format). You must have “something” for all the fields, this is enforced by the database. Do not confuse no data, such as an empty string, with null. Do not attempt to add a record that has an empty string for artist or title.




Updating a song: You will want to display the songs with their id number, this will allow you to uniquely identify each song. Find out what song they want to update and get the new information. You can ask for all the information again (not the best way), or ideally ask for each piece of information and “remember” the existing info if the user enters nothing. This would allow the user to only have to type the updated text and not retype text that will not be altered. Update the specific record.




Deleting a song: Display the songs with their id number and delete the specified song.




Again, allow for an option to not delete the song.




Searching for songs. There will be two different searches. The first search method will allow the user to search by artist or title. Don’t ask which, display any songs that contains search criteria somewhere in either the title or artist. The second search method (a separate menu item) will ask for the name of an album. List all songs from that album and the total time for those songs. All searches should gracefully handle no matching results.




Requirements




Complete comment section that includes your name, id number, program number and a description of the program.

Use of shbang.




Creating the database - new image




To create the database(s), start the image. Within the image, go to Blackboard and save the fall2018.sql file. Then run the MySQL Workbench program. To find this click on the blue and white icon in the upper left-hand corner of the desktop. MySQL Workbench in the in the Development menu. Click on the gray Local Instance 3306 area in the display. From there, click on File\Run SQL Script… in the menu structure. Select the folder where you saved the sql file, I put it on the Desktop just to make it easy. Select fall2018.sql and click on the Open button. The next window should show the initial lines of the sql file. Click on ‘Run’ and it should create the fall2018 database. You can do the same procedure for namesdbBackup. This creates the same database used in class to demonstrate connecting to a database. To confirm it worked, click on Schemas at the bottom left-hand of the Workbench. You should see the namesdb and/or fall2018 databases. You may need to refresh the area.