CIS 4365 Professor Kirs Slide 1 Entity Relationship

CIS 4365: Professor Kirs Slide 1 Entity Relationship Diagrams Chapter 3: Entity-Relationship Modeling (With Considerable Modifications)

CIS 4365: Professor Kirs Slide 2 Entity Relationship Diagrams The Entity Relationship Diagram (ERD) • Developed by Chen (1976) • THE Most commonly used data modeling tool • Data modeling is generally considered the most important component of the systems development process • Shows the structure, requirements and constraints of the intended system, independent of software (DBMS), at a higher level of abstraction • Tool for communications between database designers and users • Also, a planning/organization tool

CIS 4365: Professor Kirs Slide 3 Entity Relationship Diagrams A Quick Aside: • How many times have you been shown a model in a class only to find out it is useless ? ? Too Many !!! • This is NOT one of those times -- • ERDs form the foundation of all database modeling • It is IMPOSSIBLE to develop a working Database without them

CIS 4365: Professor Kirs Slide 4 Entity Relationship Diagrams Basic ERD Symbols • Anything about which we wish to maintain information Entity Person Thing Event Place Object Description • Entity Instance: A single occurrence of the entity (record) • Entity Type: A collection of entities (File) Relationship Attributes • An association (or action which occurs) between Entity types Customers place orders Orders contain parts • Fields within a Record (entity Instance) CUSTOMER(custid, name, address) • Connectors between other elements

Slide 5 CIS 4365: Professor Kirs Entity Relationship Diagrams A Simple ERD: Consider the following situation A customer places an order. The order consists of parts. Entity Relationship Customer Places An Organization An Association about which we between Entities wish to maintain information Altogether, a Database Another Relationship Orders Contain Another Entity Parts

CIS 4365: Professor Kirs Slide 6 Entity Relationship Diagrams PROBLEM: The model does not clearly show the entity instances are related (Cardinality). Places Customer One Customer can place many orders 1 One Order can contain many parts M Orders Contain M One Order is placed by one (and only 1) customer A One-to-Many (1: M) Relationship One Part is contained in many orders A Many-to-Many (M: M) Relationship M Parts

Slide 7 CIS 4365: Professor Kirs Entity Relationship Diagrams Alternative Notation Places Customer Given 1 Customer, how many Orders can be placed ? ? Many Given 1 Order, How many parts can it contain ? ? Many Orders Given 1 Order how many customers placed it? ? ONE Contain Parts Given 1 part, How many orders can contain it ? ? Many

Slide 8 CIS 4365: Professor Kirs Entity Relationship Diagrams Cardinality Constraints Places Customer A Customer MAY (OPTIONAL), place more than 1 order. An Order MAY (OPTIONAL) contain many parts. Orders An Order MUST (MANDATORY) be placed by one customer Contain Parts A Part MUST (MANDATORY) be contained in many orders

CIS 4365: Professor Kirs Slide 9 Entity Relationship Diagrams Additional Notation Customer Orders Customer Strong Entity An Entity which is not dependent upon other entities Weak Entity An Entity which exits because of another entity Places Identifying Relationship Orders

CIS 4365: Professor Kirs Slide 10 Entity Relationship Diagrams Associating Attributes With Entities Simple Attribute Derived Attribute Customer_Name Yrs_in_Business Customer_ID Primary Key Purch_Agts Multivalued Attribute (Repeating Groups)

CIS 4365: Professor Kirs Slide 11 Entity Relationship Diagrams Associating Attributes With Entities (Cont. ) Order_Num Order_Date Foreign Key Customer Composite Attribute (fixed number) Order_ID Order Cust_Address Street City Street

CIS 4365: Professor Kirs Slide 12 Entity Relationship Diagrams Degree of Relationship: Number of Entities Participating Customer Orders Employee Places 1 M Contain M M Is Assigned 1 1 Orders a 1: M Binary Relationship Parts a M: M Binary Relationship Title a 1: 1 Binary Relationships are the most common Type of Relationships

CIS 4365: Professor Kirs Slide 13 Entity Relationship Diagrams Additional Relationships • Consider the relationship between the Part that a Vendor ships to a Store Vendor M M Parts M Produces A Vendor Produces Many Parts A Part can be Produced by many Vendors A M: M Relationship A Part can be shipped to many Stores can hold to many Parts Also a M: M Relationship Ships M Stores

CIS 4365: Professor Kirs Slide 14 Entity Relationship Diagrams Additional Relationships • More specifically, consider the situation where Stanley (a Tool Vendor), Produces many Parts (Hammers, Screw Drivers, etc), and Ships them to many Home Depot Stores. M M M Vendor Parts Produces Ships Stanley Produces Many Parts (Hammers, Screws, etc. ) A Part can be Produced by many Vendors (Hammers by Sears, Black and Decker, etc. ) A M: M Relationship M Stores A Part can be shipped to many Stores (El Paso West Side, El Paso East Side) A M: M Relationship (Each) Store can hold to many Parts

CIS 4365: Professor Kirs Slide 15 Entity Relationship Diagrams What if I wished to find out what parts a vendor ships to what warehouses ? ? • The three entities are interdependent (A simultaneous relationship) Parts Vendor Supplies Cardinality ? ? ? • Given 1 vendor and 1 part, how many Stores? • Given 1 Store and 1 vendor, how many parts? • Given 1 Store and 1 part, how many vendors? A Ternary (of degree 3) Situation Store Many

