Database Design: Tables, Fields, Relationships - Essay.

Explore Different Data Types

Explore the data types listed in Figure 7.2 and try to find at least three instances (other than images) where you could use a blob data type. Also, find database usage instances where CHAR is a more suitable data type as compared to VARCHAR. For example, you can say that 'phone numbers are more suitable to be stored as a CHAR because' and present your reasoning.Post the summarised answers to the two topics above on the Discussion Board.Discussion is very important to critical thinking. Please try to review the posts from your peers and engage in discussion.

Database Design
Databases are critical in making a web application reliable and efficient. That insurance can only be guaranteed if a database is designed thoroughly. The first step in a database design is to identify the 'tables', one way to identify an early list of tables is to find out all nouns (and entities) that appear in a requirement specification document (or any other formal/informal form of early communication with the client).

Each noun can initially be considered as a table in the database. A block diagram is used to visualise the structure. The block diagram is often called 'Database Schema' or 'Entity Relationship Diagram (ERD)'. Figure 7.3 shows three different ways a table can be represented in an early database structure design. Once a table is identified, we then identify all the attributes/fields related to the table,and we can review the list of nouns created earlier and see if a noun is more suitable as an attribute of a table rather than a separate table. For example, Name might be more suitable as part of a table 'Employee/Staff/Student' etc.

Private key is important to be marked in some way on the diagram as we use private keys to build relationships between tables, Figure 7.3 uses a key icon, a column with a 'PK' marked field and a highlighted underlined font to represent a key.Consider that you are designing a web application for an online retail store selling groceries.Assuming how existing grocery retail stores operate, shortlist at least three tables that the database will need for enabling the store to sell their products online.

Create a model diagram (using similar notation as shown in Figure 7.3) that shows tables with their related ields/attributes.If you don't engage or initiate discussions the activity of posting on a Discussion Board loses its
potential to promote critical discussions. Be that first drop of rain and interact with posts from your peers.
Post your database diagram on the Discussion Board.

Note: You can create the diagram using a paper and pen, you can use StarUML (2018) or explore other similar tools.

If you have completed Activity 7.2 (and depending on the tables you decided to shortlist) some of you will notice that there might be an evident relationship between the tables, for example (tables in italic),one customer can have one basket, one basket can have multiple items, a customer can have multiple orders and so on.

The relationships between tables makes it possible to combine and query data across multiple tables.Figure 7.4, presents three main types of relationships; one-to-one, one-to-many and many-to-many.Figure 7.4, also presents a notation to use when drawing relationship diagrams where '1' is used for one and '*' is used for many. Connolly and Hoar (2015, p.486), Figure 11.6 presents other variations of notation used to draw relationships. Watch the following video (Prescott Computer Guy 2011) for another perspective and example discussing relational databases. 

Explore Different Data Types

A fixed size content provides better system performance since it allows faster calculation within a database.  Since CHAR is fixed length, it is more suitable to use it for a predefined fixed size data up to 20 characters, such as phone numbers, postcodes, ID numbers, etc.

I agree with the student for the usage of BLOB data types to store large media files including files, audio files or images. However based on the MySQL documentation the maximum size of a file allowed for the BLOB data type is 2GB.

I agree with the student on the scenarios that CHAR is better of being used than VARCHAR because CHAR is better used in place of VARCHAR when the data expected for the filed is of the same length for example a filed taking Y or N character.

• Discuss Unit 7 Activities - Unit 7.5

Top of Form

1. a) My understanding of a try/catch/throw exception handing is simply that an input is taken and using the 'try' block it is sent to a function that evaluates it according to given criteria and, if this fails, then a catch statement is 'thrown' which gives an error statement (using $e->getMessage() which is part of the functionality of the exception object) otherwise if the evaluation passes then control is returned back to the "try" block and the program continues on.
2. b) There are a variety of areas in an application that errors can be caught i.e.,

• Errors on validation
• Data is missing
• Failing to connect with databases

The real problem is that there are probably a multitude of places where you could put a specific try/catch/throw error exception handler but these would be numerous and in a really complex programme, no matter how hard you try and debug your code, some bugs may still slip through and so you may decide to put a general exception handler in place so that the error handling is a little bit more user-friendly than the standard PHP error handing.

The reference below is quite useful since it provide a user friendly break down of the PHP exception handling.

w3schools. 2018. PHP Exception Handing.

3) RE: Discuss Unit 7 Activities - Unit 7.5

You are totally correct in that there are a whole multitude of places one could use a try catch block but there are certainly places you don't need to and places you most definitely should. For example, the mysqli class used to carry out database queries in PHP has its own little connection error handling method built into it as per below:

Define the constants for the database connection

   define("USER", "database-user-name-here");

   define("PWRD", "user-name-password-here");

   define("DB", "database-name-here");

