Weekly activity_02
Subject: Database Management System
Data Models
Question No: 01
What is a business rule, and what is its purpose in data modeling?
A business rule is a statement that defines or limits some aspect of a business. It is used to guide
the behavior or structure of an organization. In data modeling, business rules are used to define
the requirements and constraints that the data model must meet. These rules help ensure that the
data model accurately represents business processes and necessities.
The purpose of incorporating business rules into data modeling is to:
1. Ensuring data integrity: By defining business rules, data modeling helps ensure that the data
stored in the database is accurate, consistent and reliable.
2. Improving data quality: Business rules help maintain high quality data by enforcing data
constraints and validation.
3. Facilitate Decision Making: By capturing business rules in the data model, organizations can
make informed decisions based on defined constraints and requirements.
4. Improving system interoperability: Business rules help standardize the data model and
facilitate integration with other systems or applications.
In summary, business rules play a key role in data modeling by guiding the design and
implementation of a data model that correctly represents business requirements and constraints.
Question No: 02
What are a relationship, and what three types of relationships exist?
In data modeling, a relationship defines how two or more entities are linked or related to each
other. Relationships are key in representing interactions and dependencies between different
entities in a database. There are three main types of relationships in data modeling:
1. One-to-one relationship (1:1):
In a one-to-one relationship, each record in one entity relates to exactly one record in another
entity, and vice versa. This type of relationship is relatively rare in database design, but can be
useful when there is a strict one-to-one communication between entities.
2. One-to-Many (1: N) Relationship:
In a one-to-many relationship, each record in one entity can be related to numerous records in
another entity, but each record in the second entity is related to only one record in the first entity.
This is the most common type of relationship in data modeling and is used to represent
hierarchical structures.
3. Many-to-Many Relationship (N: M):
In a many-to-many relationship, numerous records in one entity can be associated with multiple
records in another entity. This type of relationship requires the use of a join table or associative
entity to resolve the many-to-many relationship into two one-to-many relationships. Many-to-
many relationships are commonly used to represent complex relationships between entities.
Understanding and properly defining these relationships is essential to designing an effective
data model that accurately represents the real-world interactions and needs between the various
entities in the database.
Question No: 03
Give an example of each of the three types of relationships.
Here are examples of each of the three types of relationships in the context of a simplified data
model for a library:
1. One-to-One relationship (1:1):
Example: Library member and library card each library member is associated with exactly
one library card, and each library card is assigned to only one library member. This
represents a one-to-one relationship because each member has a unique card and each card is
associated with a precise associate.
2. One-to-Many Relationship (1: N):
Example: Author and Books an author can write multiple books, but each book is authored by
only one author. This represents a one-to-many relationship because one author can be associated
with multiple books, but each book is associated with only one author.
3. Many-to-Many (N: M) Relationship:
Example: Books and Genres A book can belong to multiple genres and a genre can be associated
with multiple books. This represents a many-to-many relationship because each book can have
multiple genres and each genre can be associated with multiple books. To represent this
relationship in a database would require a join table linking books and genres.
These examples illustrate how different types of relationships can be applied to real-world
scenarios and demonstrate the unlike ways entities can be linked in a data model.
Question No: 04
Consider the scenario described by the statement: “A customer can make many payments,
but each payment is made by only one customer”. Model this statement using a crow’s foot
modelling technique.
In the crow's foot notation, the "one" side of the relationship is represented by a straight line, and
the "many" side is represented by a crow's foot (three lines). Based on the scenario described,
where a customer can make many payments but each payment is made by only one customer, we
can model this relationship as follows:
Entity 1: Customer
Entity 2: Payment
Relationship:
One Customer can make many Payments (one-to-many relationship from Customer to
Payment)
Each Payment is made by only one Customer (one-to-one relationship from Payment to
Customer)
Using the crow's foot notation, the relationship between Customer and Payment can be
represented as:
Custome
r
-----|<---- Payment
In this notation:
The crow's foot side (|<) represents "many" (one Customer can make many Payments).
The straight line side (-----) represents "one" (each Payment is made by only one
Customer).
This notation visually represents the one-to-many relationship from Customer to Payment and
the one-to-one relationship from Payment to Customer as described in the scenario.
Question No: 05
Consider the below figure 1 as your guide, complete following exercises. The Tiny college
relational diagram shows the initial entities and attributes for Tiny College.
Identify each relationship type and write all the business rules.
Using [Link] (or another application such as lucid chart check with your lecturer/tutor)
creates a basic Crow’s Foot ERD for Tiny College.
Relationship between the class and the course:
A one-to-many relationship is indicated by the "1" next to COURSE and
the crow's feet next to CLASS.
Business rules:
Each course can have multiple classes.
Each class is associated with only one course.
The Relationship between ENROLL and CLASS:
The one-to-many nature of this relationship is shown by the crow's feet next to
ENROLL and the "1" next to CLASS.
Business rules:
Each class can have multiple enrollments.
Each enrollment record is associated with only one class.
Relationship between enrollee and student:
Here, a one-to-many link is shown by the "1" next to STUDENT and the crow's
feet next to ENROLL.
Business rules:
Each student can be enrolled in multiple classes.
Each enrollment is related with only one student.
Question No: 06
Describe the relationship and identify the business rules depicted in the ERD diagram show
in figure 2.
Rules for relationships and business:
The ERD (Entity-Relationship Diagram) shown in Figure 2 depicts the relationships between
three entities: PROFESSOR, STUDENT, and CLASS. The diagram shows:
1. PROFESSOR to STUDENT: The line connecting PROFESSOR and STUDENT has a
notation that indicates a one-to-many relationship, labeled "advise". This means that one
professor can advise many students, but only one professor advises each student. So the business
rule here is:
Each student has one and only one advisor.
A professor can advise none, one or more students.
2. PROFESSOR to CLASS: The connection between PROFESSOR and CLASS depicts a
many-to-many relationship, as indicated by the crow's feet at both ends, labeled "teach". A
professor can teach multiple classes, and a class can be taught by multiple professors (perhaps in
different lessons or for team teaching scenarios). The business rules are here:
A professor can teach zero, one, or many classes.
A class may be taught by one or more professors.
3. STUDENT to CLASS: There is no straight line connecting STUDENT to CLASS in this
diagram, indicating that student enrollment in classes is not directly represented in this ERD.
Sometimes the enrollment relationship is represented through an associative entity (not shown
here) that would manage the many-to-many relationship that typically exists between students
and the classes they enroll in.
This ERD provides a high-level view of the relationships within an educational institution
between professors, students, and classes
Question No: 07
Create a Crow’s foot ERD for following descriptions
a. An airliner can be assigned to fly many flights, but each flight is flown by only on
airliner.
b. Each of the Mega Corporation’s divisions is composed of many departments. Each
of those departments has many employee assigned to it, but each employee works
for only one department. Each department is managed by one employee, and each
of those managers can manage only one department at a time
Let’s create a Crow's foot ERD for the scenario involving an airliner, flights, Mega Corporation's
divisions, departments, and employees. Here is the ERD:
Airliner (AirlinerID, Airliner Name)
Flight (Flight ID, Flight Number, Destination, AirlinerID)
Division (Division ID, Division Name)
Department (Department ID, Department Name, Division ID, Manager ID)
Employee (Employee ID, Employee Name, Department ID)
Relationships:
1. Airliner to Flight: One-to-many relationship where an airliner can be assigned to many
flights, but each flight is flown by only one airliner.
2. Division to Department: One-to-many relationship where each division can have many
departments.
3. Department to Employee: One-to-many relationship where each department has many
employees assigned to it, but each employee works for only one department.
4. Department to Manager: One-to-one relationship where each department is managed by
one employee (Manager ID in Department).
5. Manager to Department: One-to-one relationship where each manager can manage only
one department at a time.
Flight
Flight-ID
Flight-Number
Destination Manager
Airliner
Airliner-ID Manager-ID
Airliner-ID
Manager-Name
Airliner-Name Division
Division-ID
Division-Name
Department-ID
Department-Name
Division-ID
Manager-ID
Employee
Employee-ID
Employee-Name
Question No: 08
Create a Crow’s Foot ERD to include the following business rules for fro ProdCo Company:
Each sales representative writes many invoices.
Each invoice is written by one sale representative.
Each sales representative is assigned to one department.
Each department has many sales representatives.
Each customer can generate many invoices.
Each invoice is generated by many customers
I will create a Crow's Foot ERD that includes entities for Sales Representative, Invoice,
Department, Customer, and the relationships between them. Here is the ERD:
Sales Representative (Rep-ID, Rep-Name, Department-ID)
Invoice (Invoice-ID, Invoice-Number, Rep-ID)
Department (Department-ID, Department-Name)
Customer (Customer-ID, Customer-Name)
Relationships:
1. Sales Representative to Invoice: One-to-many relationship where each sales
representative writes many invoices and each invoice is written by one sales
representative.
2. Sales Representative to Department: One-to-one relationship where each sales
representative is allocated to one department.
3. Department to Sales Representative: One-to-many relationship where each department
has many sales representatives.
4. Customer to Invoice: One-to-many relationship where each customer can produce
many invoices and each invoice is produced by many customers.
Invoice
Sales
represen Department
tative
Customer