CIT178 Relational Databases Online
Topic outline
-
-
Livestream Office Hours Spring 2022: Sunday 7:00pm-10:00pm, Monday and Wednesday 5:15pm-6:15pm or by appointment
I will not be in Zoom outside of class and office hours. If you select the link and I am not in Zoom yet, you will be placed into a waiting room. Please be patient and wait for office hours or class to begin.
-
Critical thinking is one of the general education outcomes assessed in our course. This semester, we are using a standardized test known as The Test of Everyday Reasoning with Numeracy (TER-N) to assess the critical thinking competency.
You may have multiple courses using this test, but you only need to take it ONE time per school year (Fall 2021 through Spring 2022).
Please do the following:
1) Check your email for a message with the subject line TER-N username and password. The email includes the username and password you need to login to the exam and instructions on how to access the test.
2) Please read the testing instructions attached to the email message.
3) Once you complete the test, please enter this dropbox and submit "Done"
Let me know if you have any questions (you should receive an email with your username and password on Friday, March 24th)
-
-
Make sure you schedule and take the IT Specialist Database certification exam.
Exam Dates are arranged by contacting the testing center and scheduling a date and time for your test . Appointments are required. 231-995-2257 or nmcapptc@nmc.edu
Testing dates and times are :
Tuesday, April 26th - 9:00am to 3:00pm
Wednesday, April 27th - 9:00am to 3:00pm
Thursday, April 28th - 9:00am to 3:00pm
Saturday, April 30th - 9:00am to 1:00pm
Tuesday, May 3rd - 9:00am to 3:00pm
Wednesday, May 4th - 9:00am to 3:00pm
RETAKES ONLY: Thursday, May 5th - 9:00am to 3:00pmTry to schedule your test ASAP - they only have a certain number of computers at the testing center AND the testing center is used for all professional certification exams!
-
Points from the exam will be posted here. A passing score will result in 100 points. If you don't pass, you can retake the exam, but you must wait 24 hours (that is a Certiport/Microsoft requirement).
How your final will be scored
You have two opportunities to pass this test. If you don’t obtain 700 out of 1,000 points on the first attempt, you can return for a retake. Certiport requires 24 hours between tests. Make sure you take the exam early to allow for the opportunity to retake the test if you need to.You will earn points as follows:- If you obtain a minimum of 700 points, you pass and will earn all 100 points.
- If you take two attempts and pass the second attempt, the second attempt score will be used and you will earn all 100 points.
- If you take two attempts and do not pass the second, I will take the higher of the two and divide your score by 700. For example, if you receive 680 on the MTA exam, then you will receive 97 out of 100 as your final exam score (680/700 = 97)
- If you obtain a minimum of 700 points, you pass and will earn all 100 points.
-
The certification exam must be taken at the professional testing center located in the Parsons-Stulen building on the Aero-Park campus.
To ensure social distancing, you must call or email and schedule a testing time BEFORE you can take the exam: 231-995-2257 OR nmcapptc@nmc.edu
The testing center schedule is shown below:
Please note the following:- Testers will need to bring a valid ID and a Certiport (www.certiport.com) username and password.
- Appointments are required.
- No food, drink, cell phones or other personal items allowed in Testing Center.
- Retakes are allowed 24 hours after an unsuccessful attempt. NO RETAKES AFTER MAY 5TH!
- Test accommodations are granted by Certiport and tester must give Testing Center 48 hours notice.
- Testing Center is located in the Parsons-Stulen Building, 2600 Aero Park Drive, second floor, room PS206
You can use resources within this block to help you prepare for the test.
-
NOTE: This is a webp file that you can download and view in your browser.
-
NOTE: This is a webp file that you can download and view in your browser.
-
This video is part two of a four-part series on SQL Data Definition and Data Manipulation commands. In this video we focus on the ALTER TABLE statement, using it to and remove columns, and various table-based constraints like CHECK, UNIQUE and the tricky FOREIGN KEY.
-
This video is part three of a four-part series on SQL Data Definition and Data Manipulation commands. In this video we focus on the 4 statements that make up the data management / CRUD operations: INSERT, SELECT, UPDATE and DELETE. In addition we continue to improve the SQL script for our fictitious company, Spiffy Lube.
NOTE: The video shows how to remove constraints in order to update tables
-
This video is part three of a four-part series on SQL Data Definition and Data Manipulation commands. In this video we focus on the 4 statements that make up the data management / CRUD operations: INSERT, SELECT, UPDATE and DELETE. In addition we continue to improve the SQL script for our fictitious company, Spiffy Lube.
NOTE: The video shows how to remove constraints in order to update tables
-
Discussion: Peer Review of Final Showcase, GitHub and ePortfolio Assignments are due by Saturday, 5/7 11:59 pm
- Peer Review of Term Project - see week 13 for the discussion forum
- GitHub assignment – create a repo for your final project and upload the project
- ePortfolio assignment – link your GitHub repo into your ePortfolio
-
To see a video showing you how to create the repo, upload files and commit, view: https://youtu.be/w_LUK8M2sJs
You will need to:
1. Login to GitHub
2. Create a repository named: CIT178 Term Project
3. Upload your Final Project folder to the repo
4. Submit a link to the project using this dropbox
-
NOTE: If you have a current LinkedIn profile, you do NOT need to create a new one. Just add a link to your final project repository as indicated in the instructions.
- Peer Review of Term Project - see week 13 for the discussion forum
-
Week 13(4/17-4/24) Chapter 16 Transaction Processing, Chapter 17 Database Security and Term Project Showcase
Assignments/Quizzes are due by Sunday, 4/24 11:59 pm
- Certiport Assignment - create an account or login to an existing account and take a screenshot
- Lecture/demo: Chapter 16: Transaction Processing
- Textbook Assignment
- Lecture/demo: Chapter 17: DB Security
- Textbook Assignment
- Unit 5 Quiz (chapters 15, 16 and 17)
- Discussion: Final Project Showcase
Homework: Complete assignments and quiz.
-
The college has exam vouchers - all you need to do is register to obtain a username and password. If you already have an account, login and take a screenshot and submit that for credit :)
-
Attend the industry night out, submit a paragraph summarizing the event and what you learned, and receive 10 points extra credit.
-
The quiz is worth 15 points and includes 15 questions from chapters 15, 16, 17 and the lecture notes.
Each question is worth 1 point depending upon the difficulty of the question.
Questions include a variety of question types similar to what you will see on the certification exam. The question types include multiple choice, multiple answer, selection, matching and reorder.
You can take the quiz 3 times. Your highest score will be recorded in the gradebook automatically. There is no time limit on the quiz, and it is open book/open note.
The best strategy for success is to use the book and notes instead of googling the answer. If you google the answer, you will probably get it wrong.
Correct answers will not be displayed until the test has closed; however, you will know when you submit the test if an answer was correct or incorrect
- Certiport Assignment - create an account or login to an existing account and take a screenshot
-
Assignments/Quizzes are due by Monday, 4/18 11:59 pm
- Chapter 15: How to code stored procedures, functions, and triggers
- Textbook Assignment
- File Project Milestone #3 Assignment - This is the last part of the final project. Make sure you have all of the project completed and that you have made any necessary corrections.
-
Assignments/Quizzes are due by Sunday, 4/10 11:59 pm
- Lecture/demo: Chapter 14: How to code scripts
- Textbook Assignment
- Lab assignment
- Unit 4 Quiz (chapters 9,13 and 14)
-
Attend the industry night out, submit a paragraph summarizing the event and what you learned, and receive 10 points extra credit.
-
Attend the event, submit a paragraph summarizing the event and what you thought about it, and receive 10 points extra credit.
-
The quiz is worth 25 points and includes 22 questions from chapters 9,13,14 and the lecture notes.
Each question is worth 1 or 2 points depending upon the difficulty of the question.
Questions include a variety of question types similar to what you will see on the certification exam. The question types include multiple choice, multiple answer, selection, matching and reorder.
You can take the quiz 3 times. Your highest score will be recorded in the gradebook automatically. There is no time limit on the quiz, and it is open book/open note. There are 5 questions that are randomly drawn from a pool of questions. These 5 questions will change each time you take the test.
The best strategy for success is to use the book and notes instead of googling the answer. If you google the answer, you will probably get it wrong.
Correct answers will not be displayed until the test has closed; however, you will know when you submit the test if an answer was correct or incorrect
-
Assignments are due by Sunday, 3/27 11:59 pm
- Lecture/demo: Chapter 9: How to work with Functions
- Chapter 9 Textbook Assignment
- Chapter 9 Lab Assignment
- Lecture/demo: Chapter 13 - How to work with views
- Chapter 13 Textbook Assignment
- Final Project Milestone #3 Lab Assignment (section #2)
Homework: Complete all current assignments. Read Chapter14
SPRING BREAK runs Monday 3/28 through Sunday 4/3-
To see a video explaining how to use CHOOSE, watch: https://youtu.be/pUQMnVkr71s
To see a video explaining how to use CASE with GROUPING and ROLLUP, watch: https://youtu.be/hN-QWoorRzg
-
Assignments/Quizzes are due by Sunday, 3/20 11:59 pm
- Lecture/demo: Chapter 6: How to code subqueries
- Chapter 6 Textbook Assignment
- Chapter 6 Lab assignment
- Unit 3 Quiz (covers chapters 5,6,7)
Homework: Finish assignments and quiz. Read chapters 9 and 13
-
IMPORTANT: Task #3 is the only one that requires subqueries! The rest of the assignment is a review of information we have covered throughout the semester!
PLEASE READ: After you create the testcorp database, when you query the Order table, you will need to put it in brackets to use the table because SSMS thinks it is a keyword. Use [Order] when you refer to the table and everything should work fine.
Example: SELECT * FROM [Order]
-
The quiz is worth 30 points and includes 25 questions from chapters 5,6, 7 and the lecture notes.
Each question is worth 1 or 2 points depending upon the difficulty of the question.
Questions include a variety of question types similar to what you will see on the certification exam. The question types include multiple choice, multiple answer, selection, matching and reorder.
You can take the quiz 3 times. Your highest score will be recorded in the gradebook automatically. There is no time limit on the quiz, and it is open book/open note.
The best strategy for success is to use the book and notes instead of googling the answer. If you google the answer, you will probably get it wrong.
Correct answers will not be displayed until the test has closed; however, you will know when you submit the test if an answer was correct or incorrect
- Lecture/demo: Chapter 6: How to code subqueries
-
Assignments/Quizzes are due by Sunday, 3/13 11:59 pm
- Lecture/demo: Chapter 5: How to code summary queries
- Chapter 5 Textbook Assignment
- Final Project Milestone #3 Lab assignment (section 1)
Homework: Finish assignments. Read chapter 6
-
Milestone #3 has 3 different sections. This is section #1.
-
Assignments/Projects are due by Sunday, 3/6 11:59 pm
- Lecture/demo: Chapter 7- How to insert, update, and delete data
- Textbook Assignment
- Final Project Milestone #2 lab assignment (due this week)
- Final Project Mini-Showcase and Discussion Forum (due this week)
Homework: Finish assignments , finish final project milestone #2, finish the mini-showcase and post to the discussion forum, review classmates forum posts. Read chapter 5
-
Week 6 (2/20-2/27): Chapter 11 - Creating tables using SQL and Chapter 12: Creating tables using SSMS
Assignments/Quizzes are due by Sunday, 2/27 11:59 pm
- Lecture/demo: Chapter 11: How to create a database and its tables with SQL Statements
- Textbook Assignment - Chapter 11
- Lecture/demo: Chapter 12: How to create a database and its tables with Management Studio
- Textbook Assignment - Chapter 12
- Lab for Final Project Milestone #2 (not due until 3/6)
- Unit 2 Quiz (Chapters 8,10,11,12)
-
Make sure you have gotten feedback on final project milestone #1 and incorporated any changes into your tables BEFORE you begin working on milestone #2!
-
The quiz is worth 50 points and includes 37 questions from chapters 8,10,11, 12 and the lecture notes.
Each question is worth between 1-3 points depending upon the difficulty of the question.
Questions include a variety of question types similar to what you will see on the certification exam. The question types include multiple choice, multiple answer, selection, matching and reorder.
You can take the quiz 3 times. Your highest score will be recorded in the gradebook automatically. There is no time limit on the quiz, and it is open book/open note.
The best strategy for success is to use the book and notes instead of googling the answer. If you google the answer, you will probably get it wrong.
Correct answers will not be displayed until the test has closed; however, you will know when you submit the test if an answer was correct or incorrect
-
Assignments/Quizzes are due by Sunday, 2/20 11:59 pm
- Final Project Milestone #1 You need to come up with an idea for a database you will be working with for the rest of the semester, you need to layout tables for your database AND you need to make up data. You will be creating the tables in Excel (just like the Chapter 10 textbook assignment).
IMPORTANT: The final project lab assignment WILL need revision after you submit it. I expect that everyone will need help with this. Students who submit the spreadsheet and ER diagram will receive all the points (even if it needs adjustments), so do not worry about losing points. Designing tables correctly is a process and takes experience. You can only learn how by actually doing it. I can help you before you submit it or after you submit it. I can help you using conferencing software (Zoom) OR I can create a video showing you how to fix any issues and explain why the issues are problematic. It is important that we correctly define the tables BEFORE you work on the next milestone which involves creating the tables in SSMS! - Discussion: Legal and Ethical issues Involved in Data Sharing
Homework: Make any revisions to the final project lab assignment as needed and participate in the class discussion.After you are done with everything, please read chapters 11 and 12 for next week.
- Final Project Milestone #1 You need to come up with an idea for a database you will be working with for the rest of the semester, you need to layout tables for your database AND you need to make up data. You will be creating the tables in Excel (just like the Chapter 10 textbook assignment).
-
Assignments/Quizzes are due by Sunday, 2/13 11:59 pm
- Lecture/demo: Chapter 8- How to Work with Data Types. You do not have to complete an assignment for this chapter. The lecture/demo includes links to interactive examples you should practice. You will be applying what you have learned when you create the database tables for your final project.
- Lecture/Demo Chapter 10: How to design a database - We will look at normalizing database tables. There are a lot of examples in the lecture that should help you better understand the process.
- Chapter 10 Textbook Assignment - The procedures you use in this assignment will also be used in the final project assignment will be working on next week.
Homework: Complete the chapter 10 textbook assignment
- Lecture/demo: Chapter 8- How to Work with Data Types. You do not have to complete an assignment for this chapter. The lecture/demo includes links to interactive examples you should practice. You will be applying what you have learned when you create the database tables for your final project.
-
Assignments/Quizzes are due by Sunday, 2/6 11:59pm
- Lecture/demo: Chapter 4- How to retrieve data from two or more tables
- Chapter 4 Textbook Assignment
- Chapter 4 Lab Assignment
- Unit 1 Quiz (Chapters 1,2,3 and 4)
Homework for next week: Finish assignments. Read chapter 8 and chapter 10.
-
The quiz is worth 30 points and includes 26 questions from chapters 1,2,3 and 4 and the lecture notes.
Each question is worth 1 or 2 points depending upon the difficulty of the question.
Questions include a variety of question types similar to what you will see on the IT Specialist Database certification exam. The question types include multiple choice, multiple answer, selection, matching and reorder.
You can take the quiz 3 times. Your highest score will be recorded in the gradebook automatically. There is no time limit on the quiz, and it is open book/open note.
The best strategy for success is to use the book and notes instead of googling the answer. If you google the answer, you will probably get it wrong.
- Lecture/demo: Chapter 4- How to retrieve data from two or more tables
-
Assignments/Quizzes are due by Sunday, 1/30 11:59pm
- Lecture/demo: Chapter 2- How to use the Management Studio
- Chapter 2 Textbook Assignment
- Lecture/demo: Chapter 3- How to retrieve data from a single table
- Chapter 3 Textbook Assignment
Homework for next week: Finish assignments. Read chapter 4
-
With ties looks for values that match the last item in the list and displays them. It makes the most sense for rankings. For example, if you wanted to know the top 5 GPAs in a graduating class, you would want to know if other students had the same GPA as the 5th student (they should all have the 5th rank). It should be noted that SQL uses the column specified in the ORDER BY clause to determine which rows have "tie" values.
-
Topics:
- SQL Home
- SQL Intro
- SQL Syntax
- SQL Select
- SQL Select Distinct
- SQL Where
- SQL And, Or, Not
- SQL Order By
- SQL Select Top
- SQL Like
- SQL In
- SQL Between
-
Assignments/Quizzes are due by Sunday, 1/23 11:59pm
- Course Overview Video
- Welcome message
- Syllabus, Schedule, and Course Information
- Student Profile Glossary
- Discussion: Introductions
- Lecture/demo: Chapter 1- An introduction to relational databases and SQL
- Lecture/demo: Required Software and Installation Instructions (must have installed before week 2)
Homework for next week:
- Read chapters 1, 2 and 3
-
This is your first assignment.
1. Select the Add a new entry command button or link.
2. Enter your name as a Concept.
3. Add your photo by using the "insert or edit image" button in the "Definition" section (resize to a thumbnail, approx. 200 pixels wide and let the height automatically adjust).
4. Add a one sentence statement about yourself. (If you have a hashtag that describes you, you can add that below the sentence).
NOTE: Your information (and the information your classmates post) will be randomly displayed in the Class Spotlight located on the left side of the Moodle window. To see a video showing how to create the profile, watch: https://youtu.be/4rP8UoeT7Zw
-
Introduce yourself to your classmates. In your introduction, please cover the following: Your name or nickname, your interests or hobbies and your major or program of study. If you would like to share additional information about your work, family etc that is fine (feel free to share whatever you are comfortable with).
Be sure to contact me if you have questions.
-
Download the WideWorldImporters-Standard.bak which is near the bottom of the list of files to download.
-
If you would like to see a video showing how to download and install SQL Server Developer Edition and the SQL Server Management Studio, view: https://youtu.be/B2vQiMd3QYQ
If you would like to see a video showing how to download and install the publisher's database files, the Northwind database files and the WorldImporter database backup, view: https://youtu.be/4VJX6rIYCm0
-
Microsoft Corporation offers a free copy of MS Office 365 for students. This includes Word, Excel, PowerPoint and Access.
This is an excellent opportunity for students!
