Relational Data Model in DBMS: An Introduction

 

Relational Model:

 

The relational model represents data and relationships among data by a collection of tables, each of which has a number of columns with unique names. In a relational model the database is organized in fixed format.

 

Following figure shows a sample relational table. it consists of two tables, one is the employee information table and the other is the salary table for the particular employee information table.

 

Emp_Id	Emp_name	Emp_add	Emp_dept
101	Ajay	Wardha	Computer
102	Vijay	Nagpur	Mechanical
103	Sujay	Amravati	Civil

 

Fig: Employee information table

 

Emp_Id	Salary
101	30000
102	25000
103	20000

 

Fig: salary table

 

 

The properties of relational tables are as follows:

 

1.    Values are atomic.

 

2.    Each row is unique.

 

3.    Column values are of the same type.

 

4.    The sequences of columns are not important.

 

5.    The sequences of rows are also not important.

 

 

·      In a relational table, each column has a unique name.

 

·     Certain fields may be designated as keys which mean that searches for specific values of that field will use indexing to speed them up where fields in two different tables take values from the same set, a join operation can be performed to select related records in the two tables by matching values in those fields.

 

·      Often, but not always, the fields will have the same name in both table.

 

·      In above ex: the attribute Emp_ID can be a key (maybe a primary key) which can be indexed or sorted in an order to search specific value for Emp_ID.

Tabular Representation of Strong Entity, Weak Entity and Relationship Sets in DBMS

 

Tabular Representation of Strong Entity Sets:

 

Let ‘E’ be a strong entity set with descriptive attributes a₁, a_{2 . . .} a_n. We represent this entity by a table called E with ‘n’ distinct columns, each of which corresponds to one of the attributes of E. Each row in this table corresponds to one entity of the entity set E.

 

For ex: consider the entity set loan of the E-R diagram. It has two attributes: loan-number and amount. We represent this entity set by a table called loan, with two columns, as in below fig. We can add a new entity to the database by inserting a row into a table. We can also delete or modify rows.

 

Let D₁ denote the set of all loan numbers, and let D₂ denote the set of all balances. Any row of the loan table must consist of a 2-tuple (v₁, v₂), where v₁ is a loan (that is, v₁ is in set D₁) and v₂ is an amount (that is, v₂ is in set D₂). In general, the loan table will contain only a subset of the set of all possible rows.

 

We refer to the set of all possible rows of loan as the Cartesian product of D₁ and D₂, denoted by: D₁ × D₂

 

In general, if we have a table of ‘n’ columns, we denote the Cartesian product of D₁, D₂, · · ·D_n by: D₁ × D₂ × · · · × D_n−1 × D_n

Fig: Loan Table

Fig: Customer Table

Consider another ex. of entity set customer having attributes customer-id, customer-name, customer-street, and customer-city. The table corresponding to the customer has four columns, as shown in above fig.

Tabular Representation of Weak Entity Sets:

 

Let ‘A’ be a weak entity set with attributes a₁, a₂ . . . , a_m. Let ‘B’ be the strong entity set on which ‘A’ depends. Let the primary key of ‘B’ consist of attributes b₁, b₂ . . . b_n. We represent the entity set ‘A’ by a table called ‘A’ with one column for each attribute of the set: {a₁, a₂ . . . a_m} ∪ {b₁, b₂ . . . b_n}

 

For ex: consider the entity set payment in the E-R diagram. This entity set has three attributes: payment-number, payment-date, and payment-amount. The primary key of the loan entity set, on which payment depends, is loan-number. Thus, we represent payment by a table with four columns labeled loan-number, payment-number, payment-date, and payment-amount, as shown in below fig.

Fig: Payment table

Tabular Representation of Relationship Sets:

 

Let ‘R’ be a relationship set, let a₁, a₂ . . . a_m be the set of attributes formed by the union of the primary keys of each of the entity sets participating in ‘R’, and let the descriptive attributes (if any) of ‘R’ be b₁, b₂ . . .  b_n. We represent this relationship set by a table called ‘R’ with one column for each attribute of the set:

{a₁, a₂ . . . a_m} ∪ {b₁, b₂ . . .  b_n}

 

Consider the relationship set borrower in the E-R diagram. This relationship set involves the following two entity sets:

• customer, with the primary key customer-id

• loan, with the primary key loan-number

