Database programming Books

Book SynopsisThis unique cookbook contains a wealth of solutions to problems that SQL programmers face all the time. The recipes inside range from how to perform simple tasks, like importing external data, to ways of handling issues that are more complicated, like set algebra. Authors Ales Spetic and Jonathan Gennick, two authorities with extensive database and SQL programming experience, include a discussion with each recipe to explain the logic and concepts underlying the solution. SQL (Structured Query Language) is the closest thing to a standard query language that currently exists, and Transact-SQL -- a full-featured programming language that dramatically extends the power of SQL -- is the procedural language of choice for both Microsoft SQL Server and Sybase SQL Server systems. The Transact-SQL Cookbook is designed so you can use the recipes directly, as a source of ideas, or as a way to learn a little more about SQL and what you can do with it. Topics covered include: *Audit logging. In addition to recipes for implementing an audit log, this chapter also includes recipes for: improving performance where large log tables are involved; supporting multiple-languages; and simulating server push. *Hierarchies. Recipes show you how to manipulate hierarchical data using Transact-SQL. *Importing data. This chapter introduces concepts like normalization and recipes useful for working with imported data tables. *Sets. Recipes demonstrate different operations, such as how to find common elements, summarize the data in a set, and find the element in a set that represents an extreme. *Statistics. This chapter?s recipes show you how to effectively use SQL for common statistical operations from means and standard deviations to weighted moving averages. *Temporal data. Recipes demonstrate how to construct queries against time-based data. *Data Structures. This chapter shows how to manipulate data structures like stacks, queues, matrices, and arrays. With an abundance of recipes to help you get your job done more efficiently, the Transact-SQL Cookbook is sure to become an essential part of your library.Table of ContentsPreface 1. Pivot Tables Using a Pivot Table 2. Sets Introduction The Students Example Implementing Set Difference Comparing Two Sets for Equality Implementing Partial Intersection Implementing Full Intersection Classifying Subsets Summarizing Classes of Sets Aggregating Aggregates Summarizing Aggregated Classes Including Nonaggregated Columns Finding the Top N Values in a Set Reporting the Size of a Set's Complement Finding the Complement of a Set Finding the Complement of a Missing Set Finding Complements of Sets with Different niverses Comparing a Set with Its Universe Dynamic Classification System 3. Data Structures Types of Data Structures Working Example Finding Regions Reporting Region Boundaries Limiting Region Size Ranking Regions by Size Working with Sequences Working with Runs Cumulative Aggregates in Lists Implementing a Stack Implementing Queues Implementing Priority Queues Comparing Two Rows in an Array Printing Matrices and Arrays Transposing a Matrix Calculating a Matrix Trace Comparing Two Matrices for Size Adding and Subtracting Matrices Multiplying Matrices 4. Hierarchies in SQL Types of Hierachies Creating a Permission Hierarchy Changing Individual Permissions Adding New Individual Permissions Centralizing Authorization Logic Implementing General Hierarchies Traversing Hierarchies Recursively Manipulating Hierarchies Recursively Aggregating Hierarchies Preparing Multilevel Operations Aggregating Hierarchies Revised 5. Temporal Data Introduction The Schedules Example Enforcing Granularity Rules Storing Out-of-Range Temporal Values Deriving the First and Last Dates of the Month Printing Calendars Calculating Durations Reporting Durations Querying Periods Querying Periods and Respecting Granularity Finding Available Periods Finding Common Available Periods Excluding Recurrent Events Excluding Nonrecurring Events Finding Continuous Periods Using Calendar Information with Periods Using Calendar Information with Durations 6. Audit Logging Audit Logs The Warehouse Example Row-Level Logging Reporting Log Events Generating Current Snapshots Generating Time-Restricted Snapshots Undoing Table Changes Minimizing Audit-Log Space Consumption Online Account Balancing Activity-Level Logging Partitioning Large Log Tables Server Push 7. Importing and Transforming Data Considerations When Importing Data Working Examples Importing External Data Importing Data into a Live System Importing with a Single Procedure Hiding the Import Procedure Folding Tables Pivoting Tables Joining Arrays with Tables Joining Arrays with Master Tables Joining Arrays with Multiple Master Records Extracting Master Records from Tables Generating Master Records Online Working with Duplicates 8. Statistics in SQL Statistical Concepts The Light-Bulb Factory Example Calculating a Mean Calculating a Mode Calculating a Median Calculating Standard Deviation, Variance, and Standard Error Building Confidence Intervals Calculating Correlation Exploring Patterns with Autocorrelation Using a Simple Moving Average Extending Moving Averages Appendix: The T-Distribution Table Index

