Skip to main content

Search Bar Icon Close Menu

Online classes

Effective: Late Fall 8-Week, 2018/2019

CISS 402: Advanced Database

Course Description

This course is a second course in database systems. Topics include: object-based models, storage architecture, data architecture, advanced SQL, NoSQL, transaction, performance tuning, distributed databases, security, social and ethical issues.

Prerequisite: CISS 202

Proctored Exams: Midterm and Final



Syllabus Contents

Textbooks

Required

  • Ricardo, Catherine M., and Susan Urban. (2017). Databases Illuminated (3). Burlington, MA: Jones & Bartlett Learning.
    • [ISBN-978-1-284-05694-5 ]
    • Note: This text comes with an access code to supplementary resources. These resources are optional, and are not required for course completion.

MBS Information

Textbooks for the course may be ordered from MBS Direct. You can order

For additional information about the bookstore, visit http://www.mbsbooks.com.


Course Overview

This course provides an in-depth look at relational databases. Topics include E-R Design, SQL, transaction control, database security, distributed databases and query optimization. 



Technology Requirements

Participation in this course will require the basic technology for all online classes at Columbia College:
  • A computer with reliable Internet access
  • A web browser
  • Acrobat Reader
  • Microsoft Office or another word processor such as Open Office

You can find more details about standard technical requirements for our courses on our site.


Course Learning Outcomes

  1. Demonstrate an in-depth knowledge of database systems.
  2. Apply database development knowledge to build a moderately complex business application using a high-level programming language.

Grading

Grading Scale
Grade Points Percent
A 900-1000 90-100%
B 800-899 80-89%
C 700-799 70-79%
D 600-699 60-69%
F 0-599 0-59%
Grade Weights
Assignment Category Points Percent
Discussions (8) 200 20%
Case Studies (3) 150 15%
Dropboxes (8) 200 20%
Quizzes (6) 150 15%
Exams (2) 300 30%
Total 1000 100%


Schedule of Due Dates

Week 1
Assignment Points Due
Discussion 1 25 Thursday/Sunday
Discussion: Introduction -- Sunday
Dropbox 1 25
Quiz 1 25
Week 2
Assignment Points Due
Discussion 2 25 Thursday/Sunday
Dropbox 2 25 Sunday
Case Study 1 50
Quiz 2 25
Proctor Information N/A
Week 3
Assignment Points Due
Discussion 3 25 Thursday/Sunday
Dropbox 3 25 Sunday
Quiz 3 25
Week 4
Assignment Points Due
Discussion 4 25 Thursday/Sunday
Dropbox 4 25 Sunday
Midterm 150
Week 5
Assignment Points Due
Discussion 5 25 Thursday/Sunday
Dropbox 5 25 Sunday
Case Study 2 50
Quiz 4 25
Week 6
Assignment Points Due
Discussion 6 25 Thursday/Sunday
Dropbox 6 25 Sunday
Quiz 5 25
Week 7
Assignment Points Due
Discussion 7 25 Thursday/Sunday
Dropbox 7 25 Sunday
Case Study 3 50
Quiz 6 25
Week 8
Assignment Points Due
Discussion 8 25 Thursday/Saturday
Dropbox 8 25 Saturday
Final 150
Total Points 1000

Assignment Overview

Discussions

Each week there is a discussion on different database concepts. Your initial post to the discussion is due each week by Thursday at 11:59 pm Central Time (CT). Each week you must also respond to at least two other students by Sunday at 11:59 pm CT, except in Week 8 when the deadline is Saturday at 11:59 pm.

Your initial post should be a substantial, original contribution that completely answers the question or addresses the topic. You responses should also substantively add to and extend the conversation by adding new points or raising new questions. All posts should be well formatted, free from grammatical and spelling errors, and posted by the deadlines.  All discussions are set so that you must post your initial response before you can view the responses of your peers.


Dropbox