Since the relationship set has no attributes, the borrower table has two columns, labeled customer-id and loan-number, as shown in following fig.

Fig: Borrower table

Aggregation in DBMS

 Aggregation:

One limitation of the E-R model is that it cannot express relationships among relationships. To illustrate the need for such a construct, consider the ternary relationship works-on in following fig., between an employee, branch, and job.

Fig: E-R diagram for ternary relationship

Now, suppose we want to record managers for tasks performed by an employee at a

Branch i.e. we want to record managers for (employee, branch, job) combinations. Let us assume that there is an entity set manager. One alternative for representing this relationship is to create a quaternary relationship manages between employee, branch, job, and manager.

 

A quaternary relationship is required in this case because binary relationship between manager and employee would not permit us to represent which (branch, job) combinations of an employee are managed by which manager. Using the basic E-R modeling constructs, we obtain the following E-R diagram:

Fig: E-R diagram with redundant relationship

But, there is redundant information in the above fig. since every employee, branch, job combination in manages is also in works-on. The best way to model a situation such as the one just described is to use aggregation.

 

Aggregation is an abstraction through which relationships are treated as higher level entities. Thus, for our example, we regard the relationship set works-on (relating the entity sets employee, branch, and job) as a higher-level entity set called works-on. Such an entity set is treated in the same manner as is any other entity set. We can then create a binary relationship ‘manages’ between works-on and manager to represent who manages what tasks as shown in following fig.

Fig: E-R diagram with aggregation.

Constraints on Generalizations in DBMS

 Constraints on Generalizations:

 

For modeling an enterprise more accurately, the DB designer may place certain constraints on a particular generalization. One type of constraint involves determining which entities can be members of a given lower-level entity set. The membership may be one of the following:

a)    Condition-defined:

• In condition-defined lower-level entity sets, membership is evaluated on the basis of whether or not an entity satisfies an explicit condition or predicate.
• For ex: suppose that a higher-level entity set account has an attribute account-type. All account entities are evaluated on the defining account-type attribute.
• Only those entities that satisfy the condition account-type = “savings account” are allowed to belong to the lower-level entity set person.
• Similarly, all entities that satisfy the condition account-type = “checking account” are included in the checking account.
• Since all the lower-level entities are evaluated on the basis of the same attribute (on account-type), this type of generalization is said to be attribute-defined.

 b)   User-defined:

• User-defined lower-level entity sets are not constrained by a membership condition but, the database user assigns entities to a given entity set.
• For ex: assume that, after 3 months of employment, bank employees are assigned to one of four work teams.
• We therefore represent the teams as four lower-level entity sets of the higher-level employee entity set.
•  A given employee is not assigned to a specific team entity automatically on the basis of an explicit defining condition.
• Instead, the user in charge of this decision makes the team assignment on an individual basis.
• The assignment is implemented by an operation that adds an entity to an entity set.

A second type of constraint relates to whether or not entities may belong to more than one lower-level entity set within a single generalization. The lower-level entity sets may be one of the following:

a)    Disjoint:

• This constraint requires that an entity belong to no more than one lower level entity set.
• In our ex: an account entity can satisfy only one condition for the account-type attribute.
• It can be either a savings account or a checking account, but cannot be both.

b)   Overlapping:

• In overlapping generalizations, the same entity may belong to more than one lower-level entity set within a single generalization.
• Consider the employee work team example, and assume that certain managers participate in more than one work team.
• A given employee may therefore appear in more than one of the team entity sets that are lower-level entity sets of employee. Thus, the generalization is overlapping.
• As another ex: suppose generalization applied to entity sets customer and employee leads to a higher-level entity set person.
• The generalization is overlapping if an employee can also be a customer.

Lower-level entity overlap is the default case. A disjointness constraint must be placed explicitly on a generalization or specialization. We can note a disjointedness constraint in an E-R diagram by adding the word disjoint next to the triangle symbol.

 

A final constraint, the completeness constraint on a generalization or specialization, specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within the generalization/specialization. This constraint may be one of the following:

 a)    Total generalization or specialization:

• Each higher-level entity must belong to a lower-level entity set.

 

b)   Partial generalization or specialization:

• Some higher-level entities may not belong to any lower-level entity set.
• Partial generalization is the default.
• The total generalization in an E-R diagram is specified by using a double line to connect the box representing the higher-level entity set to the triangle symbol.

 

