R:BASE 11 Beginners Tutorial

The following are steps in designing a useful, efficient database, which are discussed in the following section:

 

•List the objects

•List the facts about the objects

•Turn the objects and facts into tables and columns

•Determine the relationships among objects

•Determine the key columns

•Determine the linking columns

•Determine the relationship constraints

•Evaluate the design

 

List the Objects

Make a list of all the objects, which are the themes or subjects about which you want to track information. For the SkyWrite information system, there are three objects:

 

•Employees

•Customers

•Flights

 

List the Facts about the Objects

Each object can have a great deal of information associated with it. List all of the facts, or information, about an object.

 

 

Turn the Objects and Facts into Tables and Columns

In relational database design, objects become tables, and facts become columns. Each table contains information about a single object or event. Employees and customers are examples of objects. Performing a task like flying is an example of an event. Each of these objects or events becomes a separate table in the database.

 

Each table is made up of one or more columns. A column is a specific fact about the object or event; for example, an employee's last name is a fact about employees, or the date of flight is information about flights. Each group of facts is stored in a row, which is a collection of values that are unique to a particular instance for that table; for example, all the facts about one employee.

 

The object of the table shown here is employees; the facts are individual pieces of information about employees.

 

 

 

When turning facts into columns, you need to consider two things:

 

The column name - When naming a column, use a name that describes the data the column will hold. A column name can be up to 128 characters long. It must begin with a letter, and can contain letters, numbers, and the symbols # and _. R:BASE reserved words cannot be used as column names. For a complete list, see the  "Reserved Words" entry in the R:BASE Help Reference Index.

 

The column's data type - The type of data you'll store in a column determines the column's data type (the available data types are described below). Determining the column's data type now will make creating the database easier. Use the list below to determine the correct data types for your columns.

 

 

The following is a list of the preliminary tables, columns, and data types for the SkyWrite database:

 

Employee

 

Customer

 

Flights

 

At this stage in the design, it helps to draw a simplified sketch of the database. For example, you can draw one box for each table. In later steps you can fill in key columns and draw the relationships between the tables.

 

Determine the Relationships Among the Objects

Using the simplified sketch above, look at the tables and determine how they might be related.

 

•Are employees related to flights? Yes, they are; employees schedule the skywriting flights. Therefore, draw a line connecting the Employee and Flights boxes.

 

•Are customers related to flights? Yes; there wouldn't be any flights if a customer did not order some skywriting. So, draw a line connecting the Customer box and the Flights box.

 

•Are employees related to customers? They might be, but in this case they are only related when a flight is scheduled. Therefore, you would not directly relate Employee with Customer.

 

You now know how each object or table is related; next determine the type of relationship. There are three types of relationships among tables: one-to-one, one-to-many, and many-to-many. They are defined as follows:

 

•One-to-one relationships

For any given row in Table A, there is only one row in Table B. For any given row in Table B, there is only one row in Table A. Usually, all the columns are in one table for this relationship.

 

•One-to-many relationships

For any given row in Table A, there are many rows in Table B. For any given row in Table B, there is only one row in Table A. Usually, two tables are used linked by a common column with values that match across the tables. The common column is known as the key column.

 

•Many-to-many relationships

For any given row in Table A, there are many rows in Table B. For any given row in Table C, there are many rows in Table B. Usually, three tables are used in this relationship with two of the tables using a common column in relation to a third. These two tables do not link directly to each other; instead, each of these link to a third table in a one-to-many relationship by a common key column with values that match.

 

The next step in determining the types of relationships among tables is to list every table and how it relates to any others. For example:

 

•Employee is related to Flights

•Customer is related to Flights

 

The relationship between the Employee and Flights tables is a one-to-many relationship. That is, if you look at a single row in the Employee table, there should be many rows of data in the Flights table for that employee;one employee can schedule many flights. If you then look at a single row in the Flights table, it will point to only one employee in the Employee table; a flight is scheduled by one employee.

 

Perhaps your reaction now is to say that employees are related to customers, too, and therefore the Employee table is related to the Customer table. The first statement--employees are related to customers-is true, but only when a flight occurs. All flights are recorded in the Flights table, and therefore it is the Flights table that shows the pertinent relationship.

 

An easy way to identify the type of relationship is to ask whether a specific row in Table A can point to (link with) one or many rows in Table B, then reverse the table order and ask the question again.

 

