Ruby

Basic Ruby Stuff

Ruby

Basic Ruby Stuff

  • Ruby generalconcepts

    Variables: variable = some_value

    Console Output: puts something

    Call method: object.method(arguments)

    Equality: object == other

    Inequality: object != other

    if condition
    # happens when true
    else
    # happens when false
    end

  • Ruby NUMbers

    Number: 0, 1, -42

    Decimals: 5.1, 8.7, -3.0

    Basic Math: a operator b

    Comparison: a operator b

    Math operators: +, -, *, /, %

    Comparison operators: >, <, >=

  • Ruby STRings

    Create: 'A string'

    Interpolation:
    "A string and #{expression}!"

    Length: string.length

    Concatenate: string + string2

    Substitute:
    string.gsub(a_string, substitute)

    Access: string[position]

  • Ruby ARrays

    Create: [contents]

    Number of elements: array.size

    Access: array[position]

    Adding an element:
    array << element

    Assigning:
    array[number] = value

    Delete at index:
    array.delete_at(i)

    Iterating:
    array.each do |x| .. end

Variable Vocabulary

Here's some vocabulary related to variables. Look at the following code:

first_name = "Jesse"
last_name  = "Farmer"
full_name  = "#{first_name} #{last_name}"

num1 = 10
num2 = 45

sum = num1 + num2

sum += 5

The following sentences are all true.

  • The single equals character = is called the assignment operator.
  • The variable first_name is assigned the value "Jesse", a String.
  • The variable last_name contains the string "Farmer"
  • full_name is a variable created by interpolating the values of first_name and last_name, separated by a space
  • num1 is equal to 10, an Integer
  • sum is equal to 55, which is the sum of the values of num1 and num2
  • The last line increments sum by 5, so sum is now 60

Rules of Variable Naming

In Ruby, variables must begin with a lower-case letter (a through z) or an underscore (_). After the first character, they can also contain upper-case letters and numbers.

That is,

num
_num
nUM
num_123

are all valid variable names. These, however, are invalid and will result in an error if you try to use them as variables:

Pants
1dog
num%^&

By convention, variables never contain upper-case letters. Breaks in words (periods, spaces, etc.) are replaced by underscores. These are all good variable names:

seconds_so_far
first_name
last_name
created_at
last_updated_at

Writing Methods

In addition to the built in methods that Ruby provides, we can also write our own methods. Whenever we have an action we want to perform like adding two numbers or parsing a string, we will define that action in a method.

The general syntax for defining a method is:

def method_name(parameter list)
  # ... one or more statements ...
  return value
end 

The keywords def and end signify the beginning and the end of the method. The method_name should be meaningful and represent what the statements in the method do. The (parameter list) is the values that are sent to the method when it is called.

If there are no parameters then the parentheses can be empty or not added at all. Inside the method there are statements which perform the action of the method and a return statement that sends a value back to the place where the method was called.

For example an add method would look like:

def add(num1, num2)
  return num1 + num2
end

When we define a method, we will either write it in a Class or a Module. If it is in a Module, then it can be called without explicitly defining an instance object to call it on by just using the name and the arguments, e.g., add(3,4).

Type the add method into the SANDBOX and then practice calling it with several different arguments. Don't forget to add a puts in front of each call so you can see the output.

Methods can also work with strings, contain calls to other methods and lots more. Consider the greeting method

def greeting(firstname, lastname)
  fullname = firstname.downcase.capitalize + " " + lastname.downcase.capitalize
  return "Hello " + fullname + ", would you like to learn to program?"
end

What happens when you call this method with different input?

  • greeting("Barack", "obama")
  • greeting("QUEEN", "Elizabeth")

Array Basics

Arrays are the most fundamental kind of collection. Think of them as an ordered list, like the months in the year or a list of today's chores. The bits and bytes on your hard drive and in your computer's memory can also be thought of as an array, laid out physically.

Every language has at least one way of representing ordered lists. The terminology might differ slightly — arrays, lists, and vectors are all common terms — but the core concept is the same. In Ruby we use the Array class to represent ordered lists.

