MS Access Vinyl Database 4 - Creating Relationships Between The Tables

<p>In any database there will be certain relationships between the tables. For instance in our vinyl records database Elvis Presley released many records. Therefore there will be many data records in the <i>t_music</i> table that need to reference the data record for Elvis in the <i>t_artist</i> table. <p>Creating a <i>relationship</i> at database level is optional, but it will do three things: <p><ol><li>It will save us from having to state the relationship in every single <i>query</i> we create</li><li>It will stop us from entering a music track with a reference to an artist who doesn't exist in the <i>t_artist</i> table</li><li>It will stop us from deleting an artist who has music tracks - otherwise that would leave what are called "orphan" records in the <i>t_music</i> table</li></ol> <p><center><a href="http://3.bp.blogspot.com/-fbJgkjlBSAo/VCU1hUoMyPI/AAAAAAAAN9Q/Fu7aFOsytwc/s1600/20140926a.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-fbJgkjlBSAo/VCU1hUoMyPI/AAAAAAAAN9Q/Fu7aFOsytwc/s1600/20140926a.jpg" /></a></center> <p>To create our relationships, open the database and first of all click on <i>Database Tools</i> on the menu bar and then click the <i>Relationships</i> tool as shown. <p><center><a href="http://4.bp.blogspot.com/-MjLqJAQmtZo/VCU2LlzDxuI/AAAAAAAAN9Y/_qsKlpU4TF4/s1600/20140926b.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-MjLqJAQmtZo/VCU2LlzDxuI/AAAAAAAAN9Y/_qsKlpU4TF4/s1600/20140926b.jpg" /></a></center> <p>The above screen window will open. We now need to select and add all of the tables shown. Either add them one at once, or click <i>t_artist</i>, then hold down the [Shift] key and click the bottom table. This will select them all and then clicking the <i>Add</i> button will add them all at once. <p><center><a href="http://3.bp.blogspot.com/-xk_-VenP1I8/VCU2wuteeBI/AAAAAAAAN9g/gMvditg6YP0/s1600/20140926c.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-xk_-VenP1I8/VCU2wuteeBI/AAAAAAAAN9g/gMvditg6YP0/s1600/20140926c.jpg" /></a></center> <p>We now have a screen with all the tables added. They will all be the same height at first which means we will not be able to see all of the fields in the t_music table. To extend the t_music box click on the bottom border and with the mouse button still held down, drag the mouse downwards to expand the box as shown. <p><center><a href="http://4.bp.blogspot.com/-FXnMjq8de_E/VCU3lnm3_PI/AAAAAAAAN9o/lLCd9qOfXdU/s1600/20140926d.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-FXnMjq8de_E/VCU3lnm3_PI/AAAAAAAAN9o/lLCd9qOfXdU/s1600/20140926d.jpg" /></a></center> <p>All of the relationships we are going to create involve the t_music table, so it makes sense to have that in the centre of the screen. You can move the tables about by clicking the title of the table and dragging. <p>First move the t_label table to a spot underneath the t_artist table and then move the t_music table into the space it has left in the middle. <p><center><a href="http://1.bp.blogspot.com/-D-wr70yazCM/VCU5Ggvn91I/AAAAAAAAN90/EH2n6hDCbq0/s1600/20140926e.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-D-wr70yazCM/VCU5Ggvn91I/AAAAAAAAN90/EH2n6hDCbq0/s1600/20140926e.jpg" /></a></center> <p>Now to create our first relationship. We'll start with the t_label table and click and drag from the <i>label_ID</i> field upwards and to the right, releasing the mouse button whilst the mouse is hovering over the <i>music_label_ID</i> field of the t_music table. Make sure you do it in this direction and <b>not</b> by starting from the t_music table. Record labels release many music tracks. We don't want each music track to be able to have many labels... <p><center><a href="http://2.bp.blogspot.com/--v8kgO0HyZM/VCU6HQ-gRFI/AAAAAAAAN98/62J8FQDXyIE/s1600/20140926f.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/--v8kgO0HyZM/VCU6HQ-gRFI/AAAAAAAAN98/62J8FQDXyIE/s1600/20140926f.jpg" /></a></center> <p>The Edit Relationships dialogue box will open at this point. First of all, check that both <i>label_ID</i> and <i>music_label_ID</i> appear in the small table in the middle as shown. If all looks ok, click the <i>Enforce Referential Integrity</i> tick box. This is the factor that will stop you later from deleting a label with tracks existing in the t_music table. Otherwise by deleting the label you could leave the music tracks sort of floating in limbo and wondering which label released them... It will also stop you from adding a music track with a bad or non-existent reference to a label. For example; if you had 21 artists in the t_label table but tried to enter "47" as a label reference in the music_label_ID table. In a later article we'll create a form to enter the data in anyway and that will allow us to pick the label from a list of existing ones - you won't need to remember any artist_ID references and if you even try to enter a bad reference MS Access will tell you that you are being stupid... When you are certain everything looks right, click the <i>Create</i> Button. <p><center><a href="http://2.bp.blogspot.com/-PFXDiXSkTfE/VCVLei-XCnI/AAAAAAAAN-M/kSpK8T72Rks/s1600/20140926g.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-PFXDiXSkTfE/VCVLei-XCnI/AAAAAAAAN-M/kSpK8T72Rks/s1600/20140926g.jpg" /></a></center> <p>Your screen should now look like this. A line appears between the two fields that have the relationship and it should show "1" against the t_label table and the sign for infinity "∞" against the t_music table. <p><center><a href="http://2.bp.blogspot.com/-BT471PqOFOA/VCVMiHUuwxI/AAAAAAAAN-U/5dKpF0q-ZLg/s1600/20140926h.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-BT471PqOFOA/VCVMiHUuwxI/AAAAAAAAN-U/5dKpF0q-ZLg/s1600/20140926h.jpg" /></a></center> <p>In the same way, create relationships between the table t_media and t_music and finally between t_artist and t_music. Remember: <i>always</i> click and drag <i>to</i> the t_music table, not <i>from</i> it. <p><center><a href="http://1.bp.blogspot.com/-6g6XDN5yH68/VCVN3C0WkXI/AAAAAAAAN-c/_xmU-fU6GK4/s1600/20140926i.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-6g6XDN5yH68/VCVN3C0WkXI/AAAAAAAAN-c/_xmU-fU6GK4/s1600/20140926i.jpg" /></a></center> <p>Our relationships are now in place. They should be treasured as should any good relationship!

Diposting oleh Abdullah Syafii