Course Details
Textbook
Murach, Joel. Murach’s MySQL Mike Murach and Associates. Fresno, CA: 2015.
ISBN: 978-1-890774-82-0
Software and Hardware
This course will require you to install two software packages: MySQL Community
Server and MySQL Workbench. Details on installation of these two software packages
can be found in Appendix A (Windows) and Appendix B (Mac OS) of your textbook.
Course Description
This course covers database development and programming techniques emphasizing
database structures, database models, and database access using SQL. Students are
instructed in the essential concepts and design methodology for the relational
database model as implemented by the MySQL database system. Other topics include
the data definition language, the data manipulation language, database
normalization, and transaction processing.
3 Credit Hours (3 hours lecture per week)
Course Syllabus
The CIS 1413 Syllabus provides information on the course
outcomes, grading and policies.
Agenda
Agenda
Week 1 (Monday, August 22 - Sunday, August 28):
- Chapter 1: An introduction to relational databases and SQL
Objectives:
- Identify the three main hardware components of a client/server system.
- Describe the way a client accesses the database on a server using these terms: application software, data access API, database management system, SQL query, and query results.
- Describe the way a relational database is organized using these terms: tables, columns, rows, cells, primary keys, and foreign keys.
- Identify the three types of relationships that can exist between two tables.
- Describe the way the columns in a table are defined using these terms: data type, null value, and default value.
- Describe how an entity relationship diagram can show how the tables in a database are defined and related.
- Describe the difference between DML statements and DDL statements.
- List three coding techniques that can make your SQL code easier to read and maintain.
- Describe the use of a database driver.
- Activity: Download and install MySQL Community Server and MySQL Workbench. If you are
using Windows, follow the instructions in Appendix A of your course textbook.
If you are using Mac OS, follow the instructions in Appendix B of your course textbook.
- Readings: Chapter 1
- Slides: Chapter 1 slides
- Online Lecture: Chapter 1 lecture
- Blackboard: Respond to the following question by Saturday, August 27th at 5pm:
Share some information about yourself:
- Why did you enroll in this class?
- Have you ever used or interacted a database before? (note that if you have purchased anything
before... you have most likely used a database)
- What do you like to do when you are not in class?
- If you were an astronaut, what planet would you like to visit and why?
(You do not have to respond to two other student posts to receive full credit for
this Blackboard post.)
- Blackboard: Respond to the following question by Saturday, August 27th at 5pm:
In Chapter 1, two types of
client/server architectures are discussed in detail. What are some of
the concerns and challenges that arise when you are using a web-based system?
(Remember to respond to two other student posts for full credit.)
- Assignment: Respond to the "Course Agreement Form" as posted in your course
syllabus by Saturday, August 27th at 5pm
I have read the course syllabus for CIS 1413 Relational Database Programming
at Pulaski Technical College, and I understand its content. I also understand
the rules for the class, and I will follow and abide by these rules, including
those relating to attendance, assignments, grading criteria, plagiarism, and behavior.
Semester: _____________
Date: _____________
Print name: _____________
Signature: _____________
Email address: (please use your PTC email address) _____________
Telephone: _____________
Week 2: (Monday, August 29 - Sunday, September 4):
- Chapter 2: How to use MySQL Workbench and other tools
Applied Objectives:
- Start or stop the MySQL database server.
- Use MySQL Workbench to do any of the following:
Create a database connection
Navigate through the objects of a database
View the column definitions for a table
View the data for a table
Edit the column definitions for a table
- Use MySQL Workbench to enter, run, open, and save SQL statements and scripts.
- Use the MySQL Reference Manual to look up information about SQL statements.
- Use MySQL Command Line Client to run a SQL statement.
Knowledge:
- Briefly describe the function of each of these client tools: the MySQL Command Line Client, MySQL Workbench, and the MySQL Reference Manual.
- Readings: Chapter 2
- Slides: Chapter 2 slides
- Online Lecture: Chapter 2 lecture
- Online Demo: How to install MySQL, Workbench and Murach files.
- Online Demo: Installing the My Guitar Shop database.
(Note that this tutorial shows one way to store your files, you may use another way if you prefer.)
- Activity: Create and interact with the My Guitar Database. First download the My Guitar shop starter files,
mgs_ex_starts, to begin the
Chapter 2 Project,
due Thursday, September 1st at 5pm.
- Blackboard: Respond to the following question by Saturday, September 3rd at 5pm:
What are some differences between
spreadsheets and databases? Give an example when you would prefer to use a spreadsheet.
Give an example when you would prefer to use a database.
(Remember to respond to two other student posts for full credit.)
- Have a wonderful Labor day holiday!
Week 3: (Tuesday, September 6 - Sunday, September 11):
- Chapter 3: How to retrieve data from a single table
Applied Objectives:
- Code SELECT statements that require any of the language elements presented in this chapter.
Knowledge:
- Distinguish between the base table values and the calculated values in SELECT statements.
- Describe the use of a column alias.
- Describe the order of precedence and the use of parentheses for arithmetic expressions.
- Describe the use of the CONCAT function in string expressions.
- Describe the use of functions with strings, dates, and numbers.
- Describe the use of the DISTINCT keyword.
- Describe the use of comparison operators, logical operators, and parentheses in WHERE clauses.
- Describe the use of the IN, BETWEEN, and LIKE operators in WHERE clauses.
- Describe the use of IS NULL in a WHERE clause.
- Describe the use of column names, column aliases, calculated values, and column numbers in ORDER BY clauses.
- Readings: Chapter 3
- Slides: Chapter 3 slides
- Online Lecture: Chapter 3 lecture
- Online Demo: Chapter 3 Demo
- Activity: Interact with the My Guitar Database.
Chapter 3 Project,
due Thursday, September 8th at 5pm.
- Blackboard: Respond to the following question by Saturday, September 3rd at 5pm:
In this chapter, arithmetic operators are presented on page 83. Discuss the difference
between division, integer division and modulo. Give an example of when you would
want to use each kind of operator.
(Remember to respond to two other student posts for full credit.)
Week 4: (Monday, September 12 - Sunday, September 18):
- To help study for the quiz, remember that the end of each chapter in the book contains practice questions.
The solutions to these questions are provided by the publishers on the textbook website.
To see a list of solutions by chapter, you can also click on the provided
link.
- Quiz: Chapter 1, 2 and 3, due Thursday, September 15th at 5pm.
Week 5: (Monday, September 19- Sunday, September 25):
- Chapter 4: How to retrieve data from two or more tables
Applied Objectives:
- Use the explicit syntax to code an inner join that returns data from a single table or multiple tables.
- Use the explicit syntax to code an outer join.
- Code a union that combines data from a single table or multiple tables.
Knowledge:
- Explain when column names need to be qualified.
- Describe the proper use of a table alias.
- Describe the differences between an inner join, a left outer join, a right outer join, a full outer join, and a cross join.
- Describe how to combine inner and outer joins.
- Describe the use of the implicit syntax for coding joins.
- Describe the use of the USING and NATURAL keywords for coding joins.
- Readings: Chapter 4
- Slides: Chapter 4 slides
- Online Lecture: Chapter 4 lecture
- Online Demo: Chapter 4 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 4 Project,
due Thursday, September 22th at 5pm.
- Blackboard: Respond to the following question by Saturday, September 24th at 5pm:
One of the most complicated aspects of SQL was discussed in this section: inner and outer joins.
There are many ways to explain the differences, including sample code, examples and Venn diagrams.
Stackoverflow.com has several examples on
http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins.
Review the postings on this forum and discuss which one that you prefer and why.
If you have a better method of explaining inner and outer joins, share in your post.
(Remember to respond to two other student posts for full credit.)
Week 6: (Monday, September 26 - Sunday, October 2):
- Chapter 5: How to insert, update, and delete data
Applied Objectives:
- Create a copy of a table that can be used for testing INSERT, UPDATE, and DELETE statements.
- Given the specifications for an action that modifies data, code the INSERT, UPDATE, or DELETE statement for doing the action.
Knowledge:
- Describe MySQL’s default behavior when you execute an INSERT, UPDATE, or DELETE statement.
- Explain how to handle null values and default values when coding INSERT and UPDATE statements.
- Readings: Chapter 5
- Slides: Chapter 5 slides
- Online Lecture: Chapter 5 lecture
- Online Demo: Chapter 5 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 5 Project,
due Thursday, September 29th at 5pm.
- Blackboard: Respond to the following question by Saturday, October 1st at 5pm:
The beginning of chapter 5 (page 150) discusses some of the advantages and disadvantages of
creating test tables. While it is advantages to create test tables before working with
live data, creating realistic test tables can be challenging. Discuss in detail an time
when a test table will not work as expected and demonstrate with a brief example.
(Remember to respond to two other student post for full credit.)
- Blackboard: Respond to the following question by Saturday, October 1st at 5pm:
I like to get feedback from students throughout the course. This is your space to
place comments. For some responses, I may get back with you through email, for others
I will respond to your Blackboard course. Please let me know two things that you
like about the course and two things that you would like to be different in the
course. (You do not have to respond to two other student posts to receive full credit for
this Blackboard post.)
Week 7: (Monday, October 3 - Sunday, October 9):
- Chapter 6: How to code summary queries
Applied Objectives:
- Code summary queries that use aggregate functions, including queries that use the WITH ROLLUP operator.
Knowledge:
- Describe summary queries.
- Describe the differences between the HAVING clause and the WHERE clause.
- Describe the use of the WITH ROLLUP operator.
- Readings: Chapter 6
- Slides: Chapter 6 slides
- Online Lecture: Chapter 6 lecture
- Online Demo: Chapter 6 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 6 Project,
due Thursday, October 6th at 5pm.
- Blackboard: Respond to the following question by Saturday, October 8th at 5pm:
Your text goes into some of the differences between the HAVING and WHERE clauses.
Review the example and explanation given at
http://www.programmerinterview.com/index.php/database-sql/having-vs-where-clause/. What
do you think about the example, does it help to explain some of the differences? Why or why not?
Also, review Ed's comment (the first comment listed) discussing potential disadvantages of
using HAVING when there is an equivalent WHERE clause. If you are unfamiliar with Big O notation,
take a look at
https://rob-bell.net/2009/06/a-beginners-guide-to-big-o-notation/.
(Remember to respond to two other student post for full credit.)
Week 8: (Monday, October 10- Sunday, October 16):
- Midterm grades will be posted by October 14th; they will include grades from week 1-6.
- To help study for the quiz, remember that the end of each chapter in the book contains practice questions.
The solutions to these questions are provided by the publishers on the textbook website.
To see a list of solutions by chapter, you can also click on the provided
link.
- Quiz: Chapter 4, 5 and 6, due Thursday, October 13th at 5pm.
Week 9: (Monday, October 17- Sunday, October 23):
- Chapter 7: How to code subqueries
Applied Objectives:
- Code SELECT statements that require subqueries.
Knowledge:
- Describe the way subqueries can be used in the WHERE, HAVING, FROM and SELECT clauses of a SELECT statement.
- Describe the difference between a correlated subquery and a noncorrelated subquery.
- Readings: Chapter 7
- Slides: Chapter 7 slides
- Online Lecture: Chapter 7 lecture
- Online Demo: Chapter 7 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 7 Project,
due Thursday, October 20th at 5pm.
- Blackboard: Respond to the following question by Saturday, October 22th at 5pm:
This chapter reintroduces the idea of using comments within your code to help make
your code more readable and easier to understand. You can also use comments to "comment out"
parts of your code. When would it be wise to comment out parts of your code? Give an example
of a code segment containing a section that you could comment out and how that could help you
debug complex code.
(Remember to respond to two other student post for full credit.)
Week 10: (Monday, October 24- Sunday, October 30):
- Chapter 8: How to work with data types
Applied Objectives:
- Code queries that convert data from one data type to another.
Knowledge:
- Describe the data that can be stored in any of the character, numeric, date/time, and large object data types.
- Describe ENUM and SET data types.
- Readings: Chapter 8
- Slides: Chapter 8 slides
- Online Lecture: Chapter 8 lecture
- Online Demo: Chapter 8 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 8 Project
due Thursday, October 27th at 5pm.
- Blackboard: Respond to the following question by Saturday, October 29th at 5pm:
Throughout the chapter, each data type is defined with the required size. Selecting the
correct size for the data in your database is very important. Imagine that you were designing
a database for Pulaski Technical College to store student information. Identify several (at least
10) different types of data elements that you would want to use to store student
information and the associated data type. Discuss why you have chosen each data type.
(Remember to respond to two other student post for full credit.)
- Blackboard: Respond to the following question by Saturday, October 1st at 5pm:
I like to get feedback from students throughout the course. This is your space to
place comments. For some responses, I may get back with you through email, for others
I will respond to your Blackboard course. Please let me know two things that you
like about the course and two things that you would like to be different in the
course. (You do not have to respond to two other student posts to receive full credit for
this Blackboard post.)
Week 11: (Monday, October 31- Sunday, November 6):
- Chapter 9: How to work with functions
Applied Objectives:
- Code queries that format numeric or date/time data.
- Code queries that require any of the scalar functions presented in this chapter.
Knowledge:
- Describe how the use of functions can solve the problems associated with (1) sorting string data that contains numeric values, and (2) doing date or time searches.
- Readings: Chapter 9
- Slides: Chapter 9 slides
- Online Lecture: Chapter 9 lecture
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 9 Project,
due Thursday, November 3rd at 5pm.
- Blackboard: Respond to the following question by Saturday, November 5th at 5pm:
The end of this chapter discusses CASE and IF statements. These statements
can produce the same result, but sometimes one is preferred over the other. Create
an example using a CASE and IF statement that produces the same result where the CASE
statement is preferred and discuss why. Create
an example using a CASE and IF statement that produces the same result where the IF
statement is preferred and discuss why.
(Remember to respond to two other student post for full credit.)
Week 12: (Monday, November 7- Sunday, November 13):
- To help study for the quiz, remember that the end of each chapter in the book contains practice questions.
The solutions to these questions are provided by the publishers on the textbook website.
To see a list of solutions by chapter, you can also click on the provided
link.
- Quiz: Chapter 7, 8 and 9, due Saturday, November 12th at 5pm.
Week 13: (Monday, November 14- Sunday, November 20):
- Chapter 10: How to design a database
Applied Objectives:
- Given the specifications for a database, identify the tables, columns, keys, relationships, and indexes for the database.
- Given the tables for an unnormalized database, normalize the structure to the third normal form.
- Use MySQL Workbench to create or work with an EER model for a database and any EER diagrams that are associated with that model.
Knowledge:
- Give three criteria for when a column should be indexed.
- Describe referential integrity.
- Explain how MySQL uses declarative referential integrity to prevent deletion, insertion, and update problems.
- Explain how normalizing a database to the third normal form affects database performance.
- Readings: Chapter 10
- Slides: Chapter 10 slides
- Online Lecture: Chapter 10 lecture
- Online Demo: Chapter 10 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 10 Project ,
due Thursday, November 17th at 5pm.
- Blackboard: Respond to the following question by Saturday, November 19th at 5pm:
When you are working with a client or designing a database for yourself, EER diagrams
can be a great asset. Discuss two advantages of creating and using an EER diagram.
Are there any disadvantages of using the EER diagrams? Explain your reasoning.
(Remember to respond to two other student post for full credit.)
- Have a wonderful fall break next week!
Week 14: (Monday, November 21- Sunday, November 27):
- Chapter 11: How to create databases, tables, and indexes
Applied Objectives:
- Given the design for a database, write the DDL statements to create the tables, constraints, and indexes that are required.
- Write a script that includes all of the DDL statements for creating the tables of a database.
- Use MySQL Workbench to work with the columns, data, constraints, and indexes, for a table.
Knowledge:
- Describe how each of these types of constraints restricts the values that can be stored in a table: not null, unique, primary key, and foreign key.
- Describe the difference between a column-level constraint and a table-level constraint.
- Describe the use of an index.
- Describe the use of a script for creating the tables of a database.
- Describe two character sets that are commonly used with MySQL and the pros and cons of each character set.
- Describe how a collation works with a character set.
- Describe two storage engines that are commonly used with MySQL and the pros and cons of each engine.
- Readings: Chapter 11
- Slides: Chapter 11 slides
- Online Lecture: Chapter 11 lecture
- Online Demo: Chapter 11 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 11 Project ,
due Thursday, November 24th at 5pm.
- Blackboard: Respond to the following question by Saturday, November 26th at 5pm:
Your text has given you much background on how to create and manipulate databases. Read the following
articles about the uses of databases:
Databases touch many aspects of our lives. Discuss some aspects about databases that you
find help in your everyday life. Discuss some aspects about databases that make you nervous
or concerned.
(Remember to respond to two other student post for full credit.)
Week 15: (Monday, November 28- Sunday, December 4):
- Note that November 28th is the last day to drop or withdraw from courses.
- Chapter 12: How to create views
Applied Objectives:
- Create and use views, including read-only and updatable views.
Knowledge:
- Describe a view.
- Describe the benefits of using views.
- Given a view, determine whether it is updatable.
- Describe the effect of the WITH CHECK OPTION clause on an updatable view.
- Readings: Chapter 12
- Slides: Chapter 12 slides
- Online Lecture: Chapter 12 lecture
- Online Demo: Chapter 12 Demo
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 12 Project,
due Thursday, December 1st at 5pm.
- Blackboard: Respond to the following question by Saturday, December 3rd at 5pm:
COMPLETE
(Remember to respond to two other student post for full credit.)
Week 16: (Monday, December 5 - Friday, December 9):
- Chapter 14: How to manage transactions and locking
Applied Objectives:
- Given a set of SQL statements to be combined into a transaction, write a script that begins, commits, and rolls back the transaction.
Knowledge:
- Describe the use of transactions.
- Describe the use of save points.
- Describe the way locking helps prevent concurrency problems.
- Describe the way the transaction isolation level affects concurrency problems and performance.
- Describe a deadlock.
- Describe three techniques that can reduce deadlocks.
- Readings: Chapter 14
- Slides: Chapter 14 slides
- Online Lecture: Chapter 14 lecture
- Activity: Enhance and manipulate the My Guitar Database.
Chapter 14 Project,
due Thursday, December 8th at 5pm.
- Blackboard: Respond to the following question by Saturday, December 8th at 5pm:
Throughout this course, we have learned much about databases. For your last discussion board
assignment, identify some of the facts about database that you found the most interesting.
With all of the information that you have learned, where will you take your database knowledge
next?
(Remember to respond to two other student post for full credit.)
Finals Week: (Saturday, December 10 - Friday, December 16):
- To help study for the quiz, remember that the end of each chapter in the book contains practice questions.
The solutions to these questions are provided by the publishers on the textbook website.
To see a list of solutions by chapter, you can also click on the provided
link.
- Quiz: Chapter 10, 11, 12, and 14, due Thursday, December 15th at 5pm.
Extensions
You may always complete your work ahead of time, extensions and rescheduling for assignments are only given when
circumstances beyond your control (e.g. being sick, academic, sports travel, etc)
prevent you from completing a project on time. You must notify me by
email providing documentation of your circumstances well in advance of the due date.
© Dr. Laura Goadrich, Pulaski Technical College