First 3 Rules of Data Normalization for Newbies - Part 1

The First Normal Form States: "All attributes are not repeated or must be single-valued"

In identifying whether a table is in its 1NF, consider this 3-points checklist:
1. Does a Primary Key exist?
2. Have you ensured that each attribute is in its basic form? Being in its basic form means that it is not derived
3. Have you ensured that the attribute is not repeated and considered single-valued?

Let us examine the following simple example :
1NF_sample1

1. Existence of a Primary Key: EMPLOYEE is uniquely identified by its attribute Employee_ID.

2. Verification that the attribute is in its basic form: The attribute Emp_Age is derived. Emp_Age is broken to its basic form, e.g. Emp_DateOfBirth.

3. Verification that the attribute is not repeating: In the example, Emp_Dependent, Emp_DependentNM, Emp_DependentDOB can be classified as repeating occurrences of EmployeeDependent. To make it in 1NF, move these to its own Entity.

The final result of the example will be:

Next blog will be on the 2NF. :)

Intrusions: Preference and the TeleInterActive Lifestyle

In the first post to this blog, we spoke of businesses' many "P's": process, programs, projects, people, perception, policy, procedures, patents, products, price, place and promotion. It's time to start discussing these organizational attributes and how the TeleInterActive Lifestyle might impact them and how to prepare your organization for the incorporation of distributed workgroups, remote workers and distant partners.

We're actually going to start with a "P" not on the original list, "Preference". As we've been working in this area over the past 4 years, one engaging debate is whether or not technology that allows the TeleInterActive Lifestyle is enabling or intrusive. Does leading the life increase or decrease our ability to make our preferences a reality in our lives?

One side of the debate is that technology has led to more intrusion, more opportunity to be interrupted. This is what I want to focus on today.

Broadband into the home, VPNs, wireless and mobile, cell phones, 2G, 2.5G, 3G, WiFi hotspots, n-tier architecture, web services, SOA, and so on, and so forth... an amazingly expanding cornucopia of technologies to keep us connected, on-line and available.

So, Ozzie of the old TV show and many other "Grey Flannel Suit" white collar workers carried their work home in briefcases. Today, we can get at our corporate data and do our work from home, from a hotel room, from any cafe boasting a WiFi hotspot, through GPRS or CDMA 1xRTT cell phones, and in any many other ways as well. Nothing really new here, as knowledge workers always took work home with them.

A newer phenomenon is that personal life can intrude on business hours as well. Again, from one perspective, not really new... Who hasn't picked up the company phone and made a lunch date, an doctor's appointment or get the evening's grocery list. But now, I know children who can't imagine not being able to contact their parents any time of the day via cell phone. We use email and IM to make or break those lunch dates, and maybe even order those groceries or take-out over the web to pick-up on the way home.

My contention is that prior to the industrial revolution, we led our lives - period. As society evolved after the industrial revolution, our lives artificially changed into "business life" vs. "personal life". Vacations replaced travel. And between the peasants and the nobles rose the middle class to further the growth of the cities that started as we evolved from hunter-gather to agrarian to industrial life styles. Now work can and does intrude into our "off time" [I think I remember what that is] :>> and home intrudes into work time, with greater and greater ease. My hope is that we can once again integrate the two and just have a life with peaks in one area contending with peaks in the other, but achieving some balance over time. Others think that the business peaks overwhelm personal concerns, leaving little time for family, friends and alone time.

Remember, there is an off button to all these gadgets. &#59;)

First 3 Rules of Data Normalization for Newbies - Introduction

The work of Dr. Codd as published in A Relational Model of Data for Large Shared Data Bank by E.F. Codd provides the detailed and comprehensive explanation of Relational Model and Normalization.

I just want my blog to be short and sweet each day. So, over the next 3 days, I shall blog on a normalization rule. I learn by example so I shall discuss the rules through examples.

Normalization helps eliminate the problems of redundancy in database design. It reinforces referential integrity in the database and provides a way to index. A highly normalized design may need a large number of joins. Hence, there is a need to carefully review the degree of normalization in a database design. One must consider the database’s intended use.

The First 3 Rules of Normalization are:

  1. First Normal Form (1NF): All attributes are not repeated or must be single-valued
  2. Second Normal Form (2NF): An attribute must depend upon its entities entire UID
  3. Third Normal Form (3NF): No non-UID attribute can be dependent upon another non-UID attribute