//Create the connection variable

   $mysqli = new mysqli("localhost", USER, PWRD, DB);

//If the connection failed error out

   if (mysqli_connect_errno()) {

      printf("Connect failed: %sn", mysqli_connect_error());

As you can see the mysqli_connect_error() method returns true if there was a problem getting to the database such as wrong password, user doesn't have permissions for a particular statement or the database is not up which is why the mysqli_connect_error() is printed to screen as a string. You wouldn't want to use a try catch block here. However, if you had a complex class and wanted to make sure that the method you were using must return an expected value before you wanted the script to carry on then a try catch block is ideal. This way you can output a specific error statement that allows you to narrow down the debugging to that line and also stops the execution of further logic within that catch statement. An example can be seen below:

//Set up bill array for each month

Shortlist Tables for an Online Retail Store and Create a Schema

$julyBills = array("gas" => 40, "elec" => 45, "insurance" => 95);

$augBills = array("gas" => 40, "elec" => 65, "insurance" => 75);

$sepBills = array("gas" => 80, "elec" => 95, "insurance" => 70);

$octBills = array("gas" => 35, "elec" => 35, "insurance" => 45);

$balance= 400;

//Create function to py bills

   function payBill($amount, $balance, $month) {

//The balance variable has global scope

   global $balance;

   $overdraft = 200;

//If the total from the array passed in more than the balance plus overdraft throw exception

   if (array_sum($amount) > ($balance + $overdraft) ) {

      throw new Exception("Unable to pay bills as you have exceeded your overdraft in $month.")

//Otherwise return the new balance

      return $balance-= array_sum($amount);

//Try to pay the bills

   try {

      echo payBill($julyBills, $balance, "July") . "n";

      echo payBill($augBills, $balance, "August") . "n";

      echo payBill($sepBills, $balance, "September") . "n";

      echo payBill($OctBills, $balance, "October") . "n";

//Catch the month where there was an error

   } catch (Exception $e) {

      echo 'Caught exception: ', $e->getMessage(), "n";

The try will attempt to pay all the bills until the balance is less than the current balance plus 200 overdraft. If the bill was not able to be paid then the exception is thrown with the month that exceeded the overdraft so that no more bills are paid and you can see where the error was thrown.

I Second you on this that Mark is correct on his understanding of what try/catch/finally blocks are used for but some of the instances that he has suggested are not necessarily correct for example mysqli_connect class which already has its own exception handling mechanism. A good scenario to use exception handling is where exceptions are expected for example when a parse operation whether implicit or explicit has to be done. For example evaluating the code below, an exception is thrown if the parameter passed is not an integer. This is a good example.

Function foo($x)

try{

     $y=$x/2;

     Print “the result of the calculation is $y”;

}catch (NumberFormatException $e){

     Print “$x is not an integer”;

}finally{

     Print “Number or letter determiner has completed”

Bottom of Form

• Activity 7.5: Error handling

Top of Form

1. The try/catch blocks are used for handling connection errors and object errors. PDO includes three methods, which can be set using setAttribute() method:  PDO::ERRMODE_SILENT (sets the error code to a user), PDO::ERRMODE_WARNING (sets the error code and outputs a warning message), and PDO::ERRMODE_EXCEPTION (sets the error code, throws a PDOException, specifying the settings for representing error information).

 Example of the try/catch using:

<?php

function checkAge($age) {

            if($age<=18) {

                        throw new Exception("You are not old enough!");

            return true;   

 try {

            checkAge(17);

            //a user will not see this message in case of exception

            echo "You are old enough";

 catch(Exception $err) {

            //a user will see this message in case of exception

            echo $err->getMessage();

 The try block includes all that is supposed to throw an error (exception), e.g. connection error.  In the case of error, an Exception PHP class will be executed and the event will get handled by the catch block. Inside the catch block the code to something with the object variable (e.g. $err) which the Execution was assigned to.

Understand Relationships Between Tables

I agree with you on the definition of exception handling and your example of a good scenario on where exception handling can be used. Exception handling ensures a block of code is executed without stopping in case any errors or exceptions are encountered within the block of code. When a block of code is written under try{} the block is executed until an exception is thrown and the program starts to execute the block of code in the catch{} one by one. The finally{} always executes when the try{} block exits whether or not an exception has occurred. PHP has its own classes with inbuilt exception handling which does not require the user to declare the try/catch/finally block. A good example of this is the MySQLI_connect and PDO classes.

1. There are several common types of programming and software errors.
2. Syntax Errors. May occur when writing code the computer language syntax specifications are ignored.  Typos or grammatical mistakes may also be the reason for syntax errors.  This type of error is easy to detect and fix.
3. Compile time errors. This type of error occurs during the compilation stage.  It mostly includes syntax errors, semantic errors or errors related to a compiler's failures.  The errors of this type can be handled easily.
4. Logical errors. The term describes situations when the program output does not meet the requirements.  In other words, the program does not function as it should.  Since the origins of these types of errors are difficult to detect, logical errors are hard to handle.
5. Run-time errors.  May occur after the compilation stage, as they are not visible for a compiler.  This type of error includes a program's failure to carry out some operations.  Run-time errors can be detected and fixed by going back to the pre-compiling stage and rewriting the code.

I agree with you on the type of errors that are encountered during writing of the code, compiling and running of the program. Syntax errors are seen during writing of the code if the user is using an IDE like Netbeans or Dreamweaver. Compile time errors are encountered during compilation time of the program while logical errors are encountered at run time thus they can also be run-time errors resulting to use of the wrong logic.

Bottom of Form

5 RE: Discuss Unit 7 Activities

i am actually having this problem on the Activity 7.6: PHP and MySQL Working Example. 

does anyone know where is the problem?

html> 
Warning: mysqli_connect(): MySQL server has gone away in C:xampphtdocs6CC549listdata.php on line 11

This error is caused by a server time out thus the connection is closed. The solution is to identify which part of your code is causing the timeout. Another cause of the error could be a query running after the connection has been closed.

Warning: mysqli_connect(): Error while reading greeting packet. PID=1528 in C:xampphtdocs6CC549listdata.php on line 11

A possible cause for this error could be you are trying to access the server externally and if your MySQL is configured to reject external connections then this error is encountered. If you are accessing the server from the same computer it would be advisable to use localhost as the domain name in your connection string.

Warning: mysqli_connect(): (HY000/2006): MySQL server has gone away in C:xampphtdocs6CC549listdata.php on line 11
Connection unsuccessful 

This error is caused by a server time out thus the connection is closed. The solution is to identify which part of your code is causing the timeout. Another cause of the error could be a query running after the connection has been closed.

Warning: mysqli_query() expects parameter 1 to be mysqli, boolean given in C:xampphtdocs6CC549listdata.php on line 31

This error can be caused by an error in your query so try and run the query from PhpMyAdmin to make sure its working fine and then append it in your code. Another way to know where the error in the query is to echo mysqli_error;

Notice: Trying to get property 'num_rows' of non-object in C:xampphtdocs6CC549listdata.php on line 32

The cause of this error is that you are trying to get the num_rows from a variable that is not object. A common miss is that you have not created the object using either mysqli_fetch_array or mysqli_fetch_assoc.

Warning: mysqli_close() expects parameter 1 to be mysqli, boolean given in C:xampphtdocs6CC549listdata.php on line 45

This error can be caused by an error in your query so try and run the query from PhpMyAdmin to make sure its working fine and then append it in your code. Another way to know where the error in the query is to echo mysqli_error;

Top of Form

1. Try / Catch blocks are part of most modern programming languages, including PHP, C# and Java.

They take the same form, which is to wrap code within a Try / Catch block. The try contains the code to be executed, and if an error is encountered during execution, the error together with its message is passed to the catch element of the block. Additionally, there is a finally element, which is used once the code has executed in totality from either the try or catch element.

As an example, let us take writing to a database (I have used c# as an example).

String connstr = “connectionstring1Example”;

Sqlconnection Conn = new SQLConnection(connstr);

Conn.Open():

Conn.execute(“Insert to table (tablecolumn1) values (‘hello world’);”);

Conn.close();

Catch(Exception e)

Console.Writeline(“Could not insert to database” + e.innerexception().ToString());

Finally()

Console.Writeline(“Reached Finally block”);

1. Exceptions

There are huge amounts of exceptions that can be caught, however they either need to be exceptions within the language themself (e.g. trying to write a string to an integer, not being able to connect to a database), or they have to be defined by a custom exception by the programmer. For example, you can throw any type of exception within any method if you wish, together with sample commentary for the user. This is especially useful if you want someone to be over 18 registering for the website as an example, or you do not want someone typing in a number less than 100 on a web form. I was able to see this from stackoverflow where a developer is throwing an error in their code :-

Exceptions that are difficult to track are items such as if your field mapping is incorrect, and you are writing to the wrong text field. Both fields are probably defined as varchar, therefore no error would be thrown by the application even though it could lead to serious issues within your application.

I agree with you on the definition of exception handling and your example of a good scenario on where exception handling can be used. Exception handling ensures a block of code is executed without stopping in case any errors or exceptions are encountered within the block of code. When a block of code is written under try{} the block is executed until an exception is thrown and the program starts to execute the block of code in the catch{} one by one. The finally{} always executes when the try{} block exits whether or not an exception has occurred. PHP has its own classes with inbuilt exception handling which does not require the user to declare the try/catch/finally block. A good example of this is the MySQLI_connect and PDO classes.

7) Activity 9.1: jQuery Form Element Selectors

The focus element is used to gain focus on an element.

In the example below when the user clicks on the text element it triggers a chain of actions in  the following order:
(1) this element, that is the input text element
(2) next, move onto the next element that is the password element
(3) apply css style to display the text ‘focus activated’

  $( this ).next( "span" ).css( "display", "inline" );

(4) I used the blur method so that when the user clicks on another element it fades out the original element’s text slowly(3 seconds = 3 milliseconds). 

$("input").blur(function(){

   $( this ).next( "span" ).css( "display", "inline" ).fadeOut(3000);

<!doctype html>

<html lang="en">

<head>

  <meta charset="utf-8">

  <title>focus demo</title>

  <style>

  span {

    display: none;

  </style>

  <script data-src="https://code.jquery.com/jquery-1.10.2.js"></script>

</head>

<body>

<p><input type="text"> <span>focus activated</span></p>

<p><input type="password"> <span>focus activated</span></p>

<script>

$(document).ready(function(){

$( "input" ).focus(function() {

  $( this ).next( "span" ).css( "display", "inline" );

(document).ready(function(){

$("input").blur(function(){

   $( this ).next( "span" ).css( "display", "inline" ).fadeOut(3000);

W3Schools. jQuery focus() Method

I agree with your description of the $( ":focus" ) which is a pseudo-class selector that is used to get the currently focused element. Using  $( document.activeElement ) retrieves the element without having to search the entire DOM tree. This selector has to be used with another selector or a tag name and if not it will be the same as (“*:focus”). Your example shows a very reasonable and practical way to use $( ":focus" )

Activity 8.2: Create a Model for the Case Study Presented in the Assessment Brief for this Module

Using lists, UML Use Case diagrams (Agile Modeling 2014), scenarios, storylines or any other form of data representation create a set of functional requirements.

Post your functional requirements list or model on the Discussion Board. Compare your list with others',: do they have different requirements? Do you agree or disagree? Ask questions and provide feedback.

Scenario

New customer
To search for flights, accommodation, car hire and insurance there is no need to register onto the site. Although when purchasing a holiday it is mandatory to register for an account so that not only can B&B keep all the necessary information in the database but also the customer can login at any time to check for any changes by logging into his account or make any necessary changes.
The user goes online to find a holiday. He types “book holiday in Spain” and amongst the search results he finds B&B. The link ‘Book and Board’ is clicked and user is taken to the site.The user types in the destination with leaving and returning dates.

User is displayed with a list of choices for flights, accommodation, car hire, travel insurance for that particular destination. The user then is given the choice to choose and narrow it down to his liking by cost, distance, top deals, most recommended by customers who have previously booked before and recommendations by B&B.
The user selects the different parts of the package and is now ready to purchase it. The user is then taken to the registration page to register his details and the package details that are in the shopping basket are then displayed after the registration process allowing the user to see whether everything he has chosen is correct and if so pay online using B&B’s online secure payment system or PayPal’s.

Once user pays for the holiday, the user receives the travel tickets along with accommodation through his B&B account after purchase and also two weeks before the holiday is to take place through the post. The user can then login from anywhere to access the booking details.
Any amendments the user may need to make such as changing name or dates after the purchase has been done incurs a charge on which a call to one of B&B’s representatives may be needed.

Existing customer
The process is the same as for the new customer although there is no need to register as the customer only needs to login and make the booking.  

Existing customers who aren’t confident in booking online due to a lack of basic IT skills can just call one of B&B’s representatives who will do the booking over the phone by calling the customer back so that customer won’t be charged for the call.

A customer who is able to book a package online although wishes to speak to a respresentative to book it for them can also do so.

UML Use case diagram

The use case(attached) describes the various functions or behaviours that B&B undertake.

It represents a dialog between a user and the system, from the user's point of view.

Functional requirements

- Website needs to be responsive, easy to use, secure and attractive

- Needs to have accessibility features for people who need larger fonts/images

- Advertise using GoogleAdWords 
- Appear in search engines using Search Engine Optimization(SEO), newspapers and travel brochures found in agencies

- A customer can create an account, login and search for holidays

- Agent can make amendments to current bookings, confirmations and cancellations.

- A customer/visitor can search for holidays

- Online agents provide support with bookings issues

- Customer can check whether customer is eligible for a discount from their account

- Agent contacts customers with offers over the phone and/or by email depending on their communication preferences

- Online agent has administrative privileges in case a customer need their details changed. Only agents would change account information

- Every order gets an ID the customer can save.

- Quarterly data is shared between the manager and its branches over a secure Private Network.

- Collect brief feedback from a customer after a package is sold.