LEARNING INTENTION: Develop an understanding of:

• What a Database is
• How it is different from a spreadsheet
• How data is different from information

A database is the name given to a system that has a collection of organized data. At its most basic, a database can be very similar to a spreadsheet by containing a single table of data that can be used to calculate, chart and predict. When carefully designed, however, databases offer many advantages that are difficult to achieve in a spreadsheet, such as relational links, unnecessary duplication of data, and multi-user write-access. You will learn more about these as you work through the unit.

It’s important to understand the differences between data and information:

• Data: The raw, unorganised facts that need to be processed. Data can be something simple and appear to be random and useless until it is organised.
  • 75, 92, 34, 12, 72, 100, 65
• Information: When data is processed, organised, structured or presented in a given context so as to make it useful, it is called information.
  • Fail: 12, 34
  • Pass: 65, 72, 75, 92, 100
  • %
  • Maths Test

To do:

1. Create a new folder within your Digital Tech folder on your OneDrive.
   
   
2. Open up Microsoft Access. On the first page that appears, select “Blank desktop database”.
   
   
   
3. Change the File Name to “Netflix Subscribers”, select the folder icon to navigate to the folder you made in Step 1, then select [Create].
   
   
   
4. Congratulations, you’ve made your first database! Now, before you begin to run around the room high-fiving everyone, you’ll need to begin lesson 2, develop your database structure and input some data. You can high-five everyone after you’ve completed it! 

SUCCESS CRITERIA: 

• You've setup your first database and it has been saved on a cloud drive (OneDrive)
• Understand which of the following is data, and which is information:
  • 28
  • 13% fuel remaining
  • 47cm
  • 2.3, 4.7, 5.6

LEARNING INTENTIONS: Extend your understanding of database development. Learn about Data Types and AutoNumbers.

After completing Lesson 1, you should have a screen that looks similar to what is shown below:

By default, your first tables have been made for you: Table1.

1. Select the “View” button, and select the “Design View” mode. This will force you to name your table. Give it a new name “Subscribers”.
   
   
   
2. In the new screen, you will already have the top “ID” field created by default. We’ll leave that for now, and fill out some additional fields in table as shown below:
   
   UPDATE: Change the Phone Data Type to from "Number" to "Large Number". This is a new datatype that didn't exist when this tutorial was first created.
   
   The Data Type field restricts the type of data that is allowed to be entered into it. If you want a field to only ever contain numbers, such as the “Phone” field, then someone cannot accidentally type text into it without the program telling them that an error has occurred.
   
   
   
3. Change your view back to the Datasheet (you will need to save your table first)
   
   
   
4. Enter in 5 sample subscribers in the datasheet view of your table. Note: you do not have to enter in anything in the ID field – this is generated automatically because it’s Data Type = AutoNumber (every time a new entry is created, the AutoNumber creates a new number that is unique / not replicated anywhere else in the table).
   
   
5. Save your database. Congratulations, you’ve now officially created a simple database that has some data organised in it. We’ll continue to improve this in the next lesson.
   
   
6. High five everyone in the room because you’re such a legend!

Note: To re-open a table after saving and closing your database, you just need to double-click it in the menu on the left-side of the screen.

SUCCESS CRITERIA:

• Subscribers table with 6 fields, descriptions and suitable data types.
• 5 records entered in the datasheet view
• You are able to answer the following questions:
  • What is the difference between the Design and Datasheet views?
  • Why are data types useful for our database?
  • Why is having an AutoNumber particularly useful for this table?

LEARNING INTENTIONS: Understand the importance of a Primary Key.

In the last lesson we completed the first table of our database and learnt about data types. Now we’ll create an extra table that will store the different countries that will be used in each of the Subscriber records.

1. In the “Create” section, select “Table”
   
   
   
2. Change the view to Design View (just like we did in Lesson 2, Step 1), save it with the name “Countries”.
   
   
3. We won’t keep the default ID field like we did with the Subscriber table. We’ll create a new field that will be used to store unique values for each country in our list – this is called a Primary Key. It’s essential to have a primary key that can be used to uniquely identify each record within a table – we’ll use the country code to do this.
   
   a. With the ID field selected, click on the “Delete Rows” button. Select “Yes” in the confirmation box that appears next.
   
   
   b. Create the following fields:
   
   
   Note: ISO Alpha 2 Links to an external site. are internationally recognized codes that designate every country. Each is unique, and the two-letter suffixes are commonly used in top level domains Links to an external site. (the part of a URL that shows the country in the letters immediately following the final full stop in an Internet address).
   
   
   c. Because the ISO Alpha 2 is unique for each country, we can use this for our new Primary Key. Select the “ISO Alpha 2” field, and then click on the “Primary Key” button.
   
   
   d. You should now be able to see the Primary Key symbol next to the ISO Alpha 2 field. This is confirmation that only unique values can be placed in the field when you are entering in records.
   
