1 / 49

Database Design

demont
Download Presentation

Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. 1 Database Design Sections 4 & 5 Subtype, Supertype, Mutually exclusive, non-transferability, transferable, 1:1, 1:M, M:M, Redundant, Intersection entity, Barred relationship, CRUD analysis

    2. 2 DJs on Demand – Partial ERD Go through discuss conventions: Caps for entities, # UID o optional * mandatory ------- line _____________ line Crow’s feet/single toe indicate cardinality M:M relationships resolve later Not final versionGo through discuss conventions: Caps for entities, # UID o optional * mandatory ------- line _____________ line Crow’s feet/single toe indicate cardinality M:M relationships resolve later Not final version

    3. 3 Reserved Words A reserved work is one that has a specific meaning and function in the computer system, language, or database. Examples: NULL DATE DISTINCT To avoid confusion, it is good to avoid these words. Instead of ‘date’ use ‘birth date’, ‘date of sale’, ‘transaction date’To avoid confusion, it is good to avoid these words. Instead of ‘date’ use ‘birth date’, ‘date of sale’, ‘transaction date’

    4. 4 Video Store – example next slide Identify business rules Construct ERD Class activity Use example like Block Busters, or NetflexUse example like Block Busters, or Netflex

    5. 5 Video Store example Read the given business scenario and walk through the steps below. 1. Examine the nouns. Are they things of significance? 2. Name each entity. 3. Is there information of interest about the entity that the business needs to hold? 4. Is each instance of the entity uniquely identifiable? Which attribute or attributes could serve as its UID? 5. Write a description of the entity. 6. Diagram each entity and a few of its attributes.  Remember, entity names are always singular. 7. Identify the relationships and draw them on the ERD. Entities: MOVIE CUSTOMER ACTOR TAPE/DVD Entities: MOVIE CUSTOMER ACTOR TAPE/DVD

    6. 6 Video ERD don’t need to define all entities at beginning not all attributes need to be defined at first start with big picture Have customer sign off at each stagedon’t need to define all entities at beginning not all attributes need to be defined at first start with big picture Have customer sign off at each stage

    7. 7 Use Source Documents Source document assist in identifying entities, attributes, and relationships

    8. 8 Subtype A subtype: inherits all attributes of the supertype inherits all relationships of the supertype usually has its own attributes or relationships is drawn within the supertype never exists alone may have subtypes of its own is also known as a "subentity“ Supertype is the entity and then the subtypes lie within the supertype.Supertype is the entity and then the subtypes lie within the supertype.

    9. 9 Subtype & Supertype Supertype (entity) can be subdivided into Subtypes Example: Discuss example: ANIMAL - vertebrate - invertebrate EXAM - Quiz - Midterm - Final - Exam_1 Theory: can be exclusivity (can be one and only one subtype) Animal can be only vertebrate or invertebrate subtype has own attributes and relationships drawn within supertype may have subtypes of itself Subtype inherits a attributes and relationships of supertype Discuss example: ANIMAL - vertebrate - invertebrate EXAM - Quiz - Midterm - Final - Exam_1 Theory: can be exclusivity (can be one and only one subtype) Animal can be only vertebrate or invertebrate subtype has own attributes and relationships drawn within supertype may have subtypes of itself Subtype inherits a attributes and relationships of supertype

    10. 10 Attributes Supertype can have attributes at supertype level Attributes at supertype level are for all subtypes Subtypes can have own attributes for only that attribute

    11. 11 Example Every QUIZ, MIDTERM, or FINAL is an EXAM Every EXAM is either a QUIZ, a MIDTERM, or a FINALEvery QUIZ, MIDTERM, or FINAL is an EXAM Every EXAM is either a QUIZ, a MIDTERM, or a FINAL

    12. 12 Global Fast Food Employees STAFF is an entity with supertypes What are the attributes of STAFF? #id *first name *last name *DOB *salary What subtypes exist in STAFF? ORDER TAKER *overtime COOK *training OTHER Are there any attributes in any of the subtypes? STAFF #id *first name *last name *DOB *salary Subtypes: ORDER TAKER *overtime COOK *training OTHERSTAFF #id *first name *last name *DOB *salary Subtypes: ORDER TAKER *overtime COOK *training OTHER

    13. 13 Example: an incorrect subtypes in the illustration. AUTOMOBILE subtypes are not mutually exclusive AUTOMOBILE possible solution BUILDING subtypes have problem of not being exhaustive, should be minimum of 2 types AUTOMOBILE SUDAN and/or SUV can have 4-doors solution: remove ‘4-door’ as type and make ‘number of doors’ an attribute of Supertype Add another non-overlapping subtype or an ‘OTHER’ subtype BUILDING must be more type of building than just ‘HOUSE’ possible others, CHURCH, SCHOOL, CASTLE etc.AUTOMOBILE SUDAN and/or SUV can have 4-doors solution: remove ‘4-door’ as type and make ‘number of doors’ an attribute of Supertype Add another non-overlapping subtype or an ‘OTHER’ subtype BUILDING must be more type of building than just ‘HOUSE’ possible others, CHURCH, SCHOOL, CASTLE etc.

    14. 14 Subtype Needs Exhaustive: Every instance of the supertype is also an instance of one of the subtypes. Treating all parts or aspects without omissions. 2 or more items Mutually exclusive: Every instance of the supertype is of one and only one subtype. A relationship that presents choices which are unable to be true at the same time. Discuss how these apply to the previous example.Discuss how these apply to the previous example.

    15. 15 Correctly identify subtypes When considering supertypes and subtypes, you can use three questions to see if the subtype is correctly identified: Is this subtype a kind of supertype? Have I covered all possible cases? (exhaustive) Does the example fit into one and only one subtype? (mutually exclusive)

    16. 16 Nested subtypes

    17. 17 Documenting Business Rules Source documents assist in identiying: entities attributes relationships Some business rules are “implemented by programming” “implemented by programming” means that once the database is created, a programmer would write code that would enforce a business rule.“implemented by programming” means that once the database is created, a programmer would write code that would enforce a business rule.

    18. 18 Business Rules Recognize if Business rule is implemented by the data model or by programming Example: Only an employee with a title of manager can manage other employees The event planner must contact the customer before the DJ can be assigned Business rules can be changed as society changes or business leaders feel change is needed Define some business rules for Cerritos College Prerequisite must be met before a student takes course requiring prerequisite. Classes may only be taught by faculty. Each shift must be supervised by a manager Students must pay fees within 7 days or be dropped from the class.Define some business rules for Cerritos College Prerequisite must be met before a student takes course requiring prerequisite. Classes may only be taught by faculty. Each shift must be supervised by a manager Students must pay fees within 7 days or be dropped from the class.

    19. 19 Business rule types Structural Must be entered at time of initial data entry Example: When ORDER entered a staff id must be entered Does not require code written to enforce Procedural Work flow related Programmed validation is procedural constraint Generally need to write code Example: Commission can not be > 25% of salary Business rule: A formalized statement of the usual, customary, or generalized course of action or behavior for a business. Structural business rule: A structural business rule indicates the types of information to be stored and how the information elelments interrelate Procedural busines rule: A business rule that is workflow or business process related. (e.g., A has to happen before B, and then C has to happen at the same time as D.) this is also called a process business rule.Business rule: A formalized statement of the usual, customary, or generalized course of action or behavior for a business. Structural business rule: A structural business rule indicates the types of information to be stored and how the information elelments interrelate Procedural busines rule: A business rule that is workflow or business process related. (e.g., A has to happen before B, and then C has to happen at the same time as D.) this is also called a process business rule.

    20. 20 Structural Business Rule examples:

    21. 21 Procedural Business Rule example: 4.2.7 Ask students the question: What procedural business rule is evident from the animation? Answer: The project manager first works with the client to confirm the event. Then he assigns an event manager and a DJ to the event. Discussion: How do you represent this in the ER diagram? Can you? Answer: No, this is a rule that will have to be implemented by a computer program. Visualize a system where the partners can look up events and update them. The program could allow only the project manager to enter a new event and then assign the partners who will be working on it. Once the assignment is made, the system could send out an email notification to the event manager and the DJ, giving them details about the event, the customer contact information, etc.4.2.7 Ask students the question: What procedural business rule is evident from the animation? Answer: The project manager first works with the client to confirm the event. Then he assigns an event manager and a DJ to the event. Discussion: How do you represent this in the ER diagram? Can you? Answer: No, this is a rule that will have to be implemented by a computer program. Visualize a system where the partners can look up events and update them. The program could allow only the project manager to enter a new event and then assign the partners who will be working on it. Once the assignment is made, the system could send out an email notification to the event manager and the DJ, giving them details about the event, the customer contact information, etc.

    22. 22 Discussion Students must have studied algebra and geometry in order to sign up for trigonometry. Could you represent this in the ERD? • How would you implement this with programming? • If the student had taken the subjects, can you think of an additional business rule that a school may want in this scenario? 4.2.8 Could you represent this in the ERD? Answer: No. How would you implement this with programming? Answer: When a student tries to sign up online for the trigonometry class, a piece of code would “check” to see if that student had taken algebra and geometry. If the student had taken the subjects, can you think of an additional business rule that a school may want in this scenario? Answer: Student achieved a passing grade.4.2.8 Could you represent this in the ERD? Answer: No. How would you implement this with programming? Answer: When a student tries to sign up online for the trigonometry class, a piece of code would “check” to see if that student had taken algebra and geometry. If the student had taken the subjects, can you think of an additional business rule that a school may want in this scenario? Answer: Student achieved a passing grade.

    23. 23 Each entity has Name Optionality Cardinality Transferability Example on next slide.

    24. 24 Relationship Transferability Slide 4: Tell Me /Show Me – Let's review a simple relationship between … Transferable: Property of a relationship between A and B, where an instance of A is related to an instance of B, and the association can be moved to another instance of B Reiterate that the relationship name must work from both perspectives. Optionality Can you have a TYPE that does not classify any SONG? (Yes) Must every SONG have a TYPE? (Yes) Cardinality How many SONGs can be classified under one TYPE? (One or more) How many TYPEs can a SONG have? (Just one) Note: Students may argue that a SONG can be classified by many TYPEs. Agree, but tell them that the assumption for the DJ model is that although many TYPEs can apply to a SONG, they want to track the main TYPE. Transferability Can a SONG be changed from one TYPE to another TYPE? (Most likely the answer is yes, unless they have rigid rules about TYPE. Explain that this demonstrates a transferable relationship.) Slide 4: Tell Me /Show Me – Let's review a simple relationship between … Transferable: Property of a relationship between A and B, where an instance of A is related to an instance of B, and the association can be moved to another instance of B Reiterate that the relationship name must work from both perspectives. Optionality Can you have a TYPE that does not classify any SONG? (Yes) Must every SONG have a TYPE? (Yes) Cardinality How many SONGs can be classified under one TYPE? (One or more) How many TYPEs can a SONG have? (Just one) Note: Students may argue that a SONG can be classified by many TYPEs. Agree, but tell them that the assumption for the DJ model is that although many TYPEs can apply to a SONG, they want to track the main TYPE. Transferability Can a SONG be changed from one TYPE to another TYPE? (Most likely the answer is yes, unless they have rigid rules about TYPE. Explain that this demonstrates a transferable relationship.)

    25. 25 Relationship transferability Rules established for the purpose such as safety, efficiency, or increased profit Example: Airline tickets can be transferable or non-transferable? Non-transferable means also not updatable symbol used to denote non-transferable blood type is non-transferable STUDENT can not transfer the RECEIPT issued to them for registration. Therefore STUDENT can’t be transferred to another RECIEPTSTUDENT can not transfer the RECEIPT issued to them for registration. Therefore STUDENT can’t be transferred to another RECIEPT

    26. 26 Non-transferable relationship Orders are non-transferable Resolution would be to cancel order and create new order Examples: Airline TICKET belong to one and only one PASSENGER What about CUSTOMERS and CREDIT CARD ACCOUNT PRIVILEGES are these transferable? Is the relationship between PATIENT and PRESCRIPTION transferable or not? Example: Poem and author You can’t transfer the author from one poem to another 5.1.5 Slide 5: Tell Me /Show Me – Nontransferability of Relationships Nontransferable: Property of a relationship where an instance of A is related to an instance of B, and the association cannot be moved to another instance of B Nontransferable relationships are important to note because they usually reflect a business rule and will have implications when the ERD is implemented as a database -- the foreign key column cannot be updated. Assess the understanding of your students as you decide whether or not to discuss this with them. Theoretically it doesn’t matter at which end of the relationship you draw the diamond, but we always draw them at the “many” (crows-feet) end.Examples: Airline TICKET belong to one and only one PASSENGER What about CUSTOMERS and CREDIT CARD ACCOUNT PRIVILEGES are these transferable? Is the relationship between PATIENT and PRESCRIPTION transferable or not? Example: Poem and author You can’t transfer the author from one poem to another 5.1.5 Slide 5: Tell Me /Show Me – Nontransferability of Relationships Nontransferable: Property of a relationship where an instance of A is related to an instance of B, and the association cannot be moved to another instance of B Nontransferable relationships are important to note because they usually reflect a business rule and will have implications when the ERD is implemented as a database -- the foreign key column cannot be updated. Assess the understanding of your students as you decide whether or not to discuss this with them. Theoretically it doesn’t matter at which end of the relationship you draw the diamond, but we always draw them at the “many” (crows-feet) end.

    27. 27 Example 5.1 exercise 1 a Each town may be the birthplace of many people. Each person must be born in one and only one town.

    28. 28 Relationship types One to One (1:1) One to Many (1:M) Many to Many (M:M)

    29. 29 One to One relationship 5.2.6 Slide 6: Tell Me /Show Me – One-to-One(1:1) Relationships One- to- one: A relationship where each record in Table A can be related to one, and only one, record in Table B, and each record in Table B relates to one, and only one, record in Table A The slide example is interesting. Alternatively, TEACHER and STUDENT could have been modeled as subtypes of PERSON, unless a PERSON can be both a TEACHER and a STUDENT at the same time.5.2.6 Slide 6: Tell Me /Show Me – One-to-One(1:1) Relationships One- to- one: A relationship where each record in Table A can be related to one, and only one, record in Table B, and each record in Table B relates to one, and only one, record in Table A The slide example is interesting. Alternatively, TEACHER and STUDENT could have been modeled as subtypes of PERSON, unless a PERSON can be both a TEACHER and a STUDENT at the same time.

    30. 30 One to One relationship Usually few of these relationships Often mandatory at one side If mandatory at both ends be aware, it might be a error. Possible should be only one entity not two

    31. 31 One to One relationship 5.2.7 Slide 7: Tell Me /Show Me – 1:1 relationships (of all three variations) Mandatory 1:1 A 1:1 relationship, mandatory at both ends, tightly connects two entities: when you create an instance of one entity, there must be exactly one dedicated instance for the other simultaneously; for example, entity PERSON and entity BIRTH. Each PERSON must be the result of one and only one BIRTH. Each BIRTH must result in one and only one PERSON. This leads to the question why you want to make a distinction between the two entities anyway. The only acceptable answer is: only if there is a business need, such as keeping birth records separate from person records. Note that someone may point out that the above relationship does not apply to twins, triplets, etc. Point out that those are considered multiple births (one birth per baby!).5.2.7 Slide 7: Tell Me /Show Me – 1:1 relationships (of all three variations) Mandatory 1:1 A 1:1 relationship, mandatory at both ends, tightly connects two entities: when you create an instance of one entity, there must be exactly one dedicated instance for the other simultaneously; for example, entity PERSON and entity BIRTH. Each PERSON must be the result of one and only one BIRTH. Each BIRTH must result in one and only one PERSON. This leads to the question why you want to make a distinction between the two entities anyway. The only acceptable answer is: only if there is a business need, such as keeping birth records separate from person records. Note that someone may point out that the above relationship does not apply to twins, triplets, etc. Point out that those are considered multiple births (one birth per baby!).

    32. 32 One to Many relationship 5.2.4 Slide 4: Tell Me /Show Me – One-to-Many (1:M) Relationships One- to- many (1:M): A relationship where a single record in Table A can be related to one or more records in Table B, but a single record in Table B can only be related to one record in Table A Note that the word “Many” can mean one-or-more or zero-or-more, depending on the optionality. Emphasize that “Many” does not mean “two-or-more” ! The example given (mandatory on the many side, optional on the one side) is the most common. However, you may want to discuss other variations briefly, or just have them for your own background knowledge: Mandatory at both ends: This type of relationship typically models entities that cannot exist without each other. This usually represents an ideal situation (we cannot have ORDER ITEMs without ORDERs!). This is hard to implement in the physical database because it causes the “chicken and egg” problem (explain in detail). Mandatory on the one side, optional on the many side: This is rarely used. You will see it only when the relationship expresses that an entity instance exists only when it is a nonempty set, and where the elements of the set can exist independently. In the example below, a MUSICIAN may be part of one BAND. According to the model, a BAND is of no interest if it is empty. How can you have a BAND without MUSICIANs?5.2.4 Slide 4: Tell Me /Show Me – One-to-Many (1:M) Relationships One- to- many (1:M): A relationship where a single record in Table A can be related to one or more records in Table B, but a single record in Table B can only be related to one record in Table A Note that the word “Many” can mean one-or-more or zero-or-more, depending on the optionality. Emphasize that “Many” does not mean “two-or-more” ! The example given (mandatory on the many side, optional on the one side) is the most common. However, you may want to discuss other variations briefly, or just have them for your own background knowledge: Mandatory at both ends: This type of relationship typically models entities that cannot exist without each other. This usually represents an ideal situation (we cannot have ORDER ITEMs without ORDERs!). This is hard to implement in the physical database because it causes the “chicken and egg” problem (explain in detail). Mandatory on the one side, optional on the many side: This is rarely used. You will see it only when the relationship expresses that an entity instance exists only when it is a nonempty set, and where the elements of the set can exist independently. In the example below, a MUSICIAN may be part of one BAND. According to the model, a BAND is of no interest if it is empty. How can you have a BAND without MUSICIANs?

    33. 33 One to Many Most common type are mandatory on one side and optional on the other Mandatory at both ends models entities that cannot exist without each other Each PERSON must be the result of one and only one BIRTH. Each BIRTH must result in one and only one PERSON.Each PERSON must be the result of one and only one BIRTH. Each BIRTH must result in one and only one PERSON.

    34. 34 One to Many example

    35. 35 Many to Many relationship 5.2.5 Slide 5: Tell Me /Show Me – Many-to-Many (M:M) Relationships Many- to- many (M:M): A relationship in which many records in one table match many records in another table Remind students that we have seen examples of these already. In the next lesson, we will learn to resolve them.5.2.5 Slide 5: Tell Me /Show Me – Many-to-Many (M:M) Relationships Many- to- many (M:M): A relationship in which many records in one table match many records in another table Remind students that we have seen examples of these already. In the next lesson, we will learn to resolve them.

    36. 36 Many to Many Often common in initial version of ERD Later stage of design converted to other type M:M relationships STUDENTs to COURSEs PRODUCTs to PARTsM:M relationships STUDENTs to COURSEs PRODUCTs to PARTs

    37. 37 Redundant relationship Redundant relationship is one that can be derived from another relationship in the model Attributes1 Attribute 2 Derived field Hours rate pay Units for a course grade GPA, Total units 5.2.8 Slide 8: Tell Me /Show Me – Redundant Relationships Redundancy: Something that is unnecessarily repetitive, the state of being unnecessarily repetitive On the left side, if a person lives in town A, which is in country B, then we can conclude that the person lives in country B. However, on the right side, the relationship is nonderivable : “born in/the birthplace of.” Just because a person lives in town A, which is in country B, does not automatically mean that the person was born in country B.Attributes1 Attribute 2 Derived field Hours rate pay Units for a course grade GPA, Total units 5.2.8 Slide 8: Tell Me /Show Me – Redundant Relationships Redundancy: Something that is unnecessarily repetitive, the state of being unnecessarily repetitive On the left side, if a person lives in town A, which is in country B, then we can conclude that the person lives in country B. However, on the right side, the relationship is nonderivable : “born in/the birthplace of.” Just because a person lives in town A, which is in country B, does not automatically mean that the person was born in country B.

    38. 38 Many to Many Relationship Resolution All M:M relationships must be changed to a set of 1:M and M:1 relationship Insert a transition entity between Occurs when you go from conceptual model to design model

    39. 39 Example: M:M with Intersection Entity Registration is an intersection entity The intersection entity gets the many end of the relationship when you insert it in between a M:M relationship. Slide 6: Tell Me /Show Me – An intersection entity Intersection entity: The product of the resolution of a many to many relationship Explain why the optional M:M relationship has become mandatory at the Many end of the M:1 relationships.Registration is an intersection entity The intersection entity gets the many end of the relationship when you insert it in between a M:M relationship. Slide 6: Tell Me /Show Me – An intersection entity Intersection entity: The product of the resolution of a many to many relationship Explain why the optional M:M relationship has become mandatory at the Many end of the M:1 relationships.

    40. 40 Resolving M:M with Intersection Create an intersection entity If you can’t find a good intersection entity create one by concatenate two entities together

    41. 41 Example Note Barred relationships. Does not need to be a barred relationship also. Barred relationship is part of a unique ID (UID) #unique need to barred Never put Foreign Key in at Conceptual Level. Add then at Logical level. Makes a Unique composite Key for intersection EntityNote Barred relationships. Does not need to be a barred relationship also. Barred relationship is part of a unique ID (UID) #unique need to barred Never put Foreign Key in at Conceptual Level. Add then at Logical level. Makes a Unique composite Key for intersection Entity

    42. 42 CRUD analysis Create ER models that reflect all business rules gathered during the interview process Identify the create, retrieve, update, and delete (CRUD) requirements of the business Validate your ER model by performing a CRUD analysis CRUD analysis ties data modeling to business functions. Although functional modeling – what operations are carried out on the data, not just what data is modeled - is not covered in this course, students do need to realize that the business functions and processes that the client describes will operate on the data. CRUD analysis checks that all the appropriate operations are possible. This is a good tool to check for completeness and proper scope of the data model.CRUD analysis ties data modeling to business functions. Although functional modeling – what operations are carried out on the data, not just what data is modeled - is not covered in this course, students do need to realize that the business functions and processes that the client describes will operate on the data. CRUD analysis checks that all the appropriate operations are possible. This is a good tool to check for completeness and proper scope of the data model.

    43. 43 CRUD requirements CRUD analysis will help you: Translate between the consultant and the client. Provide a guideline for the DBA who will eventually build the database. Check that you haven’t missed any entities or relationships in your data model. Check to make sure that you haven’t modeled anything that the business does not require. CRUD analysis ties data modeling to business functions. Although functional modeling – what operations are carried out on the data, not just what data is modeled - is not covered in this course, students do need to realize that the business functions and processes that the client describes will operate on the data. CRUD analysis checks that all the appropriate operations are possible. This is a good tool to check for completeness and proper scope of the data model.CRUD analysis ties data modeling to business functions. Although functional modeling – what operations are carried out on the data, not just what data is modeled - is not covered in this course, students do need to realize that the business functions and processes that the client describes will operate on the data. CRUD analysis checks that all the appropriate operations are possible. This is a good tool to check for completeness and proper scope of the data model.

    44. 44 What is CRUD? CRUD is an acronym for create, retrieve, update, delete. Check for completeness and accuracy. Slide 4: Tell Me /Show Me – CRUD Analysis CRUD analysis: The practice of checking a data model for create, retrieve, update and delete functions that the business requires Functions: Used to perform calculations on data, modify individual data items, manipulate output for groups of rows, format dates and numbers for display, convert column data types Remind them that a client often describes business functions as part of the scenario. For example: “Whenever we get a new customer, we take down basic information (name, address, email) and assign an ID.” (CREATE) “We’d like to print out a list of songs to be played at each event.” (RETRIEVE) “The event manager reserves the location and may do a site visit. Then she notes down the status and date of each job.” (UPDATE) “A number of our customers were small companies that were hit hard by the recession. They went out of business. We deleted them from our current records.” (DELETE) An alternative acronym for CRUD is BREAD: Browse, Enter, Alter, Delete.Slide 4: Tell Me /Show Me – CRUD Analysis CRUD analysis: The practice of checking a data model for create, retrieve, update and delete functions that the business requires Functions: Used to perform calculations on data, modify individual data items, manipulate output for groups of rows, format dates and numbers for display, convert column data types Remind them that a client often describes business functions as part of the scenario. For example: “Whenever we get a new customer, we take down basic information (name, address, email) and assign an ID.” (CREATE) “We’d like to print out a list of songs to be played at each event.” (RETRIEVE) “The event manager reserves the location and may do a site visit. Then she notes down the status and date of each job.” (UPDATE) “A number of our customers were small companies that were hit hard by the recession. They went out of business. We deleted them from our current records.” (DELETE) An alternative acronym for CRUD is BREAD: Browse, Enter, Alter, Delete.

    45. 45 Create keywords Look for these words when you interview the client. INPUT ENTER LOAD IMPORT RECORD CREATE Another example you can use is from the DJ business scenario: “Each event is tied to a customer. A customer can ask us to work at many events.” Ask students: Does the ERD have a relationship that reflects this? Answer: Yes, it’s the relationship between CUSTOMER and EVENT and the relationship between JOB ASSIGNMENT and PARTNER.Another example you can use is from the DJ business scenario: “Each event is tied to a customer. A customer can ask us to work at many events.” Ask students: Does the ERD have a relationship that reflects this? Answer: Yes, it’s the relationship between CUSTOMER and EVENT and the relationship between JOB ASSIGNMENT and PARTNER.

    46. 46 Retrieve keywords Look for: VIEW REPORT BRING UP PRINT FIND READ LOOK UP

    47. 47 Update keywords Look for: CHANGE MODIFY ALTER UPDATE Slide 7: Tell Me /Show Me – Update Operation Here’s another DJ example that you can use: “We don’t allow customers to transfer ownership of an event to another customer.” This points to a nontransferable relationship, which will disallow updates to certain attributes in the associated entity. In this case, your data model must show a nontransferable relationship between CUSTOMER and EVENT.Slide 7: Tell Me /Show Me – Update Operation Here’s another DJ example that you can use: “We don’t allow customers to transfer ownership of an event to another customer.” This points to a nontransferable relationship, which will disallow updates to certain attributes in the associated entity. In this case, your data model must show a nontransferable relationship between CUSTOMER and EVENT.

    48. 48 Delete keywords Look for: DISCARD REMOVE TRASH PURGE DELETE Slide 8: Tell Me /Show Me – Delete Operation Obsolete: No longer in use You may want to mention that businesses often don’t delete data -- they just flag it as obsolete, or they keep an archive of it. One of the rare occasions that they do want to delete a record from the database is if it’s entered erroneously.Slide 8: Tell Me /Show Me – Delete Operation Obsolete: No longer in use You may want to mention that businesses often don’t delete data -- they just flag it as obsolete, or they keep an archive of it. One of the rare occasions that they do want to delete a record from the database is if it’s entered erroneously.

    49. 49 CRUD analysis If you have a business function that has no entity to CRUD against, then your data model may be incomplete. Similarly, if you have entities in your ERD that are not touched by any CRUD (there is no business function that creates, retrieves, updates, or deletes from it), then you may not need that entity in your model.

More Related