Course Content and Outcomes Guides (CCOG)

CCOG for CIS 275 archive revision 201704

You are viewing an old version of the CCOG. View current version »

Effective Term:
Fall 2017 through Summer 2018

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 and Semantic Object modeling tools, normalization rules, relational database terminology, program/query development, multi-user database issues (including the Internet) and data administration. Audit available.

Intended Outcomes for the course

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

  • Describe and explain the importance of database processing and database development.
  • Design and prepare a data model using an Entity Relationship Diagram and a Semantic Object Model 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.
  • Analyze the impact of database applications and learn the techniques for developing views, forms and reports.
  • Describe the significance of multi-user databases and the need for backup/recovery and security of databases.
  • Analyze the effects of the Internet on databases in the client/server implementation.

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 and a Semantic Object Model 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.