The individual items of an array are called elements or members.

Why arrays?

Why do we need arrays? Imagine a Ruby program that asks you to enter any number of sentences, one line at a time. When you type done, it prints out the sentences from shortest to longest, regardless of what order you entered them.

How could we write a program that does that? We don't know beforehand how many sentences the user will enter or how long they will be. What if the user decided to paste in the complete works of Shakespeare one line at a time? That's a lot of long, long lines.

Without an Array we'd have to resort to something crazy like using a separate variable for each line, but of course we don't know how many variables we'd need as we're writing the program. With an Array, we just read in each line and append them successively to the end of the Array.

Array Questions

Arrays are great at answering questions like "What's the first thing on the list?", "What's the last thing on the list?", "What's the fourth thing on the list?", "How long is the list?", etc.

Arrays can also be empty, like a blank sheet of paper. "Are you empty?" is also a question an Array would be happy to answer.

Creating Arrays

Creating a new Array is easy. Type the following into the SANDBOX:

powers_of_ten = [1,10,100]
puts powers_of_ten.inspect

The variable powers_of_ten now holds the Array [1,10,100] as its value. The first element is 1, the second is 10, and the third is 100.

puts and arrays

Why did we run puts powers_of_ten.inspect instead of puts powers_of_ten? Try it:

powers_of_ten = [1,10,100]
puts powers_of_ten

You can see that rather than printing out the array, Ruby prints outs the contents of the array, one line at a time. This special behavior is hard-coded into the lowest levels of Ruby. It's inconsistent with the rest of the language, but remains there to be backwards-compatible with the oldest versions of the Ruby language.

Calling Array#inspect before printing the array will cause puts to print the actual array.

A Metaphor

If you're having trouble thinking about collections, imagine them as a storage room full of boxes and manned by a concierge. You don't have direct access to the storage room, but you can ask the concierge for information about what's in the storage room, tell him to put something new into the storage room, or tell him to take something out of the storage room.

To do that, though, you need to have some convention for referring to particular boxes. An Array is like a storage room where the boxes are ordered from left to right. Rather than asking for a box with a particular label, you ask about the first box, the second box, etc.

And of course you can place a new box at the beginning or the end of the ordered collection of boxes.

Thought Experiment

You're the concierge. You take out your sharpie and write the box number on each box, from '#1' to '#10' if there are 10 boxes. You'll need to keep these numbers up-to-date if someone ever adds or removes something from the storage unit.

Which request would be more annoying to fulfill? Someone asking you to put a new box after the last box, or put a new box before the first box?

The exact same reasoning applies to computer arrays.

Creating More Arrays

Type the following, one at a time, into the SANDBOX:

# This is an empty array
puts [].inspect

# This is an array with a single element, the string "apples"
puts ["apples"].inspect

# This is an array with three elements: integers 1, 2, and 3
puts [1,2,3].inspect

# This is an array of all the months
puts ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"].inspect

# The elements of an array don't all need to be the same type.
# In fact, that can be any type.
puts [10, "cats", 4.5, "piano"].inspect

# We can use variables in arrays, too.
dummy = "foobar"
puts [1, 2, dummy].inspect # => [1, 2, "foobar"]

Reading from Arrays

Let's take a look at that array of months from above. Paste the following into the SANDBOX.

months = ["January", "February", "March", 
          "April", "May", "June", 
          "July", "August", "September", 
          "October", "November", "December"]

(Yes, you can split up array declarations across multiple lines, so long as the line ends with a comma.)

Once we have data in an array, how do we get data out of an array? We do it by indexing the array, a fancy way of telling the array: "Give me the element at this position."

Make sure you've entered in the code that defines the months array above, then type the following into the SANDBOX.

# You "index into" an array using the bracket [...] notation.
# The index (an integer) goes between the brackets

# What's at index 0?
puts months[0]

This should print out "January".

If you see the error below it means you didn't paste in the code to define the months array. Ruby will raise an error if you try to access a variable before you define it.

