Course Information

Course Number: CST1204

Course Title: Database Systems Fundamentals

Course Description:

In this course, students are introduced to the concepts and characteristics of relational database systems. The organization of data within relational databases, including normalization and integrity constraints, and the concepts related to relational design are explained. The focus of the course is Structure Query Language (SQL), the language of relational database systems. Through hands-on experience in class and off-campus, SQL is practiced, concepts are reinforced, and students gain proficiency in using SQL to code and maintain data in relational tables. In addition, students gain proficiency in manipulating relational data using an industry-standard relational database system.

Credits / Hours: 2 Class Hours, 2 Lab Hours, 3 Credits

Section Number:

Pre/Co-requisites: CST1100 Introduction to Computers and CST1101 Programming and Problem Solving

[back to top]

Course Objectives

Upon successful completion of the course, the student should be able to:

  • Understand the role of a relational database in an Information Systems domain
  • Apply relational design techniques necessary for data organization in RDBs, including normalization and the use of keys.
  • Use DDL code to create, alter and drop relational tables.
  • Use DML code to select, insert, update, and delete data in relational tables.
  • Use the various features of the SELECT command to effectively execute multiple relational functions, including:
    • The retrieval of relational data from one table for various retrieval conditions.
    • The retrieval of relational data from several tables with the help of joins or subqueries and various retrieval conditions.
    • The execution of aggregate calculations on data from one or several tables.
    • To populate tables with data from other applications and export data to other applications (including spreadsheets).

[back to top]

General Education Outcomes:

  • SKILLS/Inquiry/Analysis: Students will employ scientific reasoning and logical thinking.
  • SKILLS/Communication: Students will communicate in diverse settings and groups, using written (both reading and writing), oral (both speaking and listening), and visual means
  • VALUES, ETHICS, RELATIONSHIPS / Professional/Personal Development: Students will work with teams, including those of diverse composition. Build consensus. Respect and use creativity.

[back to top]

Required Materials

  • Pratt, Philip J., and Mary Z. Last. 2009. A Guide to SQL, 9th edition. Boston: Cengage Learning. ISBN-13: 978-1-111-52727-3. ISBN-10: 1-111-52727-X
  • Students are encouraged to have a USB storage device for class projects.

[back to top]

Schedule

Week Topic Chapter
1 Topic One: Introduction to database concepts Relational databases

·       Entities Attributes and Relationships

·       Functional Dependence

·       Primary keys

·       Database Design

·       Design method

·       Database design requirements

·       Database design process example

·       Normalization (first, second and third normal form)

·       Diagrams for database design

Topic One: Introduction to database concepts Relational Databases Supplemental Resources

 

 2
2-3 Topic Two: Creating Tables

  • Introduction to Oracle
  • Creating a table
  • Dropping a table
  • Using Data types
  • Using nulls
  • Viewing table Data

Topic Two: Creating Tables Supplemental Resources

 

 3
4 Topic Three: Updating Data

  • Changing existing data in a table
  • Adding a new row to an existing table
  • Deleting a row from an existing table
  • Adding a new column in an existing table
  • Changing the structure of an existing column
  • Changing a value in a column to null
  • Dropping a table

Topic Three: Updating Data Supplemental Resources

TEST ONE

 6
5-6 Topic Four: SQL Single Table Queries

Simple Queries

  • Retrieving specific columns and all rows
  • Retrieving all columns and all rows
  • Using the WHERE Clause
  • Using compound conditions
  • Using the BETWEEN Operator
  • Using computed columns
  • Using the LIKE operator
  • Using the IN Operator
  • Nulls

Sorting

  • Using the ORDER BY clause
  • Additional Sorting Options

Topic Four: SQL Single Table Queries Supplemental Resources

TEST TWO

 4
7 Topic Five: Using Functions

·       Using the COUNT function

·       Using the SUM function

·       Using the AVG, MAX, and MIN functions

·       Using the DISTINCT Operator

Topic Five: Using Functions Supplemental Resources

 4
8-9 Topic Six: Grouping

·       Using the GROUP BY clause

·       Using a HAVING Clause

·       Having vs. WHERE

Topic Six: Grouping Supplemental Resources

TEST THREE

 4
10-12 Topic Seven: Multiple Table Queries

Querying Multiple Tables

  • Joining Two tables

Comparing joins, IN and EXISTS

  • Using the IN operator
  • Using the EXISTS Operator
  • Using a Subquery within a subquery
  • Using alias
  • Joining a table to itself
  • Using a self join on a primary key column
  • Joining several tales
  • ALL and ANY
  • Special Operations (Inner and outer join and product)