CIS 4365: Professor Kirs Slide 16 Entity Relationship Diagrams What about cardinality CONSTRAINTS in Ternary Relationships ? ? Parts Vendor Supplies Store • Given 1 vendor and 1 part, MUST there be many Stores? • Given 1 Store and 1 vendor, MUST there be many parts? • Given 1 Store and 1 part, MUST there be many vendors? The TRUE solution lies in the actual situation NO NO NO

CIS 4365: Professor Kirs Slide 17 Entity Relationship Diagrams Additional Relationships • Suppose we wished to track people who were married to each other (and get information about spouses) • We could set up a binary relationship A Person may have 1 spouse Person SSN Addr. A Spouse MUST have 1 person Is Married to Spouse SSN Primary/ Foreign Spouse SSN Addr. Keys But, Each Entity Type has the same attributes

Slide 18 CIS 4365: Professor Kirs Entity Relationship Diagrams Unary Relationships (Recursive) A Person May be married Person A Spouse Must be married Is Married to Unary relationships may take on any cardinality • 1: 1 A person is married to a person • 1: M An employee manages (an) employee(s) • M: M parts contain parts

CIS 4365: Professor Kirs Slide 19 Entity Relationship Diagrams How do we know how to set up an ERD? • It depends on the semantics of the problem • Consider the following problem description “At this college, there are many departments. Each department has a number of faculty members, and a faculty member may belong to only one department. In each department, there is one faculty member assigned to supervise the other faculty members”. Let’s build the ERD in stages Keep in mind that there are many ways to arrive at the same solution (Equifinality)

CIS 4365: Professor Kirs Slide 20 Entity Relationship Diagrams We know that we have Departments which consist of Faculty both must be entity types, since we wish to keep information about them We also know that Departments have many Faculty and that each Faculty may belong to a single Department Consists of Faculty Given 1 Department, how many faculty members? Given 1 Faculty member, how many departments? Many One

CIS 4365: Professor Kirs Slide 21 Entity Relationship Diagrams We also know that a Faculty member is designated as another Faculty member’s supervisor. Our ERD might now appear as: Department Consists of Faculty Supervises One faculty member MUST supervise many other faculty members Each faculty member MUST be supervised by ONLY ONE other faculty member

CIS 4365: Professor Kirs Slide 22 Entity Relationship Diagrams Let’s add one more constraint: “The faculty member who supervises other faculty is also responsible for managing the department”. (This involves another relationship between faculty and department) Department Consists of Faculty Supervises Manages Given 1 Department, how many faculty managers? One (and only one) Given 1 Faculty, how many Departs. does s/he manage? One (or none)

CIS 4365: Professor Kirs Slide 23 Entity Relationship Diagrams Translating ERDs into database structures • Consider the following problem setting “You work for a hospital. The Head Administrator has told you that he suspects that Dr. Smith, as well as all of the other physicians which she supervises, has been prescribing too much Codeine for their patients. He wants a list of all of Dr. Smith’s patients, their illnesses, and what prescriptions were given to those people. If it looks suspicious, he will later want the same list for each of the physicians Dr. Smith supervises. ” • There is really one (non-apparent) constraint: “Each patient can have only one primary physician. ”

CIS 4365: Professor Kirs Slide 24 Entity Relationship Diagrams Simplifying/Elucidating the Problem Semantics: “A Physician Treats many Patients. ” Given “A Patient is Treated by only one Primary Physician. ” Given “Many Patients Suffer many Illnesses. ” “Many Illnesses are Suffered by many Patients. ” Inferred “Many Illnesses Require many Prescriptions. ” “Many Prescriptions are Required by many Illnesses. ” Inferred “A Physician Supervises many other Physicians. ” “A Physician is Supervised by another Physician. ” Inferred We can now identify (based in the original Statement) All of the Entities and their Cardinality and, (implicitly), their constraints

CIS 4365: Professor Kirs Entities: Slide 25 Entity Relationship Diagrams Physician Patient Illness Prescription “You work for a hospital. The Head Administrator has told you that he suspects that Dr. Smith, as well as all of the other Physicians which she Supervises, has been prescribing too much Codeine for their Patients. He wants a list of all of Dr. Smith’s patients, their Illnesses, and what Prescriptions were given to those people. If it looks suspicious, he will later want the same list for each of the physicians Dr. Smith supervises. ” 1 st Relationship: Physician Treats Patient

CIS 4365: Professor Kirs Physician Slide 26 Entity Relationship Diagrams Treats Given 1 Physician, how many Patients? Mandatory? Given 1 Patient, how many Physicians? Mandatory? Patient Many No One Yes Attributes for Physician ? ? ? Physician Name Specialty Address

CIS 4365: Professor Kirs Slide 27 Entity Relationship Diagrams Attributes for Patient ? ? ? There could be any number. Let’s keep it simple right now Patient Name Address

CIS 4365: Professor Kirs Slide 28 Entity Relationship Diagrams Problem 1: Suppose we find that there are 11 Mary Smiths in the Physician Database Solution 1: Check the address as well Problem 2: There are 2 Mary Smiths in the Physician Database with the same Address Solution 2: Assign a UNIQUE identifier (e. g. , SSN) Physician Phys. ID Name Specialty Address Primary Key: Every entity MUST have a unique identifier (SSN? ? )

