Normalization 1nf 2nf 3nf bcnf

Oracle SQL Project

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Normalization & Database Connectivity (Week - 10, 11, & 12)

Database Systems

Avimanyou Vatsa, Ph.D. Department of Computer Science

Fairleigh Dickinson University

Slides are adapted and modified from c© Carlos Coronel, Steven Morris, Silberschatz, Korth, and Sudarshan. All rights

reserved.

September 17, 2020 Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Last Week, Quick Review

The basic commands and functions of SQL

How to use SQL for data administration (to create tables and indexes)

How to use SQL for data manipulation (to add, modify, delete, and retrieve data)

How to use SQL to query a database for useful information

How to use the advanced SQL JOIN operator syntax

About the different types of subqueries and correlated queries

How to use SQL functions to manipulate dates, strings, and other data

About the relational set operators UNION, UNION ALL, INTERSECT, and MINUS

How to create and use views and updatable views

How to create and use triggers and stored procedures

How to create embedded SQL

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

This Week, Learning Objectives

What normalization is and what role it plays in the database design process

About the normal forms 1NF, 2NF, 3NF, BCNF, and 4NF

How normal forms can be transformed from lower normal forms to higher normal forms

That normalization and ER modeling are used concurrently to produce a good database design

That some situations require denormalization to generate information efficiently

About database connectivity fundamentals

About various database connectivity technologies

How web-to-database middleware is used to integrate databases with the Internet

What services are provided by web application servers

Functions and Procedural Constructs

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Normalization

Evaluating and correcting table structures to minimize data redundancies

Reduces data anomalies Used while designing a new database structure

Analyzes the relationship among the attributes within each entity Determines if the structure can be improved

Normal forms First normal form (1NF) Second normal form (2NF) Third normal form (3NF)

Well designed 3NF structures meet the requirement of 4NF

Structural point of view: Higher normal forms are better than lower normal forms

Denormalization: Produces a lower normal form Results in increased performance and greater data redundancy

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

How Do We Start Work on Normalization Process?

Each table conforms to the concept of well-formed relations

Each table represents a single subject

No data item will be unnecessarily stored in more than one table

All nonprime attributes in a table are dependent on the primary key

Each table is void of insertion, update, and deletion anomalies

Ensures that all tables are in at least 3NF

Works on one relation at a time

Starts by:

Identifying the dependencies of a relation (table) Progressively breaking the relation into new set of relations

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Characteristic of Normal Forms

Normal Forms Characteristic First Normal Form (1NF) Table format, no repeating

groups, and PK identified

Second Normal Form (2NF)

1NF and no partial dependencies

Third Normal Form (3NF) 2NF and no transitive dependen- cies

Boyce-Codd normal form (BCNF)

Every determinant is a candidate key (special case of 3NF)

Fourth Normal Form (4NF) 3NF and no independent multival- ued dependencies

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Concepts & Types of Functional Dependencies

Partial dependency: Functional dependence in which the determinant is only part of the primary key

Assumption - One candidate key Straight forward Easy to identify

Transitive dependency: An attribute functionally depends on another nonkey attribute

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Conversion to First Normal Form

The domains of all attributes of relation schema are atomic (Non-atomic domains: Set of names, composite attributes, numbers like CS101, etc) Repeating group: Group of multiple entries of same type can exist for any single key attribute occurrence - Existence proves the presence of data redundancies Enable reducing data redundancies Steps are:

Eliminate the repeating groups Identify the primary key Identify all dependencies

Dependency diagram: Depicts all dependencies found within given table structure

Helps to get an overview of all relationships among table’s attributes Makes it less likely that an important dependency will be overlooked

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Conversion to First Normal Form

1NF describes tabular format in which: All key attributes are defined There are no repeating groups in the table All attributes are dependent on the primary key

All relational tables satisfy 1NF requirements

Some tables contain partial dependencies Subject to data redundancies and various anomalies

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Conversion to Second Normal Form

Steps are: Make new tables to eliminate partial dependencies Reassign corresponding dependent attributes

Table is in 2NF when it: Is in 1NF Includes no partial dependencies

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Conversion to Third Normal Form

