Lab 2

⮚ What is a Relationship?
● Relationships indicate a meaningful connection between two entity types.
● Relationships may have attributes, but they cannot have key attributes.
● Verbs indicate names of relationship types

Relationship Degree

1. Unary Relationship
● A unary relationship, also called recursive, is one in which a relationship exists
between occurrences of the same entity set.

2. Binary Relationship
● A Binary Relationship is the relationship between two different Entities
3. Ternary Relationship
● A ternary relationship is an association among three entities. This type of
relationship is required when binary relationships are not sufficient to accurately
describe the semantics of the association.

Relationship Cardinality

1. One-to-One
● Each entity in the relationship will have exactly one related entity

President Governs Country

2. One-to-Many
● An entity on one side of the relationship can have many related entities, but an
entity on the other side will have a maximum of one related entity
 EMPLOYEE DEPARTMENT

3. Many-to-Many
● Entities on both sides of the relationship can have many related entities on the
other side

EMPLOYEE PROJECT

Cardinality Constraint

⮚ What is a Participation Constraints?

● In a Relationship, Participation constraint specifies the presence of an entity
when it is related to another entity in a relationship type. It is also called
the minimum cardinality constraint.
Participation Constraints Types

1. Total Participation Constraint

● It specifies that each entity present in the entity set must mandatorily participate
in at least one relationship instance of that relationship set,for this reason, it is
also called as mandatory participation
● It is represented using a double line between the entity set and relationship set

Example of total participation constraint

● It specifies that each student must be enrolled in at least one course where
the “student” is the entity set and relationship “enrolled in” signifies total
participation
● It means that every student must have enrolled at least in one course

2. Partial Participation Constraint

● It specifies that each entity in the entity set may or may not participate in the
relationship instance of the relationship set, is also called as optional participation
● It is represented using a single line between the entity set and relationship set in
the ER diagram
 Example of partial participation constraint
● A single line between the entities i.e courses and enrolled in a relationship
signifies the partial participation, which means there might be some courses
where enrollments are not made i.e enrollments are optional in that case

ER-Diagram Standards
University Academic Registration System
1- A university intends to develop a database system to manage its academic structure and student
registration process. The system should support the organization of departments, professors,
students, courses, and course offerings each semester.

2- The university is composed of several departments. Each department has its own unique
identifier, name, office location, and contact number. A department employs many professors and
admits many students. Every professor and every student must belong to exactly one department,
while each department may have many professors and many students associated with it, Student
may be in general department in first and second level In addition, each department offers a
number of academic courses, and every course is offered by one and only one department.

3- Each professor working at the university is identified by a unique ID and has a recorded full
name, hiring date, salary, unique email address, and office location. The office location consists
of a building name and room number. A professor may have more than one telephone number for
contact purposes. Some professors may act as academic advisors. Every student is assigned
exactly one advisor to guide them in planning their academic path; however, not all professors are
required to advise students.

4- Students admitted to the university are identified by a unique student ID. For each student, the
system stores the full name, date of birth, email address, academic level, and one or more phone
numbers. Although the system keeps the date of birth, it does not directly store the student’s age,
since it can be calculated when needed. Similarly, the cumulative GPA of a student is not stored
directly but is calculated based on the grades obtained in completed courses.

5- Each course offered by the university has a unique course code, title, description, and number
of credit hours. Some courses require the completion of other courses before enrollment is
allowed. A course may require several prerequisite courses, and a course may itself serve as a
prerequisite for multiple other courses. However, some courses may not require any prerequisites.

6- Courses are offered every semester in the form of sections. A section cannot exist
independently without being associated with a specific course. For each semester, a course may
have several sections, each identified by a section number within that course and semester. Every
section has a scheduled time, assigned classroom, and a maximum number of students allowed to
enroll. Each section must be taught by exactly one professor, although a professor may teach
multiple sections in a given semester or may not teach any sections at all.

7- At the beginning of each semester, students register for sections. A student may enroll in
several sections during the same semester, and each section may have many students enrolled in
it. When a student registers for a section, the system records the date of enrollment and, at the end
of the semester, the final grade achieved in that section can be ( A,B,C,D, F). The GPA of the
student is then calculated from the grades obtained across all completed sections.

8- The university requires that no section exceed its maximum capacity (D) and that students may
only enroll in courses for which they have satisfied all prerequisite requirements.
Answer of Use Case Study
 Order Management System

A retail company intends to develop a database system to manage its customers, employees,
suppliers, products, and sales orders across its different branches. The system must support
customer orders, product categorization, supplier management, and inventory tracking.

The company operates through several branches. Each branch has a unique branch identifier,
name, address, and contact number. The address consists of city, street, and building number.
Every branch employs multiple employees, and each employee must be assigned to exactly one
branch. Some employees are appointed as branch managers. Each branch must have exactly
one manager, while an employee manages at most one branch.

Each employee is identified by a unique employee ID and has a recorded full name, email, job
title, hiring date, salary, and one or more phone numbers. The full name consists of first name,
middle name, and last name. Employees may supervise other employees. A supervisor may
supervise several employees, while an employee may have at most one direct supervisor.

The company serves many customers. Each customer has a unique customer ID, company name,
contact person name, contact title, address, city, postal code, country, and one or more phone
numbers. Although the system stores the customer’s registration date, the number of years the
customer has dealt with the company is not stored directly, as it can be calculated when
needed. Some customers may not have placed any orders yet.

The company purchases products from suppliers. Each supplier has a unique supplier ID,
company name, contact name, contact title, address, city, country, and phone number. A
supplier may supply many products, but each product is supplied by exactly one supplier.

Products are organized into categories. Each category has a unique category ID, name, and
description. A category may contain many products, while each product belongs to exactly one
category.
Each product has a unique product ID, product name, quantity per unit, unit price, units in stock,
reorder level, and a discontinued status. Some products may be complementary to other
products; for example, a beverage product may be associated with a specific snack item. A
product may have several complementary products, and a product may serve as a complement
to multiple other products. However, some products may not have any complementary items.

When customers place orders, each order is assigned a unique order ID and is handled by
exactly one employee. An order is placed by exactly one customer and processed at one branch.
The system records the order date and shipping date. Each order may contain multiple products,
and each product may appear in many different orders. For every product included in an order,
the system records the quantity ordered, unit selling price at the time of the order, and discount
applied. The total amount of the order is not stored directly but can be calculated from the
ordered products, quantities, prices, and discounts
Answer of Student_UseCaseStudy: