NORMALISATION

Database Normalization

A series of steps followed to obtain a database design that allows for efficient access and storage of data in a relational database .These steps reduce data redundancy and the chances of data becoming inconsistent.

A table in a relational database is said to be in normal form if it satisfies certain constraints. Codd’s original work defined three such forms but there are now five generally accepted steps of normalization. The output of the first step is called First Normal Form (1NF); the output of the second step is Second Normal Form (2NF), etc.

First Normal Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship

Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key

Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key.

Fourth Normal Form separates independent multi-valued facts stored in one table into separate tables.

Fifth Normal Form breaks out data redundancy that is not covered by any of the previous normal forms.

keys-Day10

Keys :

Surrogate key

A unique primary key generated by the RDBMS that is not derived from any data in the database and whose only significance is to act as the primary key.

A surrogate key is frequently a sequential number but doesn't have to be. Having the key independent of all other columns insulates the database relationships from changes in data values or database design and guarantees uniqueness.

Some database designers use surrogate keys religiously regardless of the suitability of other candidate keys However, if a good key already exists, the addition of a surrogate key will merely slow down access, particularly if it is indexed.

Intelligent key

A relational database which depends wholely on one or more other columns in the same table. An intelligent key might be identified for implementation convenience, where there is no good candidate key

For example, if the three-letter initials of a group of people are known to be unique but only their full names are recorded, a three letter acronym for their names (e.g. John Doe Smith -> JDS) would be an intelligent key.

Intelligent keys are a Bad Thing because it is hard to guarantee uniqueness, and if the value on which an intelligent key depends changes then the key must either stay the same, creating an inconsistency within the containing table, or change, requiring changes to all other tables in which it appears as a foreign key The correct solution is to use a surrogate

Partial key

A key which identifies a subset of a set of information items and which could narrow the subset to one item if other partial key(s) were combined with it.

Compound key

(Or "multi-part key", "concatenated key") A key which consists of more than one attribute of the body of information (e.g. database "record ") it identifies.

Alternate key

A candidate key which is not selected as a primary key

Unique key

A key which identifies only one body of information out of several.

Primary key

The candidate key selected as being most important for identifying a body of information (an entity, object or record).

Candidate key

One of several possible attributes or combinations of attributes which can be used to uniquely identify a body of information (a "record ").

Super Key

A Set of attributes that uniquely identify a tuple in a relation is called a super key.

Secondary Keys

Keys other than the candidate keys are called the secondary keys.

Foreign Key

A key column in a table that identifies records in a different tbale is called foreign key.

DB ADMIN-DAY9

Database Administrator

The database administrator is the person(s) in control of the DBMS. One goal of DBMS's is to permit centralized control.

Typical responsibilities of the database administrator:

· Scheme definition

· Storage structure and access method definition

· Scheme and physical organization modification

· Granting authorization for data access

· Integrity constraint specification

Database Users

People who interact with the DBMS:

· Application programmers

· Application programmers write application programs in some programming language (called the host language) which permits the use of DML commands

· Typical host languages: C, Cobol with special syntax to separate DML commands from host language commands

· A DML precompiler handles embedded DML commands before the program is compiled

· Application programs are used to ease common or complicated interaction with the DBMS

· Examples: generating paychecks, doing account transfers,

· Sophisticated users: interact with the DBMS using the DML directly. The query processor translates DML commands into commands to the database manager.

· Specialized users: interact with the DBMS in specialized ways:

· CAD

· Expert systems

· Graphical or audio data

· Temporal data

· Naive users: interact with the database through application programs.

DB MANAGER-DAY8

DBMS Components

· File manager

· Database manager

· Query processor

· DML precompiler

· DDL compiler

Database Manager :

The database manager is the program or program unit that provides the interface between the physical level and the conceptual level.

Functions of the database manager:

· Interaction with the file manager (file system)

· Minimizing file reads and writes, as disk access is slower than main memory acces

· Translating DML commands to file operations

· Integrity enforcement

· checking that consistency constraints are satisfied

· taking some action when they aren't

· Security enforcement

· Preventing unathorized access to data

· Example: through a password and security classification system

· Backup and recovery

· Detect when information in the database or data dictionary is lost or corrupted due to disk crash, power failure, software errors ...

· Restore the database to a previous consistent state

· Concurrency control - making sure that concurrent updates don't give surprising or inconsistent results.

The database manager for a small system typically does not implement all of these functions.

DATA LANGUAGES-DAY7