Steps are: Make new tables to eliminate transitive dependencies - Determinant: Any attribute whose value determines other values within a row - Reassign corresponding dependent attributes

Table is in 3NF when it: Is in 2NF Contains no transitive dependencies

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Improving the Design

Evaluate PK assignments and naming conventions

Refine attribute atomicity

Atomic attribute: Cannot be further subdivided Atomicity: Characteristic of an atomic attribute

Identify new attributes and new relationships

Refine primary keys as required for data granularity

Granularity: Level of detail represented by the values stored in a table’s row

Maintain historical accuracy and evaluate using derived attributes

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Example: The Completed Database

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Example: The Completed Database

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Surrogate Key Considerations

Used by designers when the primary key is considered to be unsuitable

System-defined attribute

Created an managed via the DBMS

Have a numeric value which is automatically incremented for each new row

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

The Boyce-Codd Normal Form (BCNF)

Every determinant in the table should be a candidate key

Candidate key: Same characteristics as primary key but not chosen to be the primary key

Equivalent to 3NF when the table contains only one candidate key

Violated only when the table contains more than one candidate key

Considered to be a special case of 3NF

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

A Table That is in 3NF and not in BCNF

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Decomposition to BCNF

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Fourth Normal Form (4NF)

Table is in 4NF when it:

Is in 3NF Has no multivalued dependencies

Rules

All attributes must be dependent on the primary key, but they must be independent of each other No row may contain two or more multivalued facts about an entity

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Example: Tables with Multivalued Dependencies

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Example: A Set of Tables in 4NF

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Example: A Set of Tables in 4NF

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Normalization and Database Design

Normalization should be part of the design process

Proposed entities must meet required the normal form before table structures are created

Principles and normalization procedures to be understood to redesign and modify databases

ERD is created through an iterative process Normalization focuses on the characteristics of specific entities

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Example

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Initial Contracting Company ERD

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Modified Contracting Company ERD

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Incorrect M:N Relationship Representation

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Final Contracting Company ERD

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

The Implemented Database

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Denormalization

Design goals

Creation of normalized relations Processing requirements and speed

Number of database tables expands when tables are decomposed to conform to normalization requirements

Joining a larger number of tables:

Takes additional input/output (I/O) operations and processing logic Reduces system speed

Defects in unnormalized tables

Data updates are less efficient because tables are larger Indexing is more cumbersome No simple strategies for creating virtual tables known as views

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Common Denormalization Examples

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Database Connectivity

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Example: PHP, HTML, CSS – MySQL Connectivity

Connectivity, Insert, Select, and Other Operations

1. Download following *.php files from the blackboard and upload in “htdocs” at XAMPP. 2. Open in browser: localhost:8080/aviCode/connection.php

connection.php

insertOperation.php

operation.php

operationWithCheck.php

displayRecords.php

displayRecordsWithHtml.php

Note: 1. You may use any programming language: C, C++, java, C#, ASP.NET, Python, etc. 2. MySQL manual’s Connectors APIs: https://dev.mysql.com/doc/refman/8.0/en/connectors-apis.html

Avi Vatsa Database Systems

localhost:8080/aviCode/connection.php
https://dev.mysql.com/doc/refman/8.0/en/connectors-apis.html
Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Database Connectivity

Database middleware: Provides an interface between the application program and the database

Data repository - Data management application used to store data generated by an application program

Native SQL Connectivity: Connection interface provided by database vendors, which is unique to each vendor Interfaces are optimized for particular vendor’s DBMS - Maintenance is a burden for the programmer

Universal Data Access (UDA): Collection of technologies used to access any type of data source and manage the data through a common interface

ODBC, OLE-DB, ADO.NET form the backbone of MS UDA architecture

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

ODBC, DAO, and RDO

Open Database Connectivity (ODBC): Microsoft’s implementation of a superset of SQL Access Group Call Level Interface (CLI) standard for database access

Widely supported database connectivity interface Allows Windows application to access relational data sources by using SQL via standard application programming interface (API)

Data Access Objects (DAO): Object-oriented API used to access desktop databases such as MS Access and FileMaker Pro

