What’s in Module 3? Read Carefully.
Module 3 includes contents from:
· Mainly chapters 4 and 5 of the R. Watson text. Support for SQL related to these chapters is also in Chapter 10.
· Supplementary content can be found in multiple locations, including:
· J. Murach, MySQL recommended text: in case you still have trouble installing MySQL server and Workbench, see Appendix A (for Windows) and B(for Mac OS)
· J. Murach for MySQL queries: Chapter 4: How to retrieve data from two or more tables.
· Free online tutorials:
· Video tutorials at lynda.com, free as a TWU student, but you have to set up an account through TWU
· At http://www.mysqltutorial.org/ (interactive)
· Section 5 on joining tables
· Section 13 on constraints (How to set foreign keys)
If you have questions or need clarification, let your instructor know as soon after the module post. Post questions in the office hour forum. Clarifications and questions may be added to this document as needed.
Tasks for this module
1. Readings: Extracting Data from Two or More Entities:
· A slide outline is provided to guide your reading for each chapter.
· Outline for Chapter 4: The One-to_Many Relationship
· Outline for Chapter 5: The Many-to_Many Relationship
· There is a discussion forum for this module.
· You are required to participate in one of the discussion forums.
· If you missed a discussion forum in a previous module, you may participate for credi t in a second discussion forum (i.e. a make-up missed discussion forum opportunity). If you take this opportunity, you must participate in discussion topics from both chapters.
· You will learn about how tables are related and connected to each other. The diagrams shown in the chapters come from different software packages, and you will notice they are similar but not identical. The idea here is to understand what relationships are and how to make those relationships work in SQL and queries without getting lost in the details of the diagram. In another unit, later in the semester, we will dig into the diagrams (models) themselves, and understand how to create them. For this module concentrate on understanding the relationships between any 2 tables to:
· Understand from which tables the data you want in query results need to come.
· How you need to connect those tables (i.e. set foreign key constraints to set the relationships)
· How to join to tables in a query.
· For the other elements (fields, aggregate functions, WHERE clause, etc.) those are mostly as in the previous module.
· You will practice creating, populating and querying 2 or more database tables in this unit.
· The discussion forum gives you the opportunity to practice and get feedback on examples. So it’s important to participate early in the module time frame. Discussion boards will not be reset.
· Detailed instructions and topics are listed in the forum.
· The discussion forums are graded, and participation is required. Access the discussion forum for chapter 3.
· You will need to create database tables and write some queries. If you do not have the MySQL software in place yet, you can again use an online SQL emulator for this module. However, you will want to have MySQL in place for module 4.
· (This is a repeat from the Module 3 post) How to work with an SQL emulator:
· Write and same your SQL code using a text editor. This is IMPORTANT, as a word processor such as MS Word will alter elements of the code.
· What is a good text editor? There are several free downloadable example available, for example I use Notepad++ or Komodo Edit.
· Write your code in a text processor, so you can save it. Save any code you write as and sql document. You can copy and paste the text into an SQL emulator.
· Emulators are available (free) online. Each comes with some sample SQL code (which you can delete). These are the ones I’ve tried:
· From codingground (Links to an external site.)Links to an external site. at tutorialspoint.com
· The one at jdoodle (Links to an external site.)Links to an external site. is also good.
· Write or copy your code in the window, and execute. Save your code and results as needed.
2. Complete the assignment for the module . The module assignment requires you to apply your knowledge and understanding of concepts presented in the module. Follow the link to access the Assignment for module 3. A direct link to the assignment is also posted in the module.
3. Complete the module quiz . The module quiz tests your knowledge and understanding of terms, definitions and concepts presented in the module. Fill in the blank questions will be reviewed for correctness manually.
4. All tasks are due by end-of-day 10/2/2018. Summary of tasks:
· Discussion forum task for Module 3
· Module 3 assignment
· Module 3 quiz
This is the forum for the discussion question in Module 3, Chapters 4 and 5. Carefully study the examples in the chapters first. Participate EARLY in the module.
From previous module : A step-by-step example of how to create a database, a database table, and populate a database table. The example uses the SHARE entity and data from the text.
Participate in ONE discussion topics for the chapter as follows.
· Please REVIEW the post in the Module 3 Overview page on some make-up credit opportunity if you’ve missed a discussion forum in the past modules.
· You can use an emulator to generate tables and queries.
· The discussion forum for this module is progressive. In other words, the tasks in the first topic must be completed, before the queries in subsequent topics can be completed.
Topics for Chapter 4:
· C4_CSN: Create the STOCK and NATION database at the beginning of the chapter and populate it with data. Note that the STOCK table is the SHARE table from the previous chapters, with small modifications. You can copy the code for creating and populating the SHARE table and modify it. Populate the tables with data. The initial post should be the code to create the database. Subsequent post in this thread can be the INSERT statements to populate and add data to the tables.
· C4_QSN: Write and execute 2 of the queries shown in the text on the database created in C4_CSN. Can you repeat a query already posted? Only if your query is different or if there is an error. Otherwise choose different query. If there are no query examples left in the chapter, create a new one on your own (see next topic). Document each query – i.e. explain what output data it generates.
· C4_MQ-18: Write the SQL for query in slide 18 of the chapter outline on the database created in C4_CSN. You can improve on a posted solution.
· C4_MQ-27: Write the SQL for query in slide 27 of the chapter outline on the database created in C4_CSN. You can improve on a posted solution.
Topics for Chapter 5:
· C5_CMN: Create the example data base used in chapte 5 of the text and populate it with data. The initial post should be the code to create the database. Subsequent post in this thread can be the INSERT statements to populate and add data to the tables.
· C5_QMN: Write and execute 2 of the queries shown in the text on the database created in C5_CMN. Can you repeat a query already posted? Only if your query is different or if there is an error. Otherwise choose different query. If there are no query examples left in the chapter, create a new one on your own. Document each query – i.e. explain what output data it generates.
· Participation means that you contribute a significant post or a significant comment on one or more posts by others in the thread. Your posts should be detailed, address specific examples or experiences, and documented if possible with URL references.
· You must use threaded replies:
· If you are the first to post on a given topic, start a new thread. Use the thread names listed above
· Use reply in subsequent posts for the same topic
· You may use any number of likes
· The discussion forum is graded: 5 points for the required post. Up to 5 points if you are taking the make-up opportunity.