Question

Consider a banking application where each account is identified first by an account number and then by its type (Savings, Chequing, and Loan). This scheme allows the customer to remember just one number instead of three, and then to pick a specific account by its type. Other attributes to be considered are the date the account was opened and the account’s current balance. Draw an ERD for the entity type Account with the attributes account number, account type, date opened, current balance. What is the key of the entity type? Is there an attribute that is likely a derived attribute? Show these attributes appropriately in the ERD.

Short answer

The key of the entity type 'Account' is the 'Account Number'. There do not appear to be any derived attributes among the ones listed. The ERD would have the 'Account' entity with 'Account Number' (primary key), 'Account Type', 'Date Opened', and 'Current Balance' as its attributes.

Step-by-step solution

Identify the Entity and its Attributes

Start by identifying the entity and its attributes. In this case, the entity is 'account' and it has attributes - account number, account type, date opened, and current balance.

Identify the Primary Key

Next, identify the primary key for the entity. This is a unique identifier for each record in the entity. Here, it should ideally be the 'account number' as it is unique for each account.

Identify the Derived Attributes

A derived attribute is the one that can be calculated by using other attributes. In this case, none of the attributes seem to be derived since none can be calculated from others.

Draw the ER Diagram

Next, draw the ER diagram with 'account' as the entity. Create a rectangle for the entity. Inside the rectangle, write down the name of the entity at the top. Then, write down the primary key attribute under the entity name in oval shape and underline it. Then, write down the rest of the attributes in oval shape, connecting them to the rectangle with lines.

Key concepts

ERD Attributes

When we talk about the Entity-Relationship Diagram, or ERD, we refer to a visual representation of how data is structured within a system, business, or application. In an ERD, 'entities' are objects or concepts about which data is stored. Each entity has 'attributes', which can be thought of as the properties or details of that entity.

For instance, in our banking application scenario, the entity is an 'Account'. Attributes for this account entity include the account number, account type (Savings, Chequing, Loan), the date opened, and the current balance. These attributes provide a complete overview of the account's identity and state. The ERD not only shows these attributes but also how they interconnect and where there are relationships with other entities (if any). This visual aspect aids immensely in understanding complex databases at a glance.

Primary Key

A 'primary key' is a fundamental part of relational database design. It's a unique identifier for each record in a database table. Think of it like a social security number for the data records: no two records can have the same primary key and every record must have one.

In our banking example, the account number serves as the primary key for the 'Account' entity. Even if two accounts have the same type and were opened on the same date with the same balance, they must have different account numbers. This unique attribute ensures that each account can be easily distinguished and referenced without confusion. In drawing an ERD, the primary key is often underlined to highlight its significance.

Database Design

The term 'database design' refers to the process of constructing a detailed data model of a database. This model outlines how data is to be stored, accessed, and managed within a database management system. A well-designed database is crucial for reliability, efficiency, and accuracy in data management.

In constructing a database for a banking system, great care must be taken to decide on the proper structure and relationships of the data. The chosen design impacts how we create tables, define relationships between data points, and enforce data integrity. During the design phase, you would determine what needs to be a primary key, how tables should link, and the rules that ensure that data remains consistent and accurate.

Relational Databases

Relational databases are the predominant choice for storing structured data. They organize data into tables which consist of rows and columns, much like a spreadsheet. Each row represents a unique record, and each column represents an attribute of that record.

The 'relational' aspect arises from the method databases use to store relationships between tables. These relations are defined by foreign keys, which reference primary keys in other tables, establishing links between them.

Relational databases are incredibly powerful for handling large quantities of structured data that can be logically organized into entities with attributes. Systems like the banking application in our example are built on these principles to ensure consistency, prevent data duplication, and maintain data integrity across all transactions.