Category Archives: Information Technology

Information Technology, programming, website design, etc. Javascript, HTML, CSS, or whatever.

Confusing things about the Samsung Galaxy S5

My new phone is great but it had some confusing features it took a while to figure out (with help).

1) The weather app that comes with the phone was only updating when I clicked it. It would show the previous day’s temp but the current time. What’s up with that? How do I get it to automatically update like my old phone (HTC One X).
Solution: Click on the app to go to the main weather screen. Choose the menu in the upper right, choose settings, and set the auto-update as desired (not “none”). Not that bad. I think the default should be something other than “none” but perhaps starting at none forces you to realize there IS a setting and adjust it accordingly.

2) So, how do you turn the data off? You choose setting and you see the place where you can turn wifi on and off, bluetooth, sound, display, etc. – but no data. You would think that would be a common request but it’s actually well hidden. It is easier to see where you can request using MORE mobile data any time your wifi cuts out – in order to keep the video or song playing – whatever you are doing. That is the opposite of what I want when I am on the treadmill. I can live without any video but I can’t be using up my valuable mobile data megs on non-vital things.
Solution: The mobile data setting is actually under “more networks” / “mobile networks”. There is a mobile data checkbox there you can toggle.

3) I got all my emails set up on their email app but the Gmail was no there. It was set up on a separate GMail app already. How do I combine them together in one interface?
Solution: I have not found a way to do this yet and have given up. I just check both sources of mail and live with it. let me know if you have any ideas to try.

4) The email app has some setting (under manage accounts) mentioning a sync schedule and a sync period without any explanation of what those mean and how they interact. All the online tutorials are silent on the meaning. They just say “and this is where you choose your sync schedule” or “sync period” and give no explanation. Am I the dumb one here? Is it so obvious what the difference between schedule and period is?
Solution: It turns out a period in this context means the amount of time the email app will keep emails for. It will keep no email older than X length of time. The schedule is how often to contact the mail server and check for new emails. It’s just that simple. It’s nice that you can set those time frames different for each email address. Pretty cool.

5) One of the “killer apps” of the S5 is it has a heart rate monitor. Oh yeah? How do you use it?
Solution: IT appears there is only one way to use it (so far) and that is the health app that comes with it. You click the health app, choose heart rate, and stick your finger on the spot where the camera flash is located. There are three “devices” in that port: one is the flash, one is the distance measurement for the camera, and one is the heart rate sensor. It appears to be sufficiently accurate (plus or minus a couple). I got running and then measured my heart rate and it counted 166 beats per minute. I tried it multiple times and it was repeatable. Supposedly my maximum heart rate would be 220 minus my age: or a total of 175 for me. It did not seem possible to get a rating higher than 166 so I don’t know if the sensor is off a bit or if the formula is approximate or if I have a body older than my age would indicate. :-) As I recall from my heart test 2 years ago, my maximum heart rate was right where it was supposed to me according to the formula. That was with a better sensor though.

Password advice

Taken from article by Mat Honan (Wired Magazine)

“Brilliant move, using the same password on GMail, Amazon, and that motorcycle forum! I’ve really enjoyed reading your email and taking over your bank account. I’m so glad you never learned to protect yourself”

1) Get a good password manager
The best passwords are long strings of letters, numbers, and symbols that you can’t remember. So you’ll need a tool to keep track of them – ideally, one you can access from any device. Look for a product that not only stores passwords but also generates them for you. I like IPassword, which works well on Mac OS, Windows, IOS, and Android.

2) Perform a password audit
Import all your existing passwords into your password manager (you probably have more than you realize stored in your browser. Now for the audit. Search for reused passwords first; these are your biggest security risk. Eliminate every instance of repetition. then search for schemes (like 123Facebook or 1234Google). A Savvy hacker – or cracking program – will get past those in seconds. Finally, sort your passwords by strength and change the weak ones.

3) Search your email
Your inbox is a treasure trove of passwords. an easy solution: Do a simple search for “password” and delete all the results. Also search for “login” and “username”. This way, if someone does get into your email, they’ll have a harder time finding all your accounts.

4) Wall off critical accounts
Your bank, email, online investing, cell phone, internet service, and data storage accounts are critical. Take extra steps to protect those. If you haven’t already done so, set up a two-step verification for such accounts. Two-step login requires an additional code that’s sent to your phone (the code changes each time). Ifyour bank doesn’t offer two-step, change to one that does.

SQL instructions

