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
   244   245   246   247   248   249   250   251   252   253   254