Paper Example on Database Management Concepts

A database may be defined as a collection of interrelated data stored together to serve various purposes (Albert, Cabot, Gomez, & Pelechano, 2011). A Database Management System (DBMS) refers to software that is responsible for storing, maintaining and utilizing databases (Brodie & Mylopoulos, 2012). Thus, a database system refers to a database along with a Database Management System. In a relational database model, data is organized into rows and columns of a table. The term relation is used to refer to a table, while the term tuple is used to refer to a row. The term attribute refers to a column of a table. The set of allowed values for each attribute is called the domain of the attribute (Albert, Cabot, Gomez, & Pelechano, 2011). Attribute values are required to be indivisible (atomic). The domain is atomic if its elements are considered to be indivisible. Examples of non-atomic domains include set of names, composite attributes and identification numbers that can be broken up into parts. A relational schema R is in the first normal domain if the domains of all the attributes of R are atomic. Non-atomic values complicate storage and encourage redundant storage of data. The special value null is a member of every domain. The null value causes complications in the definition of many operations. Relation instance refers to a specific instance of a relation containing a specific set of rows. SQL data allows the specification of information regarding relations i.e. the schema for each relation, domain values associated with each attribute, integrity constraints, indices to be maintained for each relation, physical storage structure etc. (Adamski, 2011).

A view is a kind of table whose contents are taken from other tables depending upon a condition. The view does not contain data of their own. The content of a view is derived from a table and that table is called BASE TABLE (Albert, Cabot, Gomez, & Pelechano, 2011). These are the tables that actually contain data. Once a view is defined, the view name can be used to refer to the virtual relation that the view generates. A view definition causes the saving of an expression; the expression is substituted into queries using views. Views extend the control over data and they are an excellent way to allow users to some but not all the information in the table (Xiaofeng & Xiang, 2013).

A database system would have a primary key (every relation) refers to unique attributes that identify tuples. If attribute values are sufficient to identify a unique tuple of each possible relation it is called a super key. The primary key does not have duplicate values in the same relation (i.e. it is non-redundant). In addition, all attributes that can identify the tuples are candidate keys for primary keys, otherwise, it is known as an Alternate Key. A foreign key (whose values are derived from the primary key of some other table) represents relationships in Foreign tables or Detail tables (value in one relation must appear in another), and the table that defines the Primary-key, which the foreign-key or detail-table refers to, is called Primary table or Master Table. A system of rules that a database management system uses to ensure that relationships in related tables are valid, and users do not accidentally delete or change related data is called referential integrity. The foreign key plays an integral role in referential integrity in database management system (Coronel & Morris, 2016). The purpose of this paper is to explore the above database design concepts and the relevance to the real world applications.

Critical Analysis of the Concepts

The theoretical framework mostly applied in database design and development is the relational theory conceived by E. F. Codd in 1969. The relational theory is based on mathematical branches referred to as set theory (Kunen, 2014) and predicate logic (Hodges, 2001). The basic concept behind the relational theory is that a database consists of a series of relations that can be manipulated using certain operations that return tables (Litwin, 2003). This framework was in vast contrast to the more convention theories of database design and development that were much more complicated, rigid (nonflexible) and dependent on the physical storage methods of the data (Litwin, 2003). According to many peoples thinking, the term relational in this framework is derived from the fact that tables are related together in a relational database. Although convenient, however, this thinking is not accurate because the word relational defines a related set of information. In fact, relational database theorists apply the terms relations, attributes and tuples contrary to many peoples use of the common terms such as tables, columns, and rows, respectively.

Databases and database management systems can use the relational framework whose fidelity can be determined using the 12 rules opined by Codd (cited in Wade & Chamberlin, 2012). These 12 rules have since been expanded to 300 to increase the fidelity of databases and their management systems. However, previous research suggests that many database management programs are still a long way from meeting all Codds rules completely (Wade & Chamberlin, 2012).

The relational model can be applied to database design. However, when designing a database, decisions have to be made concerning taking a system in the real world and model it in a database. The decision-making process comprises selecting the types of tables, their columns, and the relationships between the tables. Based on this requirement, it follows that the design and development of a well-refined database take time and effort. While it would be nice if this process was totally intuitive and obvious, or even better automated, this is simply not the case. A well-designed database takes time and effort (Wade & Chamberlin, 2012).

