Prelims

Half Title Page

DATABASE MANAGEMENT SYSTEMS

Title Page

DATABASE MANAGEMENT SYSTEMS: A BUSINESS-ORIENTED APPROACH USING ORACLE, MySQL, AND MS ACCESS

SOTIRIOS ZYGIARIS

University of Maryland, USA and PMU University, KSA

United Kingdom – North America – Japan – India – Malaysia – China

Copyright Page

Emerald Publishing Limited

Howard House, Wagon Lane, Bingley BD16 1WA, UK

First edition 2018

Copyright © 2018 Emerald Publishing Limited

Reprints and permissions service

Contact: permissions@emeraldinsight.com

No part of this book may be reproduced, stored in a retrieval system, transmitted in any form or by any means electronic, mechanical, photocopying, recording or otherwise without either the prior written permission of the publisher or a licence permitting restricted copying issued in the UK by The Copyright Licensing Agency and in the USA by The Copyright Clearance Center. Any opinions expressed in the chapters are those of the authors. Whilst Emerald makes every effort to ensure the quality and accuracy of its content, Emerald makes no representation implied or otherwise, as to the chapters’ suitability and application and disclaims any warranties, express or implied, to their use.

British Library Cataloguing in Publication Data

A catalogue record for this book is available from the British Library

ISBN: 978-1-78756-696-5 (Print)

ISBN: 978-1-78756-695-8 (Online)

ISBN: 978-1-78756-697-2 (Epub)

DBMS used in the book

ORACLE 11g APEX 4.2

Maria DB (formerly MySQL) from XAMPP stack 5.5.30

Microsoft Access 2013

Screenshots from Oracle 11g Application Express 4.2. Copyright Oracle, used with permission by Oracle Corporation.

Dedication

To the memory of my father. To my dear mother and my family Nitsa, Maria, and Georgia

Acknowledgments

This book has been a group effort, and there are many people who contributed and their help meant a great deal to me. I am in a continual state of gratitude to all my PMU students who helped with their remarks while I was teaching the course. I am grateful because I received important constructive comments from a student’s point of view. I am grateful to my reviewers Dr Malik F. Saleh and Isidoros Passas whose ideas and suggestions are scattered throughout this book. Lastly, I owe a special debt of gratitude to Krista Marie Osborne PMU’s supervisor of Advising and Learning Enrichment Services for her valuable editing support.

About the Author

Dr. Sotirios Zygiaris is an IT professional working with ecommerce and business intelligence applications. He received his PhD in Innovation Management from Aristotle University, Greece and his main degrees from Lamar University in Texas, USA in Computer Science (BSc) and MIS (MSc).

He has six years of IT experience working for Fina Oil and Chemical. He has also worked as a Lecturer at the University of Sheffield teaching computer science courses. With 12 years of research management experience managing European Union multinational research projects in technological innovation and industrial research and development, he is a European Union international expert in innovation and smart city technological platforms.

Dr Zygiaris was an Associate Professor of Information Technology at University of Maryland College. Currently, he is an Assistant Professor of Management Information Systems at PMU in the Kingdom of Saudi Arabia. He is the author of two books related to business transformation using technological advancements and business process re-engineering.

Preface

This book is meant to help business students and professionals to learn the concept of databases in all aspects from design and implementation, to management of databases. A database is a central component of every business application. You as a business student must understand how data are stored, retrieved, and managed in your corporate environment. With the advancing of enterprise computing and cloud technologies, managers are responsible to retrieve data ad hoc and construct business reports for decision-making and storytelling. Managers having solid knowledge of database technologies are empowered with analytical and reporting skills. For this reason, an advancing number of universities include in their business administration curriculum database management in their skill set profile.

The book manages to codify all information technology issues in database design and implementation into terms that can be easily understood by business students. For example, other relative textbooks cover database design in up to four chapters, at an average of seventy pages, presenting complex design methodologies that are very difficult to be followed by non-IT students. On the contrary, this book has codified the database design process in twenty pages in Chapter 2, presenting in discrete steps the database designing process.

One case study, “Atza Inc.,” is used throughout the book that follows all database aspects from design to implementation to management. This way, the students follow all phases of a database cycle in examples and illustrations provided in each book chapter. Four more business cases in project management, human resources, inventory management, and customer service are specified. These cases are allocated to students as work for projects and assignments. The case study for Atza Inc. refers to a fictitious company that the book purposes. Any data used in this book are imaginary and are not related to any real company or persons.

The book covers three different database management systems: MS-Access, ORACLE, and MariaDB, facilitating the adaptation to different lab specifications and database environments. To avoid lengthy technical implementation details, the readers are directed to the book’s YouTube video channel where all technical implementation details are presented in each one of the three corresponding database management systems. For example, when the student has to learn how to create database tables in ORACLE, he/she follows the book link to the relative video. New editions of database management systems are covered by modifying the videos without any changes to the textbook.

