APPENDIX B USE OF SDM IN DESIGN OF DATABASES

Document Sample
APPENDIX B USE OF SDM IN DESIGN OF DATABASES Powered By Docstoc
					                                                    Appendix C
                             OVERVIEW OF DATABASE MODELS

INTRODUCTION

           Database is a collection of related data which can be shared by the different users within an organization.
In other words, it is a sructure that holds data about entities (records), attributes (fields), and the relationship among
entities. Collection of programs called Data Base Management System (DBMS) maintain these databases. They
provide capabilities such as data definition, and data manipulation. They offer different interfaces for different types
of users, logical representation for database, and mapping to physical representation of data.
           Different data structure and methodology used by different DBMSs represent different database models.
We have three popular database models.
•          Relational database
•          Network (CODASYL or DBTG) database
•          Hierarchical database.

          In the following sections a very brief description for each of these models are presented. These brief
descriptions are only intended to be a reminder for the above models and to prepare us for the next chapter
(conversion of SDM into database models). I recommend the readers refer to other texts written on the different
database management systems such as [Date90], [Elmasri89], [Silberchatz90], and [Ulman89].


RELATIONAL DATABASE MODEL: AN OVERVIEW

          Relational database is a collection of tables. A relation is a two-dimensional table made up of rows and
columns. Formally a table is called a relation and this is why the model is called relational database rather than table
database.


Basic Terminologies In Relational Database

•         Entity: Each table stores data about an entity or object. An entity is a thing      distinguishable from other
          entities such as: student, course, employee, department.

•         Attribute: Describes each entity such as: student_name, course_number, department_phone #.

•         Relationship: Describes a relationship between two entities such as: teacher advising student, student
          enrolling in courses, employee working in a department.

•         Database: A structure that can hold information about entities, attributes of  each entity, and
          relationships among entities. A database is defined by James Martin as "a shared collection of interrelated
          data designed to meet the needs of multiple types of end users."

•         DBMS (Data Base Management System): In order to manipulate a database we need some kind of
          a tool. This tool is a very complex program and is called DBMS. Users will see only a very small
          portion of it. Different DBMS models use different data structures. DBMS's which are using a relational
          database concept are called relational DBMSs.

•         DBA (Data Base Administrator): The people that manage, monitor, tune, and control the DBMS
          software.



                                                           C-1
•           Primary Key (PK): An attribute(s), field(s), or column(s) which can uniquely identify each record
            (tuple or instance) of a relation (table).

•           Foreign Key (FK): An attribute(s) which is a regular attribute(s) in one table but primary key(s) or
            attribute(s) in another table.

•           Base Table: is a physical table. It actually exists and holds data.

•           View Table: is a logical table. It does not exist. It is logically built using some base tables and any
            change in the base table will change the VIEW table.

The following table shows how the terminology is used in different environments:

        Relational                                Relational                               Traditional
        Database                                  DBMS                                     File Processing
        Approach                                  Softwares                                Approach

        •    Relation                             •   Table                                •   File
        •    Tuple                                •   Row                                  •   Record
        •    Attribute                            •   Column                               •   Field
        •    Primary Key (PK)                     •   Primary Key (PK)                     •   Search Key
        •    Relationship through FK              •   Relationship through FK              •   Not Used

       In the majority of relational DBMS products, maintaining, manipulation and control of the Primary Key (PK)
and Foreign Key (FK) is the responsibility of the user.



AN EXAMPLE IN RDBS

       The following database consisted of three tables.

student ( s# ,name, major, minor, sex) : PK ==> s#

enrollment (s# , c# , qrt , year , grd) : PK ==> s#, c#, qrt, and year as composite PK.
                                          FK ==> s# and c#.

course (c# , cname, cr_hrs)                      : PK ==> c#




                                                           C-2
 course
                                             cr
     c#             cname                   hrs               enrollment
     cs358      int ro DBMS                     4             S#     C#      qrt   year   grd
     cs121      int ro - C om putres            3
                                                              s123   cs358     f   86      a
     cs256      int ro system A&D               4
                                                              s345   cs358     f   86      a
     cs458      DBMS I                          4
                                                              s123   cs121     f   87      b
     cs356        s
                Sy Analy&Design                 5
                                                              s678   cs121     w   86      c
     cs468      4GL&Prot otyping                4
                                                              s567   cs256     s   87      b
     cs459      DBMS II                         4
                                                              s124   cs458     s   88      b
     cs659      Adv . Dat abase Sy s            4
                                                              s234   cs356     w   88      c
                                                              s345   cs468     s   89      b
                                                              s100   cs121     f   86      a
student                                                       s345   cs458     f   86      c
                                                              s200   cs459     f   87      a
s#           name      maj or           minor        sex
                                                              s123   cs458     s   89      b
s123      smit h       comp-sci        acct               m   s567   cs356     f   88      a
s124      j ones       acct            mgt                f   s456   cs659     w   86      b
s234      t hm pson    mkt             comp-sci           f   s567   cs468     w   88      d
s345      young        comp-sci        mat h              m   s678   cs256     w   89      c
s456      green        mat h           comp-sci           m   s100   cs256     s   89      d
s567      black        eet             mat h              f   s200   cs659     s   88      a
s678      j am es      comp-sci        mkt                m
s100      smit h       mgt             f in               f
s200      j honson     comp-sci        mat h              m




                                                    C-3