CIS 4365: Professor Kirs Slide 29 Entity Relationship Diagrams Given our layout for the Physician Entity, we can set up our Patient Entity in much the same manner: Patient Pat. ID Name The Primary Key: Address Perhaps SSN

CIS 4365: Professor Kirs Slide 30 Entity Relationship Diagrams Next Problem: How do we relate a physician to a patient? ? Solution 1: Place the Patient Information (e. g. , using the primary key Pat. ID) as an attribute in the Physician Entity Physician Phys. ID Name Specialty Address Pat. ID 1 • • • Pat. IDn Next Problem: How many patients does a physician have? None? Maybe the physician has no patients presently admitted One? Maybe they are the President’s Physician A Million? Maybe they are an ambulance chaser

CIS 4365: Professor Kirs Slide 31 Entity Relationship Diagrams If we follow our layout (especially using a relational database layout) we MUST allocate enough fields for the MAXIMUM case (e. g. , a Million Patient fields) So ? ? Assume that each Pat. ID requires 9 -bytes of storage (Reasonable) Assume that the average physician has 70 patients (Reasonable) Assume there a total of 500 Physicians (Maybe) We should need: 500 * 70 * 9 = 315, 000 Bytes But we must allocate for the maximum: 500 * 1, 000 * 9 = 5, 400, 000 Bytes A difference of: 5, 399, 685, 000 Bytes ? ? What is the Solution ? ?

CIS 4365: Professor Kirs Slide 32 Entity Relationship Diagrams Put the Physician Identifier into the Patient Table (Since by definition, a Patient can have only one Physician) Patient Pat. ID Name Address Phys. ID Phys. Id is a Foreign Key: A foreign key is an attribute in a table that serves as a primary key in a table for the same database

CIS 4365: Professor Kirs Slide 33 Entity Relationship Diagrams How would the tables actually be laid-out ? ? ? Physician Table Attributes/Fields Phys. ID Patient Table Attributes/Fields 1 Pat. ID Name Specialty Address M Phys. ID Is the field Phys. Id actually placed in the Patient Table ? ? ? YES

CIS 4365: Professor Kirs Slide 34 Entity Relationship Diagrams How Do we deal with Multivalued Attributes ? ? ? Depends: Sometimes, attributes are stored in separate tables In this case, they are separate fields in the table Physician Table Attributes/Fields Phys. ID Patient Table Attributes/Fields 1 Pat. ID Name Specialty Street City State Zip. Code M Phys. ID

CIS 4365: Professor Kirs Slide 35 Entity Relationship Diagrams Consider our Next Semantic Constraint: “Many Patients Suffer many Illnesses. ” Physician Treats Patient Suffer Illness Given 1 Patient, MUST they suffer many Illnesses ? ? No Given 1 Illness, MUST it be suffered by many Patients ? ? Maybe ? ? The true constraints of a problem are not always stated nor readily inferred --- INVESTIGATION is often needed

CIS 4365: Professor Kirs Slide 36 Entity Relationship Diagrams What Attributes are needed for Illnesses ? ? ? Depends --- Let’s, again, keep it simple Illness Ill. Code Description Others Problem: How do we relate patients and Illnesses?

CIS 4365: Professor Kirs Slide 37 Entity Relationship Diagrams Solution 1: Put Ill. Code in the Patient Table as a foreign Key Patient Pat. ID BUT -- Name Address Phys. ID Ill. Code 1 • • • Ill. Coden This causes the same problem we encountered when we tried to put Pat. ID in the Physician Table !!!

CIS 4365: Professor Kirs Slide 38 Entity Relationship Diagrams Solution 2: Put Pat. ID in the Illness Table as a foreign Key Illness Ill. Code Description Others Pat. ID 1 • • • Pat. IDn This Solution is No Better than the Other !!!

CIS 4365: Professor Kirs Slide 39 Entity Relationship Diagrams Is One Solution Preferred Over Another ? ? ? IF we put Ill. Code in the Patient Table as a foreign Key, the Question becomes “What are the maximum number of Illnesses a Patient can have? ” ? ? A Million ? ? IF we put Pat. ID in the Illness Table as a foreign Key, the Question becomes “What are the maximum number of Patients that can have any Illness? ” ? ? A Million ? ? So? ? ? Which one is Preferred? ? ? NEITHER Another Solution Must Be Found

CIS 4365: Professor Kirs Slide 40 Entity Relationship Diagrams What Other Solution Could there be ? ? ? Create A Separate Table Which Links the Two Entities What Table ? ? ? How about a Table called Suffers ? ? ? We Can’t have a Table Called Suffers is the Relationship between Patient and Illness. Why Can’t a Relationship also be a Table ? ? ?

CIS 4365: Professor Kirs Slide 41 Entity Relationship Diagrams How would relationship/table Suffers be Laid out ? ? ? If we look at our relationship, and the Entity Attributes: Suffer Patient Illness Pat. ID Name Phys. ID Ill. Code Others Name Address We Know that the new entity should contain attributes from each of the other Entitites Which Ones ? ? ?

CIS 4365: Professor Kirs Slide 42 Entity Relationship Diagrams As Before, It makes sense to Include Primary Keys Which One ? ? Pat. ID or Ill. Code ? ? Why Not Both ? ? How Can we have both Pat. ID and Ill. Code ? ? By Creating A Concatenated Key is one which merges two strings (or fields) together into a single field How Can we merge two strings ? ?

