Syllabus

New York City College of Technology/CUNY

Computer Systems Technology Department

 

CST1204 – Introduction to Databases 

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 are explained as well as the concepts related to relational design. The focus of the course is Structure Query Language (SQL), the language of relational database systems. Through hands-on experience both 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. 

 

2 class hours, 2 lab hours, 3 credits

Course Objectives:

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

1. Understand the role of a database in an IS, and the relationships databases have with other parts of the IS.
2. Understand the organization of the data in the RDB, the concepts of the table structure, the primary and the foreign keys.
3. Create tables according to a given design, including choosing data types for columns and declaring the column and the table constraints (primary key, foreign key, NOT NULL, CHECK). 
4. Populate tables with data and manipulate the data (create, update, delete and retrieve).
5. Program data retrieval queries, including:
   1. Select data from one table for various retrieval conditions.
   2. Select data from several tables with the help of joins or subqueries, and for various retrieval conditions.
   3. Perform aggregate calculations on data from one or several tables.
   4. Populate tables with data from other applications and export data to other applications (including spreadsheets).

 

Prerequisites:  

CST1100 Introduction to Computers, CST1101 Programming and Problem Solving 

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.

 

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 in The City University of New York and at New York City College of Technology and is punishable by penalties, including failing grades, suspension, and expulsion. The complete text of the College policy on Academic Integrity may be found in the catalog.

 

Grading:

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
• 40% – Four Exams
• 20% – Final Exam
• 30% – Four Homework Assignments

 

Course Outline: 

Week	Topic	Chapter
1	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	2
2-3	Creating Tables Introduction to Oracle Creating a table Dropping a table Using Data types Using nulls Viewing table Data	3
4	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 TEST ONE	6
5-6	SQL. Single Table Queries Simple Queries Retrieving certain 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 TEST TWO	4
7	Using Functions Using the COUNT function Using the SUM function Using the AVG, MAX and MIN functions Using the DISTINCT Operator	4
8-9	Grouping Using the GROUP BY clause Using a HAVING Clause Having vs. WHERE TEST THREE	4
10-12	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) TEST FOUR	5
13	Database Administration Creating and using views Using a view to update data Dropping a view Security Indexes System catalog Integrity constraints in SQL	7
14	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	8
15	Review and FINAL

 

Assessment criteria:

 

For the successful completion of this course a student should be able to:	Evaluation methods and criteria
1. Understand the role of a database in an IS.	1. Students will demonstrate on homework and exams what functions the database provides in an IS.
2. Utilize design techniques such as normalization and keys, to organize the data in the RDB	2. Students will demonstrate on homework, exams, and lab projects that they can specify the properties of the attributes of a table including the primary and foreign keys, and the required constraints.
3. Populate the tables of a database and manipulate the data (read, update, delete).	3. Students will perform projects using the chosen DBMS to create a database, populate it with data, and manipulate the data.
4. Perform calculations on data from one or more tables using the aggregate functions.	4. Students will perform lab assignments that require the use of aggregate functions for several calculations.
5. Write queries that join several tables.	5. Students will demonstrate on homework, exams, and lab projects that they can write queries joining several tables.
6. Demonstrate the ability to write subqueries.	6. Students will demonstrate on homework and exams the use of subqueries.
7. Import and export data from other applications.	7. Students will perform lab assignments that employ exporting/importing data from databases to other applications.

 

General Education Outcomes and Assessment:

 

Learning Outcomes	Assessment Method
SKILLS/Inquiry/Analysis Students will employ scientific reasoning and logical thinking.	Several programming assignments that will employ logical reasoning techniques learned.
SKILLS/Communication Students will communicate in diverse settings and groups, using written (both reading and writing), oral (both speaking and listening), and visual means	Group project that will employ both reading, writing and communication skills as well as interpersonal skills.
VALUES, ETHICS, RELATIONSHIPS / Professional/Personal Development   Students will work with teams, including those of diverse composition. Build consensus. Respect and use creativity.	Group project that will employ interpersonal skills.