Read more less

Book SynopsisDespite the wide availability and use of SQL*Loader, few Oracle DBAs and developers know how powerful it really is. This book describes all of SQL*Loader's functions, including how to construct the necessary control files, load different types of data, and get the best performance. It covers the newest SQL*Loader features in 2001 - the loading of large object (LOB) columns and the new object types (nested tables, varying arrays, and object tables).Trade Review'Wow the title hits it bulls-eye - The Definitive Guide. - A humongous effort went into the book and the examples. It's another one for the desk, always to hand. Bravo Well Done" plomax@oriole.comTable of ContentsPreface. 1. Introduction to SQL*Loader The SQL*Loader Environment A Short SQL*Loader Example SQL*Loader's Capabilities Issues when Loading Data Invoking SQL*Loader. 2. The Mysterious Control File Syntax Rules The LOAD Statement Command-Line Parameters in the Control File Placing Data in the Control File. 3. Fields and Datatypes Field Specifications Datatypes. 4. Loading from Fixed-Width Files Common Datatypes Encountered Specifying Field Positions Handling Anomalous Data Concatenating Records Nesting Delimited Fields. 5. Loading Delimited Data Common Datatypes Encountered Example Data Using Delimiters to Identify Fields Common Issues with Delimited Data Concatenating Records Handling Nested Fields. 6. Recovering from Failure Deleting and Starting Over Restarting a Conventional Path Load Restarting a Direct Path Load. 7. Validating and Selectively Loading Data Handling Rejected Records Selectively Loading Data. 8. Transforming Data During a Load Using Oracle's Built-in SQL Functions Writing Your Own Functions Passing Data Through Work Tables Using Triggers Performing Character Set Conversion. 9. Transaction Size and Performance Issues Transaction Processing in SQL*Loader Commit Frequency and Load Performance Commit Frequency and Rollback Segments Performance Improvement Guidelines. 10. Direct Path Loads What is the Direct Path? Performing Direct Path Loads Data Saves Loading Data Fields Greater than 64K UNRECOVERABLE Loads Parallel Data Loading. 11. Loading Large Objects About Large Objects Considerations when Loading LOBs Loading Inline LOBs Loading LOBs from External Data Files Loading BFILEs. 12. Loading Objects and Collections Loading Object Tables and Columns Loading Collections Using NULLIF and DEFAULTIF with an Object or a Collection. Index

Read more less

Book Synopsis 　 DESCRIPTION Geospatial data is hard to ignore. Nearly every car, phone, or camera has a GPS sensor, and aerial photos, satellite imagery, and data representing political boundaries, roads, rivers, and streams are available for free download from many websites. Geoprocessing is the science of reading, analyzing, and presenting geospatial data programmatically. The Python language, along with dozens of open source libraries and tools, makes it possible to take on professional geoprocessing tasks without investing in expensive proprietary packages like ArcGIS and MapInfo. Geoprocessing with Python teaches how to use the Python programming language along with free and open source tools to read, write, and process geospatial data. It shows how to access available data sets to make maps or perform analyses using free and open source tools like the GDAL, Shapely, and Fiona Python modules. Readers will master core practices like handling multiple vector file formats, editing and manipulating geometries, applying spatial and attribute filters, working with projections, and performing basic analyses on vector data. They’ll also learn how to create geospatial data, rather than just consuming it. The book also covers how to manipulate and analyze raster data, such as aerial photographs, satellite images, and digital elevation models. KEY SELLING POINTS Geoprocessing from the ground up Shows how to create custom geoprocessing tools Takes advantage of free and open source tools AUDIENCE Readers need a basic knowledge of Python or a similar programming language. No previous experience with geoprocessing or geospatial data required. Perfect for readers who have used Python with Esri ArcGIS and want to apply their Python skills elsewhere. ABOUT THE TECHNOLOGY Geoprocessing is the science of reading, analyzing, and presenting geospatial data programmatically. The Python language, along with dozens of open source libraries and tools, makes it possible to take on professional geoprocessing tasks without investing in expensive proprietary packages like ArcGIS and MapInfo.