The account generalization is total: All account entities must be either a savings account or a checking account. When the generalization is partial, a higher-level entity is not constrained to appear in a lower-level entity set. The work team entity sets illustrate a partial specialization. The generalization of checking-account and savings-account into account is a total, disjoint generalization.

Attribute Inheritance in DBMS

 

Attribute Inheritance:

 

The attribute inheritance is an important property of higher and lower level entities created by specialization and generalization. The attributes of the higher-level entity sets are said to be inherited by the lower-level entity sets. For ex: customer and employee inherit the attributes of person. Thus, customer is described by its name, street, and city attributes, and additionally a customer-id attribute. While employee is described by its name, street, and city attributes, and additionally employee-id and salary attributes.

 

A lower-level entity set (or subclass) also inherits participation in the relationship sets in which its higher-level entity (or superclass) participates. Attribute inheritance applies through all tiers of lower-level entity sets.

 

Fig: Specialization and Generalization

The above fig. (Fig: Specialization and Generalization) depicts a hierarchy of entity sets. In the fig., employee is a lower-level entity set of person and a higher-level entity set of the officer, teller, and secretary entity sets. The entity sets in this diagram have only single inheritance. If an entity set is a lower-level entity set in more than one ISA relationship, then the entity set has multiple inheritance, and the resulting structure is said to be a lattice.

Block Diagram of Computer and Functions

 

What is a Computer ?

We can define a computer as a “programmable electronic device designed to accept data, perform prescribed mathematical and logical operations at high speed, and display the results of these operations”. A computer is a device that operates upon data. Data can be defined as a collection of facts and figures. Data can be anything like bio data of applicants when computer is used for short listed candidates for recruiting; marks obtained by students in various subjects when used for preparing results; details of passengers when used for making airline or railway reservations; or a number of different parameters when used for solving scientific research problems, etc.

 

Hence, data comes in various shapes and sizes depending upon the type of computer application. A computer can store, process, and retrieve data as and when desired. The fact that computers process data is so fundamental that many people have started calling it a data processor.

 

The activity of processing data using a computer is called as data processing. Data processing consists of three sub activities:

 

1.    Capturing input data

2.    Manipulating the data and;

3.    Managing output results.

 

As used in data processing, information is data arranged in an order and form that is useful two people receiving it. Hence, data is a raw material, used as input to data processing and information is processed data obtained as output of data processing.

 

No matter what shape and size of computer we are talking about, all computer systems perform the following operations for converting the input data into useful information and present it to the user. These operations are:

 

1. Inputting- process of entering data and instructions into a computer system.
2. Storing- saving data and instructions to make them more readily available for initial or additional processing as and when required.
3. Processing- performing arithmetic operations (add, subtract, multiply, divide, etc.) or logical operations (comparison like equal to, less than, greater than, etc.) on data to convert them into useful information.
4. Outputting- process of producing useful information for use, such as printed report or visual display.
5. Controlling- directing the manner and sequence in which the above operations are performed.

 Block Diagram of a Computer:

The internal architecture of computers differs from one system model to another. However, basic organization remains the same for all computer system. Following figure shows a block diagram basic computer organization.

 

The solid lines indicate flow of instruction and data, and dotted lines represent control exercised by control unit. It displays five major building blocks (functional units) of a digital computer system. These units correspond to the five basic operations perform by all computer systems.

Fig: Block diagram of a computer system

The functions of the 5 major building blocks can be described as follows:

Input Unit:

· Data and instructions must enter a computer system before the computer can perform any computation on the supplied data.

· The input unit that links a computer with its external environment performs this task.

· Data and instructions enter a computer through an input unit in a form that depends upon the input device used. 

· However, a computer's memory is designed to accept input in binary code and hence all input devices must transform input signals to binary codes.

· Units called input interfaces accomplish this transformation. Input interfaces match the unique physical or electrical characteristics of input devices to the requirements of a computer system.

 

· An input unit performs following functions:

1. It accepts or reads instructions and data from outside world.
2. It converts these instructions and data in a computer acceptable form.
3. It supplies the converted instructions and data to computer system for further processing.

Output Unit:

· An output unit performs the reverse operation of that of an input unit.

