SQL Table

6 Key excerpts on "SQL Table"

• 

  eBook - ePub

  Beginning Visual Basic 2015

  • Bryan Newsome(Author)
  • 2015(Publication Date)
  • Wrox

    (Publisher)

  The database engine, in your case Microsoft SQL Server 2014, manages the file or files and the data within those files. The database format is known as a relational database. At its most basic level, data is stored in tables, rows, and columns similar to how you see data in a spreadsheet. The data in tables is related by special database keys, which allows for better storage and faster retrieval. To access the data, you will use Structured Query Language (SQL). After you complete this chapter, you’ll understand all this information. Database Tables A table contains a collection of data, which is represented by one or more columns and one or more rows of data. Imagine the way data is stored in a spreadsheet in rows and columns. Each column in a table represents an attribute of the data stored in that table. For example, a column named First Name would represent the first name of an employee or customer. This column is an attribute of an employee or customer. A row in a table contains a collection of columns that form a complete set of attributes of one instance of the data stored in that table. For example, suppose a table contains two columns: First Name and Last Name. These two columns in a single record describe the name of that one person. This is illustrated in Figure 12.1. FIGURE 12.1 TRY IT OUT Creating Tables In this Try It Out, you use SQL Server Management Studio (SSMS) to create a database and add a table. NOTE SQL Server Management Studio is a tool you use to help manage SQL Server Databases. As a developer, you may or may not have access to your company’s SQL Server databases. If you are allowed to access them, this is the best tool to use. It enables you to create and change databases you are using in your applications, as well as to create and test SQL statements you will use in your application. There are many other features this application can do for you to help manage your databases that you will not use in this book

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • Auerbach Publications

    (Publisher)

  This chapter shows how the relational database model is used from an application perspective. There is little point in understanding something such as relational database modeling without seeing it applied in some way, and so this chapter looks at how a database model is accessed by applications. A relational database model contains tables, where rows in tables are accessed using a computer programming language called Structured Query Language (SQL). SQL is a declarative programming language that is used to read data from tables and update data in those tables.

  A declarative programming language describes what you are coding without describing how the problem is solved, which can be difficult to understand, because declarative code requires all instructions in a single complex instruction. Other computer programming languages such as C and FORTRAN are procedural or modular and break software down into separate parts called modules; and then there is Java, which is object-oriented.

  When a database model is well designed, the creation of SQL code can be a simpler process, which also can imply that difficulties encountered in coding of SQL queries can often indicate database model design flaws, or just too much granularity in a data model.

  This chapter covers:

  •  What SQL is •  The origins of SQL •  Different types of queries •  Changing data in tables •  Changing table structure

  4.1    What is SQL?

  SQL is a declarative computer programming language used for accessing row and column values in relational database tables:

  •  SQL is structured in the sense that it is used to access structured data from tables in a relational database. •  Use of the word “language” implies a computer programming language that has specific syntax for performing specific tasks.

  •  SQL is a declarative language consisting of single commands where the database itself does a lot of the work in deciding how to get that information. In other words, SQL tells the database what it wants, and the database does some of the logical assessment, because logic is built into a relational database using keys and relationships between tables. A procedural language, on the other hand, contains blocks of commands, where those blocks of commands are sequences of distinct steps, typically where each successive step is dependent on the result of the previous step (command) in the sequence.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL For Dummies

  • Allen G. Taylor(Author)
  • 2018(Publication Date)
  • For Dummies

    (Publisher)

  Part 1

  Getting Started with SQL

  IN THIS PART… The essentials of relational databases Basic SQL concepts Fundamental database tools

  Passage contains an image Chapter 1

  Relational Database Fundamentals

  IN THIS CHAPTER

  Organizing information

  Defining “database” in digital terms

  Deciphering DBMS

  Looking at the evolution of database models

  Defining “relational database” (can you relate?)

  Considering the challenges of database design

  SQL (pronounced ess-que-ell, not see’qwl, though database geeks still argue about that) is a language specifically designed with databases in mind. SQL enables people to create databases, add new data to them, maintain the data in them, and retrieve selected parts of the data. Developed in the 1970s at IBM, SQL has grown and advanced over the years to become the industry standard. It is governed by a formal standard maintained by the International Standards Organization (ISO).

  Various kinds of databases exist, each adhering to a different model of how the data in the database is organized.

  SQL was originally developed to operate on data in databases that follow the relational model. Recently, the international SQL standard has incorporated part of the object model, resulting in hybrid structures called object-relational databases. In this chapter, I discuss data storage, devote a section to how the relational model compares with other major models, and provide a look at the important features of relational databases.

  Before I talk about SQL, however, I want to nail down what I mean by the term database. Its meaning has changed, just as computers have changed the way people record and maintain information.

  Keeping Track of Things

  Today people use computers to perform many tasks formerly done with other tools. Computers have replaced typewriters for creating and modifying documents. They’ve surpassed calculators as the best way to do math. They’ve also replaced millions of pieces of paper, file folders, and file cabinets as the principal storage medium for important information. Compared with those old tools, of course, computers do much more, much faster — and with greater accuracy. These increased benefits do come at a cost, however: Computer users no longer have direct physical access to their data.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  CompTIA IT Fundamentals (ITF+) Study Guide

  Exam FC0-U61

  • Quentin Docter(Author)
  • 2018(Publication Date)
  • Sybex

    (Publisher)

  Figure 7.3 , you can see that there are four tables. Why would an administrator set it up this way? One of the key principles to setting up a database is to secure the data and make it available only to those who need it. At the same time, it should be easily accessible to users with proper access. In the example of this fictitious university, a student may log into the system and see their personal data, courses for which they are registered, and the instructor’s names. But they have no reason to see the instructor’s personal information unless the instructor provides it. Someone in the registrar’s office can add new courses without seeing personally identifiable information about a student. And instructors can be set up to see the names of the students in their course, but not the student’s contact information or credits or GPA. Databases are usually modular to make it easier to allow or restrict access to specific data.

  The physical schema is the actual tables, columns, and relationships created in the RDBMS. Many database management tools have a process for creating a physical schema based on a logical schema, or the physical schema can be created manually. Regardless, the logical schema should exist first—it’s always best to plan a database out on paper before implementing it, so you can catch any potential issues while they’re still easy to fix.

    Any changes made to the physical schema should also be reflected in the logical schema.

  Tables

  These are what look like spreadsheet and contain data. One database can (and often does) have multiple tables. Each row in a table is called a record. If you want to link multiple tables together, you need to structure the data in such a way that it makes it easy to identify which records belong with each other, such as by using a customer identifier.

  Fields

  Columns in a table are called fields. Fields contain a single data type for all records. For example, a field could contain a phone number, cost, or other numerical data, or a name, product description, or other text data. A database wouldn’t be designed in such a way that a field contains a part number for one record and a cost for another. Those need to be two separate fields. This is because when databases are queried, the user is looking for a specific type of data. It needs to be in the correct field!

  For a table in a database to be relational, it must have a primary key. In fact, its creation is mandatory when a table is created. A primary key is one or more fields whose data is used to identify a record uniquely. The following must be true for primary keys:

  • The data in the combination of columns must be unique. (For example, if a database uses first_name and last_name

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Relational Database Design and Implementation

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

    (Publisher)

  (The word “table” is used synonymously with “relation” in the relational data model, although, to be strictly correct, not every table is a relation.) The definition specifies what will be contained in each column of the table, but does not include data. When you include rows of data, you have an instance of a relation, such as the small customer relation in Figure 5.1. Figure 5.1 A simple customer relation. At first glance, a relation looks much like a flat file or a rectangular portion of a spreadsheet. However, because it has its underpinnings in mathematical set theory, a relation has some very specific characteristics that distinguish it from other rectangular ways of looking at data. Each of these characteristics forms the basis of a constraint that will be enforced by the DBMS. Columns and Column Characteristics A column in a relation has the following properties: ▪ A name that is unique within the table: Two or more tables within the same relational database schema may have columns with the same names—in fact, as you will see shortly, in some circumstances this is highly desirable—but a single table must have unique column names. When the same column name appears in more than one table, and tables that contain that column are used in the same data manipulation operation, you qualify the name of the column by preceding it with the name of its table and a period, as in: ▪ A domain: The values in a column are drawn from one and only one domain. As a result, relations are said to be column homogeneous. In addition, every column in a table is subject to a domain constraint. Depending on your DBMS, the domain constraint may be as simple as a data type, such as integers or dates

  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)

  ▪ A primary key: A primary key is a column or combination of columns with a value that uniquely identifies each row. As long as you have unique primary keys, you also have unique rows. We will look at the issue of what makes a good primary key in great depth in the next major section of this chapter.

  ▪ There are no positional concepts: The rows can be viewed in any order without affecting the meaning of the data.

  Note: For the most part, DBMSs do not enforce the unique row constraint automatically. However, as you will see shortly, there is another way to obtain the same effect.

  Types of Tables

  A relational database works with two types of tables. Base tables are relations that are actually stored in the database. These are the tables that make up your schema. However, relational operations on tables produce additional tables as their result. Such tables, which exist only in main memory, are known as virtual tables. Virtual tables may not be legal relations—in particular, they may have no primary key—but because virtual tables are never stored in the database, this presents no problem in terms of the overall design of the database.

  The use of virtual tables benefits a DBMS in several ways. First, it allows the DBMS to keep intermediate query tables in main memory rather than storing them on disk, enhancing query performance. Second, it allows tables that violate the rules of the relational data model to exist in main memory without affecting the integrity of the database. Third, it helps avoid fragmentation of database files and disk surfaces by avoiding repeated write, read, and delete operations on temporary tables.

  Note: SQL, the language used to manage most relational databases, also supports “temporary base tables.” Although called base tables, temporary tables are actually virtual tables in the sense that they exist only in main memory for a short time and are never stored in the physical database.

  A Notation for Relations

  You will see instances of relations throughout this book used as examples. However, we do not usually include data in a relation when documenting that relation. One common way to express a relation is as follows:

  Sign up to read

  Learn more about book