CIS 4365: Professor Kirs Slide 43 Entity Relationship Diagrams Suppose that we had the fields: 123 -45 -6789 A 98. 017. 67 (Pat. ID: Assume Clara Schumann) and (Ill. Code: Assume a Broken Arm) If we wished to merge the fields, we would end-up with: 123 -45 -6789 A 98. 017. 67 (Or Perhaps 123456789 A 9801767 If we leave-out the Formatting) SO ? ? We Know that Clara Schumann (Pat. ID 123 -45 -6789) Suffered a Broken Arm (Ill. Code A 98. 017. 67)

CIS 4365: Professor Kirs Slide 44 Entity Relationship Diagrams Does That mean we have Linked the two entities ? ? ? Yes --- But it violates one of our Conditions: An Entity MUST have a UNIQUE Identifier Who else can have Clara Schumann’s broken arm but her ? ? ? No One --- BUT What if Clara Schumann (Pat. ID 123 -45 -6789) Suffered another Broken Arm (Ill. Code A 98. 017. 67)? ? The entry would still appear as: 123456789 A 9801767 (Which is No Longer unique)

CIS 4365: Professor Kirs Slide 45 Entity Relationship Diagrams How can we make the occurrence unique ? ? ? We can Time-stamp (more on this later) the event, meaning we can add the time of the occurrence For example, if Clara broke her arm on July 4, 2000, we might enter the event as: 123456789 A 9801767 070400 Pat. ID + Ill. Code + Date

CIS 4365: Professor Kirs Slide 46 Entity Relationship Diagrams Would the Field NOW be unique ? ? ? Maybe --- It depends on the event If it were a date of birth (e. g. ) it would be sufficient We need to answer the question: “Can a Patient break their arm more than once a day? ” If the answer is YES, then we need to add the time: 123456789 A 9801767070420001608 Pat. ID + Ill. Code + Date + Time (Assume Military Time: 16: 08 => 4: 08 PM) Why Not Add seconds ? ? Let’s Get Real !!

Slide 47 CIS 4365: Professor Kirs Entity Relationship Diagrams How would the ERD be laid-out ? ? ? The Relationship Suffer is now the Associative Entity Suffer Pat. ID Patient Name Suffer Illness Phys. ID Ill. Code Name Address Others Treatment is the field name we have given the primary (concatentated) key (Pat. ID+Ill. Code+Date+Time) Others Whatever other Attributes we wish

Slide 48 CIS 4365: Professor Kirs Entity Relationship Diagrams How would the tables actually be laid-out ? ? ? Let’s Consider one relationship at a time: Patient Table Attributes/Fields Pat. ID Name Street Suffer Table Attributes/Fields 1 M Treatment Others City State Zip. Code Phys. ID Given 1 Patient, How many Treatments? Many Given 1 Treatment, How many Patients? One

Slide 49 CIS 4365: Professor Kirs Entity Relationship Diagrams And the Suffer/Illness Relationship would be: Suffer Table Attributes/Fields Treatment Others M Illness Table Attributes/Fields 1 Ill. Code Name Others Given 1 Treatment, MANY Illnesses are Possible Given 1 Treatment, ONE Illness is Possible

Slide 50 CIS 4365: Professor Kirs Entity Relationship Diagrams The Full Relationship would appear as: Patient Table Attributes/Fields Pat. ID Name Street City State Zip. Code Phys. ID 1 Suffer Table Attributes/Fields M Treatment Others M 1 Illness Table Attributes/Fields Ill. Code Name Others

CIS 4365: Professor Kirs Slide 51 Entity Relationship Diagrams Is this how we PHYSICALLY Set up the Data Base using a DBMS ? ? Not Really --- We would set-up each concatenated component as a separate field Table Suffers would be set-up as: Suffer Table Attributes/Fields Pat. ID Ill. Code Date/Time Others

CIS 4365: Professor Kirs Slide 52 Entity Relationship Diagrams Let’s, again, Consider one relationship at a time: Suffer Table Attributes/Fields Patient Table Attributes/Fields Pat. ID Name Street 1 M Pat. ID Ill. Code City Date/Time State Others Zip. Code Phys. ID Given 1 Pat. ID, How many occurrences in Many Table Suffer will appear ? ? Given 1 ocurrance of Pat. ID in Table Suffer, how many occurrences in Table Patient are there ? ? One

Slide 53 CIS 4365: Professor Kirs Entity Relationship Diagrams The Suffer/Illness Relationship would be: Suffer Table Attributes/Fields Pat. ID 1 Ill. Code Date/Time Others Illness Table Attributes/Fields Ill. Code Name M Others Given 1 Occurance of Ill. Code in Suffer, One Occurrence of Ill. Code is possible in Illness Given 1 Treatment, MANY Illnesses are Possible

Slide 54 CIS 4365: Professor Kirs Entity Relationship Diagrams The Full Relationship would appear as: Patient Table Attributes/Fields Pat. ID Name Street 1 M Pat. ID Ill. Code City Date/Time State Others Zip. Code Phys. ID Illness Table Attributes/Fields Suffer Table Attributes/Fields 1 M Cardinality ? ? As previously defined Ill. Code Name Others

