Database systems are designed to manage large bodies of information. These large
bodies of information do not exist in isolation. They are part of the operation of
some enterprise whose end product may be information from the database or
may be some device or service for which the database plays only a supporting
Database design mainly involves the design of the database schema. The
design of a complete database application environment that meets the needs of
the enterprise being modeled requires attention to a broader set of issues. In
this text, we focus initially on the writing of database queries and the design of
1. Design Process
A high-level data model provides the database designerwith a conceptual framework
in which to specify the data requirements of the database users, and how
the database will be structured to fulfill these requirements. The initial phase of
database design, then, is to characterize fully the data needs of the prospective
database users. The database designer needs to interact extensively with domain
experts and users to carry out this task. The outcome of this phase is a specification
of user requirements.
Next, the designer chooses a data model, and by applying the concepts of
the chosen datamodel, translates these requirements into a conceptual schema of
the database. The schema developed at this conceptual-design phase provides a
detailed overview of the enterprise. The designer reviews the schema to confirm
that all data requirements are indeed satisfied and are not in conflict with one
another. The designer can also examine the design to remove any redundant
features. The focus at this point is on describing the data and their relationships,
rather than on specifying physical storage details.
In terms of the relational model, the conceptual-design process involves decisions
on what attributes we want to capture in the database and how to group
these attributes to form the various tables. The “what” part is basically a business
decision, and we shall not discuss it further in this text. The “how” part is mainly a
computer-science problem. There are principally two ways to tackle the problem.
The first one is to use the entity-relationship model; the other is
to employ a set of algorithms (collectively known as normalization) that takes as
input the set of all attributes and generates a set of tables.
A fully developed conceptual schema indicates the functional requirements
of the enterprise. In a specification of functional requirements, users describe the
kinds of operations (or transactions) that will be performed on the data. Example
operations include modifying or updating data, searching for and retrieving
specific data, and deleting data. At this stage of conceptual design, the designer
can review the schema to ensure it meets functional requirements.
The process of moving from an abstract data model to the implementation of
the database proceeds in two final design phases. In the logical-design phase, the
designer maps the high-level conceptual schema onto the implementation data
model of the database system that will be used. The designer uses the resulting
system-specific database schema in the subsequent physical-design phase, in
which the physical features of the database are specified. These features include
the form of file organization and the internal storage structures.
2. Database Design for a University Organization
To illustrate the design process, let us examine how a database for a university
could be designed. The initial specification of user requirements may be based
on interviews with the database users, and on the designer’s own analysis of
the organization. The description that arises from this design phase serves as the
basis for specifying the conceptual structure of the database. Here are the major
characteristics of the university.
• The university is organized into departments. Each department is identified
by a unique name (dept_name), is located in a particular building, and has a
• Each department has a list of courses it offers. Each course has associated with
it a course id, title, dept_name, and credits, and may also have have associated
• Instructors are identified by their unique ID. Each instructor has name, associated
department (dept_name), and salary.
• Students are identified by their unique ID. Each student has a name, an associated
major department (dept_name), and tot_cred (total credit hours the student
earned thus far).
• The university maintains a list of classrooms, specifying the name of the
building, room_number, and room capacity.
|These Topics Are Also In Your Syllabus|
|1||Introduction to Database Security Issues||link|
|2||Introduction to Database Security Issues||link|
|You May Find Something Very Interesting Here.||link|
|3||The Natural Join||link|