Web Technology 2: Module 5

Note: Module 4 is missing because I missed class when I was in Sydney. I’ll track down notes from that class and maybe post them later.

Today’s class is on MySQL. SQL stands for Structured Query Language. it lets you access and manipulate databases. MySQL is a “flavor” of SQL. RDBMS stands for Relational Database Management System. it’s the basis of SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. It’s stored in database objects called tables, which has columns and rows, with data. How do we talk to the database? SQL statements talk to them. They’re not case sensitive. Most common statement is the SELECT * from TABLE_NAME. You can have more than one statements at the same time. Semicolons separate them. Asterisk is a good way to search for anything and everything. Results are stored in a result table called a result set. You don’t see a physical table, it’s just like a temporary table where your data is held and you can interact with it.

MySQL is the most popular open source database application. It comes with a database server, different client applications, and several utilities. The server stores the actual data. The client applications used for interacting with the server.

The purpose the web site or application dictates how the database should be designed. When creating databases and tables come up with names (identifiers) that are clear, meaningful, easy to type and easy to remember. Each row in a table is called a record. When naming your tables, only use letters, numbers, and underscores. They shouldn’t be longer than 64 characters, and they must be unique. The column name is local to its table. It’s a good idea, if you can, to keep all column names unique, but easy to remember.

Determine the database’s name (must be unique), the table’s name, and the column names. EAch column needs a data-type. There are three primary types: text (strings), numbers, and dates/times. Within each of the main types are variants. Choosing a column type impacts performance, so it’s good to figure out what type of data you want to use. It is a good idea to include length attributes, to help performance, but be careful to not be too short on the lengths because it can cut off data. Columns can have an empty space. By default, the column will say it’s “null” (no value). You can set it to be NOT NULL (forces the field to have data).

Number types can be marked as UNSIGNED, which forces a column to be a positive number. You can also use ZEROFILL, forcing a column to be padded with zeros. You also need to assign a AUTO_INCREMENT to your primary key. An index is a database’s way of keeping tabs on the data in the records. A key keeps a table normalized, meaning that the data is separated in to unique items. Primary key: an artificial way to refer to a record. Foreign key is usually a linked primary key in another table.

Identify each column type. Choose a subtype for each column. Set the maximum length for text columns. Identify your primary key. Identify which columns cannot have NULL values. Make any numeric types UNSIGNED if not using negative numbers. Establish any default column values. We’ll be using phpMyAdmin (one of the most popular software applications). It’s main use is to connect to a MySQL server. Must be accessed through a web browser.

Class giving it a shot… Homework for next week is to create a table to get the data from the user registration form from the homework last week (which I’m behind on). Basically, matching the fields to the data in the table. In a document (word, or whatever), draw a map of how that data will relate to the fields in the registration form. Finish comps and HTML/CSS for our web apps.