Slide 55 CIS 4365: Professor Kirs Entity Relationship Diagrams As a recap, our ERD (to date) would appear as: Physician Treats Physid Pat. ID Name Specialty Street City State Zipcode Physid Suffer Patient Patid Ill. Code Illness Illcode Illname Others Date. Time Others Messy, n’est pas ? ? Oui !!! A Nasty Little Bugger !!

CIS 4365: Professor Kirs Slide 56 Entity Relationship Diagrams We have already mentioned that there alternative modeling diagrams. There is one more we need to know about: UML (Unified Modeling Language) Set of OO modeling conventions that are used to specify or describe software systems Attempt to create a single, standard process Provides notation for OO Modeling • Does NOT prescribe a method for developing Systems Adopted by the Object Management Group as the industry standard in 1997 • Still often referred to as a ‘work in progress’

CIS 4365: Professor Kirs Slide 57 Entity Relationship Diagrams In UML, we might represent our relationships (so far) as: Physician • Phys. ID Name Specialty Street City State Zipcode Patient has 1 • Pat. ID Name Street * City State Zipcode ~Phys. ID Where: • Primary Key ~ Foreign Key x Composite Attribute Illness Can May 1 1 * Suffer * • ~ Pat. ID • ~ Illode Date/Time x Others • Illcode Illname x Others

CIS 4365: Professor Kirs Slide 58 Entity Relationship Diagrams Now that one is nice and clean !!! It’s the one we are going use, right ? ? ? Non, mon ami. Why not, moi fol ? ? ? • That is not how the textbook presents it • Most organizations still use the Chen notation • If it ever changes (and it will, I think, soon), you’ll have no problems adapting

CIS 4365: Professor Kirs Slide 59 Entity Relationship Diagrams Consider our Next Semantic Constraints: “Many Illnesses Require many Prescriptions. ” “Many Prescriptions are Required by many Illnesses. ” Physician Treats Patient Suffer Illness Cardinality Constraints ? ? Given 1 Illness, MUST it Require many No Prescriptions ? ? Given 1 Prescription, MUST it be Required by many Illnesesses ? ? No Require Prescription

CIS 4365: Professor Kirs Slide 60 Entity Relationship Diagrams What Attributes are needed for Prescriptions ? ? ? Once again, it depends --In line with what we have been doing, let’s keep it simple Drug. Code Prescription Others Name We Already know that Illnesses has the attributes: Ill. Code Illness Name Others

CIS 4365: Professor Kirs Slide 61 Entity Relationship Diagrams What about the relationship between Illness and Prescription ? ? We Already know, since the relationship between Illness and Prescription is M: M, that Requires will be an Associative Entity Illness Require Prescriptions What about the attributes for the Associative Relationship Require ? ?

CIS 4365: Professor Kirs Slide 62 Entity Relationship Diagrams Once again, we could set up a concatenated key, such as Dosage, which would uniquely link the two entities together: Require Dosage Others Dosage = Illcode + Drugcode Where: (The Primary Keys of the two Entities which it links) For example: A 9801767 87 F 78 B 61 Ill. Code for Broken Arm Drug. Code for Codeine

Slide 63 CIS 4365: Professor Kirs Entity Relationship Diagrams Physically, we might link the Illness and Requires as: Illness Table Attributes/Fields Ill. Code Name Others Requires Table Attributes/Fields 1 M Ill. Code Drug. Code Others Given 1 Occurance of Ill. Code in Illness, How many Occurrences of Ill. Code in Requires ? ? Many Given 1 Occurance of Ill. Code in Requires, How many Occurrences of Ill. Code in Illness ? ? One

Slide 64 CIS 4365: Professor Kirs Entity Relationship Diagrams Requires and Prescription would be linked as: Requires Table Attributes/Fields Ill. Code Drug. Code Others Prescription Table Attributes/Fields 1 M Drug. Code Others Given 1 Occurance of Drug. Code in Requires, How many Occurrences of Drug. Code in Prescription ? ? One Given 1 Occurance of Drug. Code in Prescription, How many Occurrences of Drug. Code in Requires ? ? Many

Slide 65 CIS 4365: Professor Kirs Entity Relationship Diagrams The Full Relationship would appear as: Illness Table Attributes/Fields Ill. Code Name Others 1 Requires Table Attributes/Fields M Ill. Code Drug. Code Prescription Table Attributes/Fields 1 M Drug. Code Others Notice that in each case, we have broken a M: M Relationship into two 1: M Relationships

CIS 4365: Professor Kirs Slide 66 Entity Relationship Diagrams There is one additional Problem: The Concatentated/Composite Key Ill. Code, Drug. Code is INSUFFICIENT to uniquely identify a record • Many Patients may Suffer the same Illness and may Require the Same Prescription How do we correct that ? ? ? A quick-and-dirty fix would be to add a unique (autonumber) as the Primary key

Slide 67 CIS 4365: Professor Kirs Entity Relationship Diagrams The New Relationship would appear as: Illness Table Attributes/Fields Ill. Code Name Others 1 Requires Table Attributes/Fields M Prescription Table Attributes/Fields 1 Number Ill. Code Drug. Code M Drug. Code Others Now, the Field Number is the only Primary key