Provides an optimized interface that expose functionality of Jet data engine to programmers DAO interface can be used to access other relational style data sources

Remote Data Objects (RDO) Higher-level object-oriented application interface used to access remote database servers

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Architecture

Dynamic-link libraries (DLLs) Implements ODBC, DAO, and RDO as shared code that is dynamically linked to the Windows operating environment

Components of ODBC Architecture High-level ODBC API through which application programs access ODBC functionality Driver manager that is in charge of managing all database connections ODBC driver that communicates directly to DBMS

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Using ODBC, DAO, and RDO to Access Databases

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Configuring an Oracle Data Source

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Object Linking and Embedding for Database (OLE-DB)

Database middleware that adds object-oriented functionality for access to data

Series of COM objects provides low-level database connectivity for applications

Types of objects based on functionality

Consumers (applications or processes) Providers (data or service)

Does not provide support for scripting languages

ActiveX Data Objects (ADO) provides: High-level application-oriented interface to interact with OLE-DB, DAO, and RDO Unified interface to access data from any programming language that uses the underlying OLE-DB objects

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Object Linking and Embedding for Database (OLE-DB) Architecture

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

ADO.NET

Data access component of Microsoft’s .NET application development framework

Microsoft’s .NET framework

Component-based platform for developing distributed, heterogeneous, interoperable applications Manipulates any type of data using any combination of network, operating system, and programming language Extends and enhances functionality critical for the development of distributed applications

DataSet: Disconnected memory-resident representation of the database

Contains tables, columns, rows, relationships and constraints Internally stored in XML format Data in DataSet is made persistent as XML documents

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

ADO.NET Framework

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Java Database Connectivity (JDBC)

Application programming interface that allows a Java program to interact with a wide range of data sources

Advantages of JDBC: Company can leverage existing technology and personnel training Allows direct access to database server or access via database middleware Allows programmers to use their SQL skills to manipulate the data in the company’s databases Provides a way to connect to databases through an ODBC driver

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

JDBC Architecture

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Web-to-Database Middleware

Web server is the main hub through which Internet services are accessed

Server-side extension: Program that interacts directly with the web server

Provides its services to the web server in a way that is totally transparent to the client browser Known as web-to-database middleware

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Web-to-Database Middleware

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Web Server CGI and API Interfaces

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

The Web Browser

Software that lets users navigate the web from their client computer

Interprets HTML code received from web server

Presents different page components in standard way

Web is a stateless system

Stateless system: Web server does not know the status of any clients

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Client-Side Extensions

Add functionality to Web browser

Types are:

Plug-in: External application automatically invoked by the browser when needed Java and JavaScript: Embedded in web page - Downloaded with the Web page and activated by an event ActiveX and VBScript: Embedded in web page

Downloaded with page and activated by event Oriented to Windows applications

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Web Application Servers & Features

Middleware application that expands the functionality of web servers by linking them to a wide range of services Used to:

Connect to and query database from web page Present database data in a webpage using various formats Create dynamic web search pages Create webpages to insert, update and delete data Enforce referential integrity Use simple and nested queries and program logic to represent business rules

Web Application Server Features Integrated development environment Security and user authentication Computational languages Automation generation of HTML pages Performance and fault -tolerant features Database access with transaction management capabilities Access to multiple services

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Web Database Development

Process of interfacing databases with the web browser

Code examples

ColdFusion PHP

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Cloud Computing Services

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Cloud Services: SQL Data Services

Cloud computing-based data management service

Provides relational data management to companies Hosted data management and standard protocols Common programming interface

Advantages:

Reliable and scalable at a lower cost than in-house systems High level of fault tolerance Dynamic and automatic load balancing, automated data backup and disaster recovery are included Dynamic creation and allocation of processes and storage

Avi Vatsa Database Systems

Objective Normalization 1NF 2NF 3NF BCNF 4NF Design Example Denormalization Connectivity

Thanks!

Thank You! Questions?

Avi Vatsa Database Systems

Objective
Normalization
1NF
2NF
3NF
BCNF
4NF
Design
Example
Denormalization
Connectivity