Storing Information

Every organization has some information needs. A library keeps a list of members, books, due dates, and fines. A company needs to save information about employees, departments, and salaries. These pieces of information are called data.
Organizations can store data on various media and in different formats, such as a hard-copy document in a filing cabinet or data stored in electronic spreadsheets or in databases.

A database is an organized collection of information.
To manage databases, you need a database management system (DBMS). A DBMS is a program that stores, retrieves, and modifies data in databases on request. There are four main types of databases: hierarchical, network, relational, and (most recently) object relational.

Relational Database Concept

• Dr. E. F. Codd proposed the relational model for database systems in 1970.
• It is the basis for the relational database management system (RDBMS).
• The relational model consists of the following:
  – Collection of objects or relations
  – Set of operators to act on the relations
  – Data integrity for accuracy and consistency

Relational Model

The principles of the relational model were first outlined by Dr. E. F. Codd in a June 1970 paper titled “A Relational Model of Data for Large Shared Data Banks.” In this paper, Dr. Codd proposed the relational model for database systems.
The common models used at that time were hierarchical and network, or even simple flat-file data structures. Relational database management systems (RDBMS) soon became very popular, especially for their ease of use and flexibility in structure. In addition, a number of innovative vendors, such as Oracle, supplemented the RDBMS with a suite of powerful application development and user products, providing a total solution.

Components of the Relational Model
• Collections of objects or relations that store the data
• A set of operators that can act on the relations to produce other relations
• Data integrity for accuracy and consistency
For more information, see An Introduction to Database Systems, Eighth Edition (Addison-Wesley: 2004), written by Chris Date.

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.

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 database design.

Purpose of Models
Models help communicate the concepts that are 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 relationships among 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 their 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 database design
• Offers an effective framework for integrating multiple applications

ER Modeling (continued)

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.

ER Modeling Conventions

Entities
To represent an entity in a model, use the following conventions:

• Singular, unique entity name
• Entity name in uppercase
• Soft box
• Optional synonym names in uppercase within parentheses: ( ) Attributes
  To represent an attribute in a model, use the following conventions:
• Singular name in lowercase
• Asterisk (*) tag for mandatory attributes (that is, values that must be known)
• Letter “o” tag for optional attributes (that is, values that may be known)

Relationships

Symbol	Description
Dashed line	Optional element indicating “maybe”
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”

ER Modeling Conventions (continued)
Relationships
Each direction of the relationship contains:

• A label: for example, taught by or assigned to
• An optionality: either must be or maybe
• 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 sign: #
• 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 refers 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.

Relating Multiple Tables (continued)
Guidelines for Primary Keys and Foreign Keys

• You cannot 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 it must be null.
• A foreign key must reference either a primary key or a unique key column.

Terminology Used in a Relational Database
A relational database can contain one or many tables. A table is the basic storage structure of an RDBMS. A table holds all the data necessary about something in the real world, such as employees, invoices, or customers.
The slide shows the contents of the EMPLOYEES table or relation. The numbers indicate the following:

1. A single row (or tuple) representing all data required for a particular employee. Each row in a table should be identified by a primary key, which permits no duplicate rows. The order of rows is insignificant; specify the row order when the data is retrieved.
2. A column or attribute containing the employee number. The employee number identifies a unique employee in the EMPLOYEES table. In this example, the employee number column is designated as the primary key. A primary key must contain a value, and the value must be unique.
3. A column that is not a key value. A column represents one kind of data in a table; in this example, the data is the salaries of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved.

Terminology Used in a Relational Database (continued)

1. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in the same table or in another table. In the example, DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table.
2. A field can be found at the intersection of a row and a column. There can be only one value in it.
3. A field may have no value in it. This is called a null value. In the EMPLOYEES table, only those employees who have the role of sales representative have a value in the COMMISSION_PCT (commission) field.

Relational Database Properties

A relational database:

• Can be accessed and modified by executing structured query language (SQL) statements
• Contains a collection of tables with no physical pointers Uses a set of operators

Properties of a Relational Database
In a relational database, you do not specify the access route to the tables, and you do not need to know how the data is arranged physically.
To access the database, you execute a structured query language (SQL) statement, which is the American National Standards Institute (ANSI) standard language for operating relational databases. The language contains a large set of operators for partitioning and combining relations. The database can be modified by using the SQL statements.

Class-3 Finished. To Be Continued...

Main Author:

• Salome Clement
• Chaitanya Koratamaddi
• Nancy Greenberg.

copyright issue: i get a copy as an Oracle certified professional (OCP) and can use for training course purpose.