The first part of the book covers the topics on database environment and database design. The second part from Chapters 3 to 7 covers the structured query language (SQL) extensively as a basic management tool to build, store, retrieve data, and make business reports by processing the data. The third part covers database management tools in managing and securing the database, creating business intelligence, and understanding the concept of distributed databases in corporate environments.

To the Student

Every feature of this book is designed to help you learn the database management concept. I have a few goals for you in this book: to understand the importance of databases in the business environment and to assist you in developing problem-solving techniques using databases. I want you to learn databases in a new way, removing and codifying all technical and theoretical complexities and presenting the topic in manner that will be appreciated by non-IT students.

To improve the learning experience, read every chapter carefully. Follow the links to YouTube videos presenting visual and technical implementation details in a gradual manner. At the end of the book, follow the terminology. It is very important to “speak the terms” when taking interviews, engaging in discussions, or writing business reports. Answer the review questions at the end of each chapter. When you feel ready, test your knowledge by taking the online multiple-choice quiz for each chapter. Make sure that you go back and review the chapter when questions are not answered correctly.

I provide for you two ways to receive practical experience. First, follow the forecasting case study in problems and exercises at the end of Chapter 2 and solve all problems and exercises in each chapter following the same case study. Follow the “Atza Inc.” examples illustrated in chapter and do the exercises using the forecasting case study. All problems and exercises at the end of each chapter refer to this case study.

Second, choose one or more of the four case studies in project management, human resources, inventory management, or customer service. This is your main assignment that must be implemented in the database management system that you are using. In Chapter 2, you must do the database design. In Chapter 3, you will implement the proposed design into a database. In Chapters 4–7, you must include all SQL queries necessary for business reporting. In Chapter 8, you will include all database management features for optimizing and securing your database. In Chapter 9, you will create a web application using visual tools. The application ensures that all operations such as data entry, update, deletion, and queries are made through client web pages using forms and reports. In Chapter 10, you will enhance your application inserting three-dimensional business reports.

Lastly, this book imparts the necessary structured and codified knowledge and leaves an impression that database management is not reserved only for IT students with high technical and programing skills. In fact, with the right guidance from your course instructor, all students from every discipline can master database management, including you!

To the Instructor

I would like to thank all who have chosen this book edition as a textbook for delivering your course. This textbook is designed as a one-semester course in database management systems. Although it is intended for non-IT students, IT students will also find it very resourceful and concise. “To the point” is a frequent comment I had received from students.

One case study, “Atza Inc.” is used throughout the book that follows all database aspects from design to implementation to management. The first part of the book covers the topics on database environment and database design. The second part from Chapter 3 to 7 covers the structured query language (SQL) extensively, as a basic management tool to build, store, retrieve data, and make business reports by processing the data. The third part covers database management tools in managing and securing the database, creating business intelligence, and understanding the concept of distributed databases in corporate environments.

Once the students conceptualize the database environment in Chapter 1, they must understand the database design process from requirement document and conceptual design (E–R diagram) to logical design (ERM diagram). At this point, they must choose one of the four case studies in project management, human resources, inventory management, or customer service, and conduct a database design exercise. The “Atza Inc.” case study will be used as the main term project assigned to single or group of projects.

In Chapter 3, where the database design must be implemented into a database, you must choose one of the three covered DBMS (MS-Access, ORACLE, or MARIADB) as your class working system. Follow the correspondent’s DBMS YouTube videos to create and populate with data a database. In Chapters 4–7, you must include all SQL queries necessary for business reporting. In Chapter 8, you will include all database management features for optimizing and securing your database. In Chapter 9, you will create a web application using visual tools. The application ensures that all operations such as data entry, update, deletion, and queries are made through client web pages using forms and reports. In Chapter 10, you will enhance your application inserting three-dimensional business reports. Examples of term project assignments are provided online in the instructor’s resources.

Use the forecasting case study in the “Problems and Exercises” sections from Chapter 2 for small assignments and homework at the end of each chapter. Make sure that students follow the “Atza Inc.” examples illustrated in the chapter and do the exercises using the selected case study. All problems and exercises at the end of each chapter refer to this case study. Examples of assignments and model answers are provided online in the instructor’s resources. Each chapter is covered by:

• Online multiple-choice quizzes (uploadable in text form or to a form for direct upload into blackboard or canvas) along with the answer key in the instructor’s resources.

• A PowerPoint presentation for each chapter in the instructor’s resources.

• Solutions to review questions and exercises.