Introduction to Information Technology/Relational Databases
Introduction
[edit | edit source]A relational database is a database which has a structure that can recognize the relations between data. This means that data within the database can be analyzed in many ways without needing to change the database tables. The basic structure of these databases is a set of tables organizing data into predefined categories. These databases are easy to add new data to, whether it is more information or a new data category. The standard way in which this system is interacted with is through the structured query language (SQL). This language is used to gather data and perform queries for information within the database.
Terminology
[edit | edit source]A Database is a collection of data organized so that it can be managed and updated. It is a set of schemas, tables, queries, reports, views and other objects.
The relational database was first defined in June 1970 by Edgar Codd, of IBM's San Jose Research Laboratory. Codd's view of what qualifies as an RDBMS is summarized in Codd's 12 rules. A relational database has become the predominant type of database. Other models besides the relational model include the hierarchical database model and the network model.
The table below summarizes some of the most important relational database terms and the corresponding SQL term:
| SQL term | Relational database term | Description |
|---|---|---|
| Row | record | A data set representing a single item |
| Column | Attribute or field | A labeled element of a tuple, e.g. "Address" or "Date of birth" |
| Table | Relation or Base relvar | A set of tuples sharing the same attributes; a set of columns and rows |
| View or result set | Derived relvar | query |
DBMS
[edit | edit source]DBMS stands for Data Base Management System. This type of software is used to interact with the database and access something stored in one. DBMS provides a way to see data that’s accessed by many users from multiple locations while limiting what data is visible. A few advantages of using a DBMS include the protection of your data, data will be easier to find and harder to lose if its organized in one place and activity in a DBMS is logged so you can see who accessed what and when.
Update
[edit | edit source]Modifying data within the database by inserting new data, deleting data, or modifying existing data.
Retrieval
[edit | edit source]Retrieving Data from the database and providing it to the user.
Administration
[edit | edit source]Performing any services to keep the data base running, secure, and recover lost or corrupted data. The services provided could be either registering and maintaining new users as well as enforcing data security. The people who handle these tasks are Database Administrators. So far there are at least 3 types of Database Administrators.
1. Systems DBAs (Database Administrator) - These admins focus on the physical aspects of administrating databases such as the DBMS installation, patching and upgrading the database, and general maintenance.
2. Application DBAs - These admins are in charge of managing the application components that access the database and configure the database management systems to work for users. They handle application patches and upgrades to this application software.
3. Development DBAs - They focus on development aspects of database administration. This can include data model maintenance and design, SQL writing, and generation of the DDL or data definition language.
What is a Key?
[edit | edit source]A key is a tool that links a piece of data to another table. A primary key is what links data to other tables. A primary key should not repeat and it can’t be “NULL”, blank, empty, or a zero when referring to values. A foreign key links an entire row of data to that table’s or another table’s primary key. This provides a link between data in two tables.
Other types of Keys
[edit | edit source]Keys are an important part of a relational database. They are used to establish and identify a relation between tables. They also ensure that each record within a table can be identified by a combination of one or more fields within a table. We also have a super key and the candidate key. The super key is a set of attributes within a table that identifies each record within a table. It is a superset of a candidate key. The candidate key is the set of fields from which primary keys can be selected. It is an attribute or set of attributes that can act as a primary key for a table to identify each record in that table. Keys that are not selected as primary keys are known as secondary keys or alternative keys.
Relationships
[edit | edit source]Types Of Relationships
[edit | edit source]one-to-one
[edit | edit source]A value in one table corresponds to only value on the related table. For example, a list Social Security Numbers that corresponds to a list of the people who go with those numbers.
one-to-many
[edit | edit source]A value in one table may correspond to many different values in the related table. For example, a list of parents and a list of their kids.
many-to-many
[edit | edit source]Many values in one table may correspond to many values in another table. For example, a list of siblings and a list of their siblings.
Entity-Relationship Diagrams
[edit | edit source]An entity-relationship diagram is a representation of a system of information that shows the relationship between people, objects, places, concepts or events within a system in the form of a graph. One would usually use an ER model if they wanted to analyze, define, and describe what is important to processes in an area of a business. They are a series of entities and relationship. Entities are capable of existing independently from anything else, that can also be identified. A relationship shows how each entity is related to another.
Junction Tables
[edit | edit source]Junction tables (aka Bridge Tables) are tables designed to handle many-to-many relationships between two groups. They create "junctions" between data sets, allowing associations with one another. For instance, if a data set had a group for people's names and a group for people's classes, a junction table would allow correlations between the two. This is a many-to-many relationship since more than one student can be in a single class, and one class can have several students.
Constraints
[edit | edit source]Constraints make it possible to further restrict the domain of an attribute. For instance, a constraint can restrict a given integer attribute to values between 1 and 10. Constraints provide one method of implementing business rules in the database. SQL implements constraint functionality in the form of check constraints. Constraints restrict the data that can be stored in relations. These are usually defined using expressions that result in a boolean value, indicating whether the data satisfies the constraint. Constraints can apply to single attributes, to a tuple (restricting combinations of attributes), or to an entire relation. Since every attribute has an associated domain, there are constraints (domain constraints). The two principal rules for the relational model are entity integrity and referential integrity.
Normalization
[edit | edit source]Database normalization is a systematic process of organizing data in columns and rows. Its purposes are to cut data redundancy (to avoid anomalies) and make sure the data is stored. It is important to reduce or eliminate data redundancy as it makes it difficult for application developers to store objects in a relational database. A direct benefit of data normalization is the performance of the database systems are fast, accurate, and efficient. As a result, you can get a quick response from the database.
Cardinality
[edit | edit source]Cardinality pertains to the uniqueness of data values within a column. Low cardinality refers to a column containing several repeated values and high cardinality represents when a column has several unique values. It is also referred to the relationships between tables. These relationships are one-to-one, one-to-many, and many-to-many. Cardinality is important because it links tables together with precision.
Index
[edit | edit source]An Index is a type of data structure that helps reduce the amount of time for retrieval operations on a database table. Indices give the user the ability to locate any data they seek without having to search every row in a database table by hand. Database indices are set up to use queries that filter using attributes that can find matching sequences using the index. Although indices are usually not considered part of a database they are essential in working with databases.