Design of an E-R Database Schema

By -

Design of an E-R Database Schema

The E-R data model gives us much flexibility in designing a database schema to model a given enterprise. In this section, we consider how a database designer may select from the wide range of alternatives. Among the designer’s decisions are:

• Whether to use an attribute or an entity set to represent an object (discussed earlier in Section 2.2.1)

• Whether a real-world concept is expressed more accurately by an entity set or by a relationship set (Section 2.2.2)

• Whether to use a ternary relationship or a pair of binary relationships (Section 2.2.3)

image

• Whether to use a strong or a weak entity set (Section 2.6); a strong entity set and its dependent weak entity sets may be regarded as a single “object” in the database, since weak entities are existence dependent on a strong entity

• Whether using generalization (Section 2.7.2) is appropriate; generalization, or a hierarchy of ISA relationships, contributes to modularity by allowing common

image

attributes of similar entity sets to be represented in one place in an E-R diagram

• Whether using aggregation (Section 2.7.5) is appropriate; aggregation groups a part of an E-R diagram into a single entity set, allowing us to treat the aggregate entity set as a single unit without concern for the details of its internal structure.

We shall see that the database designer needs a good understanding of the enterprise being modeled to make these decisions.

Design Phases

A high-level data model serves the database designer by providing a conceptual framework in which to specify, in a systematic fashion, what the data requirements of the database users are, 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 data model, 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. Since we have studied only the E-R model so far, we shall

use it to develop the conceptual schema. Stated in terms of the E-R model, the schema specifies all entity sets, relationship sets, attributes, and mapping constraints. The de- signer reviews the schema to confirm that all data requirements are indeed satisfied and are not in conflict with one another. She can also examine the design to remove any redundant features. Her focus at this point is describing the data and their relationships, rather than on specifying physical storage details.

A fully developed conceptual schema will also indicate 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; they are discussed in Chapter 11.

In this chapter, we cover only the concepts of the E-R model as used in the conceptual-schema-design phase. We have presented a brief overview of the database-design process to provide a context for the discussion of the E-R data model. Database design receives a full treatment in Chapter 7.

In Section 2.8.2, we apply the two initial database-design phases to our banking- enterprise example. We employ the E-R data model to translate user requirements into a conceptual design schema that is depicted as an E-R diagram.

Database Design for Banking Enterprise
We now look at the database-design requirements of a banking enterprise in more detail, and develop a more realistic, but also more complicated, design than what we have seen in our earlier examples. However, we do not attempt to model every aspect of the database-design for a bank; we consider only a few aspects, in order to illustrate the process of database design.
Data Requirements

The initial specification of user requirements may be based on interviews with the database users, and on the designer’s own analysis of the enterprise. 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 banking enterprise.

• The bank is organized into branches. Each branch is located in a particular city and is identified by a unique name. The bank monitors the assets of each branch.

• Bank customers are identified by their customer-id values. The bank stores each customer’s name, and the street and city where the customer lives. Customers may have accounts and can take out loans. A customer may be associated with a particular banker, who may act as a loan officer or personal banker for that customer.

• Bank employees are identified by their employee-id values. The bank administration stores the name and telephone number of each employee, the names of the employee’s dependents, and the employee-id number of the employee’s manager. The bank also keeps track of the employee’s start date and, thus, length of employment.

• The bank offers two types of accounts — savings and checking accounts. Ac- counts can be held by more than one customer, and a customer can have more than one account. Each account is assigned a unique account number. The bank maintains a record of each account’s balance, and the most recent date on which the account was accessed by each customer holding the account. In addition, each savings account has an interest rate, and overdrafts are recorded for each checking account.

• A loan originates at a particular branch and can be held by one or more customers. A loan is identified by a unique loan number. For each loan, the bank keeps track of the loan amount and the loan payments. Although a loan- payment number does not uniquely identify a particular payment among those for all the bank’s loans, a payment number does identify a particular payment for a specific loan. The date and amount are recorded for each payment.

In a real banking enterprise, the bank would keep track of deposits and withdrawals from savings and checking accounts, just as it keeps track of payments to loan accounts. Since the modeling requirements for that tracking are similar, and we would like to keep our example application small, we do not keep track of such de- posits and withdrawals in our model.

Entity Sets Designation

Our specification of data requirements serves as the starting point for constructing a conceptual schema for the database. From the characteristics listed in Section 2.8.2.1, we begin to identify entity sets and their attributes:

• The branch entity set, with attributes branch-name, branch-city, and assets.

• The customer entity set, with attributes customer-id, customer-name, customer- street; and customer-city. A possible additional attribute is banker-name.

