INTRODUCTION

In the previous chapter, you learned a bit more about databases and database management software (or DBMS). In this chapter, you will learn how to design your own databases. This includes making decisions such as:

• deciding which data to record
• deciding how to group the data
• deciding which fields to use in your tables
• deciding what type of valuable information should be stored in these fields
• deciding how to maintain data.

In fact, for every database you design, you will be faced with hundreds of choices, layouts and decisions. These decisions can often make it feel overwhelming to start working on a database. The goal of this chapter is to not only give you guidelines that will help you to create the best possible database, it will also show you how to complete each step along the way. By the end of the chapter, you will have created a database that can be used, as it is, in an instant messaging application.

DATABASES

Data is unprocessed numbers, signals or facts and in order for humans to use it and for computers to share it, the data needs to be processed and organised into meaningful information. One way in which data is shared amongst people is by the use of letters and characters.

Once data has been processed into information, this information can be interpreted to provide knowledge, which allows yoou to make decisions based on that knowledge.

A database is designed to store data (facts), these facts are organised within the database so that they can be sorted or extracted in response to a user’s requirements which will allow them to make a decision.

For example, your school probably has a database containing the details all of the books in your school library, with data such as author, subject, title, ISBN number, and so on.

If you were given a History project to research the history of your city, you would be able to search the database for books on written about this subject. With a list of the available books the database provides, you would be able to decide which of them to use for your research to get the information you required for your project.

UNIT
9.1 Characteristics of quality data

Let’s first clarify the different terms that will be used and how they relate to each other:

• Data is raw material; and information is when data is organised and presented by someone. For example, if I take individual pictures of all the learners in your school, the collection of photographs is data. When the photographs are re-organised into each class unit, then the photographs become information.
• Knowledge is information read, heard, or seen, and understood.

These three elements help a person to make beneficial decisions.

In order for the computer to use the data to produce accurate results that allow decision making, the data has to be of a good quality.

The table below summarises the characteristics of quality data.

Table 9.1: Characteristics of quality data

VALIDATE YOUR DATA

Data validation refers to the process in which you check whether the data is accurate, in the correct format or of the correct type before recording it in your database. This ensures that the data in your database is consistent and accurate. Since data validation is usually created by a programmer, there are thousands of different types of data validation that can be done. However, the following are some of the easiest and most useful ways to validate data:

• format check: A format check ensures that the data is in the correct format. This can be used to ensure that an email address contains an @ sign and ends with a country code.
• data type check: A data type check ensures that the data is of the correct data type (e.g. string, integer, double or datetime).
• range check: A range check ensures that the data falls within a specific range. For example, if a range check can be used to ensure a student’s grade is set between 1 and 12.
• check digit: A digit check ensures that the data is a specific number of characters long before it is recorded. This is useful for data like phone numbers or ID numbers.

DATABASE DESIGN TIPS

Using the information, you learned in the previous two sections, you should be equipped to determine what you need to create a database. In this section, you will learn how to do this by following these simple rules:

1. Accessing data

2. Validate your data

3. Standardise your data

4. Use effective names

5. Manipulating and grouping data

6. Be careful with calculated fields

7. Have one source of truth

ACCESSING AND MANIPULATING DATA

In general, there are two ways to obtain data for a database:

• With manual data collection, the data is collected and entered into the database by hand. This can be very time consuming, but is often the only way to capture data, especially if you are the first person to create the data. For example, when Blizzard created World of Warcraft, they had a team of video game writers who wrote millions of lines of dialogue for the game. These lines were then manually captured in one of the games databases.
• With the electronic collection of data, data is automatically obtained from an electronic source and added to a database. For example, Facebook automatically records every status update, comment and like on their website and stores it in a database. With more than 1,2 billion status updates and comments per day, recording these updates manually would be impossible. The disadvantage of electronic data collection is that it usually requires significant programming to set up and mistakes can easily be introduced in the data. However, once it is set up it allows incredible amounts of data to be collected quickly.

For the rest of the chapter, you will work towards building a database for a new messaging app called Secret Messaging Service (or SMS for short).

