Database Fundamentals- Content Analysis

9

*

Database Principles: Fundamentals of Design, Implementations and Management

Lecture 10 - CHAPTER 11: CONCEPTUAL, LOGICAL AND PHYSICAL DATABASE DESIGN

9

*

In this chapter, you will learn:

• About the three stages of database design: conceptual, logical and physical.
• How to design a conceptual model to represent the business and its key functional areas.
• How the conceptual model can be transformed into a logically equivalent set of relations.
• How to translate the logical data model into a set of specific DBMS table specifications.
• About different types of file organization.
• How indexes can be applied to improve data access and retrieval.
• How to estimate data storage requirements.

9

*

Database Design

• Necessary to focus on the data
• Must concentrate on the data characteristics required to build database model
• At this point there are two views of data within system:
• Business view of data as an information source
• Designer’s view of the data structure, its access, and activities required to transform data into information

9

*

Database Design (cont..)

9

*

Database Design (cont..)

To complete the design phase, we must remember

these points:

• The process of database design is loosely related to analysis and design of larger system
• The data component is only one element of a larger system
• Systems analysts or systems programmers are in charge of designing other system components
• Their activities create procedures that will help transform data within database into useful information
• The Database Design does not constitute a sequential process
• Iterative process that provides continuous feedback designed to trace previous steps

9

*

Database Design (cont..)

9

*

3 Stages of Database Design

9

*

I. Conceptual Design (CD)

• In the CD, Data modeling is used to create an abstract database structure that represents real-world objects in most realistic way possible
• The CD must embody clear understanding of business and its functional areas
• Ensure that all data needed are in the model, and that all data in the model are needed
• Requires four steps:
• Data analysis and requirements
• Entity relationship modeling and normalisation
• Data model verification
• Distributed database design

9

*

I. Conceptual Design (cont..)

• Data Analysis and Requirements
• First step is to discover the data element characteristics
• Obtains characteristics from different sources
• Must take into account the business rules
• Derived from the description of operations which is a
• Document that provides precise, detailed, up-to-date, and thoroughly reviewed description of activities that define organization’s operating environment

9

*

I. Conceptual Design (cont...)

• Entity Relationship (ER) Modeling
  and Normalization
• Designer must communicate and enforce appropriate standards to be used in documentation of design
• Use of diagrams and symbols
• Documentation writing style
• Layout
• Other conventions to be followed during documentation

9

*

I. Conceptual Design (cont..)

9

*

I. Conceptual Design (cont..)

Fig 11.2 in your book

9

*

I. Conceptual Design (cont..)

9

*

I. Conceptual Design (cont..)

9

*

I. Conceptual Design (cont..)

9

*

I. Conceptual Design (cont..)

• Entity Relationship (ER) Modeling and
  Normalization (cont…)
• Data dictionary
• Defines all objects (entities, attributes, relations, views, and so on)
• Used in tandem with the normalization process to help eliminate data anomalies and redundancy problems

9

*

I. Conceptual Design (cont..)

• The Data Model Verification
• The ER Model must be verified against the proposed system processes to corroborate (confirm) that the intended processes can be supported by the database model
• A revision of the original design starts with careful reevaluation of the entities, followed by the detailed examination of the attributes that describe these entities
• Define design’s major components as modules:
• A module is an information system component that handles a specific function

9

*

I. Conceptual Design (cont..)

9

*

I. Conceptual Design (cont..)

9

*

I. Conceptual Design (cont..)

• Data Model Verification (cont..)
• Verification process starts with:
• Selecting a central (most important) entity
• Which is defined in terms of its participation in most of the model’s relationships
• The next step is to identify the module or subsystem to which central entity belongs and to define boundaries and scope
• Once the module is identified, the central entity is placed within module’s framework

9

*

I. Conceptual Design (cont..)

• Distributed Database Design
• Portions of the database may reside in different physical locations
• Designer must also develop data distribution and allocation strategies

9

*

II. DBMS Software Selection

• The selection of the software is critical to an information system’s smooth operation
• Advantages and disadvantages should be carefully studied
• Some common factors that may affect the purchasing decision are:
• Cost
• DBMS features and tools
• Underlying model: Hierarchical, network, relational etc…
• Portability
• DBMS requirements

9

*

III. Logical Design

• Used to translate the conceptual design into internal model for selected database management system
• Logical design is software-dependent
• Requires that all objects in the model be mapped to specific constructs used by selected database software

9

*

III. Logical Design (cont..)

• Used to translate the conceptual design into internal model for the selected database management system
• Logical design is software-dependent
• The logical design stage consists of the following phases:

Creating the logical data model.

Validating the logical data model using normalization.

Assigning and validating integrity constraints.

Merging logical models constructed for different parts for the database together.

Reviewing the logical data model with the user.

9

*

III. Logical Design (cont..)

9

*

III. Logical Design (cont…)

