2nd case study in DATABASE

1. Definition of relational database.

            In 1970, relational database was invented by E. F. Codd at IBM. It consist of a collection of tables that store particular set of data for which the data can be accessed, processed, manipulated in many different ways without having to reorganize the database tables. The process by which data is organized logically is called normalization. Normalization regulates data’s structure and simplifies the way data is defined. The stored data is manipulated using a programming language called Structured Query Language (SQL).

            The primary unit of storage in a relational database is the relation (a two-dimensional table). The relational database can contain one or more of these relations, with each table consisting of a unique set of rows and columns. Each row contains a unique instance of data or single record for the categories defined by the columns. This column consist the attributes of the data or called fields. This unique instance of data is identified by the primary key. Primary key can be used to reduce redundancy of the stored data. For example, a typical business order entry database will include a table that describe a customer with columns for name, address, phone number, and so forth. Another table will describe an order: product, customer, date, and so forth. A user of the database could obtain a view of the database that fitted the user’s need. For example, a branch office manager might like a view or report on all customers that had bought products after a certain date. A financial service manager in the same company could obtain a report on accounts that needed to be paid from the same table.

            Most of database system in use today is based on the relational system. It is considered as powerful because it requires few assumptions about how the data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. The most important feature of relational systems is that a single database can be spread across several relations.

            There are four types of relationship in a relational database, namely: one-to-one, one-to-many, many-to-one and many-to-many relationship. In one-to-one relationship, one record in a table is related to another record in a separated table. In one-to-many relationship, one record in a table is related in multiple records in another table. For many-to-one relationship, more than one record in a single relates to only one record in another table. The many-to-many relationship refers to more than one record in a table relates to more than one record in another table.

            Relational database is used to match data by using common characteristics found within the data set. This grouping of data can help user to easily access and manipulate data, data can be monitored without difficulty, data redundancy can b e reduced and data inconsistency can be avoided. For example, a data set containing all the cake order transaction in a bakeshop can be grouped according by the date the transaction occurred, buyer’s name and so on. 

It is a database which stores and manages the information in tables, which are composed of rows and columns. The name 'Relational' came from that fact that the tables in such a database are nothing but a collection of similar type of information stored in form of rows. This is the reason why a table is also referred to as a relation in such a database.

2. Importance of Relational Database.

The relational model is the basis for any relational database management system (RDBMS). A relational model has three core components: a collection of objects or relations, operators that act on the objects or relations, and data integrity methods. In other words, relational database has a place to store the data, a way to create and retrieve the data, and a way to make sure that the data is logically consistent.

Usually, only one data type can be stored in a database table column. Columns in a database table have a fixed value. Single rows of a database table are uniquely identified by a unique value (typically a primary key). Multiple users can access a database table at the same time, with various combinations of read and write capabilities in different parts of the database. A database usually has transaction-control capabilities, making it possible to “roll back” a change if something happened to prevent it from completing successfully (such as a power failure). There are many tools for repairing and recovering databases.

In addition to being relatively easy to create and access, a relational database has the important advantage of being easy to extend. After the original database creation, a new data category can be added without requiring that all existing applications be modified.

The following are also the importance of relational database:

·         Data entry, updates and deletions will be efficient.

·         Data retrieval, summarization and reporting will also be efficient.

·         Since the database follows a well-formulated model, it behaves predictably.

·         Since much of the information is stored in the database rather than in the application, the database is somewhat self-documenting.

·         Changes to the database schema are easy to make.

3. What is the integrity rule (its definition)?

The relational model defines several integrity rules that are necessary part of any relational database. An integrity rule is used to promote the accuracy and reliability of stored data within the database and assures that data is consistent, certified and can be reconciled. In other words, there is a close correspondence between the facts stored in the database and the real world it models. These are the rules which a relational database follows in order to stay accurate and accessible. These rules govern which operations can be performed on the data and on the structure of the database.

4. What are the two types or general form of integrity rules?

There are two types of integrity rules: general and database-specific.

a.            General integrity rules

