bit275: ice 17 – part 3 walk-through: creating a music ... · enter some data (e.g., revolver,...
TRANSCRIPT
![Page 1: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/1.jpg)
1
BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC Database in SQL Server
01. Start SQL Server, and Login.
![Page 2: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/2.jpg)
2
From the main menu, select Tools > Options > Designers > Table and Database Designers, and uncheck the option Present saving changes that requires table re-creation, then OK button
02. Right-click on Database, select New Database...
![Page 3: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/3.jpg)
3
03. Name the database Music, then select OK button
04. Expand Music database, right-click on Tables, select Table...
![Page 4: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/4.jpg)
4
05. This table will be the "artist" table, so add the ArtistID and ArtistName...
06. Right-click on the ArtistID, then select Set Primary Key
07. Right-click on the Table tab, select Table_1...
![Page 5: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/5.jpg)
5
08. Name the table 'Artist', then press OK button
09. Hit the 'Refresh' icon, to see Artist table show up in Tables
![Page 6: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/6.jpg)
6
10. Click on the ArtistID field, and in the Column Properties window below, select Identity Specification to expand/drop down, and change the (Is Identity) to Yes; this will also change both Identity Increment and Identity Seed to 1. This is how you set up Auto-Increment in SQL Server. Now, Save table, by right-clicking on the Table tab and select Save Artist.
11. Right-click on the Artist table, under Tables, and select Edit Top 200 Rows
![Page 7: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/7.jpg)
7
12. Under ArtistName, enter some artists (e.g., Beatles, Led Zeppelin, Rolling Stones), but do not enter anything under ArtistID, because this will be added automatically through the Auto-Increment logic.
13. To test, right-click on Artist table, and select Select Top 1000 Rows. If everything works correctly, you are finished with the Artist table, for now.
14. Create a Genre table, by repeating the same process used in the Artist table
![Page 8: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/8.jpg)
8
15. Enter some data (e.g., Class Rock, Jazz, Classical, Folk, Electronic, Indy)
16. To test, right-click on Genre table, and select Select Top 1000 Rows. If everything works correctly, you are finished with the Genre table, for now.
![Page 9: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/9.jpg)
9
17. Create a Media table, by repeating the same process used in the Artist and Genre tables
18. Enter some data (e.g., Vinyl, CD, MP3, Flac, Wav)
![Page 10: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/10.jpg)
10
19. To test, right-click on Media table, and select Select Top 1000 Rows. If everything works correctly, you are finished with the Media table, for now.
20. Create an Album table, by repeating the same process used in the Artist, Genre, and Media tables. You will also add columns for the ArtistID, GenreID, and MediaID (for foreign keys)
![Page 11: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/11.jpg)
11
21. Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It Bleed, Sticky Fingers, Exile on Main Street). You will also want to enter the coinciding values for the ArtistID, GenreID, and MediaID)
22. To test, right-click on Album table, and select Select Top 1000 Rows. If everything works correctly, you are finished with the Album table, for now.
23. At this point, you can run a simple query on the table, even without any relationships added:
![Page 12: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/12.jpg)
12
24. Here you may want to think of adding a 'joining table ' to represent a many-to-many relationship between the Artist and Album tables. This means you would have to edit the Album table and delete the ArtistID column there.
25. You might then run a simple query to test:
26. Next you should probably build some relationships. You can do this in Database Diagrams; double-click on Database Diagrams, and say Yes to create. Click on the Database Diagram folder to open diagram and add all tables to the diagram, then close the table box.
![Page 13: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/13.jpg)
13
27. Rearrange the tables in the diagram by dragging-dropping them.
28. Right-click on the Artist table and select Relationships
29. Click and hold down cursor on on Artist.ArtistID and drag to ArtistAlbum.ArtistID, then select OK on both pop-up windows
30. Repeat with Album.AlbumID and ArtistAlbum.AlbumID
31. Repeat for Genre.GenreID to Album.GenreID
32. Repeat for Media.MediaID to Album.MediaID
33. Right-click on Diagram tab and save as Music
34. On "The following tables will be saved to your database" pop-up, select Yes.
35. To export/save your database to an .sql file, right-click on the Music database, select Tasks, then Generate Scripts...
![Page 14: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/14.jpg)
14
36. On the Introduction page, select Next
37. On Choose Objects, make sure that Script entire database and all database objects is selected, then select Next
![Page 15: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/15.jpg)
15
38. On the Set Script Options page, select Advanced button
39. Scroll down to Types of data to script and select Schema and data, then select OK button
![Page 16: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/16.jpg)
16
40. Back on the Set Scripting Option page, under File name: choose where you want to save your .sql file, give the file a name (e.g., music.sql), then Save, then select Next button
41. On Summary page, select Next
![Page 17: BIT275: ICE 17 – PART 3 Walk-Through: Creating a MUSIC ... · Enter some data (e.g., Revolver, Rubber Soul, Sgt. Pepper, Led Zeppelin I, Led Zeppelin II, Led Zeppelin III, Let It](https://reader034.vdocuments.us/reader034/viewer/2022042021/5e7844421085fd0baa0cec21/html5/thumbnails/17.jpg)
17
42. On the Save or Publish Scripts page, select Finish button.
43. Go to where you saved your Music.sql file and open with your favorite text editor (e.g., Notepad++) to view. This is how you will submit your SQL Server database for Assignment 3.