SELECT {TOP #} {DISTINCT} Fields {INTO tablename}
FROM tablename
  {WHERE condition {AND condition}
    {GROUP BY non-aggregate fields Each field must be in the “group by” or aggregate (if used)
      {HAVING condition {AND condition}}} Requires a group by phrase
  {ORDER BY fields}} Sorting order

Functions:

AVG, COUNT, MAX, MIN, SUM
SET NOCOUNT ON
SET ROWCOUNT #
SET IOSTATISTICS ON

Joins:

(Inner, outer, left, right, cross, full)
Ex: SELECT booktitle, sum(quantity), name FROM titles T, Sales S, Customer C,
WHERE T.part = S.part and S.customer = C.customer
GROUP BY booktitle, name
Outer ex: …Where T.part *= S.part (includes all of T records and only matching S records, else null)
Oracle ex: …Where T.part =(+) S.part (includes all of T records and only matching S records, else null)

Changes:

INSERT INTO tablename (field1, field2…) VALUES (value1, value2…)
INSERT INTO tablename {(field names)} SELECT fields FROM table WHERE condition
UPDATE tablename SET field1 = value1, field2 = field2 FROM table WHERE condition
DELETE FROM tablename WHERE condition
CREATE TABLE tablename (field1 datatype, field2 datatype…) {AS (SELECT …)}
CREATE DATABASE dbname ON (NAME = logical, FILNAME(?) = , SIZE = maxsize,
FILEGROWTH = …) LOG ON (names…)
ALTER TABLE tablename ADD field datatype
ALTER TABLE tablename MODIFY field datatype
DROP TABLE tablename
CREATE VIEW viewname {(field names)} AS SELECT fields FROM table WHERE condition
DROP VIEW viewname

Cursors:

DELCARE cursorname CURSOR FOR SELECT…
OPEN cursorname
CLOSE cursorname
FETCH cursorname INTO (var1, var2, …)    each FETCH advances through the recordset
@@rowcount is the number of rows in the set not fetched yet
@@identity is the row number in the table
@@SQLstatus is the error state of the last fetch (0=success, 1=error, 2= no data)
DEALLOCATE CURSOR cursorname

Rights: GRANT, DENY, REVOKE

Misc:

USE databasename Put a # in front of a table name to make it temporary.
EXISTS (query as above) returns a boolean value
BREAK exits the While loop
CONTINUE Skips to the beginning of the loop again.
Sp_help, sp_helpdb, sp_addtype, sp_droptype Be aware of page size and row/page locking modes.

Programming:

DECLARE @variablename datatype
SELECT @variablename=constant
PRINT @variablename
WHILE condition BEGIN {code statements…} END
IF condition BEGIN {code statements…} END ELSE IF condition BEGIN {code statements…} END
ELSE BEGIN {code statements…} END
BEGIN TRANSACTION {transactionname}
ROLLBACK TRANSACTION
COMMIT TRANSACTION

CREATE PROCEDURE procedurename
@variablename datatype, @variablename datatype…
AS BEGIN
SQL statements…
END

EXECUTE procedurename

CREATE TRIGGER triggername ON tablename FOR [DELETE/INSERT/UPDATE]
AS BEGIN
SQL statements…
END

 

CGI Primer

CGI stands for Common Gateway Interface.
The idea is to define a common (same) way to interface (communicate, “talk”) between two computer systems. The most common case is a person’s Internet Browser on a home or work computer communicating to a web server somewhere on the Internet. Information can be sent in two ways: GET and POST.

GET

A GET command is the common HTTP command we are familiar with (but probably did not know the name).
When we ask for a web page we are sending a GET command to the web server for that page.
Examples of GET commands:
1) http://www.websavvysolutions.com
2) ftp://ftp.yahoo.com/public/
3) http://www.websavvysolutions.com/cgi-bin/test.cgi?a=1&bob=the%20builder&mixed23=123456789

The maximum length of any variable in a GET command must be 255 characters or less.
Any CGI “GET” variables are sent following the “?”.
An equals sign (“=”) separates the variable name from the value of that variable. In this case, “a” has a value of “1”. Each variable value pair is separated by an “&” sign. No spaces are allowed. In general, there are many characters that have a meaning in a web address and therefore cannot be used in the variable name or value directly. “Illegal” characters can be “escaped” with a percent sign (“%”) followed by the ASCII table equivalent number (in HEX) for that symbol. (If you don’t know what HEX or ASCII are about, you will have to look those up on your own or just stick to the “legal” characters).

POST

A POST command is similar to the GET command except that the variables are not visible on the URL like we saw above, but are sent in a separate file. The file contains the variable value pairs on separate lines without the “?” or “&” symbols. The web browser knows how to handle either formatting when it is told to send a form.

Recommendations

You should use a GET command when you want to allow the variables to be bookmarked or you want to use a link instead of a form submission.
You should use a POST command when you want to hide the variables from bookmarking or you have more than 255 charcters worth of information to send in a single variable.

Using PERL for CGI programs

GET information is stored in the QUERY_STRING variable of the web server.
POST information is sent to the script directly (STDIN).
In the PERL programming language you can retrieve all the variable pairs into the “pairs” array using the code below:

