Developing Windows-Based and Web-Enabled

Information Systems

Boca Raton London New York

CRC Press is an imprint of the Taylor & Francis Group, an informa business

Nong Ye Teresa Wu

Developing Windows-Based and Web-Enabled

Information Systems

CRC Press Taylor & Francis Group 6000 Broken Sound Parkway NW, Suite 300 Boca Raton, FL 33487-2742

© 2015 by Taylor & Francis Group, LLC CRC Press is an imprint of Taylor & Francis Group, an Informa business

No claim to original U.S. Government works Version Date: 20140716

International Standard Book Number-13: 978-1-4822-5387-0 (eBook – PDF)

This book contains information obtained from authentic and highly regarded sources. Reasonable efforts have been made to publish reliable data and information, but the author and publisher cannot assume responsibility for the valid- ity of all materials or the consequences of their use. The authors and publishers have attempted to trace the copyright holders of all material reproduced in this publication and apologize to copyright holders if permission to publish in this form has not been obtained. If any copyright material has not been acknowledged please write and let us know so we may rectify in any future reprint.

Except as permitted under U.S. Copyright Law, no part of this book may be reprinted, reproduced, transmitted, or uti- lized in any form by any electronic, mechanical, or other means, now known or hereafter invented, including photocopy- ing, microfilming, and recording, or in any information storage or retrieval system, without written permission from the publishers.

For permission to photocopy or use material electronically from this work, please access www.copyright.com (http:// www.copyright.com/) or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers, MA 01923, 978-750-8400. CCC is a not-for-profit organization that provides licenses and registration for a variety of users. For organizations that have been granted a photocopy license by the CCC, a separate system of payment has been arranged.

Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and are used only for identification and explanation without intent to infringe.

Visit the Taylor & Francis Web site at http://www.taylorandfrancis.com

and the CRC Press Web site at http://www.crcpress.com

v

Contents

Preface ……………………………………………………………………………………………………………………………xv Acknowledgments ……………………………………………………………………………………………………… xvii Authors ……………………………………………………………………………………………………………………….. xix Overview …………………………………………………………………………………………………………………….. xxi

Section I Foundation of Information Systems

1. Boolean Algebra and Digital Logic Circuits for Computer Hardware …………………… 3 1.1 Boolean Logic …………………………………………………………………………………………………. 3

1.1.1 Binary Values and Boolean Operators …………………………………………………. 3 1.1.2 Basic Properties of Boolean Algebra ……………………………………………………. 4 1.1.3 Conversion of a Truth Table for a Function into a Boolean Expression … 5

1.2 Digital Logic Circuits ……………………………………………………………………………………… 8 1.2.1 Digital Logic Gates ……………………………………………………………………………… 8 1.2.2 Combinational Circuits ……………………………………………………………………… 10 1.2.3 Sequential Circuits …………………………………………………………………………….. 13

1.3 Summary ………………………………………………………………………………………………………. 17

2. Digital Data Representation ………………………………………………………………………………….. 19 2.1 Representation of Numbers …………………………………………………………………………… 19

2.1.1 Conversion between Unsigned Binary and Decimal Numbers …………. 19 2.1.2 Representation of Signed Integers ……………………………………………………… 22

2.1.2.1 Signed Magnitude Method ………………………………………………….. 23 2.1.2.2 One’s Complement Method …………………………………………………. 23 2.1.2.3 Two’s Complement Method …………………………………………………. 25

2.1.3 Representation of Signed Floating Point Values ………………………………… 27 2.2 Representation of Alphabet and Control Characters ……………………………………… 27 2.3 Error Detection and Correction …………………………………………………………………….. 28 2.4 Summary ………………………………………………………………………………………………………. 32

3. Computer and Network System Software…………………………………………………………….. 33 3.1 The Operating System …………………………………………………………………………………… 33

3.1.1 Process Management …………………………………………………………………………. 33 3.1.2 Storage Management …………………………………………………………………………. 34 3.1.3 I/O Management ……………………………………………………………………………….. 35

3.2 Networking and Communication Software ………………………………………………….. 35 3.2.1 OSI Reference Model …………………………………………………………………………. 35 3.2.2 Transmission Control Protocol/Internet Protocol ……………………………… 36

3.3 Summary ………………………………………………………………………………………………………. 41

4. Overview of Information Systems ………………………………………………………………………… 43 4.1 Information System Concepts ……………………………………………………………………….. 43

vi Contents

4.2 The Role of Information System in Business …………………………………………………. 44 4.2.1 Transaction Processing System ………………………………………………………….. 47 4.2.2 Management Information System ……………………………………………………… 47 4.2.3 Decision Support System …………………………………………………………………… 47 4.2.4 Executive Information System and Strategic Information System ……… 47 4.2.5 Electronic Business and Electronic Commerce ………………………………….. 48 4.2.6 Mobile Commerce ……………………………………………………………………………… 48

4.3 Post-PC Information Age: Five Trends in the Future Information System Applications ………………………………………………………………………………………………….. 48 4.3.1 Mobile ……………………………………………………………………………………………….. 48 4.3.2 Social Media ……………………………………………………………………………………… 49 4.3.3 Big Data …………………………………………………………………………………………….. 49 4.3.4 Cloud Computing ……………………………………………………………………………… 50 4.3.5 Consumerization of IT ………………………………………………………………………. 50

4.4 Summary ………………………………………………………………………………………………………. 50

Section II Database Design and Development

5. Conceptual Data Modeling: Entity-Relationship Modeling ………………………………… 55 5.1 Types, Attributes, and Instances of Entities …………………………………………………… 55 5.2 Types, Attributes, Instances, and Degrees of Relationships between Entities ….. 59 5.3 Maximum and Minimum Cardinalities of a Relationship …………………………….. 60 5.4 Associative Entities ……………………………………………………………………………………….. 66 5.5 Weak Entities ………………………………………………………………………………………………… 71 5.6 Superclass and Subclass of Entities ……………………………………………………………….. 71 5.7 Summary ………………………………………………………………………………………………………. 76

6. Logical Database Design: Relational Modeling and Normalization …………………… 79 6.1 Relational Model of Database and Data Integrity Constraints ………………………. 79 6.2 Transformation of an E-R Model to a Relational Model …………………………………. 83

6.2.1 Transformation of Entities and Their Attributes………………………………… 83 6.2.2 Transformation of Superclass and Subclass Entities and Their

Attributes ………………………………………………………………………………………….. 86 6.2.3 Transformation of Relationships and Their Attributes ………………………. 86 6.2.4 Transformation of Association Entities ……………………………………………… 93 6.2.5 Transformation of Weak Entities ……………………………………………………….. 93

6.3 Normalization ………………………………………………………………………………………………. 96 6.3.1 Data Redundancy and Data Anomalies …………………………………………….. 96 6.3.2 Functional Dependency …………………………………………………………………….. 98 6.3.3 Normalization and Normal Forms ………………………………………………….. 100

6.4 Summary …………………………………………………………………………………………………….. 104

7. Database Implementation in Microsoft Access …………………………………………………… 109 7.1 Tables for Data Storage ………………………………………………………………………………… 109

7.1.1 Setting the Primary Key for a Table …………………………………………………. 109 7.1.2 Data Types of a Data Field ……………………………………………………………….. 110 7.1.3 Field Size Property of a Data Field …………………………………………………… 111

viiContents

7.1.4 Format Property of a Data Field ………………………………………………………. 113 7.1.5 Input Mask Property of a Data Field ………………………………………………… 113 7.1.6 Default Value Property of a Data Field …………………………………………….. 114 7.1.7 Validation Rule and Validation Text Properties of a Data Field………… 115 7.1.8 Required Property of a Data Field …………………………………………………… 115 7.1.9 Indexed Property of a Data Field ……………………………………………………… 115 7.1.10 Adding and Deleting Records of a Table………………………………………….. 115

7.2 Relationships of Tables ………………………………………………………………………………… 117 7.3 Queries for Data Retrieval …………………………………………………………………………… 120

7.3.1 Select Queries Using One Table ……………………………………………………….. 120 7.3.2 Select Queries with Joins of Multiple Tables …………………………………….. 123 7.3.3 Select Queries with Parameters and Calculated Fields …………………….. 126 7.3.4 Select Queries with Groupings of Records ………………………………………. 128 7.3.5 Crosstab Queries ……………………………………………………………………………… 132 7.3.6 Embedded Select Queries ………………………………………………………………… 134

7.4 Action Queries …………………………………………………………………………………………….. 140 7.5 Summary …………………………………………………………………………………………………….. 141

8. Structured Query Language ………………………………………………………………………………… 151 8.1 Introduction to SQL …………………………………………………………………………………….. 151 8.2 Backus–Naur Form ……………………………………………………………………………………… 152 8.3 SQL Syntax ………………………………………………………………………………………………….. 152

8.3.1 SQL Data Definition Language and Data Types ………………………………. 153 8.3.1.1 CREATE SCHEMA ……………………………………………………………. 153 8.3.1.2 CREATE VIEW ………………………………………………………………….. 154 8.3.1.3 CREATE DOMAIN ……………………………………………………………. 154 8.3.1.4 CREATE TABLE…………………………………………………………………. 155

8.3.2 SQL Data Manipulation Laguage: Data Queries ………………………………. 157 8.3.2.1 Basic Structure of the SELECT Command …………………………. 157 8.3.2.2 Aggregate Functions ………………………………………………………….. 160 8.3.2.3 Nested Subqueries……………………………………………………………… 162 8.3.2.4 JOIN Query ……………………………………………………………………….. 163

8.3.3 SQL Data Manipulation Language: Data Modification ……………………. 165 8.3.3.1 I