data base instance and reduction of er diagram

Ø  Database Instance - 

·         The data stored in database at a particular moment of time is called instance of database. 

·         Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database.

·        It is important that we distinguish these two terms individually. Database schema is the skeleton of database.

·        It is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make any changes to it.

·        A database schema does not contain any data or information.

·        A database instance is a state of operational database with data at any given time. It contains a snapshot of the database.

·        Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed.

Ø  Reduction ER diagrams to tables – a database conforming to an ER diagram can be represented by a collection of tables.

Example – student tables

1)     Mapping entity – an entity is a real world object with some attributes.

 

Mapping process –

·        Create table for each entity.

·        Entity attribute should become fields of table with their respective data type.

·        Declare primary key.

2)     Mapping relationship – 

Mapping process –

·        Create table for a relationship.

·        Adds the primary keys.

·        If relationship has any attribute, add each attribute as a field of table.

·        Declare all foreign keys constraints.

3)     Mapping weak entity –

 

 Mapping process –

·        Create table for weak entity.

·        Add all its attributes to table field.

·        Add primary key of identifying entity set.

·        Declare all foreign keys constraints.

4)     Mapping hierarchical entity – specialization and generalization comes in front of hierarchical entities sets.

Mapping process –

·        Create table for higher level entities.

·        Create table for lower level entities.

·        Add primary keys of higher level entities in the table of lower level entities.

·        Add attributes in higher level and lower level tables.

·        Add primary keys in higher level and lower level.