if($ENV{'REQUEST_METHOD'} eq 'GET'){
	@pairs=split(/&/,$ENV{'QUERY_STRING'});
}elsif ($ENV{'REQUEST_METHOD'} eq 'POST'){
	read (STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
	@pairs=split(/&/,$buffer);
	if ($ENV{'QUERY_STRING'}){
		@getpairs=split(/&/,$ENV{'QUERY_STRING'});
		push (@pairs,@getpairs);
	}
}

Then to filter out the encodings and remove the “=” you can use the code below:

foreach $pair (@pairs) {
	($key, $value)=split(/=/,$pair);
	$key=~ tr/+/ /;
	$key=~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
	$value=~ tr/+/ /;
	$value=~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
	if ($formdata{$key}) {
		$formdata{$key}.=", $value";
	}else{
		$formdata{$key}.=$value;
	}
}

Now the variables are keys in the “formdata” hash and the values of the CGI variables are in the hash under each variable key stored. You can then use this hash throughout the “CGI” program you are writing.
Play around with it for a while – try things out – and it will start to make sense eventually. Good luck with your programming!

Tim Arrowsmith

P.S. I bill my time through WebSavvy Solutions if you would like a quote on a specific job or script. http://www.websavvysolutions.com/

Sarcastic advice from IT Help Desk to users

  1. Don’t write anything down. We can play back the error messages from here.
  2. When a technician says he’s coming right over, go for coffee. It’s nothing to us to remember 481 screen saver passwords.
  3. When a technician tells you that monitors don’t have cartridges in them, argue. We love a good argument.
  4. When you call us to have your computer moved, be sure to leave it buried under half a ton of postcards, baby pictures, stuffed animals, dried flowers, bowling trophies, and popsicle art. We don’t have a life, and we find it deeply moving to catch a fleeting glimpse of yours.
  5. When you’re getting a NO DIAL TONE message at home, call computer support. We can fix your line from here.
  6. When the copier doesn’t work, call computer support. There’s electronics involved in it.
  7. When something’s wrong with your home PC, dump it on a technician’s chair with no name, no phone number, and no description of the problem. We love a puzzle.
  8. When you get a message about insufficient disk space, delete everything in the Windows directory. It’s nothing but trouble anyway.
  9. When we offer training on the upcoming OS upgrade, don’t bother. We’ll be there to hold your hand after it’s done.
  10. Don’t learn the proper name for anything technical. We know exactly what you mean by “My thingy’s outta whack”.

ASP Reference

Request

TotalBytes
ClientCertificate
Cookies
Form
QueryString
ServerVariables
BinaryRead
(default action is a combination of Cookies, Form, QueryString, and ServerVariables)

Response

Buffer (true/false)
CacheControl (private/public)
Charset
ContentType
Expires (in minutes)
ExpiresAbsolute (mm/dd/yy)
IsClientConnected
PICS
Status (200,404,etc.)
Cookies (.item/.key)
AddHeader
AppendToLog
BinaryWrite
Clear
End
Flush
Redirect
Write

Server

ScriptTimeout (in seconds)
CreateObject
HtmlEncode
MapPath
UrlEncode

Application

CodePage
EnableSessionState
Language
LCID
Transaction

Application_OnEnd
Application_OnStart

Session

CodePage
LCID
SessionId
Timeout (in minutes)
Contents
StatusObjects
Abandon

Session_OnEnd
Session_OnStart

JSP Reference

5 kinds of things:

Scriplet, Expressions (returning a string), declarations,
actions, directives.

Scriptlets:

<% code here %>
or
<jsp:scriptlet> code here </jsp:scriptlet>

Expressions:

<%= var_name %>
or
<% out.print(var_name) %>
or
<jsp:expression>var_name</jsp:expression>

Declarations:

<%! var_name=constant (no output) %>
or
<jsp:declaration>delcarative
code</jsp:declaration>

Actions:

<jsp:useBean id="var_name"
class="sub_classpath"

scope="page|request|session|application" type="class" beanName="var_name">
<jsp:setProperty|getProperty>
</jsp:useBean>
<jsp:include>   (inserts text "as is" AFTER the parsing)
or
<%@ include file="filename" %>   (inserts code into the page BEFORE parsing)
or
<jsp:include page="somepage.htm"
flush="true" />
<jsp:forward page="somepage.htm"/>
<jsp:plugin type="applet"
code="x.class" codebase="/java" vspace="0"
hspace="0"
width="60"
height="80" jrevision="1.2" iepluginurl=""
nspluginurl="">
<jsp:param name="z"
value="dummy"/>
<jsp:fallback>Unable to initialize
java…</jsp:fallback>
</jsp:plugin>

Directives:

<%@ directive_name %>
or< <jsp:directive.directive_name/> <%@page attribute="value" import="name" language="java" autoflush="true" buffer="none" buffered="false" contentType="text/plain" errorPage="error.jsp" isErrorPage="true" info="whatever" isThreadSafe="true" session="true" %> (autoflush sends the page incomplete once the buffer is full) <jsp:directive.include file="name"/> (affects the rest of the page, but cannot do dynamic includes) (Action allows dynamic includes (<%=foo%>) but cannot affect the rest of the page) <%@ taglib uri="http://www...jar" prefix="my" %>
<my:subtag>my parameters</my:subtag>

comments:

<%– comment –%>
<!– comment –>
<% code… //comment %>

Other:

All code is in Java (obviously).
To precompile a script enter the URL:
my_file.jsp?jsp_precompile="true"
All <%! … %> tags are persistent across threads
isThreadSafe="false" turns off multithreading.
synchronized(object){statements} assures that the statements
are single threaded.
<%@page errorPage="errors.htm" %>
out.print("hello"); is the default form for JSP
void jspInit() and void jspDestroy() are the built-in start
and stop procedures.
The proper form of JSP is XML compliant.
Get details on custom tags if interested…

Request:

getParameter(parameter);
getServerName();
getRequestURI();
getQueryString();
getReader();
getInputStream();
getMethod();
String getAttribute(String);
Enumeration getAttributeNames();
getParameterNames();
getParameterValues();
cookie[] getCookie();
getHeader("cookie");
new Cookie (String name, String value);
void cookie.setMaxAge(int expire);
void cookie.setSecure(boolean flag);
(only with SSL)
void cookie.setDomain(String pattern);
void cookie.setPath(String URI);

Response:

Void addCookie(Cookie cookie);
void setContentType(String type);
addHeader(String name, String value)
Location=URL

Expires=dateValue
setHeader(…);
addIntHeader(String name, int value);
setIntHeader(…);
addDateHeader(String name, long date);
setDateHeader(…);
(add adds a new header line, set replaces the header line)
sendError(int code, String Msg); (HTTP response codes)
void sendRedirect("http://…");

Session:

putValue(String key, object value);
getValue(String key);
removeValue(String key);
String[] getValueNames();
getMaxInteractiveInterval();
setMaxInteractiveInterval(int Seconds);
long getCreationTime();
long getlastAccessedTime();
(seconds since Jan 1 1970)
invalidate();
(destroys the session)
boolean isnew();
(was the session used before – first page seen)
getID();
(security risk – don’t use)

Application:

getServerInfo();
getRealPath(request.getRequestURI());
log();

JavaBean design:


  1. Constructor requires no parameters.
  2. All access to the bean is through accessor methods
  3. Public {object type} get {property}
  4. Public void set {property}({object})
  5. Public Boolean is{Property}();
  6. Distributed in jar file with "Java-Bean! True" in manifest
  7. Implementing the "serializeable" interface allows the bean to
    be stored as text with live data.

Beans must be separately compiled before use.

Enterprise Java Beans are more restrictive: There are two kinds – session beans and
entity beans. Session beans are transient with only onw client each.
Entity beans represent persistent storage (possibly from a database).

JDBC:

Class.forName("sun.jdbc.odbc.jdbcOdbcDriver");
DriverManager.RegisterDriver(new
Oracle.jdbc.driver.OracleDriver());

URL="jdbc:oracle:thin:@"+host+":1521:ORCL":
connection conn=DriverManager.getConnection(URL, login, password);
statement st=conn.CreateStatement();
String SQL="Select * from
sometable";
resultSet rset st.executeQuery(SQL);


(the result set starts before the first row).
int returnValue=st.executeUpdate(otherSQL);
st.close;
conn.close;

How the Internet works

Vocabulary

In order to have an educated discussion of HOW the Internet works we will need to come to a common understanding of the term used to discuss the workings of the Internet.

  1. Internet: The decentralized collection of Interconnected Networks and computers that share publicly accessible information.
  2. Domain: A collection of computers (or one) under the control of a server administrator.
  3. Domain name: The string of letters used to remember a web site (like yahoo.com).
  4. Top Level Domain (TLD): The last part of the domain name (like .com). There are 8 main US TLDs available as of this writing (com, net, org, gov, mil, biz, ws, us). There are also hundreds of others for countires (like uk, nz, au, ca, ge, fr etc.)
  5. DNS: Domain Naming System – The service (group of computers) that translate the domain name into an IP address (from the whois database).
  6. IP address: The Internet Protocol number for the computer connected to the Internet (like 192.168.1.1) using the Internet Protocol (IP)
  7. ISP: Internet Service Provider – The service used to connect a home or work computer to the Internet proper.
  8. HTML: HyperText Markup Language – the language that web pages are “programmed” in.
  9. HTTP: HyperText Transfer Protocol – The protocol for requesting and transmitting HTML web pages (on Port 80).
  10. URL: Uniform Resource Locator – the address of a given web page or web site (like http://www.arrowsmithweb.com/internet.htm).
  11. SSL: Secure Socket Layer – the most common form of secure web page encryption.
    It comes in different strengths (40 bit, 64 bit, 128 bit, and 256 bit (I think)).

  12. Registrar: A company who maintains the whois record of your domain name information and shares it with the root DNS servers.
    (Here’s the main list)

Browsing the web

When people access the Internet they are usually accessing the World Wide Web (WWW) and using an Internet browser (like Internet Explorer, Google Chrome, Firefox, or Safari).
Generally we use HTTP, which is why most URLs start with “http://www.”.
Once a URL is typed into the browser and the enter/return key is pressed the browser will attempt to retrieve the requested web page.
First thing it does is check the URL for a name (instead of an IP number).
If there is a name, the name is resolved at their local (ISP provided) DNS server (converted into an IP address).
Then it sends a GET request for the desired file to that IP address.
If the host server is operating fine, it will respond with a response code of 200 (in the header) and provide the file.
When the browser get the file it parses it into instructions for display on the screen and other requests as needed (supporting images and other media files or framed documents).
When all the files have been requested, received, and displayed, communication is ended.
HTTP is a connectionless protocol, so once the communication for that request is over, the connection is dismantled. HTTP version 1.1 allows some reusing of the connection while multiple files are retrieved. Generally a browser can only download 6-10 files at a time per domain.
The next time a page is requested, the process starts over again with a new connection is created.

It’s worth noting that there are other forms of web communication.
When a form is filled out and “submitted” is is usually send using a POST command instead of a GET command.
The difference is that the data is more hidden and can contain greater amounts of data.
A GET command can be saved in a bookmark, while a POST command will contain only the web page itself in the bookmark.

When the page is secure (SSL) it encrypts the messages being sent to and from any web page that accepts HTTPS communication.
Remember – it’s the protocol that matters the most. You can have an insecure form, but secured POSTing and handling of that form.
Many web store-fronts use this technique to build whatever form they want on their site for purchasing products and then use a credit card processing web site to handle the SSL transaction and the credit card information.
SSL can be expensive to support on your own website, so it is many times cheaper to go with a third-party company to help you out.

Getting your own web site

Once you have an idea for a web site and you have started to design it, you need to find a web site hosting company
(like WebSavvy Solutions). There are many reasons for this:

A) Usually your ISP will forbid you to host web sites (at least commercial ones) from your home computer.

B) Your ISP usually gives you a dynamic IP address, so you can’t tell your DNS server where to point.