undefined local variable or method `months' for main

Counting from 0

It probably strikes you as odd that "January", the first element of the months array, is at months[0] and not months[1]. If it doesn't, seek medical attention immediately: you might be a computer.

Computers and most programming languages count from 0 rather than 1. So the first element of an array is at index 0, the second is at index 1, etc. There are good reasons for this, but for now you'll just have to get used to it.

Changing Arrays

We can change the values in an array, too.

array = [1,2,3]
puts "array[2] is #{array[2]}"

array[2] = "apples"
puts "array[2] is now #{array[2]}"

Array Vocabulary

It's worth learning some vocabulary around arrays, just to get comfortable. Using the months array, again, the following statements are all true:

  • The string "January" is the first element of the array months
  • The string "January" is at index 0
  • The string "December" is the last element of the array months
  • The string "December" is at index 11
  • The array months has 12 elements
  • The value at index 3 is the string "April", which is the fourth element

Array#each

So far we've been writing code like this to loop through an Array:

names = ["Alex", "Cassie", "Stephanie"]

names.length.times do |i|
  puts names[i]
end

The variable i isn't playing much of a role here. Most of the time we don't actually care what its value is. We're only using it to get at the values we want.

In other words, what we're really saying isn't "Run this block of code as many times as there are elements in Array", but "Run this block of code once for each element in Array."

Ruby lets us express this more directly using Array#each:

names = ["Alex", "Cassie", "Stephanie"]

names.each do |name|
  puts name
end

In the same way that names.length.times do |i| ... end hands the block of code the current index of the Array and then runs it, names.each do |name| ... end hands the block of code the current element of the Array and the runs it.

We don't need that silly i at all most of the time, and using Array#each more directly expresses what we want to do (in addition to reading more like everyday English).

Examples

Let's re-write the examples from the previous lesson using Array#each instead of Integer#times.

array_sum

Here's array_sum:

def array_sum(array)
  sum = 0
  array.each do |element|
    sum += element
  end

  sum
end

puts array_sum([10, 20, 30, 100])
puts array_sum([])
puts array_sum([-1, 1])

This will print out:

160
0
0

array_reverse

Here's array_reverse:

def array_reverse(array)
  results = []

  array.each do |item|
    # Array#push appends an element to the end of the Array
    # So we append the reversed strings to the new array, 
    # one at a time

    results.push(item.reverse)
    # You could also call:
    #   results << item.reverse
  end

  results
end

puts "Some examples..."
puts array_reverse(['racecar']).inspect
puts array_reverse(['Nancy Drew', 'Frank Hardy']).inspect

my_array = ['pineapple', 'mango', 'coconut']
puts ""
puts "What if we call array_reverse twice, on itself?"
puts array_reverse(my_array).inspect
puts array_reverse(array_reverse(my_array)).inspect

add_ten_to_each

Here's add_ten_to_each:

def add_ten_to_each(array)
  new_array = []

  array.each do |element|
    new_array.push(element + 10)
  end

  new_array
end

my_array = [1,2,3,4,5]
puts "my_array is #{my_array.inspect}"

# other_array is now equal to [11, 12, 13, 14, 15]
other_array = add_ten_to_each(my_array)
puts "other_array is now #{other_array.inspect}"

What are Objects and Classes?

We've been talking a lot about objects and classes, but what are they?

Objects

Objects are bundles of data and the methods that read, write, change, or otherwise act on that data.

For example, the array [1, 2, 3] is an object. Here's some of the data stored in that object:

  1. The length of the array
  2. The individual elements of the array and their respective positions

Here are some Array methods that act on that data:

  1. Array#length returns the length of the array, e.g.,

    [1,2,3].length # => 3
    
  2. Array#first returns the first element of the array

    [1,2,3].first # => 1
    
  3. Array#reverse returns a new array in the opposite order, e.g.,

    [1,2,3].reverse # => [3,2,1]
    

An object's data and methods "travel along" with it wherever it goes.

Many Operators are Really Methods

Many operators you don't think of as methods are actually methods in disguise. Every integer is an object — an instance of Fixnum, usually — and addition (+) is actually a method on that object.

Take a look:

5 + 4  # => 9
5.+(4) # => 9

10 * 10  # => 100
10.*(10) # => 100

100 / 2  # => 50
100./(2) # => 50

array = [1,2,3]
array.[](0)  # => 1
array.[](1)  # => 2
array.[](2)  # => 3

Ruby lets us write mathematical operations like +, *, etc. as we would normally because it's more convenient for us, but behind the scenes it's calling those methods.

See what happens when we try to add two Hashes:

> {} + {}
NoMethodError: undefined method `+' for {}:Hash