The eight dropbox assignments include designing a database, writing SQL commands to create/alter a database, and optimizing database operations. Dropbox assignments will be due by Sunday at 11:59 pm CT of the week they are assigned, except in Week 8 where they will be due by Saturday at 11:59 pm CT.


Case Study

You will complete three case studies that take a business problem and design, create, or modify a relational database for optimal efficiency.  I recommend you use Visual Studio Community (Free Version).  Each Case Study will be due by Sunday at 11:59 pm CT of the week it is assigned.  


Quizzes

Each week, you will take a quiz over the weekly readings. You will have 90 minutes to complete the quiz and you will only receive one attempt. Each quiz is worth 25 points and will include 25 multiple choice questions. The quiz is due by 11:59 PM CT on Sunday of the week it is assigned.


Exams

Your midterm and final exams are proctored tests located in the Quizzes area of the course. It is your responsibility to arrange a proctor following the procedures outlined in the Content area of the course. Both exams are closed book and will include a combination of 50 multiple choice and 5 short answer questions over the concepts covered in weekly readings. You will have 2 hours to complete each exam.

Each exam, worth 150 points, will be available beginning Wednesday at 12:00 am CT and you must take the exam before 11:59 pm CT on the day it is due. The Midterm will cover readings from Weeks 1-4 and the Final will cover readings from Weeks 5-8.



Course Outline

Click on each week to view details about the activities scheduled for that week.

Week 1: UML, ERD, and object-relational databases
Learning Resources
  • Chapter 3
  • Chapter 7
  • Instructional Materials [Content area of the course]

Recommend: Reviewing Chapters 1 and 2

Discussion 1

What is an existence dependency between database entities? How do strong and weak entities differ? Provide an example of both strong and weak entities. Explain why your examples would be considered strong or weak entities.

Discussion: Introduction

Please post an introduction of yourself.  What is your background?  Do you have any work experience (either related to database design/development or unrelated)?  What do you hope to get out of the course? Anything else you would like us all to know about you?

Dropbox 1

A dentist’s office needs to keep information about patients, the number of visits they make to the office, work that must be performed, procedures performed during visits, charges and payments for treatment, and laboratory supplies and services. Assume there is only one dentist, so there is no need to store information about the dentist in the database. There are several hundred patients. Patients make many visits, and the database should store information about the services performed during each visit and the charges for each of the services. There is a standard list of charges, kept outside the database. The office uses three dental laboratories that provide supplies and services.

Draw an E-R diagram for this example.

Quiz 1

A 25 multiple-choice question quiz over required textbook readings. The quiz is available in the Quizzes area of the course.

Week 2: Relational Database Management Systems and SQL
Learning Resources
  • Chapter 5
  • Instructional Materials [Content area of the course]
Discussion 2

Triggers allow the DBMS to audit changes to the database as well as ensure the integrity of its data. Figure 5.6(B) in your text is an example of a trigger designed to update the total number of students enrolled in a class whenever another student enrolls.

For the Employee table below, write a trigger that will call a stored procedure which will create an audit trail that will track all updates to the salary field. The audit records will be written into the SalaryAudit table, as seen below. Explain the logic of each step of your trigger.

Employee (EmpID, lastName, firstName, department, salary, dateHired)

SalaryAudit (Date_of_update, EmpID, oldsalary, newsalary)

In your responses to other students, consider their logic for the trigger and how it compares to your own logic process.

Dropbox 2

Write the DDL commands needed to create a relational database from the schema shown below:

Barn (barnID, bName)

Owner (ownerID, pName, pPhone, pAddress)

RaceHorse (hNum, hName, gender, type, purchaseDate, purchasePrice, barnID)

Ownedby (hNum, ownerID)

Case Study 1

You have been charged with designing a database for a small-town residential real estate agency owned by Tom Smith. This agency is not associated with any other agencies.