9

*

Review the complete logical model with the user

• Reviewing the completed logical model with the users to ensure that all the data requirements have been modelled
• Ensure that all the transactions are supported within the different user views.
• This stage is very important as any problems need to be solved before beginning the physical database design stage.

9

*

IV. Physical Database Design

• Physical database design requires the definition of specific storage or access methods that will be used by the database.
• Involves the translation of the logical model into a set of specific DBMS specifications for storing and accessing data.
• The ultimate goal must be to ensure that data storage is effective to ensure integrity and security and efficient in terms of query response time.

9

*

IV. Physical Design (cont..)

• Is the process of selecting data storage and data access characteristics of the database
• The storage characteristics are a function of device types supported by the hardware, the type of data access methods supported by the system, and DBMS
• Particularly important in older hierarchical and network models
• Becomes more complex when data are distributed at different locations

9

*

IV. Physical Database Design (cont..)

The following information needs to have been collected:

A set of normalized relations devised from the ER model and the normalization process.

An estimate of the volume of data which will be stored in each database table and the usage statistics.

An estimate of the physical storage requirements for each field (attribute) within the database.

The physical storage characteristics of the DBMS that are being used

9

*

Stages of Physical Database Design

Analysing data volume and database usage.

Translate each relation identified in the logical data model into a table.

Determine a suitable file organization.

Define indexes.

Define user views.

Estimate data storage requirements.

Determine database security for users.

Additional slides are after the summary

9

*

Analysing Data Volume and Database Usage

• The steps required to carrying out this phase are:

Identifying the most frequent and critical transactions.

Analysis of critical transactions to determine which relations in the database participate in these transactions.

9

*

Analysing Data Volume and Database Usage (cont….)

• Data volume and data usage statistics are usually shown on a simplified version of the ERD.
• This diagram is known as a composite usage map or a transaction usage map.

9

*

Analysing Data Volume and Database Usage (cont….)

9

*

Translate logical relations into tables

Identify the primary and any foreign keys for each table.

Identify those attributes which are not allowed to contain NULL values and those which should be UNIQUE.

• You can exclude the primary key attribute(s) here as the PRIMARY KEY constraint automatically imposes the NOT NULL and UNIQUE constraints.

9

*

Translate logical relations into tables

• For each relation you should:

Identify each attribute name and its domain from the data dictionary.

• Note any attributes which require DEFAULT values to be inserted into the attribute whenever new rows are inserted into the database.

Determine any attributes that require a CHECK constraint in order to validate the value of the attribute.

9

*

Translate logical relations into tables (continued)

9

*

Translate logical relations into tables (cont..)

9

*

Determine Suitable File Organisation

• Selecting the most suitable file organization is very important to ensure that the data is stored efficiently and data can be retrieved as quickly as possible.
• To do this the DBMS must know where this record is stored and how it can identify it.
• Look at the future growth of the database and whether the type of file organization provides some protection against data loss.

9

*

Determine Suitable File Organisation (cont..)

• There are three categories of file organizations:
• files which contain randomly ordered records known as heap files
• files which are sorted on one or more fields such as file organizations which are based on indexes
• files hashed on one or more fields known as hash files.

9

*

Determine Suitable File Organisation (cont..)

• Sequential File Organizations
• Records are stored in a sequence based on the value of one or more fields which is often the primary key.
• In order to locate a specific record the whole file must be searched and every record in the file must be read in turn until the required record is located.

9

*

Determine Suitable File Organisation (cont..)

• Heap File Organizations
• Records are unordered and inserted into the file as they come.
• Only used when a large quantity of data needs to be inserted into a table for the first time.

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

• Indexed File Organizations
• Records can be stored in a sorted or unsorted sequence and an index is created locate specific records quickly.

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

• Types of Indexes
• Primary index —these indexes are placed on unique fields such as the primary key.
• Secondary index —these indexes can be placed on any field in the file that is unordered.
• Multi-level index —is used where one index becomes too large and so is split into a number of separate indexes in order to reduce the search.

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

• B-trees
• Balanced or B-trees and are used to maintain an ordered set of indexes or data to allow efficient operations to select, delete and insert data.
• A special kind of B-tree is known as the B+-tree where all keys reside in the leaves. This tree is most
• often used to represent indexes which act as a ‘road_map’ so that each index can be quickly located

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

9

*

Conceptual Design of the DVD rental store

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

• Bitmap Indexes
• Bitmap indexes are usually applied to attributes which are sparse in their given domain.
• A two-dimensional array is constructed.
• One column is generated for every row in the table which we want to index with each column representing a distinct value within the bitmapped index.
• The two-dimensional array represents each value within the index multiplied by the number of rows in the table.

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

• Bitmap indexes are usually used when:
• A column in the table has low cardinality.
• The table is not used often for data manipulation activities and is large.
• Specific SQL queries reference a number of low cardinality values in their where clauses.