The relational model specifies two general integrity rules. They are referred to as general rules, because they apply to all databases. The two general integrity rules are the entity integrity and referential integrity. 

 - The entity integrity rule is very simple. It says that primary keys cannot contain null data. The reason for this rule should be obvious. Users can't uniquely identify or reference a row in a table, if the primary key of that table can be null. It's important to note that this rule applies to both simple and composite keys. For composite keys, none of the individual columns can be null. Fortunately, Microsoft Access automatically enforces the entity integrity rule for the users. No component of a primary key in Microsoft Access can be null.   
-   The referential integrity rule says that the database must not contain any unmatched foreign key values. This denotes that:

·   A row may not be added to a table with a foreign key unless the referenced value exists in the referenced table.

·   If the value in a table that's referenced by a foreign key is changed (or the entire row is deleted), the rows in the table with the foreign key must not be "orphaned."

In general, there are three options available when a referenced primary key value changes or a row is deleted. The options are:

·   Disallow. The change is completely disallowed.

·   Cascade. For updates, the change is cascaded to all dependent tables. For deletions, the rows in all dependent tables are deleted.

·   Nullify. For deletions, the dependent foreign key values are set to null.

b.                  Database-Specific Integrity Rules

All integrity constraints that do not fall under entity integrity or referential integrity are termed database-specific rules or business rules. These types of rules are specific to each database and come from the rules of the business being modeled by the database. It is important to note that the enforcement of business rules is as important as the enforcement of the general integrity rules.

Rules in Microsoft Access 2.0 are now enforced at the engine level, which means that forms, action queries and table imports can no longer ignore your rules. Because of this change, however, column rules can no longer reference other columns or use domain, aggregate, or user-defined functions.

Without the specification and enforcement of business rules, bad data will get in the database. The old saying, "garbage in, garbage out" applies pertinently to the of business rules.

 Business rules should be implemented to ensure that the data entered meets the requirements of a particular setting for the database. Business rules should be documented as they are implemented. This should detail each rule, where and how it is implemented and enforced within the database design. Over time these rules may change, and having each and every rule documented will make it much easier to find and modify the design.

5.      Discuss primary, candidate, alternate and foreign keys.

A key is an entity in a table that distinguishes one row of data from another. The key may be a single column, or it may consist of a group of columns that uniquely identifies a record.

Keys are crucial to a table structure for many reasons, some of which are identified below:

    

        - They ensure that each record in a table is precisely identified.
        - They help establish and enforce various types of integrity.
        - They serve to establish table relationships.

A.         PRIMARY KEY

·         A primary key will uniquely identify a row each record. It may or may not provide information about the record it identifies. It must not be Null-able that is if it exists in a record it cannot have the value Null. Primary key data should be immutable. This means that once set, the information should never be changed. Although it is possible to modify the information in a primary key column, it is inadvisable. This is because the change would need to be propagated to every linked table to maintain referential integrity. This usually prevents the use of data such as a car's registration number as the primary key; if a car's registration number is changed, the primary key information would need to be updated.

B.         CANDIDATE KEY

·   A candidate key is a superkey that has no unique subset; it contains no columns that are not 

necessary to make it unique. It is a column in a table which has the ability to become a primary 

key. It uniquely identifies the rows in a table. There can be more than 1 candidate key in a table,

 in which one should be a primary key.

·    A candidate key is one that can identify each row of a table uniquely. Generally a candidate 

key becomes the primary key of the table. If the table has more than one candidate key, one of 

them will become the primary key, and the rest are called alternate keys.

C.        ALTERNATE KEY

·       Any of the candidate keys that are not part of the primary key is called an alternate key. 

Alternate Key or Unique Key is similar to Primary Key, except it accepts null Values .So that

 the records can still be entered submitting null values to this attribute.

D.        FOREIGN KEY

·        A foreign key is an attribute or combination of attributes in a table whose values match

 a primary key in another table.

·        It is a referential constraint between two tables. It identifies a column or a set of columns in one (referencing) table that refers to a set of columns in another (referenced) table.  It is a reference to a key in another relation, meaning that the referencing table has, as one of its attributes, the values of a key in the referenced table. Foreign keys need not have unique values in the referencing relation. Foreign keys effectively use the values of attributes in the referenced relation to restrict the domain of one or more attributes in the referencing relation.

·        A foreign key could be described formally as: "For all tables in the referencing relation projected over the referencing attributes, there must exist a table in the referenced relation projected over those same attributes such that the values in each of the referencing attributes match the corresponding values in the referenced attributes."

---