While it would be awesome to build a database capable of handling 1,2 billion messages per day, the goal of the chapter is to build a proof of concept database that can be used to work with a prototype of your application. At the most basic level, the database will need to store the details of different users, as well as the messages sent between the users.

QUALITIES OF VALUABLE INFORMATION

A second important aspect to consider is what data is valuable to record. When designing a database, it is easy to go overboard with the information you collect, so you start asking users additional questions (like age, phone number and address) just so that you can add the information into your database. Once in your database, the data lies there, increasing the size of your database without ever being used.

This problem suggests three important questions to ask yourself when creating a database:

• Will I ever use the data in this field?
• What fields do I need specifically for my application?
• What fields would I need for my application in the future?

Based on these three questions, you can determine whether certain types of information are valuable to the application and should be recorded and stored in the database.

HOW TO GET TO INFORMATION

HAVE ONE SOURCE OF TRUTH

This links to redundancy and consistency issues discussed in the previous sections, but a database should only have one source of truth. This means that both consumers and database users should never be confused about where the data comes from or which data is real. This has a few implications for you as a designer:

• the same data should not be recorded in more than one place
• unused tables and fields that can be confusing (such as “song_id_old”, or “song_id_2”) should be removed
• tables that are automatically created by combining other tables should be labelled clearly
• numbered tables (like “users_1” and “users_2”) should either be combined into one table (“users”) or renamed more clearly (“users_south_africa” and “users_rest_of_africa”).

STANDARDISE YOUR DATA

As you learned earlier in this chapter, the data in a database should be consistent. One way to achieve consistency is by standardising your data so that certain fields always look and work in the same way. For example, imagine that you are building a database for the school and you need to capture the grade a student is in. If multiple people enter the values, you may end up with non-standard data, like:

• “5th Grade”
• “5”
• “Grade 5”
• “5th”

If you would like all the data to use a format like “5th Grade”, you could enforce this using data validation. You could also set the field to only accept numbers or integers (like 1,2 and 3) and then link this field to a different table where 1 is equal to “1st Grade” and 2 is equal to “2nd Grade”.

There are many ways of standardising your database, so it is up to you to choose a method that works.

USE EFFECTIVE NAMES

Your tables and fields should use effective names that make sense and are easy to understand. Working with a database where all the tables are called “Table 1”, “Table 2” and so forth is not best practice.

When choosing names:

• choose descriptive names
• choose simple names
• avoid abbreviations
• do not use numbers in the name
• do not use spaces in the name
• do not use strange characters in the name (other than underscores)
• use a consistent naming pattern

Field names and table names are also usually written in lowercase (so user_name rather than UserName).

MANIPULATING AND GROUPING DATA

Data, and especially complex data, should be broken into smaller logical pieces and grouped according to the chosen categories. This allows you to work with the data more easily. For example, it is usually better to store people’s first name, middle name and last name separately, rather than as one long string in a single field. This allows you to use those items individually.

By breaking the data into logical pieces, you can send a customised greeting message like “Welcome, John!”. If the data is grouped, you would need to create a string handling script to find the first name.

BE CAREFUL WITH CALCULATED FIELDS

Calculated fields are incredibly useful since they allow you to automate large parts of your database. For example, rather than entering a student’s score on four tests and her average, you could create a field that automatically calculates the average. However, you need to be careful when doing this to ensure that the field will give the correct answer in all situations.

To understand the problem, look at the following set of marks and average:

In this example, the database correctly calculated an average of 75%. Now, imagine that instead of scoring 50% for the first test, the student failed to write the first test. Your database now looks as follows:

What do you think the average should be? Depending on how you design the calculated field, the average could be 62.5%, it could be 83.3%, or you could receive an error. Since only one of these values will be correct based on the school’s rules, you need to think carefully about how you program the field to ensure it always gives the correct answer.

9.2.1For each question, select the correct answer.

a.Which one is a characteristic of quality data?

A.Accuracy

B.Sensitivity

C.Punctuality

D.Readability

