Term Paper Relational Database

TermPaper: Relational Database

Tablesin Relational Databases

Arelational database is describes a collection of items of data thatare arranged in tabular form[ CITATION Par12 l 2057 ].Data from these tables can be reassembled or accessed in various wayswithout necessary having to rearrange the database tables. Therefore,relational databases play a crucial role in eliminating dataduplication as the data within a table can be separated intodifferent fields using a primary field that can be used in creatingrelationships with other tables.

Thedatabase is characterized by various features made of integratedsearch, calculated fields, customized templates, import/exportbetween databases, relational capability, field types andcustomizable print reports and views[ CITATION Lev12 l 2057 ].These elements enable easy and quick access to information required.The major aspects critical for relational databases include:

  • Tables/entities

  • Fields/Attributes

  • Records

  • Queries

  • Forms

Thispaper provides an in-depth exploration of tables/entities inrelational databases.

Tables/Entities

Atable refers to rows and column structure that contains a collectionof data that is related. It comprises of a set of data values withinan enclosure (cell) formed by the intersection of rows and columns[ CITATION Tha13 l 2057 ].The number of columns in tables are specified, but rows can be asmany as may be required. A row is identified using value(s) in aspecified column subset. A primary key is a column subset thatidentifies a particular row. A table acts as a storage of data in adatabase, without which the database would be of little or noimportance.

Atable comprises of rows and columns.

Row(Record/Tuple)

Arow is a horizontal division of a table that is identified using aprimary key[ CITATION Tha13 l 2057 ].Two rows cannot have the same value of the primary key. This impliesthat each row can be selected by just knowing the primary key. Atable can have zero or multiple rows. The number of rows can be asmany as possible though this is subject to the nature of primary key.For instance, when ‘State’ is set as primary key, then the tableshould be restricted to a maximum of fifty rows since a primary keycannot have duplicates. In relational databases, the tables cannothave a first or last row. Rather, a first or last row can be teasedusing keywords such as TOP or LIMIT after retrieval and storage ofdata. This implies that a person can only observe the first row ofthe results, which, ideally, it is not the first one that isphysically stored within the table[ CITATION Tha13 l 2057 ].

Column(Attribute)

Acolumn refers to the vertical divisions of a table defined to hold aunique data type like numeric, date or text. A column is defined bydata type and name. Data type enhances validation of storedinformation while the name is used during selecting and ordering ofdata in SQL statements. The duplication of column names is notallowed in tables[ CITATION Her13 l 2057 ].

RelationalDatabase Concepts

Field

Afield holds actual values of a column. Fields represent smallerentities of a record.

RelationalDatabase Keys

Aprimary key

Agood primary key is not duplicated among different attributes.However, when natural attributes are used, surrogate keys are usedinstead. Natural attributes are used in describing the data that isbeing entered. A surrogate key refers to an artificial attributegiven to an object to uniquely identify it. For example, in a schoolsetting, a surrogate key can be the student ID so as to differentiatethem. The surrogate key may not have intrinsic meaning, but it isvery useful in identifying a row. A composite key is also anothercommon occurrence. This is a key that is made up of more than oneattribute in a table for unique identification of a record. Forinstance, the database relating classes, teachers, and students mayhave the ‘classes identified by a composite key of the time slotand room number since no any class that can have the exact samecombination. Such a composite key can act as a form of verification,though a weak one[ CITATION Tha13 l 2057 ].

ForeignKey

Thisrefers to the field that pairs the column containing primary key fromanother table. This helps in cross-referencing tables. This key makesefficient use of values of the attributes within the referencedrelation to restricting the domain of a single or multiple attributesfor referencing relation. In formal terms, all rows in referencingrelation that is projected on referencing attributes should have onerow within the referenced relation that allows referencing attributesmatch the values in referenced attributes[ CITATION Lev12 l 2057 ].

DefiningRelationship in Tables

Tablerelationship entails matching data in columns having the same name ineither table. Often, relationship links primary key in one table thatuniquely identifies each row to a foreign key entry in another table.The definition of foreign keys in tables necessitates modeling of therelationship between two or more tables. Tables can be related inthree main ways:

  1. One-to-One

  2. One-to-Many

  3. Many-to-Many

One-to-ManyRelationships

