Database Design

Database Essentials - Part 2

Division 1

IntroductionA database is a set of related tables. Each table has rows and columns. Datum is kept in each cell formed by the intersection of a row and a column. Of course all data in a database are related. So, for a table, the cell contents are related. For a database the tables are related. There are two kinds of database. You have the Relational Database and the Object Database. There is no clear cut between the relational database and the object database.

This is the beginning of an article series. It focuses on relational database. Many of the features of object database are found in relational database. So, as I explain relational database I also explain these many features of object database. Object database is relatively new; it is still to become popular. Most databases today are relational databases. After completing this series, understanding object database will not be difficult.

A computer programmer learns database in order to create databases for organizations (companies). When an organization has a well design database, data processing is extremely fast and accurate.

You need basic knowledge in computer programming (including OOP) in order to understand this series. You also need to have basic notion in the way Organizations (companies) operate. In other words you need to know basic things like, a company has a manager (or CEO). There may be an assistant manager. There may be an accountant who tracks the way money in used in the company. There may be a treasurer. A company has departments. There may be a Sales department. There may also be a manufacturing department (factory). You do not need to understand much more than these basic ideas in order to understand this series. I will explain any other thing you need concerning Organizations for you to understand the series.

The series is made up of parts. A part is one tutorial (or article). Parts are grouped into divisions. This is the first part in the first division. The first division is called Database Essentials.

Who learns Database?A computer programmer learns database in order to create databases for organizations (companies). When an organization has a well design database, data processing is extremely fast and accurate.

TablesA database is made up of related tables for an Organization. Of course, each table has a name. The following link has the tables I use to explain the principles of this tutorial. Open the link in a new Tab of your browser window. As you read this tutorial, you should be referring to the web page (tab) of the opened link.

/diagrams/database-tables.htm

Table 1.1 shows information about employees of an organization. The name of the table is Employee.

The table has 10 rows and 9 columns. The first column is for the last names of the employees. The second column is for the first names. The third column is for the Job each employee performs. The fourth is for the addresses. The fifth is for the cities. The sixth is for the regions. A region is a state. In some countries a region might be called a province. In other countries it is just called a region. The seventh column is for the postal code. The eighth is for the country. The ninth is for the cell phone numbers.

Note that for any column name that is made up of a phrase, the words are joined. For example, you have "LastName" instead of "Last Name". You can also join the words using underscores. So you can have "LastName" or "Last_Name". A database works with a program. The column names are used in a program. Know that in order for these column names to be used, the words of the phrase names have to be joined. A column name consisting of a single word remains as it is (not joined with any other word).

Relational Database NamesAs mentioned above, there are two types of databases: the relational database and the object database. There is no clear cut between the two databases. In this section I give you the relational database names for the characteristics of a database table.

A column is called a Field or a Data Item. The content of each cell in a table is a value. You can call that a data value.

An entity is anything in the real world that you wish to track in an organization (or company). Examples are employee, sale, and merchandise. You may want to be monitoring (tracking) the statistics of employee, sale or merchandise. An entity can be expressed as a table. This meaning of entity is also applicable to object databases.

Object Database NamesWith object databases a table that does not have rows is called a Class. A row of a table is called an instance of a class or an object. A column of a table is called Attribute. Here, attribute means characteristic. You can also call Attribute, Property. A column is considered to be a property of the table. The attribute name is the name of the column. Remember, an empty table is a class. It represents the rows that will be in the table. A row is always assumed to have data. A row is considered as an instance of a class. So a column is a property (attribute) of a class or object (row). A table cell content has a value.

An object database comes with a different approach to keep tables, but it still does the same things that relational database does. With object database, emphasis is not laid in the column-and-row structure: the table is called a collection, meaning a collection of objects; which in relational database is a collection of rows. Each row is an object having attributes. As you move along a row, from one column value to another column value, you are said to be moving from attribute to attribute. The column value (table cell content) is called, the value of the attribute. So a row is an object having attributes and the attributes have values, which are the table cell contents.

In object database, a collection of objects (rows) is still called an object.

The IDEach column in a database table has a name. The name of a column is always typed above it. In Table 1.1 (in browser window tab), the first column name is LastName; the second is FirstName; the third is Job, etc; each column has a name. All columns in any database table have names. We have seen those for Table 1.1. These names identify the columns.

Note that in Table 1.1 the rows are not identified as the columns have been identified by names. Identifying rows in a database table is optional. You do not have to identify rows of a database table. However it is always very convenient to identify rows. Rows in a database table are usually many; Table 1.1 is just an illustration. Sometimes you can have as many as 1000 rows in a table. The number of rows in a database table is not fixed. It generally increases as business activities in the organization are carrying on. In the case of the employee table of Table 1.1, as the company grows the number of rows increases because the number of employees increases. Well, if the company has to lay off workers then the number of rows will decrease.

Generally, unlike with the rows, the number of columns for a database table is more or less fixed and small over time. So you can identify the columns with names that can be easily remembered. Since the number of rows of a table is not fixed, you need to identify them with a code. The code for each row is called an ID, for Identification. Table 1.2 (in the browser window tab) identifies the rows with numbers as 10, 20, 30, 40, etc. Table 1.3 (in the browser window tab) identifies the rows with MSE1, MSE2, MSE3, etc.

The IDs for the rows form a column and has its own name. In these last two tables the name of the ID column is EmployeeID. The ID column is usually the extreme left column in a table. The values in the ID column are usually in a progressive sequence. In Table 1.2 there is an increase of 10 (10, 20, 30, 40, etc.) as you go down each row. In Table 1.3 there is an increase of 1 (MSE1, MSE2, MSE3, etc.).

If the ID code has letters, then the letters in the ID code have meaning known to the organization. If the name of the organization (company) is Modern Systems for example, the abbreviation of the organization can be used in the ID code. You can have something like MSE1, MSE2, MSE3, etc. where M stands for Modern and S stands for System; E stands for Employee. Each organization decides on how to code the ID for the rows of its tables. To understand the code, you need to talk to one of the workers in the organization. You the database designer can also propose coding for the ID.

Table 1.1, Table 1.2 and Table 1.3 are the same tables, but Table 1.1 does not have an ID column while Table 1.2 and Table 1.3 have.

ID may also be called number. So you may hear of Employee Number, Customer Number and Order Number instead of Employee ID, Customer ID and Order ID.

Table NamesIn relational database, each table has a name. The name of each of the above tables is Employee. In object database, the name of a table is the name if the class. Remember a class is a table without rows. A class represents the rows that will exist. When the class acquires rows it is called a collection. A collection is also called an object.

Use of DatabaseWhen you have a lot of information, in order to use it you have to keep it in a database. In a database the information is kept in an organized format and can be easily and better accessed. In the past, database was kept in paper files in directories. As time went on, they were kept in files in directories in computers. A program was used to access the data in the files.

Today, you have what is called a Database Management System, abbreviated DBMS. DBMS is computer software. You have relational DBMS and object DBMS. The relational DBMS keeps the data as tables (related). The object DBMS keeps the data as related collections (classes). Today programs are also written to address DBMS and access the data.

At this point you should have a good feel of what a database is. Let us stop here and continue in the next part of the series.

Chrys

???????