b.Which characteristic to quality data does this statement refer to? The data in one part of your database should not contradict or differ from the data in another part of your database.

A.Currency

B.Accuracy

C.Consistency

D.Relevancy

c.You are creating a database for a South African news website. If the database only contains news from Europe, the database will have poor ...

A.Currency

B. Accuracy

C.Consistency

D.Relevancy

d.After creating your news website database, you realise the database only updates once a week. Unless this problem is fixed, the database will have poor ...

A.Currency

B.Accuracy

C.Consistency

D.Relevancy

e.After the website has been running successfully for a few months, a friend asks you to go hiking with him in Lesotho. When you reach your destination, you find out that there is no Internet and you cannot update your website. Once you get back home, you need to add the missing data to ensure your database has a good ...

A.Currency and completeness

B.Accuracy and currency

C.Consistency and accuracy

D.Relevancy and consistency

9.2.2What are the five characteristics of a good database?

9.2.3List the characteristic(s) of quality data that are missing in the following database examples.

a.A database for currency exchange rates that is updated every month.

b.An African weather app with weather details for Australia.

c.A classroom list of attendance with the names of students’ pets.

d.An exercise application that takes your height.

9.2.4What is data validation?

9.2.5Suggest one form of data validation for each of the four fields. Give reasons for your answers. Look at the “movies” table from the previous unit.

9.2.6What is the difference between data, information and knowledge? Provide your own example.

9.2.7What is the aim of grouping data?

UNIT
9.2 Creating a simple database

The first step to building the SMS database is to create the database. To do this, you need to:

1. install database software

2. create the SMS database

3. create the first table.

Since this database will be used for rapid-application development (RAD), we will use Microsoft Access to create it. Microsoft Access is an easy to use DBMS with a what you see is what you get (abbreviated as WYSIWYG) user interface. This means you can create the database visually and you can see the changes to the database as you make it.

However, as a programmer you will be working with databases for the rest of your life, which means you will be using SQL for the rest of your life. This database is an excellent opportunity to start using SQL, so the SQL command for each still will be shown. At the end of the chapter, the same database used throughout the chapter will be recreated quickly in SQL, with each line of code explained.

AS YOU BEGIN WORKING WITH DATABASES, YOU WILL NEED TO KNOW THE FOLLOWING KEY TERMINOLOGY:

Field: A field is part of a record and contains a single piece of data for the subject of the record.

Primary Key: A field that uniquely identifies each record in the table.

Composite/Combined Primary Key: Is a set of more than one key that, together, uniquely identifies each record.

Foreign Key: A foreign key is a primary key in one table that appears as a field in another table.

Data-types of fields: The fields within a database often require a specific type of data to be input. Data-type that each field stores has to be declared/set. Common datatypes are: text, number, data, time Yes/No,etc.

Record: A record is composed of fields and contains all the data about one particular person, company, or item in a database

Table: A database table is composed of records and fields that hold data. Each table in a database holds data about a different, but related, subject.

Relationship: Is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables, while linking disparate data items.

CREATING A DATABASE

Now you are ready to create your first database.

Example 9.2.1 Creating a database in Access

To create a database in Microsoft Access:

1.Open the Start Menu and search for “Access".

2.Click on the Access item to open the application.

3.Once Access is open, double click on the Blank database option in the main window.

4.Enter the name “SMS.accdb” in the File name text box at the bottom of the File New Database window.

5.In the window that opens, click on the Open icon next to the File Name text box.

6. In the File New Database window that opens, select an appropriate folder to save your database and click OK.

7. Click OK to close the window, then click on the Create button to create the database.
The SMS database should now open in Access.
Congratulations, you have just created your first empty database!

DESIGN THE TABLE(S)

Now that your database has been created, you can start thinking about your first table. However, before you can create it you need to answer these four questions:

• What table are you creating?
• Which fields will you include in your table?
• What data type will each of these fields be?
• Which column will be the primary key for your table?

Let’s work through these questions and the practical steps one-by-one.

TABLE

