SIVANANDA REDDY GANGIREDDY: Normal Forms

First Normal Form

For a table to be in first normal form, data must be broken up into the smallest units possible.In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.

For in the above example city1 and city2 are repeating.In order that this table to be in First normal form you have to modify the table structure as follows.Also not that the Customer Name is now broken down to first name and last name (First normal form data should be broken down to smallest unit).

Figure :- 10.2 Customer table normalized to first normal form

Second Normal form

The second normal form states that each field in a multiple field primary keytable must be directly related to the entire primary key. Or in other words, each non-key field should be a fact about all the fields in the primary key. In the above table of customer, city is not linked to any primary field.

Figure :- 10.3 Normalized customer table.

Figure :- 10.4 City is now shifted to a different master table.

That takes our database to a second normal form.

Third normal form

A non-key field should not depend on other Non-key field. The field "Total" is dependent on "Unit price" and "qty".

Figure :- 10.5 Fill third normal form

So now the "Total" field is removed and is multiplication of Unit price * Qty. Note :- Fourth and Fifth normal form is left as a home work to users.

(B)What is denormalization ?

Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance.The sacrifice to performance is that you increase redundancy in database.

E R Modeling--Basic concepts

Feb 8th, 2008

E R Modeling

In software development

process there are mainly six phases such as:
► Requirement
► Architecture
► Design
► Implementation
► Testing
► Deployment

For this software development process different model are available such as
► Agile
► Clean room
► Iterative
► RAD
► RUP
► Spiral
► Waterfall
► XP
► Scrum
► Component Assembly Model…

Suppose if we take the RAD model, it is a linear sequential software process that emphasizes an extremely short development life cycle. It is a component based construction approach. This is a type of time boxing approach. The RAD model development can be broadly classified into four stages:

► Business Modeling <=>Requirement and Architecture
► Data Modeling & Process Modeling <=>Design [DB Modeling]
► Application Generation <=> Implementation
► Testing and Turnover <=> Testing and Deployment

One of the most important part of “Database Modeling” is ER Modeling.
Why do we need an ER Model?
►ER model is a conceptual design. It is a simple, minimal and accurate description of database requirements.
►Identification of entities, relationships and attributes.
►Diagrammatic representation
►Offers an effective frame work for integrating multiple applications.

Core elements
► Entity
► Attributes
► Relationship

type
► Attributes on relationship
** A database can be modeled as:
► A collection of entities
► Relation ship among entities.

Entity: A thing of significance about which information needs to be known is called an entity. An entity is a person, place, concept or thing about which business needs data.
Ex: Accounts Department --------Entity

An entity type is a collection of entities that share a common definition.
Ex: Department --------------------Entity Type

Entities can further be classified as Strong and Weak Entities.
Ex: Cinema ------ Strong Entity
Seat ------ Weak Entity
Seat is a weak entity because there would be no preference for a Seat without Cinema.

Attributes: Some thing which describes or qualifies an entity is known as an attribute. Attributes are the characteristics of entity type that we are interested in.
Ex 1: Employee ----------- Entity
Employee no -------- Attribute.
Attributes are further classified into:
► Simple and Composite attributes
► Multi valued attributes
► Derived attributes.

1.Simple and Composite attribute:
Ex: Employee----------Entity
Ename [FName, LName] = Tom Hanks
Address[ Street, City, State, Postal Code]= 12 Mile,Farmington Hills, MI,456112.
2.Multivalued attributes:
Ex: Phone Number [State code, Area code, Phone Number] = 248-186-1819.
3.Derived Attributes:
Ex: Age ---------------------------Can be calculated from date of birth
Experience---------------------Can be calculated from date of joining.

Attributes can also serve as keys while modeling:
► Candidate key. Ex: Empno, job.
► Primary key. Ex: Deptno, Dname.
► Composite Key. Candidate key with more than one attributes.

A relationship is an association among several entities. An attribute can also be a property of relation ship. There are mainly three types of relationships.

