3. METODOLOGÍA EBA-LITE
3.1 Evaluación Positiva
To classify the entities in an entity set into subclass entity is known as class hierarchies. Example, we might want to classify Employees entity set into subclass entities Hourly-Emps entity set
Notes and Contract-Emps entity set to distinguish the basis on which they are paid. Then the class hierarchy is illustrated as follows:
ename eno salary Employee hours - worked ISA contractid Contract - Emps Hourly _Emps hourly -wages
This class hierarchy illustrates the inheritance concept. Where, the subclass attributes ISA (read as : is a) super class attributes; indicating the “is a” relationship (inheritance concept).Therefore, the attributes defined for a Hourly-Emps entity set are the attributes of Hourly-Emps plus attributes of Employees (because subclass can have superclass properties). Likewise the attributes defined for a Contract-Emps entity set are the attributes of Contract-Emps plus attributes of Employees.
Class Hierarchy based on Sub-super Set
1. Specialization: Specialization is the process of identifying subsets (subclasses) of an entity
set (superclass) that share some special distinguishable characteristic. Here, the superclass (Employee) is defined first, then the subclasses (Hourly-Emps, Contract-Emps, etc.) are defined next.
In short, Employees is specialized into subclasses.
2. Generalization: Generalization is the process of identifying (defining) some generalized
(common) characteristics of a collection of (two or more) entity sets and creating a new entity set that contains (possesses) these common characteristics. Here, the subclasses (Hourly-Emps, Contract-Emps, etc.) are defined first, then the Superclass (Employee) is defined, next.
In shortly, Hourly-Emps and Contract-Emps are generalized by Employees. Class Hierarchy based on Constraints
1. Overlap constraints: Overlap constraints determine whether two subclasses are allowed
to contain the same entity.
Notes
Example: Can Akbar be both an Hourly-Emps entity and a Contract-Emps entity? The answer is, No.
Other example, can Akbar be both a Contract-Emps entity and a Senior-Emps entity (among them)?
The answer is, Yes. Thus, this is a specialisation hierarchy property. We denote this by writing “Contract-Emps OVERLAPS Senior-Emps”.
2. Covering Constraints: Covering constraints determine whether the entities in the
subclasses collectively include all entities in the superclass.
Example: Should every Employee be a Hourly-Emps or .Contract-Emps? The Answer is, No. He can be a Daily-Emps.
Other example, should every Motor-vehicle (superclass) be a Bike (subclass) or a Car (subclass)?
The Answer is YES.
Thus generalization hierarchies property is that every instance of a superclass is an instance of a subclass.
We denote this by writing “ Bikes and Cars COVER Motor-vehicles”.
6.11.2 Aggregation
Aggregation allows us to indicate that a relationship set (identified through a dashed box) participates in another relationship sets. That is, a relationship set in an association between entity sets. Sometimes we have to model a relationship between a collection of entities and relationships.
Example: Suppose that we have an entity set called Project and that each Project entity is sponsored by one or more departments. Thus, the sponsors relationship set captures this information but, a department that sponsors a project, might assign employees to monitor the sponsorship. Therefore, Monitors should be a relationship set that associates a sponsors relationship (rather than a Project or Department entity) with an Employees entity. However, again we have to define relationships to associate two or more entities.
Use of Aggregation
We use an aggregation, when we need to express a relationship among relationships. Thus, there are really two distinct relationships, Sponsors and Monitors, each with its own attributes.
Example: The Monitors relationship has an attribute until that records the ending date until when the employee is appointed as the sponsorship monitoring. Compare, this attribute with the attribute since of Sponsors, which is the starting date when the sponsorship took effect.
Task Specialisation and generalisation are two important concepts of EER. What is your opinion?
Notes To enable the readers for a better understanding of ER diagram we shall present few examples in this study. The reader should go through these examples carefully for a better and easier way of writing ER diagrams.
Case Study
Insurance Policy Management System
C
onsider a popular and common problem in the today’s world: Insurance Policy Management System. The Software Requirements Specifications (SRS) for this problem can be stated as follows:1. The Insurance Company has many branches with branchid, branch name, address or location, phone numbers, fax, etc.
2. In every branch there are different types of staff working. For example, there is a branch manager, field officers, development personnel, secretarial assistants, etc. It is necessary to keep track of staffed, staff name, address, position, salary, date of birth, etc.
3. Apart from the regular employees there are part-time staff called insurance agents who work on commission basis.
4. The Insurance Company must store the details of policy holders: policy holder’s name, policy number, address, tenure, maturity amount, premium amount, etc. It is mandatory to mention the nominees in every policy.
With our knowledge of ER diagram studied so far, the various entity sets and the attributes can be identified as follows:
Step-1 and Step-2: Entity Sets and Attributes
1. Branches BranchID, BranchName, Addr, Phone
2. Staff StaffID, StaffName, Addr
3. Customers PolicyNo, Name, Addr, Age, Phone
4. Policy (weak) PolicyName, Tenure, MatAmount, PremAmount, Bonus
Sub Entity Sets of Staff
1. Managers Exp, Qualification
2. DevOfficers Qualification
3. SectAssts TypingSpeed
4. FullTime Salary, Position
5. Agents Type, Comm
Policy is a weak entity, because without a customer or policy holder the insurance policy doesn’t exist. Also, the entity set Policy does not have primary key, but only partial key (PolicyName). The name of the policy may be as follows: Endowment, Moneyback, Medical, etc.
Figure depicts the ER Diagram for Insurance Policy management with all the constraints incorporated.
Notes Customers 1 M Policy IName Phone Addr PolicyNo Age PolicyName PremAmount MatName Tenure Bonus Hold Date Interact Staff StaffID StaffName Age Phone WorkFor Branches Addr BranchName Phone Branch
Managers SectAssts DevOfficers FullTime Agents M
1
M
1
Step-3: Relationship Sets
As per the requirements as stated already, the following relationship sets may be identified: 1. WorksFor Staff and Branches N: 1 Total on either side
2. Interacts Staff and Customers 1:N Total on either side 3. Hold Customers and Policy 1: N Total on either side 4. Staff (Managers, SectAssts, DevOfficers) IS-A Relationship
5. Staff (FullTime, Agents) IS-A Relationship
6.12 Summary
Relational algebra is a procedural language. It provides a collection of operations to manipulate relations. It supports the notion of a query which is a request to retrieve information from a database. The relational algebra data operations and their corresponding operators are:
Basic Operations:
Selection Select Operator ( )
Projection Project Operator ( )
Cross-product Cartesian product (×)
Union Operator ( )
Set Difference Difference Operator (–)
Notes Additional Operations:
Intersection Intersect Operator ( )
Join Join Operator ( )
Division Division operator ( )
Rename Rename operator ( )
Here, selection, projection, rename are unary operators and other operators are binary.
6.13 Keywords
Binary operations: Operations which operate on two relations.
ER model: The entity-relationship (ER) data model allows us to describe the data involved in
real-world enterprise in terms of objects (entities) and their relationships, and is widely used to develop an initial database design.
Relational algebra: The Relational Algebra which is an algebraic notation, where queries are
expressed by applying specialized operators to the relations.
Relational calculus: The Relational Calculus which is a logical notation, where queries are
expressed by formulating some logical restrictions that the tuples in the answer must satisfy.
Unary operation: Operations which operates on only one relation.
6.14 Self Assessment
Choose the appropriate answer:1. Relational calculus is an alternative to: (a) Relational algebra
(b) Relational valuation (c) Related query (d) Relational calculation 2. QBE stands for
(a) Quick-by-Example (b) Query-by-Example (c) Queue-by-Example (d) Query-by-Expansion Fill in the blanks:
3. A ... is a variable that takes on tuples of a particular relation schema as values.
4. A ...formula is defined in a manner that is very similar to the definition of a TRC formula.
5. The ER model is important for its role in ... 6. ... represent entity sets.
Notes 7. An entity set attributes that does not have a primary key within them, is termed as a
... set.
8. ...determine whether two subclasses are allowed to contain the same entity. 9. ... determine whether the entities in the subclasses collectively include all
entities in the superclass.
10. ... allows us to indicate that a relationship set participates in another relationship sets.