You must choose which of two tables to create first: “users” or “messages”. The “users” table will contain all the users’ information, while the “messages” table will contain the messages sent between users.

There are two possible approaches:

• Create the central table of your database first, because this is the most important table in your database. In the SMS database, the “messages” table contains the most important data in your table, so it would be the central table of your database.
• Create the table that is not dependent on any other tables first, because this table can be created without relying on any other tables. In the SMS database, the “users” table is independent of the other tables, since you can know who the users are without having any messages, but you cannot have messages without knowing which users sent them.
  For this example, we will follow the second, more common, approach and create the users table first.

Example 9.2.3 Renaming an existing table in Access

If Access automatically created an empty table for your database. This default table is called “Table1” and can be found in the All Access Objects panel on the left side of Access.

To rename the “Table1” table to “users”:

1.Open Access.

2.Inside Access, open the “SMS.accdb” file. You should see the file in the Recent panel on the left of the Access window. If not, click on the Open Other Files button and open it from the folder where you saved the database previously.

3. Find the “Table1” table in the All Access Objects panel on the left side of Access.

4. Right click on this table and select the Rename option. Note, you could also use the F2 hotkey to rename the table.

5. Enter the name “users” for the table and press Enter.

6. Click on the Save icon to save your database.

Take note, when creating a new table, you may be unable to rename it immediately. However, by pressing on the Save icon, Access will ask you to enter a name for your database. You can use this opportunity to rename it.

Congratulations you have just renamed the existing default table “table1” to “users”!

Example 9.2.4 Creating a new table in Access

You may not always have an existing table to rename. To create a new table in Access:

1. Open Access and the “SMS” database.

2. Open the Create ribbon at the top of the Access window.

3. Click on the Table option.

4. Save the database using the CTRL + S hotkey.

5. In the dialogue box that opens, enter the name “messages” for the table and press the OK button.

Congratulations you have just created a new table!

FIELDS

The next decision you need to make is about the fields to include in the “users” table. Since your application is called Secret Messaging Service, you should probably not collect too much information about your users. Instead, you should ask yourself what the least amount of information is that you need to make your application work.

For your application to work, the only fields that you require are:

• username
• password.

It is possible that, in the future, you may want to use email to authenticate user accounts (like Facebook) or link user accounts to phone numbers (like WhatsApp), but for now you do not need either of those fields.

However, before you can create your two fields you need to select a data type for them.

FIELD DATA TYPES

In the previous section, you decided that your table will have two fields: “username” and “password”. Both fields will therefore potentially contain a combination of letters, numbers and symbols. This means that both the fields should be text fields. Since neither the username nor the password should be longer than 255 characters, both fields can be Short Text fields in Access.

Example 9.2.5 Creating fields in Access

To create the two short text fields:

1.Open Access and the “SMS” database.

2.Inside the “SMS” database, click on the users option in the All Access Objects panel to open your users table.
You should now see an empty table.

3. Click on the Click to Add option at the top of your table.

4. Select the Short Text option.

5. Enter “username” to create the username field.

6. Select the field and click on the Fields button at the top of the Access ribbon (under Table Tools).

7. In the Fields ribbon, tick the Required and Unique buttons. This will make sure that each user has a username and that all usernames are unique.

8. Using the Fields ribbon, change the Field Size of the “username” field to 30. This will limit the number of characters that can be entered into this field to 30.

9. Repeat the process for your second field and call it “password”. The password field should not be unique, since users are allowed to have the same password.

10. Set the Field Size of the password field to 20.

11. Save your database using the CTRL + S hotkey.

Congratulations, you have just created your first two fields in Microsoft Access!

Example 9.2.6 Changing the primary key field name

To change the “ID” field’s name:

1. Open the “users” table of your “SMS” database.

2. Right click on the “ID” field and select the Rename option.

3. Enter the name “userid” and press Enter.

Congratulations, you have just renamed your primary ID field and finished creating your first table! To do this, you had to create the table, create two fields and select their data types. You also had to make sure that the entries in the username field were unique and required, and that passwords were required.

The database should now look like this:

