Out: March 14, 2017
Due: March 28, 2017

CENG256 Lab 5

Part I: Getting Familiar with MySQL on the Command Line
The following preliminary steps should be done as soon as possible. Everyone enrolled in the course should have an account on apollo and a mysql account – but just in case you don’t the turnaround time to set one up is a day or two.

  1. Login to apollo.humber.ca. Your username/ password will be same as for Windows and munro.
  2. Transfer the file employee.sql to your apollo account:
    curl munro.humber.ca/~king/CENG256/employee.sqlemployee.sql
    Examine the contents of the file. You
  3. cat the hidden file .my.cnf - it contains your mysql username and password:
    ie: password = 20Humber17
    user = username
    host = localhost
    Login to mysql using the above username and on the 2nd line enter your password at the prompt
    mysql -u username -p
    Enter password:
  4. Issue the command: show databases;

+------+

| Database |

+------+

| information_schema |Instead of king_a, king_b your name will appear.

| king_a | In what follows, where I use king_a, use your db name

| king_b |

+------+

  1. At the mysql prompt enter the following commands:
  2. use king_a;
  3. create table myTable
    (
    name varchar(20),
    age numeric(3,0),
    salary numeric(8,2) default 20000
    );
    insert into myTable values (‘John’,27,40000); --insert 2 more records
    select * from myTable;
  4. At any time you can run a shell command, including a temporary exit to a sub shell
    mysql> system ls -l
    mysql> system bash
    … enter multiple shell commands
    exit
    mysql
  5. Create the employee table from the script employee.sql and veryify that it worked:
    mysql> source employee.sql;
    mysql> describe employee;
    mysql>select * from employee;

You can try update, insert and delete operations on the table. Any SQL command that avoids Oracle extensions will work here. Because the script employee.sql drops the table right at the start you can use it at any point in time to restore the employee table to its original form.

Part II: Quiz Application Part II
The data for this exercise will be based on the same set of questions you created for Lab 4.

  1. Create an SQL script on apollo that creates 3 tables and inserts 2 Questions and up to 4 possible answers for each. Topic is a string representing a different subject. Each of your questions should have a different value for topic. (3)
    Questions(questionNo, Question, CorrectAnswer,Topic, Author)
    Answers(id, questionNo, answer)
    Authors(userName, password)
    Use the employee.sqlscript as a model. The Topic field should represent a topic such as “Programming”, “Database”, “HTML” or “JavaScript. Make each question belong to a different Topic. The Author should be you. Both fields will be used later will be used later in the assignment. In the Authors table place two entries, one for you, one for king – make the password for king fairly short and simple. The default value for author should be you.
    Verify that this works by displaying the table structure and displaying the contents of all tablestables. Hilitethe password value for king as I’ll be using it.
  2. Write a php program on munro that displays the questions and answers as a mulitple choice quiz. (This should look the similar similar to the output of Lab 4 only retrieving the data from the tables on Apollo as opposed to a text file) Develop it in stages as follows: (6 marks/2 marks for each of a-d)
  3. Connect to your database (see example connect1.php)
  4. Retrieve the all the questions only and display them as an ordered list within a form.
  5. Retrieve the questions and possible responses and display the latter as radio button groups.
  6. Add a submit button for show – you don’t have to make it active. (no marks)
  1. Create a form Lab5.html that allows you to create a question and submit it for insertion into the data base. (6 marks + 3 bonus marks)
  2. Create a simple pure HTML form that allows data entry of a single question and up to 4 answers. Use text fields or text areas. Put a radio button next to each text box so that
    you can indicate the correct answers. (2 marks)
  3. Add a submit button which inserts your question into your database tables. (3 marks)
  4. Verify that the question and answers have been added. (1 mark)
  5. Bonus: Following the method used in the File Transfer demo for the email field, create a button that will add or remove another text field so that the number of possible responses created is flexible. (3 marks)
  6. Modify the supplied web page Login1.php (on the course web site its called Login1.txt) consisting of fields just for username and password. (10 marks)
  7. The username field should be a dropdown list generated for the list of user names in the Authors table. (2)

  8. To validate the userNamesubmitthe form to another PHP script and verify that the username and password match what’s in the database. If they do not return an error page. If they do match return the page described in c. Have the server set a cookie with a duration of 0 (length of the session) indicating who the current user is. (2)
  9. Modify the suppled web page Actions.php (on the course web site its called Actions.txt) so that it generates a list of topics and users:

  10. The list of topics should be a unique list of topics generated from the Questions table and the list should contain the attribute MULTIPLE. (2)
  11. The list of questions to be edited will be the be only those questions created by the current logged in user. (2)
  12. Both lists will be generated by SQL statements.
  13. Write up the status of each part of the assignment and provide the following:
  14. Your SQL script for Q1
  15. A listing of the PHP program for Q2 and proof that your form can add a record to the database.
  16. A listing of Login1.php with your modifications and a status report.
  17. A listing of Validate.php with your modifications and a status report.
  18. A list of Actions.phpwith your modifications and a status report.

There are 25 marks on this lab. It is marked out of 30. A basic trick you need to master is writing SQL that generates a list of options. You repeat that trick in Questions 4a and 4c.