C) Most people do not leave their computer on and connected to the Internet all the time, but people expect your web site to be available all the time.

D) Your ISP upload speed is too slow for all but the most rarely visited web sites.

E) Your home computer is not very reliable or safe from hacker attack compared to a professional web hosting company.

Your next step is to get a domain name from a Registered Domain Name registrar or a sub agent.
There are lots of places out there now.
Originally Network Solutions had a monopoly on the service, but now it has been broadened out to many companies.
You will need to pay $5 to $50 per year to have your chosen (available) name registered to you.

The third step is to set up your DNS entries with your registrar to point to the DNS servers associated with your web site host.
You can specify 1 to 4 DNS servers in order to provide redundancy to the system. If the primary DNS server is down,
the secondary server will be consulted, and so forth.

Your host (with the DNS servers) will set up their DNS to point to the server your web site is hosted on.
This completes the sequence.
Now when someone requests your domain name, they will be directed to the server you are hosted on.

More about DNS

OK, now you ask “well, how does the DNS entry at my host get to the DNS server of my ISP and the other DNS servers around the world?”
Good question.
Every computer involved in the DNS system runs a program called a DNS Resolver.
Every DNS Resolver has addresses for “Root” DNS resolvers. These Root resolvers
point to computers that can resolve the Top Level Domains. For example,
they would point to one that knows about “.com”s, which would in turn have the
address of the system that knows about “domainname”. A “.com” resolver is
given the information about “domainname” when “domainname” is registered as “domainname.com”.

