CCOG for CIS 275 Winter 2025
- 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
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: 24Outcomes:
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.