Relational Databases

10 Key excerpts on "Relational Databases"

• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  1 Introduction to Databases and the Relational Data Model LEARNING OBJECTIVES • To explain fundamental features of a database • To learn about different types of databases • To understand the fundamental concepts of a relation • To describe and identify primary and foreign keys Databases are a tool that provide many features beyond the definition, population, and retrieval of data, including support for concurrent access by multiple users, recovering from failures, and managing user access privileges. This chapter provides a brief history of various data models and then introduces the relational data model, which is the focus of this text. Relational Databases provide the conceptual simplicity of modeling data using tables yet provide the power to handle numerous types of applications. The relational data model supports the specification of various constraints to manage the integrity of the data. 1.1 Databases Are a Tool Data are everywhere. Data are stored in many different formats in various shapes and sizes. Smaller data sets may be stored in files as just textual data, such as in a comma-separated value (csv) file. In a csv file, each line represents related data values separated by commas. Spreadsheets are another common method of storing data, but spreadsheets must be interpreted by a specific program. A csv file can also be interpreted by spreadsheet programs or programs specifically writ- ten to analyze the data. Many may be familiar with the use of a simple spreadsheet that uses basic formulas for main- taining information. For certain data, a spreadsheet works quite well, e.g. student grades for classes. However, for other data, it is difficult to determine how to represent it correctly in a blank canvas of rows and columns in a spreadsheet. What if there are multiple kinds of relationships between the data? Does one use multiple worksheets with references between them? The com- plexity of the data representation quickly escalates.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Computing Skills for Biologists

  A Toolbox

  • Stefano Allesina, Madlen Wilmes(Authors)
  • 2019(Publication Date)
  • Princeton University Press

    (Publisher)

  C H A P T E R 10 • • • • • • • • • • • • • • Relational Databases 10.1 What Is a Relational Database? A database is a structured collection of data, making the data easy to access, manage, and update. Databases are particularly useful for handling large data sets, and are one of the main items in the toolbox of biologists working with sequence data, spatial data (GIS, 1 combining maps and databases), and “big data” in general. This chapter introduces Relational Databases, in which data are arranged in tables, and different tables are connected through related columns. Rela-tional databases consist of three components: first, the data, organized in tables; second, a language to query the data (Structured Query Language, SQL); and third, a program to manage the data (relational database manage-ment system, RDBMS). The tables of a relational database can be thought of as a series of spread-sheets that are linked with each other. Each table is composed of rows (called records ) and columns (called fields ). Each field contains a certain type of data (e.g., text, numeric, date). The relationships between the tables are encoded in a schema . To interact with a relational database, you write SQL queries , com-mands that retrieve, update, or modify the data. In most cases, running a query returns a particular representation of a (processed) subset of the data. Relational database management systems store the data and related objects in specific binary formats that can be read by specialized software (as opposed to text files, which can be read by almost any software). 1. Geographic information system. 338 ● Chapter 10 10.2 Why Use a Relational Database? Throughout the book, we have always sided with the use of plain text, given that text files provide a simple, efficient way to store your data, code, and manuscripts. When you have much data, however, using Relational Databases can greatly improve performance.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Fundamentals of Database Management Systems

  • Mark L. Gillenson(Author)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  One was that these DBMSs were simply too large to store and use on the early PCs. The other was that they were too complex to be used by the very broad array of non-information-systems professionals at whom the PCs were targeted. Today, the relational approach to database management is by far the primary database management approach used in all levels of information systems and for most application purposes, from accounting to banking to manufacturing to sales on the World Wide Web. Relational database management is represented today by such products as Microsoft Access and SQL Server, Oracle, Sybase, and IBM’s DB2 and Informix. While these and other relational database systems differ in their features and implementations, they all share a common data structure philosophy and a common data access tool: Structured Query Language (SQL) (often pronounced ‘‘sequel’’). This chapter will focus on the basic concepts of how data is stored and retrieved in a relational database by a relational DBMS. Chapter 6 will discuss some additional relational database concepts. Then, Chapter 7 will describe logical database design and Chapter 8 will go into physical database design. THE RELATIONAL DATABASE CONCEPT Relational Terminology In spite of the apparent conflict between non-redundant, linear file data storage and data integration demonstrated in Chapter 3, the relative simplicity of simple, linear files or structures that resemble them in a true database environment is very desirable. After all, the linear file arrangement is the most basic and commonly used data structure there is. This is precisely one of the advantages of relational database management. The Relational Database Concept 107 CONCEPTS IN ACTION 5-A BLACK & DECKER Black & Decker is one of the world’s largest producers of electric power tools and power tool accessories; it is among the largest-selling residential lock manufacturers in the U.S., and is a major manufacturer of faucets sold in the U.S.

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  Learning PostgreSQL 11

  A beginner's guide to building high-performance PostgreSQL database solutions, 3rd Edition

  • Salahaldin Juba, Andrey Volkov(Authors)
  • 2019(Publication Date)
  • Packt Publishing

    (Publisher)

  Relational Databases

  This chapter, and the ones following, will provide a high-level overview of topics related to database development. These topics will cover the theoretical aspects of Relational Databases. The first two chapters try to summarize theoretical topics that are seen on a daily basis. Understanding these theoretical concepts will enable developers to not only come up with clean designs but also to master Relational Databases.

  This chapter is not restricted to learning PostgreSQL, but covers all Relational Databases. The topics covered in this chapter include the following:

  • Database management systems
  • Relational algebra
  • Data modeling

  Passage contains an image

  Database management systems

  Different database management systems (DBMS ) support diverse application scenarios, use cases, and requirements. DBMS have a long history. First, we will quickly take a look at the recent history, and then explore the market-dominant database management system categories.

  Passage contains an image

  A brief history

  Broadly, the term database can be used to present a collection of things. Moreover, this term brings to mind many other terms including data, information, data structure, and management. A database can be defined as a collection or repository of data, which has a certain structure, managed by a DBMS. Data can be structured as tabular data, semi-structured as XML documents, or unstructured data that does not fit a predefined data model.

  In the early days, databases were mainly aimed at supporting business applications; this led us to the well-defined relational algebra and relational database systems. With the introduction of object-oriented languages, new paradigms of DBMS appeared, such as object-Relational Databases and object-oriented databases. Also, many businesses, as well as scientific applications, use arrays, images, and spatial data; thus, new models such as raster, map, and array algebra are supported. Graph databases are used to support graph queries such as the shortest path from one node to another, along with supporting traversal queries easily.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Readings in Artificial Intelligence and Databases

  • John Mylopoulos, Michael L. Brodie(Authors)
  • 2014(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  We do not propose to give a detailed definition of the relational model here—the original definition appeared in [7], and an improved one in Sees. 2 and 3 of [8]. Its structural part consists of domains, relations of assorted degrees (with tables as their principal conceptual representation), at-tributes, tuples, candidate keys, and primary keys. Under the principal representation, attributes become columns of tables and tuples become rows, but there is no notion of one column succeeding another or of one row suc-ceeding another as far as the database tables are con-cerned. In other words, the left to right order of columns and the top to bottom order of rows in those tables are arbitrary and irrelevant. The manipulative part of the relational model consists of the algebraic operators (select, project, join, etc.) which transform relations into relations (and hence tables into tables). The integrity part consists of two integrity rules: entity integrity and referential integrity (see [8, 11] for recent developments in this latter area). In any particular ap-plication of a data model it may be necessary to impose further (database-specific) integrity constraints, and thereby define a smaller set of consistent database states or changes of state. In the development of the relational model, there has always been a strong coupling between the structural, Introduction to AI and Databases 1.4 Relational Database: A Practical Foundation for Productivity 63 manipulative, and integrity aspects. If the structures are defined alone and separately, their behavioral properties are not pinned down, infinitely many possibilities present themselves, and endless speculation results. It is therefore no surprise that attempts such as those of CODASYL and ANSI to develop data structure definition language (DDL) and data manipulation language (DML) in sep-arate committees have yielded many misunderstandings and incompatibilities.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Relational Database Design and Implementation

  Clearly Explained

  • Jan L. Harrington(Author)
  • 2009(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Chapter 5. The Relational Data Model

  Once you have a completed ER diagram, you can translate that conceptual logical schema into the formal data model required by your DBMS. Today, most new database installations are based on the relational data model. We call databases that adhere to that model Relational Databases.

  Note: The older data models that are described in Appendix A are still in use in many legacy database systems. However, it is extremely rare to find a business creating a new one. On the other hand, the object-oriented data model is still quite current, and although it has not replaced the relational data model and does not appear to be doing so, some new installations use either object-oriented or a combination of relational and object-oriented.

  A relational database is one whose logical structure is made up of nothing but a collection of “relations.” Although you may have read somewhere that a relational database has “relationships between files” nothing could be further from the truth. In this chapter, you will learn exactly what a relational database is and how relations provide representations of data relationships.

  Note: Remember from Chapter 4 that we said that a DBMS isolates database users from physical storage. A logical data model therefore has absolutely nothing to do with how the data are stored in files on disk.

  The relational data model is the result of the work of one man: Edgar (E.F.) Codd. During the 1960s, Dr. Codd, although trained as a mathematician, worked with existing data models. His experience led him to believe that they were clumsy and unnatural ways of representing data relationships. He therefore went back to mathematical set theory and focused on the construct known as a relation. He extended that concept to produce the relational database model, which he introduced in a paper in 1970.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Engineering Informatics

  Fundamentals of Computer-Aided Engineering

  • Benny Raphael, Ian F. C. Smith(Authors)
  • 2013(Publication Date)
  • Wiley

    (Publisher)

  For example, consider a business application that processes orders from customers. The application-level representation consists of data structures for customers and orders. At this level, operations such as ‘place an order’ and ‘delete an order’ are defined. If a relational DBMS is used for this application, the conceptual view consists of tables in which these data are stored. Operations such as ‘insert a record’ and ‘delete a record’ are defined at this level. Internally, all these data might be stored in one or many files in formats proprietary to the DBMS. Low-level read and write operations are defined at this level. Engineers dealing with applications need not be concerned with this level.

  5.3 Relational Database Systems

  Relational Databases are based on the relational model presented by Codd (1970). The main theme of Codd's model is a conceptual representation scheme that protects users from having to know how the data are organized in files (i.e. the internal representation described in Section 5.2.3). This representation scheme has firm mathematical foundations with roots in set theory.

  The principle behind the relational model is to organize data in the form of two-dimensional tables (called relations). Each table consists of a fixed number of columns (attributes) and a variable number of rows (records). A standard set of operations is defined on tables so that data can be modified in an implementation- and application-independent manner (i.e. at the conceptual level described in Section 5.2.3).

  Relational Databases are popular for the following reasons:

  • Commercial applications contain large amounts of structured data that are easily organized into tables.
  • Tables are a natural way of organizing many types of data. The simplicity of table representation is appealing.
  • Standard table operations such as selecting, inserting, updating and deleting of records are efficiently implemented, thus accommodating large volumes of data.

  There are many examples of data organized into tables within commercial applications. Here are some common examples:

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Systems

  A Pragmatic Approach, 3rd edition

  • Elvis Foster, Shripad Godbole(Authors)
  • 2022(Publication Date)
  • Auerbach Publications

    (Publisher)

  Not many products were able to survive the rigors of that benchmark. Interestingly, two of the products that survived the test are still doing well in the industry today; they are Oracle and DB2. A third product — Ingres — has gradually faded but not before spawning and/or impacting development of other products such as structured query language (SQL) Server (from Microsoft), Sybase (now integrated into SAP Adaptive Server Enterprise), and PostgreSQL.

  9.2.2 Revised Definition of a Relational System

  In 1985, Codd, in a paper captioned “Is Your DBMS Really Relational?” revised the definition of a relational system. Christopher Date later made some important recommendations about the revised model (see Date 1990 ). The revised model with its recommendations is presented in Table 9.3 .

  Table 9.3 Codd’s Revised Definition of a Relational System

  Features Category	Clarification
  Data structures	Structural features include the following: Relations including base relations, views, queries, and snapshots Attributes that comprise the structure of the normalized relations Domains Primary keys Foreign keys Queries User-defined data types Rows (tuples) of time-dependent data for each relation
  Data integrity	Integrity features including: Entity integrity rule Referential integrity rule Primary key inheritance rules Type inheritance and conversion rules (user defined) User-defined integrity rules Ensure closure and uniqueness of attributes
  Data manipulation	Manipulation features including: Entry, update, and deletion of data Theta-select Projection on certain attributes, while omitting others Theta-join and natural join Outer join Divide Union Intersection Difference Relational assignment Rename Extend Summarize
  Other features	Discussed in Sections 1.2, 1.5, and 2.5

  Additionally, Codd proposed 12 rules for determining how relational a DBMS product is. With these 12 rules added to the redefined model, a DBMS is considered to be fully relational if it satisfies all structural, integrity, and manipulative features and fulfills the 12 rules. This was followed by a so-called zero-rule

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Design of Industrial Information Systems

  • Thomas Boucher, Ali Yalcin(Authors)
  • 2010(Publication Date)
  • Academic Press

    (Publisher)

  Chapter 2 The Relational Database Model Design of Industrial Information Systems Copyright © 2006 by Academic Press, Inc. All rights of reproduction in any form reserved. 27 2.1 INTRODUCTION In this chapter, we introduce the basic concepts of the relational database. Early attempts at developing database systems were based on the hierarchical and network models. However, these have been largely displaced in industry by the relational model, which is the foundation of modern database management systems. Therefore, it shall be the relational model that we focus on exclu-sively in this chapter. Learning to use a database is greatly enhanced by hands-on experience. For that reason, we provide many examples and encourage the reader to try them on a database management system. We have chosen to illustrate the examples on Microsoft Access because of its wide availability on personal computers and its ease of use. The programming instructions are based on Access 2003. 2.2 THE DATABASE MANAGEMENT SYSTEM (DBMS) A database can be thought of as a computerized filing cabinet. This filing cabinet electronically stores data defined and “filed” by users within the organization that maintains the database, usually referred to as the “enterprise.” The database system has both hardware and software components. Hardware is the physical storage medium for the data (hard disk, tape, etc.). The software is the medium through which the user accesses the physically stored data. This software is called the database management system (DBMS) . The DBMS allows the user to store, retrieve, and update data without having particular knowledge about the physical location of data or how related data are stored. In effect, the user is provided a view of the data that makes it easy for him or her to access and use.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Principles of Database Management

  The Practical Guide to Storing, Managing and Analyzing Big and Small Data

  • Wilfried Lemahieu, Seppe vanden Broucke, Bart Baesens(Authors)
  • 2018(Publication Date)
  • Cambridge University Press

    (Publisher)

  To safeguard its data quality, Sober wants to periodically check that this is not the case in its relational database. In the previous chapter, we dealt with the modeling aspects of Relational Databases. In this chapter, we elaborate on SQL, which is the lingua franca of RDBMSs and one of the most popular data definition and manipulation languages in use in the industry nowadays. Its core functionality is implemented, with only some minor variations, throughout all RDBMS products. We discuss the SQL language constructs to express a relational model and to formulate queries that retrieve and modify the data in a relational database. First, we provide an overview of the SQL data definition language (SQL DDL) to define a relational data model. We also discuss how the SQL data manipulation language (SQL DML) can be used for data manipulation such as retrieving data, updating data, inserting new data and deleting existing data. We then elaborate on SQL views and SQL indexes, which are part of the external and internal data model, respectively. We also illustrate how SQL can be used for authorization by granting or revoking privileges to users or user accounts. We conclude by illustrating how SQL can be used for managing metadata. 7.1 Relational Database Management Systems and SQL ........................................................................................................... As explained in the previous chapter, Relational Databases are based upon the relational data model and managed by a relational database management system, or RDBMS. Structured Query Language, or SQL, 1 is the language used for both data definition and data manipulation. It is one of the most popular database languages currently in use in the industry. It what follows, we elaborate on its key characteristics and position it in terms of the three-layer database architecture discussed in Chapter 1.

  Sign up to read

  Learn more about book