Topic Seven: Multiple Table Queries Supplemental Resources

TEST FOUR

 5
13 Topic Eight: Database Administration

  • Creating and using views
  • Using a view to update data
  • Dropping a view
  • Security
  • Indexes
  • System catalog
  • Integrity constraints in SQL

Topic Eight: Database Administration Supplemental Resources

 7
14 Topic Nine: SQL Functions and Procedures

  • Using SQL in a programming environment
  • Using functions
  • Concatenating columns
  • Stored procedures
  • Error handling
  • Using update procedures
  • Selecting multiple rows with a procedure

Topic Nine: SQL Functions and Procedures Supplemental Resources

 8
15 Review and FINAL

[back to top]

Grading Policy

Passing grades are given only if all assignments are completed. The professor reserves the right to ask you to defend any of your assignments or tests. Your final grade is based on the following:

  • 10% – Participation (online discussion topics)
  • 40% – Four Exams
  • 20% – Final Exam
  • 30% – Four Homework Assignments
Letter Grade A A- B+ B B- C+ C D F
Numerical Grade 93-100 90-92.9 87-89.9 83-86.9 80-82.9 77-79.9 70-76.9 60-69.9 <=59.9

[back to top]

Class Etiquette & Netiquette

You are expected to show respect both in the classroom and online for peers and instructors regardless of their race, gender identity and presentation, ethnicity, national origin, religion, cultural identity, socioeconomic background, sexuality, sexual orientation, ability, neurodivergence, age, etc.

[back to top]

Plagiarism 

Please review City Tech’s Academic Integrity Policy Manual found here and in the City Tech catalog, which outlines the College’s Academic Integrity Policy here. All work for this class will be processed through the SafeAssign within Blackboard. This feature detects plagiarism. If any work is tagged as plagiarized, the work will receive a zero grade and be turned over to the Chair of the CST Department for further academic disciplinary action.

[back to top]

City Tech Academic Integrity Policy

Students and all others who work with information, ideas, texts, images, music, inventions, and other intellectual property owe their audience and sources accuracy and honesty in using, crediting, and citing sources. As a community of intellectual and professional workers, the College recognizes its responsibility for providing instruction in information literacy and academic integrity, offering models of good practice, and responding vigilantly and appropriately to infractions of academic integrity. Accordingly, academic dishonesty is prohibited at The City University of New York and New York City College of Technology and is punishable by penalties, including failing grades, suspension, and expulsion.

[back to top]

Diversity Statement

This course welcomes students from all backgrounds, experiences, and perspectives. In accordance with the City Tech and CUNY missions, this course intends to provide an atmosphere of inclusion, respect, and the mutual appreciation of differences so that together we can create an environment where all students can flourish. It is the instructor’s goal to provide materials and activities that are welcoming and accommodating of diversity in all of its forms, including race, gender identity and presentation, ethnicity, national origin, religion, cultural identity, socioeconomic background, sexuality, and sexual orientation, ability, neurodivergence, age, etc. Your instructor is committed to equity and actively seeks ways to challenge institutional racism, sexism, ableism, and other forms of prejudice. Your input is encouraged and appreciated. If a dynamic you observe or experience in the course concerns you, you may respectfully inform your instructor without fear of how your concerns will affect your grade. Let your instructor know how to improve the effectiveness of the course for you personally or for other students or student groups. We acknowledge that NYCCT is located in the traditional homelands of the Canarsie and Lenape peoples.

[back to top]

Disability/Medical Accommodations Statement

City Tech is committed to supporting the educational goals of enrolled students with disabilities in the areas of enrollment, academic advisement, tutoring, assistive technologies, and testing accommodations. If you have or think you may have a disability, you may be eligible for reasonable accommodations or academic adjustments as provided under applicable federal, state, and city laws. You may also request services for temporary conditions or medical issues under certain circumstances.

If you have questions about your eligibility or would like to seek accommodation services or academic adjustments, please contact the Center for Student Accessibility at 300 Jay Street, room L-237, (718) 260-5143 or http://www.citytech.cuny.edu/accessibility/

Students who miss a scheduled presentation or exam due to illness or medically-related emergencies will be referred to the Center for Students Accessibility. The CSA will review any documentation requested and give the student a letter to share with the relevant instructor if accommodations need to be made.

[back to top]

Course Resources

[back to top]

College Resources

[back to top]