Database Management Systems|Data Modeling using the Entity Relationship Model

5.1 Stages of Database Development

As in any other development process, the database design also involves a number of stages carries out at different contexts. How ever, initially the UoD needs to be identified and studied before moving on to other stages. The figure 5.1 will illustrate the stages in database development, and then we will take the important ones for the discussion.

In this lesson we will consider up to the conceptual design stage. That is we will cover Requirements collection and analysis and Conceptual design.

5.1.1 Requirement Collection and Analysis

• Database Designers interview the prospective users of the database

• Understand and document their data requirements

• The requirements should be as detailed and complete as possible

• In parallel, it is useful to specify the functional requirements of the application (These include

• Functional Requirements specify the user-defined operations or transactions applied to the database

5.1.2 Create Conceptual Schema

• Called Conceptual Design

• Use a High Level data model

• Represents the description of data requirements of the users

• Includes Detailed descriptions of entity types, relationships and constraints

5.2 Key concepts in conceptual data modelling

5.2.1 Entity-Relationship Model

• View the real world as Entities and Relationships

• Percept real world o Consists of a collection of objects

• These objects are called Entities

• Entities have Attributes

• Relationships (associations) may exist among these objects

• Overall logical structure (schema) is represented graphically by an E-R diagram

5.2.2 Basic Symbols used in E-R Diagrams

Single-tire or centralized database systems were not adequate specially when there are higher number or users expecting sophisticated facilities via the database systems such as Graphical User Interfaces. This requires a lot of computational power from the machine on which the system is running and therefore unable to cater for thousands of users.

5.2.3 ER Diagram

Eg:

5.2.4 Degree of Relationship

Specifies the number of entities that participate in a relationship.

• Unary – One Entity (Entity is related to itself)

• Binary – 2 Entities

• Ternary– 3 Entities

• N-ary – N number of Entities

In most of the cases ternary relationships are decomposed to two or more binary relationships

5.2.5 Connectivity and Cardinality

• Connectivity specifies the mapping of associated entity instances in a relationship

• Cardinality specifies the minimum and maximum number of entity occurrences in an association

5.2.5.1 One to One Relationships (1:1)

• At most, only one instance if Entity X is associated with one instance of Entity Y.

• This relationship exhibits a mandatory connectivity with a cardinality of 1

5.2.5.2 One to Many Relationships (1:N)

• At most one instance of a entity X is associated with zero, one or more instances of entity Y

• But for one instance for Entity Y, there exists only one instance of Entity X

5.2.5.3 Many to Many Relationships (M:N)

• One instance of Entity X is associated with Zero, One or Many instances of Entity Y and, One instance of Entity Y is associated with Zero, One or Many instances of Entity X

• Considered as non-specific

5.2.6 Entities and Attributes

• Entity has an independent existence

o Physical Existence ( Eg: Person, Car)

o Conceptual Existence (Eg: Job, Course)

• Entity has attributes

o Properties that describe the entities (Eg: Employee Name, Age, Address)

• Entity will have a value for each of these attributes

• Those values describe the particular object instance (entity occurrence)

5.2.6.1 Identifying an Entity

• Multiple occurrences

• Have at least one descriptive attribute, in addition to the key attribute

5.2.6.2 Attribute Types

• Atomic (Simple) Attributes

o Not divisible

o Has an individual meaning

o Eg: Blood Group

• Composite Attributes

o Can be further divided into subparts

o Can form a hierarchy

o Eg: Address -> House Number, Street, City, State

• Single Valued Attributes

o Have a single value for a particular entity

o Eg: Age

• Multi-Valued Attributes

o Can have a set of values to the same entity

o Different entity occurrences may have different numbers of values

for the particular attribute

o Eg: Places Worked before

• Stored Attributes

o Attributes of which the value is stored directly

o Eg: Date of Birth

• Derived Attributes

o Attributes of which the value is derived/determined using

another stored value

o The based value can either be an attribute of the same entity or, it

can be taken from a related entity

• Complex Attribute

o Formed by nesting composite attributes and multi-valued

attributes in an arbitrary way

o Eg: {MemberContact ({ContactNo(CountryCode, AreaCode,

PhoneNumber)}, Address ({ResidenceNo, StreetName, City,

Country })

• Null Values

o Entity may not have an applicable value for a particular attribute

o Null is a special value which represents the meaning of “Nothing”

5.2.6.3 Key Attribute

• Attribute that is used to identify the Entity Instance uniquely

• Denoted by highlighting the attribute name

• A candidate key is an attribute or set of attributes that uniquely identifies individual occurrences or an entity type.

• An entity type may have one or more possible candidate keys, the one which is selected is known as the primary key.

• Sometimes, the key attribute can be formed by combining more than one attributes. Those are called composite keys

5.2.6.4 Weak Entities

• Entities that do not have a key attribute

• Always related to specific entities of another entity type

• Cannot be identified without an Owner Entity (total participation constraint or existence dependency)

• Relates to the Owner Entity in combination with its attribute values

• Owner is called Identifying or Owner Entity type

• Relationship is called identifying relationship

• A weak entity type borrows key attributes from another entity type (called the owning or strong entity type) to uniquely identify entities.

• But it has a partial key (Complete key is composed by borrowing key attribute from owner entity)

• Room cannot exist without a building. Room No is always considered with the Building Name. Room No alone will not make a key

5.2.7 Constructing ER Models

• Identify entities

o list all potential entity types.

• Remove duplicate entities

• DO NOT include the system as an entity type

• List the attributes of each entity

o All properties to describe the entity which are relevant to the

application

o DO NOT have attributes of one entity as attributes of another entity

• Mark the primary keys.

o Attribute(s) uniquely identify instances of that entity type

o This may not be possible for some weak entities.

• Define the relationships

o Describe the cardinality and optionality of the relationships

• Examine the constraints between participating entities.

• Remove redundant relationships