Database Elements

/Database Elements

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. it can be stored in pieces of paper or electronic memory, etc. A database is an organised collection of data, so that it can be easily accessed and managed. It can organise 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, Informix, PostgreSQL, SQL Server, etc. SQL or Structured Query Language is used to operate on the data stored in a database.

 

Database components

A database is made up of several main components.

  • Rows
    It represent each record. A row is the smallest unit of data that can be inserted into a database. Rows 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
    It contain the definition of each field. You give each column a name, so that it is 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
    It is a 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
    A database schema 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. These formulas are expressed in a data definition language, such as SQL. As part of a data dictionary, a database schema indicates how the entities that make up the database relate to one another, including tables, views, stored procedures, and more.
  • Table
    A database table is a structure that organises data into rows and columns – forming a grid. Tables are similar to a worksheets in spreadsheet applications. The rows run horizontally and represent each record. The columns run vertically and represent a specific field. The rows and columns intersect, forming a grid. The intersection of the rows and columns defines each cell in the table.
  • 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. While workflow and tables are optional for database entity, properties are required.
    An entity is an object that exists. It doesn’t have to do anything; it just has to exist. An entity can be a single thing, person, place, or object. In database administration, only those things about which data will be captured or stored is considered an entity.  If you’re creating a database of your employees, examples of entities you may have 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. An entity will have zero or more attributes, and each of those attributes apply only to that entity.

 

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. Comman 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 you to identify a row in a relation (table). They allow you to find the relation between two tables. Keys help you 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 you 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 you to establish a relationship between and identify the relation between tables
  • Help you to enforce identity and integrity in the relationship.

 

Common types of keys with different functionality:

  1. Super key
    A Super key is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification.
  2. Primary key
    A column or group of columns in a table which helps us to uniquely identifies every row in that table is called a primary key. This DBMS can’t be a duplicate. The same value can’t appear more than once in the table.
    Rules for defining Primary key:

    • Two rows can’t have the same primary key value
    • It must for every row to have a primary key value.
    • The primary key field cannot be null.
    • The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.
  3. Candidate key
    A super key with no repeated attribute is called candidate key. The 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
  4. Alternate key
    It is a candidate key which is currently not the primary key. However, A table may have single or multiple choices for the primary key.
  5. Foreign key
    A foreign key is a column which is added to create a relationship with another table. Foreign keys help us to maintain data integrity and also allows navigation between two different instances of an entity.
  6. Compound key
    Compound key has many fields which allow you to uniquely recognize a specific record. It is possible that each column may be not unique by itself within the database. However, when combined with the other column or columns the combination of composite keys become unique.
  7. Composite key
    A composite key is a 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.
  8. Surrogate key
    An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key. They do not lend any meaning to the data in the table. Surrogate key is usually an integer.

 

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. We can say that the Teacher_ID foreign key has helped to establish a relationship between the Courses and the Teachers tables.

// 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. They are:

  1. 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. This kind of relationship can be implemented in a single table and therefore does not use a foreign key.
    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.
  2. One-to-Many (or Many-to-One)
    A 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. This is by far the most common relationship that exists between a pair of tables in a database, and it is the easiest to identify. It is crucial from a data-integrity standpoint because it helps to eliminate duplicate data and to 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.
    graphics/10fig09.gif
  3. 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. This is the second most common relationship that exists between a pair of tables in a database. It can be a little more difficult to identify than a one-to-many relationship, so you must be sure to examine the tables carefully.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.graphics/10fig13.gif

 

Transaction

A transaction can be defined as a group of tasks. A single task is the minimum processing unit which cannot be divided further. Let’s take an example of a simple transaction. Suppose a bank employee transfers Rs 500 from A’s account to B’s account. This transaction involves several low-level tasks.

// Operation in A's Account
Open_Account(A)
Old_Balance = A.balance
New_Balance = Old_Balance - 500
A.balance = New_Balance
Close_Account(A)

// Operation in B's Account
Open_Account(B)
Old_Balance = B.balance
New_Balance = Old_Balance + 500
B.balance = New_Balance
Close_Account(B)

 

All types of database access operation which are held between the beginning and end transaction statements are considered as a single logical transaction. During the transaction the database is inconsistent. Only once the database is committed the state is changed from one consistent state to another.

Important about Database Transactions

  • A transaction is a program unit whose execution may or may not change the contents of a database.
  • The transaction is executed as a single unit
  • If the database operations do not update the database but only retrieve data, this type of transaction is called a read-only transaction.
  • A successful transaction can change the database from one CONSISTENT STATE to another
  • DBMS transactions must be atomic, consistent, isolated and durable (ACID).
  • If the database were in an inconsistent state before a transaction, it would remain in the inconsistent state after the transaction.

 

A database is a shared resource accessed. It is used by many users and processes concurrently. For example, the banking system, railway, and air reservations systems, stock market monitoring, supermarket inventory, and checkouts, etc. For maintaining the integrity of data, the DBMS system you have to ensure ACID properties.

  • Atomicity: A transaction is a single unit of operation. You either execute it entirely or do not execute it at all. There cannot be partial execution.
  • Consistency: Once the transaction is executed, it should move from one consistent state to another.
  • Isolation: In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.
  • Durability: · The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

 

SQL Data Types

Data types define what type of data a column can contain. Data types mainly classified into three categories for every database.

  • String Data types
    1. CHAR(Size) : It is used to specify a fixed length string that can contain numbers, letters, and special characters.
    2. VARCHAR(Size): It is used to specify a variable length string that can contain numbers, letters, and special characters.
    3. BINARY(Size): It is equal to CHAR() but stores binary byte strings.
    4. VARBINARY(Size) : It is equal to VARCHAR() but stores binary byte strings.
    5. TEXT(Size): It holds a string that can contain a maximum length of 255 characters.
    6. TINYTEXT: It holds a string with a maximum length of 255 characters.
    7. MEDIUMTEXT: It holds a string with a maximum length of 16,777,215.
    8. LONGTEXT: It holds a string with a maximum length of 4,294,967,295 characters.
  • Numeric Data types
    1. BIT(Size): It is used for a bit-value type. The number of bits per value is specified in size.
    2. INT(size): It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255.
    3. INTEGER(size): It is equal to INT(size).
    4. FLOAT(size, d): It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter.
    5. FLOAT(p): It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().
    6. DOUBLE(size, d): It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter.
    7. DECIMAL(size, d): It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter.
    8. BOOL: It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.
  • Date and time Data types
    1. DATE: It is used to specify date format YYYY-MM-DD. Its supported range is from ‘1000-01-01’ to ‘9999-12-31’.
    2. DATETIME(fsp): It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss.
    3. TIMESTAMP(fsp): It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch(‘1970-01-01 00:00:00’ UTC). Its format is YYYY-MM-DD hh:mm:ss.
    4. TIME(fsp): It is used to specify the time format. Its format is hh:mm:ss.
    5. YEAR: It is used to specify a year in four-digit format.
August 15th, 2019|Categories: Programming|Tags: |
avatar
  Subscribe  
Notify of