DATABASE MANAGEMENT SYSTEMS

1 DATABASE MANAGEMENT SYSTEMSThis page intentionally left blankDATABASE MANAGEMENTSYSTEMS: A BUSINESS-ORIENTED APPROACH USINGORACLE, MySQL, ANDMS ACCESSSOTIRIOS ZYGIARISU niversity of Maryland, USA and PMU University, KSAU nited Kingdom North America Japan India Malaysia ChinaEmerald Publishing LimitedHoward House, Wagon Lane, Bingley BD16 1WA, UKFirst edition 2018 Copyrightr2018 Emerald Publishing LimitedReprints and permissions part of this book may be reproduced, stored in a retrieval system , transmitted inany form or by any means electronic, mechanical, photocopying, recording orotherwise without either the prior written permission of the publisher or a licencepermitting restricted copying issued in the UK by The Copyright Licensing Agencyand in the USA by The Copyright Clearance Center.

2 Any opinions expressed in thechapters are those of the authors. Whilst Emerald makes every effort to ensure thequality and accuracy of its content, Emerald makes no representation implied orotherwise, as to the chapters suitability and application and disclaims any warranties,express or implied, to their Library Cataloguing in Publication DataA catalogue record for this book is available from the British LibraryISBN: 978-1-78756-696-5 (Print)ISBN: 978-1-78756-695-8 (Online)ISBN: 978-1-78756-697-2 (Epub)DBMS used in the bookORACLE 11g APEX DB (formerly MySQL) from XAMPP Access 2013 Screenshots from oracle 11g Application Express Copyright oracle , used withpermission by oracle Number 1985 ISO 14001 ISOQAR certified MANAGEMENT system ,awarded to Emerald for adherence to Environmental standard ISO 14001 the memory of my father.

3 To my dear mother and my family Nitsa,Maria, and GeorgiaThis page intentionally left blankAcknowledgmentsThis book has been a group effort, and there many people who contributed andtheir help meant a great deal to me. I am in a continual state of gratitude to allmy PMU students who helped with their remarks while I was teaching thecourse. I am grateful because I received important constructive comments froma student s point of view. I am grateful to my reviewers Dr Malik F. Saleh andIsidoros Passas whose ideas and suggestions are scattered throughout this , I owe a special debt of gratitude to Krista Marie Osborne PMU s super-visor of Advising and Learning Enrichment Services for her valuable page intentionally left blankContentsAbout the AuthorxvPrefacexviiTo the StudentxixTo the InstructorxxiPART I: UNDERSTANDING THE DATABASEAPPROACHC hapter 1 The DATABASE From Data to Information to File The DATABASE The DATABASE DATABASE Types of DBMS14 Chapter 2 DATABASE The DATABASE Development Conceptual DATABASE Designing Designing Logical DATABASE Design Using the Relational Step 1: Converting Entities into Step 2: Implementing 1:M Step 3: Implementing M.

4 M Normalization Optimizing the DATABASE Converting the DATABASE to FirstNormal Form (1NF) Converting the DATABASE to SecondNormal Form (2NF) Converting the DATABASE to Third NormalForm (3NF)40 PART II: DATABASE DEVELOPMENTC hapter 3 Structured Query Language (SQL): Data SQL Creating the Creating the DATABASE Inserting Data into the Deleting and Updating Deleting Data from a Updating the Data of a ON DELETE and ON UPDATE ReferentialIntegrity Updating the Table Structure84 Chapter 4 Structured Query Language (SQL):Introducing Query Understanding the Operations of SQL Attribute and Row Selection Extraction and Update of Query Executing SQL Forming SQL Create SQL Queries Using Conditional andLogical Create SQL Queries Using Text Create SQL Queries Using Date Using Arithmetic Operations in SQL Using the DISTINCT Sorting the Data in SQL SQL Aggregate The COUNT Aggregate The SUM Aggregate The AVG Aggregate The MAX Aggregate The MIN Aggregate Function117 Chapter 5 Structured Query Language (SQL).