CIS 4365: Professor Kirs Slide 68 Entity Relationship Diagrams BUT, BUT How do we associate a Patient with a Prescription ? ? ? We could (maybe) select (more on that we we cover SQL) a specific Patient with a specific Illness and find out what Prescriptions were given What if we wished to find all of the prescriptions given to all of the patients ? ? That could be problematic.

CIS 4365: Professor Kirs Slide 69 Entity Relationship Diagrams We COULD set-up an additional relationship between tables Patient and Prescription: Patient Suffer Illness Given 1 Prescription there may be many Patients Using it. Require Meaning Use would become an Associative Entity Use Prescription Given 1 Patient they may be Using many Prescriptions

Slide 70 CIS 4365: Professor Kirs Entity Relationship Diagrams The New Relationship would appear as: Illness Patient Attributes/Fields Pat. ID Name Others 1 Uses Table Attributes/Fields M Pat. ID Drug. Code Prescription Table Attributes/Fields 1 M Drug. Code Others Again, we have broken a M: M Relationship into two 1: M Relationships

Slide 71 CIS 4365: Professor Kirs Entity Relationship Diagrams Is this a Unique Primary Key ? ? ? Can’t a Patient have the same Drug prescribed more than Once ? ? ? Yes -- But once again, we can create a unique (autonumber) as the Primary key Patient Table Attributes/Fields Pat. ID Name Others 1 Uses Table Attributes/Fields M Prescription Table Attributes/Fields 1 Number Pat. ID Drug. Code Others M Drug. Code Others

CIS 4365: Professor Kirs Slide 72 Entity Relationship Diagrams Shouldn’t We also set up a relationship between The Physician and the Prescription? ? Physician Treats Patient Suffer Prescribe Illness Require Given 1 Prescription there may be many Physicians Prescribing it. Use Given 1 Physician they may be Prescribing MANY Prescriptions Prescription

CIS 4365: Professor Kirs Slide 73 Entity Relationship Diagrams That is really not necessary, since knowing the Pat. ID can readily give us the Phys. ID: Physician Table Attributes/Fields Phys. ID Patient Table Attributes/Fields 1 Pat. ID Name Specialty Street City State Zip. Code M Phys. ID

CIS 4365: Professor Kirs Slide 74 Entity Relationship Diagrams We DO have one real Problem, However: We know what Illnesses each Patient Suffered Patient Suffer Illness We know what Illnesses each Required what Prescriptions And Now, We know what Patients Used which Prescriptions BUT, We do NOT know what Patients Used which Prescriptions for what Illnesses Require Use Prescription

CIS 4365: Professor Kirs Slide 75 Entity Relationship Diagrams Why Not? ? ? Suppose we had the following data stored about a Patient: Patient Table Pat. ID 123456789 Suffers Table Name Clara Schumann Pat. ID 123456789 Illness Table Illcode I 3456 I 7654 I 8795 Date 10/01/01 10/29/01 11/07/01 We know That Clara Schumann Suffered: • A Broken Arm on 10/01/01 • A Headache on 10/29/01 • A Bad Hair Day on 11/07/01 Illcode I 3456 I 7654 I 8795 Description Broken Arm Headache Bad Hair Day

CIS 4365: Professor Kirs Slide 76 Entity Relationship Diagrams We Further know: Patient Table Pat. ID 123456789 Illness Table Suffers Table Name Clara Schumann Pat. ID 123456789 Illcode I 3456 I 7654 I 8795 Date 10/01/01 10/29/01 11/07/01 Description Broken Arm Headache Bad Hair Day Prescription Table Uses Table Number 11111 22222 33333 44444 Illcode I 3456 I 7654 I 8795 Pat. ID 123456789 We know That Clara Schumann Used: • Aspirin • Thorazine • Codeine • Retin-A Drug. Code D 999 D 888 D 777 D 666 Drug. Code Description D 999 D 888 D 777 D 666 Aspirin Thorazine Codeine Retin-A BUT, we don’t know, for example, what she used Aspirin for

CIS 4365: Professor Kirs Slide 77 Entity Relationship Diagrams But we didn’t use the Requires Table !!! It won’t make any difference. Consider the following data: Pat. ID 1234567890 Illness Table Suffers Table Patient Table Name Clara Schumann George Bush Pat. ID 123456789 234567890 Illcode I 3456 I 7654 I 8795 Date 10/01/01 10/29/01 11/07/01 11/22/01 12/14/01 Illcode I 3456 I 7654 I 8795 Description Broken Arm Headache Bad Hair Day Notice That Clara Schumann and George Bush BOTH had Headaches and Bad Hair Days

CIS 4365: Professor Kirs Slide 78 Entity Relationship Diagrams But we didn’t use the Requires Table !!! It won’t make any difference. Consider the following data: Pat. ID 1234567890 Illness Table Suffers Table Patient Table Name Clara Schumann George Bush Pat. ID 123456789 234567890 Illcode I 3456 I 7654 I 8795 Date 10/01/01 10/29/01 11/07/01 11/22/01 12/14/01 Notice That Aspirin was Required for BOTH Headaches and Bad Hair Days Illcode I 3456 I 7654 I 8795 Description Broken Arm Headache Bad Hair Day Requires Table Number Illcode 99999 88888 77777 66666 55555 I 3456 I 7654 I 8795 Drug. Code D 999 D 888 D 777 D 666 D 999 Prescription Table Drug. Code Description D 999 D 888 D 777 D 666 Aspirin Thorazine Codeine Retin-A