Properties to be offered for sale are visited by an agent (the listing agent) who collects information. Once listed, the property can be sold by any agent (the selling agent). However, when a binder (a declaration of a buyer’s intention to purchase) and a deposit are placed on the property, no agent can show the property to another prospective buyer. If no contract follows within a month, or if the buyer retracts the bid, the property becomes available again. If the sale goes through and a contract is signed by all parties, the binder status and the listing status are marked as sold, and the contract date is recorded in the binder table. At the end of each month, the listing and binder information for all houses sold that month are removed from their corresponding tables, and the information is put in the Sold table. Prospective buyers can register with only one agent. When the property is sold, the commission is divided between the listing agent and the selling agent, who can be the same person. The commission is always 10% of the selling price.

For each property, be sure to track such things as address, type, style, asking price, date the property was listed, owner name, owner phone number, size of house, number of bedrooms, number of bathrooms, number of floors, features, status, and listing agent.

  1. Draw a complete E-R Diagram for this real estate agency.
  2. Write the necessary SQL statement(s) for this business rule: If a binder (buyer's declaration of intent to purchase) and a deposit are placed on a property, the property cannot be shown to another potential buyer for one month. This information needs to be up-to-date at all times, ensuring that all agents viewing the database are aware if a property is no longer available. Hint: You will need both a trigger and stored procedure.

 

Quiz 2

A 25 multiple-choice question quiz over required textbook readings. The quiz is available in the Quizzes area of the course.

Proctor Information
Submit your proctor form to the appropriate Dropbox folder by the end of the week. Remember to “Save” the form before placing it in Dropbox. See the Content area for more information.
Week 3: Database Security
Learning Resources
  • Chapter 8
  • Instructional Materials [Content area of the course]
Discussion 3

The CIA model of information security is a very fundamental concept in security. Ensuring the three sides of the CIA model is protected is an important step in designing a secure system. Do you feel the CIA model provides enough security for today’s systems? Why or why not? Provide an example that supports your belief. 

Dropbox 3

For each of the following, write SQL statements to create views where needed and to grant the indicated privileges for the University database with this schema:

Student (stuId, lastName, firstName, major, credits)
Faculty (facId, name, department, rank)
Class (classNumbe, facId, schedule, room)
Enroll (stuId, classNumber, grade)


  1. Give permission to read the tables Student and Class to user 201.
  2. Create a view of Enroll that does not include the grade attribute, and give user 201 permission to read and update the view.
  3. Create a role that includes reading Student, Class, and the view created in 2. Give that role to all clerks in the Deans office, which includes users 202, 203, 204, and 205.
  4. Give permission to user 206, an assistant dean, to read and modify (insert, delete, update) the Faculty and Class tables. This user can authorize others to read and modify Class but not Faculty.
  5. User 206 authorizes user 300 to read Class. Write the command to do this
Quiz 3

A 25 multiple-choice question quiz over required textbook readings. The quiz is available in the Quizzes area of the course.

Week 4: Transaction Management
Learning Resources
  • Chapter 9
  • Instructional Materials [Content area of the course]
Discussion 4

What options does a DBMS have to handle concurrent execution of transactions in a multi-user database? Which option do you feel is best, and why?

Dropbox 4

Assume the following transactions are to be performed:

Transaction S:

read(a);
a=a+10;
write(a);
read(b);
b=b*5;
write(b);

Transaction T:

read(a);
a=a*2;
write(a);

  1. If the initial value of a is 10 and the initial value of b is 20, what are their final values if we perform the transactions serially, using order S,T?
  2. Using the same initial values, what are the final values of a and b if the order of execution is T,S?
  3. Does this result have any implications for serializability?
  4. Write a concurrent schedule for transactions S and T that illustrates the lost update problem.
  5. Apply the standard two-phase locking protocol to the schedule you devised in step 4. Will the protocol allow the execution of that schedule? Does deadlock occur?
Midterm

An exam over Weeks 1 - 4, consisting of 50 multiple-choice and 5 short answer questions. The exam is available in the Quizzes area of the course.

Week 5: Distributed Databases
Learning Resources
  • Chapter 10
  • Instructional Materials [Content area of the course]
Discussion 5

You have been charged with designing a distributed database system for a bank that has multiple branches in multiple small towns separated by hundreds of miles in a rural area. What architecture would you recommend? Why?

Dropbox 5

Use the data distribution scheme for a distributed University example [available in the course] to answer the following questions: 

  1. Describe two strategies for this query:
    1. Find the ClassNumber and the names of all students enrolled in all classes with schedule MWF9
  2. Describe two strategies for this query:
    1. Find the ClassNumber, facId of the teacher, and the campus of all classes being taken by RosemaryHughes, who is a student at the North campus.
  3. Consider the query “Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus.” The query is entered at the North campus.
    1. Describe a strategy that does not use the semijoin
  4. Consider the query “Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus.” The query is entered at the North campus.
  5. Describe a strategy that does use a semijoin
Case Study 2
Use the E-R Diagram you created for Case Study 1 to create a database for the real estate agency.  Add sample data (a minimum of 10 records in each table) to your database.
Quiz 4

A 25 multiple-choice question quiz over required textbook readings. The quiz is available in the Quizzes area of the course.

Week 6: Big Data and NoSQL
Learning Resources
  • Chapter 12
  • Instructional Materials [Content area of the course]
Discussion 6

This chapter looks at the five Vs of big data. Which of the five do you think is most important? Why? Which of the five do you think is least important? Why?

Dropbox 6

Select multiple Hadoop, NoSQL, and NewSQL products and conduct a detailed comparison of the similarities and differences between the tools that you select. For NoSQL products, include key-value pair systems, column-oriented systems, document-oriented systems, and graph-oriented systems. How would you determine which tool is the most appropriate to use for a given big data application?

Quiz 5

A 25 multiple-choice question quiz over required textbook readings. The quiz is available in the Quizzes area of the course.

Course Evaluation
Please evaluate the course. You will have an opportunity to evaluate the course near the end of the session. A link sent to your CougarMail will allow you to access the evaluation. Please note that these evaluations are provided so that I can improve the course, find out what students perceive to be its strengths and weaknesses, and in general assess the success of the course. Please do take the time to fill this out.
Week 7: Relational Query Optimization
Learning Resources
  • Chapter 13
  • Instructional Materials [Content area of the course]
Discussion 7

What are database statistics, and why are they important? How are they obtained? Which optimizer statistics do you feel are most important for efficient database performance? Why?

Dropbox 7

Assume we have the following information about a University database:

  • All tables are stored in packed form in blocks of length 4096 bytes.
  • Student has 10,000 tuples, each 200 bytes long. It is hashed on stuId, the primary key, and has a secondary non-clustered index on lastName, with three levels. There are 8000 values for lastName, 2000 values for firstName, 25 values for major, and 150 values for credit.
  • Faculty has 800 tuples of length 100 bytes. It has an index on facId, the primary key, with two levels. There is a secondary non-clustered index on department, with one level. facName has 650 values, department has 25 values, and rank has 4 values.
  • Class has 2500 tuples with length of 100 bytes. It is hashed on classNumber, the primary key, and has no secondary indexes. facId has 700 values here, schedule has 35 values, and room has 350 values.
  • Enroll has 50,000 tuples with length of 100 bytes. It has a composite index on the primary key, {classNumber, stuId}, with four levels, and it has no other index. The attribute grade has 10 values.

Find the read cost only (no writing costs) of Class |x| Enroll using nested loops with:

  • Class as the outside loop and buffer size of only two blocks
  • Enroll as the outside loop and buffers size of two blocks
  • Class as the outside loop and buffer size of 10 blocks
  • Enroll as the outside loop and buffer size of 10 blocks
Case Study 3

A multiple listing service is a cooperative service in which different real estate agencies agree to list and show one another’s properties for sale, dividing the sales commission. The Smith Real Estate Agency has decided to join with other real estate agencies in neighboring towns and use a multiple listing service. Assume the following table layouts for their database:

AgencySalesOffice (saleofficename, address, numberOfAgents, generalTelephone)

Agent (agentID, agentName, salesofficename, agentTelephone)

ActiveListing (listID, address, type, style, size, askingPrice, dataListed, ownerName, ownerTelephone, lotSize, houseSize, numBedrooms, numBaths, numFloors, features, status, listing_agentID)

Sold (listID, selling_agentID, buyerID, sellingPrice, dateOfContract)

ProspectiveBuyer (buyerID, name, address, telephone, typeWanted, styleWanted, sizeWanted, bedroomsWanted, highestPrice, specialRequests, agentID)

Binder (listID, buyerID, sellingPrice, dateBid, dateAccepted, amountDeposit, status, dateCancelled, dateOfContract)

  1. Choose a data distribution plan for this data and justify your choice, using the criteria listed in Figure 10.5 of your textbook.
  2. Write a fragmentation schema as follows: Decide how the data should be fragmented to fit the distribution plan. Write the SQL commands you would use to create the fragments. For each fragment, identify the location(s) where it will be stored.
  3. Explain how the following query would be answered (include the SQL command): Find the names and addresses of all prospective buyers and their agents for a new listing, which is a colonial style residence with four bedrooms and an asking price of $800,000.
  4. Explain how the following query would be answered (include the SQL command): Find all the houses that are suitable to show a prospective buyer who is interested in a two-story residence with three bedrooms and two bathrooms that costs under $500,000. For each house, show all the listing information, plus the name and telephone number of the listing agent.
  5. Explain how the following query would be answered (include the SQL command): For all houses sold last month for which Jane Hayward was the listing agent, find the name of the selling agent.
Quiz 6

A 25 multiple-choice question quiz over required textbook readings. The quiz is available in the Quizzes area of the course.

Week 8: Social and Ethical Issues in Computing
Learning Resources
  • Chapter 15
  • Instructional Materials [Content area of the course]
Discussion 8

Assume you have a position as a database administrator in a large corporation. The company has been collecting data about employees, including monitoring their working habits by recording their keystrokes, timing their telephone interactions with clients, and scanning their email for personal correspondence. As DBA, you are asked to help set up a record-keeping system to store such data. Does the company have a legal right to perform this kind of monitoring? What is your professional responsibility in this situation? How would you personally feel about this situation?

Dropbox 8

Assume you and a group of friends have developed an innovative piece of software for which you wish to protect your intellectual property rights. Identify the mechanism you would choose, and describe what you would need to do (if anything) to establish your ownership and enforce your ownership of the software. Would your rights be recognized in other countries? If not, is there any mechanism you can use to enforce them overseas?

Final

An exam over Weeks 5-8, consisting of 50 multiple-choice and 5 short answer questions. The exam is available in the Quizzes area of the course.



Course Policies

Student Conduct

All Columbia College students, whether enrolled in a land-based or online course, are responsible for behaving in a manner consistent with Columbia College's Student Conduct Code and Acceptable Use Policy. Students violating these policies will be referred to the office of Student Affairs and/or the office of Academic Affairs for possible disciplinary action. The Student Code of Conduct and the Computer Use Policy for students can be found in the Columbia College Student Handbook. The Handbook is available online; you can also obtain a copy by calling the Student Affairs office (Campus Life) at 573-875-7400. The teacher maintains the right to manage a positive learning environment, and all students must adhere to the conventions of online etiquette.

Plagiarism

Your grade will be based in large part on the originality of your ideas and your written presentation of these ideas. Presenting the words, ideas, or expression of another in any form as your own is plagiarism. Students who fail to properly give credit for information contained in their written work (papers, journals, exams, etc.) are violating the intellectual property rights of the original author. For proper citation of the original authors, you should reference the appropriate publication manual for your degree program or course (APA, MLA, etc.). Violations are taken seriously in higher education and may result in a failing grade on the assignment, a grade of "F" for the course, or dismissal from the College.

Collaboration conducted between students without prior permission from the instructor is considered plagiarism and will be treated as such. Spouses and roommates taking the same course should be particularly careful.

All required papers may be submitted for textual similarity review to Turnitin.com for the detection of plagiarism. All submitted papers may be included in the Turnitin.com reference database for the purpose of detecting plagiarism. This service is subject to the Terms and Conditions of Use posted on the Turnitin.com site.

Non-Discrimination

There will be no discrimination on the basis of sex, race, color, national origin, sexual orientation, religion, ideology, political affiliation, veteran status, age, physical handicap, or marital status.

Student Accessibility Resources

Students with documented disabilities who may need academic services for this course are required to register with the office of Student Accessibility Resources. Until the student has been cleared through this office, accommodations do not have to be granted. If you are a student who has a documented disability, it is important for you to read the entire syllabus as soon as possible. The structure or the content of the course may make an accommodation not feasible. Student Accessibility Resources is located in Student Affairs in AHSC 215 and can be reached by phone at (573) 875-7626 or email at sar@ccis.edu.

Online Participation

You are expected to read the assigned texts and participate in the discussions and other course activities each week. Assignments should be posted by the due dates stated on the grading schedule in your syllabus. If an emergency arises that prevents you from participating in class, please let your instructor know as soon as possible.

Attendance Policy

Attendance for a week will be counted as having submitted any assigned activity for which points are earned. Attendance for the week is based upon the date work is submitted. A class week is defined as the period of time between Monday and Sunday (except for week 8, when the work and the course will end on Saturday at midnight.) The course and system deadlines are based on the Central Time Zone.

Cougar Email

All students are provided a CougarMail account when they enroll in classes at Columbia College. You are responsible for monitoring email from that account for important messages from the College and from your instructor. You may forward your Cougar email account to another account; however, the College cannot be held responsible for breaches in security or service interruptions with other email providers.

Students should use email for private messages to the instructor and other students. The class discussions are for public messages so the class members can each see what others have to say about any given topic and respond.

Late Assignment Policy

An online class requires regular participation and a commitment to your instructor and your classmates to regularly engage in the reading, discussion and writing assignments. Although most of the online communication for this course is asynchronous, you must be able to commit to the schedule of work for the class for the next eight weeks. You must keep up with the schedule of reading and writing to successfully complete the class.

Late discussion posts, assignments, quizzes, and exams will not be accepted 

Course Evaluation

You will have an opportunity to evaluate the course near the end of the session. A link will be sent to your CougarMail that will allow you to access the evaluation. Be assured that the evaluations are anonymous and that your instructor will not be able to see them until after final grades are submitted.

Proctor Policy

Students taking courses that require proctored exams must submit their completed proctor request forms to their instructors by the end of the second week of the session. Proctors located at Columbia College campuses are automatically approved. The use of ProctorU services is also automatically approved. The instructor of each course will consider any other choice of proctor for approval or denial. Additional proctor choices the instructor will consider include: public librarians, high school or college instructors, high school or college counseling services, commanding officers, education service officers, and other proctoring services. Personal friends, family members, athletic coaches and direct supervisors are not acceptable.


Additional Resources

Orientation for New Students

This course is offered online, using course management software provided by Desire2Learn and Columbia College. The course user guide provides details about taking an online course at Columbia College. You may also want to visit the course demonstration to view a sample course before this one opens.

Technical Support

If you have problems accessing the course or posting your assignments, contact your instructor, the Columbia College Helpdesk, or the D2L Helpdesk for assistance. Contact information is also available within the online course environment.

Online Tutoring

Smarthinking is a free online tutoring service available to all Columbia College students. Smarthinking provides real-time online tutoring and homework help for Math, English, and Writing. Smarthinking also provides access to live tutorials in writing and math, as well as a full range of study resources, including writing manuals, sample problems, and study skills manuals. You can access the service from wherever you have a connection to the Internet. I encourage you to take advantage of this free service provided by the college.

Access Smarthinking through CougarTrack under Students -> Academics -> Academic Resources.


+

Request info