Data Definition Language(DDL)

The data definition language (DDL) is the language used to define and modify the conceptual scheme of the database.

· Often used to define/modify sub schemes

· The definition of the conceptual scheme (written in the DDL) is compiled into a file called the data dictionary

· The data dictionary is consulted for scheme information whenever data is read or modified

· A separate language called the data storage and definition language is used to access the physical scheme of the database

Data Manipulation Language (DML):

The language used at the conceptual and view levels to retrieve, insert, delete and modify information stored in the database

Query

A request for information retrieval. Example (in English): List all customers who have an account balance over $500

Query language

The part of the DML used for retrieving information

The terms query language and data manipulation language are often used interchangeably.

Example: SQL is a DML, but is usually called a query language.

DML's are classified by the kind of query language they include:

· Procedural DML's require the user to specify what data is needed and to provide an algorithm for getting the data. The algorithm is written in query language.
Examples: SQL, Quell

· Nonprocedural DML's require only that the user specify what data is needed
Examples: Data log, QBE

Notes:

· Like the distinction between procedural programming languages (C, C++, Ada) and nonprocedural languages (Prolog)

· DDL's manipulate schemes, while DML's manipulate instances

DATA INDEPENDENCY-DAY6

Data Independence

The ability to modify a scheme definition at one level without affecting the scheme at the next higher level

Physical data independence

The ability to change the physical scheme without affecting the conceptual scheme

Logical data independence

The ability to change the conceptual scheme without affecting sub-schemes

· Easy if information is added to the conceptual scheme

· Often impossible if information used in some sub-scheme is deleted from the conceptual scheme

Notes:

· Physical data independence implies that programs that use the conceptual level are unaffected by changes to the physical scheme

· Logical data independence implies that programs that use some sub-scheme are unaffected by changes to the conceptual scheme

· Data independence is like data abstraction in software engineering

SCHEMA-DAY5

Instances and Schemes

The data stored in the database at any given time is an instance of the database.

The overall design of the database is the database scheme.

Example:

Name	account number	balance
TOM CRUISE	1024	8725.38
DANY WALT	4977	10.50

Is an instance of a database with scheme (name, account number, balance)

Database systems have schemes (designs) at each level of abstraction:

· The physical scheme is the design at the physical level, i.e. as a file of records of a particular type

· The conceptual scheme is the design at the conceptual level. Example: (name, account number, balance) is a conceptual scheme.

· A subscheme is a design at the view level

· A sub scheme is part of a scheme

· Example: (name, account number) is a sub scheme of (name, account number, balance)

· The sub scheme controls what information can be seen using the corresponding view

· Example: the previous sub scheme could be used by someone preparing ATM cards for the bank

As with levels of abstraction, there is:

· One physical scheme

· One conceptual scheme

As many sub schemes as needed

MODELS-DAY4

Object-based logical models

Characteristics:

· Structure of the database is flexible

· Data constraints can be specified explicitly

Examples: * The entity-relationship (ER) model

*Object-oriented (OO) models

Common characteristics of ER and OO models:

· Data is viewed as sets of entities (objects) that represent things in the real world

· Entities (objects) in the system are distinct and uniquely identifiable

· Example: customer entities can be uniquely identified by social security number

· New kinds of objects (entities) can be constructed from old kinds

Record-based logical models

Characteristics:

· Data is viewed as fixed-format records of various types

· Example: one record type for customers, another for checking accounts, ...

Examples of record-based logical models:

· Relational model

· Network model

· Hierarchical model

Network model

· Data represented by collections of records

· Relationships between data items are represented by links between records

· Example: each customer record is linked to the checking account record of that customer

· Records and links can form an arbitrary graph (can have unconnected components and cycles)

Hierarchical model

· Again, data represented by collections of records and relationships by links

· Records and links form a tree (connected graph with no cycles)

The network and hierarchical models are low-level and mostly of historical interest.

DATA MODELS

A PICTORIAL REPRESENTATION SHOWING THE RELATION OF THE:
* DATA ABSTRACTION LEVELS
* DATA MODELS AND
* CHARACTERISTICS

DBMS-DAY3(DATA MODELS)

Data Models

A data model is a collection of conceptual tools for describing:

· Data

· Data relationships

· Data semantics

· Consistency constraints

Data models:

· Provide a way of thinking about data that isn't linked to the implementation of the database

· Are used at the conceptual and view levels.

Kinds of data models:

· Object-based logical models

· Record-based logical models

· Physical data models