CIS 4365: Professor Kirs Slide 79 Entity Relationship Diagrams But we didn’t use the Requires Table !!! It won’t make any difference. Consider the following data: Pat. ID 1234567890 Illness Table Suffers Table Patient Table Name Clara Schumann George Bush We Know that Headaches and Bad Hair Days have been treated with Thorazine, Aspirin, Codeine and Retin-A We Know that George has received Aspirin and Thorazine BUT For What ? ? ? Pat. ID 123456789 234567890 Illcode I 3456 I 7654 I 8795 Date 10/01/01 10/29/01 11/07/01 11/22/01 12/14/01 We Know that George has suffered from Headaches and Bad Hair Days. Uses Table Number 11111 22222 33333 44444 55555 66666 Pat. ID 123456789 234567890 Drug. Code D 999 D 888 D 777 D 666 D 999 D 888 Illcode I 3456 I 7654 I 8795 Description Broken Arm Headache Bad Hair Day Requires Table Number Illcode 99999 88888 77777 66666 55555 I 3456 I 7654 I 8795 Drug. Code D 999 D 888 D 777 D 666 D 999 Prescription Table Drug. Code Description D 999 D 888 D 777 D 666 Aspirin Thorazine Codeine Retin-A

CIS 4365: Professor Kirs Slide 80 Entity Relationship Diagrams How can we link the three entities together ? ? ? We COULD Rearrange the linkages between Relationships: Patient Suffer Where: Given 1 Occurrence of Pat. ID in Patient, there MAY be many Occurrences of Pat. ID in Require, BUT, given 1 Occurrence of Pat. ID in Require, there can be ONLY 1 occurrence of Pat. ID in Patient Illness Require Prescription

CIS 4365: Professor Kirs Slide 81 Entity Relationship Diagrams Note that an additional Foreign Key is needed in Table Require Patient Table Pat. ID Requires Table Name Address Illness Table Number Pat. ID Ill. Code Name Drug. Code Others Phys. ID Prescription Table

Slide 82 CIS 4365: Professor Kirs Entity Relationship Diagrams How Would this Work ? ? ? Consider the same data set: Pat. ID 1234567890 Illness Table Suffers Table Patient Table Pat. ID 123456789 234567890 Name Clara Schumann George Bush Illcode I 3456 I 7654 I 8795 Date 10/01/01 10/29/01 11/07/01 11/22/01 12/14/01 Requires Table We Know: George has Suffered from Headaches and Bad Hair Days. Illcode I 3456 I 7654 I 8795 Description Broken Arm Headache Bad Hair Day Prescription Table Number 99999 88888 Pat. ID 123456789 Ill. Code I 3456 I 7654 Drug. Code D 999 D 888 77777 66666 55555 1234567890 I 8795 I 7654 I 8795 D 777 D 999 D 888 Drug. Code Description D 999 D 888 D 777 D 666 Aspirin Thorazine Codeine Retin-A For his Headache he received Aspirin and for his Bad Hair Days he received Thorazine.

CIS 4365: Professor Kirs Slide 83 Entity Relationship Diagrams What does this mean ? ? ? Initially, we asked the simple questions: • Can a Prescription Exist without an Illness? • Can an Illness Exist without a Patient? The EASY answer is: No way, Jose !!! We now need to ask the question: “Are any entities interdependent upon each other? ? ”

CIS 4365: Professor Kirs Slide 84 Entity Relationship Diagrams In other words, we need to examine whether: • A Patient Exists without an Illness and Prescription. • An Illness Exists without a Patient and Prescription. • A Prescription Exists without a Patient and Illness. These are NOT easy Questions. • • A Patient MUST suffer some Illness An Illness MUST be suffered by (at least) one Patient A Patient NEED NOT require a Prescription An Illness NEED NOT require a Prescription ---- BUT ----A Prescription MUST be associated with BOTH a Patient AND an Illness

CIS 4365: Professor Kirs Slide 85 Entity Relationship Diagrams Therefore, we have a TERNARY relationship (of degree three, meaning there are three entities participating in the relationship) Patient Requires Prescription Illness

CIS 4365: Professor Kirs Slide 86 Entity Relationship Diagrams Maybe we should join EVERY Entity with every other Entity, in case we need to relate them ? ? ? NOT a good idea --- Why ? ? • Space: If we link/join tables together, we MUST do so by adding Foreign Keys to a relation Consider American Express, with 300 Million Customers: IF we add 1 Foreign Key (e. g. , SSN, each entry requiring 9 -bytes) For EACH record we add to the Database, we need: 300, 000 * 9 = 2, 100, 000 Bytes = 2. 1 Gigabytes IF we had 1, 250 Eentities (given American Expresses’ size, that would NOT be unusual), we would require: 2, 100, 000 * 1, 250 = 2, 625, 000, 000 = 26, 250 Terra. Bytes (A hefty Amount)

CIS 4365: Professor Kirs Slide 87 Entity Relationship Diagrams • Complexity: • When we Add a record, We Must make sure to update ALL of the related fields • When we Delete a record, We Must make sure to also delete it in ALL of the related fields • When we Modify a record, We Must make sure to also modify it in ALL of the related fields • When we Create Reports and Queries, We Must make sure to include ALL of the related fields • Utility: • How Often will we need these relationships?

