Monday, October 8, 2007

Relational Database

Definition of a Relational Database
A relational database uses relations or two-dimensional tables to store information.
For example, you might want to store information about all the employees in your company. In a relational
database, you create several tables to store different pieces of information about your employees, such as an
employee table, a department table, and a salary table.

-------------
Relational Database Management System
Oracle provides a flexible RDBMS called Oracle9i. Using its features, you can store and manage data with
all the advantages of a relational structure plus PL/SQL, an engine that provides you with the ability to
store and execute program units. Oracle9i also supports Java and XML. The Oracle server offers the
options of retrieving data based on optimization techniques. It includes security features that control how a
database is accessed and used. Other features include consistency and protection of data through locking
mechanisms.
The Oracle9i server is an object-relational database management system that provides an open,
comprehensive, and integrated approach to information management. An Oracle server consists of an
Oracle database and an Oracle server instance. Every time a database is started, a system global area (SGA)
is allocated, and Oracle background processes are started. The system global area is an area of memory
used for database information shared by the database users. The combination of the background processes
and memory buffers is called an Oracle instance.
-------------
Data Models
Models are a cornerstone of design. Engineers build a model of a car to work out any details before putting
it into production. In the same manner, system designers develop models to explore ideas and improve the
understanding of the database design.
Purpose of Models
Models help communicate the concepts in people’s minds. They can be used to do the following:
• Communicate
• Categorize
• Describe
• Specify
• Investigate
• Evolve
• Analyze
• Imitate
The objective is to produce a model that fits a multitude of these uses, can be understood by an end user,
and contains sufficient detail for a developer to build a database system.

--------------

ER Modeling
In an effective system, data is divided into discrete categories or entities. An entity relationship (ER) model
is an illustration of various entities in a business and the rel ationships between them. An ER model is
derived from business specifications or narratives and built during the analysis phase of the system
development life cycle. ER models separate the information required by a business from the activities
performed within a business. Although businesses can change thei r activities, the type of information tends
to remain constant. Therefore, the data structures also tend to be constant.
Benefits of ER Modeling
• Documents information for the organization in a clear, precise format
• Provides a clear picture of the scope of the information requirement
• Provides an easily understood pictorial map for the database design
• Offers an effective framework for integrating multiple applications
Key Components
• Entity: A thing of significance about which information needs to be known. Examples are
departments, employees, and orders.
• Attribute: Something that describes or qualifies an entity. For example, for the employee entity, the
attributes would be the employee number, name, job title, hire date, department number, and so on.
Each of the attributes is either required or optional. This state is called optionality.
• Relationship: A named association between entities showing optionality and degree. Examples are
employees and departments, and orders and items.


Entities
To represent an entity in a model, use the following conventions:
• Soft box with any dimensions
• Singular, unique entity name
• Entity name in uppercase
• Optional synonym names in uppercase within parentheses: ( )
Attributes
To represent an attribute in a model, use the following conventions:
• Use singular names in lowercase.
• Tag mandatory attributes, or values that must be known, with an asterisk: *.
• Tag optional attributes, or values that may be known, with the letter o.
Relationships
Symbol Description
Dashed line Optional element indicating “may be”
Solid line Mandatory element indicating “must be”
Crow’s foot Degree element indicating “one or more”
Single line Degree element indicating “one and only one”

Relationships
Each direction of the relationship contains:
• A label, for example, taught by or assigned to
• An optionality, either must be or may be
• A degree, either one and only one or one or more
Note: The term cardinality is a synonym for the term degree.
Each source entity {may be must be} relationship name {one and only one one or more} destination
entity.
Note: The convention is to read clockwise.
Unique Identifiers
A unique identifier (UID) is any combination of attributes or relationships, or both, that serves to distinguish
occurrences of an entity. Each entity occurrence must be uniquely identifiable.
• Tag each attribute that is part of the UID with a number symbol: #
• Tag secondary UIDs with a number sign in parentheses: (#)
---------------

Relating Multiple Tables
Each table contains data that describes exactly one entity. For example, the EMPLOYEES table contains
information about employees. Categories of data are listed across the top of each table, and individual cases are
listed below. Using a table format, you can readily visualize, understand, and use information.
Because data about different entities is stored in different tables, you may need to combine two or more tables
to answer a particular question. For example, you may want to know the location of the department where an
employee works. In this scenario, you need information from the EMPLOYEES table (which contains data about
employees) and the DEPARTMENTS table (which contains information about departments). With an RDBMS
you can relate the data in one table to the data in another by using the foreign keys. A foreign key is a column
or a set of columns that refer to a primary key in the same table or another table.
You can use the ability to relate data in one table to data in another to organize information in separate,
manageable units. Employee data can be kept logically distinct from department data by storing it in a separate
table.
Guidelines for Primary Keys and Foreign Keys
• You can not use duplicate values in a primary key.
• Primary keys generally cannot be changed.
• Foreign keys are based on data values and are purely logical, not physical, pointers.
• A foreign key value must match an existing primary key value or unique key value, or else be null.
• A foreign key must reference either a primary key or unique key column.

No comments: