IS-230 Database Design

This course covers the fundamentals of database and its design. Fundamentals of database include the advantages of relational database compared to flat-file database, hierarchy of data (e.g. field, record, table), types of relationship among tables and SQL (Structured Query Language). On database design topics include normalization, data modeling using conceptual model (e.g. ERD) and logical model.

• Prerequisite Course(s): IS 201, MS100 with grade of ‘C’ or higher

A. PROGRAM LEARNING OUTCOMES (PLOs)
The student will be able to:

1. Demonstrate an in-depth understanding of technical concepts and ethical issues pertaining to information systems
2. Demonstrate theoretical knowledge and practical skills in the management and strategic use of information systems and technology.
3. Demonstrate proficiency in the use of different software applications significant to manipulating and analyzing information as well as generating and presenting reports in the various functional areas of business.
4. Demonstrate solid foundation skills in database design and management, web engineering, programming, and networking
5. Demonstrate the ability to adapt to latest technologies using their foundation knowledge and skills from CIS.

B. STUDENT LEARNING OUTCOMES (SLOs) - GENERAL:
The student will be able to:

1. Differentiate flat-file from relational database.
2. Identify the Hierarchy of Data, its difference and as well as its relationship from one another.
3. Identify different type of table relationships.
4. Demonstrate the ability to use important SQL construct to access or manipulate a database.
5. Design a conceptual model for database using ERD.
6. Demonstrate understanding of Normalization.
7. Create an actual database using a conceptual model and normalization concepts.

SLO	PLO1	PLO2	PLO3	PLO4	PLO5
1				D	I
2				I
3				I,D
4			I	IDM
5				IDM
6				ID
7	I	I	D	M	D

I = Introduced
D = Demonstrated
M = Mastered

C. STUDENT LEARNING OUTCOMES (SLOs)-SPECIFIC:

The student will be able to:

General SLO1. Differentiate flat-file from relational database.

Student Learning Outcomes	Assessment strategies
1.1 Identify which software is a flat-file type of database and a relational one.	End-of-Unit Test
1.2 Explain the key advantages of relational database over flat-file.	End-of-Unit Test

General SLO2. Identify the Hierarchy of Data, its difference and as well as its relationship from one another.

Student Learning Outcomes	Assessment Strategies
2.1 Explain the relationship and difference among the hierarchy of data.	Graded Exercise, End-of-Unit Test

General SLO3. Identify different type of table relationships.

Student Learning Outcomes	Assessment Strategies
3.1 Describe the three different types of table relationships along with examples.	Non-Graded Exercise, End-of-Unit Test
3.2 Create an actual one-to-many relationship on database-management software.	Graded Exercise

General SLO4. Demonstrate the ability to use important SQL construct to access or manipulate a database.

Student Learning Outcomes	Assessment Strategies
4.1 Identify the basic syntax for T-SQL commands.	Graded Exercise, End-of-Unit Test
4.2 Demonstrate the usage of SELECT, UPDATE and INSERT commands.	Graded Exercise, End-of-Unit Test
4.3 Develop a T-SQL command based on a given scenario.	Graded Exercise, End-of-Unit Test
4.4 Implement different T-SQL commands on various RDBMS software.	Graded Exercise

General SLO#5: Design a conceptual model for database using ERD.

Student Learning Outcomes	Assessment Strategies
6.1 Explain the three basic forms of normalization.	Graded Exercise
6.2 Convert an ERD into Normalized form.	Graded Exercise

General SLO7. Create an actual database using a conceptual model and normalization concepts.

Student Learning Outcomes	Assessment Strategies
7.1 Demonstrate the use of an RDMBS to convert a normalized conceptual model into an actual database.	Graded Exercise
7.2 Create a relationship among normalized tables.	Graded Exercise
7.3 Demonstrate reliability of design by an actual entry of data.	Graded Exercise
7.4 Create friendly user-interface for created database.	Graded Exercise
7.5 Generate reports from an actual database.	Graded Exercise

D. COURSE CONTENT

1. Course Overview

2. Introduction to Database

• Database in our everyday lives
• History of Database
• Hierarchy of Data
• Character
• Field
• Record
• Table
• Database
• Flat File vs. Relational Databases

3. Introduction to Database Design

• Three types of Table relationship
• Primary keys, Foreign keys and Candidate keys
• Referential Integrity
• Conceptual Modeling using ERD
• Normalization
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)

4. Introduction to T-SQL (Transact-Structure Query Language)

• SELECT command
• INSERT command
• UPDATE command
• DELETE command

5. Creating Database in MSAccess, mySQL et al

• Creating Tables
• Creating Relationship
• Creating Queries
• Creating Forms
  • Designing Forms
  • Adding Controls
  • Adding Sub-forms
• Creating Reports
• Creating Switchboard Manager

E. METHODS OF INSTRUCTION
Hands-on Activity
PowerPoint Presentation
Lecture/Guided Practice

F. REQUIRED COURSE MATERIALS

Pratt, Philip J. (2012). Concepts of Database Management 7th Edition, United States: Boston, MA (Course Technology) (or most recent edition).

USB Flash-Drive

G. REFERENCE MATERIALS
Grauer, Robert (2008). Microsoft Office Access 2007 Comprehensive, United States: New Jersey Prentice Hall (or most recent edition).
Hernandez, Michael J (2003). Database Design for Mere Mortals 2nd edition
Boston, MA; Pearson Education, Inc. (or most recent edition).

H. INSTRUCTIONAL COSTS
External Drive for student projects

I. EVALUATION
Student must pass the course with a Grace of ‘C’ or better
J. CREDIT BY EXAMINATION
None