With your username and table created, you are ready to start adding data to it.

In the next unit, you will learn how to add data, edit data and delete data from your table.

UNIT
9.3 Working with data

Now that you have a database, you need to test whether it will work in your application. At this stage in the rapid application development cycle, you are not ready to give the application to your friends so that they can create their own usernames. This means that, to test the database, you will need to add your own mock data.

In this section you will learn how to add data, change data and modify data in your database.

ADDING DATA TO A TABLE

To test your database, you should add the following five users to your table.

Example 9.3.7 Adding data to your table in Access

Now that you have your data, you can add it to your table. To do this:

1.Open the “users” table of your “SMS” database.

2.In the first cell of the “username” column, enter “LovelyLethabo” (without the quotation marks) and press Enter.

3.In the first cell of the “password” column, enter “P@ssw0rd” and press Enter.

4. You have just added your first record! Did you see that the userid incremented automatically?
Repeat this process until you have added all five records to your table.

5. Click on the Save icon (or press the CTRL + S hotkey) to save your table.

EDITING DATA ON A TABLE

After telling the user “Shadowzzz” that “password” is the least secure password in the world, he has asked you to change his password to “AmongAngerAgainst”.

Example 9.3.8 Modifying data in Access

Fortunately, modifying data is incredibly easy in Microsoft Access. To change Shadowzzz’s password:

1. Open the “users” table of your “SMS” database.

2. Find the record with the username “Shadowzzz” and click on its password.

3. Enter the new password: “AmongAngerAgainst”

4. Save the database by using the CTRL + S hotkey.

You can change any value in your table like this, as long as the new value meets the requirements of the field.

DELETING DATA FROM A TABLE

After using your SMS application for a while, the user LovelyLethaba asks to be removed from your database.

Example 9.3.9 Deleting records in Access

To delete LovelyLethaba’s record from your database:

1. Open the “users” table of your “SMS” database in Microsoft Access.

2. Right click anywhere on LovelyLethaba’s record.

3. Select the “Delete Record” option.

Congratulations, you have just removed a record from your database!

In general, you should think carefully before deleting any records. Since databases are relational, deleting a field on one record might cause errors on many other tables. In general, it might be better to add a Boolean field to your table that you can use to enable or disable users. You should also think carefully before deleting any tables from a database. Once a table has been deleted, it is gone forever! Unlike many other actions, you cannot use the Undo button to correct your mistake if you accidentally a table.

Activity 9.4 Working with data

9.4.1Choose the answer and write ‘true’ or ‘false’ next to the question number. Correct the statement if it is FALSE. Change the underlined word(s) to make the statement TRUE. (You may not simply use the word ‘NOT’ to change the statement.)

a.You should think carefully before deleting any records. Since databases are flat, deleting a field on one record might cause errors on many other tables.

b.It might be better to add a Boolean field to your table that you can use to enable or disable users.

c.Once a table has been deleted, you can use the UNDO button to correct your mistake if the table was accidentally deleted.

9.4.2a.Give a step-by-step procedure of how you would add someone with the username “Fahd_here” and password “MyKitt3n$f00D” to your messaging database.

b.Let’s say they want to change their username to I.am.Fahd, how will you do that in the database?

9.4.3a.Add the following data to your “books” database.

Take note, the table above should not be added to your database as it is. Instead, the data should be placed in the correct fields and tables of your database.

b. Add at least ten records based on your own school to the “sports” database. Make sure to include records for both tables.

UNIT
9.4 Setting up relationships

Having successfully created your first table and added data to it, you are ready to create additional tables and set up the relationships between them. In this unit, you will therefore learn how to:

• plan the relationships between your tables
• create additional tables
• create the relationships.

DESIGNING RELATIONSHIPS

To plan the relationships between your tables, you can create an entity relationship diagram (ERD). These diagrams show the tables in your diagram, the fields in each table, and the relationships between the fields of different tables. This section will look at how an ERD can be drawn in two phases:

• first, the tables will be drawn
• second, the relationships between the tables will be drawn.

DRAWING THE TABLES