•A customer can point to one or many flights? Many.

•A flight can point to one or many customers? One.

 

The relationship between the Flights table and the Customer table is one-to-many.

 

•An employee can point to one or many flights? Many.

•A flight can point to one or many sales employees? One.

 

The relationship between the Flights table and the Employee table is one-to-many. There are no one-to-one or many-to-many relationships in the SkyWrite database.

 

You can represent these relationships in your sketch.

 

 

Determine the Key Columns

A key column is one that contains a unique value for every row in the table. Key columns are used to ensure the accuracy of your data and improve the speed of your database's performance. The value in a key column can be an account number, social security number, part number, license number, or any other numeric value or combination of characters that will be different for each row in a table.

 

One good example of a key is an employee identification number. Sometimes the number is a social security number; sometimes it is another unique number assigned by the payroll department. There is a direct relationship between an employee's identification number and the rest of the data about that employee. If you know an employee's identification number, you can easily find his or her name, address, and other information.

 

A column is effective as a key only if no two rows in the table have the same value in that column. You can use a company name as a key if it is universally unique. However, if there is any possibility that another company could have the same name, then it is not unique and must not be used as a key column. Do not use any column as a key when the possibility of a duplicate exists.

 

All of the tables in the SkyWrite database will use an integer value as the key value. The key to the Employee table will be the EmployeID column. The key to the Customer table will be the CustomerID column. The key to the Flights table will be the FlightID column.

 

 

Determine the Linking Columns

Remember that relational database models deal with data in the form of tables, and each of those tables represents a single object or event. The foundation of a relational database is that these tables relate to one another through common data contained in linking columns. Linking columns allow data from two or more tables to be retrieved and manipulated at the same time.

 

The best linking columns are those that will point to a unique row of data in the table. Since a key column is one that contains only unique values, it makes a good linking column. Linking columns are also called common columns.

 

For example, by knowing that the key column for the Employee table is EmployeID, and knowing that you want to track the employee who scheduled the flight, you can include EmployeID as a column in the Flights table. Once EmployeID is in the Flights table, you can look at any row in that table and, knowing the EmployeID values, determine the employee who is associated with that flight.

 

Where you place the links depends on the type of relationship between the tables. Once you know the type of relationship, it is easy to determine where to place the linking column.

 

The following rules will guide you in placing the links:

 

One-to-one relationships-The link should either be the most stable column or be taken from the table where the key column is created. The most stable column is the one in which the data is least likely to change.

 

One-to-many relationships-The linking column should come from the table on the one side of the relationship. The key column from the Employee table (one side) should be placed in the Flights table (many side). Similarly, one customer is associated with many flights. The key column from Customer (one side) should be placed in the Flights table (many side).

 

Determine the Relationship Constraints

Often the answers you get from a database come from more than one table. For example, if you want to know who the salesperson was for a particular flight, you determine the name by using the value in EmployeID to look up the correct row in the Employee table. The question of who the employee is can be answered only if there is a row in the Employee table with a number in EmployeID corresponding to the number in the Flights table. Similarly, the question of who the customer is can be answered only if there is a row in the Customer table with a number in CustomerID corresponding to the number in the Flights table.

 

To make sure that your database model works, you need to specify that no row can be added to the Flights table unless there are already corresponding rows in the Employee and Customer tables. These requirements are known as relationship constraints.

 

Note: R:BASE enforces relationship constraints by implementing primary and foreign keys, form design, and rules. Primary and foreign keys are discussed in Lesson Two. Form design is discussed in Lesson Four, and rules in Lesson Two.

 

Evaluate the Design

The last step in the design process is evaluating the design. Look for any design flaws that could cause the data to be unreliable or redundant.

 

You can evaluate every table by asking the following questions:

 

•Does each table have a single theme?

•Does each table have at least one key column?

•Is the table easy to add data to and retrieve data from?

•Are the columns unique to the table?

 

To evaluate the Customer table, for example, you might take the following approach:

 

•The table has a single theme: customers

•The table has a key: CustomerID

•The table is easy to use

•Each column name is unique to this table except for CustomerID, which is the linking, or common column, in the Flights table

 

You can evaluate the Flights table in the same way:

 

•The table has a single theme: scheduled flights

•The table has a key: FlightID

•The table is easy to use

•Each column name is unique to this table except for the key columns used as the links from the Customer and Employee tables