Page 249 - Information_Practice_Fliipbook_Class11
P. 249
9.2 Relational Data Model
To build a house, we require a house blueprint that serves as the basis for constructing the house. Similarly, a data
model is a representation (often graphical) of the structure of real-world objects. Developing a database system
requires a design (model) that will be the basis of communication between designers and programmers. A data model
comprises the following components:
1. Entity: An entity is a real-world object about which data is stored in the database. For example, a company's
database may have employee and department entities.
2. Attribute: An attribute defines the characteristics of an entity. For example, employee id, date of birth, address, and
salary is one of the attributes of an employee.
3. Relationship: A relationship specifies how two entities are linked (or related) to each other. For example, in a
database that deals with employees of a company who work in different departments, the employee entity is
related to the department entity since an employee works in a department.
4. Constraint: A constraint specifies the restrictions imposed on the data stored. For example, each employee should
have a unique id.
The relational data model founded by E.F. Codd of IBM is prevalent. It defines a database as a collection of relations.
Each relation is a table comprising rows and columns. We will use the terms, relation and table interchangeably.
9.2.1 Relational Data Model Concepts
Let us begin with an example. We consider a company having several employees, who work in different departments.
Further, an employee may work on several projects in the company. Let us call the database COMPANY. We organise
information about the company into four tables, EMPLOYEE, DEPARTMENT, PROJECT, and WORKS_ON, as
shown in Fig 9.3. We will follow the convention that the names of tables are in uppercase letters. However, the
name of an attribute may be a suitable combination of uppercase and lowercase letters.
Relation: EMPLOYEE
+-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
| ID | FName | LName | Gender | Address | City | Pin_Code | DOB | Salary | Dept_No |
+-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
| E0001 | Raj | Reddy | M | West Godavari | Andhra Pradesh | 534197 | 1980-06-13 | 100000 | 2 |
| E0002 | Dhiraj | Bora | M | Dispur, Kamrup, Assam | Guwahati | 781005 | 1975-09-30 | 85000 | 1 |
| E0003 | Muskan | Taneja | F | 8/33, Geeta Colony | Delhi | 110031 | 1990-01-25 | 100000 | 2 |
| E0004 | Hiten | Oberoi | M | 15, Dimna Road, Mango | Jamshedpur | 831018 | 1985-06-24 | 100000 | 4 |
| E0005 | Anshul | Verma | M | House 10, Sector 16, | Noida | 201304 | 1990-01-01 | 100000 | 1 |
| | | | | Gautum Budh Nagar | | | | | |
| E0006 | Rajit | Gadh | F | 12, Beldih Triangle, | Jamshedpur | 831001 | 1960-05-07 | 60000 | 4 |
| | | | | Bistupur | | | | | |
| E0007 | Taran | Adarsh | M | B-76, CST Road, Kalina, | Mumbai | 400098 | 1965-01-13 | 70000 | 5 |
| | | | | Santacruz East | | | | | |
| E0008 | Naval | Dhingra | M | E-14 Vivek Vihar | Delhi | 110095 | 1975-08-04 | 70000 | 2 |
| E0009 | Naveen | Basra | F | 28, Aambagan Road, | Jamshedpur | 831001 | 1980-09-24 | 60000 | 4 |
| | | | | Sakechi | | | | | |
| E0010 | Savita | Ambedkar | F | C-49, G-Block, Bandra | Mumbai | 400051 | 1987-07-11 | 50000 | 5 |
| | | | | Kurla, Bandra East | | | | | |
+-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
Relation: DEPARTMENT Relation: PROJECT
+---------+----------------+------------+--------+ +---------+-----------+----------+
| Dept_No | Dept_Name | Location | Mgr_Id | | Proj_No | Proj_Name | Dept_Num |
+---------+----------------+------------+--------+ +---------+-----------+----------+
| 1 | Accounts | Noida | E0002 | | 1 | ProjectA | 2 |
| 2 | Administration | Delhi | E0005 | | 2 | ProjectB | 1 |
| 3 | Home Goods | Mumbai | NULL | | 3 | ProjectA | 1 |
| 4 | Automobile | Jamshedpur | E0007 | | 4 | ProjectC | 5 |
| 5 | Textile | Mumbai | E0004 | | 5 | ProjectD | 4 |
+---------+----------------+------------+--------+ +---------+-----------+----------+
Database Concepts and the Structured Query Language 235

