Database Design

1. Database Design Principles of database design

2. Relational Models Relational databases are designed to provide efficient structures for transaction processing • Minimize data redundancy • Identify stable structures • Provide for flexible queries

3. Relational Databases Based on tables which represent things about which we store data • Rows represent instances • Columns (attributes) are single valued properties Connections among tables are defined by common attributes

4. Data Modeling Describe the data structures and relationships that are required to support the users business model. The business model is the abstract description of the rules and processes by which the organization creates value.

5. Understand the business environment and uses of data Identify fields needed to produce required information store data in smallest parts avoid calculated fields Group fields into tables Determine each table’s primary key Include a common field in related tables Avoid redundancies Considerations in Relational Database Design

6. Conceptual Data Modeling • Understand the structure of data independent of process • Describe the information objects of the organization and the relationships between objects • Develop from knowledge of the organization and users

7. Entities • Something of importance that can be identified in the business environment. • Similar entities are grouped in entity classes. • Example - individual employees can be grouped in an entity class called EMPLOYEE. • Individual members of an entity class are called entity instances.

8. Entities • Entity rows cannot be duplicates • Entities must have more than one instance • Entities must have more than one attribute

9. Examples of Entities • Person - customer, • Object - item, product • Event - sale, purchase • Concept - course, flight

10. Identifying Entities • In the business context, entities are usually described by nouns. • The person, or organization using the system is usually not an entity. • Reports, screens and processing steps are not entities. • Entities with only one attribute are usually modeled as attributes of another entity.

11. Attributes Entities have attributes or properties that describe the characteristics of the entity. • All entity instances in an entity class have the same attributes. Attribute values will vary. • Attributes can be composites of other attributes. • The type of data described by the attribute is the same for all entity instances. The value can change but must be the same type (numbers, date, etc).

12. Keys Single field or combination of several fields to identify records for retrieval and processing • Primary key - unique identifier • Secondary key -identifier, but not necessarily unique • Foreign key - primary key in another related table

13. Example of Relational Tables

14. Examples of Relational Tables

15. Identifiers Entity instances have some name or identification string to identify them within the context of the environment. This name or string is an identifier. Unique identifiers identify a single entity instance. Non-unique identifiers can identify sets of instances.

16. Relationships A relationship is a connection or association between entity instances in different entity classes. It specifies what row connects with what row in associated tables.

17. Normalization A process for identifying structural problems with relational databases to reduce processing anomalies. • Theoretically based • Bottom-up (starts with attribute lists) • Step by step procedure

18. Steps in Normalization Every non-key attribute is fully functionally dependent on the primary key. No functional dependencies between non-key attributes.