Relational Databases

Pete Loshin   Today’s Top Stories    or  Other Business Intelligence Stories  

Driving BI Value with Data Integration: Overcoming the Thorniest Challenges Survey: Business Intelligence Maturity and the Quest for Better Performance BI @ The Speed of Business Computerworld Custom Publishing: Bright City Lights Sign up to receive Security Resource Alerts

January 08, 2001 (Computerworld) -- Everyone knows what a simple database is: Telephone directories, mail-order catalogs and dictionaries are all databases of sorts. Databases can be structured or organized in several different ways: as flat files, as hierarchical or networked structures or as related tables. Of these types, relational databases manage most organizations' data.

A database can be described as a table of data consisting of columns and rows, similar to a spreadsheet. Each row contains a single record; each column contains all the instances for each row of one particular piece of data. For example, a typical telephone directory consists of columns holding telephone numbers, subscriber names and subscriber addresses. Each row consists of a number, name and address. This simple form is called a flat file because of its two-dimensional nature and the fact that all data is stored in a single file.

Ideally, every database has at least one column with a unique identifier, or key. Consider the phone book: There may be many entries for J. Smith, but none of the phone numbers will be duplicated. The phone number serves as the key.

In reality, things aren't quite so simple. Two or more people sharing a phone might want a listing for each name; this causes the phone number to appear in two or more places, resulting in multiple rows with keys that aren't unique.

Data Causes Problems

In the simplest databases, every record is complete in a single row, meaning the phone company would need a separate column for every piece of account information. That would require a separate column for a secondary subscriber, another for a tertiary subscriber and so on for as many additional subscribers as might be needed.

This means every record in the database would have to have all the extra columns, even if most would never be used. It also means the database would have to be redesigned and rebuilt every time you rolled out a new service. Add touch-tone service, and you've got to rebuild to add a new column. Add caller ID, call waiting and so on, and you'd have to rebuild again and again.

In the 1960s, only the biggest companies could afford computers to manage their data. Moreover, databases built on static data models and procedural programming languages such as Cobol could be costly to maintain and weren't always reliable. Procedural languages specify sequences of events that the computer should step through to accomplish tasks. Programming such sequences was difficult, especially if you needed to change the structure of the database or cook up a new kind of report.

Powerful Relations

E.F. Codd, a researcher at IBM's San Jose research laboratory, essentially created and defined the relational database concept in his seminal work "A Relational Model of Data for Large Shared Data Banks" (Communications of the ACM, June 1970).

Codd created a model that allowed designers to break down their databases into separate but related tables that made sense for performance while retaining the outward appearance of the original database to end users. Codd has since been considered the father of the relational database.

It works in the following manner: The phone company could build its primary table around a primary key such as telephone number and store it with other basic customer information. The company can define a separate table with columns for that primary key and for value-added services like caller ID and call waiting. It could define yet another table to track billing, with the primary-key phone numbers and billing data in each row.

End users can easily extract the information they want in the form they want it, though it's stored in separate tables. So a phone company customer service representative can retrieve a subscriber's billing information as well as the status of special services or the latest payment received, all on the same screen.

Codd originally specified 12 rules for relational databases, most of which address aspects of data integrity, updates and access, though the first two are quite accessible even to a nontechnical audience.

Rule 1, the information rule, mandates that all information in a relational database be represented as a set of values stored in tables; Rule 2, the guaranteed access rule, mandates that every piece of data in a relational database be accessible by table name, primary key and column name. In other words, all data is stored in tables, and if you know the table, the primary key and the column name of the piece of data you want, you should always be able to retrieve it.

One of the implications of Codd's work is that relational databases use declarative rather than procedural programming languages. Declarative languages such as SQL permit users to, in effect, tell the computer, "I want the following bits of data from all records that fit this set of criteria." The computer then figures out the steps it needs to go through to pull that data from the database.

The vast majority of heavy-duty databases rely on relational database management system software available from vendors such as Oracle Corp., Sybase Inc., IBM and Microsoft Corp.