5 In Multiple Joining The Rationale of Table Joining Tables with Cross From Cross Join to Inner SQL JOIN Inner Outer Using Alias in Cases of Using SQL Commands in Joined Recursive Relational Set UNION Set INTERSECT Set MINUS Set Operator139 Chapter 6 Structured Query Language (SQL):Grouping Data and Grouping The Rationale of Data The GROUP BY The DISTINCT Grouping with Limitations Using theWHERE Grouping with Calculated The HAVING The Rational of Using WHERE HAVING IN ALL and ANY SELECT FROM INSERT, UPDATE, DELETE Revised withSubqueries168 Chapter 7 Structured Query Language (SQL):Views, Triggers, and Creating Virtual Tables as Data Updating through The Rationale of Creating SQL Managing Arithmetic Attributes in Selecting Random Values in Concatenating and Managing Text Date Managing Functions in Creating User-defined Functions188 PART III: DATABASE MANAGEMENTC hapter 8 DATABASE .

6 Administration and DATABASE DATABASE and Metadata DATABASE Efficient Data Storage Configuring Indexes Improving DATABASE DATABASE Backup and DATABASE DATABASE Security Mitigating DATABASE Security Creating Users and Granting Privileges209 Chapter 9 Building Web DATABASE DATABASE Defining DATABASE DATABASE Application GET and POST Single-user and Multiuser Web DATABASE Building DATABASE Building a Web DATABASE Application in Building a DATABASE Application in Building a DATABASE Application in MariaDB230 Chapter 10 DataBases in the Business Business Challenges in the Global Business A Framework for Business Data Warehouses and OLAP OLAP Operations Roll-up, Drill-down,Slice and Dice, SQL ROLLUP, CUBE.

7 And GROUPIN Data Mining in Big Data and Predictive Enterprise-wide The Need for Integration in Business ERPs and Enterprise-wide Databases in Cloud Enterprise Computing258 Chapter 11 Distributed DataBases and Concurrency Centralized and Distributed Distributed DATABASE The Fragmentation Replication Distributed DATABASE Using Distributed Client s Distribution Client s Distribution Concurrency Transaction MANAGEMENT in Concurrency Control with Lost Concurrency Control with Uncommitted Concurrency Control with Two-Phase Commit Protocol in DistributedDatabases280 References289 Index291 ContentsxiiiThis page intentionally left blankAbout the Zygiarisis an IT professional working withecommerce and business intelligence applications.

8 Hereceived his PhD in Innovation MANAGEMENT fromAristotle University, Greece and his main degrees fromLamar University in Texas, USA in Computer Science(BSc) and MIS (MSc).He has six years of IT experience working for FinaOil and Chemical. He has also worked as a Lecturerat the University of Sheffield teaching computer sciencecourses. With 12 years of research MANAGEMENT experi-ence managing European Union multinational researchprojects in technological innovation and industrial research and development, heis a European Union international expert in innovation and smart city techno-logical Zygiaris was an Associate Professor of Information Technology atUniversity of Maryland College. Currently, he is an Assistant Professor ofManagement Information SYSTEMS at PMU in the Kingdom of Saudi is the author of two books related to business transformation using techno-logical advancements and business process page intentionally left blankPrefaceThis book is meant to help business students and professionals to learn the con-cept of databases in all aspects from design and implementation, to managementof databases.

9 A DATABASE is a central component of every business as a business student must understand how data are stored, retrieved, andmanaged in your corporate environment. With the advancing of enterprise com-puting and cloud technologies, managers are responsible to retrieve data ad hocand construct business reports for decision-making and storytelling. Managershaving solid knowledge of DATABASE technologies are empowered with analyticaland reporting skills. For this reason, an advancing number of universitiesinclude in their business administration curriculum DATABASE MANAGEMENT intheir skill set book manages to codify all information technology issues in databasedesign and implementation into terms that can be easily understood by businessstudents. For example, other relative textbooks cover DATABASE design in up tofour chapters, at an average of seventy pages, presenting complex design meth-odologies that are very difficult to be followed by non-IT students.

10 On thecontrary, this book has codified the DATABASE design process in twenty pages inChapter 2, presenting in discrete steps the DATABASE designing case study, Atza Inc., is used throughout the book that follows alldatabase aspects from design to implementation to MANAGEMENT . This way, thestudents follow all phases of a DATABASE cycle in examples and illustrations pro-vided in each book chapter. Four more business cases in project MANAGEMENT ,human resources, inventory MANAGEMENT , and customer service are cases are allocated to students as work for projects and study for Atza Inc. refers to afictitious company that the book purposes. Anydata used in this book are imaginary and are not related to any real company book covers three different DATABASE MANAGEMENT SYSTEMS : MS-Access, oracle , and MariaDB, facilitating the adaptation to different lab specifica-tions and DATABASE environments.