To see how to create a table in an ERD, you can start with the first table in your database.

Example 9.3.10 Creating an ERD for your first table

An ERD is used to map the relationships between your tables. To create an ERD for your first table:

1. On a piece of paper, draw a square with enough space to list all the fields of your table.

2.Inside the square, at the top, write down the name of your table (“users”).

3.Underneath the table name, make a list of all the table’s fields.

4.Draw a line between the table’s name and the table’s fields, as shown in the image below.

5. On the right-hand side of the square, write down the data type for each field.

6. Draw a line between the field names and their data types.

Congratulations, you have just created the first table for your entity diagram! In the next example, you will plan the remaining tables for your database using an ERD.

The second table of your ERD should be the “messages” table that will be used to capture and store all the messages sent between users of your application. Before you can draw the table on an ERD, you first need to decide which fields to create for this table. Based on the description of the table, the table requires at least three fields:

• message (text)
• senderid (integer)
• receiverid (integer)

Using information, you can link each message to a specific sender and receiver based on their unique user IDs. However, without recording when the message was sent, the messages may not appear in the correct order in your application. You should therefore add a fourth field (called timestamp) to your table. You should also add a “messageid” primary key to your table, to ensure that each message can be uniquely identified.

Example 9.3.11 Adding the “messages” table to the ERD

To add the “messages” table to your ERD:

1. Draw a second square on your piece of paper.

2. Add the table name (“messages”) to the top of the square.

3. List the four fields on the left side of the square, and their data types on the right side of the square. Take note, the “timestamp” field’s data type will be datetime.

4. Draw the lines between the table name, field names and data types.

Your ERD should now have two tables on it (as is shown below).

DRAWING THE RELATIONSHIPS

Two tables can have three different types of relationships. They are:

• one-to-one: With a one-to-one relationship, one record on a table is related to one record on another table. For example, if you had a “user preferences” table, then each user would have a single set of preferences, and each set of preferences would be related to a single user.
• one-to-many: With a one-to-many relationship, one record on a table is related to many records on another table. For example, in your database, a single user on the “users” table might be the sender of more than one message on the “messages” table.
• many-to-many: With a many-to-many relationship, many records on one table are related to many records on another table. For example, if you had a “conversations” table in your application, then each user might be part of multiple conversations while each conversation might have multiple users.

To draw the relationships for your database, you need to carefully consider how the data is linked in your table. The best way to do this is to ask yourself what the minimum and maximum number of connections between your tables are. Once you know this, you can indicate these connections using one of the symbols from the table below.

Example 9.3.12 Drawing the relationships

To draw the relationship between the “users” and “messages” table:

1.Ask yourself, which fields connect these tables? The answer is that the “userid” field in the “users” table is related to the “senderid” field in the “messages” table.

2.Draw a line connecting these fields.

3.Next, ask yourself, what is the minimum and the maximum number of users who can send a single message? Each message must have at least one sender, but also no more than one user. The minimum and maximum are both therefore one.

4.To indicate this relationship, draw the one and only one symbol at the point the line connects to the “users” table.

5.Next, ask yourself, what is the minimum and maximum number of messages a single user can send? Since a user can send no messages or many messages, the minimum is zero and the maximum is many.

6.To indicate this relationship, draw the zero to many symbol at the connecting point of the line connects to the “messages” table.

However, this is not the only relationship between the two tables. The “userid” is also related to the “receiverid” field. Since the app is just for direct communications between two users at this stage (and not for group chats), the relationship between the “userid” and “receiverid” is the same as the relationship between the “userid” and “senderid”. Adding this relationship to the ERD you obtain your final diagram.

Congratulations, with these relationships drawn, any database administrator can look at your ERD and immediately understand that the two tables are related using two one-to-many relationships.

To see a second example of an ERD diagram being drawn, follow the QR codes in the margin to watch two short videos.

With your ERD diagram ready, you can now create the new table and set up the relationships between the tables.

SETTING UP RELATIONSHIPS BETWEEN TABLES

