Page 124 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE
P. 124
Unit 7: Relational Database Design
3. Logical Database Design: We must choose a DBMS to implement our database design and Notes
convert the conceptual database design into a database schema in the data model of the
chosen DBMS. Task here is to convert E-R schema into relational database schema.
4. Schema Refinement: The fourth step in database design is to analyze the collection of
relations in our relational database schema to identify potential problems and to refine it.
5. Physical Database Design: In this step we must consider typical expected workloads that
our database must support and further refine the database design to ensure that it meets
desired performance criteria. This step may simply involve building indexes on some
tables and clustering some tables, or it may involve a substantial redesign of parts of the
database schema obtained from the earlier design steps.
6. Security Design: In this step, we identify different user groups and different roles played
by various users (e.g., the development team for a product, the customer support
representatives, and the product manager). For each role and user group, we must identify
the parts of the database that they must be able to access and the parts of the database that
they should not be allowed to access and take steps to ensure that they can access only the
necessary parts.
Task Uses of Superkey in DBMS.
7.8.1 Logical Database Design
During logical design we transform the E-R diagrams that were developed during conceptual
design into relational database schemas.
Transforming E-R diagrams to relations is a relatively straightforward process with a well-
defined set of rules. In fact, many CASE tools can automatically perform many of the conversion
steps. However, it is important that we understand the steps in this process for three reasons:
1. CASE tools often cannot model more complex data relationships such as ternary
relationships and supertype/subtype relationships. For these situations we may have to
perform the steps manually.
2. There are sometimes legitimate alternatives where we will need to choose a particular
solution.
3. We must be prepared to perform a quality check on the results obtained with a CASE tool.
The ER model is convenient for representing an initial, high-level database design. Given an ER
diagram describing a database, there is a standard approach to generating a relational database
schema that closely approximates the ER design. We now describe how to translate an ER
diagram into a collection of tables with associated constraints, i.e., a relational database schema.
7.8.2 Entity Sets to Tables
An entity set is mapped to a relation in a straightforward way: Each attribute of the entity set
becomes an attribute of the table.
Notes Both the domain of each attribute and the (primary) key of an entity set.
LOVELY PROFESSIONAL UNIVERSITY 117
