Module 2
Database Design Fundamentals
a. Database Concepts
Before learning how to design a database, you need to be familiar with some
important database concepts related to relational databases, which are the types of
databases you examined in Module 1 and that you use throughout the rest of this text.
The terms entity, attribute, and relationship are important to understand when designing a
database; the concepts of functional dependence and primary keys are critical when
learning about the database design process.
A relational database is a sophisticated system designed to store and manage vast
amounts of structured data efficiently. At its core, it comprises a collection of tables,
formally termed relations, each representing a specific entity or concept within the
database schema. These tables consist of rows and columns, where rows correspond to
individual records or instances, and columns represent attributes or properties of those
records.
The concept of a relational database is grounded in the relational model proposed
by Edgar F. Codd in the 1970s, which introduced a revolutionary way to organize and
interact with data. Codd's model emphasized the importance of data independence,
allowing users to access and manipulate data without needing to understand its
underlying physical structure.
In a relational database, relationships between different entities are established
through keys, such as primary keys and foreign keys, which ensure data integrity and
consistency. Primary keys uniquely identify each record within a table, while foreign
keys establish links between tables, enabling the creation of complex relationships and
facilitating efficient data retrieval.
One of the key advantages of relational databases is their flexibility and
scalability. They allow users to perform complex queries, aggregate data, and extract
meaningful insights using Structured Query Language (SQL), a powerful language for
managing relational databases. SQL enables users to define, manipulate, and control data
with ease, making it a widely adopted standard in the field of database management.
Furthermore, relational databases offer robust transaction management
capabilities, ensuring the ACID (Atomicity, Consistency, Isolation, Durability) properties
essential for maintaining data integrity and reliability. Transactions, which represent a
series of database operations, can be executed atomically, ensuring that either all changes
are applied or none at all, thereby preserving data consistency.
Moreover, relational databases provide mechanisms for data security and access
control, allowing administrators to define user privileges and restrict unauthorized access
to sensitive information. By implementing authentication and encryption techniques,
organizations can safeguard their data against potential threats and breaches.
In recent years, the advent of cloud computing has revolutionized the way
relational databases are deployed and managed. Cloud-based database services offer
scalability, high availability, and cost-effectiveness, enabling organizations to focus on
innovation and business growth without worrying about infrastructure management.
In conclusion, relational databases play a pivotal role in modern data
management, providing a robust and flexible framework for storing, organizing, and
accessing structured data. With their ability to handle complex relationships, ensure data
integrity, and support transactional processing, relational databases remain a cornerstone
of information technology infrastructure in various industries.
There are some terms and concepts that are very important for you to know when
working in the database environment. The terms entity, attribute, and relationship are
fundamental when discussing databases. An entity is like a noun; it is a person, place,
thing, or event. For example, the entities of interest to KimTay Pet Supplies are
customers, invoices, and sales reps.
Entities play a pivotal role in modeling the various aspects of a domain within a
database system. Let's explore the entities pertinent to different industries, including
those relevant to schools, real estate agencies, and used car dealers, in greater detail,
highlighting their characteristics and relationships within the respective domains.
In summary, entities serve as the fundamental building blocks of a database,
capturing the essential elements of a domain and enabling efficient data management and
retrieval. By identifying and modeling relevant entities within their respective industries,
organizations can design robust database schemas that support their business objectives
and facilitate informed decision-making.
An attribute is a property of an entity. The term is used here exactly as it is used
in everyday English. For example, for the entity person, the list of attributes might
include such things as eye color and height. For KimTay Pet Supplies, the attributes of
interest for the entity customer are first name, last name, address, city, and so on. For the
entity faculty at a school, the attributes are faculty ID, name, office number, phone, and
so on. For the entity vehicle at a car dealership, the attributes are the vehicle
identification number, model, color, year, and so on.
Relationships play a vital role in database modeling by capturing the associations
and interactions between entities within a domain. Let's delve deeper into the concept of
relationships, using the example of KimTay Pet Supplies to illustrate the significance and
implications of these associations within the context of a business environment.
Customers: These entities represent individuals or organizations purchasing pet
supplies from KimTay Pet Supplies. Customers may include pet owners, pet care
businesses, veterinary clinics, and animal shelters. Attributes associated with customers
may include customer ID, name, contact information, purchasing history, and
preferences.
Sales Representatives: Sales representatives are employees of KimTay Pet
Supplies responsible for managing customer relationships, promoting products, and
facilitating sales transactions. Each sales representative is assigned a portfolio of
customers to nurture and support. Attributes associated with sales representatives may
include representative ID, name, contact information, territory or region, and
performance metrics such as sales volume and customer satisfaction ratings.
The relationship between customers and sales representatives is bi-directional and
reciprocal, reflecting a symbiotic partnership aimed at achieving common business
objectives such as sales growth, customer retention, and market expansion. By nurturing
and maintaining strong relationships between customers and sales representatives,
KimTay Pet Supplies can effectively meet the needs and preferences of its diverse
customer base, drive sales revenue, and sustain long-term business success.
In summary, relationships between entities in a database schema serve as the
linchpin that binds together various components of a business ecosystem. By modeling
and understanding these relationships, organizations like KimTay Pet Supplies can
optimize their operations, enhance customer engagement, and drive competitive
advantage in the marketplace.
Technically, you say that a sales rep is related to all of his or her customers, and a
customer is related to his or her sales rep. The relationship between sales reps and
customers is an example of a one-to-many relationship because one sales rep is
associated with many customers, but each customer is associated with only one sales rep.
In this type of relationship, the word many is used in a way that is different from
everyday English; it might not always mean a large number.
In the context of database modeling for KimTay Pet Supplies, the term "many"
carries significant implications regarding the associations between sales representatives
and customers. Let's explore this concept in greater detail, considering the various
scenarios and considerations that arise when defining and understanding these
relationships within the database schema.
By recognizing and accommodating the variability inherent in the relationships
between sales representatives and customers, the database schema for KimTay Pet
Supplies can effectively capture the complexity and nuances of customer management
within the business. This flexibility enables the organization to adapt to changing market
conditions, customer preferences, and business objectives while maintaining robust data
integrity and consistency.
Furthermore, the database schema should incorporate mechanisms for managing
and tracking these relationships over time, including functionalities for adding, removing,
and updating customer associations for sales representatives. This ensures that the
database remains reflective of the current state of customer relationships within the
organization, facilitating informed decision-making and strategic planning initiatives.
In summary, the term "many" in the context of sales representative-customer
relationships at KimTay Pet Supplies encompasses a spectrum of associations, ranging
from zero to multiple customers per sales representative. By understanding and
accommodating this variability within the database schema, the organization can
optimize its customer management processes, enhance sales effectiveness, and drive
sustainable business growth in a competitive marketplace.
How does a relational database handle entities, attributes of entities, and
relationships between entities? Entities and attributes are fairly simple. Each entity has its
own table. In the KimTay Pet Supplies database, there is one table for sales reps, one
table for customers, and so on. The attributes of an entity become the columns in the
table. In the table for sales reps, for example, there is a column for the sales rep ID, a
column for the sales rep’s first name, and so on.
What about relationships? At KimTay Pet Supplies, there is a one-to-many
relationship between sales reps and customers, meaning each sales rep is related to the
many customers that he or she represents, and each customer is related to the one sales
rep who represents the customer. How is this relationship implemented in a relational
database? If you want to determine the name of the sales rep who represents Billy Rufton
(customer ID 182), you would locate the row for Billy Rufton in the CUSTOMER table
and determine that the value for REP_ID is 10. Then you would look for the row in the
SALES_REP table in which the REP_ID is 10. The one rep with REP_ID 10 is Richard
Miller, who represents Billy Rufton.
On the other hand, if you want to determine the names of all the customers of the
rep named Susan Garcia, you locate the row for Susan Garcia in the SALES_REP table
and determine that the value in the REP_ID column is 05. Then you look for all the rows
in the CUSTOMER table on which the REP_ID is 05. After identifying Susan Garcia’s
rep number, you find that the many customers she represents are numbered 125 (Joey
Smith), 375 (Melanie Jackson), 543 (Angie Hendricks), and 795 (Randy Blacksmith).
You implement these relationships by having common columns in two or more
tables. The REP_ID column in the SALES_REP table and the REP_ID column in the
CUSTOMER table are used to implement the relationship between sales reps and
customers. Given a sales rep, you can use these columns to determine all the customers
that he or she represents; given a customer, you can use these columns to find the sales
rep who represents the customer.
In this context, a relation is essentially a two-dimensional table. If you consider
however, you can see that certain restrictions are placed on relations. Each column has a
unique name, and entries within each column should match this column name. For
example, if the column name is CREDIT_LIMIT, all entries in that column must be
credit limits. In addition, each row should be unique—when two rows are identical, the
second row does not provide any new information. For maximum flexibility, the order of
the columns and rows should be immaterial. Finally, the table’s design should be as
simple as possible by restricting each position to a single entry and by preventing
multiple entries (also called repeating groups) in an individual location in the table. A
table design that includes repeating groups.
When you remove the repeating groups, all of the rows are single-valued. This
structure is formally called a relation. A relation is a two-dimensional table in which the
entries in the table are single-valued (each location in the table contains a single entry),
each column has a distinct name, all values in the column match this name, the order of
the rows and columns is immaterial, and each row contains unique values. A relational
database is a collection of relations.
There is a commonly accepted shorthand representation to show the tables and
columns in a relational database: for each table, you write the name of the table and then
within parentheses list all of the columns in the table. In this representation, each table
appears on its own line. Using this method, you represent the KimTay Pet Supplies
database as follows: SALES_REP (REP_ID, FIRST_NAME, LAST_NAME,
ADDRESS, CITY, STATE, POSTAL, CELL_PHONE, COMMISSION, RATE)
CUSTOMER (CUST_ID, FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE,
POSTAL, EMAIL, BALANCE, CREDIT_LIMIT, REP_ID) INVOICES
(INVOICE_NUM, INVOICE_DATE, CUST_ID) INVOICE_LINE (INVOICE_NUM,
ITEM_ID, QUANTITY, QUOTED_PRICE) ITEM (ITEM_ID, DESCRIPTION,
ON_HAND, CATEGORY, LOCATION, PRICE).
Notice that some tables contain columns with duplicate names. For example, the
REP_ID column appears in both the SALES_REP table and the CUSTOMER table.
Suppose a situation existed wherein someone, or the database management system
(DBMS) might confuse the two columns. Note the DBMS is a set of programs that allows
users to store, manipulate, and retrieve data efficiently. For example, if you write
REP_ID, it is not clear which REP_ID column you want to use. You need a mechanism
for indicating the REP_ID column to which you are referring. One common approach to
solving this problem is to write both the table name and the column name, separated by a
period. Thus, you reference the REP_ID column in the CUSTOMER table as
CUSTOMER.REP_ID, and you reference the REP_ID column in the SALES_REP table
as SALES_REP.REP_ID. Technically, when you reference columns in this format, you
say that you qualify the names. It is always acceptable to qualify column names, even
when there is no potential for confusion. If confusion might arise, however, it is essential
to qualify column names.
b. Functional Dependence
The concept of functional dependence is crucial to understanding the rest of the
material in this module. Functional dependence is a formal name for what is basically a
simple idea. To illustrate functional dependence, suppose that the SALES_REP table for
KimTay Pet Supplies is structured. The only difference between the SALES_REP table
and the one is the addition of an extra column named PAY_CLASS.
Suppose that one of the policies at KimTay Pet Supplies is that all sales reps in
any given pay class earn their commissions at the same rate. To describe this situation,
you could say that a sales rep’s pay class determines his or her commission rate.
Alternatively, you could say that a sales rep’s commission rate depends on his or her pay
class. This phrasing uses the words determines and depends on in the same way that you
describe functional dependency. If you wanted to be formal, you would precede either
expression with the word functionally. For example, you might say, “A sales rep’s pay
class functionally determines his or her commission rate,” and “A sales rep’s commission
rate functionally depends on his or her pay class.” You can also define functional
dependency by saying that when you know a sales rep’s pay class, you can determine his
or her commission rate.
In a relational database, column B is functionally dependent on another column
(or a collection of columns), A, if at any point in time a value for A determines a single
value for B. You can think of this as follows: When you are given a value for A, do you
know that you can find a single value for B? If so, B is functionally dependent on A
(often written as A → B). You also can say that A functionally determines B.
At KimTay Pet Supplies, is the LAST_NAME column in the SALES_REP table
functionally dependent on the REP_ID column? Yes, it is. If you are given a value for
REP_ID, such as 10, there is a single LAST_NAME, Miller, associated with it. This is
represented as: REP_ID → LAST_NAME At this point, a question naturally arises: How
do you determine functional dependencies? Can you determine them by looking at
sample data, for example? The answer is no.
Consider the SALES_REP table, in which last names are unique. It is very
tempting to say that LAST_NAME functionally determines ADDRESS, CITY, STATE,
and POSTAL (or equivalently that ADDRESS, CITY, STATE, and POSTAL are all
functionally dependent on LAST_NAME). After all, given the last name of a rep, you
can find the single complete address; however, this is not always the case. What would
happen if multiple sales reps had the same last name?
What would happen if the last name of rep 20 was also Garcia? You would have
the situation illustrated. Because there are now two reps with the last name of Garcia, you
can no longer find a single address using a rep’s last name—you were misled by the
original data. The only way to determine functional dependencies is to examine the user’s
policies. This process can involve discussions with users, an examination of user
documentation, and so on.
The policy implemented by KimTay Pet Supplies regarding the hiring of sales
representatives based on their last names introduces an interesting dimension to the
understanding of database design and functional dependencies. Let's explore this scenario
in greater detail and consider its implications within the context of database
normalization and data integrity.
Firstly, the policy not to hire two representatives with the same last name implies
that the last name uniquely identifies each sales representative within the organization. In
database terminology, this suggests that the last name serves as a candidate key or a
unique identifier for the sales representative entity. As a result, if LAST_NAME uniquely
determines the other columns in the SALES_REP table, it implies a functional
dependency where knowledge of the last name alone is sufficient to determine all other
attributes associated with the sales representative, such as first name, contact information,
and sales performance metrics.
From a database design perspective, this scenario highlights the concept of
functional dependencies and the importance of identifying key attributes that uniquely
identify entities within a table. By recognizing that the last name uniquely identifies sales
representatives, database designers can ensure data integrity and prevent anomalies such
as duplicate entries or inconsistencies in the representation of sales personnel.
However, it's essential to consider the implications of this policy in the absence of
such a constraint. Without the restriction on hiring sales representatives with the same
last name, the last name alone may not suffice to determine all other attributes in the
SALES_REP table. In this case, additional attributes or combinations of attributes may be
required to uniquely identify sales representatives and avoid data redundancy or
ambiguity.
Furthermore, the policy underscores the importance of considering real-world
constraints and business rules when designing a database schema. By incorporating
policies and constraints into the database design process, organizations can align their
data management practices with operational requirements and regulatory standards,
thereby enhancing data quality, consistency, and reliability.
In summary, the policy implemented by KimTay Pet Supplies regarding the hiring
of sales representatives based on their last names offers valuable insights into the
principles of database design, functional dependencies, and data integrity. By recognizing
the unique identifier for sales representatives and incorporating relevant constraints into
the database schema, organizations can ensure robust data management practices that
support their business objectives and operational needs.
c. Primary Keys
Another important database design concept is the primary key. In the simplest
terms, the primary key is the unique identifier for a table. For example, the REP_ID
column is the unique identifier for the SALES_REP table. Given a rep ID in the table,
such as 10, there is only one row on which that rep ID occurs. Thus, the rep ID 10
uniquely identifies a row (in this case, the second row).
Defining the primary key of a table requires a precise understanding of its
properties and implications within the database schema. Let's delve deeper into the
concept of primary keys, considering their role in ensuring data integrity, enforcing
uniqueness, and facilitating efficient data retrieval and manipulation.
A primary key serves as a cornerstone in the relational database model, providing
a unique identifier for each record within a table. Functional Dependency: The primary
key, denoted by column A or a collection of columns, must ensure that all other columns
in the table are functionally dependent on it. This implies that the values of these other
columns are uniquely determined by the values of the primary key. In practical terms, this
means that each record in the table must be uniquely identified by its primary key, and
any changes or updates to the primary key should cascade to maintain consistency within
the table.
Non-redundancy: In addition to ensuring functional dependency, the primary key
must exhibit non-redundancy. This property implies that no subcollection of the columns
in the primary key possesses the same functional dependency as the primary key itself. In
other words, the primary key must be minimal in terms of the number of columns
required to uniquely identify each record. This ensures efficiency in data storage and
retrieval while minimizing the risk of redundancy and data anomalies.
By adhering to these properties, the primary key plays a crucial role in
maintaining data integrity and consistency within the database schema. It enables
efficient indexing, searching, and referencing of records, facilitating rapid access to
information and supporting various database operations such as joins, queries, and
updates.
Furthermore, the selection of an appropriate primary key is often guided by
considerations such as data distribution, domain constraints, and performance
requirements. While natural keys derived from real-world attributes may offer intuitive
identifiers, surrogate keys generated by the database system, such as auto-incrementing
integers, are commonly used to ensure simplicity, stability, and efficiency in primary key
management.
In summary, the primary key of a table is more than just a unique identifier—it is
a fundamental component that ensures data integrity, enforces uniqueness, and facilitates
efficient data management within the database schema. By adhering to the properties of
functional dependency and non-redundancy, database designers can create robust and
reliable data models that meet the needs of the organization and support its business
objectives effectively.
You can indicate a table’s primary key with a shorthand representation of a
database by underlining the column or collection of columns that comprise the primary
key. The complete shorthand representation for the KimTay Pet Supplies database is as
follows: SALES_REP (REP_ID, FIRST_NAME, LAST_NAME, ADDRESS, CITY,
STATE, POSTAL, CELL_PHONE, COMMISSION, RATE) CUSTOMER (CUST_ID,
FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, POSTAL, EMAIL,
BALANCE, CREDIT_LIMIT, REP_ID) INVOICES (INVOICE_NUM,
INVOICE_DATE, CUST_ID) INVOICE_LINE (INVOICE_NUM, ITEM_ID,
QUANTITY, QUOTED_PRICE) ITEM (ITEM_ID, DESCRIPTION, ON_HAND,
CATEGORY, LOCATION, PRICE).
d. Database Design
This section presents a specific method you can follow to design a database when
given a set of requirements that the database must support. The determination of the
requirements is part of the process known as systems analysis. A systems analyst
interviews users, examines existing and proposed documents, and examines
organizational policies to determine exactly the type of data needs the database must
support. This text does not cover this analysis. Rather, it focuses on how to take the set of
requirements that this process produces and determine the appropriate database design.
After presenting the database design method, it's crucial to delve deeper into its
application through a comprehensive exploration of sample requirements and the ensuing
database design process. This not only reinforces understanding but also provides
practical insights into how theoretical concepts translate into real-world solutions.
Let's consider a hypothetical scenario to illustrate this. Imagine a scenario where a
small business, let's call it "TechMart," wants to create a database to manage its
inventory, sales, and customer information. This scenario provides a rich opportunity to
demonstrate the database design method in action.
Firstly, we would start by gathering requirements from TechMart. This might
include understanding the types of products they sell, how they categorize them, the
information they want to capture about each product (such as name, price, quantity in
stock, supplier details, etc.), and how they want to track sales transactions (including
customer information, items purchased, quantity, total cost, etc.).
Next, we would analyze these requirements to identify the entities and
relationships involved. In this case, we might identify entities such as Products,
Suppliers, Customers, Sales, and possibly more depending on the complexity of the
business processes.
Once we have identified the entities, we would define their attributes and
relationships. For example, a Product entity might have attributes like ProductID, Name,
Price, QuantityInStock, and SupplierID (as a foreign key linking it to the Suppliers table).
Similarly, the Sales entity might have attributes like SaleID, CustomerID (as a foreign
key linking it to the Customers table), SaleDate, TotalCost, etc.
After defining the entities, attributes, and relationships, we would proceed to
create a conceptual schema, often represented using entity-relationship diagrams (ERDs).
This diagram visually depicts the entities, their attributes, and the relationships between
them.
Once the conceptual schema is finalized, we would translate it into a logical
schema, which involves defining the tables, their columns, primary and foreign keys,
constraints, and indexes. This step is crucial as it lays the foundation for the actual
database implementation.
With the logical schema in place, we would then proceed to implement the
database using a database management system (DBMS) such as MySQL, PostgreSQL, or
SQL Server. This involves creating the tables, defining their relationships, and populating
them with sample data to ensure everything works as expected.
Finally, we would validate the database design by performing various tests, such
as data integrity checks, querying the database to retrieve information, and simulating
real-world scenarios to ensure the system meets the original requirements.
Throughout this process, it's essential to iterate and refine the design based on
feedback and changing requirements. Additionally, documenting the database design and
providing user documentation and training are crucial for ensuring successful adoption
and usage.
In conclusion, by applying the database design method to real-world scenarios
like the one presented for TechMart, we can effectively translate requirements into a
well-structured and efficient database system that meets the organization's needs. This
hands-on approach not only reinforces theoretical concepts but also equips database
designers with practical skills essential for success in the field.
. Read the requirements, identify the entities (objects) involved, and name the
entities. For example, when the design involves departments and employees, you might
use the entity names DEPARTMENT and EMPLOYEE. When the design involves
customers and sales reps, you might use the entity names CUSTOMER and
SALES_REP.
Identify the unique identifiers for the entities you identified in Step 1. For
example, when one of the entities is ITEM, determine what information is required to
uniquely identify each individual item. In other words, what information does the
organization use to distinguish one item from another? For the ITEM entity, the unique
identifier for each item might be ITEM_ID; for a CUSTOMER entity, the unique
identifier might be CUST_ID. When no unique identifier is available from the data you
know about the entity, you need to create one. For example, you might use a unique
number to identify items when no item numbers exist.
Identifying attributes for entities is a crucial step in the database design process as
it forms the basis for defining the structure of tables and columns within the database.
Let's further explore this process by elaborating on the attributes for each entity in our
hypothetical scenario of TechMart's inventory and sales management system.
Attributes play a crucial role in defining the structure and content of entities
within a database. They provide a comprehensive overview of the information to be
stored and managed, enabling efficient data organization, retrieval, and manipulation.
Let's explore the significance of attributes in greater detail and consider their various
characteristics and implications within the database schema.
Attributes serve as the building blocks of entities, representing the specific
characteristics or properties of each entity. These properties encapsulate relevant
information about the entity, such as its identity, attributes, and relationships with other
entities. For example, in a database for a retail store, attributes of a "Product" entity might
include product ID, name, description, price, and quantity in stock.
One of the key considerations in attribute design is the selection of appropriate
data types and constraints to accurately represent the nature of the information being
stored. This ensures data consistency, integrity, and validity within the database. For
instance, numeric attributes may be assigned integer or decimal data types, while textual
attributes may use character or string data types. Additionally, constraints such as
uniqueness, nullability, and range validation may be applied to enforce data integrity and
business rules.
Certain attributes, such as identifiers (e.g., ProductID, SupplierID), play a pivotal
role in establishing relationships between entities. These identifiers serve as unique keys
that uniquely identify each instance of an entity within the database. By sharing
identifiers across multiple entities, relationships can be established, enabling efficient
data retrieval and ensuring referential integrity within the database.
Moreover, attributes may exhibit various characteristics, including primary keys,
foreign keys, composite keys, and derived attributes, each serving a specific purpose in
the database schema. Primary keys uniquely identify each record within an entity, while
foreign keys establish relationships between entities by referencing primary keys of
related entities. Composite keys comprise multiple attributes that collectively serve as the
primary key, while derived attributes are computed or derived from other attributes
within the entity.
In addition to their functional roles, attributes contribute to the comprehensibility
and usability of the database schema. Descriptive attribute names and clear
documentation enhance understanding and facilitate communication among stakeholders
involved in database design, development, and usage. Furthermore, attributes serve as the
basis for defining queries, reports, and analytical models, enabling users to extract
meaningful insights and make informed decisions based on the data stored in the
database.
In summary, attributes form the foundation of entities within a database,
providing a detailed representation of the information to be stored and managed. By
carefully designing and managing attributes, database designers can ensure data integrity,
facilitate relationships, and support efficient data retrieval and analysis, ultimately
contributing to the effectiveness and utility of the database in supporting organizational
objectives.
Additionally, attributes such as names, contact information, and addresses may
appear in multiple entities, Identify the functional dependencies that exist among the
attributes. Ask yourself the following question: If you know a unique value for an
attribute, do you also know the unique values for other attributes? For example, when
reflecting common data elements across different aspects of the business process. you
have the three attributes REP_ID, FIRST_NAME, and LAST_NAME and you know a
unique value for REP_ID, do you also know a unique value for FIRST_NAME and
LAST_NAME? If so, then FIRST_NAME and LAST_ NAME are functionally
dependent on REP_ID (REP_ID → FIRST_NAME, LAST_NAME).
Use the functional dependencies to identify the tables by placing each attribute
with the attribute or minimum combination of attributes on which it is functionally
dependent. The attribute(s) for an entity on which all other attributes are dependent is the
primary key of the table. The remaining attributes are the other columns in the table.
Once you have determined all the columns in the table, you can give the table an
appropriate name. Usually the name will be the same as the name you identified for the
entity in Step 1.
Identify any relationships between tables. In some cases, you might be able to
determine the relationships directly from the requirements. It might be clear, for example,
that one sales rep is related to many customers and that each customer is related to
exactly one sales rep. When it is not, look for matching columns in the tables you created.
For example, if both the SALES_REP table and the CUSTOMER table contain a
REP_ID column and the values in these columns must match, you know that reps and
customers are related. The fact that the REP_ID column is the primary key in the
SALES_REP table tells you that the SALES_REP table is the one part of the relationship
and the CUSTOMER table is the many part of the relationship.
In the upcoming section, we will embark on a detailed exploration of the database
design process for KimTay Pet Supplies. By applying the systematic approach outlined
earlier, we will develop a robust database structure tailored to meet the specific
requirements of KimTay Pet Supplies' operations.
To initiate this process, we will first thoroughly examine the collection of
requirements provided for the KimTay Pet Supplies database. These requirements
encompass various aspects of the business, including inventory management, customer
information, sales transactions, and potentially more depending on the specific needs of
the company.
For instance, the database may need to store information about different pet
products, such as pet food, toys, grooming supplies, etc. Each product would likely have
attributes like ProductID, Name, Price, QuantityInStock, SupplierID, CategoryID, and
possibly additional details like Weight, Size, and Description.
Furthermore, the database would need to manage information about suppliers who
provide these pet products. Attributes for the Suppliers entity may include SupplierID,
Name, ContactPerson, Phone, Email, Address, City, and Country.
In addition to managing products and suppliers, the database would also need to
handle customer information for effective customer relationship management (CRM).
Attributes for the Customers entity may include CustomerID, FirstName, LastName,
Email, Phone, Address, City, and Country.
Moreover, the database would facilitate the recording of sales transactions,
tracking important details such as the products sold, the quantity purchased, the total cost,
and the payment method used. Attributes for the Sales entity may include SaleID,
CustomerID, SaleDate, TotalCost, and PaymentMethod.
Additionally, to support financial management and reporting, the database may
require the recording of transactions, including purchases, returns, and refunds. Attributes
for the Transactions entity may include TransactionID, Type, TransactionDate, and
Amount.
As we progress through the database design process, we will meticulously define
the relationships between these entities, ensuring data integrity and consistency
throughout the system. This involves establishing primary and foreign key constraints,
enforcing referential integrity, and optimizing the database schema for efficient data
retrieval and manipulation.
Furthermore, we will consider potential scalability and performance
considerations, anticipating future growth and evolving business requirements. This may
involve implementing indexing strategies, partitioning data, and optimizing queries to
ensure optimal performance under varying workloads.
In summary, the forthcoming section will delve into the intricacies of designing
the KimTay Pet Supplies database, leveraging a comprehensive understanding of the
business requirements and applying proven database design principles to develop a robust
and scalable solution tailored to the company's needs.
When designing a database to store information about sales representatives, it's
essential to consider not only the basic attributes provided but also additional factors that
might enhance data management, analysis, and reporting capabilities. By expanding the
database schema to encompass these additional attributes and functionalities, we can
create a comprehensive platform for managing sales representatives' information,
tracking performance, and driving organizational success. This enriched dataset not only
supports day-to-day operations but also enables strategic planning, resource allocation,
and performance optimization initiatives.
For a customer, store the customer’s ID, first name, last name, street address, city,
state, postal code, e-mail address, balance, and credit limit. In addition, store the ID, first
name, and last name of the sales rep who represents this customer. The analyst has also
determined that a sales rep can represent many customers, but a customer must have
exactly one sales rep (in other words, a sales rep must represent a customer; a customer
cannot be represented by zero or more than one sales reps).
When designing a database to store information about items, it's crucial to
consider not only the basic attributes provided but also additional factors that might
enhance data management, analysis, and operational efficiency. By expanding the
database schema to encompass these additional attributes and functionalities, we can
create a comprehensive platform for managing items, optimizing inventory operations,
and driving business growth. This enriched dataset not only supports day-to-day
inventory management but also enables strategic decision-making, cost optimization, and
customer satisfaction initiatives.
When it comes to managing invoices, ensuring accurate and comprehensive
record-keeping is essential for businesses to maintain smooth operations and uphold good
customer relationships. Let's delve deeper into how we can structure the information for
an invoice, covering various aspects such as the invoice number, date, customer details,
and sales representative information.
Every invoice should have a unique identifier, commonly referred to as the
invoice number. This number helps in tracking payments, referencing orders, and
resolving any discrepancies. Alongside the invoice number, the date of issuance is crucial
for maintaining a chronological record of transactions, aiding in financial reporting, and
adhering to regulatory requirements.
e. Normalization
Database design is a crucial aspect of any information system, as it lays the
foundation for storing and organizing data efficiently. Once the initial design is created,
it's imperative to analyze it thoroughly to ensure it is free from potential problems that
could hinder performance or data integrity. One of the primary techniques used for this
analysis is normalization.
Normalization is a process that involves identifying and addressing issues such as
data duplication, redundancy, and inconsistency within a database schema. By adhering
to specific normalization principles, database designers can optimize the structure of the
database to minimize storage space, improve data integrity, and simplify data
management.
The process of normalization typically involves several steps, often referred to as
normal forms. The most commonly used normal forms are First Normal Form (1NF),
Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form
(BCNF), among others. Each normal form has its own set of rules and criteria that must
be met to achieve a higher level of normalization.
In the first normal form (1NF), the database schema is organized in such a way
that it eliminates repeating groups and ensures that each column contains atomic values.
This helps to reduce data redundancy and allows for more efficient querying and
manipulation of data.
Second normal form (2NF) builds upon the first normal form by eliminating
partial dependencies within the database schema. This means that each non-key attribute
is fully functionally dependent on the entire primary key, further reducing redundancy
and improving data consistency.
Third normal form (3NF) takes normalization a step further by eliminating
transitive dependencies within the database schema. This ensures that each non-key
attribute is dependent only on the primary key and not on other non-key attributes,
thereby promoting data integrity and reducing the risk of anomalies.
Boyce-Codd Normal Form (BCNF) is a stricter form of normalization that
eliminates all non-trivial functional dependencies within the database schema. This
means that every determinant in the database is a candidate key, ensuring that there are
no redundancies or anomalies in the data.
In addition to these normal forms, there are other advanced techniques and
principles, such as denormalization and vertical partitioning, that can be used to further
optimize database design and performance in certain scenarios.
Overall, normalization is a critical process in database design that helps to
identify and eliminate potential problems that could compromise the integrity and
efficiency of the database. By following normalization principles and best practices,
database designers can create robust and efficient database schemas that meet the needs
of their users and applications.
The goal of normalization is to convert unnormalized relations (tables that satisfy
the definition of a relation except that they might contain repeating groups) into various
types of normal forms. A table in a particular normal form possesses a certain desirable
collection of properties. Although there are several normal forms, the most common are
first normal form, second normal form, and third normal form. Normalization is a process
in which a table that is in first normal form is better than a table that is not in first normal
form, a table that is in second normal form is better than one that is in first normal form,
and so on. The goal of this process is to allow you to take a table or collection of tables
and produce a new collection of tables that represents the same information but is free of
problems.
According to the definition of a relation, a relation (table) cannot contain a
repeating group in which multiple entries exist on a single row. However, in the database
design process, you might create a table that has all the other properties of a relation but
contains a repeating group. Removing repeating groups is the starting point when
converting an unnormalized collection of data into a table that is in first normal form. A
table (relation) is in first normal form (1NF) when it does not contain a repeating group.
For example, in the design process you might create the following INVOICES
table, in which there is a repeating group consisting of ITEM_ID and QUANTITY. This
notation describes a table named INVOICES that consists of a primary key,
INVOICE_NUM, and a column named INVOICE_DATE. The inner parentheses indicate
a repeating group that contains two columns, ITEM_ID and QUANTITY. This table
contains one row per invoice with values in the ITEM_ID and QUANTITY columns for
each invoice with the number INVOICE_NUM and placed on INVOICE_DATE.
When you convert an unnormalized table to a table in first normal form, the
primary key of the table in first normal form is usually the primary key of the
unnormalized table concatenated with the key for the repeating group. The key for the
repeating group is the column in the repeating group that distinguishes one occurrence of
the repeating group from another. In the INVOICES table, ITEM_ID was the key to the
repeating group and INVOICE_NUM was the primary key for the table. When
converting the unnormalized data to first normal form, the primary key becomes the
concatenation of the INVOICE_NUM and ITEM_ID columns.
Although the INVOICES table is in first normal form (because it contains no
repeating groups), problems exist within the table that require you to restructure it. The
description of a specific item, KH81 for example, occurs twice in the table. This
duplication (formally called redundancy) causes several problems. It is certainly wasteful
of space, but that is not nearly as serious as some of the other problems.
Updates: If you need to change to the description of item KH81, you must change
it twice—once in each row on which item KH81 appears. Updating the item description
more than once makes the update process much more cumbersome and time consuming.
Inconsistent data: There is nothing about the design that prohibits item KH81
from having two different descriptions in the database. In fact, if item KH81 occurs on 20
rows in the table, it is possible for this item to have 20 different descriptions in the
database.
Additions: When you try to add a new item and its description to the database,
you will face a real problem. Because the primary key for the INVOICES table consists
of both INVOICE_NUM and ITEM_ID, you need values for both of these columns to
add a new row to the table. If you add an item to the table that does not yet have any
invoices, what do you use for INVOICE_NUM? The only solution is to create a dummy
INVOICE_NUM and then replace it with a real INVOICE_NUM once an order for this
item is actually received. Certainly, this is not an acceptable solution.
Deletions: If you delete invoice 14216 from the database and it is the only invoice
that contains item CA75, deleting the invoice also deletes all information about item
CA75. For example, you would no longer know that item CA75 is an Enclosed Cat Litter
Station.
These problems occur because the DESCRIPTION column is dependent on only a
portion of the primary key (ITEM_ID) and not on the complete primary key. This
situation leads to the definition of second normal form. Second normal form represents an
improvement over first normal form because it eliminates update anomalies in these
situations. A table (relation) is in second normal form (2NF) when it is in first normal
form and no nonkey column (that is, a column that is not part of the primary key) is
dependent on only a portion of the primary key.
You can identify the fundamental problem with the INVOICES table: It is not in
second normal form. Although it is important to identify the problem, what you really
need is a method to correct it; you want to be able to convert tables to second normal
form. First, take each subset of the set of columns that make up the primary key and
begin a new table with this subset as its primary key.
Converting the original INVOICES table to a new INVOICES table, an ITEM
table, and an INVOICE_LINE table eliminates the update anomalies. A description
appears only once for each item, so you do not have the redundancy that existed in the
original table design. Changing the description of item KH81 from Wild Bird Food (25
lb) to KimTay Premium Wild Bird Food (25 lb), for example, is now a simple process
involving a single change. Because the description for an item occurs in a single place, it
is not possible to have multiple descriptions for a single item in the database at the same
time.
To add a new item and its description, you create a new row in the ITEM table,
regardless of whether that item has pending or actual invoices. In addition, deleting
invoice 14216 does not delete item number CA75 from the database because it still exists
in the ITEM table. Finally, you have not lost any information by converting the
INVOICES table to second normal form. You can reconstruct the data in the original
table from the data in the new tables.
CUST_ID determines all the other columns. In addition, REP_ID determines
REP_FIRST_NAME and REP_LAST_NAME. When a table’s primary key is a single
column, the table is automatically in second normal form. (If the table were not in second
normal form, some column would be dependent on only a portion of the primary key,
which is impossible when the primary key is just one column.) Thus, the CUSTOMER
table is in second normal form. Although this table is in second normal form, shows that
it still possesses update problems similar to those identified for the INVOICES table. The
sales rep name occurs many times in the table. The redundancy of including a sales rep
ID and full name in the CUSTOMER table results in the same set of problems that
existed for the INVOICES table.
These update anomalies are because the REP_ID determines REP_FIRST_NAME
and REP_LAST_NAME, but REP_ID is not the primary key. As a result, the same
REP_ID and consequently the same REP_FIRST_NAME and REP_LAST_NAME can
appear on many different rows.
You have seen that tables in second normal form represent an improvement over
tables in first normal form, but to eliminate problems with tables in second normal form,
you need an even better strategy for creating tables. Third normal form provides that
strategy. Before looking at third normal form, however, you need to become familiar with
the special name that is given to any column that determines another column (like
REP_ID in the CUSTOMER table). Any column (or collection of columns) that
determines another column is called a determinant. A table’s primary key is a
determinant. In fact, by definition, any candidate key is a determinant. (Remember that a
candidate key is a column or collection of columns that could function as the primary
key.)
Now you have identified the problem with the CUSTOMER table: It is not in
third normal form. There are several steps for converting tables to third normal form.
First, for each determinant that is not a candidate key, remove from the table the columns
that depend on this determinant (but do not remove the determinant). Next, create a new
table containing all the columns from the original table that depend on this determinant.
Finally, make the determinant the primary key of this new table.
Previously the first and last names for the sales rep were named
REP_FIRST_NAME and REP_LAST_NAME, respectively. It was not possible to use
the identifiers FIRST_ NAME and LAST_NAME because in the CUSTOMER table
FIRST_NAME and LAST_NAME were being used to identify the first and last names of
the customer. Because the fields REP_FIRST_NAME and REP_LAST_NAME are now
being removed from the CUSTOMER table and placed into the SALES_REP table, it is
now possible to use the identifiers of FIRST_NAME and LAST_NAME to correspond to
the first and last names for a sales rep.
Has this new design for the CUSTOMER table corrected all of the previously
identified problems? A sales rep’s name appears only once, thus avoiding redundancy
and simplifying the process of storing a sales rep’s first and last name. This design
prohibits a sales rep from having different names in the database. To add a new sales rep
to the database, you add a row to the SALES_REP table; it is not necessary for a new rep
to represent a customer. Finally, deleting all customers of a given sales rep will not
remove the sales rep’s record from the SALES_REP table, retaining the sales rep’s first
and last name in the database. You can reconstruct all the data in the original table from
the data in the new collection of tables. All previously mentioned problems have indeed
been solved.
f. Diagrams for Database Design
The use of entity-relationship (E-R) diagrams in representing the structure of a
database is indeed widely appreciated for its clarity and simplicity. These diagrams
provide a visual representation of the various entities within a database and the
relationships between them, making it easier for stakeholders to understand the data
model.
In an E-R diagram, each entity is typically depicted as a rectangle, with the name
of the entity written inside. For example, in a database for a university, entities could
include "Student," "Course," "Professor," and "Department." These rectangles not only
represent the entities themselves but also encapsulate the attributes associated with each
entity. Attributes are the characteristics or properties of an entity, such as "student ID"
and "name" for the "Student" entity.
One of the key aspects of an E-R diagram is the representation of relationships
between entities. These relationships define how entities are connected to each other and
can be classified into different types, such as one-to-one, one-to-many, and many-to-
many relationships.
For instance, in a university database, there could be a one-to-many relationship
between the "Department" and "Professor" entities, indicating that one department can
have many professors, but each professor belongs to only one department. This
relationship is depicted in the E-R diagram by drawing a line between the "Department"
and "Professor" rectangles, with a crow's foot symbol on the "Department" side to
indicate the "one" side of the relationship and a single line on the "Professor" side to
indicate the "many" side.
Similarly, there could be a many-to-many relationship between the "Student" and
"Course" entities, signifying that each student can enroll in multiple courses, and each
course can have multiple students enrolled. In an E-R diagram, this relationship is
represented by drawing a line between the "Student" and "Course" rectangles, with crow's
foot symbols on both sides to indicate the "many-to-many" nature of the relationship.
In addition to entities and relationships, E-R diagrams may also include other
components such as primary keys, foreign keys, and cardinality constraints, which further
enhance their expressiveness and usefulness in database design and communication.
Overall, E-R diagrams serve as invaluable tools for database designers,
developers, and stakeholders alike, providing a clear and intuitive visualization of the
database structure and facilitating effective communication and collaboration throughout
the database development process.
Several different styles of E-R diagrams are used to diagram a database design. In
the version, an arrowhead indicates the many side of the relationship between tables. In
the relationship between the SALES_REP and CUSTOMER tables, for example, the
arrow points from the SALES_REP table to the CUSTOMER table, indicating that one
sales rep is related to many customers. The presence of one-to-many relationships within the
INVOICE_LINE table in a database schema implies a significant aspect of the underlying data
model. Let's delve deeper into the implications and considerations associated with these
relationships.
The first one-to-many relationship, indicated by the line from the INVOICES
table to the INVOICE_LINE table, suggests that each invoice can have multiple invoice
lines associated with it. This relationship is fundamental in representing the items or
services that are being billed within each invoice. It underscores the fact that an invoice is
not just a single entity but rather a collection of individual items or services, each
represented by a distinct line in the invoice.
Furthermore, the second one-to-many relationship, depicted by the line from the
ITEM table to the INVOICE_LINE table, highlights the connection between items (or
products) and their occurrences within different invoices. This relationship signifies that
each item in the database can appear on multiple invoice lines across various invoices. It
reflects the notion that items are independent entities that can be sold or included in
multiple transactions, each represented by an invoice line.
These one-to-many relationships between the INVOICE_LINE table and both the
INVOICES and ITEM tables carry several implications for database design and
management. Firstly, they enable efficient tracking and management of invoice-related
information, such as itemized billing details and invoice totals. By associating invoice
lines with specific invoices and items, the database can accurately capture and represent
the details of each transaction.
Moreover, these relationships facilitate data integrity and consistency within the
database. By establishing referential integrity constraints, such as foreign key constraints,
between the tables involved in these relationships, the database can ensure that only valid
and existing invoices and items are referenced within the invoice lines. This helps prevent
data anomalies and inconsistencies, ensuring the reliability and accuracy of the data
stored in the database.
Additionally, these relationships enable powerful querying and reporting
capabilities, allowing users to retrieve and analyze invoice-related information in various
ways. For example, users can easily generate reports that summarize sales by item or
analyze the distribution of items across different invoices. Such analyses can provide
valuable insights into sales trends, inventory management, and customer preferences,
facilitating informed decision-making within the organization.
In summary, the presence of one-to-many relationships within the
INVOICE_LINE table underscores the interconnected nature of invoice data and item
data within the database. These relationships play a crucial role in structuring and
managing invoice-related information, ensuring data integrity, and enabling insightful
analysis and reporting capabilities.
The use of descriptive relationship names, represented by diamonds in entity-
relationship diagrams, adds a layer of clarity and understanding to the database schema.
These names not only serve as labels but also convey meaningful information about the
nature of the relationship between entities. Let's explore further how these relationship
names contribute to the comprehension and usability of the database design.
In the context of the SALES_REP and CUSTOMER tables, the relationship
named REPRESENTS encapsulates the essence of the connection between a sales
representative and a customer. By naming the relationship in this manner, it immediately
communicates that a sales representative acts as a representative or intermediary for a
customer. This could imply that the sales representative is responsible for managing the
relationship with the customer, handling sales transactions, providing support, and
addressing customer needs. As a result, users and stakeholders can quickly grasp the role
and significance of this relationship within the broader context of the database schema.
Similarly, the relationship named PLACED between the CUSTOMER and
INVOICES tables conveys the action or event of a customer placing an invoice. This
reflects the sequence of events in a typical business transaction, where a customer
initiates the creation of an invoice by making a purchase or requesting a service. By
naming the relationship in this manner, it clarifies the flow of data and operations within
the database, highlighting the interaction between customers and invoices. This
understanding can be particularly valuable for users who need to track and analyze
customer transactions, monitor billing activities, or assess the financial performance of
the business.
Moreover, descriptive relationship names not only aid in understanding individual
relationships but also contribute to the overall coherence and readability of the database
schema. By providing meaningful labels for relationships, database designers can create a
schema that is intuitive and accessible to users and stakeholders from diverse
backgrounds. This facilitates communication, collaboration, and knowledge sharing
among team members involved in database design, development, and usage.
Furthermore, descriptive relationship names can serve as documentation for the
database schema, helping future developers and analysts understand the rationale behind
the design choices and the semantics of the relationships. This can be particularly
valuable in scenarios where databases undergo updates, enhancements, or migrations, as
it provides valuable context and guidance for making informed decisions and
modifications.
In summary, the use of descriptive relationship names in entity-relationship
diagrams enhances the clarity, coherence, and usability of the database schema. By
succinctly capturing the essence of the relationships between entities, these names
empower users and stakeholders to better comprehend, navigate, and utilize the database
for various purposes, ultimately contributing to improved efficiency, effectiveness, and
reliability in database management and operations.
The relationship name "CONTAINS" between the INVOICES and
INVOICE_LINE tables encapsulates a fundamental aspect of the database schema,
highlighting the composition of invoices in terms of their constituent invoice lines.
Expanding upon this relationship and its implications can provide valuable insights into
the structure and functionality of the database.
At its core, the "CONTAINS" relationship signifies that an invoice comprises
multiple invoice lines, each representing a specific item or service included in the
invoice. This relationship reflects the hierarchical nature of invoice data, where the
overall invoice serves as an aggregation of individual line items, detailing the quantity,
price, and other relevant information for each item.
By naming the relationship "CONTAINS," database designers effectively
communicate the essence of the association between invoices and invoice lines. This
descriptive label intuitively conveys that the primary purpose of an invoice is to contain
or encompass various line items, reflecting the items purchased, services rendered, or
charges incurred by a customer during a transaction.
Moreover, the "CONTAINS" relationship underscores the importance of invoice
lines as integral components of the invoicing process. Each invoice line represents a
distinct entry in the invoice, providing itemized details that enable accurate billing,
tracking, and reconciliation of transactions. This granular level of information not only
enhances transparency and accountability but also facilitates thorough analysis and
auditing of financial records.
Furthermore, the "CONTAINS" relationship serves practical purposes in database
management and querying. By establishing this relationship, database administrators can
enforce referential integrity constraints to ensure that every invoice line is associated with
a valid invoice. This helps maintain data consistency and accuracy, preventing orphaned
or dangling records that could compromise the integrity of the database.
Additionally, the "CONTAINS" relationship enables efficient retrieval and
manipulation of invoice data through structured queries and reporting mechanisms.
Database users can leverage this relationship to generate comprehensive reports
summarizing invoice contents, analyzing sales trends, and evaluating customer
purchasing patterns. Such insights are invaluable for decision-making, resource
allocation, and strategic planning within organizations.
In summary, the "CONTAINS" relationship between the INVOICES and
INVOICE_LINE tables embodies the essence of the invoicing process, emphasizing the
hierarchical relationship between invoices and their constituent line items. By naming
this relationship descriptively, database designers provide clarity and context, facilitating
understanding, usage, and management of the database schema. This fosters effective
communication and collaboration among stakeholders and empowers users to leverage
the database effectively for business operations and analysis.
The relationship name "IS_ON" between the ITEM and INVOICE_LINE tables is
a succinct representation of the connection between items and their occurrences on
invoices. By naming the relationship in this manner, the database schema communicates
that a given item can appear on multiple invoices, reflecting its usage or sale in various
transactions. Let's delve deeper into the implications and significance of this relationship
within the context of the database design.
At its core, the "IS_ON" relationship signifies a many-to-many association
between items and invoices. It acknowledges the fact that items are not tied to a single
invoice but rather can be included on multiple invoices over time. This recognition of the
versatility and flexibility of items in the invoicing process is essential for accurately
modeling real-world business scenarios where items are bought, sold, or consumed in
diverse transactions.
By representing the relationship as "IS_ON," the database schema conveys a clear
and intuitive understanding of the role of items within the invoicing context. It
emphasizes that items serve as the focal point of transactions, being listed on invoices to
indicate the products or services exchanged between parties. This understanding is
instrumental in designing an effective database structure that accurately captures and
reflects the dynamics of business operations.
Furthermore, the use of the "IS_ON" relationship facilitates efficient querying and
retrieval of data related to items and their invoicing history. Database users can leverage
this relationship to analyze the sales performance of individual items, track inventory
movement across invoices, and identify patterns or trends in item usage over time. This
insight is invaluable for inventory management, sales forecasting, and strategic decision-
making within organizations.
Moreover, the representation of the "IS_ON" relationship using the notation
where the number "1" indicates the one side and the letter "n" represents the many side
underscores the cardinality of the relationship. It clarifies that each item can be associated
with multiple invoice lines (the "many" side), while each invoice line corresponds to a
single item (the "one" side). This distinction aids in understanding the nature of the
relationship and its impact on database design and querying.
In summary, the "IS_ON" relationship between the ITEM and INVOICE_LINE
tables is a vital component of the database schema, capturing the linkage between items
and their occurrences on invoices. By naming the relationship descriptively and
representing its cardinality, the database design fosters clarity, comprehension, and
usability, empowering users to effectively manage and analyze item-related data within
the context of invoicing operations.
