Document Sample
Databases Powered By Docstoc
					Database Management
What is a database?

A collection of information related to a

particular subject or purpose

   tracking customer orders

   maintaining a music collection
What is a database?

Sometimes databases        You have to
are not                       track information from
                               a variety of sources
   well organized
                              coordinate and
   stored completely on
                               organize them yourself
    a computer
    Example case
Suppose you are engaged in a buying and selling

You need to
   contact your suppliers

   order goods in bulk

   whenever they reached their re-order levels in the
Example case cont…

However your data is not well organized.

1.   The phone numbers of your suppliers are stored in

     various locations.

2.   You have a card file (a pack of cards) for suppliers

     containing supplier phone numbers.
Example case cont…

3.   The product information also contains supplier phone


4.   Order information is stored in a computerized

     spreadsheet in Excel.

5.   You also have product information files in a file

     Example case cont…
     What is an order ?

1.    supplier name                5.   purchase price,
2.    Address                      6.   required date,
3.    contact phone number         7.   extended totals and sub
4.    Goods and their                   totals, etc.
      quantities ordered
  Example case cont…

If a supplier's phone number changes,

  you might have to update that information in all

  the three places.
   Example case cont…
   Advantage of a Database
You only have to update

that information in one

                          The supplier's phone number is

                          automatically updated wherever

                          you use it in the database
         Data Modelling
 That describe the data models of DBMS.
 Initially definition of some terms are given below.

An entity is a thing (object) which can
be distinctly identified.
Examples: Principal,School, Teacher, Subject
      Data Modelling
An attribute is a property of an entity.
•Attributes of a principal entity might be
 principal name, principal number, address
•Attributes of a school entity might be
 school name, school number, district
        Data Modelling
A relationship is an association between two or
many entities.
•An entity “principal” may be associated with an
entity “school” via a relationship “manages”
•An entity “teacher” may be associated with an entity
“subject” via a relationship “teaches”
       The Degree of a Relationship
• One value of an entity can select or determine one
  or many values of the other entity.
• This is an important property of a relationship,
  which is generally known as its degree
• There are three possible kinds of relationship
  degree, namely,
          1. One to one
          2. One to many
          3. Many to many
      One to One Relationship

A relationship between entities A & B is said to be
one to one (often written 1:1) if for each value of A,
there is almost one value of B related with it and
vice versa.
A Principal manages one school and
A school is managed by one principal
       One to Many Relationship
• If one value of entity A is related with one or many
  values of entity B and
 One value of B is related with only one value of A
• The relationship between A and B is said to be one to
  many (often written 1:N)
A school employs many teachers and
A teacher is employed by only one school.
        Many to Many Relationship
• A many to many (often written M:N) relationship is
  a combination of one to many relationships in each
• With a many to many relationship, a value of entity A
  is related with one or many values of B and
 value of B is related with one or many values of A.
A teacher teaches many subjects and
A subject is taught by many teachers
      Types of Data Model
• A data model is the way database appears to its
• It is designed to be easy for users to visualize and to
• One of the functions of a DBMS is to map the data
  - the user view of the database
  - on to the file store that is actually holding it.
       Types of Data Model
•   A data model shows how entities are related.
•   There are three generally recognized data models.
             1. Hierarchical
             2. Network
             3. Relational
•   A DBMS can be put into one of the above model
•   These model differ in the way they structure, organize and
    manipulate data items and relationship among them.
•   The three models also differ in their origins and in their
    typical applications.
      Hierarchical Model

• The hierarchical model is used with data that fall
  naturally into a hierarchy.
• Data records in the database are related by one to
  many relationship.
• This model organizes data according to associations
  that are represented as trees.

      Prog-1     Prog-1     Prog-1
      Hierarchical Model

• The entity type at the top of the tree is usually
  known as the root.
• In generally, the root may have any number of
  dependents, each of these may have any number
  of lower level dependents and any number of
• Choose one of the data entities as superior, and
  list the others as subordinates.
       Hierarchical Model

Database containing details of programming work being
carried out in a software house. The database must hold
information about programming projects: name of the
project, date started, estimated cost etc. There is also to be
information about programmers employed: name, pay rate,
date joined etc. Finally it is necessary to hold information of
which programmers work(from time to time) on which
project and vice versa.
         Hierarchical Model

