Database Design: Dick Steflik

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

Department Works in

Department_id Dept_name

Employee_name

Wrong

Employee_address Dept_location

Employs

Employee Employee_id

Department Works in

Department_id

Employee_name

Dept_name

Correct

Employee_address

Dept_location

2NF

Employs

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 WRONG

Order_description Customer_id Customer_name Orders Order_id Order_date Order_description

Customers Belongs to places

Customer_id Customer_name

CORRECT