♦
A Logical View of Data:
Develop a logical view of data to represent and access data
in an enterprise. After you develop a logical view of data, you
can add it to a data service to make virtual data available
for end users.
Before you develop a logical view of data, you can define
the physical data objects that you want to use in a logical data
object mapping. You can also profile the physical data
sources to analyze data quality.
1. Create or import a logical data model.
2. Optionally, add logical data objects to the logical data object
model and define relationships between objects.
3. Create a logical data object mapping to read data from a
logical data object or write data to a logical data object. A
logical data object mapping can contain transformation logic
to transform the data. The transformations can
include data quality transformations to validate and cleanse
the data.
4. View the output of the logical data object mapping.
♦ Keys: Keys are very important part of Relational database.
They are used to establish and identify relation between tables. They also
ensure that each record within a table can be uniquely identified by
combination of one or more fields within a table.
For example: Primary Key, Foregin
key
♦ Integrity Rules:
Integrity Rules are imperative to
a good database design. Most RDBMS have these rules automatically, but it is
safer to just make sure that the rules are already applied in the design. There
are two types of integrity mentioned in integrity rules, entity and reference.
·
Integrity rules are needed to inform the DBMS
about certain constraints in the real world.
·
Specific integrity rules apply to one specific
database.
Example: part weights must be greater than zero.
·
General integrity rules apply to all databases.
♦ Relational Set Operators.
One of the characteristics of
RDBMS is that it should support all the transaction on the records in the table
by means relational operations. That means it should have strong query language
which supports relational algebra. There are three main relational algebras on
sets – UNION, SET DIFFERENCE and SET INTERSECT. The same is implemented
in database query language using set operators.
♦ The Data Dictionary and the System Catalog:
Data
dictionary provides a detailed description of all tables found within the
user/designer-created database. It contains the attribute names and
characteristics for each table in the system. In short it contains metadata (Descriptive
statistical information about the element of a set of data.)
System catalog contains metadata .the system catalog can
be described as detailed system data dictionary that describes all objects
within the database, including data about table name, the table s creator and
creation date, the number of columns in each table, the data type corresponding
to each column, index filenames, index creators, authorized users, and access
privileges. Current relational database software generally provides only a
system catalog, from which the designer s data dictionary information may be
derived.
In effect, the system catalog automatically produces
database documentation. As new tables are added to the database, that
documentation also allows the RDBMS to check for and eliminate homonyms and
synonyms.
Homonyms indicate the use of the same attribute name to
label different attributes. For example, you might use C-NAME to label a
customer name attribute in a CUSTOMER table and also use C-NAME to label a
consultant name attribute in a CONSULTANT table.
Synonym is the opposite of homonym and indicates the use
of different names to describe the same attribute.
♦ Relationships within the Relational Database:
A heavy part of designing a
relational database is dividing the data elements into related tables. Once
you're ready to start working with the data, you rely on relationships between
the tables to pull the data together in meaningful ways. For instance, order
information is useless unless you know which customer placed a particular
order. By now, you probably realize that you don't store customer and order
information in the same table. Instead, you store order and customer data in
two related tables and then use a relationship between the two tables to view
each order and its corresponding customer information at the same time.
There are three types of
relationships:
- One-to-one:
Both tables can have only one record on either side of the relationship.
Each primary key value relates to only one (or no) record in the related
table. They're like spouses—you may or may not be married, but if you are,
both you and your spouse have only one spouse. Most one-to-one
relationships are forced by business rules and don't flow naturally from
the data. In the absence of such a rule, you can usually combine both
tables into one table without breaking any normalization rules.
- One-to-many:
The primary key table contains only one record that relates to none, one,
or many records in the related table. This relationship is similar to the
one between you and a parent. You have only one mother, but your mother
may have several children.
- Many-to-many:
Each record in both tables can relate to any number of records (or no
records) in the other table. For instance, if you have several siblings,
so do your siblings (have many siblings). Many-to-many relationships
require a third table, known as an associate or linking table, because
relational systems can't directly accommodate the relationship.
♦ Data Redundancy Revisited:
Data redundancy in database means
that some data fields are repeated in the database.
This data repetition may occur either if a field is repeated in two or more
tables or if the field is repeated within the table.
Data can appear multiple times in a database for a variety of reasons. For
example, a shop may have the same customer’s name appearing several times if
that customer has bought several different products at different dates.
Disadvantages of data redundancy
- Increases
the size of the database unnecessarily.
- Causes
data inconsistency.
- Decreases
efficiency of database.
- May cause
data corruption.
♦ Indexes:
Indexing is a data structure
technique to efficiently retrieve records from the database files based on some
attributes on which the indexing has been done. Indexing in database systems is
similar to what we see in books.
The only minor disadvantage of using index is that it takes up a little more
space than the main table. Additionally, index needs to be updated periodically
for insertion or deletion of records in the main table.
