Database Design
The Information System
The Information System
The database is part of a larger whole known as an information system (IS), which provides for data collection, storage, and retrieval
A complete information system is composed of people, hardware, and software, the database(s), application programs, and procedures
Systems analysis is the process that establishes the need for an information system and its extent
The process of creating an information system is known as systems development
The performance of an information system depends on the following factors:
Database design and implementation
Application design and implementation
Administrative procedures
The term database development describes the process of database design and its implementation
The Systems Development Life Cycle (SDLC)
The Systems Development Life Cycle (SDLC) traces the history of an information system
The SDLC provides the big picture within which database design and application development can be mapped out and evaluated
The traditional SDLC is divided into the following five phases:
Planning
Analysis
Detailed systems design
Implementation
Maintenance

The Systems Development Life Cycle (SDLC)
The SDLC planning phase yields a general overview of the company and its objectives
During the SDLC analysis phase problems that were defined during the planning phase are examined in greater detail
In the detailed systems design phase, the designer completes the design of the system’s processes
During the implementation phase, the hardware, DBMS software, and application programs are installed, and the database design is implemented
Maintenance can be grouped into three types: corrective, adaptive, and perfective
The Database Life Cycle
The Database Life Cycle
The database life cycle (DBLC) is a cycle that traces the history of a database within an information system
The DBLC is divided into the following six phases:
Database initial study
Database design
Implementation and loading
Testing and evaluation
Operation
Maintenance and evolution

The Database Life Cycle (DBLC)
The Database Initial Study
The overall purpose of the database initial study is to:
Analyse the company situation
Define problems and constraints
Define objectives
Define scope and boundaries

A Summary of Activities in the Databse Initial Study
Database Design
This is arguably the most critical DBLC phase, making sure that the final product meets user and system requirements.
In the process of database design, you must concentrate on the data characteristics required to build the database model.
At this point, there are two views of the data within the system,
the business view of data as a source of information and
the designer’s view of the data structure, its access, and the activities required to transform the data into information.
The figure below contrasts those views

Two Views of Data: Business Manager and Designer
Database Design
The process of database design is loosely related to the analysis and design of a larger system. The data component is only one element of a larger information system.
The systems analysts or systems programmers are in charge of designing the other system components. Their activities create the procedures that will help transform the data within the database into useful information.
The database design does not constitute a sequential process. Rather, it is an iterative process that provides continuous feedback designed to trace previous steps
The database design process is depicted below.

Database Design Process
Implementation and Loading
The output of the database design phase is a series of instructions detailing the creation of tables, attributes, domains, views, indexes, security constraints, and storage and performance guidelines
Virtualisation is a technique that creates logical representations of computing resources that are independent of the underlying physical computing resources
Testing and Evaluation
You should test for at least the following:
Physical security
Password security
Access rights
Audit trails
Application code and database security
Data encryption
Diskless workstations
Database backups can be performed at different levels:
Full backup/dump – all database objects are backed up in their entirety
Differential backup – only modified/updated objects since last full backup are backed up
Transaction log backup – only the transaction log operations that are not reflected in a previous backup are backed up
Operation
Once the database has passed the evaluation and testing stage, it is considered operational
The beginning of the operational phase starts the process of system evolution
Problems that could not have been foreseen during the testing phase being to surface during the operational phase
A critical aspect of an operational database system is compliance
Maintenance
Some required periodic maintenance activities include the following:
Preventative maintenance (backup)
Corrective maintenance (recovery)
Adaptive maintenance
Assignment of access permissions and their maintenance for new and old users
Generation of database access statistics
Periodic security audits based on the system-generated statistics
Monthly, quarterly, or yearly system usage summaries for internal billing or budgeting purposes
Conceptual Design
Conceptual design is the first stage in the database design process
It is a process that uses data-modeling techniques to create a model of a database structure that represents real-world objects as realistically as possible
The goal at this stage is to design a database independent of database software and physical details
The output of this process is a conceptual data model that describes main data entities, attributes, relationships, and constrains
Keep in mind the following minimal data rule:
All that is needed is there, and all that is there is needed
The conceptual design has four steps, which are listed below
STEP | ACTIVITY |
|---|---|
1 | Data analysis and requirements |
2 | Entity relationship modelling and normalisation |
3 | Data model verification |
4 | Distributed database design |
Data Analysis and Requirements
The first step in conceptual design is to discover the characteristics of the data elements. Therefore, the designer’s efforts are focused on:
Information needs
what output (reports and queries) must be generated by the system, what information does the current system generate, and to what extent is that information adequate?
Information users
who will use the information?
how is the information to be used?
what are the various end-user data views?
Information sources
where is the information to be found?
how is the information to be extracted once it is found?
Information constitution
what data elements are needed to produce the information?
what are the data attributes?
what relationships exist in the data?
what is the data volume?
how frequently is the data used?
what data transformations will be used to generate the required information?
The designer obtains information from a variety of sources including the following:
Developing and gathering end-user data views
Directly observing current system – existing and desired output
Interfacing with the systems design group
Description of operations is a document that provides a precise, detailed, up-to-date, and thoroughly reviewed description of the activities that define an organisation’s operation
Entity Relationship Modelling and Normalisation
The process of defining business rules and developing the conceptual model using ER diagrams can be described using the steps shown in the table below:
STEP | ACTIVITY |
|---|---|
1 | Identify, analyse, and refine the business rules |
2 | Indentify the main entities, using the results of Step 1 |
3 | Define the relationship among the entities, using the results of Steps 1 and 2 |
4 | Define the attributes, primary keys, and foreign keys for each of the entities |
5 | Normalise the entities (Remember that entities are implemented as tables in an RDBMS) |
6 | Complete the intitial ER diagram |
7 | Validate the ER model against the end users’ information and processing requirements. |
8 | Modify the ER model, using the results of Step 7 |
The following figure summarises the ER modelling interactions

