Document Sample
dbms_1_2_part_ Powered By Docstoc
  1. AN Entity is represented by a set of attributes. Attributes are descriptive propereties possessed
   by each member of an entity set.

 2. The attributes of customer entity set are customer_id, customer _ name, and city possible
    attributes of the account entity set are account_no and balance.

3. each entity has a value for each of its attributes. For instance, a particular customer entity may
    have the value C101 for attributes customer_id, hari for customer _name, and bombay for city.

Attributes are classified as

1. Simple attributes.
2. Composite attributes.
3. Single valued attribute
4. Multi valued attribute
5. Derived Attribute .
• 1. Simple attributes :
        - it is an attribute composed of a single components with an
  independent existence.
       - simple attribute can not be further subdivided.
       eg : roll _no, acc_no etc.

   2. composite attribute :
       - an attribute composed of multiple components each with an
   indepentent existence called composite.
     eg : a) Name , which is composed of attributes like first name, middle name
   and last name.
          b) Address, which is composed of other components like street, city,

  3. Single valued attributes( ATOMIC) :
       - A single valued attribute is one that holds a single value for a single
        eg : cust_id, Room_no
•     Multi value attributes :
       - it holds multiple values for a single entity.

        eg: student entity can have multiple values for the hobby attributes such as
      reading,music, painting.

    Derived attribute :
       - it represent a value that is derivable from the value of a related attribute .
      Eg : age attribute can be derived from the dat of birth attribute.

      - A relationalship expresses an association among several entities
     - A relationalship set is a set of relationalships of the same type.

     eg :   consider two entities person and company
         Person _id

                                     Works                      company
        person                        for

                                                             Comp_                   Comp_
                          Person_                             name                    addr
   name                    addr

                 E R DIAGRAM :
1. The relationship Works for represents association between persaon and company .
2. this is binary relationship set.
    - The function that an entity plays in a relationship called that entity role . The role is
  one end of an association.

                               employee          Works           employer
            person                                for

 - person and company participates in association ‘work for’ .
- person assumes the role of employee with respect to company ,A company assumes the
   role of employer with respect to person.

- A relationship may also have attributes called descriptive attributes.
- consider a relationalship set ‘depositor’ with entity sets customer and account.
- we can associate attributes access – date to the depositor relationship to specify the
  most recent date on which a customer accessed an account.

          customer                     depositor                  account

Cust_id            Cust_         Access_dat                Acc              balance
                   name              e                     _no


      - A Unary relationship exits when an association is maintained with in a
   single entity .
  eg :
                       boss employee

            manage                  worker

    - boss and worker distingishes the two employees participating in the
   manage association.

  2. Binary relationship :
     - when two entities are associated.
              publisher               publishes                  book

     - When there are three entities associated.
 eg : the entities teacher, subject,student are related using aternary relationship

          teacher                    teaches                       subject


   - when there are four entities associated.
   eg:                            teacher

        student                     studies                  Course material

• Constraints :
   - An E-R enterprise schema may define certain constraints to which the
   content of a database system must conform .
       Two main important types of constraints are :

      - It express the number of entities to which another entity can be
   associated via relationship set.
      - for binary relationship set R between entity sets A and B the mapping
   cardinalities must be one of the following.
                    ( or)
   Cardinality specifies how the number of occurance of one object is related to
   the number of occurance of another object   .
•   One-to-one (1:1) – one object can relate to only one other object.
•   One-to-many (1:N) – one object can relate to many objects.
•    many –to- one(N:1) –Many object can relate to one object
•   Many-to-many (N:N) – some number of occurances of an object can relate
    to some other number of occurances of another object.

1. One-to-one(1:1) :
     - AN entity in A is associated with at most one entity in B and an entity
   B is associated with at most one entity in A,

           A1                                     B1

           A2                                     B2

           A3                                     B3

                1. One-to-one(1:1) MAPPING:
•     EG: A customer with single account at given branch

                CUSTOMER                                             ACCOUNT

•   ii) One –to – many:
          - AN entity in A is associated with any number ofentities in B and an
    entity B is associated with at most one entity in A,

                      A1                                    B3

                     A2                                     b4

                     A3                                     b5

                               One-to-many (1:N)
•     eg : A customer having two accounts at a given branch

                CUSTOMER                                           ACCOUNT

                             One-to-many (1:N)

    Many –to- one(N:1):
          - AN entity in A is associated with at most one entity in B and an entity B is
     associated with any number of entities in A,

     eg:                                       Works
                employees                                                 company

      - Many employees works for a company.
