lab-2.md

+# Lab 2: Relational Data Modelling
+
+This lab is not directly assessed. However, you need to complete it in order to progress to Lab 3, which *is* assessed. Of course, you will always benefit from doing the lab exercises, regardless of whether they are assessed, as they will help you to develop your understanding of the course material.
+
+You will need to work in small groups for this lab. These groups have been set up on the VLE. Please inform the tutor if anyone is missing from your group as it may be necessary to rejig some groups on the day.
+
+Suggested timings are indicated by each task. Please refer to these, and try to stick to them. It *should* be possible for each group to complete the lab within 2 hours.
+
+---
+
+## Learning Objectives
+
++ Describe how objects and their properties are represented in a relational database
++ Depict a relational database schema in an Entity Relationship (ER) diagram
++ Identify and select appropriate field types for storing data in a MySQL database
+
+---
+
+## Context
+
+In lab 1 you were working on a \`Memory Bank' dynamic web application.
+
+The app was dynamic in the sense that the data it presented (the memories and names of the contributors) were loaded dynamically from a .js file. You might also have considered the implications of storing the data in this way.
+
+Let us suppose we wanted to develop the app functionality further...
+
++ We want users of the app to be able to search for memories containing specific words.
++ We'd like the most recent memories to appear highest in the list by default, but for the user to be able to change the sort criteria.
++ We'd like a user to be able to click on a contributor's name and see only their contributed memories+ ...and so on.
+
+How readily does the current data storage solution facilitate these basic **functional requirements**? Let's assume we now have a very large bank of memories...
+
+A **Database Management System (DBMS)** can support the storage and retrieval of large amounts of data, including the retrieval of filtered datasets (i.e. **subsets** of the data that match certain criteria). The browser then isn't required to load all of the data from a file, which could be very time-consuming, and difficult to parse as a single JavaScript object.
+
+A DBMS is a piece of software that can be installed on either a personal computer, or on a web server. There are various different types of DBMS. They are different in the sense that they may model and store data in different ways, and they may provide different ways for a user to interact with the data inside them. The type we will consider first is called **MySQL**, and it uses the **Relational Database Model**.
+
+---
+
+## Task 1: Agree on an application concept (10 mins)
+
+Before doing anything else, your group needs a simple idea for a data-driven web application. In Lecture 2, Sorrel suggested devising an application that could help you with your studies, but it could be something else altogether. 
+
+The whole class will vote on the application they would most like to see developed in the Lectures/Lab example code.
+
+If you are unable to agree an idea within 10-15 minutes, you will be advised to complete tasks 2-7 for the \`MemoryBank' application.
+
++ **Brainstorm application concepts and then pick one!**
+
+---
+
+## Task 2: (10 mins)
+
++ **Define 3 basic functions the app will perform.**
+
+You can refer to the examples that were suggested, or make up your own. Make them very specific, i.e. \`Display a list of memories from the database, from most recent to oldest. The contributor's name will appear next to the memory text'...and so on.
+
+You may find sketching a simple wireframe helps with this part of the task.
+
+---
+
+## Task 3: Object Identification (20 mins)
+
+In a relational database **schema**, every type of object or **entity** is represented by its own distinct **table**.
+Every **record** (row) in the table will hold details about a specific instance of that type of thing.
+
+What are the objects or entities that need to be represented in your database? For example, if the Twitter app were to use a relational database, a `Tweet' might be considered an entity (type of thing), and have its own table. Each row within the Tweet table would then represent a specific Tweet.
+
++ **List the tables required for your application to meet the basic requirements identified in Task 2.**
+
+---
+
+## Task 4: Object Properties (20 mins) 
+
+Each column or **field** in the tables will represent a property of that class of object. For example, the properties of a Tweet might include 'user_account', 'timestamp' and 'tweet_text'.
+
+What information about an object do you think would be needed for your application to meet its functional requirements?
+
++ **List the fields you agree should be associated with each entity in your schema.** 
+
+---
+
+## Task 5: Field Types (20 mins)
+
+When designing a relational database schema, we should also consider the **type** of data that each column or field in the database will contain.
+
+We will be using MySQL DBMS (version 5.5). Here are some of its commonly used field types:
+
++ INT (whole numbers)
++ DECIMAL (decimal numbers)
++ VARCHAR(L) (variable length string, where L is the maximum length)
++ CHAR(L) (fixed length string, where L is the length)
++ TEXT (for long strings of text)
++ BLOB (binary object, for binary encoded data such as images)
++ TIMESTAMP (for date and time values stored in UTC)
+
+You can refer to the [MySQL v5.5 Documentation](https://dev.mysql.com/doc/refman/5.5/en/data-types.html) for other field types. 
+
++ **Beside each field on your list, specify which field type you would choose to represent its data.**
+
+---
+
+## Task 6: Depict your schema in an ER diagram (20 mins)
+
+A standard format for depicting relational database schemas is with an Entity Relationship (ER) diagram. [Some examples](https://learn.gold.ac.uk/mod/page/view.php?id=490003) have been shared with you on the VLE (it's a good idea to have a look!)
+
+Although we have yet to discover exactly how the entities are connected, it should be possible for your group to sketch out a simple form of ER diagram.
+
++ **Produce an ER diagram for your application's schema.**
++ Include the table names, field names, field types, and some simple connecting lines to indicate which tables are related.
+
+---
+
+## Task 7: Upload your design documents
+
+**Upload pictures of any hand-drawn documents or notes on the VLE, or enter your notes manually.**
+
+There is a [special forum](https://learn.gold.ac.uk/mod/forum/view.php?id=490039) set up for this under week 3 (each solution should be its own discussion/post). Try to come up with a memorable name for your app, and put it in the title of your discussion. This will help when it comes to the voting, as we will all know which app we're talking about!
+
+---
+
+## Task 8: Questions?
+
+Take a few minutes to reflect on what you have done today. *What did you learn, and what did you find confusing?* 
+
+**Make a note** of what you need to read more about, and what questions you'd like to bring to Friday's lecture.
+
+---
+
+## Extension: Dig and Discuss
+
++ Read a bit deeper about MySQL field types. In particular, start to think about the storage requirements for your chosen types. Do you still feel these were the best choices?
++ If you are friends with someone from another group, why not discuss your groups' respective outputs over lunch/tea? Perhaps your friend will spot a weakness to your design, or vice versa.
++ Over the coming week, you will have the opportunity to review some of the other groups' uploads on the VLE. Do that, and leave them some feedback.
+
+