1. Project at the top level
proj_1            payroll       10.11.2001          100,000

     prog_1          A.Sarath     100        09.11.1998     100
         prog_3         R.Kumar      175       10.08.2000     205

proj_2        inventory         10.12.2000        400,000

     prog_1          A.Sarath  100   09.11.1998    65
        prog_3         R.Kumar   175    10.08.2000    80
      Hierarchical Model

2. Programmers at the top level

Disadvantages of Hierarchical Model
1. The same data appears more than once (duplication)
2. It is not possible to add new programmer until he is
   assigned to a project.
3. If a project is cancelled programmer detail may be lost.
     Network Data Model
•    The network data models are based on graph
•    This can be considered as an expansion of the
     hierarchical model.
•    The main difference between these two models is,
    - In a hierarchical model, a child record has exactly one
    - In a network model, a child record can have any
      number of parents.(many to many relationship)
   Network Data Model
     Proj-1            Proj-2

Prog-1        Prog-2            Prog-3
     Network Data Model
• A network database consists of two structures out of
 which any database could be constructed;
      1. A set of records
      2. A set of links
• A record is a standard record type made up of fields.
• A link is a structure in which record types are linked
 together in a one-to-many relationship. Each link type
 involves two record types, a parent (owner) record and a
 child (member) record.
       Network Data Model
•    The relationship between a parent record and a
    child record is many to many. By introducing a link
    record, a one to many relationship can be built up
    between a parent record and a link record. Also
    between a child record and a link record.

    Project                           Programmer

          1:N                        1:N
         Network Data Model
proj_1    payroll 10.11.2001 100,000

                             proj_2 inventory 10.12.2000   400,000

 Proj-1            Proj-1                       Proj-2
 Prog-1            Prog-3                       Prog-3
 100               205                          80

 prog_1           A.Sarath     100     09.11.1998   100

         prog_3        R.Kumar       175     10.08.2000    205
  Network Data Model
 The figure shows three sorts of record:
 a) Records giving details about projects omitting
    the programmer details
 b) Records giving details of programmers
    omitting the project details
 c) Link records each showing one project and one
    programmer(who has worked on that project)
    together with the number of hours.
       Network Data Model
1. The problems found in the hierarchical model do not arise
   with network model.
2. Can update any record without possibility of inconsistency.
3. Insertion of data concerning a new programmer is very
4. Deleting the payroll project record implies deleting two
   link records,but does not result in losing programmer
      Relational Data Model
• The relational model is entirely different in concept to the
  hierarchical and network models.
• This consists a collection of tables of data elements.
• These tables are known in mathematics as relations.
• A table is characterized by rows and columns together
  with data values at each entry.
• The rows are called tuples and the columns are called
• Each column is headed by the name of the attributes.
       Relational Data Model

•    A domain is a pool of values of a particular
    type. The actual values appearing in given
    attribute are always drawn from one
    domain, but the same domain may be
    shared by many attributes.
         Relational Data Model
Proj#      Proj_Name Date_Started   Estimate_Cost
Proj_1     Payroll   10.11.2000     100,000
Proj_2     Inventory 10.12.2000     400,000

Prog# Prog_Name         Pay_Rate    Date_Joined
Prog_1 A.Sarath         100         09.11.1998
Prog_3 R.Kumar          175         10.08.2000
    Relational Data Model


Proj#      Prog#     Hours_Worked
Proj_1     Prog_1    100
Proj_1     Prog_3    205
Proj_2     Prog_3    80
       Relational Data Model
• Each relation must have a defined primary key, one
  or more of the fields which together will uniquely
  identify the tuple.
