Assignment 3
Assignment 3 – Theory Summer 2019 tso omvs sessions(3) Contents Introduction4 Researching Your Answers4 A Little Advice Before you start5 Demonstrating Knowledge and Increased Penalties for Irrelevant Answers6 You Must Submit YOUR Answer in this Original Word Document to Blackboard6 You Must RENAME this Original Word Document to Include your LAST NAME7 NEVER submitted an Assignment as an Email Attachment7 ONLY Submit a FINAL Version of ALL Assignment7 Requests to Clear Previously Assignments for Re-Submission7 Submitting Late Assignments7 Academic Integrity and Plagiarism8 1.0 Introduction to Database Design9 1.1 Conceptual design10 1.2 Logical design10 1.3 Bottom-up, Top-down, or Combination Design Approaches10 1.4 Overview of the Steps Recommended for Database Design11 1.5 How does one determine an entity or table?12 2.0 Normalizing a Database13 2.1 Rule 1 -Separate all repeating fields and store these repeating fields into a separate table.13 2.1.2 Primary Key and Non-Key Columns14 2.1.2 Composite Primary Keys and Atomic Primary Key15 2.1.3 Limitations of Composite Primary Key15 2.1.4 Natural Primary Keys15 2.1.5 Advantages of an Auto Increment Primary Key16 2.2 Rule 2 - All non-key fields should be functionally dependent upon the "entire" primary key.16 2.3 Rule 3 - Eliminate any non-key fields that can be created, calculated or derived by a combination of the other non-key fields, within the row or between rows.17 2.4 A Four-Table Normalized Design to Store a Customer's Invoice18 2.5 Bachman Diagrams18 2.6 Pros of a Normalized or De-normalized Database Design19 2.6.1 Pros and Cons of a Normalized Database Design19 2.6.2 Pros and Cons of De-normalized Database Design19 2.7 Comparing Transactional Database Storage Design to Data Warehouse Design (OLTP to OLAP)20 2.8 Descriptive vs. Predictive vs. Prescriptive Data Analytics20 2.9 Terrorist Attacks: A Comparison of Descriptive, Predictive and Prescriptive Analytics21 2.10 Jeff Jonas (IBM researcher) and Contextual Data Processing Models23 3.0 Index Analysis and Design24 3.1 Advantages of Database Indexes in Conceptual Design24 3.1 Disadvantages or Costs of Indexes26 3.3 Applying Index Design to a Customer Invoice Database27 3.3.1 Stock Number Index in the Invoice Item Table27 3.3.2 Invoice Date Index in the Invoice Header27 3.3.3 Purchase Order Number Index in the Invoice Header27 3.3.4 Customer Number Index in the Invoice Header Table28 4.0 Data Relationships29 4.1. One-To-One Relationships29 4.2 One-To-Many Relationships31 4.3 Many-To-Many Relationships32 4.3.1 Student – Major Many-to- Many Relationships32 4.3.2 Invoice – Inventory Many-to- Many Relationships32 4.4 Appling Relationship Analysis to the Customer Invoice Database34 5.0 Review Questions36 5.1 Questions - Functional and Data Analysis36 5.2 Questions - OLTP and OLAP37 5.3 Questions - Bottom–Up versus Top-Down Database Design Approaches39 5.4 Questions - Limitations Selecting a Primary Key40 5.5 Questions - Natural Key versus Auto-incrementing Key Design Considerations41 5.6 Questions - Data Relationships42 6.0 Applying Normalization and Indexes Analysis46 7.0 Entity Relationship Modeling (ERD Diagrams)48 7.1 Introduction to ERD: Nouns versus Verbs48 7.2 Common Errors Identifying Major Nouns and Entities49 7.3 ER Diagrams Usage50 7.3.1 Database ERD Diagrams50 7.3.1 Software Engineering ERD Diagrams50 7.4 ER Diagram Symbols and Notations50 7.4.1 Entity51 7.4.2 Weak Entity51 7.4.3 Attribute51 7.4.4 Multivalued Attribute52 7.4.5 Derived Attribute52 7.5 Relationship53 7.51 Recursive Relationship53 7.5.2 Multiplicity, Cardinality and Participation54 7.5.3 Degrees of Relationship (Cardinality)54 7.6 How to Draw ER Diagrams54 7.7 ER Diagram Best Practices55 7.8 Generalization Specialization, Aggregation and Composition in DBMS56 7.8.1 Generalization and Specialization in DBMS56 7.8.2 Aggregation in DBMS58 7.8.3 Composition in DBMS59 7.9 Questions - Applying ERD Concepts60 7.9.1 Examples and Description of UML Concepts61 Enter your Name Here Introduction Researching Your Answers Most requirements of this assignment will require you to research answers from your text book (you must read the text book to get some answers), from the Internet, from a video or any other reasonable source. Many Internet sources, video links, text book and Blackboard presentations are provided in this document to help you START your research. Assignments are always a great place to read and reference your text book. May students assume that they should start by reading the text book. While this reading the text book is ALWAYS at great idea, the following assignment questions may help you focus on what is important in this course. One strategy is to review an assignment question for important keywords, e.g., multitasking, Procedure Division, virtualization, etc. Then look for those key words in either 1) your text book "detailed" table of contents in the beginning of the book, or 2) in the index at the end of the book. Some text books have key word glossaries at the end of the chapter or end of the book. Every semester the Internet sources, e.g., a Google keyword search, or Internet video, e.g., a YouTube keyword search may be improved. Better students start with research sources provided in this assignment, and then search for improved or updated answers. While your objective may be to demonstrate your knowledge to EARN an excellent grade on this assignment, better students are always looking towards the future to impress internship and career recruiters for those interesting and high entry-level salaries. Employers don't pay you for a grade in any single class or assignment. Employers will pay you BIG MONEY for demonstrated knowledge or skills. Hopefully, this assignment will prepare you. A single research source maybe very incomplete or the format or the content may not be appropriate for some required answers. Some students do not READ the text book or review the Blackboard presentations provided to you in Blackboard. Some answers are only available from Blackboard presentations or update documents. When assignment requirements may be only answered by viewing a YouTube video, you will be notified. You may cut-and-paste answers whenever appropriate. You MUST synthesize your answers to include multiple sources. I would be impressed that you consult a Google image search and paste and appropriate image to supplement your answer. While I permit cut-and-paste, I do expect you use your own words so that you: 1. Organize the answer 2. Demonstrate that you have read what you have cut-and-pasted 3. Use any means that clearly displays that you have gained knowledge. A Little Advice Before you start There is NO requirement to read each reference link or view each video in detail. Some of the links will have overlapping content. Some links will provide more information than the question requirements, but employers consider these topics to be minimal knowledge of a RMU CIS graduate. It is recommended to visit each reference link and overview the content. Then read each question and return to each reference link or video as needed. You may supplement your answers with content from your text book by using a question keyword and looking up in the glossary or index in the back of the book. PDF text books can be easily searched for keywords. It is not required to read your text books before working on this assignment unless specified in a requirement. Text book contents are used to support quiz questions (which contain the answers), which are used on the tests. The reference links and videos are excellent resources. The topics presented in the course assignments have been highly recommended by two or more employers who recruit RMU CIS students as minimum computer hardware, operating system, and application development knowledge. Employers expect that students should be able to present one or two sentences of the majority of keywords applicable to job requirements in a face-to-face interview. On the other hand, each assignment is allocated 100 points out of a total of 1000 points. The number of questions or hands-on activity on each assignment varies. Assume that an average assignment has 50 requirement questions. This means that a requirement may be worth 2 points towards your final grade. The bottom line is that missing a few questions will have little effect of your assignment or final grade. Not completing an assignment will generally decrease your final grade by at least a letter grade. Do not waste time on the small problems. Demonstrating Knowledge and Increased Penalties for Irrelevant Answers If you can DEMONSTRATE your knowledge of the topic for the requirement there will be no penalty for your answer. It is not the intention of this assignment to be "not picky". Parital credit will be awarded as appropriate. If you cut-and-paste and pray that your instructor will not read your inappropriate and irrelevant answer, the question will be penalized by increasing the deduction points beyond the original requirement points. The instructor hates irrelevant cut-and-paste BS, or answers that appears that the student is guessing and hopes the instructor does not read the answer. The instructor reserves the right to increase the penalty for any submitted question or assignment that may be construed as "wasting the instructor's time". Therefore, a four-point requirement may be penalized as six-points (two additional point penalty for wasting the instructor's time). Sometimes blank answers will earn you’re a high assignment grade than BS answers. For example, a submitted 100-point assignment may be penalized as 125 minus points when your final grade is calculated for any assignment that should have never been submitted in the first place. You Must Submit YOUR Answer in this Original Word Document to Blackboard This Assignment Word Document will contain hidden markers that may be used to detect plagiarism and provide an audit trail of those who may have modified the Word document. Many students in my classes work very hard to complete and learn from their assignments. It is not fair to those students who have professionally demonstrated their knowledge to receive the same grade as those who have plagiarized their assignments You MUST answer ALL requirement in this Word document and ONLY THIS Word Document. You MAY NOT use or edit any other word processor, except any version of Microsoft Word. Do not use GOOGLE DOCS or Open Office DOCX files at any time. If you use any other Word Processor you will be assigned ZERO credit. If you do not have a copy of WORD, you may use VMWARE VIEW (available from the RMU website) to access a virtual lab computer which contains any software needed for this course. http://www.rmu.edu/web/cms/departments-offices/administration-services/it/Pages/vmware-view.aspx NEVER STORE ANY DOCUMENTS ON THE DESKTOP OF VMWARE VIRTUAL COMPUTER. You will lose your document. It is preferable to store your documents on RMU Drive U: If necessary you can email the document to yourself. You Must RENAME this Original Word Document to Include your LAST NAME YOU MUST enter your name in the beginning of this document