The Hash class hasn't defined a + method, so Ruby yells at us when we try to call it. The Hash class is telling us, "I don't know what it means to add two hashes together."

What are classes, then?

Classes

We've seen some classes already: Fixnum, Float, String, Array, and Hash are all classes. Ruby requires that class names begin with a capital letter.

One way to think of a class is like a blueprint. The Array class contains instructions for creating particular arrays like [1,2,3] and ['apples', 'cats', dogs'].

Another way is to think of a class as a "kind" or "type." For example:

  1. [1,2,3] is a kind of Array.
  2. {:first_name => 'George', :last_name => 'Washington'} is a kind of Hash.
  3. 5 is a kind of Fixnum.
  4. "apples" is a kind of String.

Objects are instances of a class. For example, 5 is an instance of Fixnum and [1,2,3] is an instance of Array.

When we tell a class to create a new instance (object), we say we're instantiating the class.

Classes help us reason about and create rules for whole groups of objects. We can say what it means for two arrays to be concatenated or two integers to be added without referencing a particular array or integer, for example.

That is, the "rules for integer addition" are the same whether we're talking about 5 + 4 or 100 + 100000.

Similarly, we can talk about people per se, rather than a particular person. If we were building a database of famous musicians we might say that "Roger Daltrey" was an instance of the Person class.

Here's a possible Ruby program (we'll learn how to write these ourselves in the next lesson):

roger_daltrey  = Person.new("Roger Daltrey")
pete_townshend = Person.new("Pete Townshend")
john_entwistle = Person.new("John Entwistle")
keith_moon     = Person.new("Keith Moon")

the_who = Band.new("The Who")
the_who.add_member roger_daltrey 
the_who.add_member pete_townshend
the_who.add_member john_entwistle
the_who.add_member keith_moon

# I'll fight you if you disagree
best_album = Album.new("Quadrophenia")
the_who.add_album best_album

Person, Band, and Album are all classes we'd have to write ourselves, as are the add_member and add_album methods.

Object and Class Vocabulary

Using the example above, the following statements are all true:

  1. roger_daltrey, pete_townshend, etc. are instances of the Person class
  2. Person, Band, and Album are classes
  3. keith_moon is an object
  4. keith_moon is an instance of the Person class
  5. the_who is an instance of the Band class
  6. "The Who", passed to Band.new, is an instance of the String class.
  7. add_member is a method on the_who object.
  8. Quadrophenia is The Who's best album.
*Ruby // Hashes can map from anything to anything! You can map from Strings to Numbers, Strings to Strings, Numbers to Booleans... and you can mix all of those!

HTML // CSS

front-end Dev stuff

HTML // CSS

How awesome it is?

HTML 5 Cheatsheet

HTML is the standard format for web browsers.

CSS 3 Cheatsheet

veign.com

A Cascading Style-Sheet is the best way to style your html file.

If you're interested in working on this site with me or have an idea to add to it, please use the form at the bottom to reach me.

It's Peanut Butta' Jelly Time!

SQL

database = fun.

SQL

database = fun.

A Quick-Start Tutorial on Relational Database Design

Introduction