Thisis the most famous type of relationship used in relational databases.The relationship is ideal in cases where table ‘A` has several rowsthat match with those in table ‘B` while table ‘B` may have atmost one row with data that match those in table ‘A`. For instance,the tables labeled ‘Titles` and ‘Publishers` may only thisrelationship since one publisher can publish several titles but eachtitle can only come from a single publisher, or several children canhave one mother, but one child can only have one biological mother.

Insimple terms, one-to-many relationship happens when only one ofrelated column has a distinctive constraint or primary key. The sideof the primary key in this type of relationship is designated using akey symbol. On the other hand, the side of the foreign key of thisrelationship is shown using an infinity symbol.The table containing the primary key contains a single record that isnot related to any other, or several records within a related table[ CITATION Her13 l 2057 ].

TableRelationships

Many-to-ManyRelationships

Thisis the type of relationship where one row in one table have severalrows with data that match with other rows of the second table. Thisis established by defining a junction table. For instance, the‘Titles’ table and ‘Authors’ table have this type ofrelationship that is defined using one-to-many relationshipfrom each table to ‘TitlesAuthors` table. The third table,‘TitlesAuthors`, has the primary key comprising a combination ofthe author-ID column and title-ID column. Each recordin tables ‘C’ and ‘D’ and relate to one or multiple recordsin either table.

One-to-OneRelationships

Inthis relationship, the tables related have only one similar rows thatmatch with the other. A one-to-one relationship is rare since most ofthe information relation using this technique would be available inone table. Nevertheless, a one-to-one relationship is critical whendividing a table into multiple columns, storing temporary data,isolating a section of the table for the sake of security, or storinginformation that represents a subset of the primary table. A keysymbol is used in denoting the primary side of a one-to-onerelationship. Similarly, the side for the foreign key is also shownusing the key symbol[ CITATION Par12 l 2057 ].

Normalizationin Tables

Whendesigning tables, normalization takes place to help in reducing datacorruption and redundancy. The normalization process entails severalsteps normally referred to as ‘Normal Forms (NF)’. The steps varyfrom 1NF to 6NF, with the Boyce-Coddnormal form (BCNF) occupying the spot between the third and fourthNF[ CITATION Lev12 l 2057 ].

Atable is considered normalized after reaching 3NF beyond which aimingfor higher levels is unlikely. Nevertheless, a 3NF table has a highlikelihood of being in 5NF level. First three NF are simple todevelop[ CITATION Her13 l 2057 ].

FirstNormal Form

1NFis the most important step and also the simplest. The majorrequirement for this step is that there should be no any repeatingsets of data in the tables. This implies that identical pieces ofdata cannot be serialized into multiple identical columns or singlefields.

SecondNormal Form

2NFrequires that there should be no field that should depend partiallyon any candidate key within the table with the exception of primarykey. It includes all field combinations that identify rows in aunique way.

ThirdNormal Form

Thisis the last form to be considered for a table to be normalized. 3NFrequires that columns depend only on primary key. This implies that acolumn is not fully dependent on the primary key of the correspondingtable. In some cases, representing tables in 3NF can negativelyaffect the usability and performance of the table. Therefore, alldata from a table should, therefore, be queried before anynormalization. This should be done using the SELECT query. Ininstances when resource conservation and performance are moreimportant than data integrity, then a lower NF level would be ideal[ CITATION Tha13 l 2057 ].

Conclusion

Therelational databases form the dominant means through which structureddata is stored. They are designed to enhance retrieval and storage ofhuge quantities of data. Tables form a critical aspect of therelational database. A table refers to rows and column structure thatcontains a collection of data that is related. It comprises of a setof data values within an enclosure (cell) formed by the intersectionof rows and columns. The number of columns in tables are specified,but rows can be as many as may be required. A row is identified usingvalue(s) in a specified column subset. A primary key is a columnsubset that identifies a particular row. A table acts as a storage ofdata in a database, without which the database would be of little orno importance.

References

Hernandez, M. J. (2013). Database design for mere mortals: a hands-on guide to relational database design. Pearson Education.

Levene, M., &amp Loizou, G. (2012). A guided tour of relational databases and beyond. Springer Science &amp Business Media.

Paredaens, J., De Bra, P., Gyssens, M., &amp Van Gucht, D. (2012). The structure of the relational database model (Vol. 17). Springer Science &amp Business Media.

Thalheim, B. (2013). Dependencies in relational databases. Springer-Verlag.