Skip to main content

Data Models..ER Model


A DBMS uses a data model, as its underlying structure. Data model is a collection of tools for describing data, data relationship, data semantics (meaning) and consistency constraints. Various data models can be classified into three groups.
1. OBJECT BASED LOGICAL MODEL
2. RECORD BASED LOGICAL MODEL
3. PHYSICAL DATA MODEL
A data model definition must include the definitions of all possible objects which can be represented by it, all possible operations (insertion, modification, deletion, retrieval etc) which can be performed on these objects, and any rules necessary to preserve the integrity of the data base constructed by it.

OBJECT BASED LOGICAL MODELS
These are used in describing the data at the conceptual level and view level. Some of the object based logical models are:
1. Entity relationship model
2. Object oriented model
3. Binary model
4. Semantic model
5. Infological model
6. Functional data model

1. Entity relationship model (ER Model)
It consists of a collection of basic objects called entities and relationships among these objects. This can be used for modeling real world situations.
Entity

An entity is an object and is distinguishable from other objects. It can be any object (person, place, thing, events or an abstract concept), within the scope of interest of the organization about which data is collected.
E.g. :: John, 04/01/1971 ; Account No. 19826 of SBT Edappally ; Republic day
Entity set
An entity set is a set of entities of the same type. The set of all persons having an account in a bank can be defined as the entity set “Customer”. The entity set “Account” may represent the set of all accounts in the bank. Entity set need not be disjoint (disjoint means sets without any common element. Such sets are also called mutually exclusive sets) A person entity may be an employee entity or a customer entity, both or neither.
Attributes

