A2 Computing Projects Database Theory / Prototypes

1. A2 Computing ProjectsDatabase Theory / Prototypes

2. Including a DatabaseWhat You Need Simple? Including a database in your project will help you identify a specific level of complexity. The more of the A2 syllabus that you use in your data processing, the easier it is to meet the highest level of complexity. The following slides will show you how to create a proof of concept that will meet the various levels of complexity. Creating an ERD (entity relationship diagram) should help you identify your level of complexity. people inventions Many to Many One to Many One to One

3. Including a Database0NF Simple It’s not enough to just include a database. By including a flat file or a number of unrelated tables, or tables that have a many to many relationship, you remain at limited complexity. In this example, we have included a table called people and their inventions. One person can have many inventions, but more than one person can work on an invention. Creating an ERD (entity relationship diagram) should help you identify your level of complexity. people inventions This Database can be said to be in 0NF Or O Normal Form

4. Rules of 1NF Limited All records have a Primary Key There is no duplication of data fields Data is atomic Each field has a unique name Atomic data cannot be divided any more. Eg. My full name is broken down into Title, FirstName, MiddleName & Surname fields

5. Including a Database1NF Limited In 1NF a database will consist of a number of tables which are related and each will have a primary key. Your SQL queries will be basic, such as SELECT statements to bring data to the screen & some INSERT statements to add new data. Creating an ERD (entity relationship diagram) should help you identify your level of complexity. subject question users This Database can be said to be in 1NF Or 1st Normal Form

6. Rules of 2NF Adequate The database is already in 1NF AND All Data depends on the Primary key A simple way to remember this is that the data for that table must be relevant only to that table. Eg. A table called student which held course details wouldn’t be in 2NF

7. Including a Database2NF Adequate Creating an ERD (entity relationship diagram) should help you identify your level of complexity. In 2NF a database will consist of a number of tables which are related and each will have a primary key. All data will be specifically related to the table that holds it. Your SQL queries will be dynamic, allowing users to select, insert & update data based upon their user input (eg. Completing a form) Your database will have the potential to store significant volumes of data. subject test users question This Database can be said to be in 2NF Or 2nd Normal Form

8. Rules of 3NF Complex The database is already in 2NF AND All Data links are fully dependant on the keys Data should not be duplicated across the database, instead it is linked via primary & foreign keys. To avoid many to many relationships, a database in 3NF will employ ‘link tables’ to create effective relationships between the records.

9. Including a Database3NF Complex Creating an ERD (entity relationship diagram) should help you identify your level of complexity. In 3NF a database will consist of a number of tables which are related purely through the use of unique keys (both foreign & primary). No data will be duplicated and there will be no m-2-m relationships Your SQL queries will be dynamic, allowing users to select, insert, update & delete data based upon their user input (eg. Completing a form). Queries may include data from several tables. Your database may be hosted remotely or make use of connection strings. Data extracted from the database may be used in your program to calculate outputs. subject test class question This Database can be said to be in 3NF Or 3rd Normal Form student

10. Viewing Your Hosted Pages Limited Each of you has been set up with a hosted web account where you may upload your project web files, images for documentation and database. To access your website home page, navigate to www.sgscomputing.net/Firstname_Surname You will find a single PHP page in your directory with a simple welcome message.

11. Setting Up FTP Complex In order to upload your files to your hosting site, you will need to set up an FTP client. There are a number of free programs available. In the examples, we will be using FileZilla. FTP sets up a connection between your local files & your host account which enables you to drag & drop new files to your desired location.

12. Understanding Your FTP Client Complex Your login details System / Upload messages Your local directories Your hosted directories Your local files Your hosted files

13. Understanding Your FTP Client Complex Host: sgscomputing.net Username: Firstname_Surname Password: <check your emails!> Click ‘Quick Connect’ to connect Navigate to the folder where you have saved your project files Drag your required file(s) from the local to the hosted screen

14. Connecting To YourHosted Database Complex In order to edit and maintain your hosted database, you will need to access a MySQL client. There are a number of free programs available. In the examples and in class, we will be using PHP MyAdmin. A hosted database means that all of your data is stored and backed up online. For this particular database, remote connections are not allowed. However, there are some hosted databases which will allow you to connect them to a local copy of your files.

15. Connecting To Your Remote Database Complex Your database has been created with your name on a hosted account. This is in a shared host alongside other students. You may only access your own database. You may not access another person’s database. Any student found to be accessing another person’s database will be removed from the system and required to use their own hosting. To connect to your database, you will need the department login: Username: sgscomputingnet Password: SGScomputing2014! https://mysqladmin.ipage.com/mysqladmin/index.php

16. Creating Tables in YourRemote Database Adequate Click on your database (your name) Create your first table by giving it a name, and the number of fields required, then click ‘Go’

