Improved E-R Model and Business Rules

Presentation Transcript

Upgraded E-R Model and Business Rules CS263 Lecture 4

Introduction The fundamental ER model was presented in the mid 1970s Since then business connections and business information have turned out to be more mind boggling The term Enhanced Entity Relationship (EER) display alludes to the expansion of the first ER show

Supertype/Subtype connections Most imperative new demonstrating develop in the EER demonstrate Allows us to show a general element sort (the supertype) and afterward subdivide it into a few specific element sorts (called subtypes) Each subtype acquires from its supertype and may have exceptional qualities of its own

Representing supertypes and subtypes The supertype is associated with a line to a hover, which thusly is associated by a line to each subtype that has been characterized (see Fig.) The "U" molded image on each line interfacing a subtype to the circle demonstrates that the subtype is a subset of the supertype, and furthermore shows the course of the relationship Attributes shared by every one of the substances are related with the supertype, while ascribes that are one of a kind to a specific subtype are related with that subtype

Basic documentation for supertype/subtype connections

A case: the EMPLOYEE supertype Suppose that an association has 3 essential sorts of workers: Hourly: Employee_Number, Employee_Name, Address, Date_Hired, Hourly_Rate Salaried: Employee_Number, Employee_Name, Address, Date_Hired, Annual_Salary, Stock_Option Contract advisors: Employee_Number, Employee_Name, Address, Date_Hired, Contract_Number, Billing_Rate

Employee supertype Many properties the same crosswise over 3 sorts, so we could characterize a supertype called EMPLOYEE, with subtypes for HOURLY_EMPLOYEE, SALARIED_EMPLOYEE and CONSULTANT (see Fig)

Employee supertype with three subtypes All representative subtypes will have emp_no., name, address, and date-procured Each worker subtype will likewise have its own traits

When to utilize supertype/subtype relations Should consider utilizing subtypes when either (or both) of the accompanying conditions are available: 1. There are credits that apply to a few (yet not all) of the cases of a substance sort 2. The occasions of a subtype take an interest in a relationship one of a kind to that subtype Both are valid in the accompanying Fig., where PATIENT has two subtypes: OUTPATIENT and RESIDENT PATIENT (the essential key is PATIENT_ID) All patients have an Admit_Date and a Patient_Name

Patient illustration Every patient is looked after by a RESPONSIBLE_PHYSICIAN who builds up a treatment anticipate the patient Each subtype likewise has exceptional traits. Outpatients have a Checkback_Date, while inhabitants have a Date_Discharged and an interesting relationship that appoints every patient to a bed (this is a compulsory relationship, and each bed could possibly be doled out to a patient)

Supertype/subtype connections of patients Both outpatients and occupant patients are administered to by a mindful doctor Only inhabitant patients are doled out to a bed

Generalization and specialization Generalization: = the way toward characterizing a more broad element sort from an arrangement of more particular substance sorts. Base UP Specialization = t he procedure of characterizing at least one subtypes of the supertype, and shaping supertype/subtype connections. Best DOWN Following Figs. Indicates Generalization in a circumstance where we have 3 distinctive substance sorts: CAR, TRUCK and MOTORCYCLE In second Fig. The more broad substance sort VEHICLE is appeared

Generalization MOTORCYCLE is not appeared as it doesn't fulfill conditions for a subtype talked about before – the main characteristics of MOTORCYCLE are those that are regular to all vehicles, there are no ascribes particular to cruisers. Facilitate, MOTORCYCLE does not have a relationship to another substance sort

Example of speculation Three element sorts: CAR, TRUCK, and MOTORCYCLE All these sorts of vehicles have normal properties

Generalization to VEHICLE supertype So we put the mutual qualities in a supertype Note: no subtype for cruiser, since it has no exceptional characteristics

Specialization In a case of specialization, an element sort PART has identifier Part_No and different traits Description, Unit_price, Location, Qty_On_Hand, Routing_Number and Supplier (a multivalued property as there might be more than one provider) Some parts are inside Manufactured Parts while others are remotely Purchased Parts (a few sections are gotten from both sources – when the decision relies on upon variables, for example, producing limit, unit cost of the parts and so on.)