Read more less

Book SynopsisMaster a graph data modelling technique superior to traditional data modelling for both relational and NoSQL databases (graph, document, key-value, and column), leveraging cognitive psychology to improve big data designs. From Karen Lopezs Foreword: In this book, Thomas Frisendal raises important questions about the continued usefulness of traditional data modelling notations and approaches: Are Entity Relationship Diagrams (ERDs) relevant to analytical data requirements? Are ERDs relevant in the new world of Big Data? Are ERDs still the best way to work with business users to understand their needs? Are Logical and Physical Data Models too closely coupled? Are we correct in using the same notations for communicating with business users and developers? Should we refine our existing notations and tools to meet these new needs, or should we start again from a blank page? What new notations and approaches will we need? How will we use those to build enterprise database systems? Frisendal takes us through the history of data modelling, enterprise data models and traditional modelling methods. He points out, quite contentiously, where he feels we have gone wrong and in a few places where we got it right. He then maps out the psychology of meaning and context, while identifying important issues about where data modelling may or may not fit in business modelling. The main subject of this work is a proposal for a new exploration-driven modelling approach and new modelling notations for business concept models, business solutions models, and physical data models with examples on how to leverage those for implementing into any target database or datastore. These new notations are based on a property graph approach to modelling data. From the authors introduction: This book proposes a new approach to data modelling -- one that turns the inside out. For well over thirty years, relational modelling and normalisation was the name of the game. One can ask that if normalisation was the answer, what was the problem? There is something upside-down in that approach, as we will see in this book. Data analysis (modelling) is much like exploration. Almost literally. The data modeler wanders around searching for structure and content. It requires perception and cognitive skills, supported by intuition (a psychological phenomenon), that together determine how well the landscape of business semantics is mapped. Mapping is what we do; we explore the unknowns, draw the maps and post the Here be Dragons warnings. Of course there are technical skills involved, and surprisingly, the most important ones come from psychology and visualisation (again perception and cognition) rather than pure mathematical ability. Two compelling events make a paradigm shift in data modelling possible, and also necessary: 1.The advances in applied cognitive psychology address the needs for proper contextual framework and for better communication, also in data modelling, and 2. The rapid intake of non-relational technologies (Big Data and NoSQL).

Read more less

Book SynopsisThis book is a revised, upgraded, and hugely improved version of an earlier one called Logic and Databases. Although its effectively a brand new book, therefore, the following remarks from that earlier book are still relevant here. First, logic and databases are inextricably intertwined. The relational model itself is essentially just elementary logic, tailored to database needs. Now, if youre a database professional, this wont be news to you -- but you still might not realize just how much everything we do in the database world is (or should be!) affected by logic. Logic is fundamental, and everywhere. As a database professional, therefore, you owe it to yourself to understand the basics of formal logic, and you ought to be able to explain (and perhaps defend) the connections between formal logic and database technology. And thats what this book is about. What it does is show, through a series of partly independent, partly interrelated essays, just how various crucial aspects of database technology -- some of them very familiar, others maybe less so -- are solidly grounded in formal logic. Overall, the goal is to help you realize the importance of logic in everything you do, and also, I hope, to help you see that logic can be fun.

Read more less

Book SynopsisAchieve awesome user experiences and performance with simple, maintainable code! Embrace the full stack of web development, from styling with Bootstrap, building an interactive user interface with Angular 4, to storing data quickly and reliably in PostgreSQL. With this fully revised new edition, take a holistic view of full-stack development to create usable, high-performing applications with Rails 5.1. Rails is a great tool for building web applications, but it's not the best at everything. Embrace the features built into your database. Learn how to use front-end frameworks. Seize the power of the application stack through Angular 4, Bootstrap, and PostgreSQL. When used together, these powerful and easy-to-use tools will open you to a new world of possibilities. This second edition is updated to cover Angular - a completely reworked front-end framework - and dives into new Postgres 9.6 features such as UPSERT. Also new is Webpack coverage, to develop the front-end code for your Rails application. Create a usable and attractive login form using Bootstrap's styles, while ensuring the database table backing it is secure using Postgres' check constraints. See how creating an advanced Postgres index for a case-insensitive search speeds up your back end - enabling you to create a dynamic user experience using Angular 4. Create reusable components that bring Bootstrap and Angular together and effectively use materialized views for caching within Postgres. Get your front end working with Webpack, use Postgres' features from migrations, and write unit tests for all of it. All of this within Rails 5.1. You'll gain the confidence to work at every level of the application stack, bringing the right solution to every problem. What You Need: This book covers Postgres 9.5, Rails 5, and Ruby 2.3. You should have some experience with basic Rails concepts and a cursory understanding of JavaScript, CSS, and SQL, but by no means need to be an expert. You'll learn how to install Postgres on your computer or use a free version of it in the cloud.