17. Creating Tables in Your Remote Database Adequate Add in your Field names, data types and lengths. Don’t forget to include your Primary Key! To identify a Primary Key, check this box and use the ‘Extra’ drop down box to change your integer to an Auto Increment.

18. Connecting To Your Remote Database (PHP) Complex If you are creating a web based solution, you will need to create a PHP script which connects to your remote database. To do this, you will need a page which holds a connection string. To connect to your database, you will need to adjust the username, password & database name to your own.

19. Creating Your Local Database (Pascal - SQLite) Adequate To create a local Database for your Pascal Application, download the SQLite binary and SQLite DLL files to your P Drive. You will use the SQLite application to create your database structure via the command line interface. Using the .open command will create the database if it doesn’t already exist. To check that your database has been created, use the .databases command

20. Setting Up Tables in YourLocal Database (Pascal - SQLite) Adequate To create your database, you will need to understand some basic DDL (data description language). These are the commands used to define the database structure, aka schema. Try entering the following statement into the SQLite application to create a table. Database Data Types: integer varchar (length) char (length) real text

21. Setting Up Tables in YourLocal Database (Pascal - SQLite) Adequate Once you have created a table, it is useful to add some data into it for testing purposes. Although you will be adding data from inside your program later, this is a good chance to practice forming DML (data modelling language) statements. DML is the statements used to manipulate the data in your database. Try adding some data to your database table by using this INSERT statement.

22. Setting Up Tables in YourLocal Database (Pascal - SQLite) Limited Now you have a database, you need to connect it to an application. Create a new Application in Lazarus and save this into a folder called ‘PoC’. Add a SQLite Connection and a button to the form. Rename your connection: dbConnection

23. Setting Up Tables in YourLocal Database (Pascal - SQLite) Limited In order for the objects you have added to connect to the database, you need to add some code to the FormCreate procedure. This is the first thing that is run when the application starts. This line tells the dbConnection object to open a connection to the database.

24. Connecting to Tables in YourLocal Database (Pascal - SQLite) Limited To create a full proof of concept, set up a form with the following objects: SQLite connection TLabel Datagrid SQL Query Object SQL Transaction Navigator Pane SQL Datasource Buttons

25. Connecting to Tables in YourLocal Database (Pascal - SQLite) |Limited Double click on the form and set up your database connection & first SQL Query: ExtractFilePath is a neat little function which will set your database path to the same directory as your application.

26. Connecting to Tables in YourLocal Database (Pascal - SQLite) Limited Double click on the Commitinator button and add the following code: The purpose of the Transaction is to actually run the queries that you are creating. Next, double click on the Make it so… button and add the following code:

27. Connecting to Tables in YourLocal Database (Pascal - SQLite) Limited The two buttons both refresh the database query: Commitinator updates the data, whilst Make it so… just resets any changes you made. Test your application by changing item 1 to Barney Rubble, and adding a new item with Fred Flintstone.

28. Connecting to Tables in YourLocal Database (Pascal - SQLite) Adequate To increase the complexity of your data processing application, you will need to dynamically assign the parameters to your SQL queries. To do this, you will need to add three DBedit boxes (not standard edit boxes). The difference between an Edit box and a DBEdit is that you can connect your Database fields to each of the Edit Boxes.

29. Connecting to Tables in YourLocal Database (Pascal - SQLite) Adequate To show the data in the edit boxes, you will need to assign the DataSource to each field and then explicitly tell the program which field to assign to each edit box. To do this, add the following code to the end of the FormCreate procedure: If your database fields are different, you will need to adjust these. These fields relate directly to the SELECT query at the start of the procedure.

30. Connecting to Tables in YourLocal Database (Pascal - SQLite) Adequate Test your dynamic code by scrolling through the records using the Navigator arrows. Try changing some of the data and committing your changes – what happens to the data grid? If you add a new record to the dataset, you can now type in the details into the Dbedit boxes.

31. Connecting to Tables in YourLocal Database (Pascal - SQLite) Complex To further increase the complexity of the project, we should make our SQL statements include data from multiple tables. To do this, we will need to create a second table within the 3NF rules. Note that this time, rather than having a key that we need to type in, auto_increment has been used. This will automatically create a numeric ID for each record.

32. Connecting to Tables in YourLocal Database (Pascal - SQLite) Complex Once the second table has been created, we can add some data to it. The tables are linked through the use of the foreign key ‘inventor’ which holds the primary key from the people table. Looking at your original data, who do you think invented the shrinkinator? Clue: who’s id is 2?

33. Connecting to Tables in YourLocal Database (Pascal - SQLite) Complex To link tables in an SQL statement, we use a JOIN. In the SQLQuery code, update your SELECT statement to bring back a list of all people and their linked invention. When writing longer SQL statements, it is often easier to write these in a string variable, then pass them by reference when setting up the Query object.

34. Connecting to Tables in YourLocal Database (Pascal - SQLite) Complex The end result of this is to have a datagrid which shows data from both the people and the inventions table.