9

*

Determine Suitable File Organisation (cont..)

• Join Index
• Can be applied to columns from two or more tables whose values come from the same domain.
• It is often referred to as a bitmap join index and it is a way of saving space by reducing the volume of data that must be joined.
• The bitmap join stores the ROWIDS of corresponding rows in a separate table.

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

9

*

Determine Suitable File Organisation (cont..)

• Hashed File Organizations
• Uses a hashing algorithm to map a primary key value onto a specific record address in the file.
• Records are stored in a random order throughout the file.
• Often referred to as random or direct files.

9

*

Define Indexes (cont..)

• In SQL, indexes are created using the CREATE INDEX statement.
• For example, if we wanted to create a primary index on the DVD_ID primary key field from the DVDs table the SQL would be:

CREATE UNIQUE INDEX DVDINDEX ON DVD(DVD_ID)

9

*

Define Indexes (cont..)

• As a general rule, indexes are likely to be used:
• When an indexed column appears by itself in a search criteria of a WHERE or HAVING clause.
• When an indexed column appears by itself in a GROUP BY or ORDER BY clause.
• When a MAX or MIN function is applied to an indexed column.
• When the data sparsity on the indexed column is high.

9

*

Guidelines for creating Indexes

• Create indexes for each single attribute used in a WHERE, HAVING, ORDER BY, or GROUP BY clause.
• Do not use indexes in small tables or tables with low sparsity.
• Declare primary and foreign keys so the query optimizer within a specific DBMS can use the indexes in join operations.
• Declare indexes in join columns other than PK/FK.

9

*

Define User Views

• During the conceptual design stage the different user views required for the database are determined.
• Using the relations defined in the logical data model, these views must now be defined.
• Views are often defined taking database security into account as they can help to define the roles of different types of users.

9

*

Estimate Data Storage Requirements

9

*

Security

• Data must be protected from access by unauthorized users
• Must provide for following:
• Physical security
• Password security
• Access rights
• Audit trails
• Data encryption
• Diskless workstations

9

*

Determine database security for users

• During physical database design security requirements must be implemented
• Database privileges for users will need to be established.
• For example, privileges may include selecting rows from specified tables or views, being able to modify or delete data in specified tables etc.

9

*

Determine database security for users

• During physical database design security requirements must be implemented
• Database privileges for users will need to be established.
• For example, privileges may include selecting rows from specified tables or views, being able to modify or delete data in specified tables etc.

9

*

Security in ORACLE

• The SQL commands GRANT and REVOKE are used to authorize or withdraw privileges on specific user accounts.
• For example, the following two SQL statements grant the account with the username ‘Craig’ the ability to select rows from the DVD table and the ability to create tables.

GRANT SELECT ON DVD TO Craig;

GRANT CREATE TABLE TO Craig;

9

*

Security in ORACLE (cont..)

• Removing these privileges can be done using the following SQL statements:

REVOKE SELECT ON MOVIE FROM Craig;

REVOKE CREATE TABLE FROM Craig;

9

*

Security in ORACLE (cont..)

• A role is simply a collection of privileges referred to under a single name.
• The major benefit of roles is that a DBA can add or revoke privileges from a role at any time.
• These changes will then automatically apply to all the users who have been assigned that role.

9

*

Security in ORACLE (cont..)

• For example, in the DVD rental store, the sales staff need to perform SELECT and UPDATE operations on the CUSTOMER table.
• The SQL command CREATE ROLE is used to create the role STAFF_CUSTOMER_ROLE:
• CREATE ROLE STAFF_CUSTOMER_ROLE;
• Once created, privileges can then be granted on selected database objects to the new role.

9

*

Security in ORACLE (cont..)

• For example:

GRANT SELECT ON CUSTOMERS TO STAFF_CUSTOMER_ROLE;

GRANT UPDATE ON CUSTOMERS TO STAFF_CUSTOMER_ROLE;

• The last stage then involves granting the role to individual users accounts, e.g. Frank:

GRANT STAFF_CUSTOMER_ROLE TO Frank;

9

*

Summary

• Conceptual database design is where the conceptual representation of the database is created by producing a data model which identifies the relevant entities and relationships within the system.

9

*

Summary (cont..)

• Logical database design is the second stage in the Database Life Cycle, where relations are designed based on each entity and its relationships within the conceptual model.

9

*

Summary (cont..)

• Physical database design is where the logical data model is mapped onto the physical database tables to be implemented in the chosen DBMS.
• The ultimate goal must be to ensure that data storage is used effectively, to ensure integrity and security and to improve efficiency in terms of query response time.

9

*

Summary (cont…)

• Selecting a suitable file organization is important for fast data retrieval and efficient use of storage space.
• Indexes are crucial in speeding up data access. Indexes facilitate searching, sorting, and using aggregate functions and even join operations.