Read more less

Book SynopsisThis is a project book that guides you through the process of building a traditional Access desktop database that uses one Access database as the front-end (queries, reports, and forms) and another Access database that contains all the tables and data. By separating the data from the rest of the database, the Access database can be easily shared by multiple users over a network. When you build a database the correctly at the outset, later this database can be migrated to another system with fewer issues and fewer objects that need to be redone. Features: Understand the concepts of normalization; Build tables and links to other data sources and understand table relationships; Connect and work with data stored in other formats (Text, Word, Excel, Outlook and PowerPoint); Retrieve data with DAO, ADO, and DLookup statements; Learn how to process text files for import and export; Create expressions, queries and SQL statements; Build bound and unbound forms and reports and write code to preview and print; Incorporate macros in your database; Work with attachments and image files; Learn how to display and query your Access data in the Internet browser; Secure your database for multi-user access; Compact your database to prevent corruption resulting in data loss.Table of Contents 1: Planning and Designing a Database 2: Creating a Desktop Database 3: Creating Access Tables 4: Setting Up Primary Keys, Indexes, and Table Relationships 5: Populating Access Tables With Data 6: Creating and Working With Select Queries 7: Performing Database Operations With Action Queries 8: Designing and Using Forms 9: Form Customization 10: Designing and Using Reports 11: Report Customization 12: Compacting, Splitting, and Securing the Database Appendix: Microsoft Access File Formats

Read more less