DNS servers cache domain name IP address pairs that are commonly used so they do not have to forward the request to another DNS server somewhere.
(The DNS server should only cache the name as long as the”Time to live” specified by the host DNS server.)
If the local DNS cache is missing the requested name, a root DNS server is contacted to get the definitive answer.
The root DNS servers are the TLD DNS servers (for COM, for NET, for EDU, etc.).
More than one computer will have the complete root DNS and so each DNS server will have a list of valid root DNS servers.
Every root DNS server has a complete list of the other root DNS servers for the TLD(s) it handles.

Changes

When your change the web pages on your site (on the host computer) the changes are recognized as soon as the next person requests those files.

When a DNS entry in changed it takes more time.
This means your web site is being moved to a different IP address, but you are staying with the same host.

The host DNS server records get changed and

When a DNS entry changes at the registrar, you are choosing to
use a different set of DNS servers to specify which ip address your host computer is at.
You would change this if you are switching host companies.

Database Normalization – definitions

INTRODUCTION

According to (Elmasri & Navathe, 1994), the normalization process, as first proposed by Codd (1972), takes a relation schema through a series of tests to “certify” whether or not it belongs to a certain normal form. Initially, Codd proposed three normal forms, which he called first, second, and third normal form. A stronger definition of 3NF was proposed later by Boyce and Codd and is known as Boyce-Codd normal form (BCNF). All these normal forms are based on the functional dependencies among the attributes of a relation. Later, a fourth normal form (4NF) and a fifth normal form (5NF) were proposed, based on the concepts or multivalued dependencies and join dependencies, respectively.

Normalization of data can be looked on as a process during which unsatisfactory relation schemas are decomposed by breaking up their attributes into smaller relation schemas that possess desirable properties. One objective of the original normalization process is to ensure that the update anomalies do not occur.

Normal forms provide database designers with:

· A formal framework for analyzing relation schemas based on their keys and on the functional dependencies among their attributes.· A series of tests that can be carried out on individual relation schema so that the relational database can be normalized to any degree. When a test fails, the relation violating that test must be decomposed into relations that individually meet the normalization tests.Normal forms, when considered in isolation from other factors, do not guarantee a good database design. It is generally not sufficient to check separately that each relation schema in the database is, say, in BCNF or 3NF. Rather, the process of normalization through decomposition must also confirm the existence of additional properties that the relational schemas, taken together, should possess. Two of these properties are:

· The lossless join or nonadditive join property, which guarantees that the spurious tuple problem does not occur.· The dependency preservation property, which ensures that all functional dependencies are represented in some of the individual resulting relations.
DEFINITIONS

A relation is defined as a set of tuples. By definition, all elements of a set are distinct; hence, all tuples in a relation must also be distinct. This means that no two tuples can have the same combination of values for all their attributes.

Any set of attributes of a relation schema is called a superkey. Every relation has at least one superkey—the set of all its attributes. A key is a minimal superkey, i.e., a superkey from which we cannot remove any attribute and still have the uniqueness constraint hold.

In general, a relation schema may have more than one key. In this case, each of the keys is called a candidate key. It is common to designate one of the candidate keys as the primary key of the relation. A foreign key is a key in a relation R but it’s not a key (just an attribute) in other relation R’ of the same schema.

Integrity Constraints: the entity integrity constraint states that no primary key value can be null. This is because the primary key value is used to identify individual tuples in a relation; having null values for the primary key implies that we cannot identify some tuples. The referential integrity constraint is specified between two relations and is used to maintain the consistency among tuples of the two relations. Informally, the referential integrity constraint states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation.

An attribute of a relation schema R is called a prime attribute of the relation R if it is a member of any key of the relation R. An attribute is called nonprime if it is not a prime attribute—that is, if it is not a member of any candidate key.

A functional dependency, denoted by X->Y, between two sets of attributes X and Y that are subsets of R specifies a constraints on the possible tuples that can form a relation instance of R.

NORMAL FORMS

First Normal Form (1NF)

First normal form is now considered to be part of the formal definition of a relation; historically, it was defined to disallow multivalued attributes, composite attributes, and their combinations. It states that the domains of attributes must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.

Practical Rule1: “Eliminate Repeating Groups,” i.e., make a separate table for each set of related attributes, and give each table a primary key.

Formal Definition2:  A relation is in first normal form (1NF) if and only if all underlying simple domains contain atomic values only.

 


Second Normal Form (2NF)

Second normal form is based on the concept of fully functional dependency. A functional X->Y is a fully functional dependency is removal of any attribute A from X means that the dependency does not hold any more. A relation schema is in 2NF if every nonprime attribute in relation is fully functionally dependent on the primary key of the relation. It also can be restated as: a relation schema is in 2NF if every nonprime attribute in relation is not partially dependent on any key of the relation.

Practical Rule1: “Eliminate Redundant Data,” i.e., if an attribute depends on only part of a multivalued key, remove it to a separate table.

Formal Definition2:  A relation is in second normal form  (2NF) if and only if it is in 1NF and every nonkey attribute is fully dependent on the primary key.

 


Third Normal Form (3NF)

Third normal form is based on the concept of transitive dependency. A functional dependency X->Y in a relation is a transitive dependency if there is a set of attributes Z that is not a subset of any key of the relation, and both X->Z and Z->Y hold. In other words, a relation is in 3NF if, whenever a functional dependency X->A holds in the relation, either (a) X is a superkey of the relation, or (b) A is a prime attribute of the relation.

Practical Rule1: “Eliminate Columns not Dependent on Key,” i.e., if attributes do not contribute to a description of a key, remove them to a separate table.

Formal Definition2:  A relation is in third normal form (3NF) if and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key.

 


Boyce-Codd Normal Form (BCNF)

Boyce-Codd normal form is stricter than 3NF, meaning that every relation in BCNF is also in 3NF; however, a relation in 3NF is not necessarily in BCNF. A relation schema is an BCNF if whenever a functional dependency X->A holds in the relation, then X is a superkey of the relation. The only difference between BCNF and 3NF is that condition (b) of 3NF, which allows A to be prime if X is not a superkey, is absent from BCNF.