► Binary
Employee------Bank. Employee works for a bank and bank pays to an employee.

► Ternary
Employee-----Job----Branch. Employees can have different jobs (responsibilities) at different branches.

► Recursive
Employee------Employee. Employee works with another employee.

For a binary relationship set mapping should cardinality must be one of the following:
► One to One. Ex: Employee-Department.
► One to Many. Ex: Bank--------Employees.
► Many to One. Ex: Employee------Projects
► Many to Many. Ex: Bars serve different beers.

What are Entity Relationship Diagrams?

Entity Relationship Diagrams (ERDs) illustrate the logical structure of databases.

An ER Diagram

Entity Relationship Diagram Notations

Peter Chen developed ERDs in 1976. Since then Charles Bachman and James Martin have added some sligh refinements to the basic ERD principles.

Entity

An entity is an object or concept about which you want to store information.

Weak Entity

Attributes are the properties or characteristics of an entity.

Key attribute

A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute.

Multivalued attribute

A multivalued attribute can have more than one value. For example, an employee entity can have multiple skill values.

Derived attribute

A derived attribute is based on another attribute. For example, an employee's monthly salary is based on the employee's annual salary.

Relationships

Relationships illustrate how two entities share information in the database structure.

Learn how to draw relationships:
First, connect the two entities, then drop the relationship notation on the line.

Cardinality

Cardinality specifies how many instances of an entity relate to one instance of another entity.

Ordinality is also closely linked to cardinality. While cardinality specifies the occurences of a relationship, ordinality describes the relationship as either mandatory or optional. In other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships.

Cardinality Notations

Cardinality specifies how many instances of an entity relate to one instance of another entity.

Ordinality is also closely linked to cardinality. While cardinality specifies the occurances of a relationship, ordinality describes the relationship as either mandatory or optional. In other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships. When the minimum number is zero, the relationship is usually called optional and when the minimum number is one or more, the relationship is usually called mandatory.

There are many notation styles that express cardinality and they are all supported by SmartDraw.

Information Engineering

Chen

Bachman

Martin

Recursive relationship

In some cases, entities can be self-linked. For example, employees can supervise other employees.

What is UML?

UML stands for Unified Modeling Language. This object-oriented system of notation has evolved from the work of Grady Booch, James Rumbaugh, Ivar Jacobson, and the Rational Software Corporation. These renowned computer scientists fused their respective technologies into a single, standardized model. Today, UML is accepted by the Object Management Group (OMG) as the standard for modeling object oriented programs.

Types of UML Diagrams

UML defines nine types of diagrams: class (package), object, use case, sequence, collaboration, statechart, activity, component, and deployment.

Class Diagrams

Class diagrams are the backbone of almost every object oriented method, including UML. They describe the static structure of a system.

Package Diagrams

Package diagrams are a subset of class diagrams, but developers sometimes treat them as a separate technique. Package diagrams organize elements of a system into related groups to minimize dependencies between packages.

Object Diagrams

Object diagrams describe the static structure of a system at a particular time. They can be used to test class diagrams for accuracy.

Use Case Diagrams

Use case diagrams model the functionality of system using actors and use cases.

Sequence Diagrams

Sequence diagrams describe interactions among classes in terms of an exchange of messages over time.

Collaboration Diagrams

Collaboration diagrams represent interactions between objects as a series of sequenced messages. Collaboration diagrams describe both the static structure and the dynamic behavior of a system.

Statechart Diagrams

Statechart diagrams describe the dynamic behavior of a system in response to external stimuli. Statechart diagrams are especially useful in modeling reactive objects whose states are triggered by specific events.

Activity Diagrams

Activity diagrams illustrate the dynamic nature of a system by modeling the flow of control from activity to activity. An activity represents an operation on some class in the system that results in a change in the state of the system. Typically, activity diagrams are used to model workflow or business processes and internal operation.