CIS 4365: Professor Kirs Slide 88 Entity Relationship Diagrams Is this our Final ERD ? ? ? Physician Treats Patient Suffer Illness Require NOT quite --- Why Not ? ? Prescription

CIS 4365: Professor Kirs Slide 89 Entity Relationship Diagrams Consider the Associative Entities Suffers and Requires: Suffer Table Pat. ID Requires Table Ill. Code Number Pat. ID Date/Time Ill. Code Others Drug. Code Others So ? ? They are essentially the same (We could merge the two)

CIS 4365: Professor Kirs Slide 90 Entity Relationship Diagrams How ? ? ? Consider the relationship: Name Ill. Code Address Pat. ID Drug. Code Name Date Pat. ID Others Ill. Code Others Patient Drug. Code Treatment Prescription Illness Others The Associative Relationship has been renamed Treatment to more accurately reflect the association

CIS 4365: Professor Kirs Slide 91 Entity Relationship Diagrams What Happened to our unique Identifier Number ? ? ? Do We need it ? ? ? The Composite/Concatenated Primary/Foreign Key(s): TREATMENT (Pat. ID, Ill. Code, Drug. Code, Date, …. . ) SHOULD be sufficient to determine a record

CIS 4365: Professor Kirs Slide 92 Entity Relationship Diagrams In Tabular Form, this would appear as: Patient Table Treatment Table Illness Table Pat. ID Ill. Code Name Address Drug. Code Others Phys. ID Date/Time Others Prescription Table Drug. Code Others

CIS 4365: Professor Kirs Slide 93 Entity Relationship Diagrams Our (Complete to date) ERD would appear as: Physician Notice: Treats Patient Treatment Prescription • We Have One 1: M Relationship • And One M: M: M (Ternary) Relationship What About M: M Relationships ? ? They are very common, but the semantics of our problem do not call for any Illness

CIS 4365: Professor Kirs Slide 94 Entity Relationship Diagrams IF we were also interested in Insurance Payments, and found that: “A Patient may have many Insurance Carriers. ” (We are assuming that an Insurance carrier also has many patients) The Relationship Might Appear as: Given 1 Patient, how many Insurance Carriers ? ? Many No Mandatory ? ? Patient Carry Given 1 Insurance Carrier, how many Patients ? ? Many Mandatory ? ? No Insurance

CIS 4365: Professor Kirs Slide 95 Entity Relationship Diagrams Of Course we know that in a M: M Relationship, we need an Associative Relation: (Notice the Cardinality/Cardinality Constraint Changes) Pat. ID Name Patient Phys. ID Address Pat. ID Carry Insur. ID Others Insur. ID Name Others Insurance

CIS 4365: Professor Kirs Slide 96 Entity Relationship Diagrams Our New (Complete to date) ERD would appear as: Physician Treats Patient Treatment Carry Prescription Insurance Are We Done ? ? (I’m Tired of this) ---- Almost Illness

CIS 4365: Professor Kirs Slide 97 Entity Relationship Diagrams There is only one more semantic consideration: “A Physician Supervises many other Physicians. A Physician is Supervised by another Physician. ” How many Entities are involved ? ? One (Physician) What Type of relationship are we talking about ? ? Unary But How do we link an entity to itself ? ?

CIS 4365: Professor Kirs Slide 98 Entity Relationship Diagrams We Need One additional Field in Table Physician: Physician Table Phys. ID Name Specialty Physician Table Where Supervisor is, in fact, Phys. ID --- OR --- Phys. ID Name Specialty Supervisor Phys. ID Address Note: We COULD NOT call the field Phys. ID

CIS 4365: Professor Kirs Slide 99 Entity Relationship Diagrams How would this Actually Work ? ? Physician Table Phys. ID Name Supervisor 9876543210 7654321098 5432109876 321098765 Smith, Mary Seuss, Melvin Strangelove Demento Zhivago Kervorkian Dre 987654321 432109876 987654321 Does this mean we JOIN a --- YES !!! --table to itself ? ? ? Mary Smith Supervises Drs. Demento, Zhivago, and Dre Dr. Kervorkian Supervises Drs. Seuss and Strangelove

CIS 4365: Professor Kirs Slide 100 Entity Relationship Diagrams But Does A Physician Supervisor Supervise Him-/Herself ? ? Shouldn’t it be --Physician Table Phys. ID Name 9876543210 7654321098 5432109876 321098765 Smith, Mary Seuss, Melvin Strangelove Demento Zhivago Kervorkian Dre Supervisor Mary Smith Supervises Drs. Demento, Zhivago, and Dre 43210987654321 987654321 As the Designer, it is your call Dr. Kervorkian Supervises Drs. Seuss and Strangelove

CIS 4365: Professor Kirs Slide 101 Entity Relationship Diagrams Our Complete (Finally) ERD would appear as: Physician Supervises Treats Patient Treatment Carry Prescription • A 1: M Unary Insurance Relationship • A M: M Binary Relationship • A M: M: M Ternary Relationship Illness

CIS 4365: Professor Kirs Slide 102 Entity Relationship Diagrams ERD Summary Databases are easy Given the available DBMSs, if you put a chimpanzee in front of a terminal, s/he will be able to construct a working database Databases are difficult Construction of an effective and efficient database requires considerable knowledge and skills

CIS 4365: Professor Kirs Slide 103 Entity Relationship Diagrams