• The relations to describe the database of our example
  could be:
 Project (proj#,proj_name,date_started,estimated _cost)
 Programmer (pro#,prog_name,pay_rate,date_joined)
 Project_Programmer (proj#,prog#,hours_worked)
       Relational Data Model
Restrictions applied to Tables:-
• No two rows in a relation are identical.
Eg:- In the Relation Project, a row can always be
     uniquely identified by quoting the Project Number.

• Any domain value in an occurrence of a tuple is atomic
with respect to the set of values on which the domain is
i.e. Multiple values are not allowed.
      Relational Data Model
Links between Tables:-
Links are established by common rules. I.e. ,
      Two relations can be linked if they have attributes
      which are defined on sets of the same domain.
Eg :- * Relations Project and Proj-Prog can be linked
       via the attribute Project Number.
      * Relations Programmer and Proj-Prog can be linked
        via the attribute Programmer Number.
      Relational Data Model
Update Operations :- ( Comparison with the
Network Model )


Updating values of an attribute of a tuple is easy since
there are no duplications.
    Relational Data Model

Insertion :-

Eg :- Insertion of a new programmer.
      It is required only to insert the tuple into the
      relation Programmer.
     Relational Data Model

Deletion :-
Eg :- Deleting the Payroll project from the database.

      - Delete the tuple from the Relation, Project.
      - Delete the tuple(s) from the Relation, Proj-Prog
        which includes the Project, Payroll
Entity Relationship (ER) Diagrams
 ER diagram for the entities Department
            and Employee
ER Diagrams

If there is another table : Dependant, extend the
ER diagram including the new table
  An employee may have one or more dependants.

  To track their information is important for the better
  management of employee insurance and benefits where
  their family members would also be benefited
ER Diagrams
    How relationships work
EmpNo       DepdantName      Relation   Gender   BirthDate
E1057       Ruchira Perera   Son        M        12 - 2 -97
E1057       Sumudu Perera    Daughter   F        23 - 5 - 01
E1068       Kevin Gomes      Husband    M        14 - 7 - 66

What is the primary key of this table and why the EmpNo is
repeating in some tuples?
What is the foreign key that refers from Dependant to
What is the job of Ruchira perera’s father and at what
department he works?
Drawing ERs

Using two entities draw an ER diagram to
show students and grades they earned for
subjects they taken. Attach few attributes
for each of the entity
Draw another ERD using three entities, so
that separate entities for student personal
information and subject information
In a certain retail business customers place orders for products
they purchase. An employee of the company takes and
prepares orders for customers. The company requires keeping
and tracking information about their employees, customers,
orders, products and suppliers.
i    What are the different topics of the above scenario for
     which a different table is required?
ii   What are the attributes that are important for each of
     the entities mentioned above?
iii   Draw an ER diagram for the above scenario indicating
      different entities, attributes, primary keys, and foreign

iv    Prepare tables for your ER diagram and insert at least
      three rows to each

v     Suggest a suitable software to implement your
     Working with SQL

DDL                                                 DML

 To construct and alter the         To manipulate data in the database

Define tables, drop, add,            Insert and delete records, make
and change tables                    new information combining tables,
                                     update field values in a table, etc
Working with SQL
         End users are interested with the DML part
         Has three main key words

  Key Word   Purpose
  SELECT     List the columns from tables in a result table
  FROM       Identifies the tables for selecting columns

  WHERE      Conditions for selecting specific rows from a table
             Conditions for joining multiple rows

EmpNo       LastName          FirstName      Designation       BasicSalary     Department
E1057       Perera            Sunil          Supervisor        20,450.00       2
E1068       Gomes             Elani          Manager           35,860.54       1
E3564       Crestor           Mike           Director          48,000.00
E3352       Siveraj           Savithree      Operator          15,690.00       1

EmpNo        DepdantName                  Relation         Gender            BirthDate
E1057        Ruchira Perera               Son              M                 12 - 2 -97
E1057        Sumudu Perera                Daughter         F                 23 - 5 - 01
E1068        Kevin Gomes                  Husband          M                 14 - 7 - 66
DepNo        Name             Manager     Location
1            Production       E1068       Lot1
2            Sales                        Lot3
3            Purchasing                   Lot3
4            Accounts                     Lot2
EmpNo        LastName         FirstName   Designation   BasicSalary   Department
E1057        Perera           Sunil       Supervisor    20,450.00     2
E1068        Gomes            Elani       Manager       35,860.54     1
E3564        Crestor          Mike        Director      48,000.00
E3352        Siveraj          Savithree   Operator      15,690.00     1
EmpNo        DepdantName      Relation    Gender        BirthDate     EmpNo
E1057        Ruchira Perera   Son         M             12 - 2 -97    E1057
E1057        Sumudu Perera    Daughter    F             23 - 5 - 01   E1057
E1068        Kevin Gomes      Husband     M             14 - 7 - 66   E1068