Introduction
Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. A database is an organized collection of data, so that it can be easily accessed and managed. It can organize data into tables, rows, columns, and index it to make it easier to find relevant information. The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data. There are many databases available like MySQL, Sybase, Oracle, MongoDB, PostgreSQL, SQL Server, etc. SQL or Structured Query Language is used to operate on the data stored in a database.
Basic element of a relational database are
- Tables
- Rows (or Records)
- Columns (or Fields)
- Keys
- Indexes
Database Components
Main components of database are
- Rows : Represent a record. A row is the smallest unit of data that can be inserted into a database, span multiple columns. A record is composed of fields and contains all the data about one particular person, company, or item in a database.
- Columns : Contain definition of each field. Each column describes the data that is stored. Examples of column names could include FirstName, LastName, ProductId, Price, etc. A field contains a single piece of data for the subject of the record.
- Cell: Part of a table where a row and column intersect. A cell is designed to hold a specified portion of the data within a record.
- Schema: It represents the logical configuration of all or part of a relational database. It can exist both as a visual representation and as a set of formulas known as integrity constraints that govern a database. It is expressed in a data definition language, such as SQL. A database schema indicates how the entities that make up the database relate to one another, including tables, views, stored procedures, and more.
- Table: It is a structure that organizes data into rows and columns – forming a grid. Rows run horizontally and represent each record. Columns run vertically and represent a specific field.
- Entity: It is a thing, person, place, unit, object or any item about which the data should be captured and stored in the form of properties, workflow and tables. In database of employees, examples of entities include employees and health plan enrollment. An entity attribute defines the information about the entity that needs to be stored. If the entity is an employee, attributes could include name, employee ID, health plan enrollment, and work location.
Attributes
Each entity is described by a set of attributes (e.g., Employee = (Name, Address, Birthdate (Age), Salary). Each attribute has a name, and is associated with an entity and a domain of legal values. Common types of attributes
- Simple attributes
Simple attributes are those drawn from the atomic value domains; they are also called single-valued attributes. In the COMPANY database, an example of this would be: Name = {John} ; Age = {23} - Composite attributes
Composite attributes are those that consist of a hierarchy of attributes. For example Address may consist of Number, Street and Suburb. So this would be written as → Address = {59 + ‘Meek Street’ + ‘Kingsford’} - Multivalued attributes
Multivalued attributes are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database, are the degrees of an employee: BSc, MIT, PhD. - Derived attributes
Derived attributes are attributes that contain values calculated from other attributes. For example Age can be derived from the attribute Birthdate. In this situation, Birthdate is called a stored attribute, which is physically saved to the database.
Keys
A DBMS key is an attribute or set of an attribute which helps to identify a row in a relation (table). They allow to find the relation between two tables. Keys help uniquely identify a row in a table by a combination of one or more columns in that table. Here, are reasons for using Keys in the DBMS system.
- Keys help to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys ensure that you can uniquely identify a table record despite these challenges.
- Allows to establish a relationship between and identify the relation between tables
- Help to enforce identity and integrity in the relationship.
Key Types
Common types of keys with different functionality:
- Super key: Group of single or multiple keys which identifies rows in a table. It may have additional attributes that are not needed for unique identification.
- Primary key: A column or group of columns in a table which helps us to uniquely identifies every row in the table.
- Candidate key: A super key with no repeated attribute is called Candidate Key. Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. Properties of Candidate key:
- It must contain unique values
- Candidate key may have multiple attributes
- Must not contain null values
- It should contain minimum fields to ensure uniqueness
- Uniquely identify each record in a table
- Alternate key: Candidate key which is currently not the primary key. A table may have single or multiple choices for the primary key.
- Foreign key: A foreign key is a column which is added to create a relationship with another table. It help to maintain data integrity and also allows navigation between two different instances of an entity.
- Compound key: It has many fields which allow to uniquely recognize a specific record. When combined with the other column or columns the combination of composite keys become unique.
- Composite key: Combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.
- Surrogate key: Artificial key which aims to uniquely identify each record. These kind of key are unique because they are created in absence of primary key.
Database Relationships
A relationship is established between two database tables when one table has a foreign key that references the primary key of another table. A primary key uniquely identifies each record in the table. A foreign key is another candidate key (not the primary key) used to link a record to data in another table. For example, consider these two tables that identify which teacher teaches which course.
Courses table's primary key is Course_ID. Its foreign key is Teacher_ID. Foreign key in Courses matches a primary key in Teachers. // Courses table Course_ID Course_Name Teacher_ID Course_001 Biology Teacher_001 Course_002 Math Teacher_001 Course_003 English Teacher_003 // Teachers table Teacher_ID Teacher_Name Teacher_001 Carmen Teacher_002 Veronica Teacher_003 Jorge
There are three types of relationships in relational database design i.e.
- One-to-One
- One-to-Many (or Many-to-One)
- Many-to-Many
One To One
A pair of tables bears a one-to-one relationship when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table.
A single record in Employees table is related to only one record in Compensation table, and a single record in Compensation table is related to only one record in Employees table. A one-to-one relationship usually (but not always) involves a subset table.
One To Many
One to many relationship exists between a pair of tables when a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table. It helps to eliminate duplicate data and keep redundant data to an absolute minimum. For Example, A customer can check out any number of videos, so a single record in the CUSTOMERS table can be related to one or more records in the CUSTOMER RENTALS table. A single video, however, is associated with only one customer at any given time, so a single record in the CUSTOMER RENTALS table is related to only one record in the CUSTOMERS table.
Many To Many
A pair of tables bears a many to many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table. For example, A student can attend one or more classes during a school year, so a single record in the STUDENTS table can be related to one or more records in the CLASSES table. Conversely, one or more students will attend a given class, so a single record in the CLASSES table can be related to one or more records in the STUDENTS table.