4. Change your view back to the Datasheet View (you will need to save your Countries table first).
   
   
5. Using this resource Links to an external site., enter in 10 countries. You will also need to research the continent and current populations of each of the countries. Try to use the same ISO Alpha 2 code for more than one country. Does it work?
   

SUCCESS CRITERIA: 

• Create a Countries table with 10 records.
• Why do we need a Primary Key?
  • What does it stop us from doing in our database?
  • What types of Primary Keys are used to identify us on some systems that we use in our lives?
  • What is the shortest method of identifying different countries throughout the world?
• Population has been set to a Data Type = Number. Why?

In the last lesson we completed the second table of our database, and learnt about Primary Keys. Now we’ll use the first of two methods to link our database tables together.

1. Open the “Subscribers” table by either double-clicking it on the left-hand side, or selecting it from the tabs (but this is only an option if the table has recently been opened).
   
   
2. If you are not already on it, switch to the Design View.
   
   
3. In the “Country” field that was added previously, change the Data Type from “Short Text” to “Lookup Wizard”.
   
   
   
4. In the Lookup Wizard window that appears, select the first option, as we’ll be telling the database to grab the country names from our Countries table. Click on the Next button.
   
   
5. With “Table: Countries” selected, click the Next button.
   
   
6. The next window asks you to select which fields you want included in the lookup wizard field. We could either select ISO Alpha 2 or Country, or both. For this example, we’ll use Country, as we want the user of this database to easily identify each country without needing to know the ISO codes. Select “Country”, then click on the “>” symbol. Click the Next button.
   
   
   
7. Sort by “Country” + Ascending in the next window. This will make the resulting selection in alphabetical order.
   
   
   
8. You will now be presented with the opportunity to adjust the column width to fit everything in so that it is readable (long country names require a larger column to be readable). If you turn the “Hide Column Key” on and off by clicking on the checkbox, you’ll see that the ISO Alpha 2 Field appears. This is because it is the Primary Key, and is always associated with each entry in your table. We’ll keep it hidden for now. Follow the other instructions listed, and then click the Next button.
   
   
9. In the final window that appears, change “Country” to “Lookup Country”, and then click the Finish button.
   
   
   
10. Click “Yes” in the window that appears, as we need to save our table before any relationships between tables can be created.
    
    
11. You’ll now be looking back at the Design View of your table. It may not look like anything has changed since you selected the Lookup Wizard in step 3, but if you click on the Lookup tab in the Field Properties you can see a summary of all of the steps that you just completed.
    
    
12. Change back to the Datasheet View, and take a look at your entries that you previously made. Notice anything different? All of the countries that were previously entered for each record have now disappeared. This is something that you have to be careful of when altering the design of a table once data has been entered, and why it is so important to carefully design a database before populating it with data in order to minimise the risks of data loss. What would Netflix do if it lost all of the customer information?!!!
    
    
13. Click in the “Lookup Country” field for each of the subscribers, and allocate them a country from the list that appears. Note: If you can’t see any of the extra countries that you have created in the drop-down list, close your Subscribers table, and re-open it. Doing this forces the lookup wizard to run again, which will re-populate your list of countries correctly.

14. Now try adding in 2 more countries into the country table. Once you have done that, check that you can now select from those countries when you modify or add another subscriber into the system (remember that you’ll need to re-open your subscriber table for this to work correctly).

Lesson 5: Extra Tables

In the last lesson we linked 2 database tables together. We will now use these same skills to create a Movies table and a Viewing History table.

1. Create a new Movies table, with the following fields and data types:

*Rating: After selecting Lookup Wizard as the data type, instead of linking to a separate table like we did last time with the Countries, this time select “I will type in the values that I want”. Type in the following values, and then select “Next”.

On the next screen, select the “Limit To List” option, so that no incorrect ratings can be accidentally entered, and then select “Finish”. Limit To List helps to minimise human error when movies are entered into the database.

Note: After selecting “Finish”, it will still have Short Text as the data type. By clicking on the Lookup tab at the bottom of the screen, you’ll be able to see the values that you entered.

2. Create 5 entries in your Movie table, being careful that the information you enter is correct (research!!!)
   
   
3. Create a new Viewing History table, with the following fields and data types:

After entering the field names and data types, with the Date field selected (still in the Design View), look at the Field Properties at the bottom of the screen. There is a field called Default Value – type in =Date() next to it. This will then automatically put the current date in that field, without it needing to be typed in.

NOTE: Make sure you are typing 2 brackets ( and ). It looks a bit like a zero or letter O in the image below, but it won't work unless you type ().

