Tables and Keys

As already mentioned, tables and the relationships among them are the elements or components of the set that declare a relational database. In order to create collection of values that inform us about entities with specific characteristics (attributes) we create tables.

Tables with columns and rows Each column represents a piece of information (one field) that we want to keep for the specific entity which the table describes. As we mentioned earlier every column-field represents an attribute of the object-entity. Each column is declared so as to be able to store a unique data type. Each column accepts only one predefined type of data, INT for integer, or VARCHAR for a variable number of characters up to a defined limit. • Domain is the set of values that a column can contain • A unique name is assigned per each column in a table • There is no limit for the number of columns per table, but the norm is 255 Each row defines a single data-record in the table • Rows are in no particular order • There are no constraints on the number of rows (data-records) in a table Look at the following example which gives us a table based on a simple payroll list ordered by employee identification numbers. Table: "employees_payroll" This table keeps information about the employees payroll of a small firm and consists from four rows and four columns. Thus, we can say its dimensions are (4x4). Every column has a certain data type and every row is divided into four fields (columns). Thus, SQL gives us the possibility to submit queries to collect all values with a common data type and definition. Additionally, we can select one or more columns of a table in order to get all data-values they content. The intersection between rows and columns in a table in connection with the access through primary and foreign keys is the great power of SQL. Datatypes Every column in a table must have a datatype. During the data analysis phase the database developer chooses the best datatype for each attribute that is going to be defined as a characteristic for each table. For example attributes that are going to be used as people names such as names of students or employees must be defined as character strings with a defined length. Thus, the suitable datatype for names would be varchar(50). With this definition we put the maximum size of characters for this specific variable or field (attribute) to 50. Remember that only the actual length of text characters from the defined variable varchar is stored in the database and not the maximum length of its declaration. Keys, Primary and Foreign Keys A key is a column that can be used to identify a row in a table. If we use a set of columns to identify a row in a table then this set is called a super key. For example, look at the "employees_payroll" table. We could use the id_number and the last_name together to identify any row in the table. We could also use the set of all the columns ( id_number, first_name, last_name, Salary). These are both super keys. (i) Primary Keys A primary key is a column of the table which must contain unique data values. Every table should have a primary key. In our employees payroll table the primary key is the column id_mumber which identifies all records (rows) of the table for unique employees with unique values. Look at the following entity "employees_payroll" The id _number enables first _name, last _name, Salary to be found. The id _number is the Identifier or Primary Key of entity "employees_payroll" The primary key attribute is underlined. It cannot take a NULL value Primary Keys are very important and have significant impact on performance of database. Additionally keys are needed to guarantee the data integrity. Especially: • You cannot refer to rows by location (first, last, third, etc.) as rows have no specific order • Every table must have at least one column declared as primary key (you can declare more than one) • In a column marked as a primary key every row has a unique value as content (ii) Foreign Keys The foreign key of a table is used to create a connection between two tables. The value of a foreign key that a table keeps is always the primary key of the table with which it has established a relationship. If we suppose that we have a database with two tables, one for employees payroll with the name "employees_payroll" and a second one with the name "employees_overtime" for employees who worked overtime during the month. Then the relationship between the two tables is created by the foreign key in the table of "employees_overtime". The entity of "employees_overtime" will be: The id_overtime is the Identifier or Primary Key of entity "employees_overtime" The primary key attribute is underlined .The primary key cannot take a NULL value The employee_id is the Foreign Key of entity "employees_overtime" which must be identical with the Primary Key of entity "employees_overtime" The foreign key attribute is underlined with a broken line The id_overtime enables hours, overtime_date, employee_id to be found. Entities and Types of Table Relationships SQL is a relational database language which supports databases that consist of a set of relations. A relation between two or more tables of data is defined as a connection among them. Note that the terms table and relation mean the same thing. In our approach to this matter we will use the more common term table. Entities, Objects, Classes and Inheritance Before we start to design a data model we have already decided for which entities we will store information in our database and the relationships among them. Entity or object is a real thing that exists in the real world. An example of entities or objects are employees, overtime work hours, cars, books etc. These entities or objects are composed of attributes that they give them specific behavior that characterize them. An Entity is something of significance to the system about which information is to be held. Objects have attributes (variables) and encapsulate into them values which only through specific methods (functions) can we change. Thus, we can have relational databases that consist of objects. In an object orientation we can correspond the table with the class and the entities inside the table with objects. A class is a named collection of object instances. Each instance must have a unique object identifier (OID). Inheritance is implemented in object-oriented systems and means that a class which is in a higher level of the hierarchy structure can inherit its attributes or its methods (functions) to the lower class. The lower class inherits all information from the upper class. Our next step is to implement additional features to this class. Look at the following example to understand the inheritance process better: Let us assume that we have a table containing information about students. We define a class (table) that stores all information about a student that is common for students, such as the first name, second name, age and school. Additionally we define a class (table) for a specific type of student that is used to store additional information, such as student examination results. The second class for student examination results inherits all information from the parent class which holds information about the students. Schemas, Relationships and Notations The database schema simply shows the structure of a database with tables. Also it means the design of the database or the form of the database without any data in it. To describe the database schema of the previous example with the two tables we use the mathematical type of multi-variable function. employees_payroll(id_number,first_name, last_name, Salary) employees_overtime(id_overtime,hours,overtime_date,employee_id) The expressions above describe a database schema with two entities-tables which are defined by a set of attributes respectively. Each attribute name in parenthesis defines a column in the specific table and operates as independent variable of the function which is the entity-table. To clarify how entities or objects are stored to the specific entity-table you can think in terms of set theory in which the entity-table defines a specific set (or category) of elements (entities or objects) with the same attributes (characteristics). Thus, the data of each row of the entity-table gives you a unique entity or object from the same set (category). A relationship is an association between two entities which is important to the system. In the real world entities have relationships with other entities.Thus, relationships define access from one table-entity occurrence to another and under a specific type of connection.  These relationship types are the following • One-to-One • One-to-Many (or many-to-one depending on the  direction you are looking at it from) • Many-to-Many. A one-to-one relationship connects exactly two table-entities. If each employee in an organization had a laptop, this would be a one-to-one relationship. If every employee in an organization works for more than one department, this would be many-to-many relationship. When you are beginning with a database design, you must define the relationships of the entities of the system you are modeling. Do not forget that two systems are not going to be exactly the same. In the following table we present the main and most popular data modeling notations through which we can define the relationships between entities. Finkelstein in 1989 introduced a simple and easy symbolic way to read the relationships between the table data models. This notation way is well suited for high-level logical data modeling. But it does not support the identification of an entity attributes. Barker in 1990 presented his own way of notation that today is one of the most popular. In this book we are going to use this way of tables associations and relationships. The way of Barker offers a very useful notation for entities which are embedded into other entities as sub-types into super-types which is the same with the set theory symbolism as well as a subset is included in another set as an element. This approach to subtypes of relationships (entities) is a great advantage as long as it supports the inheritance through hierarchy of several levels deep. The UML data modeling notation is not accepted by the IT industry as a notation standard yet. But considering the popularity of the UML, and the efforts of the Object Management Group (OMG) the recognition will come very soon. In our tables relationships examples that follow we will use underline to define an attribute as primary key and a broken underline with a star to define attributes as foreign keys.