Formal Definition2:  A relation is in Boyce/Codd normal form (BCNF) if and only if every determinant is a candidate key. [A determinant is any attribute on which some other attribute is (fully) functionally dependent.]

 


Fourth Normal Form (4NF)

Multivalued dependencies are a consequence of first normal form, which disallowed an attribute in a tuple to have a set of values. If we have two or more multivalued independent attributes in the same relation schema, we get into a problem of having to repeat every value of one of the attributes with every value of the other attribute to keep the relation instances consistent.

Fourth normal form is based on multivalued dependencies, which is violated when a relation has undesirable multivalued dependencies, and hence can be used to identify and decompose such relations. A relation scheme R is in 4NF with respect to a set of dependencies F is, for every nontrivial multivalued dependency X->F, X is a superkey for R.

Practical Rule1: “Isolate Independent Multiple Relationships,” i.e., no table may contain two or more 1:n or n:m relationships that are not directly related.

Formal Definition2:  A relation R is in fourth normal form (4NF) if and only if, whenever there exists a multivalued dependency in the R, say A->>B, then all attributes of R are also functionally dependent on A.

 


Fifth Normal Form (5NF)

In some cases there may be no losses join decomposition into two relation schemas but there may be a losses join decomposition into more than two relation schemas. These cases are handled by the join dependency and fifth normal form, and it’s important to note that these cases occur very rarely and are difficult to detect in practice.

Practical Rule1: “Isolate Semantically Related Multiple Relationships,” i.e., there may be practical constraints on information that justify separating logically related many-to-many relationships.

Formal Definition2:  A relation R is in fifth normal form (5NF)—also called projection-join normal form (PJNF)—if and only if every join dependency in R is a consequence of the candidate keys of R.

A join dependency (JD) specified on a relations schema R, specifies a constraint on instances of R. The constraint states that every legal instance of R should have a losses join decomposition into sub-relations of R, that when reunited make the entire relation R. A relation schema R is in fifth normal form (5NF) (or project-join normal form (PJNF)) with respect to a set F of functional, multivalued, and join dependencies if, for every nontrivial join dependency JD(R1, R2, …, Rn) in F (implied by F), every Ri is a superkey of R.

 


Domain Key Normal Form (DKNF)

We can also always define stricter forms that take into account additional types of dependencies and constraints. The idea behind domain-key normal form is to specify, (theoretically, at least) the “ultimate normal form” that takes into account all possible dependencies and constraints. A relation is said to be in DKNF if all constraints and dependencies that should hold on the relation can be enforced simply by enforcing the domain constraints and the key constraints specified on the relation.

For a relation in DKNF, it becomes very straightforward to enforce the constraints by simply checking that each attribute value in a tuple is of the appropriate domain and that every key constraint on the relation is enforced. However, it seems unlikely that complex constraints can be included in a DKNF relation; hence, its practical utility is limited.

 


Notes:

1.  Most of the modern DBMS systems offer “some kind” of DKNF normalization, by giving the designer the opportunity to assign domain and specific properties (such as key specification) to each attribute of a relation part of a schema.  However, that’s NOT a guarantee that the resulted relation is in DKNF.

2.  The relationship between the 7 levels of normalization (1 through 5, plus BCNF and DKNF) can intuitively be represented as layered, concentric circles, with the largest circle as the 1NF, then a smaller, inside circle as the 2NF, and so on, the smallest circle being the DKNF.  This is because a relation is defined as being in a given normal form (let’s say 2NF) if and only if it is already in the immediately previous normal form (i.e., 1NF) and satisfies additional requirements.

REFERENCES

Elmasri, R., & Navathe, S. (1994). Fundamentals of Database Systems. 2nd ed. Redwood City, CA: The Benjamin/Cummings Publishing Co. pp. 143 – 144, 401, 407 – 409, 435, 438, 440, 442 – 443.

1Rettig, Marc. (1995). Database Programming & Design. Miller Freeman, Inc.

2Date, C. J. (1990). An Introduction to Database Systems. 5th ed. Volume I. Reading, MA: Addison-Wesley Publishing Company.

HTML 3 color codes
























































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