•    Many – to - Many :
          - AN entity in A is associated with Any number of entities in B and an
    entities B is associated with any number of entities in A,

     eg : employee works on number of projects and project is handled by
    number of employees. Therefore the relationship between employee and
    project is a many – to - many

                  employee                     on                       project

       - The participation of an entity set E in a relational set R IS SAID TO BE
    TOTAL if every entity in E participates in atleast one relationship in R.
      - If only some entities in E partcipate in relationships in r , the participation of
    entity set E in relationship R is said to be partial.
                                  UNIT -2
•     KEYS :
       - Key allows us to identify a set of attributes and then distinguishes entities
     from each other.
     - Keys also help uniquely identify relationship and thus distiguish relationships
     from each other.

     Different types of keys
     1. Super key .
     2. Candidate key.
     3. Primary key
     4. Foreign key.

    1. Super key.
         - it is a set of one or more attributes that allows us to identify uniquely an
     entity in the entity set.
       - eg: Roll _No attributes of the entity set ‘ student ‘ distinguishes one student
     entity from another.
  2. Candidate key :
       - A super key may contain extraneous attributes and we are often
interested in the smallest superkey.
     - A superkey for which no subset is a superkey Is called candidate key.
  eg: student name and student street are sufficient to uniquely identify one
particular student.

 3. Primary key :
     - It is a candidate key that is choosen by the database designer as the
principal means of identifying entities within the entity set.
 eg: roll no is a primary set of ‘student’ entity set.

 4. Foreign key :
     - An attributes or set of attributes , within one relation that matches the
candidate key of some relation.
- E R model that is supported with additional semantics concepts
called EER model.
 1. Specialisation .
 2. Genaralization.
 3. Aggregation.
1. Specialisation .
      - It is the process of designating subgroupings within an entity
set. it is a top – down process

       - Specilaization , which is represented by triangle symbol . The
 label ISA – stands for “is a” and represents .eg that customer
  “ is a” person.
      - ISA relationship may also reffered to as a superclass –subclass
     eg : consider an entity set person , with attributes name, street and city. A person
      may be further classified as one of the following .
       1. customer
       2. Employee
         - Each of these person types is described by a set of attributes that includes all
      the attributes of entity set person plus additional attributes.
    - eg :customer entities are further described by cust_id , employee entities by
      emp_code and salary .

        - It is the process of defining a more general entity type from a set of more
     specialized entity types.
        - It is botton- up approach .This approach results in the identification of a
     generalized entity types.
              NAME                 STREET


                                       ISA                      Creditrating

                 EMPLOYEE                            CUSTOMER

•                      ISA                   SPECIALIZATION &GENERALIZATION

    OFFICER           TELLER                 SECRETARY

    OFFICE       Station       HOUSE                     HOURS
     _NO                       WORK
     - Aggregation is a form of association . It is used to represent the
components of a complex object.

•   Relational model :
          - it is a collection of tables to represent both data and the relationship
    among those data.
         - Each table has multiple column, and each column has a unique name.
        - The relational model is an example of a record based model.
       - RBM are so named because the database is structured in fixed – format
    records of several types.
      - each table contains records of a particular type , each record type defines a
    fixed number of fields or attributes.
    - The R DATAMODEL is the most widely used data model .
    Cust_id   Cust_n     Cust      Cust
              ame        _street   _city
    192-83- johnson 12 alma palo
    7465            st
    67-89-    hayes      3 main    hariso
    9011                 st
•       customer table

    Acc_number balance                      CUST_ID       ACC_NUMBER

    A-101        500                        192-83-7465   A-101

    A-201        900                        192-83-7465   A-201

        - it is a kind of database management system that links records
    together in a tree data structures such that each record type has only
    one owner.

       - The network model replaces the hierarchical tree with a graph
    thus allowing more general connection among the nodes.
       - The main difference of the network model from the hierarchical
    model is its ability to handle many to many relationships.
      - it allows a record to have more than one parent.
     eg: An employee working for two departments.
•     Object oriented model :
         - it is based on a collection of objects. An object contains values stored in instance
      variables within the object.
       - An object also contains bodies of code that operate on the object. These bodies of
      code called methods
    - object contain the same types of values and the same methods are grouped together
      in to classes.
    - People who work with a database can be categorized as.
    1. Database users.
    2. Database administrators.

   1. Database users
       There are four different types of database system users, differentiated
by the way they interact with the system.

     a) Naïve users.
         - users who interact with the system by invoking one of the
application programs that have been written previously.
 eg : bank teller
 b) Application programmers :
          - There are computer professionals who write application programs.
         - Application programmer can choose from many tools to develop user
        - Rapid application development(RAD) tools that enable an application
programmers to construct forms and reports without writing a program.

 c) sophisticated users:
       - They interact with the system without writing programs , they form their