Book SynopsisThis book is for executives, practitioners, and project managers who are tasked with the movement of data from old systems to a new repository. It is designed as a practical guide and uses a series of steps developed in real-life situations that will get you from an empty new system to one that is populated, working and backed by the user population. This new edition is updated to take account of changes in technology and the maturing of the market for data migration services, with two brand new chapters. It guarantees to get the dirty old data out of your legacy systems and transform it into clean new data for your new system.Trade ReviewFor any practitioner faced with the challenge of delivering a successful data migration, this book is an absolute necessity. -- Dylan Jones * Founder, Data Migration Pro *The book Practical Data Migration by Johny Morris is essential reading for any practitioner facing a challenging data migration project. The book covers the most important theoretical aspects but is at the same time very practice-oriented and pragmatic. PDMv3 strategy is the basis for being able to complete projects in time and to budget. -- Dr Andreas Martens * Executive Director, qurix Technology GmbH *PDMv3 provides a valuable resource for project managers or IT practitioners seeking structured and consistent approaches to data migration, whether with regards to strategy, governance, data analysis, migration design, or testing. PDMv3 guidance, advice, and methodology, where followed, enhances the professionalism of anyone undertaking a data migration role. -- Dr David Hannell MBCS CITP * Writer, Research Analyst, Project Manager *Throughout this book, Johny Morris provides practical experience-based guidance to both executive and practitioner on how to overcome the challenges of delivering a successful data migration. This edition updated to cover Waterfall, Agile and blended approaches brings this work fully up to date. An absolute ‘must read’ for any individual or team before starting or rescuing a data migration. -- Ian Chapman MBCS CITP * Enterprise Data Architect, Expertechnix Ltd & Committee Member, DAMA UK *Johny Morris continues to provide clear and honest guidance to anyone who finds themselves having to struggle with the complexities of large-scale data migration. The PDM approach has proved invaluable in guiding the data migration team at Guy’s & St Thomas’ NHS Foundation Trust. -- Dr Tito Castillo MBCS CITP * Founder, Agile Health Informatics Ltd & Associate Vice Chair (Standards), BCS Health & Care Executive *Digital transformation are impacting all organisation and a big part of the challenge is migrating from your current IT platforms and services. This easy to read book is the must have ‘how to’ guide on data migration for executives, data professionals and practitioners. It provides an accessible and easy to read guide that covers the many forms and dimensions to data migration programmes, from someone who has spent years on the sharp side of PDM. -- Jason B Perkins * Head of Data & Analytics Architecture, BT *This is one of very few books I’ve read that I have thoroughly enjoyed from start to finish. Johny Morris’ style of writing is one I really adapt well too, a great mix of theory and experience with highlighted summary, risks and advice passages throughout. Over many years I have stressed the importance that PDM activity is owned and driven by the business, something that is overlooked when technology solutions are designed, tested and implemented. A ‘must have’ book for any BA or IT professional’s library! -- Mark Thompson * Lead Business Analyst (Banking Division), Close Brothers *My first comments on reading Practical Data Migration were ‘AMEN’ and ‘YES’. The key differentiator of PDM from many other methodologies is the word ‘Practical’. Theory has a simple goal of migrating data from a tired old system to a shiny new one. Practical covers ‘user enhancements’. -- Sean Barker FBCS CEng * Retired (previously with BAE Systems) *This book is THE authoritative guide to data migration principles and practices and an essential ‘go-to’ guide for anyone in business or IT involved in data migration projects. It is refreshingly jargon-free, and provides a systematic and proven methodology to address both the technical and cultural challenges of data migration. -- Nigel Turner * Principal Consultant, Global Data Strategy Ltd *Whether this is your first or twenty-first data migration, PDMv3 will gives practical and experienced advice to improve the success of your data migration project. Johny Morris highlights useful tips, real-life experience and golden rules. An essential read for any manager or practitioner. -- Mark Dodd CITP * Think Smart Limited *Data migrations are infrequent, and expertise in this challenging but important field is rare. This new edition of a well-established book, updated to reflect technical advances, is indispensable not only for members of project teams working on data migration but also for their managers. -- Mike Andersson MBCS * Director, Andstrom Consulting & Vice Chair (Standards), BCS Health and Care Executive *PDMv3 is the perfect guide to data migration operations! Its comprehensive coverage of data migration procedures and themes is incredibly structured and fluid. It is clear, concise, and deliberate in its delivery of what you need to know to move your data from one system to another successfully. -- Kudzai Muchenje * Regional Operations Analyst, African Development Bank Group *Table of ContentsINTRODUCTION SECTION 1: EXECUTIVE OVERVIEW1 Data migration: what's all the fuss2 Golden rules and super smart tasks3 PDMv3 overview 4 Creating a data migration strategy SECTION 2: TOOLS AND TECHNIQUES5 Project initiation6 Key data stakeholder management and demilitarised zone7 Landscape analysis8 Business transformation plan9 Data quality rules10 Gap analysis and mapping11 Migration design and execution12 Legacy decommissioning13 Waterfall versus Agile SECTION 3: FAILING DATA MIGRATION PROJECTS 14 Rescuing failing data migration projects APPENDICES A1 Data migration strategy checklistA2 Fields on a DQR document/formA3 Mapping exampleA4 PDMv3 process flow

Read more less

Book Synopsis

Read more less

Book Synopsis

Read more less

Book Synopsis

Read more less

Book SynopsisThis book reviews creative technologies that underpin the human activities of converting data and information into knowledge, creating new ideas based on that knowledge, and validating those ideas. The book calls such creative technology “knowledge technology” and explores its challenges in the age of big data. Today, artificial intelligence and big data are successfully performing pattern recognition and decision making on behalf of humans, who are incapable of processing large amounts of information instantly and accurately. In using only past data, however, there are limits to future predictions with artificial intelligence and big data. Humans need to take the lead in predicting or creating the future by trying to understand why data emerged. Knowledge technology contributes to that understanding, regardless of the field of origin. As a matter of fact, knowledge technology includes many technologies developed in informatics, management study, and systems science. The desirable features of knowledge technology are the complementary use of rational and intuitive approaches, the integration of results from quantitative and qualitative analyses, and the cooperation between artificial intelligence and humans.Table of Contents-Preface.- 1. Introduction.- 2. Defining Knowledge Technology.- 3. Integrative Knowledge Technologies.- 4. Converting Data/Knowledge into Information.- 5. Converting Information into Knowledge.- 6. Creating New Ideas from the Knowledge.- 7. Validating the Value of the New Ideas.- 8. Summary and Expectations.

Read more less