Jump to content

Server-Side Scripting/SQL Databases

Add links
From Wikiversity
Server-Side Scripting
  1. Introduction
  2. Routes and Templates
  3. Requests
  4. Iteration
  5. Strings and Files
  6. Arrays
  7. Associative Arrays
  8. Internet Data
  9. SQL Databases
  10. Document Databases
  11. Key-Value Databases
  12. Cookies and Sessions
  13. REST API
  14. Testing

This lesson introduces SQL-based database processing.

Objectives and Skills

[edit | edit source]

Objectives and skills for this lesson include:

  • Understand relational database concepts
  • Understand SQL data manipulation language
  • Use an SQL database with server-side scripts

Readings

[edit | edit source]
  1. Wikipedia: Database
  2. Wikipedia: Relational database
  3. Wikipedia: SQL
  4. Wikipedia: SQL injection

Multimedia

[edit | edit source]

Additional items may be contributed by course participants

Tutorials

[edit | edit source]

Before trying to work with any database using a programming language, you should first be familiar with that database's command language. The following tutorials will be helpful in understanding how to use MySQL and/or SQLite.

MySQL

[edit | edit source]

SQLite

[edit | edit source]

Examples

[edit | edit source]

SQLite

[edit | edit source]

The following examples use SQLite as the SQL database. A similar approach would work for almost any SQL-based database. SQLite is used because it requires no server configuration or maintenance. Performance would be adequate for small-scale projects. Larger projects should use a server-based database such as MySQL.

MySQL

[edit | edit source]

The following examples use MySQL as the SQL database. Note that MySQL databases require maintenance. Review MySQL maintenance best practices before using MySQL in a production environment.

Activities

[edit | edit source]

Complete the following activities using HTML, CSS, and a server-side scripting language. Apply best practices for user interface design and your selected scripting language, including modules, comments, indentations, naming conventions, and constants. Use HTML forms and input elements for input, server-side scripts for processing, and HTML elements for output. Use separate functions for each type of processing. Avoid global variables by passing parameters and returning results. Add comments at the top of the code modules and include references to any resources used. Add the completed code to your website as /lesson9.

  1. Create an application that allows the user to insert, update, and delete records in an SQL-based database. After each action, display the current records. Use any SQL database and data structure you like. Be sure to include date, text, and number field types in the data structure. If in doubt, any of the previous cyclone, earthquake, wildfire, or tsunami activities may be used as data examples.

Lesson Summary

[edit | edit source]

Additional items may be contributed by course participants

  • A Database is a structured set of data held in a computer, especially one that is accessible in various ways.[1]
  • SQL commands are instructions used to communicate with a database to perform tasks, functions, and queries with data.[2]
  • SQLite is an open-source, zero-configuration, self-contained, stand-alone, transaction relational database engine designed to be embedded into an application.[3]
  • SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.[4]
  • SQLite has no standalone processes. It is linked in and becomes an integral part of an application. The application program uses SQLite’s functionality through simple function calls.[5]
  • SQLite stores the entire database as a single file on a host machine.[6]
  • SQLite is a widely used database engine. It can be found in Android and iPhone devices, used with Skype and iTunes, and several other popular applications.[7]

Key Terms

[edit | edit source]

Additional items may be contributed by course participants

CRUD (Create, Read Update, and Delete)
  • The four basic functions a data application performs:[8]
    • Create - Insert a new record into the database[9]
    • Read - Select a record and display it to the user[10]
    • Update - Update or brings a change to a record in the database[11]
    • Delete - Remove a record from the database[12]
database query
A request to access data from a database to manipulate it or retrieve it.[13]
normalization
The process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.[14]
NoSQL (No Structured Query Langauge)
Term used for non-relational databases, an example being MongoDB.[15]
Structured Query Language (SQL)
Language used to communicate with a database.[16]

See Also

[edit | edit source]

References

[edit | edit source]
  1. ↑ "Database". Wikipedia. 2021-02-26. https://en.wikipedia.org/w/index.php?title=Database&oldid=1009139147. 
  2. ↑ SQL Commands
  3. ↑ "SQLite Tutorial - An Easy Way to Master SQLite Fast". SQLite Tutorial. Retrieved 2021-03-18.
  4. ↑ https://www.sqlservertutorial.net/getting-started/what-is-sql-server/
  5. ↑ Wikipedia: SLQite
  6. ↑ Wikipedia: SLQite
  7. ↑ SQLite: Most Widely Deployed and Used Database Engine
  8. ↑ https://www.codecademy.com/articles/what-is-crud
  9. ↑ https://www.sqlshack.com/crud-operations-in-sql-server/
  10. ↑ https://www.sqlshack.com/crud-operations-in-sql-server/
  11. ↑ https://www.sqlshack.com/crud-operations-in-sql-server/
  12. ↑ https://www.sqlshack.com/crud-operations-in-sql-server/
  13. ↑ https://www.educative.io/blog/what-is-database-query-sql-nosql
  14. ↑ https://www.studytonight.com/dbms/database-normalization.php
  15. ↑ https://www.mongodb.com/nosql-explained
  16. ↑ http://www.sqlcourse.com/intro.html
Retrieved from "https://en.wikiversity.org/w/index.php?title=Server-Side_Scripting/SQL_Databases&oldid=2383428"