ER Modeling Is an Iterative Process Based on Many Activities
The following figure summarises the array of design tools and information sources the designer can use to produce the conceptual model

Conceptual Design Tools and Information Sources
All objects (entities, attributes, relations, views, and so on) are defined in a data dictionary, which is used in tandem with the normalisation process to help eliminate data anomalies and redundancy problems. During this ER modeling process, the designer must:
Define entities, attributes, primary keys, and foreign keys. (The foreign keys serve as the basis for the relationships among the entities.)
Make decisions about adding new primary key attributes to satisfy end-user and processing requirements.
Make decisions about the treatment of composite and multivalued attributes.
Make decisions about adding derived attributes to satisfy processing requirements.
Make decisions about the placement of foreign keys in 1:1 relationships.
Avoid unnecessary ternary or higher degree relationships.
Draw the corresponding ER diagram.
Normalise the entities.
Include all data element definitions in the data dictionary.
Make decisions about standard naming conventions.
DBMS Software Selection
The selection of DBMS software is critical to the information system’s smooth operation
The following are common factors that affect the purchasing decision:
Cost
DBMS features and tools
Underlying model
Portability
DBMS hardware requirements
Logical Design
Logical design is the second stage in the database design process. The logical design goal is to design an enterprise-wide database that is based on a specific data model but independent of physical-level details. Logical design requires that all objects in the conceptual model be mapped to the specific constructs used by the selected database model.
The logical design is generally performed in four steps, which are listed in the table below
STEP | ACTIVITY |
|---|---|
1 | Map the conceptual model to logical model components. |
2 | Validate the logical model using normalisation. |
3 | Validate the logical model integrity constraints. |
4 | Validate the logical model against user requirements. |
Logical Design Activities
Mapping the Conceptual Model to the Relational Model
The first step in creating the logical design is to map the conceptual model to the chosen database constructs. Because most current database design projects are based on the relational database model, this section focuses on logical design using relational constructs. In the real world, logical design generally involves translating the ER model into a set of relations (tables), columns, and constraint definitions. The process of translating the conceptual model into a set of relations is depicted in the following table:
STEP | ACTIVITY |
|---|---|
1 | Map strong entities |
2 | Map supertype/subtype relationships |
3 | Map weak entities |
4 | Map binary relationships |
5 | Map higher-degree relationships |
Remember these steps are not sequential but iterative. The example of a Simple College ER model shown below illustrates this process.

Simple College Conceptual Model
The first step in the logical design stage is to map strong entities to tables. Recall from Module 4 that a strong entity is one that resides in the “1” side of all its relationships—that is, an entity that does not have a mandatory attribute that is a foreign key to another table. Therefore, the first entities to be translated into tables would be the EMPLOYEE and COURSE entities. In this case, you define the table name, its columns, and their characteristics. For example, the relation definitions for the strong entities of Simple College would be:
| ||
PRIMARY KEY: | CRS_CODE | |
| ||
PRIMARY KEY: | EMP_NUM | |
Once all strong entities are mapped, you are ready to map any entities involved in a supertype/subtype relationship or any weak entities. In the case of Simple College, the PROFESSOR entity is a subtype of the EMPLOYEE entity. PROFESSOR is also a weak entity because it inherits its primary key from EMPLOYEE and is existence-dependent on EMPLOYEE. At this point, you could also start defining the relationships between supertype and subtype entities. For example:
| ||
PRIMARY KEY: | EMP_NUM | |
FOREIGN KEY: | EMP_NUM REFERENCES EMPLOYEE | |
Next, you start mapping all binary relationships. In the previous example, you defined the supertype/subtype relationship between EMPLOYEE and PROFESSOR. This is an instance that demonstrates the iterative nature of the process. Continuing with the Simple College ER model, you would define the CLASS relation and define its 1:M relationships with PROFESSOR and COURSE:
| ||
PRIMARY KEY: | CLASS_CODE | |
FOREIGN KEY: | EMP_NUM REFERENCES PROFESSOR | |
CRS_CODE REFERENCES COURSE | ||
Next, you will proceed with all relationships between three or more entities until all relationships in the model are clearly defined. The logical design’s tables must correspond to the entities (EMPLOYEE, PROFESSOR, COURSE, and CLASS) shown in the conceptual design of the figure above, and the table columns must correspond to the attributes specified in the conceptual design. The final outcome of this process is a list of relations, attributes, and relationships that will be the basis for the next step.
An Example using Extended ER Modelling