Relational database was proposed by Edgar Codd (of IBM Research) around 1969. It has since become the dominant database model for commercial applications (in comparison with other database models such as hierarchical, network and object models). Today, there are many commercial Relational Database Management System (RDBMS), such as Oracle, IBM DB2 and Microsoft SQL Server. There are also many free and open-source RDBMS, such as MySQL, mSQL (mini-SQL) and the embedded JavaDB (Apache Derby).

A relational database organizes data in tables (or relations). A table is made up of rows and columns. A row is also called a record (or tuple). A column is also called a field (or attribute). A database table is similar to a spreadsheet. However, the relationships that can be created among the tables enable a relational database to efficiently store huge amount of data, and effectively retrieve selected data.

A language called SQL (Structured Query Language) was developed to work with relational databases.

Database Design Objective

A well-designed database shall:

  • Eliminate Data Redundancy: the same piece of data shall not be stored in more than one place. This is because duplicate data not only waste storage spaces but also easily lead to inconsistencies.
  • Ensure Data Integrity and Accuracy:
  • [TODO] more

Relational Database Design Process

Database design is more art than science, as you have to make many decisions. Databases are usually customized to suit a particular application. No two customized applications are alike, and hence, no two database are alike. Guidelines (usually in terms of what not to do instead of what to do) are provided in making these design decision, but the choices ultimately rest on the you - the designer.

Step 1: Define the Purpose of the Database (Requirement Analysis)

Gather the requirements and define the objective of your database, e.g. ...

Drafting out the sample input forms, queries and reports, often helps.

Step 2: Gather Data, Organize in tables and Specify the Primary Keys

Once you have decided on the purpose of the database, gather the data that are needed to be stored in the database. Divide the data into subject-based tables.

Choose one column (or a few columns) as the so-called primary key, which uniquely identify the each of the rows.

Primary Key

In the relational model, a table cannot contain duplicate rows, because that would create ambiguities in retrieval. To ensure uniqueness, each table should have a column (or a set of columns), called primary key, that uniquely identifies every records of the table. For example, an unique number customerID can be used as the primary key for the Customers table; productCode for Products table; isbn for Books table. A primary key is called a simple key if it is a single column; it is called a composite key if it is made up of several columns.

Most RDBMSs build an index on the primary key to facilitate fast search and retrieval.

The primary key is also used to reference other tables (to be elaborated later).

You have to decide which column(s) is to be used for primary key. The decision may not be straight forward but the primary key shall have these properties:

  • The values of primary key shall be unique (i.e., no duplicate value). For example, customerName may not be appropriate to be used as the primary key for the Customers table, as there could be two customers with the same name.
  • The primary key shall always have a value. In other words, it shall not contain NULL.

Consider the followings in choose the primary key:

  • The primary key shall be simple and familiar, e.g., employeeID for employees table and isbn for books table.
  • The value of the primary key should not change. Primary key is used to reference other tables. If you change its value, you have to change all its references; otherwise, the references will be lost. For example, phoneNumber may not be appropriate to be used as primary key for table Customers, because it might change.
  • Primary key often uses integer (or number) type. But it could also be other types, such as texts. However, it is best to use numeric column as primary key for efficiency.
  • Primary key could take an arbitrary number. Most RDBMSs support so-called auto-increment (or AutoNumber type) for integer primary key, where (current maximum value + 1) is assigned to the new record. This arbitrary number is fact-less, as it contains no factual information. Unlike factual information such as phone number, fact-less number is ideal for primary key, as it does not change.
  • Primary key is usually a single column (e.g., customerID or productCode). But it could also make up of several columns. You should use as few columns as possible.

Let's illustrate with an example: a table customers contains columns lastName, firstName, phoneNumber, address, city, state, zipCode. The candidates for primary key are name=(lastName, firstName), phoneNumber, Address1=(address, city, state), Address1=(address, zipCode). Name may not be unique. Phone number and address may change. Hence, it is better to create a fact-less auto-increment number, says customerID, as the primary key.

Step 3: Create Relationships among Tables

