Database
Content
Database Design
Dick Steflik
Entity-Relationship Diagrams
• • • Entity - any object or thing that is of interest to the organization and for which data must be stored Attribute - a descriptive element, used to characterize an entity or a relationship. Attributes are the fields that describe an entity. Relationship - describe the way that entities interact with each other, along with the business conditions under which the associate with one another. Cardinality - describes the number of entity relationships that can exist at each end of a relationship (one-to-one, one-to-many, many-tomany) Entity Supertype/subtype - an entity can be divided into two or more subtype entities, each subtyoe can have attributes of its own. Entity Instance - An entity instance is a single occurrence of an entity Synonyms - alias’ or other names for an entity or attribute Unique identifier - the combination of attributes and relationships that uniquely identify each instance of the entities
•
• • • •
Identifying Entities
• • Can be discovered by examining a variety of business documents such as reports, business plans, business process descriptions… Common business entities:
– – – – – – – – Customers Employees Vendors/Suppliers/Partners Parts Physical assets Products/Services Locations/places/sites/plants Financial assets
Identifying Entities (more)
• The preceding entities could be grouped into 5 generic categories:
– – – – – People - employees, vendors, suppliers Place - sites, offices, plants, customer locations Things - products, parts Logical concepts - conceptional objects, services Events - time sequence entities, recurring events
•
Naming entities:
– Always use singular names – each entity name must be unique – the entity name must clearly convey what the entity represents, qualifiers can be used for clarity
Describing Entities
• Each entity can be described with a set of properties:
– – – – – – – – Entity name Alias(s) Definition Existence constraints Business rules Volumetrics Attributes Example(s)
Identifying Relationships
• Relationships can be discovered by:
– – – – interviews with users joint facilitated sessions examining business documents analyzing events
Naming Relationships
• • • The relationship appears as a line connecting two entities. The relationship is written as a verb/verb phrase along the line use present tense for the verb/verb phrase
order
contains
order-item
Describing relationships
• • Relationships convey a lot of information as it is derived from the current business practices and ploicies of the organization Characteristics:
– Relationship name – Description : unambiguous description of the role and purpose of the relationship – Cardinality : describe how many instances of the parent entity are connected to how many instances of the child entity (on-to-one, one-tomany, many-to-many) – Existence constraints: does the relationship depend on other relationships – Business rules : business policy that defines one or more assertions that represent constraints. May dictate the business conditions under which a relationship is created, modified ot deleted.
Identifying Attributes
• • • Interviews with users joint facilitated sessions Examining various business or system documents
Naming attributes
• • • Each attribute of an entity should be uniquely identified names should always be singular a good practice is to always precede the attribute name with the entity name
Describing attributes
• • • • • Attribute name Alias(s) Definition Existence constraints - what other data is this attribute dependent on Business rules - business policy that defines one or more assertions that represent constraints. May dictate the business conditions under which a relationship is created, modified ot deleted. Domain - set of values which the attribute can take on Format - characterization (ex, text, date, time, money.media) Length - number of characters or digits Default value - initial value when first instantiated Derivation algorithm - for derived attributes, the algorithm that is used to derive the attribute
• • • • •
Business rules
• • • The policies by which an enterprise runs the business often designate constraints on the data used within the enterprise can be fount while developing the E-R data model:
– – – – in entities relationships attributes domains
Data Normalization
• • Normalization is a formal technique to develop a highly structured data model By using three basic normalization steps we:
– elimination of data redundancy – structuring of the data for ease of maintenance and modification, done by reducing the anomalies involved with creation, updating and deletion of data – end up with a more stable data model
First Normal Form
• • All attributes of an entity must have exactly one single occurrence in each instance a schema whose attributes are all single valued Employee Employee_id Employee_name Employee_address Employee_skills
Employee_skills is not single valued, there fore Employee is not in its first normal form, to make it first normal form it should be moved to its own structure keyed on Employee_id as on next page
First Normal form (cont)
Employee Employee_id Employee_name Employee_address Skill Employee_id Skill_id Skill_name Has
Second Normal Form
Every non-key attribute of an entity must be fully dependent upon the entity’s entire key. Employee Employee_id Employee_name Employee_address Dept_location Employee Employee_id Employee_name Employee_address Employs Works in Employs Works in Department Department_id Dept_name Wrong
Department Department_id Dept_name Dept_location Correct 2NF
Third Normal Form
An entity is in third normal form is: it is already in 2NF and every non-key attribute of this entity must not be dependent upon another non-key attribute Order Order_id Order_date Order_description Customer_id Customer_name Orders Order_id Order_date Order_description Belongs to places Customers Customer_id Customer_name CORRECT WRONG
Dick Steflik
Entity-Relationship Diagrams
• • • Entity - any object or thing that is of interest to the organization and for which data must be stored Attribute - a descriptive element, used to characterize an entity or a relationship. Attributes are the fields that describe an entity. Relationship - describe the way that entities interact with each other, along with the business conditions under which the associate with one another. Cardinality - describes the number of entity relationships that can exist at each end of a relationship (one-to-one, one-to-many, many-tomany) Entity Supertype/subtype - an entity can be divided into two or more subtype entities, each subtyoe can have attributes of its own. Entity Instance - An entity instance is a single occurrence of an entity Synonyms - alias’ or other names for an entity or attribute Unique identifier - the combination of attributes and relationships that uniquely identify each instance of the entities
•
• • • •
Identifying Entities
• • Can be discovered by examining a variety of business documents such as reports, business plans, business process descriptions… Common business entities:
– – – – – – – – Customers Employees Vendors/Suppliers/Partners Parts Physical assets Products/Services Locations/places/sites/plants Financial assets
Identifying Entities (more)
• The preceding entities could be grouped into 5 generic categories:
– – – – – People - employees, vendors, suppliers Place - sites, offices, plants, customer locations Things - products, parts Logical concepts - conceptional objects, services Events - time sequence entities, recurring events
•
Naming entities:
– Always use singular names – each entity name must be unique – the entity name must clearly convey what the entity represents, qualifiers can be used for clarity
Describing Entities
• Each entity can be described with a set of properties:
– – – – – – – – Entity name Alias(s) Definition Existence constraints Business rules Volumetrics Attributes Example(s)
Identifying Relationships
• Relationships can be discovered by:
– – – – interviews with users joint facilitated sessions examining business documents analyzing events
Naming Relationships
• • • The relationship appears as a line connecting two entities. The relationship is written as a verb/verb phrase along the line use present tense for the verb/verb phrase
order
contains
order-item
Describing relationships
• • Relationships convey a lot of information as it is derived from the current business practices and ploicies of the organization Characteristics:
– Relationship name – Description : unambiguous description of the role and purpose of the relationship – Cardinality : describe how many instances of the parent entity are connected to how many instances of the child entity (on-to-one, one-tomany, many-to-many) – Existence constraints: does the relationship depend on other relationships – Business rules : business policy that defines one or more assertions that represent constraints. May dictate the business conditions under which a relationship is created, modified ot deleted.
Identifying Attributes
• • • Interviews with users joint facilitated sessions Examining various business or system documents
Naming attributes
• • • Each attribute of an entity should be uniquely identified names should always be singular a good practice is to always precede the attribute name with the entity name
Describing attributes
• • • • • Attribute name Alias(s) Definition Existence constraints - what other data is this attribute dependent on Business rules - business policy that defines one or more assertions that represent constraints. May dictate the business conditions under which a relationship is created, modified ot deleted. Domain - set of values which the attribute can take on Format - characterization (ex, text, date, time, money.media) Length - number of characters or digits Default value - initial value when first instantiated Derivation algorithm - for derived attributes, the algorithm that is used to derive the attribute
• • • • •
Business rules
• • • The policies by which an enterprise runs the business often designate constraints on the data used within the enterprise can be fount while developing the E-R data model:
– – – – in entities relationships attributes domains
Data Normalization
• • Normalization is a formal technique to develop a highly structured data model By using three basic normalization steps we:
– elimination of data redundancy – structuring of the data for ease of maintenance and modification, done by reducing the anomalies involved with creation, updating and deletion of data – end up with a more stable data model
First Normal Form
• • All attributes of an entity must have exactly one single occurrence in each instance a schema whose attributes are all single valued Employee Employee_id Employee_name Employee_address Employee_skills
Employee_skills is not single valued, there fore Employee is not in its first normal form, to make it first normal form it should be moved to its own structure keyed on Employee_id as on next page
First Normal form (cont)
Employee Employee_id Employee_name Employee_address Skill Employee_id Skill_id Skill_name Has
Second Normal Form
Every non-key attribute of an entity must be fully dependent upon the entity’s entire key. Employee Employee_id Employee_name Employee_address Dept_location Employee Employee_id Employee_name Employee_address Employs Works in Employs Works in Department Department_id Dept_name Wrong
Department Department_id Dept_name Dept_location Correct 2NF
Third Normal Form
An entity is in third normal form is: it is already in 2NF and every non-key attribute of this entity must not be dependent upon another non-key attribute Order Order_id Order_date Order_description Customer_id Customer_name Orders Order_id Order_date Order_description Belongs to places Customers Customer_id Customer_name CORRECT WRONG