Part 1 of my blog will be about 1NF.

What working with almost no sleep can do ...

Thursday evening till morn, I worked trying to finish as much work as I can to meet deadlines due before 2005 knocks at the door. If some people are stressed with holiday shopping, you can say I am overwhelmed by the amount of work that needs to be done by the end of the year.

Anyway, yesterday morning, Friday, with only an hour of sleep, our project team was conducting a proof of concept run. I wrote a script that had the object of producing a flat file with one record with fields placed in an exact postion for upload to the mainframe. The script extracts data from an Oracle table. Simple right? We ran the script and viola... the script produced the flat file but the field positions were off by 1. The script goes:

set termout off
set hea off
set pagesize 0
spool H:\BART_LBS\pclink32\UpLoads\k4201.PRN

select CORP||'|',ITMNBR||'|',WHSE||'|',PRIMBNLOC||'|',SECBNLOC
from k42
where ITMNBR like '62-40-00200%' AND WHSE like 'WS%';

spool off

I scratched my sleepy head kept awake by coffee 88|. I checked the field lengths of the columns of my table and they were correct. So, field length inaccuracy was not the cause... Darn, don't you hate it when the simplest of things don't work right?

Here's the corrected SQL statement. The bright-eyed and :idea: alert, Lee, found the culprit.

select CORP||'|'||ITMNBR||'|'||WHSE||'|'||PRIMBNLOC||'|'||SECBNLOC||'|'
from k42
where ITMNBR like '62-40-00200%' AND WHSE like 'WS%';

Yes, the culprit was the " , " that produced the extra space. Toast to you, Lee. This blog entry is for you. &#59;D

PL/SQL FAQ for Newbies

1. What is PL/SQL? How is it different from SQL*Plus?
PL/SQL is Oracle's extension to SQL incorporating Procedural Language. It allows the data manipulation and query statements of SQL to be included in logical program blocks and procedural units of code.
SQL*Plus is an Oracle Server tool that recognizes and executes SQL statements.

2. What is the basic Structure of the PL/SQL Block?
Every unit of PL/SQL comprises one or more blocks. These may be entirely separate or nested. The general structure of the block is:
HEADER: Used only for named blocks
DECLARE: Defines the PL/SQL Objects to be used in this block
BEGIN
/* start of executable actions */
EXCEPTION
/* Error Handlers; Defines what to do if an executable action produces exceptions or errors */
END;

3. What are some of the control statements in PL/SQL?
- IF Statement
- GOTO statement
- Basic Loops
- FOR Loops
- WHILE Loops

4. How do you process PL/SQL blocks in SQL*Plus?
- Define a block in the SQL buffer and then run the buffer
- Define a block as part of a SQL*Plus script and then run the file

Example PL/SQL

5. While using PL/SQL in SQL*Plus, how can one print to screen?
Issue:
SQL> set serveroutput on;
then run your PL/SQL block

6. How do you show errors while compiling from SQL*Plus?
issue:
SQL> show errors

7. How do you issue create table from PL/SQL?
Starting with 8i, DDL statements e.g. CREATE, TRUNCATE and DROP can be called within PL/SQL by using EXECUTE IMMEDATE.
Example:
begin
EXECUTE IMMEDIATE 'CREATE TABLE SAMPLETABLE(ID NUMBER)';
end;

October 2019
Mon Tue Wed Thu Fri Sat Sun
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
 << <   > >>
The TeleInterActive Press is a collection of blogs by Clarise Z. Doval Santos and Joseph A. di Paolantonio, covering the Internet of Things, Data Management and Analytics, and other topics for business and pleasure. 37.540686772871 -122.516149406889

Search

Categories

The TeleInterActive Lifestyle

Yackity Blog Blog

The Cynosural Blog

Open Source Solutions

DataArchon

The TeleInterActive Press

  XML Feeds

Mindmaps

Our current thinking on sensor analytics ecosystems (SAE) bringing together critical solution spaces best addressed by Internet of Things (IoT) and advances in Data Management and Analytics (DMA) is updated frequently. The following links to a static, scaleable vector graphic of the mindmap.

Recent Posts

Blog software