Parking Lot EER Diagram
Relational Schema
Staff ( staffNo, fName, lName, extensionTelNo ) | ||
PRIMARY KEY: | staffNo | |
ParkingLot ( parkingLotNo, parkingLotName, location, capacity, noOfFloor ) | ||
PRIMARY KEY: | parkingLotNo | |
Covered ( spaceNo, staffNo, monthlyRate, parkingLotNo ) | ||
PRIMARY KEY: | spaceNo | |
FOREIGN KEY: | parkingLotNo REFERENCES ParkingLot(parkingLotNo) | |
staffNo REFERENCES Staff(staffNo) | ||
Uncovered ( spaceNo, staffNo, parkingLotNo ) | ||
PRIMARY KEY: | spaceNo | |
FOREIGN KEY: | parkingLotNo REFERENCES ParkingLot(parkingLotNo) | |
staffNo REFERENCES Staff(staffNo) | ||
Books ( staffNo, spaceNo, dateRequired, visitorVLicenseNo ) | ||
PRIMARY KEY: | staffNo, spaceNo | |
FOREIGN KEY: | staffNo REFERENCES Staff(staffNo) | |
spaceNo REFERENCES Covered(spaceNo) | ||
Validate the Logical Model Using Normalisation
The logical design should contain only properly normalised tables. The process of mapping the conceptual model to the logical model may unveil some new attributes or the discovery of new multivalued or composite attributes. Therefore, it’s very likely that new attributes may be added to tables, or that entire new tables may be added to the logical model. For each identified table (old and new), you must ensure that all attributes are fully dependent on the identified primary key and that the tables are in at least third normal form (3NF).
Activities such as normalisation take place at different stages in the design process
Each time you reiterate a step, the model is further refined and better documented
Functional dependencies among determinant and dependent attributes are evaluated and data anomalies are prevented via normalisation
Validate the Logical Model Integrity Constraints
All defined constraints must be supported by the logical data model
You must map constraints to the proper relational model constraints
Special attention is needed at this stage to ensure that all views can be resolved and that security is enforced to ensure the privacy of data
Validate the Logical Model Against User Requirements
The final step in the logical design process is to validate all logical model definitions against all end-user data, transaction, and security requirements
Once the steps of the logical design are complete, attention can now be placed on defining the physical requirements that allow the system to function within the selected DBMS/hardware environment
Physical Design
Physical design is the process of determining the data storage organisation and data access characteristics of the database to ensure its integrity, security, and performance
This is the last stage of the database design process
The physical design stage consists of the following steps:
STEP | ACTIVITY |
|---|---|
1 | Define data storage organisation. |
2 | Define integrity and security measures. |
3 | Determine performance measurements. |
Define Data Storage Organisation
Designers must determine the volume of data to be managed and the data usage patterns
Once that knowledge is obtained, the designer must do the following:
Determine the location and physical storage organisation for each table
Identify indexes and the type of indexes to be used for each table
Identify the views and the types of views to be used on each table
Define Integrity and Security Measures
In this step of physical design, the following two tasks must be addressed:
Define user and security groups and roles
Assign security controls
Determine Performance Measurements
Physical design becomes more complex when data is distributed at different locations because the performance is affected by the communication media’s throughput
Physical design performance measurement deals with fine-tuning the DBMS and queries to ensure that they will meet end-user performance requirements as identified in the previous stages
Database Design Strategies
Classical Design Approaches
The following are two classical approaches to database design
Top-down design starts by identifying the data sets and then defines the data elements for each of those sets
Bottom-up design first identifies the data elements (items) and then groups them together in data sets

Top-Down vs. Bottom-Up Design Sequencing
Even when a primarily top-down approach is selected, the normalisation process that revises existing table structures is inevitably a bottom-up technique. ER models constitute a top-down process even when the selection of attributes and entities can be described as bottom-up. Because both the ER model and normalisation techniques form the basis for most designs, the top-down versus bottom-up debate may be based on a theoretical distinction rather than an actual difference.
Centralised versus Decentralised Design Philosophies
Centralised design is a process by which all database design decisions are carried out centrally by a small group of people
This process is typical of relatively simple, small databases and can be successfully done by a single database administrator
Centralised design is suitable in a top-down design approach when the problem domain is relatively small, as in a single unit or department

Centralised Design
Decentralised design might be used when the system’s data component has a considerable number of entities and complex relations on which very complex operations are performed
It is also often used when the problem itself is spread across several operational sites and each element is a subset of the entire data set

Decentralised Design