Developing Windows-Based and Web-Enabled Information Systems Developing Windows-Based and Web-Enabled Information Systems Nong Ye Teresa Wu Boca Raton London New York CRC Press is an imprint of the Taylor & Francis Group, an informa business 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 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 v 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 Contents vii 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 INSERT ...........................................................................................165 8.3.3.2 UPDATE .........................................................................................167 8.3.3.3 DELETE ..........................................................................................167 8.3.4 SQL Data Manipulation Language: Relation Modification ...................168 8.3.4.1 ALTER TABLE ..............................................................................168 8.3.4.2 DROP TABLE ................................................................................169 8.4 Summary ....................................................................................................................169 9. MySQL ..................................................................................................................................177 9.1 Introduction ...............................................................................................................177 9.2 Get Ready to Work with MySQL ............................................................................178 9.2.1 MySQL Installation on Windows ..............................................................179 9.2.2 MySQL Installation on Mac OS X ..............................................................183 9.3 Working with MySQL Command Line Client ......................................................184 9.3.1 Data Types.....................................................................................................184 9.3.1.1 Numeric Data Types ....................................................................184 viii Contents 9.3.1.2 String (Character) Data Type ......................................................185 9.3.1.3 Date and Time (Temporal) Data Types .....................................185 9.3.1.4 NULL Value ..................................................................................186 9.3.2 Practice Data Definition Language in MySQL ........................................186 9.3.2.1 Statements for Database Operations .........................................186 9.3.2.2 Statements for Relation Operations ...........................................188 9.3.3 Practice Data Manipulation Language in MySQL ..................................189 9.3.3.1 Adding Records to a Table ..........................................................189 9.3.3.2 Querying Tables ...........................................................................190 9.3.3.3 Updating Tables ............................................................................190 9.3.3.4 Deleting Records ..........................................................................191 9.3.4 MySQL Transaction Control Language ....................................................191 9.3.5 MySQL Data Control Language ................................................................192 9.3.6 MySQL Utilities ............................................................................................193 9.3.6.1 SHOW Statement ..........................................................................193 9.3.6.2 DESCRIBE Statement ...................................................................194 9.3.6.3 HELP Statement ............................................................................194 9.4 Working with MySQL Workbench .........................................................................194 9.4.1 Data Modeling ..............................................................................................195 9.4.2 SQL Development ........................................................................................196 9.4.3 Server Administration ................................................................................197 9.5 Summary ....................................................................................................................198 10. Object-Based Database Systems ......................................................................................201 10.1 Object-Oriented Database Management System ..................................................201 10.1.1 Object-Oriented Database Concepts .........................................................201 10.1.1.1 Objects and Identities ..................................................................202 10.1.1.2 Complex Objects ...........................................................................202 10.1.1.3 Encapsulation ................................................................................202 10.1.1.4 Classes and Inheritance...............................................................203 10.1.1.5 Overloading, Overriding, and Late Binding ............................204 10.1.2 Object-Oriented Database Design and Modeling ...................................204 10.1.2.1 Object Modeling ...........................................................................204 10.1.2.2 Object Definition Language ........................................................206 10.1.2.3 Object Query Language ..............................................................207 10.1.3 Summary of OODBMS ................................................................................208 10.2 Object RDBMS ...........................................................................................................209 10.2.1 Object-Relational Model .............................................................................209 10.2.2 Object-Relational Query Language ...........................................................210 10.2.3 Summary of ORDBMS ................................................................................211 10.3 Summary ....................................................................................................................212 Section III Windows Application Development 11. Windows Forms and Controls in Microsoft Visual Studio .......................................217 11.1 Visual Basic Development Environment ...............................................................217 11.2 Windows Forms ........................................................................................................220 11.3 GUI Controls ..............................................................................................................223