requests in a database query language.
      - They submit each query to a query processor, whose function is to
breakdown DML statements in to instruction that the storage manager
  d) Specialized users:
         - These are sophisticated users who write specialized database
application that do not fit in to the traditional data processing frame work.
        - Among these application are computer aided design system knowledge
base and expert system, system that store data with complex data types.
        - A person who has central control over the system called database
      The Function of a DBA include :
    1. Schema definition :
         - The DBA creates the original database schema by executing a set of
     data definition statement in the DDL.
    2. Storage structure and access method definition.
    3. Schema and physical organization modification:
         - The DBA carries out changes to the schema and physical organization
     to reflect the changing needs of the organization.
    4. Granting of authorization for data access :
            - By granting different types of authorization, the DBA can regulate
     different users accessing different parts of database.
•       Routine maintenance:
         1) Periodic backup, either on to tapes or onto remote server, to prevent
      loss of data in case of disasters such as flooding.
        2) Ensuring that enough free disk space is available for normal operations
      and upgrading disk space as required.
        3) Monitoring jobs running on the database and ensuring that performance
      is not degraded by very expensive tasks submitted by some users.

    DATA DICTIONARY(System catalogs)
     - A repository of information describing the data in the database , that is the
     metadata. Or the data about the data.

      - A database system is partitioned in to modules that deal with each of the
       responsibilities of the overall system.
     - The functional components of a database system can be divided in to
       1. Storage manager.
       2. Query processor components.
   - It is a program module that provides the interface between the low
  level data stored in the database and the application programs and
  queries submitted to the system.
   - The storage manager translates the various DML statement in to low
  level file system commands.
   - it is responsible for storing , retrieving and updating data in the

various components of the storage manager :
1. Authorization and integrity manager :
       - It test for satisfaction of various integrity constraints and checks the
   authority of users accessing the data.
2. Transaction manager :
     - It ensure that the database remains in a consistent state despite
   system failures and concurrent executions proceed without conflicting
3) File manager :
       - It manages the allocation of space on disk storage and the data
    structures used to represent information stored on disk.
  4) Buffer manager :
        - It is responsible for fetching data from disk storage into main memory
    and deciding what data to cache in main memory.

 Several data structures as part of physical system implementation:
 1. DATA FILES: - which stores the database itself.
 2. Data dictionary: - It contains metadata that is data about data.
 3. indices : which provide fast access to data items that hold particular
  Query Processor :
    - The query processor is an important part of the database system. It helps
the datbase system to simplify and facilitate access to data.

  Query components include :
    1. DDL interpreter – which interprets DDL statements and records the
definitions in the data dictionary.

   2. DML compiler – which translates DML statements in a Query language
into an evaluation plan consisting of low level instruction That the query
evaluation engine understands.

  3. Query evaluation engine – which executes low level instruction generated
by DML compiler.
  Relational Algebra:
     - It is a procedural query language. It consists of a set operations that
take one or two relation as input and produce a new relation as their result.

•  A basic expression in the relational algebra consists of either one of the
   – A relation in the database
   – A constant relation
• Let E1 and E2 be relational-algebra expressions; the following are all
  relational-algebra expressions:
   – E1  E2
   – E1 - E2
   – E1 x E2
   – p (E1), P is a predicate on attributes in E1
   – s(E1), S is a list consisting of some of the attributes in E1
   –  x (E1), x is the new name for the result of E1
•      The relational algebraic operations are divided in to two groups:
     1) The first group includes the set operations. Set operations include the
      following operations.
     a) Union.
     b) intersection
     c) set difference
     d) cartesian product

    2) The second group of relational algebraic operation is developed specially
      relational databases .some of the operation of this group are
    a) Select
    b) project
    c) Rename

     a) Union.
         - Two relations are said to be union compatible if the following conditions
      are satisfied
• The two relations/tables must contain the same number of columns(have the
  same degree).
• Each column of the first relation/ table must be either the same data type as
  the corresponding column of the second relation/table or convertible to the
  same data type as corresponding column of the second.

              Customer _ name        city
              Hayes                  Pune
              Johnson                Mumbai
              jones                  solapur

               Customer name           city
               adams                   mumbai
               hayes                   pune
               jackson                 solapur
•  The result of operation is denoted by depositor U borrower is the
  relation that includes all tuples that are either in depositor or borrower
  or in both.
• duplicates are eliminated.

•  Query : find the names of all bank customer who have an account or
  loan or both.
• the result of union.

                                  dep U borrow
            Cust name                     city
            hayes                         pune
            johnson                       Mumbai
            jones                         Solapur
            adams                         Mumbai
            jackson                       Solapur
            adams                         mumbai

Shared By: