Creating a Database Model for Famous Quotes with MySQL Workbench

UFCFR3-30-1 Information Technology (2021/22) Lecture 8 Assignment discussion: working example

Domain: Famous Quotes Famous quotes (quote) organized by categories. Authors (source) must have a page and an image (public licence) available on Wikipedia. A quote has a unique id (counter) and can be in one or more categories, e.g. science & love. (category) A timestamp (ts) (when the quote was entered) is recorded. A source has a name (source) date of birth (dob), a date of death (dod) (possibly NULL they re still alive), a link to their Wikipedia page (wplink) and a link to an image (wpimg). Example: id: unique integer quote: Beauty is only skin deep, but ugly goes clean to the bone. ts: now source: Dorothy Parker dob: 1893 dod: 1967 wplink: https://en.wikipedia.org/wiki/Dorothy_Parker wpimg: https://upload.wikimedia.org/wikipedia/commons/0/04/Young_Dorothy_Parker.jpg category: philosophy,humour

First excel worksheet holding all quotes data (universal relation) Full table (9 fields) id, quote, timestamp, source, date of birth, date of death, Wikipedia link, Wikipedia image Same table (broken up to view) Same source (repetition) More than one category (many-to-many)

First excel worksheet broken up to separate worksheets (normalisation) category worksheet (table) source worksheet (table) unique keys (primary)

quote worksheet (table) category_quote worksheet (table) foreign key (to source record) primary key (unique) primary key (composite) foreign key (to category record) foreign key (to quote record)

Use MySQL workbench to create the model/database quotes (1) A little involved. XAMPP install is MariaDB which is a fork of MySQL. To avoid possible incompatibilities use the Preferences option under the Edit option in the MySQL Workbench toolbar to change the Default Target MySQL Version: to 5.17.

Use MySQL workbench to create the model/database quotes (2) Change the Schema name to the one required using the Edit Shema option:

Create (normalised) EntityRelationship model in MySQL Workbench Using MySQL Workbench many-to-many relationship are automatically resolved (join table generated) !! Note: disciplined naming convention adopted entity and field names are short, in the same case and unique across the model important for avoiding possible naming bugs in code.

Create the database - quotes Two options: 1) Forward engineer the database from MYSQL Workbench. 1a. Ensure Apache & MySQL are running (XAMPP control panel) 1c. Keep selecting default options press next - until final screen confirmation 1b. Select Forward Engineer option under Database in MySQL Workbench

Check the database - quotes Use PhpMyAdmin (http://localhost/phpMyAdmin) to validate that the database has been created.

Create the database - quotes Second option: 2) Create database & tables by hand using PhpMyAdmin. Ensure correct field names and data types, primary keys should be identified

Prepare & Import the data populate the database quotes 1) Prepare the data: Save each worksheet (but the large one) as CSV files e.g. category.csv, quote.csv, category_quote.csv and source.csv Ensure the header rows are deleted in each file. category.csv quote.csv category_quote.csv source.csv

Prepare & Import the data populate the database quotes 1) Import the data: Parent tables (i.e. those not containing foreign keys) should be imported first in this case category & source. Followed by quote. And finally category_quotes. (This will avoid any referencing errors (non-existent keys) whilst importing. Select the table, then the Import option. Choose the CSV file. Keep the default values and press the button at the bottom.

Check that data has been imported: Verify data has been correctly imported using PhpMyAdmin. category_quote table category table quote table source table

Construct HTML template page: 1 quotes-template-v1.html authored in editor: quotes-template-v1.html rendered in browser:

Construct HTML template page: 2 First refactoring: add the select box with dummy data. quotes-template-v2.html authored in editor: quotes-template-v2.html rendered in browser:

Construct PHP template page: 1 Third refactoring: connect to the database using the PDO library and test. quotes-template-v3.php: quotes-template-v3.php rendered:

Construct required query to get categories and test using PhpMyAdmin: Query in SQL window (after selecting database): SQL query result in PhpMyAdmin:

Construct PHP template page: 2 Fourth refactoring: write the <option> tags using PHP and query. quotes-template-v4.php rendered in browser: quotes-template-v4.php:

Construct PHP template page: 3 Fifth refactoring: embed the <select> in a <form> tag with a submit button. quotes-template-v5.php: category with value appears in URL after form submission since form method is set to GET Form posted to current script using PHP_SELF method set to GET quotes-template-v5.php rendered in browser: submit input

Construct required query to get fields using category id and test using PhpMyAdmin: Query in SQL window (after selecting database): SQL query result in PhpMyAdmin:

Construct PHP template page: 4 Sixth refactoring: use category value to retrieve records and display in html table. quotes-template-v6.php: Generate table only if category parameter is present in URL

Construct PHP template page: 4 Sixth refactoring: use category value to retrieve records and display in html table. quotes-template-v6.php rendered in browser:

Construct PHP template page: 5 Seventh refactoring: add table headers and prettify using CSS (Bootstrap library) (also focus back on currently selected option) quotes-template-v7.php rendered in browser:

View full script source (quotes-template.php): https://gist.github.com/pchatterjee/6cb21f5e52e2b3fca06a2c1224cb9d0c Full database dump: (Create a new DB called quotes and unzip then import the SQL file) https://fetstudy.uwe.ac.uk/~p-chatterjee/resources/sql/quotes.zip What about search? Left as an exercise. Easy enough to create a search input process using the SQL LIKE operator