Main purposes of data modeling include:
- to assist in understanding the meaning (semantics) of the data;
- to facilitate communication about the information requirements.
Building data model requires answering questions about
entities, relationships, and attributes
Conceptual Database Design
- Process of constructing a model of the data used in an enterprise, independent of all physical considerations
- built using the information in users’ requirements specification
- Conceptual data model is source of information for logical design phase
Logical Database Design
- Process of constructing a model of the data used in an enterprise based on a specific data model (e.g. Relational or Dimensional),
- but independent of physical considerations and a particular DBMS
Physical Database Design
- Process of producing a description of the database implementation on secondary storage
- Describes base relations, file organizations, and indexes used to achieve efficient access to data
- Also describes any associated integrity constraints and security measures
- Tailored to a specific DBMS
Application Design
- Design of user interface and application programs that use and process the database.
- Database design and application design are parallel activities.
- Includes two important activities:
- transaction design;
- user interface design.
Implementation
- Physical realization of the database and application designs
- This will include the database transactions implemented using a host programming language
usability
- Should also test usability of system (HCI)
- Evaluation conducted against a usability specification (measuring learnability, adaptability, etc.)
Concepts of the ER Model
Entity types
- Group of objects with same properties, identified by enterprise as having an independent existence
- Entity occurrence, uniquely identifiable object of an entity type.
Relationship types
- Set of meaningful associations among entity types.
- Relationship occurrence, uniquely identifiable association, which includes one occurrence from each participating entity type.
Attributes
Semantic net of Has relationship type
ER relationship diagram of Branch Has Staff
degree of a relationship
Number of participating entities in relationship.
- two is binary
- three is ternary
- four is quaternary
Examples: Binary Relationship
Example: Ternary Relationship
Example: Quaternary Relationship
relationship types
Recursive relationship - where same entity type participates more than once in different roles.
(Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship.)
Entities associated through two distinct relationships with role names
Attributes & Attribute Domains
Attribute
Property of an entity or a relationship type
- Example: A staff entity type may be described by the staffNo, name, position, and salary attributes
- The attributes hold values that describe each entity occurrence and represent the main part of the data stored in the database
Attribute Domain
Set of allowable values for one or more attributes
- Example: A house can only have between 1 - 15 rooms
- Attributes may share a domain
- Example: PrivateOwner, and BusinessOwner entity types share the same domain for theAddress and Branch attributes (Domain: all possible addresses)
simple attributes vs. composite attributes
Simple Attribute - composed of a single component with an independent existencen
Composite Attribute - composed of multiple components, each with an independent existence
Some attributes can be further divided to yield smaller components with an independent existence of their own; Your choice whether simple/composite.
- SIMPLE: Address = 163 Main St, Glasgow, G11 9QX
- COMPOSITE: Address can be divided into:
- No=163
- Street=Mainst
- City=Glasgow
- Postcode=G119QX
sub-domains
Domains can also be composed of domains
Example: the domain for the address attribute of the Branch entity is made up of subdomains:
- Street
- City
- Postcode.
single-valued, multi-valued, derived attributes
Single-valued Attribute - holds a single value for each occurrence of an entity type
Multi-valued Attribute - holds multiple values for each occurrence of an entity type
- multiple phone numbers for tel_No (office, mobile number)
Derived Attribute - represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type
- the value for the duration attribute of the Lease Entity is calculated from:
- the rentStart attribute and
- the rentFinish attributes
candidate, primary, composite key
Candidate Key - minimal set of attributes that uniquely identifies each occurrence of an entity type
- There might be more than one unique identifier, as such we need to define a primary key
Primary Key (PK) - A selected candidate key to uniquely identify each occurrence of an entity type
Composite Key - A candidate key that consists of two or more attributes; together are unique for each entity occurrence but not separately