Specialization Some credits apply to all parts paying little mind to source, others, for example, Routing_Number rely on upon the source as they apply just to Manufactured Parts. This proposes PART ought to be particular by characterizing the subtypes MANUFACTURED_PART and PURCHASED_PART another relationship "Supplies" has been made amongst PURCHASED_PART and SUPPLIER that permits clients to all the more effortlessly partner acquired parts with their providers The characteristic Unit_Price is presently connected with the relationship "Supplies" so that the cost may shift between providers

Example of specialization Entity sort PART Applies just to obtained parts Only applies to made parts

Specialization to MANUFACTURED PART and PURCHASED PART Note: multivalued quality was supplanted by a relationship to another substance Created 2 subtypes

Completeness requirements Whether an occurrence of a supertype should likewise be an individual from no less than one subtype. Has two conceivable tenets: 1:Total Specialization Rule: Yes (twofold line) In taking after Fig. A PATIENT should either be an OUTPATIENT or a RESIDENT PATIENT. Add up to specialization is demonstrated by the twofold line stretching out from the PATIENT personality sort to the circle

Examples of fulfillment imperatives A patient must be either an outpatient or an inhabitant tolerant Total specialization run

Completeness limitations Partial Specialization Rule: No (single line) An element occurrence of the supertype is permitted not to have a place with any subtype. In the accompanying Fig. On the off chance that a VEHICLE is an auto it will show up as an occasion of CAR, and if a truck as a case of TRUCK. Be that as it may, if the vehicle is a bike it can't show up as an occasion of any subtype. This case of fractional specialization is determined by the single line from the VEHICLE supertype to the circle

Partial specialization administer A vehicle could be an auto, a truck, or not one or the other

Disjointness limitation Whether a case of a supertype may at the same time be an individual from (at least two) subtypes. Has two conceivable standards: Disjoint Rule: An example of the supertype can be just a single of the subtypes. Taking after Fig. demonstrates that at any one time a PATIENT must be either an outpatient or an inhabitant quiet yet can't be both – indicated by the letter "d" The subclass of a patient may change after some time, yet at any given time a patient is of just a single sort

Examples of disjointness requirements A patient can either be outpatient or occupant, however not both Disjoint govern

Disjointness imperative Overlap Rule: An occasion of the supertype can all the while be an individual from more than one of the subtypes. A few PARTs are both Manufactured and Purchased. A case of PART is a specific Part Number (i.e. kind of part) as opposed to the individual part itself (Part_No). Considering Part Number 4000, at a given time there might be 250 of this part to hand, of which 100 are fabricated and 150 are bought. The cover is determined by putting an "o" in the circle Double line recommends any part should be either an acquired part or a made part, or it might all the while be both of these

A section might be both bought and made Overlap govern

Subtype discriminators Attribute of the supertype whose qualities decide the objective subtype(s) Disjoint subtypes: a straightforward credit with option qualities to demonstrate the conceivable subtypes. In the accompanying Fig. Another property "Employee_Type" has been added to the supertype to fill in as a subtype discriminator. 3 values "H" = Hourly, "S" = Salaried, "C" = Consultant. This is appointed the right esteem when another worker is included.

Subtype discriminators The expression "Employee_Type = " (the LHS of a condition articulation) is put by the line driving from the supertype to the hover, with the estimation of the property that chooses the suitable subtype put adjoining the line prompting to that subtype

A straightforward characteristic with various conceivable qualities showing the Subtype discriminator (disjoint control)

Subtype discriminators Overlapping – a composite ascribe whose subparts relate to various subtypes. Every subpart contains a boolean incentive to show regardless of whether the occasion has a place with the related subtype. In the accompanying Fig. another quality Part_Type has been added to PART. Part_Type is a composite characteristic with segments "Produced?" and "Bought?" Each of these qualities is a boolean variable, and can be joined as: Manufactures just = YN, Purchased just = NY, Purchased and fabricated = YY

A composite property with sub-traits designating "yes" or "no" to figure out if it is of each Subtype discriminator ( cover run )

Defining supertype/subtype progressive systems It is workable for any of the subtypes in these cases to have different subtypes characterized on it A supertype/subtype chain of command is a various leveled course of action of supertypes and subtypes, where each subtype has just a single supertype In demonstrating the Human assets in a University, the most broad substance sort would