A database consisting of independent and unrelated tables serves little purpose (you may consider to use a spreadsheet instead). The power of relational database lies in the relationship that can be defined between tables. The most crucial aspect in designing a relational database is to identify the relationships among tables. The types of relationship include:

  1. one-to-many
  2. many-to-many
  3. one-to-one
One-to-Many

In a "class roster" database, a teacher may teach zero or more classes, while a class is taught by one (and only one) teacher. In a "company" database, a manager manages zero or more employees, while an employee is managed by one (and only one) manager. In a "product sales" database, a customer may place many orders; while an order is placed by one particular customer. This kind of relationship is known as one-to-many.

One-to-many relationship cannot be represented in a single table. For example, in a "class roster" database, we may begin with a table called Teachers, which stores information about teachers (such as name, office, phone and email). To store the classes taught by each teacher, we could create columns class1, class2, class3, but faces a problem immediately on how many columns to create. On the other hand, if we begin with a table called Classes, which stores information about a class (courseCode, dayOfWeek, timeStart and timeEnd); we could create additional columns to store information about the (one) teacher (such as name, office, phone and email). However, since a teacher may teach many classes, its data would be duplicated in many rows in table Classes.

To support a one-to-many relationship, we need to design two tables: a table Classes to store information about the classes with classID as the primary key; and a table Teachers to store information about teachers with teacherID as the primary key. We can then create the one-to-many relationship by storing the primary key of the table Teacher (i.e., teacherID) (the "one"-end or the parent table) in the table classes (the "many"-end or the child table), as illustrated below.


The column teacherID in the child table Classes is known as the foreign key. A foreign key of a child table is a primary key of a parent table, used to reference the parent table.

Take note that for every value in the parent table, there could be zero, one, or more rows in the child table. For every value in the child table, there is one and only one row in the parent table.

Many-to-Many

In a "product sales" database, a customer's order may contain one or more products; and a product can appear in many orders. In a "bookstore" database, a book is written by one or more authors; while an author may write zero or more books. This kind of relationship is known as many-to-many.

Let's illustrate with a "product sales" database. We begin with two tables: Products and Orders. The table products contains information about the products (such as name, description and quantityInStock) with productID as its primary key. The table orders contains customer's orders (customerID, dateOrdered, dateRequired and status). Again, we cannot store the items ordered inside the Orders table, as we do not know how many columns to reserve for the items. We also cannot store the order information in the Products table.

To support many-to-many relationship, we need to create a third table (known as a junction table), says OrderDetails (or OrderLines), where each row represents an item of a particular order. For the OrderDetails table, the primary key consists of two columns: orderID and productID, that uniquely identify each row. The columns orderID and productID in OrderDetails table are used to reference Orders and Products tables, hence, they are also the foreign keys in the OrderDetails table.


The many-to-many relationship is, in fact, implemented as two one-to-many relationships, with the introduction of the junction table.

  1. An order has many items in OrderDetails. An OrderDetails item belongs to one particular order.
  2. A product may appears in many OrderDetails. Each OrderDetails item specified one product.
One-to-One

In a "product sales" database, a product may have optional supplementary information such as image, moreDescription and comment. Keeping them inside the Products table results in many empty spaces (in those records without these optional data). Furthermore, these large data may degrade the performance of the database.

Instead, we can create another table (says ProductDetails, ProductLines or ProductExtras) to store the optional data. A record will only be created for those products with optional data. The two tables, Products and ProductDetails, exhibit a one-to-one relationship. That is, for every row in the parent table, there is at most one row (possibly zero) in the child table. The same column productID should be used as the primary key for both tables.

Some databases limit the number of columns that can be created inside a table. You could use a one-to-one relationship to split the data into two tables. One-to-one relationship is also useful for storing certain sensitive data in a secure table, while the non-sensitive ones in the main table.


Column Data Types

You need to choose an appropriate data type for each column. Commonly data types include: integers, floating-point numbers, string (or text), date/time, binary, collection (such as enumeration and set).

Step 4: Refine & Normalize the Design

For example,

  • adding more columns,
  • create a new table for optional data using one-to-one relationship,
  • split a large table into two smaller tables,
  • others.
