DD_1_1_Solution.docx



CIS 9002 – Oracle Database Design NAME: ______________________ DD Lab: Section 2Download this document, save this file using the filename: your_name_CIS9002_ DD Lab Section 2.docx Insert your answers into this document. Submit the assignment via CanvasCheating/plagiarizing: See LPC Academic Honesty statement: “Cheating is defined as fraud, deceit, or dishonesty in an academic assignment. It may involve: Copying or attempting to copy from others during an examination or for an assignment;” Students need to become familiar with the Student Code of Conduct, especially with regards to cheating/plagiarizing: Page 32 of the LPC Academic Catalog states:Academic Honesty Statement: Submitting the whole of another’s work as one’s own (see the definition of “cheating” above: this includes submitting another student’s paper or a paper obtained from a commercial term paper service as one’s own);Duplicate Use of Written Work: This form of academic dishonesty includes: multiple students submitting identical or substantially similar work for academic credit or a student submitting identical or substantially similar work to multiple courses without the permission of the instructors. Faculty members will report suspected instances of academic dishonesty to the Dean of Student Services.524510086360005247005850900052451008636000These assignments originate from the section Student Resources, Practice Activities, I have given you all the questions, some partial solutions and questions for you to answer and submit for grading. Look for ____________ questions to answer.Database Design 2-1: Conceptual & Physical Models Practice Activities and some SolutionsVocabulary Directions: Identify the vocabulary word for each definition below. Physical model A design for an object (a car, a house, a database, etc.) which includes implementation details such as size, volume, weight, etc.) Conceptual model A data model, usually represented by an entity relationship diagram Data A collection of facts from which conclusions may be drawn Data modeling The process of capturing the important concepts and rules that shape a business and depicting them visually on a conceptual model Try It / Solve It You will be working in pairs for this activity. One student describes his/her “dream house” while the other student attempts to draw it. You can discuss specific details, but the student describing the house is not allowed to see what is being drawn until after time is called.After sharing your drawing, describe the importance of accurately describing information requirements. Solution: The objective of the dream-house activity is for students to demonstrate precise communication as a means to facilitate discussion and prevent mistakes and misunderstandings. Working in pairs, one student describes his/her “dream house” while the other student attempts to draw it. The pairs can discuss specific details, but the student describing the house is not allowed to see what is being drawn until after time is called. Collect student drawings and tape to the board/wall. Ask volunteer student pairs to describe the discrepancies between what they thought was being described and what was actually drawn. Solicit student ideas on how they would improve the communication process if they were to do the same activity again. Possible answers may include: Describe shapes and colors. Describe relative sizes (bigger than a hut, smaller than a hotel, etc.) Be specific (2 doors, 3 stories, 5 windows, etc.). Make comparisons to known things (similar to Mary’s house, like a castle, etc.). Other ways to test for understanding: Make sure that the students understand the difference between a conceptual and a physical data model by presenting examples and asking them to identify them as conceptual or physical: Blueprint of a house versus a house Sketch of an outfit/dress/suit versus the real thing Recipe for a cake versus the cake itself If you can show actual samples of the above (house plans, drawing of a dress/suit, printout of a recipe), that would drive home the point even better. Feel free to supplement with other examples that are more familiar or accessible to you. Review the scenario below. Identify the conceptual model and the physical model from the scenario. Zoe was about to go into a store to purchase drinks for the birthday party scheduled for that evening. Zoe knows that she needs drinks for 48 people and is expecting the store to accept a check for payment and to provide her with some assistance carrying the product to her car. Zoe wants to have carbonated drinks, non-carbonated drinks, and sugar free drinks. She is expecting to purchase eight six-packs. Zoe enters the store and discovers the entire drink distribution system is automated. She also discovers that the drinks come in varying package sizes and that she must choose the correct vending option for the products to be disbursed. Drinks are packaged in four-packs, sixpacks, and ten-packs. Solution: Conceptual model – ____________________________________________________________________Physical implementation – ___________________________________________________________________Provide five reasons for creating a conceptual data model. Answers will vary but can include any of the following: Describe exactly the information needs of the business Facilitate discussion Prevent mistakes, misunderstandings Form important “ideal system” documentation Form a sound basis for physical database design Document the processes of the business (this is also known as the “business rules”) List two examples of conceptual models and physical models. Answers will vary but should illustrate the understanding that the conceptual model is the thought and the physical model includes the implementation details of the thought.Database Design 2-2: Entities, Instances, Attributes and Identifiers Practice Activities and some SolutionsVocabulary Directions: Identify the vocabulary word for each definition below. Mandatory Required Nontangible/ Intangible Incapable of being perceived by the senses Unique Identifier (UID) Any combination of attributes and/or relationships that serves, in all cases, to uniquely identify an instance of an entity Instance An occurrence or example of an entity Attribute A characteristic; something that describes, quantifies, or specifies an entity Optional Not required Entity A named thing or category of things that is significant to the business and about which data must be known Null A value that is unavailable, unassigned, unknown, or empty; however, it is neither a zero nor a space Volatile Highly changeable Tangible Perceptible to the senses, especially the sense of touch Datatype A classification identifying one of various types of data, stating the possible values for that type, the operations that can be done on that type, and the way the values of that type are stored Single Valued Can only have one value at any point for each instance in the entity Try It / Solve It Consider the entity STUDENT. You have many students in a class but each individual student is a unique instance of the STUDENT entity. The next exercise will demonstrate this. Solution: Say, “STUDENTS please stand up.” Have everyone sit down and then say, “Julie Miller (or some specific student in your class), please stand up.” Then explain that while they are all students, Julie Miller is a specific instance of STUDENT. Have students write on a 3 x 5 note card the answers to each of the traits below, or fill out a preprinted sheet of paper that includes traits similar to the following: Month of birthday Color of shirt/blouse Shoe color Color of backpack/school bag Glasses/no glasses Year in school Eye color Has a pet cat/dog/bird Right/left-handed Number of brothers and sisters Note: The specific traits that you choose to use can vary as long as there are six or more different traits. You will want at least six traits to uniquely identify a student. Collect the card/paper from each student. Ask all students to stand up. Randomly select one student’s card/paper. Ask a question for each trait that the student recorded. You are trying to eliminate other students until you are left with only the student whose card you have. This student is your INSTANCE. For example: Will all those students who were born before or after (the month listed on the selected card) please sit down? Will all those students who are not wearing shoes of color (the color on selected card) please sit down? Will all those students who (wear/not wear) glasses please sit down? Will all those students who do not have eye color (eye color on selected card) please sit down? Will all those students who are (left/right)-handed please sit down? Will all those students who have fewer or more siblings than (number of brothers and sisters) please sit down? Make sure students can distinguish between entities and instances. Ask them how you can distinguish one student from another. You can ask them to provide answers now (name, gender, hair color, etc.), but tell them that these are attributes and will be discussed further later. Ask students which traits would make good attributes to identify someone: Does the month of your birthday ever change? Does your shoe color change? What other traits could be added if shoe color and glasses are eliminated? Think about the last time you went to get a haircut, permanent, manicure, or other service from a barbershop or hair salon. Discuss the business of a barbershop or hair salon. Name the entities that are the “main things” about this business. Give examples of instances for each entity. Solution: Customer – Vishal Gupta Services – color, cut, permanent wave, manicure, shampoo Employees/technicians – stylist, shampoo person, cashier, receptionist Products – shampoo, color, styling gel, lotion Tools – hair dryer, scissors Note: In naming entities, the class may also be including attributes. If they do this, correct them, and identify them as attributes, and tell them that we will discuss the difference more in the next lesson. The goal of this activity is to learn to make a distinction between an entity, an attribute, and an instance of an entity. Identify each concept as an entity, attribute, or instance. If you determine that the concept is an entity, provide an instance of it in column 3. If you determine that the concept is an attribute or an instance, provide an entity for it in column 3. In the last three rows, provide a concept that fits. Concept Entity? Attribute? Instance? Example Instance or Entity Vehicle Diet Cola Price Customer number Green Entity Cookie Attribute Cookie Instance Cookie Solution: In order to guide students in the direction of the given solution, you may want to provide business contexts for each concept, such as: Vehicle (a car-rental agency) Diet Coke (a restaurant) Price (a department store) Customer number (an online catalog) Green (a botanical garden) Cookie (a restaurant) Alternative answers are considered acceptable if the student can argue successfully and provide a business context or rule for it. The goal of this practice is to recognize attributes for an entity. These three entities—SONG, EVENT, and CUSTOMER—play a role in a DJ business and are listed as the first three column headings in the table below. The fourth column contains a list of attributes. Use an X or a check mark to indicate that the attribute could belong to one or more of the entities listed. For example, could Title be an attribute for Song, for Event, and/or for Customer? SONG EVENT CUSTOMER Title Description Venue First Name Phone Number Release date Last Name Type Email address These three entities—SONG, EVENT, and CUSTOMER—play a role in a DJ business and are listed as the first three column headings in the table below. The fourth column contains a list of attributes. Use an X or a check mark to indicate that the attribute could belong to one or more of the entities listed. For example, could Title be an attribute for Song, for Event, and/or for Customer? For each entity, select the attribute that could be the unique identifier of each entity. Entity: STUDENT Attributes: student ID, first name, last name, address UID: _____________ Entity: MOVIE Attributes: title, date released, producer, director UID: _____________ Entity: LOCKER Attributes: size, location, number UID: _____________ 6. Read the given business scenario and walk through the steps below. “I’m the owner of a small movie rental store. We have over 3,000 movies that we need to keep track of. “Each of our movies has a DVD or VHS tape number. For each movie, we need to know its title and category (e.g., comedy, suspense, drama, action, war, or sci-fi). “Yes, we do have multiple copies of many of our movies. “We give each movie a specific ID, and then track which DVD or VHS contains the movie. A movie can be either DVD or VHS format. “We always have at least one DVD or VHS tape for each movie we track, and each DVD or VHS tape is always a copy of a single, specific movie. “Our DVDs and VHS tapes are large enough to contain entire movies. We don’t have any movies that require multiple DVDs or VHS tapes.” Write a list of nouns used in the scenario. _____________, _____________, _____________, _____________ Name each entity. _____________, _____________, _____________, _____________ c. Is each instance of the entity uniquely identifiable? Which attribute or attributes could serve as the unique attribute to identify the entity? _____________, _____________, _____________, _____________ Age is an example of what type of attribute? Volatile attribute Birth date is an example of what type of attribute? Non-volatile attribute Read the business scenario of a fast-food restaurant below:We are a small fast food business. Our menu features food items that can be ordered by a customer. A customer places an order at the counter and indicates what food items he/she would like on that order. Lately we’ve noticed that we have some regular customers, so we started asking them for information – such as name and address, so we can mail them coupons when we have specials. Using the restaurant entities ORDER, FOOD ITEM, AND CUSTOMER, identify the attributes for each entity. Where possible, indicate whether the attribute is mandatory or optional. Where possible, pick out the UIDs for each entity Solution: This restaurant business scenario is an offshoot version of the progressive project, Global Fast Foods, which students will be building throughout the course. A definitive solution for this activity is not provided, but the solution can be similar to the Global Fast Foods model. Some students may want an entity called MENU, with food item as an attribute. Be careful with this one: although MENU could be a valid entity, food item as an attribute would be repeating: there would be many different food items on a menu. Therefore, even with MENU as an entity, you would still need FOOD ITEM as another entity. Attributes of menu could be name, hours served, etc. Ask students why they would choose to make an attribute mandatory or optional. This makes them start thinking about business rules. UID for ORDER could be number, UID for FOOD ITEM could be name (or ID), and UID for CUSTOMER could be name and address together or ID. Database Design 2-3: Entity Relationship Modeling and ERDsPractice Activities and some Solutions Vocabulary Directions: Identify the vocabulary word for each definition below. Implementation-free Not dependent on the physical model Entity relationship diagram (ERD) A drawing that is used to represent a data model. Try It / Solve It True or False: To be described as implementation-free, a data model must be changed to accommodate the database system onto which it is built. ________ True or False: To be described as implementation-free, a data model must not change to accommodate the database system onto which it is built. ________ List four goals of entity relationship modeling. Capture all required information Ensure that information appears only once Model no information that is derivable from other information already modeled Locate information in a predictable, logical place An ________ is a consistent tool that can be used to represent data requirements regardless of the type of database used. ................
................

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

Google Online Preview   Download