While most vendors' SQL flavors are interoperable only to a point, SQL makes possible the database-backed Web sites that we increasingly rely on. Easy-to-program interfaces between Web sites and relational databases make it possible for end users to add new records, update existing records and produce reports for diverse services such as conducting online trading and accessing online library catalogs.

Loshin is a freelance writer in Arlington, Mass. Contact him at pete@loshin.com.

Relational Database Model A relational database uses multiple tables related to one another by a designated key (here, the PhoneNumber field).

Print this Story

Send Us Feedback

E-mail this Story

Digg this Story

Slashdot this Story

"Enterprise search continues to lag behind commerical search because companies lack a "findability" strategy, says one researcher...." Read more... "It's IT Blogwatch: in which we all wonder how much we get paid and Glassdoor.com helps us out. Not to..." Read more... Read more Business Intelligence posts or See all Blogs

Microsoft promises four patches next week Google gives away home-cooked Web application security scanner Storm botnet stages Fourth of July attacks More top stories...

Microsoft trumpets security additions in upcoming IE8 Apple cuts price of high-end SSD MacBook Air by $500 Ultrathin showdown: Apple MacBook Air vs. Lenovo ThinkPad X300 vs. Toshiba Portege R500

This old laptop: Revitalizing an aging notebook on the cheap All it takes is a couple hours and about $125 to breathe new life into an old laptop. Here's how. Bill Gates moves on Is Microsoft's Golden Age over? What are Gates' most memorable quotes? Find out in Computerworld's complete coverage of the end of the Bill Gates era at Microsoft. Five things you should never tell your boss There are some things your CIO definitely doesn't want to hear. Also don't miss the flipside, Five things you should always tell your boss. Hands-On: Firefox 3 fixes what's broke and keeps what's right With its latest version, Mozilla's browser continues to raise the bar for what Web browsers should be. Windows Vista A to Z Reviews, analyses, how-tos, visual tours, hot issues and predictions about Microsoft's new OS. More Continuing Coverage After 9/11: Homeland SecurityAsk a Premier 100 IT LeaderBlackBerry Legal BattleData Security BreachesElectronic VotingFirefoxH-1B VisasHP's Boardroom ScandalHandheld DevicesHurricane Katrina AftermathMicrosoft Legal IssuesMicrosoft's VistaOpen SourceRFID TechnologySCO's Linux FightSarbanes-Oxley ActSpamVoice Over IPWi-FiWindows Meta File VulnerabilityWindows XP Service Pack 3 IT Careers 2010 Four years from now, the IT field will be a vastly different place. Will you be ready? More Special Reports Premier 100 IT Leaders 2007 Best in Class2006 Computerworld Horizon Awards2006 Premier 100 IT Leaders2006 Salary Survey2007 Best Places to Work in IT2007 IT Forecast2007 Premier 100 IT Leaders40 Years of ComputerworldASPs, Take TwoBest Places to Work in IT 2003Best Places to Work in IT 2004Best Places to Work in IT 2005Best Places to Work in IT 2006Business Intelligence Home RunsBusiness Intelligence: Smarter BIBusiness Intelligence: The Future of BICRM Goes VerticalCRM: Sober CRMCareer Planning Guide 2005Careers: IT Profession 2010Computerworld Horizon Awards 2005Data Management: Mining for GemsData Management: Taming Data ChaosDevelopment: Hard-Workin' Web SitesDevelopment: New Tools New ChoicesDevelopment: The New World of Application DevelopmentDevelopment: The Web Services TsunamiDevelopment: Web Services HurdlesDisaster Recovery: Preparing for the WorstE-commerce Grows UpE-mail/Groupware: Big DecisionsFaces of Mobile ITForecast 2006Global MobileHardware: Multiple Cores, Multiple ChallengesHardware: On-Demand Un-HypedHardware: The Shape of Things to ComeHorizon Awards: 10 Cool Cutting Edge TechnologiesIT Management: Guide to Managing VendorsIT Management: Navigating Global ITIT Management: Recovery AheadIT Management: The Future of ITIT Management: The Resourceful Project ManagerInnovative Technology Awards 2004Management: Reinventing ITMicrosoft in TransitionMobile/Wireless Leaders and LaggardsMobile/Wireless: The Untethered WorkerMobile/Wireless: Tiny Gadgets, Huge CostsNetwork Management: Taking ControlNetworking: Turbulence Ahead!Networking: VoIP Goes MainstreamOperating Systems: In the Slow LaneOperating Systems: Linux Goes GlobalOperating Systems: Should You Nix Unix?Outsourcing: Offshore Buyer's GuideOutsourcing: Outsourcing DangersPremier 100 IT Leaders 2004 Best in ClassPremier 100 IT Leaders 2005 Best in ClassROI: Do the Math!Salary Survey 2003Salary Survey 2004Salary Survey 2005Security Action PlanSecurity: Compliance HeadachesSecurity: Proactive SecuritySecurity: Risk and RewardSecurity: Tips From Security ProsSouped-up SecurityStorage: Battling ComplexityStorage: Cheap & Secure Data StoresStorage: Stretching Your Storage DollarsStorage: The Lean Storage MachineStorage: The New Rules of StorageStorage: The Ultimate Backup GuideSupply Chain: Missing LinksSupply Chain: RFID Reality CheckThe Business of SecurityWeb 2.0 SecurityWireless: Wireless At Work All Zones Application Performance Zone Business Continuity Zone Data Center Management Zone Enterprise-Class Security Zone The File Data Management Zone Grid Computing on Windows Zone Security Management Zone ITIL Best Practices Zone The SAS Zone Storage Virtualization Zone Business Intelligence and Analytics Zone See your link here

