Course Content and Outcomes Guides (CCOG)

CCOG for CIS 275 Fall 2024


Course Number:
CIS 275
Course Title:
Data Modeling and SQL Introduction
Credit Hours:
4
Lecture Hours:
30
Lecture/Lab Hours:
0
Lab Hours:
30

Course Description

Introduces the design, uses, and terminology of a database management system. Includes data modeling using Entity Relationship modeling tools, normalization rules, relational database terminology, and program/query development. Prerequisites: CIS 125D or CIS 133J or CIS 133N or CIS 133W or CIS 133Y or CIS 195P or CS 161 or CS 161B or instructor permission. Audit available.

Intended Outcomes for the course

Upon completion of this course the student should be able to:

  • Describe and explain the importance of database development, processing, and security.
  • Design and prepare a data model using an Entity Relationship Diagram.
  • Communicate effectively with database professionals using relational database terminology.
  • Communicate effectively with database professionals regarding the rules of normalization.
  • Prepare queries to a database using Structured Query Language.

Outcome Assessment Strategies

  • Design and prepare a data model using an Entity-Relationship diagram and a Semantic Object Model diagram.
    • Apply technology to create data designs using both the E-R model and the Semantic Object model.
    • Data model should be a real business or personal database.
    • Data model should include at least 5 and not more than 8 strong entities/objects.
    • Data model should include 1:N and M:N relationships.
  • Prepare queries to a database using Structured Query Language.
    • Write queries using basic SQL including WHERE, built-in functions, subqueries, joins, LIKE, BETWEEN, EXISTS
  • In satisfying the assessments, students must dmonstrate written communication skills. Other assessment choices include:
    • Contextual written tasks in or outside of class.
    • Written case study analysis.
    • Individual or group projects.
    • Class presentations.
    • Quizzes and/or examinations.
    • In class interactive role-plays

Course Content (Themes, Concepts, Issues and Skills)

Describe and explain the importance of database processing and database development.

  • Use appropriate terminology to describe database management systems

Design and prepare a data model using an Entity-Relationship diagram and a Semantic
Object Model diagram.

  • Apply technology to create designs using both the E-R model and the Semantic Object model.

Communicate effectively with database professionals using relational database terminology.

  • Distinguish the basic components of a relational database

Communicate effectively with database professionals regarding the rules of normalization.

  • Distinguish the various normal forms leading to domain key normal form
  • Identify reasons for denormalization

Use the operations of relational algebra which form the foundation for data manipulation

  • Distinguish among union, intersection, difference, product, selection, projection, equi-joins and outer joins.

Prepare queries to a database using Structured Query Language.

  • Write queries using basic SQL including WHERE, built-in functions, subqueries, joins, LIKE, BETWEEN, EXISTS

Analyze the impact of database applications and learn the techniques for developing views, forms and reports.

  • Distinguish views and materialization of views in forms and reports
  • Assess the value of constraints and data integrity
  • Use features like drop-down lists, buttons, check boxes, special purpose keys

Describe the significance of multi-user databases and the need for backup/recovery and security of databases.

  • Identify the need for transaction processing, commit and rollback commands
  • Assess various locking features
  • Assess various security privileges

Analyze the effects of the Internet on databases in the client/server implementation.

  • Identify the 3 tier architecture and the roles of the various components
  • Assess the process of embedding SQL in other programming language

Related Instruction

Computation
Hours: 24

Outcomes:

1. Design and prepare a data model using an Entity-Relationship diagram.

2. Prepare queries to a database using Structured Query Language.

Direct instruction (+ study time) in discipline-related computations 

  • Interpreting business logic as a collection of Entities, Attributes, and Relationships between those Entities.
  • Identifying functional dependencies between Attributes of an Entity.
  • Applying the rules for key selection to identify candidate keys, primary keys, foreign keys, composite keys, and surrogate keys.
  • Determining the minimum and maximum cardinalities between Entities in a Relationship.
  • Explicitly tracking assumptions made during the process of creating Entities, Relationships, and cardinalities.
  • Resolving ternary and greater Relationships through the introduction of Intersection Entities.
  • Identifying 1:1, 1:N and M:N relationship cardinalities and resolving M:N into 1:N relationships by introducing Intersection Entities.
  • Representing Entities and Relationships by creating an Entity Relationship Diagram (ERD), using Crows-foot notation.
  • Representing Entities as Relations in Relational Algebra following an explicit formalism.
  • Correctly identifying foreign key-primary key relationships and representing them correctly in Relations.
  • Communicating ERD Relationships in English.
  • Applying the information contained in an ERD/set of Relations to the creation of Database Tables with suitable integrity constraints.
  • Applying the rules of Database Normalization in order to minimize the possibility of Insert/Update/Delete Anomalies.
  • Applying knowledge of the information contained in an ERD/set of Relations in order to formulate Database queries using SQL.
  • Estimating the expected number of results from a Database query in order to validate the results of a query.