A Level Computer Science Projects Database Theory Prototypes

A Level Computer Science Projects Database Theory / Prototypes Teach. All. About. IT. uk

Including a Database What You Need Creating an ERD (entity relationship diagram) should help you identify your level of complexity. people Many to Many One to Many inventions Including a database in your project will help you identify a specific level of complexity. The more of the A 2 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. One to One Teach. All. About. IT.

Including a Database 0 NF Creating an ERD (entity relationship diagram) should help you identify your level of complexity. people This Database can be said to be in 0 NF Or O Normal Form inventions 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. Teach. All. About. IT.

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

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

Rules of 2 NF The database is already in 1 NF 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 2 NF Teach. All. About. IT.

Including a Database 2 NF Creating an ERD (entity relationship diagram) should help you identify your level of complexity. subject users This Database can be said to be in 2 NF Or 2 nd Normal Form test question In 2 NF 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. Teach. All. About. IT.

Rules of 3 NF The database is already in 2 NF 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 3 NF will employ ‘link tables’ to create effective relationships between the records. Teach. All. About. IT.

Including a Database 3 NF Creating an ERD (entity relationship diagram) should help you identify your level of complexity. subject class student test question This Database can be said to be in 3 NF Or 3 rd Normal Form In 3 NF 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. Teach. All. About. IT.

Creating Your Local DB (Pascal - SQLite) 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 Teach. All. About. IT.

Setting Up Tables in Your Local DB (Pascal - SQLite) 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) real text Teach. All. About. IT.

Setting Up Tables in Your Local DB (Pascal - SQLite) 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. Teach. All. About. IT.

Setting Up Tables in Your Local DB (Pascal - SQLite) 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 ‘Po. C’. Add a SQLite Connection and a button to the form. Rename your connection: db. Connection Teach. All. About. IT.

Setting Up Tables in Your Local DB (Pascal - SQLite) In order for the objects you have added to connect to the database, you need to add some code to the Form. Create procedure. This is the first thing that is run when the application starts. This line tells the db. Connection object to open a connection to the database. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) To create a full proof of concept, set up a form with the following objects: SQLite connection TLabel SQL Query Object Datagrid SQL Transaction Navigator Pane Buttons SQL Datasource Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) Double click on the form and set up your database connection & first SQL Query: Extract. File. Path is a neat little function which will set your database path to the same directory as your application. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) 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: Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) 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. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) 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. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) To show the data in the edit boxes, you will need to assign the Data. Source 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 Form. Create 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. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) 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. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) 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 3 NF 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. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) 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? Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) 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. Teach. All. About. IT.

Connecting to Tables in Your Local DB (Pascal - SQLite) The end result of this is to have a datagrid which shows data from both the people and the inventions table. Teach. All. About. IT.