An entity is represented by a set of attributes ( properties ) that characterizes it. An entity set “Customer” can have attributes customer no. , customer name, customer house name etc. A “student” entity set can have attributes such as roll no., course, class, sex, date of birth , house name etc. An attribute is often called as a data item, data name, data field or elementary item.
Domain of the attribute - The set of permitted values of an attribute is called the domain of that attribute. The domain of different attribute may be different. ( Mutually exclusive) or same or a subset or superset of the other. The domain of the attribute account no., may be the set of all positive integers, may be between 1000 to 9999 The domain of the attribute customer name may be the set of all character strings of length 30.Every entity can be described by a set of pairs(attribute, data value ), one for each attribute of the entity set. e.g., a student entity can be described by {(name, `John'), ( roll no, 101), (DOB, 1/1/71)}.
The data base consists of a collection of entity sets, each of which consist of any number of entities of the same type. ie. the information about an organization can be represented with the help of a number of entities and each individual entity can be represented by a number of attributes. The values taken by the attributes are referred to as DATA. One set of values taken by the attributes of an entity is called ENTITY OCCURENCE. The entities are inter connected by certain relationships.
Relationships and relationship sets - A relationship is an association among several entities. For example, we can define a relationship which associates customer JOHN with ACCOUNT 11875. This specify that JOHN is a customer with ACCOUNT NO.11875.
A relationship set is a set of relationship of the same type. If we consider the two entity sets CUSTOMER and ACCOUNT, we can define a relationship set CUST_ ACC to denote the association between CUSTOMER and bank ACCOUNT.
A relationship set is a mathematical relation on M > = 2 entity sets. If E1, E2, .... En are entity sets, then a relationship set R will always be a subset of { (e1, e2, ....en ) / e1  E1, e2  E2, ......., en  En }. A relationship which involves two entity sets are called binary relationship. The function that an entity plays in a relationship is called its role.

FUNCTION OR MAPPINGS - An entity relationship P may define certain consistency constraints to which the contents of a data base must conform. One important constraint is on mapping cardinality . Mapping cardinality express the number of entities to which another entity can be associated via a relationship set. i.e. How many entities from one entity set can be associated with how many entities from another entity set.
For a binary relationship between the entity set A & B, the mapping cardinality must be any one of the following :
1. ONE TO ONE MAPPING (1 - 1)
An entity in A is associated with utmost one entity in B, and an entity in B is associated with utmost one entity in A.
This is the most simplest and rarest form of relationship among two sets. If we consider the two entity set men and women ,and if we assume that, polygamy is not allowed ,then the relationship 'WIFE OF' between these two entity sets is 1 -> 1 (one to one relationship does not imply that for every entity in one set there exists a related entity in the other set.)
2. ONE TO MANY MAPPING (1 - M)
An entity in A is associated with any number of entities in B and an entity in B is associated with utmost one entity in A , this relationship is one to many from A to B.
If we consider the two sets women and children, then the relationship 'child of 'which exist between these two entities is one to many from women to children ( 1 - M)
3. MANY TO ONE (M - 1)
An entity in A is associated with utmost one entity in B and an entity in B is associated with any number of entities in A . If we consider the two sets districts and states , the relationship ' DISTRICT IN' is many to one from district to states.
4. MANY TO MANY (M - M)
An entity in A is associated with any number of entities in B and an entity in B is associated with any number of entities in A . Consider the two entities, countries and products. Then the relationships export between these two entity sets will be many to many, because a country can export more than one product and a product can be exported by more than one country.
Fig 3. one to one mapping
Fig 4. one to many mapping
Fig 5. many to one mapping
Fig 6. many to many mapping

EXISTENCE DEPENDANCY - This is another consistency constraint. If the existence of an entity A depends on the existence of another entity B, then A is said to be existence dependant on B. Entity B is said to be the dominant entity and entity A is said to be the subordinate entity. Consider the entity sets account and transactions the relationship ACC-TRAN from ACCOUNT to transaction is MANY TO MANY. Here the transaction depends on account and hence we can say that transaction entity is existence dependant on ACCOUNT entity. ACCOUNT is the dominant entity and transaction is the subordinate entity.
SUPER KEY – A super key is a set of one or more attributes, which taken collectively, can be used for unique identification of entities in an entity set. ROLL NO. is a super key of a student entity set. Place is not a super key, as more than one student may come from the same place. But {ROLL NO., PLACE} is a super key. If A is a SUPER KEY , then any SUPER set of A is a SUPER KEY.
CANDIDATE KEY - SUPER KEYS for which, no proper subset is a SUPER KEY is called a CANDIDATE KEY. A CANDIDATE KEY is a set of one more attributes which taken collectively can uniquely identify an entity in an entity set and no proper subset of that cannot identify an entity in the entity set uniquely. Several distinct sets of attributes can be CANDIDATE KEYS.
E.g. : In a class {rollno}, {name}, {house name, place, DOB ,sex} are CANDIDATE KEYS. But {rollno, name} is not a CANDIDATE KEY.{ roll no } is a SUPER KEY.
PRIMARY KEY - Primary key is any CANDIDATE KEY chosen by the database designer as the principal means of identifying the entities within an entity set. An entity set may have one primary key , any number of candidate keys and any number of super keys.
STRONG AND WEAK ENTITY SET - An entity set with sufficient attributes to form a primary key is called a strong entity set. (an entity set that has a primary key). eg: student , account.
An entity set that does not have sufficient number of attributes to form a primary key is called weak entity set. (an entity set without a primary key). eg: Transaction.
Consider the entity set transaction with attributes transaction no., date of transaction, amount and type of transaction. Transaction on different account may share the same transaction number .This entity set does not have a primary key and hence it is a weak entity set . In order for a weak entity set to be meaningful , it must be associated with a strong entity set through a relationship . A member of a strong entity set is the dominant entity and a member of a weak entity set is a subordinate entity.
DISCRIMINATOR - A weak entity set will not have a primary key and hence there should be some means for distinguishing among the entities in a weak entity set, which depends on one particular strong entity. The discriminator of a weak entity set is a set of attributes that allows this distinction to be made . The discriminator of the weak entity transaction is the transaction number since a transaction number uniquely identifies a transaction for each account .The primary key of a weak entity set is formed by the primary key of a strong entity set on which it is existence dependant plus the discriminator of the weak entity set. {account no, transaction no} is the primary key of the transaction entity set. Here the account number identifies the dominant entity of a transaction and transaction number distinguishes transaction entities with in the same account.
ATTRIBUTES OF A RELATIONSHIP SET - Let R be a relationship set involved in entity sets E1, E2....Em . Let the primary key (Ei) denote the set of attributes which form the primary key of the entity set Ei. If R has no descriptive attributes , the attributes describing individual relationship in R, denoted by attribute(R) will be primary key (E1) U primary key (E2) U ... primary key (Em). If R has descriptive attributes {d1,d2,...dn} then attribute(R) will be primary key(E1) U primary key(E2) U ...... primary key(Em) U {d1 d2 ...dn}.
e.g.: consider the entity sets accounts with primary key account and customer with primary key customer no. The relationship CUST_ACC has the descriptive attribute date of opening the account , then attribute(CUST_ACC) = primary key (customer) U primary key(ACCOUNT) U { descriptive attribute of cust-acc} = {customer no} U {ACCNO} U { date of opening}. ie CUST_ACC{customer no, account no, date of opening}.

Comments

Popular posts from this blog

KTU-FOSS LAB Solutions

Write shell scripts to show the following  ( you can write menu driven programs)
 Currently logged user and his logname ( logname)  Your current shell ( echo $SHELL)  Your home directory ( echo $HOME)  Your operating system type (echo $OSTYPE)  Your current path setting ( echo $PATH)  Your current working directory ( echo $PWD )  Show Currently logged  users ( w or who -H)      Show only the user name of logged users in the host ( users)      Details of last login ( last cek....where cek is the user id )  About your OS and version, release number, kernel version ( uname -a or  cat  /proc/version)  Show all available shells ( cat /etc/shells )  Show mouse settings (cat  /sys/class/input/mouse*/device/name )  Show computer CPU information       CPU details      ( cat /proc/cpuinfo | more )       Show information on  CPU architecture ( lscpu)       Number of Processor core ( nproc)  Show memory information       Memory details ( cat /proc/meminfo | more )       Display file system disk usage ( d…

Important Directories and Files

Important Directories
/bin                            holds the “essential” Linux commands and utilities /boot                          holds files required for boot process (kernel, vmlinuz, grub) /dev                            holds device files (hard drive, USB, CD-ROM, etc.) /etc                             holds system configuration files /etc/init.d                    holds scripts to start/stop network services /etc/rc.d                     holds system startup/shutdown scripts /etc/X11                      holds configuration files for X-windows /home                        holds user home directories (except for the root account) /lib                               holds system/shared library files /lost+found                holds files restored after system crash /mnt                            used as temporary mount point for CD-ROM, floppy, etc. /opt                              typically where large software applications are installed /proc                           holds kerne…

ER Diagrams to Table

REDUCING E-R DIAGRAM TO TABLE - A database which conforms to an E R diagram can be represented by collection of tables .For each entity set and for each relationship set in the database, we will create unique tables, which is assigned the name of the corresponding entity set or relationship sets . Each table has a no. of columns which have unique names. Each row in the table corresponds to an entity or a relationship.

REPRESENTATION OF STRONG ENTITY SET -Let E be a strong entity set with descriptive attributes a1, a2....aN . We represent this entity by table called E with N distinct columns, each of which corresponds to one of the attributes of E.

REPRESENTATION OF RELATIONSHIP SET - Let R be a relation ship set involving entity set E1,E2....En Let attribute(R) consists of 'm' attributes We can represent this relation ship set by a table called R with m distinct columns, each of which corresponds to one of the attributes in attribute (R) plus the primary key of E1..En.

REPRESENTI…