The end product of a relational model design offers many benefits such as efficient entry of data, retrieval, deletions, updates, summarization, and reporting. Additionally, the behavior of a database created using this model is predictable, self-documenting, and each-to-make changes to the database schema (Wade & Chamberlin, 2012).

Tables, Uniqueness, and Keys

According to the relational theory, tables represent "things" in the real world (Chen, 2012). If table represents an entity in the real world, then it is logical that each table should represent only one entity. Entities refer to the objects or events in the real world. For instance, a client, an invoice, or an item may be the object in the real world. On the other hand, an event could be telephone calls, orders, matches etc. These objects or events are entered into tables which comprise rows and columns. It is a requirement in the relational theory that each row in a table is unique in order to uniquely address a given row through programming (Aleksic, Ristic, Lukovic, & Celikovic, 2013). Duplication of rows presents ambiguity and challenges which could otherwise be avoided. The uniqueness of any given table is guaranteed via designating a primary key for the table. A primary key refers to the column in a table that contains unique values. Although several columns in a table can contain unique objects or entities, each table can have only one primary key. All attributes in columns that can identify the tuples are candidate keys for primary keys. Otherwise, all the other candidate key columns are known as Alternate Keys. Additionally, depending on the number of columns, keys may be simple (made up of one column) or composite (made up of two or more columns) (Aleksic, Ristic, Lukovic, & Celikovic, 2013; Chen, 2012).

It is suggested that there is no cardinal rule (cardinality) for deciding which candidate key is the primary key for a table. He argues that the decision about what ought to be a primary key should be based on principles of minimality, stability, and simplicity or familiarity. In other words, the choice should be based upon the fewest columns necessary, a key that does not change often, and a simple and familiar key. Nevertheless, it is best to use an arbitrary static number as a primary key in most situations rather than a descriptive text to avoid the misspelling and name change problems (Aleksic, Ristic, Lukovic, & Celikovic, 2013).

Foreign Keys and Domains

The significance of primary keys is to relate tables in a database. They are essential when multiple tables need to be joined together in a relationship, otherwise, a table would not make much sense. A foreign key (whose values are derived from the primary key of some other table) represents relationships in Foreign tables or Detail tables (value in one relation must appear in another), and the table that defines the Primary-key (Wu et al., 2014).

It is noteworthy that primary keys and foreign keys share a common meaning and their values are derived from the same domain (Litwin, 2003). Pools of values from which columns are drawn constitute a domain. This can be conceptualized as user-defined columns that must follow certain rules and certain operations that can be performed on those columns. Databases such as Microsoft Access may support domains only partially to limit the users from sharing the same types of data such as number, text, date et cetera (Coronel and Morris, 2016).

Relationships

Foreign keys are defined in a database to create relationships in the real world. In the real world, relationships between objects or entities can be quite complex in which many of these entities have multiple relationships with each other. A family, for instance, involves has multiple relationships between different individuals at the same time. Only relationships between pairs of tables are considered in a relational database. The relationship between these tables can be one-to-one, one-to-many or many-to-many (Thalheim, 2013). It is difficult to find a one-to-one relationship in the real world because is usually created to get overcome some limitation of a DMS rather than to model a real-world situation. These relationships may be useful when a table needs to be split into two or more tables due to an arising security situation or performance problems. In One-to-Many Relationships (also called parent-child or master-detail relationships), however, two tables are related if "for every row in the first table, there can be zero, one, or many rows in the second table, but for every row in the second table there is exactly one row in the first table (Litwin 2003). Many-to-Many relationships in particular present various problems to database design and development. This relationship has the tendency of concealing areas of poor understanding (hidden entity). Consequently, some databases do not allow the creation of many-to-many relationships. Many-to-Many relationships are eliminated by identifying and adding the hidden entity to the database, which then has a One-to-Many relationship with the existing entity. In other words, Many-to-Many relationships must be broken into several one-to-many relationships in order to model the real world situations (Coronel and Morris, 2016).

Normalization

Normalization is an extremely important concept of database design and development. The design of a database involves a series of choice such the number of tables to be present, the columns present in which table, and the type of relationships between the tables (Ma & Yan, 2010). Normalization simplifies the database design in order to achieve the optimal structure. The concept of normal forms is articulated by the normalization theory. There are various normal forms including first, second, third, Boyce Codd, fourth, and fifth normal forms. These forms provide a linear pr...