Don’t worry about entering anything into this table yet. What you have made should look like this in the Datasheet View (but the date will be different).

You now have all of the tables required for our simplified version of the Netflix Database, well done!

We have now created a database with 4 tables:

• Subscribers
• Countries
• Movies
• Viewing History

At this stage, only 2 of the tables communicate with each other: Subscribers and Countries. This lesson will focus on linking all 4 of the tables together, which is referred to as a Relational Database.

A Relational Database? What the…?

This is just a fancy term that is used to describe a database that has been designed to recognise relationships between different sections of data that are stored inside it. It will make more sense (hopefully) by the time that you have finished this lesson.

1. Close any tables that you currently have open (by clicking on the x for each)
   
   
   
2. Open the Relationships tab: Database Tools  Relationships
   
   
   
3. You’ll then see the following screen. This shows a link between Countries: ISO Alpha 2 and Subscribers: Lookup Country. This should make sense, as we have previously told Access to create a link for communication between these fields. Every time a new entry is made in the Countries table, a new country will be available to be selected in the Subscribers table.
   
   
4. We’ll now add in the other 2 tables, and create some additional links.
   
   
   a. Select the “Show Table” option.
   
   b. Double-click Movies and Viewing History, then select Close. Your screen should now look like this:
   
   c. Click and drag the table headings to reposition them to be in the same location as shown below (ignore the extra connecting lines at this stage), and then click + drag Subscribers: ID onto Viewing History: Subscriber ID. On the window that appears, select “Enforce Referential Integrity”, and click “Create”. Do the same as above, but this time drag Movies: Movie ID onto Viewing History: Movie ID. Your Relationship view should now look like this:
   
   
   
   If your relationship view doesn’t look the same, ask for assistance now!!!
   
   By adding in these relationships and enforcing referential integrity, we are telling the database software to make sure that any entry made into the Viewing History table has corresponding entries in the Subscribers and Movies tables (i.e. a subscriber can’t watch a movie that doesn’t exist in the database, and a movie can’t be watched by an unregistered user).
   
   
5. Now it’s time to look back at your Subscribers and Movies tables. I’m showing you a sample of mine below:
   
   
   
   
   
   
6. Open up your Viewing History table, and enter in a record for your 3^rd subscriber and your 2^nd movie. For this to work, you will have to enter in Subscribers: ID and Movies: Movie ID, as those are the fields that we linked up in the relationships view. In my examples above, my Subscriber: ID = 3, and my Movie: Movie ID = 2, but yours might be different.
   
   
7. Now create another record in your Viewing History table using a Subscriber ID that doesn’t exist in the Subscriber table. Does it work? Nope! Because we have already told the database to enforce referential integrity back in step 3c, which means that it won’t allow an entry for something that doesn’t exist in the other 2 linked tables.
   
   
   
   Note: You might have noticed that my View ID is showing 3 and 4 for the records that I’ve entered. This is because I had previously entered in 2 other records, and then deleted them. The AutoNumber data type doesn’t reset when a record is deleted, it just keeps on going higher, and higher, and higher…
   
   
8. Click OK on the dialog box that appeared, and change your IDs to those that exist elsewhere in your database tables. It won’t let you save, or enter any new records, until this error is resolved. This is a database method of ensuring that incorrect records are not ignored and saved accidentally.
   
   
9. To delete a record: Click in the dark grey section next to the record you wish to remove, and then select the “Delete” button.

Remember back to when a definition of Relational Databases was given to you? Go back and read it now – does it make more sense?

1. Go to bubbl.us Links to an external site., and either create an account, or login with a Google account.
   
   
2. Following the instructions shown in class, model the structure of your Netflix database. You should end up with something that looks like what is shown below, but with more detail.
   
   
   
   
3. Note that all of the Primary Keys in the table shown above have been made bold. Do the same to your version once you have entered in all of the attributes.
   
   
4. We have left the Viewing History out as a separate object at this stage. This is because it doesn't represent an entity, but it stores the records of relationship between the Subscriber and the Movie tables.  Add in the attributes of the Viewing History table.
   
   
5. Create links between Subscriber - Viewing History and Viewing History - Movie as shown below. Also link the attributes that are used for the relationships. Each of these relationships should contain a Primary Key. Why?
   
   
6. What relationship is missing?
   
   
7. As a class, develop an Entity-Relationship model for YouTube. You need to think about all of the information that would need to be stored in the entire database system.
   • What are the Entities?
   • Attributes?
   • Primary keys?
   • Where do we store the viewing data?
   • Where are playlists stored?
   • Does the system allow users to like / dislike a video?
   • Is there any unnecessary duplication of data in the system?
   • Advanced: How would advertising work in this system?