Task 1
1. Create an MS Word document named "Your Name - Your Student Number - Musica.docx"
2. Create an ER Diagram from the above scenario, being sure to:
a) Use Crow's Foot Notation
b) Mark cardinality, existence, primary keys (with an underline), and foreign keys (with
the letters "FK" in brackets).
c) Ensure the entites are in a normalised state
d) Write any assumptions you make if you think it important to clarify the reasons for building particular relationships, creating particular attributes, or leaving something out.
e) Place your name and student number within the drawing
f) Insert that ER Diagram into your Word document. If you use LucidChart or similar you could take a screenshot.
Task 2
For each of the tasks in 2a and 2b create a solution within Microsoft SQL Server and:
• Write a T-SQL comment above each solution, stating your name, the task you are solving, and the purpose of the code. For example, for question c, you could write "Harpreet. Q c. displaying all records from the xxx table" above each of the SELECT statements you create
• Paste all the code that you write into your Microsoft Word document as plain text
• Paste screenshots from Microsoft SQL Server into your MS Word document, showing the code itself and the relevant SQL Server messages and outputs.
Note: For screenshots, keep the images small by using tools like Windows-Shift-S or the MS Word Screenshot tool. Don't clip the entire screen or a whole window.
• Note that you could gather up all of your solutions for Tasks 2A and Task 2b into single batches and run each batch all at once.
• Where nececessary, attach labels to the screenshots in your MS Word document so as to make it clear which screenshot or portion of a screenshot belongs to which solution.
Task 2a
1. Write 2 CREATE statements. One to build a database named "Musica" and one to build a table named "Label" which will store details about Recording Labels. Make all columns in the Label table required columns (so that they cannot be left blank).
2. Use a single INSERT statements to create the details of 3 Labels. Name one of the Labels
"Polydor" and place it in USA.
3. Use a SELECT statement to display full details of all Labels
4. Change the country of the "Polydor" Label to "Australia" and then display the postal details of all Australian labels, showing only the name of the Label
5. Add a 2nd table to your database. It should store details about all releases and should be called "Release". Build a relationship between it and the Label table and make it impossible to enter a release without also entering its Label.
6. Make up the details of 6 releases and then display half of them by displaying only those that have a title starting with a letter in a range of your choosing (e.g. those starting with a letter between M and Z). Show their titles and release dates.
7. Use a DELETE statement to try to delete all of the releases. This should not be possible if you have properly set up referential integrity.
8. Display all Label names, along with all of their releases, grouped by Label.
Task 2b
1. Create all the tables you have drawn up in your ER Diagram. When you do this, rebuild the whole database from scratch. Choose your data types and set relationship properites carefully.
2. Add the following 7 releases to the database and then display the contents of all tables in the database. For each singer, songwriter and label mentioned, make up full records for thise singers, songwriters and labels, so that all of your tables will be filled with data. And note that if you have set up table relationships properly, it will be necessary to enter details into tables on the "one" side before tables on the "many" side.
a) "Shape of You". Written and sung by "Ed Sheeran", 2016. Label is "Planet".
b) "Atlantic City", written by Bruce Springsteen, performed by Ed Sheeran in 2014.
Label is Planet. Song length is 3:57
c) "Atlantic City", written and performed by Bruce Springsteen in 1982. Song Length is 2:58. Label is Columbia
d) "I'm Waiting for The Man". Sung by David Bowie and Lou Reed 1973. Written by Lou Reed. Song length: 4:38. Label: Columbia
e) "Life on Mars". Sung by David Bowie 1971. Written by David Bowie. Song length: 3:52. Label: Columbia
f) The 6 releases you created in section Task 2a
3. Display the names of all artists who have written a song and the title of each song
4. Display all releases in your database, showing only the release title, the singer name, the writer name, and the label for each release
5. Display the names of all artists who have not written a song
6. Display the names of singer-songwriters. That is, all artists who have written at least one song and sung at least one song (whether they sung their own songs or not). Make use of the SQL Alias function
7. Display in minutes and seconds the total length of the songs sung by David Bowie.
Task 3
1. Fringe Dweller is planning on building into her site the ability to collect money from customers for the songs they downloaded. She is concerned about storing personal details such as credit card details and customer addresses. Write 300 words (plus or minus 50 words) naming the main legal issue at play and the database tools or techniques you coud use to ensure customer information remains secure and accurate.
2. Fringe Dweller is curious about why a computer expert like you would draw a diagram before coding. Write 150 words (plus or minus 50) explaining the purpose of creating your ER Diagram before building the database
Attachment:- Case Study.rar