Normalization

Apply the so-called normalization rules to check whether your database is structurally correct and optimal.

First Normal Form (1NF): A table is 1NF if every cell contains a single value, not a list of values. This properties is known as atomic. 1NF also prohibits repeating group of columns such as item1, item2,.., itemN. Instead, you should create another table using one-to-many relationship.

Second Normal Form (2NF): A table is 2NF, if it is 1NF and every non-key column is fully dependent on the primary key. Furthermore, if the primary key is made up of several columns, every non-key column shall depend on the entire set and not part of it.

For example, the primary key of the OrderDetails table comprising orderID and productID. If unitPrice is dependent only on productID, it shall not be kept in the OrderDetails table (but in the Products table). On the other hand, if the unitPrice is dependent on the product as well as the particular order, then it shall be kept in the OrderDetails table.

Third Normal Form (3NF): A table is 3NF, if it is 2NF and the non-key columns are independent of each others. In other words, the non-key columns are dependent on primary key, only on the primary key and nothing else. For example, suppose that we have a Products table with columns productID (primary key), name and unitPrice. The column discountRate shall not belong to Products table if it is also dependent on the unitPrice, which is not part of the primary key.

Higher Normal Form: 3NF has its inadequacies, which leads to higher Normal form, such as Boyce/Codd Normal form, Fourth Normal Form (4NF) and Fifth Normal Form (5NF), which is beyond the scope of this tutorial.

At times, you may decide to break some of the normalization rules, for performance reason (e.g., create a column called totalPrice in Orders table which can be derived from the orderDetails records); or because the end-user requested for it. Make sure that you fully aware of it, develop programming logic to handle it, and properly document the decision.

Integrity Rules

You should also apply the integrity rules to check the integrity of your design:

Entity Integrity Rule: The primary key cannot contain NULL. Otherwise, it cannot uniquely identify the row. For composite key made up of several columns, none of the column can contain NULL. Most of the RDBMS check and enforce this rule.

Referential Integrity Rule: Each foreign key value must be matched to a primary key value in the table referenced (or parent table).

  • You can insert a row with a foreign key in the child table only if the value exists in the parent table.
  • If the value of the key changes in the parent table (e.g., the row updated or deleted), all rows with this foreign key in the child table(s) must be handled accordingly. You could either (a) disallow the changes; (b) cascade the change (or delete the records) in the child tables accordingly; (c) set the key value in the child tables to NULL.

Most RDBMS can be setup to perform the check and ensure the referential integrity, in the specified manner.

Business logic Integrity: Beside the above two general integrity rules, there could be integrity (validation) pertaining to the business logic, e.g., zip code shall be 5-digit within a certain ranges, delivery date and time shall fall in the business hours; quantity ordered shall be equal or less than quantity in stock, etc. These could be carried out in validation rule (for the specific column) or programming logic.

Column Indexing

You could create index on selected column(s) to facilitate data searching and retrieval. An index is a structured file that speeds up data access for SELECT, but may slow down INSERT, UPDATE, and DELETE. Without an index structure, to process a SELECT query with a matching criterion (e.g., SELECT * FROM Customers WHERE name='Tan Ah Teck'), the database engine needs to compare every records in the table. A specialized index (e.g., in BTREE structure) could reach the record without comparing every records. However, the index needs to be rebuilt whenever a record is changed, which results in overhead associated with using indexes.

Index can be defined on a single column, a set of columns (called concatenated index), or part of a column (e.g., first 10 characters of a VARCHAR(100)) (called partial index) . You could built more than one index in a table. For example, if you often search for a customer using either customerName or phoneNumber, you could speed up the search by building an index on column customerName, as well as phoneNumber. Most RDBMS builds index on the primary key automatically.

 

REFERENCES & RESOURCES

Contact

fill up the inbox, i like that

Contact

fill up the inbox, i like that

devCHEATs.com
Cookeville, TN

general: jbwilmoth@gmail.com

Pair program with me!