A2 Computing Projects Database Theory / Prototypes

of 34

Please download to get full document.

View again

All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
PDF
34 pages
0 downs
3 views
Share
Description
A2 Computing ProjectsDatabase Theory / PrototypesIncluding a DatabaseWhat You NeedSimple?Including a database in your project will help you identify a specific level of…
Transcript
A2 Computing ProjectsDatabase Theory / PrototypesIncluding a DatabaseWhat You NeedSimple?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.peopleinventionsMany to ManyOne to ManyOne to OneIncluding a Database0NFSimpleIt’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.peopleinventionsThis Database can be said to be in 0NFOr O Normal FormRules of 1NFLimitedAll records have a Primary KeyThere is no duplication of data fieldsData is atomicEach field has a unique nameAtomic data cannot be divided any more. Eg. My full name is broken down into Title, FirstName, MiddleName & Surname fieldsIncluding a Database1NFLimitedIn 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.subjectquestionusersThis Database can be said to be in 1NFOr 1st Normal FormRules of 2NFAdequateThe database is already in 1NFANDAll Data depends on the Primary keyA 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 2NFIncluding a Database2NFAdequateCreating 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.subjecttestusersquestionThis Database can be said to be in 2NFOr 2nd Normal FormRules of 3NFComplexThe database is already in 2NFANDAll Data links are fully dependant on the keysData 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.Including a Database3NFComplexCreating 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 relationshipsYour 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.subjecttestclassquestionThis Database can be said to be in 3NFOr 3rd Normal FormstudentViewing Your Hosted PagesLimitedEach 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_SurnameYou will find a single PHP page in your directory with a simple welcome message.Setting Up FTPComplex 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.Understanding Your FTP ClientComplexYour login details System / Upload messagesYour local directoriesYour hosted directoriesYour local filesYour hosted filesUnderstanding Your FTP ClientComplexHost: sgscomputing.netUsername: Firstname_SurnamePassword: <check your emails!>Click ‘Quick Connect’ to connectNavigate to the folder where you have saved your project filesDrag your required file(s) from the local to the hosted screen Connecting To YourHosted DatabaseComplex 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.Connecting To Your Remote DatabaseComplexYour 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: sgscomputingnetPassword: SGScomputing2014!https://mysqladmin.ipage.com/mysqladmin/index.phpCreating Tables in YourRemote DatabaseAdequateClick on your database (your name)Create your first table by giving it a name, and the number of fields required, then click ‘Go’Creating Tables in Your Remote DatabaseAdequateAdd 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.Connecting To Your Remote Database (PHP)ComplexIf 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.Creating Your Local Database (Pascal - SQLite)AdequateTo 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 commandSetting Up Tables in YourLocal Database (Pascal - SQLite)AdequateTo 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:integervarchar (length)char (length)real text Setting Up Tables in YourLocal Database (Pascal - SQLite)AdequateOnce 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.Setting Up Tables in YourLocal Database (Pascal - SQLite)LimitedNow 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: dbConnectionSetting Up Tables in YourLocal Database (Pascal - SQLite)LimitedIn 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)LimitedTo create a full proof of concept, set up a form with the following objects:SQLite connectionTLabelDatagridSQL Query ObjectSQL TransactionNavigator PaneSQL DatasourceButtonsConnecting to Tables in YourLocal Database (Pascal - SQLite)|LimitedDouble 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)LimitedDouble 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:Connecting to Tables in YourLocal Database (Pascal - SQLite)LimitedThe 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)AdequateTo 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)AdequateTo 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)AdequateTest 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)ComplexTo 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)ComplexOnce 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?Connecting to Tables in YourLocal Database (Pascal - SQLite)ComplexTo 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.Connecting to Tables in YourLocal Database (Pascal - SQLite)ComplexThe end result of this is to have a datagrid which shows data from both the people and the inventions table.
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks