Kennesaw State University



Module 3 – Enhanced E/R and business functionsContent:EER ModelThe enhanced entity-relationship model has added several additional new pieced.? The most important one is the supertype/subtype relationship.? The EER also has added business rules into the diagram if needed.Supertype/subtypeA supertype is a generic entity type that has a relationship with one or more subtypesA subtype is a subgrouping of the entities in an entity type that is meaningful to the organization and that shares COMMON ATTRIBUTES or relationships distinct from other subgroupings.example:EMPLOYEEEmp_numberEmp_lastnameEmp_firstnamedate_hired??????????????????????????????????????????????????????????????????? |????????????????????????????????????????????????????????????????? O??????????????????????????????????????????????? /?????????????????? |?????????????????? \??????????????????????? HOURLY EMP???????? SALARIED EMP??????? CONSULTANT??????????????????????? Hourly_rate???????????????? Annual_salary????????????? Contract_number????????????????????????????????????????????????????????? Stock_option??????????????? Billing_rateEach of the subtypes:? HOURLY EMPLOYEE, SALARIED EMPLOYEE, and CONSULTANT all share the attributes that are listed with the Supertype EMPLOYEE. They have additional unique attributes that are part of them as well.? You don't repeat the shared attributes from the supertype down to the subtype - it is known that they are "inherited" from the supertype.ConstraintsThere are additional constraints that can be added to supertype/subtype relationships.? These are:1.? completeness - whether an instance of a supertype must also be a member of at least one subtypea.? total specialization rule - each entity instance of the supertype MUST be a member of some subtype in the relationshipsb.? partial specialization rule - an entity instance of the supertype is allowed NOT to belong to a subtype2.? Disjointness Constraint - whether an instance of a supertype may simultaneously be a member of two (or more) subtypesa.? Disjoint rule - an instance of a supertype may NOT simultaneously be a member of two (or more) subtypesb.? Overlap rule - an instance of a supertype MAY simultaneously be a member of two (or more) subtypesBusiness rulesBusiness rules have also been added to E/R diagrams.? They are diagramed using an arrow-head and an accompanying list of the rule.As we analyze an enterprize with our Business functions and entities - we then need to transfer that information into a Diagram - called the Entity-Relationship Diagram.? This is a Data model that will help us design our databases properly.? As any model - the E/R model has certain components.? Following the "read me second" file, you will find a file titled "E/R diagram components".? This file shows the various components - Entity (drawn as a rectangle - and is a Noun); Attributes (these are the fields that make up a particular entity (such as for PATIENT - you would have Patient_lastname, Patient_firstname, etc.); Relationships - these are actually drawn first as a connecting line between entities and we write the relationships (which will be a verb).? We then need to determine how a particular Entity is "related" to another Entity - called Cardinality.? There are four possible combinations for an entity to relate to another entity:? mandatory one (written as two straight lines bi-secting the relationships line); mandatory many (written as one straight bi-secting line and a "less than" symbol); option one (written as a circle followed by a straight by-secting line); and option many (written as a circle followed by the "less than" symbol.? These cardinalities? mean that for one instance of an Entity (say one particular patient of the PATIENT entity) - how many MUST or SHOULD we have of the related Entity (say TREATMENT).? So for Patient X - how many TREATMENT instances MUST or SHOULD we have.? MUST means you have to have at least 1 instance of TREATMENT.? MANY means you can have 1 or many TREATMENT instances for Patient X.? For any given Patient we would probably select the cardinatlity relationship between the Entity - PATIENT and the Entity - TREATMENT as a mandatory many relationships. ?We also have what we call "relationship degrees" - an Entity may be related to itself - for instance - the entity EMPLOYEE may have a relationship with itself as "manages" - in other words, an EMPLOYEE may manage an EMPLOYEE (related to itself) - this is called a UNARY relationship.? The most common relationship between two entities is binary - an entity is related to another entity.? The third type of relationship is Ternary - where three entitites are related through one relationship. (this is very rare and we really don't want to have these types of relationships).We have various type of attributes (the fields that make up a particular entity).?? Again - for example:? STUDENT (entity)? Student_ID, Student_lastname, Student_firstname, Student_street,? etc. are the attributes (fields) that make up STUDENT entity.? We can have both required and optional attributes - in other words some of the fields may or may not have data in them.? We can also have a Composite Attribute - that is made up of sub-attributes.? For example:? Address (is actually made up of Street, City, State, etc.).? A Derived attribute is one that can be calculated from other attribute values.? For example:? Age may be calculated from Current_date and Date_of_birth.A very important attribute is called an Identifier - or "KEY" attribute.? We have two types of key attributes - Primary Key attributes and Secondary Key attributes.? The Primary Key attribute that you select MUST be unique (different) for each instance of your data.? For instance - Social_Security_number is a very good Primary Key attribute for entities such as PATIENT, STUDENT, EMPLOYEE.? You choose which of your attributes (fields) will be your Primary Key attribute.? You can then have secondary key attributes - such as Patient_lastname (however, the data won't be unique here for every instance that you have - in other words, we have many last names that are "Smith".How then do we actually draw an E/R diagram using all of the symbols, etc.? The following are the steps you follow to draw your diagram:1.? Go to your list of business functions/entities - select the first business function and select the first entity2.? Draw the entity on a pierce of paper (as a rectangle) and put the name of your entity in the rectangle (for instance if your first business function is SALES- you select your first Entity - EMPLOYEE)3.? now take your next entity in that business function and draw it as a rectangle and put the name of the entity inside (entity PRODUCT) - if you don't have anymore entities in that particular business function, go to the next business function and get the entity there.? YOU DO NOT REDRAW AN ENTITY THAT IS ALREADY DRAWN!!? For instance - EMPLOYEE will only be drawn ONCE even though it will appear in several of your business functions!!4.? Look at the last entity that you've drawn and see if that entity is related in anyway to all of the other entities you have drawn (for instance, is EMPLOYEE related to PRODUCT??)? Yes - a product is sold by an employee.? You would draw your straight line between the two entities and name your line - you can put in our example? either "is sold by" or "sells" - a PRODUCT is "sold by" an EMPLOYEE, or an EMPLOYEE "sells" a PRODUCT.? Remember - your Entities are NOUNS and your relationships are VERBS.? You do this for each entity on your diagram you have already drawn.5.? Repeat steps 3-4 until there are no more entities form your list of business functionsYou now have the fist part of your E/R diagram drawn - all entities and relationships.? You now go to the next steps6.? You add the cardinalities now - take one of your entities that you have drawn that has a relationship to another entity - such as EMPLOYEE and PRODUCT.? You determine the degree of relationship by applying the following statement:? Given one instance of an entity A (whatever that entity may be) - how many instances of entity B (the other entity in the relationship) MUST or COULD I have.? Remember - MUST means mandatory, and COULD means optional.? Given one instance of EMPLOYEE how many instances of PRODUCT MUST or COULD I have??? First - does every EMPLOYEE instances sell or handle PRODUCT?? Actually - no, you have several employees that never deal with PRODUCT.? SO - this isn't a MANDATORY relationship but an OPTIONAL one.? Given a particular EMPLOYEE instance who does have relationships with PRODUCT (for instance a salesman who is an employee) - how many instances of PRODUCT would I have - one or many??? I would hope that a salesman would sell "many" instances of PRODUCT.? So your cardinality between EMPLOYEE and PRODUCT (directional from EMPLOYEE to PRODUCT) is an optional many relationship (drawn as the circle with the less than symbol).? The symbols are drawn next the PRODUCT entity.? Now you reverse the direction.? Given one instance of a particular PRODUCT - how many EMPLOYEE instances may or could I have??? Well, since this is an individual instance of a particular PRODUCT - it would only have one EMPLOYEE selling it.? So, we would have a MANDATORY ONE relationship between PRODUCT and EMPLOYEE (drawn next to EMPLOYEE).7.? You repeat the cardinality process with ALL of your entities that you have drawn!!8.? You final step is to go back and add your Attributes into each rectangle (Entity).You have completed your E/R Diagram!!As E/R diagrams developed it became clear that we needed to have additional types of symbols to represent different types of relationships for object-oriented thinking.? The model became the Enhanced E/R model or EER.The first type of notation we needed was to be able to diagram supertype/subtype relationships.? A supertype is a generic entity type that has a relationships with one or more related subtypes.? A subtype ia a subgrouping of the entities in an entity type that is meaningful to the organization and that shares common attributes or relationships distince from other subgroupings.An example of supertype/subtype can be with EMPLOYEE (entity).? For instance - we have classifications of HOURLY EMPLOYEE, SALARIED EMPLOYEE, and CONSULTANT EMPLOYEE.? All of the EMPLOYEE types share many common attributes - for instance, Employee_lastname, Employee_firstname, Employee_socsec, Employee_street, Employee_city, etc.? However, if we look at an HOURLY EMPLOYEE type they would have an attribute of Employee_Hourly_Rate that none of the other EMPLOYEE types have.? Similarly, the SALARIED EMPLOYEE would have attributes of Employee_Annual_Salary, Employee_Stock_Option - none of the other types would have these attributes.? The CONSULTANT EMPLOYEE would have as unique attributes Employee_Contract_Number, and Employee_Billing_Rate.? So, we have many shared attributes, but some unique attributes that only certain "types" of EMPLOYEE have.? This is a great time to use the supertype/subtype diagramming on your EER.? EMPLOYEE would be your supertype, and you would have three subtypes - HOURLY EMPLOYEE, SALARIED EMPLOYEE, and CONSULTANT EMPLOYEE.? You can look at your slides to see how to diagram this.So - you use the supertype/subtype on your EER when either or both of the following apply to the company:1.? There are attributes that apply to some, but not all, instances of an entity type2.? The instances of a subtype participate in a relationship unique to that subtype (would have their unique attributes)For our lab - we have the entity PATIENT.? Do we have types of PATIENT?? How about OUTPATIENT and RESIDENT PATIENT?? Would this possibly be a supertype/subtype?As we draw our supertype/subtype we use connecting lines with a circle connecting the lower subtypes.? As we are trying to decide whether or not we have supertype/subtype entities - we need to approach the entities we have for our company in one of two ways - through Generalization or Specialization.? Generalization means we start with the normal entity - such as EMPLOYEE and begin listing all of the attributes that we have - if we find that certain attributes ONLY pertain to some types of EMPLOYEE then we probably can identify a supertype/subtype kind of relationships.? So - we redraw our E/R diagram including the new symbols.? Specialization would occur when we are listing our entities - for example, we have an entity - EMPLOYEE and we have another entity - ALUMNUS and another entity STUDENT that we have listed with our business functions of a University.? As we draw our E/R diagram and list the attributes for each of these three entities - we find that we have MANY shared attributes between the three - such as Social_Sec_Num, Lastname, Firstname, Gender, Date_of_Birth, etc.? But, we also find that we have some unique attributes that would only belong to an EMPLOYEE, ALUMNUS or STUDENT.? Such as an attribute for EMPLOYEE would have Salary, Date_hired, while ALUMNUS would have Date_of_Graduation, Degree_type, Degree_major, etc., and STUDENT would have Student_ID.? As we think about our business - the university, we might want to create a brand new supertype - PERSON that would have three subtypes under it.? It would depend on how the business/university operates if this makes sense.Two other tyings we have in the new EER are Constraints for our supertype/subtype relationships.? A constraints allow the diagram to capture some of the important business rules that the company operates under.? We will first look at Completeness Constraints.? A completeness constraint is a type of constraint that addresses the question whether an instance of a supertype MUST also be a member of at least one subtype.? The constraint is done through two types - the total specialization rule or the partial specialization rule.? Total specialization rule states that each entity instance of the supertype MUST be a member of some subtype in the relationship.? We show this through using a double line from the supertype to the circle.? The particial specialization rule states that an entity instance of the supertype is allowed NOT to be long to any subtype.? This is done using a single line from the supertype to the circle.The 2nd type of constraint is the Disjointness constraint.? A disjointness constraint is one that addresses the question whether an instance of a supertyp0e may simultaneously be a member of two (or more) subtypes.? The Disjoint rule specifies that an instance of a supertype MAY NOT simultaneously be a memer of two (or more) subtypes.? We put a letter "d" inside the circle to indicate this.? The Overlap rule specifies that an instance of a supertype may simultaneously be a member of two (or more) subtypes).? We put a leter "O" inside the circle.As you redraw your E/R diagram for Lab 2 - you can decide if you want to use any of the additional EER symbols to better diagram the hospital.? I would look at EMPLOYEE, PHYSICIAN, NURSE, TECHNICIAN, etc.? as possibilities.Business Functions ENTITYPatient Care ServicesPATIENTPHYSICIANNURSEVOLUNTEERBEDEQUIPMENTPatient Care AdministrationPATIENTPHYSCIANSTAFFMEDICAL RECORDRECIEPTHuman ResourcesPHYSICIANNURSETECHNICIANVOLUNTEERSTAFFPAYROLLFinancial AdministrationSTAFFPAYROLLPURCHASE ORDERINVOICERECEIPTExaminations / TestsPATIENTPHYSICIANTECHNICIANNURSEEQUIPMENTInventory ControlSTAFFINVOICEPURCHASE ORDERVENDOREQUIPMENTMountain View Community Hospital a) List the various Business Functions of Mountain View Community Hospital (this will be done in Word) - remember to keep this "high level" - what the hospital doesb) List the various Entities that support EACH Business function (again done in Word) each business function should have a separate list of entities associated with it.Business FunctionEntitiesMarketingProduct , Vendor, Marketing Material , Employees, Billboards, Patients, Websites, Commercials, Newspapers Human ResourcesEmployee, trainers, employee profile softwareOrderingVendor, employee, product, invoice, orderCustomer ServiceEmployees, patients, volunteersInventoryProduct, employee, equipment, storage, vendor, purchase, invoiceSales(Service)patients, employees, volunteers, building, product, billing system, trucksLinks: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download