Once you have your ERD, your tables and some mock data in your tables, you are ready to set up the relationships between your tables.

Example 9.3.13 Adding tables to the Relationships view

To add tables to the Relationships view in Microsoft Access:

1.Open the “SMS” database.

2.Open the Database Tools ribbon and click on the Relationships button.

3.If this is the first time you add relationships to your table, the Show Table window should open automatically. If it does not, click on the Show Table button from the ribbon.

4.In the Show Table window, select each of the tables and click on the Add button.

5.Once all the tables have been added to the Relationships window in Access, click on the Close button.

6.Use the CTRL + S hotkey to save your table.

The two tables have now been added to the Relationships view of Access, which is arranged just like an ERD!

Once the tables have been added to the Relationships view, you are ready to create the relationships.

Example 9.3.14 Setting up the relationships

To set up the relationships between the two tables.

1.Open the “SMS” database and click on the Relationships button in the Database Tools ribbon.

2.Drag the “userid” field (the primary key) and drop it on top of the “senderid” field (the foreign key). This will open the Edit Relationships window.

3.Make sure the Relationship Type at the bottom of the window is set to One-To-Many.

4.Click on the Create button to create the relationship. You should see a line connecting the two fields.

5.Drag the “user_id” field onto the “receiver_id” field.

6.You will be informed that a relationship already exists and asked if you would like to edit the existing relationship. Click on the No button.

9.5.1An_____shows the tables in your diagram, the fields in each table, and the relationships between the fields of different tables.

9.5.2True or false? The exact positioning of your tables on an ERD does not matter, as long as they are logically organised and there is enough space between them.

9.5.3Explain a many-to-many relationship in your own words.

9.5.4Using the techniques learned in the previous sections, create the “messages” table designed above and add at least five records to it. While completing this activity, make sure that the:

a.table and field names are entered correctly.

b.primary key for the “messages” table is renamed correctly.

c.data types are selected correctly. Take note, the “message” field should have the Long Text type, while the “timestamp” field should have the Date and Time type.

9.5.5Once the “messages” table has been created, you should add the following data to it:

If you get stuck with this activity, watch the video in the QR code to see how the tables are created and the data added.

9.5.6Based on the “message” table above, answer the following questions.

a.Which field is the primary key?

b.How many foreign key fields are there? Give a reason for your answer.

c.To which field (or fields) are the foreign key fields linked?

9.5.7Based on the “books” database created in Activity 6.4, answer the following questions.

a.Draw an entity relationship diagram.

b.Based on the ERD, what are the primary key fields for the “books” and “authors” tables called?

c.What is the relationship between the “books” and “authors” table?

d.Based on the ERD, is it true that one author can have multiple books? If yes, give examples to support your answer.

Show a video of how the table is created, the fields are created and set up, and the data is entered into the table.

1.Your local library has asked you to write a program that will help them to keep track of which member has taken out which book. The following database tables have been designed to store data for the library.

Give the name of ONE field in each of the tables that can could be used as an acceptable primary key field.

a.There is currently no relationship between the two tables. Which fields will you use from each table to create a one to many relationship? Draw the relationship with the two tables in your workbook.

b.Describe a sensible way of relating the data in the two tables. You may add any fields or tables that you feel are necessary to achieve the goal.

c.The tblMembers table displays sample data, but the field data types have not been given. Suggest data types for each of the fields listed in the tblMembers table.

d.Differentiate between data validation and data verification, providing an example in each case to illustrate your answer.

e.Should the tables be renamed to Table 1 and Table 2? Justify the response to your answer with a suitable explanation.

2.The Water Department has designed a database to store information relating to South African Dams and the
Towns that are fed by these Dams. Examine the layout of the database and answer the questions that follow:

3.Relationship between tblDams and tblTowns

a.In the tblDams table, the DamID field has been created as a primary key field.

A. What is a primary key field?

B. If this DamID field did not exist, suggest another possibility for having a primary key using only the existing data.

b.The database shown is a relational database.

A. Give TWO characteristics of a relational database.

B. Suggest another database model that can be used to store data.