· It supplies information obtained from data processing to outside world. Hence, it links a computer with its external environment.

· As computers work with binary code, results produced are also in binary form. Therefore, before supplying the results to outside world, the system must convert them to human readable form.

· Units called output interface is accomplish this task. Output interfaces match the unique physical or electrical characteristics of output devices to the requirements of an external environment.

 

· An output unit performs the following functions:

1. It accepts the results produced by a computer, which are in coded form and hence, we cannot easily understand them.
2. It converts these ordered results to human readable form.
3. It supplies the converted results to outside world.

Storage Unit:

· Data and instructions entered into a computer system through input units have to be stored inside the computer before actual processing starts.

· Similarly, results produced by a computer after processing have to be kept somewhere inside the computer system before being passed on to an output unit.

· Moreover, a computer must also preserve intermediate results for on-going processing.

· Storage unit of a computer system caters to all these needs. It provides space for storing and data and instructions, intermediate results, and results for output.

 

· The storage unit stores:

1. Data and instructions required for processing (received from input devices).
2. Intermediate results of processing.
3. Results for output, before they are released to an output device.

 

· Storage unit of all computers is comprised of following two types of storage:

 

1.    Primary Storage:

· Primary storage of a computer is also known as its main memory.

· It is used to hold pieces of program instructions and data, intermediate results of processing, and recently produced results of those jobs on which the computer is currently working.

· These pieces of information are represented electronically in the main memory chip’s circuitry and while it remains in the main memory, Central Processing Unit can access it directly at a very fast speed.

· However, primary storage can hold information only while computer system is on. As soon as the computer system switches off or resets, the information held in primary storage is erased.

· Moreover, primary storage normally has limited storage capacity because it is very expensive.

· Primary storage of modern computer systems is made of semiconductor devices. Example: Random Access Memory (RAM).

 

2.    Secondary Storage:

· Secondary storage of a computer is also known as its auxiliary storage.

· It is used to take care of the limitations of primary storage; i.e. it supplements storage capacity and the volatile characteristics of primary storage.

· This is because secondary storage is much cheaper than primary storage and it can retain information even when a computer system switches off or resets.

· Secondary storage holds the program instructions, data, and information of those jobs on which the computer system is currently not working but needs to hold them for processing later.

· Magnetic disk is the most commonly used secondary storage medium.

Arithmetic Logic Unit (ALU):

· An arithmetic logic unit (ALU) of a computer system is the place where actual execution of instructions takes place during the processing operation.

· To be more precise, calculations are performed and all comparisons are made in the ALU.

· Data and instructions stored in primary storage before processing are transferred as and when needed to the ALU where processing takes place.

· Intermediate results generated in the ALU are temporarily transferred back to primary storage until needed later.

· Hence, data may move from primary storage to ALU and back again to storage many times before processing is over.

· The type and number of arithmetic and logic operations that a computer can perform is determined by the engineering design of its ALU.

· However, almost all ALUs are designed to perform the four basic arithmetic operations (add, subtract, multiply, and divide) and logic operations or comparisons such as less than, equal to, and greater than.

 

Control Unit (CU):

· The control unit does not perform any actual processing on data.

· The control unit acts as a central nervous system for other components of a computer system.

· It manages and coordinates the entire computer system.

· It obtains instructions from the program stored in main memory, interprets the instructions, and issues signals causing other units of the system to execute them.

Central Processing Unit (CPU):

· Control unit (CU) and arithmetic logic unit (ALU) of a computer system are together known as the central Processing Unit (CPU).

· The CPU is the brain of a computer system.

· All major calculations and comparisons take place inside the CPU and the CPU is responsible for activating and controlling the operations of other units of the computer system.

Fig: CPU as ALU and CU

 

 The System Concept:

· A system is a group of integrated parts that have a common purpose of achieving some objective(s).

· Hence, the system must have the following three characteristics:

1. It must have a more than one element.
2. All its elements must be related logically.
3. All its elements must be controlled in a manner to achieve the system goal.

 

· As computer comprises of integrated components (input unit, output unit, storage unit and CPU) that work together to perform the steps called for in a program, it is a system.

· Its input and output units cannot function until they receive signals from the CPU. Similarly, the storage unit are the CPU alone is of no use.

· Hence, the usefulness of each unit depends on other units and is realizable only when all units are put together (integrated) to form a system.