31 Oct Sql server tutorial 01 Introduction to Relational Databases and SQL
Sql server tutorial Introduction to Relational Databases and SQL
- Key Skills & Concepts
- Understand Relational Databases
- Learn About SQL
- Use a Relational Database Management System
In 2011, the International Organization for Standardization (ISO) and the American
National Standards Institute (ANSI) published revisions to their SQL standard, which I
will call SQL:2011. As you will see later, the standard is divided in parts, and each part is
approved and published on its own timeline, so different parts have different publication
years; it is common to use the latest year as the collective name for the set of all parts
published up through that year. The SQL:2011 standard, like its predecessors SQL:2008,
SQL:2006, SQL:2003, SQL:1999 (also known as SQL3), and SQL-92, is based on the
relational data model, which defines how data can be stored and manipulated within a
relational database. Relational database management systems (RDBMSs) such as Oracle,
Sybase, DB2, MySQL, and Microsoft SQL Server (or just SQL Server) use the SQL
standard as a foundation for their technology, providing database environments that
support both SQL and the relational data model. There is more information on the SQL
standard later in this chapter.
Understand Relational Databases
Structured Query Language (SQL) supports the creation and maintenance of the relational
database and the management of data within that database. However, before I go into a
discussion about relational databases, I want to explain what I mean by the term database.
The term itself has been used to refer to anything from a collection of names and
addresses to a complex system of data retrieval and storage that relies on user interfaces
and a network of client computers and servers. There are as many definitions for the word
database as there are books about them. Moreover, different DBMS vendors have
developed different architectures, so not all databases are designed in the same way.
Despite the lack of an absolute definition, most sources agree that a database, at the very
least, is a collection of data organized in a structured format that is defined by metadata
that describes that structure. You can think of metadata as data about the data being stored;
it defines how the data is stored within the database.
Over the years, a number of database models have been implemented to store and
manage data. Several of the more common models include the following:
- Hierarchical This model has a parent–child structure that is similar to an inverted
tree, which is what forms the hierarchy. Data is organized in nodes, the logical
equivalent of tables in a relational database. A parent node can have many child nodes,
but a child node can have only one parent node. Although the model has been highly
implemented, it is often considered unsuitable for many applications because of its
inflexible structure and lack of support for complex relationships. Still, some
implementations such as IMS from IBM have introduced features that work around
- Network This model addresses some of the limitations of the hierarchical model.
Data is organized in record types, the logical equivalent of tables in a relational
database. Like the hierarchical model, the network model uses an inverted tree
structure, but record types are organized into a set structure that relates pairs of record
types into owners and members. Any one record type can participate in any set with
other record types in the database, which supports more complex queries and
relationships than are possible in the hierarchical model. Still, the network model has
its limitations, the most serious of which is complexity. In accessing the database,
users must be very familiar with the structure and keep careful track of where they are
and how they got there. It’s also difficult to change the structure without affecting
applications that interact with the database.
- Relational This model addresses many of the limitations of both the hierarchical and
network models. In a hierarchical or network database, the application relies on a
defined implementation of that database, which is then hard-coded into the application.
If you add a new attribute (data item) to the database, you must modify the application,
even if it doesn’t use the attribute. However, a relational database is independent of the
application; you can make nondestructive modifications to the structure without
impacting the application. In addition, the structure of the relational database is based
on the relation, or table, along with the ability to define complex relationships between
these relations. Each relation can be accessed directly, without the cumbersome
limitations of a hierarchical or owner/member model that requires navigation of a
complex data structure. In the following section, “The Relational Model,” I’ll discuss
the model in more detail.
Although still used in many organizations, hierarchical and network databases are now
considered legacy solutions. The relational model is the most extensively implemented
model in modern business systems, and it is the relational model that provides the
foundation for SQL.