Color Name Code Color Name Code Color Name Code Color Name Code
AliceBlue eff7ff AntiqueWhite f9e8d2 AntiqueWhite1 feedd6 AntiqueWhite2 ebdbc5
AntiqueWhite3 c8b9a6 AntiqueWhite4 817468 Aquamarine 43b7ba aquamarine1 87fdce
aquamarine2 7deabe aquamarine3 69c69f aquamarine4 417c64 azure efffff
azure2 deecec azure3 bcc7c7 azure4 7a7d7d beige f5f3d7
bisque fde0bc bisque2 ead0ae bisque3 c7af92 bisque4 816e59
Black 000000 BlanchedAlmond fee8c6 Blue 0000ff blue1 1535ff
blue2 1531ec blue3 1528c7 blue4 151b7e BlueViolet 7931df
Brown 980517 brown1 f63526 brown2 e42d17 brown3 c22217
burlywood1 fcce8e burlywood2 eabe83 burlywood3 c6a06d burlywood4 806341
CadetBlue 578693 CadetBlue1 99f3ff CadetBlue2 8ee2ec CadetBlue3 77bfc7
CadetBlue4 4c787e charTReuse 8afb17 charTReuse2 7fe817 charTReuse3 6cc417
charTReuse4 437c17 chocolate c85a17 Coral f76541 coral2 e55b3c
coral3 c34a2c coral4 7e2817 CornflowerBlue 151b8d cornsilk fff7d7
cornsilk2 ece5c6 cornsilk3 c8c2a7 cornsilk4 817a68 Cyan 00ffff
cyan1 57feff cyan2 50ebec cyan3 46c7c7 cyan4 307d7e
DarkGoldenrod af7817 DarkGoldenrod1 fbb117 DarkGoldenrod2 e8a317 DarkGoldenrod3 c58917
DarkGoldenrod4 7f5217 DarkGreen 254117 DarkKhaki b7ad59 DarkOliveGreen 4a4117
DarkOliveGreen1 ccfb5d DarkOliveGreen2 bce954 DarkOliveGreen3 a0c544 DarkOliveGreen4 667c26
DarkOrange f88017 DarkOrange1 f87217 DarkOrange2 e56717 DarkOrange3 c35617
DarkOrange4 7e3117 DarkOrchid 7d1b7e DarkOrchid1 b041ff DarkOrchid2 a23bec
DarkOrchid3 8b31c7 DarkOrchid4 571b7e DarkSalmon e18b6b DarkSeaGreen 8bb381
DarkSeaGreen1 c3fdb8 DarkSeaGreen2 b5eaaa DarkSeaGreen3 99c68e DarkSeaGreen4 617c58
DarkSlateBlue 2b3856 DarkSlateGray 25383c DarkSlateGray1 9afeff DarkSlateGray2 8eebec
DarkSlateGray3 78c7c7 DarkSlateGray4 4c7d7e DarkTurquoise 3b9c9c DarkViolet 842dce
DeepPink f52887 DeepPink2 e4287c DeepPink3 c12267 DeepPink4 7d053f
DeepSkyBlue 3bb9ff DeepSkyBlue2 38acec DeepSkyBlue3 3090c7 DeepSkyBlue4 25587e
DimGray 463e41 DodgerBlue 1589ff DodgerBlue2 157dec DodgerBlue3 1569c7
DodgerBlue4 153e7e Firebrick 800517 firebrick1 f62817 firebrick2 e42217
firebrick3 c11b17 FloralWhite fff9ee ForestGreen 4e9258 gainsboro d8d9d7
GhostWhite f7f7ff Gold d4a017 gold1 fdd017 gold2 eac117
gold3 c7a317 gold4 806517 Goldenrod edda74 goldenrod1 fbb917
goldenrod2 e9ab17 goldenrod3 c68e17 goldenrod4 805817 Gray 736f6e
Gray0 150517 Gray1 ffffff Gray18 250517 Gray21 2b1b17
Gray23 302217 Gray24 302226 Gray25 342826 Gray26 34282c
Gray27 382d2c Gray28 3b3131 Gray29 3e3535 Gray30 413839
Gray31 41383c Gray32 463e3f Gray34 4a4344 Gray35 4c4646
Gray36 4e4848 Gray37 504a4b Gray38 544e4f Gray39 565051
Gray40 595454 Gray41 5c5858 Gray42 5f5a59 Gray43 625d5d
Gray44 646060 Gray45 666362 Gray46 696565 Gray47 6d6968
Gray48 6e6a6b Gray49 726e6d Gray50 747170 Gray51 787473
Gray52 7a7777 Gray53 7c7979 Gray54 807d7c Gray55 82807e
Gray56 858381 Gray57 878583 Gray58 8b8987 Gray59 8d8b89
Gray60 8f8e8d Gray61 939190 Gray62 959492 Gray63 999795
Gray64 9a9998 Gray65 9e9c9b Gray66 a09f9d Gray67 a3a2a0
Gray68 a5a4a3 Gray69 a9a8a6 Gray70 acaba9 Gray71 aeadac
Gray72 b1b1af Gray73 b3b3b1 Gray74 b7b6b4 Gray75 b9b8b6
Gray76 bcbbba Gray77 bebebc Gray78 c1c1bf Gray79 c3c4c2
Gray80 c7c7c5 Gray81 cacac9 Gray82 cccccb Gray83 d0cfcf
Gray84 d2d2d1 Gray85 d5d5d4 Gray86 d7d7d7 Gray87 dbdbd9
Gray88 dddddc Gray89 e0e0e0 Gray90 e2e3e1 Gray91 e5e6e4
Gray92 e8e9e8 Gray93 ebebea Gray94 eeeeee Gray95 f0f1f0
Gray96 f4f4f3 Gray97 f6f6f5 Gray98 f9f9fa Gray99 fbfbfb
Green 00ff00 green1 5ffb17 green2 59e817 green3 4cc417
green4 347c17 GreenYellow b1fb17 honeydew f0feee honeydew2 deebdc
honeydew3 bcc7b9 honeydew4 7a7d74 HotPink f660ab HotPink1 f665ab
HotPink2 e45e9d HotPink3 c25283 HotPink4 7d2252 IndianRed 5e2217
IndianRed1 f75d59 IndianRed2 e55451 IndianRed3 c24641 IndianRed4 7e2217
ivory ffffee ivory2 ececdc ivory3 c9c7b9 ivory4 817d74
Khaki ada96e khaki1 fff380 khaki2 ede275 khaki3 c9be62
khaki4 827839 lavender e3e4fa LavenderBlush fdeef4 LavenderBlush2 ebdde2
LavenderBlush3 c8bbbe LavenderBlush4 817679 LawnGreen 87f717 LemonChiffon fff8c6
LemonChiffon2 ece5b6 LemonChiffon3 c9c299 LemonChiffon4 827b60 LightBlue addfff
LightBlue1 bdedff LightBlue2 afdcec LightBlue3 95b9c7 LightBlue4 5e767e
LightCoral e77471 LightCyan e0ffff LightCyan2 cfecec LightCyan3 afc7c7
LightCyan4 717d7d LightGoldenrod ecd872 LightGoldenrod1 ffe87c LightGoldenrod2 ecd672
LightGoldenrod3 c8b560 LightGoldenrod4 817339 LightGoldenrodYellow faf8cc LightPink faafba
LightPink1 f9a7b0 LightPink2 e799a3 LightPink3 c48189 LightPink4 7f4e52
LightSalmon f9966b LightSalmon2 e78a61 LightSalmon3 c47451 LightSalmon4 7f462c
LightSeaGreen 3ea99f LightSkyBlue 82cafa LightSkyBlue2 a0cfec LightSkyBlue3 87afc7
LightSkyBlue4 566d7e LightSlateBlue 736aff LightSlateGray 6d7b8d LightSteelBlue 728fce
LightSteelBlue1 c6deff LightSteelBlue2 b7ceec LightSteelBlue3 9aadc7 LightSteelBlue4 646d7e
LightYellow fffedc LightYellow2 edebcb LightYellow3 c9c7aa LightYellow4 827d6b
LimeGreen 41a317 linen f9eee2 Magenta ff00ff magenta1 f43eff
magenta2 e238ec magenta3 c031c7 Maroon 810541 maroon1 f535aa
maroon2 e3319d maroon3 c12283 maroon4 7d0552 MediumAquamarine 348781
MediumBlue 152dc6 MediumForestGreen 347235 MediumGoldenrod ccb954 MediumOrchid b048b5
MediumOrchid1 d462ff MediumOrchid2 c45aec MediumOrchid3 a74ac7 MediumOrchid4 6a287e
MediumPurple 8467d7 MediumPurple1 9e7bff MediumPurple2 9172ec MediumPurple3 7a5dc7
MediumPurple4 4e387e MediumSeaGreen 306754 MediumSlateBlue 5e5a80 MediumSpringGreen 348017
MediumTurquoise 48cccd MediumVioleTRed ca226b MidnightBlue 151b54 MintCream f5fff9
MistyRose fde1dd MistyRose2 ead0cc MistyRose3 c6afac MistyRose4 806f6c
moccasin fde0ac NavajoWhite fddaa3 NavajoWhite2 eac995 NavajoWhite3 c7aa7d
NavajoWhite4 806a4b Navy 150567 OldLace fcf3e2 OliveDrab 658017
OliveDrab1 c3fb17 OliveDrab2 b5e917 OliveDrab3 99c517 OliveDrab4 617c17
Orange f87a17 orange1 fa9b17 orange2 e78e17 orange3 c57717
orange4 7f4817 OrangeRed f63817 OrangeRed2 e43117 OrangeRed3 c22817
OrangeRed4 7e0517 Orchid e57ded orchid1 f67dfa orchid2 e473e7
orchid3 c160c3 orchid4 7d387c PaleGoldenrod ede49e PaleGreen 79d867
PaleGreen1 a0fc8d PaleGreen2 94e981 PaleGreen3 7dc56c PaleGreen4 4e7c41
PaleTurquoise aeebec PaleTurquoise1 bcfeff PaleTurquoise2 adebec PaleTurquoise3 92c7c7
PaleTurquoise4 5e7d7e PaleVioleTRed d16587 PaleVioleTRed1 f778a1 PaleVioleTRed2 e56e94
PaleVioleTRed3 c25a7c PaleVioleTRed4 7e354d PapayaWhip feeccf PeachPuff fcd5b0
PeachPuff2 eac5a3 PeachPuff3 c6a688 PeachPuff4 806752 peru c57726
Pink faafbe pink2 e7a1b0 pink3 c48793 pink4 7f525d
Plum b93b8f plum1 f9b7ff plum2 e6a9ec plum3 c38ec7
plum4 7e587e PowderBlue addce3 purple 8e35ef purple1 893bff
purple2 7f38ec purple3 6c2dc7 purple4 461b7e Red ff0000
red1 f62217 red2 e41b17 RosyBrown b38481 RosyBrown1 fbbbb9
RosyBrown2 e8adaa RosyBrown3 c5908e RosyBrown4 7f5a58 RoyalBlue 2b60de
RoyalBlue1 306eff RoyalBlue2 2b65ec RoyalBlue3 2554c7 RoyalBlue4 15317e
salmon1 f88158 salmon2 e67451 salmon3 c36241 salmon4 7e3817
SandyBrown ee9a4d SeaGreen 4e8975 SeaGreen1 6afb92 SeaGreen2 64e986
SeaGreen3 54c571 SeaGreen4 387c44 seashell fef3eb seashell2 ebe2d9
seashell3 c8bfb6 seashell4 817873 Sienna 8a4117 sienna1 f87431
sienna2 e66c2c sienna3 c35817 sienna4 7e3517 SkyBlue 6698ff
SkyBlue1 82caff SkyBlue2 79baec SkyBlue3 659ec7 SkyBlue4 41627e
SlateBlue 737ca1 SlateBlue1 7369ff SlateBlue2 6960ec SlateBlue3 574ec7
SlateBlue4 342d7e SlateGray 657383 SlateGray1 c2dfff SlateGray2 b4cfec
SlateGray3 98afc7 SlateGray4 616d7e snow fff9fa snow2 ece7e6
snow3 c8c4c2 snow4 817c7b SpringGreen 4aa02c SpringGreen1 5efb6e
SpringGreen2 57e964 SpringGreen3 4cc552 SpringGreen4 347c2c SteelBlue 4863a0
SteelBlue1 5cb3ff SteelBlue2 56a5ec SteelBlue3 488ac7 SteelBlue4 2b547e
Tan d8af79 tan1 fa9b3c tan2 e78e35 Thistle d2b9d3
thistle1 fcdfff thistle2 e9cfec thistle3 c6aec7 thistle4 806d7e
tomato f75431 tomato2 e54c2c tomato3 c23e17 Turquoise 43c6db
turquoise1 52f3ff turquoise2 4ee2ec turquoise3 43bfc7 turquoise4 30787e
Violet 8d38c9 VioleTRed e9358a VioleTRed1 f6358a VioleTRed2 e4317f
VioleTRed3 c12869 VioleTRed4 7d0541 Wheat f3daa9 wheat1 fee4b1
wheat2 ebd3a3 wheat3 c8b189 wheat4 816f54 Yellow ffff00
yellow1 fffc17 YellowGreen 52d017