Speeding the time to intelligence Get this Computerworld report free for a limited time, compliments of SAS. Time To Intelligence -- a concept defining how long it takes to get accurate and timely information into the hands of workers who need it most. Do it slower than your competitors and your company is toast. Do it faster, you scorch them. Business Intelligence is the key to optimizing Time To Intelligence, and success there is a combination of people, policies, and technology. Download this executive briefing  Why SaaS is Vital to Email and Web Security Why SaaS is Vital to Email and Web Security Download this webcast, free, compilments of Webroot Software Go to the webcast  Rapid application development, rapid results Download this special report now! (Source: Intersystems) All too many businesses suffer from IT infrastructures that are a hodge-podge of disconnected databases and applications. What's needed is the ability rapidly develop connected applications under a unified service-oriented architecture. InterSystems Ensemble integration environment and Cache database are effective tools in answering this need, delivering a rapid ROI. Download this white paper  White Papers Read up on the latest ideas and technologies from companies that sell hardware, software and services. Deploying Virtualized NetWare on Linux Whitepaper Toward More Flexible, Next-Generation Collaboration Solutions Driving Business Success Through Workgroup Choice and Flexibility View more whitepapers

• Microsoft promises four patches next week
• Google gives away home-cooked Web application security scanner
• Storm botnet stages Fourth of July attacks
• More top stories 

SAS Business and Analytics Zone No matter where your organization is on the path toward evolving your IT infrastructure, SAS can adapt to your situation to meet your long-term enterprise intelligence needs. We can help you drive intelligence evolution to the next level, while leveraging and extending the value of your existing IT investments. Learn more in the Business and Analytics Zone See All Zones

Intercept Spam & Viruses With MessageLabs MessageLabs is offering a complimentary 30 day trial of its managed Anti-virus and Anti-spam security solutions. MessageLabs guarantees complete protection against all know and unknown email threats. By providing 24 hour support, your business can increase productivity and decrease risk. Register for a complimentary trial and receive a free datasheet. Download this white paper now!

Since You Asked A weekly storage column from storage analyst, Steve Duplessie of the Enterprise Strategy Group Click here to read the latest column by Steve Duplessie