Attributes also called columns or fields are the data elements associated with an entity
What elements comprise a database? This article deals mainly with the objects that comprise a database. Several concepts are worthy of coverage within the scope of the database as it relates to database design. As you work with data and databases, you will see how the origination of business information and databases is formulated into database elements. The intent here is to provide a brief coverage of basic database elements to provide you with a basic understanding of the elements found in a database. Show Learn more about Databases at TechRepublic Academy! Several topics are discussed in the following sections. These topics include:
Database SchemaA schema is quite simply a group of related objects in a database. Within a schema, objects that are related have relationships to one another, as discussed earlier. There is one owner of a schema, who has access to manipulate the structure of any object in the schema. A schema does not represent a person, although the schema is associated with a user account that resides in the database. The three models associated with a schema are as follows:
Figure 1 – Collection of objects that comprise a database schema. NOTE
The table is the most fundamental element found in a database schema. Columns and rows are associated with tables. Tables, columns, and rows are discussed in the following subsections. TableA table is the primary unit of physical storage for data in a database. When a user accesses the database, a table is usually referenced for the desired data. Multiple tables might comprise a database, therefore a relationship might exist between tables. Because tables store data, a table requires physical storage on the host computer for the database. Figure 2 illustrates tables in a schema. Each table in the figure is related to at least one other table. Some tables are related to multiple tables. Figure 2 – Database tables and their relationships. Four types of tables are commonly used:
Tables are used to store the data that the user needs to access. Tables might also have constraints attached to them, which control the data allowed to be entered into the table. An entity from the business model is eventually converted into a database table. ColumnsA column, or field, is a specific category of information that exists in a table. A column is to a table what an attribute is to an entity. In other words, when a business model is converted into a database model, entities become tables and attributes become columns. A column represents one related part of a table and is the smallest logical structure of storage in a database. Each column in a table is assigned a data type. The assigned data type determines what type of values that can populate a column. When visualizing a table, a column is a vertical structure in the table that contains values for every row of data associated with a particular column. In Figure 3, columns within the Customers table are shown. Each column is a specific category of information. All of the data in a table associated with a field is called a column. Figure 3 – Columns in a database table. RowsA row of data is the collection of all the columns in a table associated with a single occurrence. Simply speaking, a row of data is a single record in a table. For example, if there are 25,000 book titles with which a bookstore deals, there will be 25,000 records, or rows of data, in the book titles table once the table is populated. The number of rows within the table will obviously change as books’ titles are added and removed. See Figure 4 for an illustration of a row of data in a table. Figure 4 – Row of data in a database table. Data TypesA data type determines the type of data that can be stored in a database column. Although many data types are available, three of the most commonly used data types are
Alphanumeric data types are used to store characters, numbers, special characters, or nearly any combination. If a numeric value is stored in an alphanumeric field, the value is treated as a character, not a number. In other words, you should not attempt to perform arithmetic functions on numeric values stored in alphanumeric fields. Design techniques such as this will be discussed in more detail throughout the book. Numeric data types are used to store only numeric values. Date and time data types are used to store date and time values, which widely vary depending on the relational database management system (RDBMS) being used. KeysThe integrity of the information stored in a database is controlled by keys. A key is a column value in a table that is used to either uniquely identify a row of data in a table, or establish a relationship with another table. A key is normally correlated with one column in table, although it might be associated with multiple columns. There are two types of keys: primary and foreign. Primary KeysA primary key is the combination of one or more column values in a table that make a row of data unique within the table. Primary keys are typically used to join related tables. Even if a table has no child table, a primary key can be used to disallow the entry of duplicate records into a table. For example, an employee’s social security number is sometimes considered a primary key candidate because all SSNs are unique. Foreign KeysA foreign key is the combination of one or more column values in a table that reference a primary key in another table. Foreign keys are defined in child tables. A foreign key ensures that a parent record has been created before a child record. Conversely, a foreign key also ensures that the child record is deleted before the parent record. Figure 5 illustrates how a foreign key constraint is related to a primary key constraint. The Auth_id column in the Booklist table references the Auth_id in the Authors table. Authors is the parent table and Booklist is the child table. If a record for Auth_id=3 needs to be created, the record must be inserted into the Authors table first. If an author needs to be removed from the database, all references to the author must first be deleted in Booklist, the child table. Figure 5 – Referential integrity, or parent/child relationships. RelationshipsMost databases are divided into many tables, most of which are related to one another. In most modern databases, such as the relational database, relationships are established through the use of primary and foreign keys. The purpose of separating data into tables and establishing table relationships is to reduce data redundancy. The process of reducing data redundancy in a relational database is called normalization and is discussed in detail in Chapter 8 Of our book, Database Design. Three types of table relationships that can be derived are as follows:
Figure 6 briefly illustrates table relationships in a relational database. A relational database allows parent tables to have many child tables, and child tables to have many parent tables. The figure shows two tables. Table 1 has an ID column (primary key) and Table 2 has an FK column (foreign key). In the one-to-one relationship example, notice that for every ID in Table 1, there is only one ID in Table 2. In the one-to-many relationships example, notice that the ID of 1 has many occurrences in Table 2. In the many-to-many relationship example, notice that the ID in Table 1 might occur multiple times in Table 2 as a foreign key, and the ID in Table 2 might occur multiple times in Table 1. Figure 6 – Available table relationships in the relational model. ConclusionThe intent of this article was to provide a brief coverage of basic database elements to provide you with a basic understanding of the elements found in a database. While it may not seem like a lot has been covered, you have actually now been exposed to the key topics involved in working with databases. As you continue to work with databases, you will find that you will continually be confronted with the above terms and concepts, including schemas, tables, columns, rows, and keys. About the AuthorsRyan K. Stephens and Ronald R. Plew are the authors of Database Design (ISBN: 0-672-31758-3), a book by Sams Publishing. Ryan is president and CEO
of Perpetual This article is based on techniques presented in Database Design (ISBN: 0-672-31758-3), which is available © Copyright Sams Publishing, All Rights Reserved What is column or fields or attributes?Each row (called a tuple) is a data set that applies to a single item, and each column contains characteristics that describe the rows. In database lingo, these columns are called attributes. A database attribute is a column name and the content of the fields under it in a table.
Are columns called attributes?The columns in a table is a field and is also referred to as an attribute. You can also think of it this way: an attribute is used to define the record and a record contains a set of attributes.
What is an attribute in a database?In general, an attribute is a characteristic. In a database management system (DBMS), an attribute refers to a database component, such as a table. It also may refer to a database field. Attributes describe the instances in the column of a database.
What is entities and attributes in database?An entity type typically corresponds to one or several related tables in database. Attribute. A characteristic or trait of an entity type that describes the entity, for example, the Person entity type has the Date of Birth attribute. Record.
|