• The employee entity set, with attributes employee-id, employee-name, telephone- number, salary, and manager. Additional descriptive features are the multivalued attribute dependent-name, the base attribute start-date, and the derived at- tribute employment-length.

• Two account entity sets — savings-account and checking-account — with the common attributes of account-number and balance; in addition, savings-account has the attribute interest-rate and checking-account has the attribute overdraft-amount.

• The loan entity set, with the attributes loan-number, amount, and originating- branch.

• The weak entity set loan-payment, with attributes payment-number, payment- date, and payment-amount.

Relationship Sets Designation

We now return to the rudimentary design scheme of Section 2.8.2.2 and specify the following relationship sets and mapping cardinalities. In the process, we also refine some of the decisions we made earlier regarding attributes of entity sets.

• borrower, a many-to-many relationship set between customer and loan.

• loan-branch, a many-to-one relationship set that indicates in which branch a loan originated. Note that this relationship set replaces the attribute originating- branch of the entity set loan.

• loan-payment, a one-to-many relationship from loan to payment, which documents that a payment is made on a loan.

• depositor, with relationship attribute access-date, a many-to-many relationship set between customer and account, indicating that a customer owns an account.

• cust-banker, with relationship attribute type, a many-to-one relationship set ex- pressing that a customer can be advised by a bank employee, and that a bank employee can advise one or more customers. Note that this relationship set has replaced the attribute banker-name of the entity set customer.

• works-for, a relationship set between employee entities with role indicators man- ager and worker; the mapping cardinalities express that an employee works for only one manager and that a manager supervises one or more employees. Note that this relationship set has replaced the manager attribute of employee.

E-R Diagram

Drawing on the discussions in Section 2.8.2.3, we now present the completed E-R diagram for our example banking enterprise. Figure 2.22 depicts the full representation of a conceptual model of a bank, expressed in terms of E-R concepts. The diagram includes the entity sets, attributes, relationship sets, and mapping cardinalities arrived at through the design processes of Sections 2.8.2.1 and 2.8.2.2, and refined in Section 2.8.2.3.

image

Comments

Post a Comment

Popular posts from this blog

Concurrency Control:Shadow Paging

By -
Image
Shadow Paging An alternative to log-based crash-recovery techniques is shadow paging . The shadow-paging technique is essentially an improvement on the shadow-copy technique that we saw in Section 15.3. Under certain circumstances, shadow paging may require fewer disk accesses than do the log-based methods discussed previously. There are, however, disadvantages to the shadow-paging approach, as we shall see, that limit its use. For example, it is hard to extend shadow paging to allow multiple transactions to execute concurrently. As before, the database is partitioned into some number of fixed-length blocks, which are referred to as pages . The term page is borrowed from operating systems, since we are using a paging scheme for memory management. Assume that there are n pages, numbered 1 through n . (In practice, n may be in the hundreds of thousands.) These pages do not need to be stored in any particular order on disk (there are many reasons why they do not, as we saw in Chapter 1
Read more

Entity-Relationship Model part2

By -
Image
Refining the COMPANY database schema by introducing relationships By examining the requirements, six relationship types are identified. All are binary relationships( degree 2) Listed below with their participating entity types: WORKS_FOR (between EMPLOYEE, DEPARTMENT) MANAGES (also between EMPLOYEE, DEPARTMENT) CONTROLS (between DEPARTMENT, PROJECT) WORKS_ON (between EMPLOYEE, PROJECT) SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE (as supervisor)) DEPENDENTS_OF (between EMPLOYEE, DEPENDENT) ER DIAGRAM – Relationship Types are: WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF R elationship Types In the refined design, some attributes from the initial entity types are refined into relationships: Manager of DEPARTMENT -> MANAGES Works_on of EMPLOYEE -> WORKS_ON Department of EMPLOYEE -> WORKS_FOR etc In general, more than one relationship type can exist between the same participating entity types MANAGES and WORKS_FOR are distinct relationshi
Read more

XML Document Schema

By -
Image
XML Document Schema Databases have schemas, which are used to constrain what information can be stored in the database and to constrain the data types of the stored information. In contrast, by default, XML documents can be created without any associated schema: An el- ement may then have any subelement or attribute. While such freedom may occasionally be acceptable given the self-describing nature of the data format, it is not generally useful when XML documents must be processesed automatically as part of an application, or even when large amounts of related data are to be formatted in XML. Here, we describe the document-oriented schema mechanism included as part of the XML standard, the Document Type Definition , as well as the more recently defined XML S chema . Document Type Definition The document type definition ( D TD ) is an optional part of an XML document. The main purpose of a DTD is much like that of a schema: to constrain and type the information present in the docume
Read more