Facilitating InformationSharing

1 Principles ofData ManagementFacilitatingInformation SharingKeith Gordon 2007 Keith right of Keith Gordon to be identified as author of this work has been asserted by him in accordance with Sections 77 and78 of the Copyright, Designs and Patents Act rights reserved. Apart from any fair dealing for the purposes of research or private study, or criticism or review, aspermittedby the Copyright Designs and Patents Act 1988, no part of thispublication may be reproduced, stored or transmitted in anyform or by any means, except with the prior permission in writing of the publisher, or in the case of reprographic reproduction,in accordance with the terms of the licences issued by the Copyright Licensing Agency.

2 Enquiries for permission to reproducematerial outside those terms should be directed to the British Computer SocietyPublishing and Information ProductsFirst Floor, Block DNorth Star HouseNorth Star AvenueSwindonSN2 978-1-902505-84-8 British Cataloguing in Publication CIP catalogue record for this book is available at the British trademarks, registered names etc. acknowledged in this publication are to be the property of their respective :The views expressed in this book are those of the author and do not necessarily reflect the views of the British Computer Societyexcept where explicitly stated as every care has been taken by the authors and the British Computer Society in the preparation of the publication.

3 No warranty is given by the authors or the British Computer Society as publisher as to the accuracy or completeness of theinformation contained within it and neither the authors nor the British Computer Society shall be responsible or liable for anyloss or damage whatsoever arising by virtue of such information or any instructions or advice contained within this publicationor by any of the and tablesxiAbout the authorxvForewordxviiGlossaryxixPrefacexx vIntroductionxxix1 data and the Enterprise1 Information is a key business resource1 The relationship between information and data2 The importance of the quality of data4 The common problems with data5An enterprise-wide view of data7 Managing data is a business issue8 Summary102 Database Development11 The database architecture of an information system11An overview of the database development process16 Conceptual data modelling (from a project-level perspective)20 Relational data analysis33 The roles of a data model49 Physical database design50 Summary533 What is data Management?

4 54 The problems encountered without data management54 data management responsibilities57 Roles within data management60 The benefits of data management62 The relationship between data management and enterprisearchitecture62 Summary64 Contentsviii4 Corporate data Modelling65 Why develop a corporate data model?65 More data modelling concepts66 The nature of a corporate data model72 How to develop a corporate data model74 Corporate data model principles78A final thought82 Summary835 data Definition and Naming Conventions84 The elements of a data definition84 data naming conventions88 Summary906 Metadata91 What is metadata?91 Metadata for data management91 Metadata for content management92 Metadata for describing data values93 Summary937 data Quality95 What is data quality?

5 95 Issues associated with poor-quality data95 The causes of poor-quality data96 The dimensions of data quality97 data model quality98 Improving data quality99 Summary1018 data Accessibility102 data security102 data integrity107 data recovery109 Summary1119 Database Administration112 Database administration responsibilities112 Performance monitoring and tuning114 Summary11510 Repository Administration116 Repositories, data dictionaries, encyclopaedias, catalogs anddirectories116 Repository features118 The repository as a centralised source of information120 Metadata models122 ContentsixSummary12211 The Management of data Management123 Techniques and skills for data administration123 Techniques and skills for database administration124 Techniques and skills for repository administration125 The positioning of data management within the enterprise125 Summary13012 Industry Trends and their Effects on data Management131 The use of packages131 Distributed data and databases133 data warehousing and data mining139 Object orientation and databases145 Multimedia and databases152 data and web

6 Technology155 Summary158 AComparison of data Modelling Notations159 BHierarchical and Network Databases169 CGeneric data Models177 DAn Example of a data Naming Convention183 EMetadata Models195FA data Mining Example201 GHTML and XML205 HXML and Relational Databases213 References219 Further reading221 Index223xiFigures and tablesFigure The relationship between data and information4 Figure A model of a database system12 Figure The three level schema architecture15 Figure A simplified view of the database development process17 Figure A conceptual data model diagram18 Figure A portion of an SQL create script19 Figure The EMPLOYEE entity21 Figure The attributes of the EMPLOYEE entity22 Figure The ADDRESS entity23 Figure The resident at relationship24 Figure The QUALIFICATION and EMPLOYEEQUALIFICATION entities26 Figure The GRADE and EMPLOYEE GRADE entities27 Figure The DEPARTMENT and ASSIGNMENT entities28 Figure The one-to-one managed by relationship29 Figure The many-to-many managed by relationship30 Figure The resolution of the many-to-many managed by relationship30 Figure The EMPLOYEE NEXT OF KIN entity32 Figure A relation shown as a table34 Figure The human resources paper record36 Figure The

7 Un-normalised EMPLOYEE relation 37 Figure The first normal form relations39 Figure Diagram of the first normal form relations41 Figure The second normal form relations43 Figure Diagram of the second normal form relations44 Figure The third normal form relations46 Figure Diagram of the third normal form relations47 Figure data management activities58 Figure data management deliverables60 Figure The business drives; data management steers60 Figure The relationship between data management andinformation management61 Figure The human resources conceptual data modeldeveloped in Chapter 267 Figure Entity subtypes68 Figure An exclusive arc70 Figure Subtyping instead of using an exclusive arc71 Figure A first top-down starter model76 Figures and tablesxiiFigure A second top-down starter model77 Figure A third top-down starter model77 Figure The supply-chain model79 Figure The improved supply-chain model80 Figure More than one type of support?

8 80 Figure The combined support model81 Figure The trade-off triangle82 Figure A data definition with validation criteria and validoperations87 Figure A data definition with valid values87 Figure An entity definition88 Figure The dimensions of data quality98 Figure The five dimensions of data model quality99 Figure Total Quality data Management methodology101 Figure Table privilege statements103 Figure A function privilege statement103 Figure A database object privilege statement104 Figure A view statement and an associated table privilegestatement104 Figure A user-specific view statement and an associatedtable privilege statement104 Figure The role of directories or catalogs116 Figure The relationship between a CASE tool and itsencyclopaedia or data dictionary117 Figure The architecture of a repository118 Figure The scope of a repository119 Figure The repository procurement process120 Figure A repository as a centralised source of information121 Figure The data administration skill set123 Figure Business-based data management127 Figure Independent data management in the IT/IS department 127 Figure data management within systems development128 Figure Dispersed IT/IS based data

9 Management129 Figure Distributed data management129 Figure Vertical fragmentation136 Figure Hybrid fragmentation136 Figure A typical data warehouse architecture139 Figure A multidimensional data model141 Figure A typical relational schema for a data warehouse144 Figure A simple conceptual data model147 Figure The ODL schema definitions for the simpleconceptual data model148 Figure Structured type declarations150 Figure Table declarations using structured types and collections 150 Figures and tablesxiiiFigure Revised simple conceptual data model with entitysubtypes151 Figure Creating structured types152 Figure Creating tables based on the structured types153 Figure A data model in Ellis Barker notation160 Figure Ellis Barker data model with attribute annotationand unique identifiers161 Figure A data model in Chen notation162 Figure A data model in Information Engineering notation163 Figure A data model in IDEF1X notation165 Figure An object class model in UML notation166 Figure Comparison of the relationship notations167 Figure Comparison of the overall data model notations168 Figure Conceptual data model170 Figure Relational database occurrences170 Figure Hierarchical database schema171

10 Figure data definition statements for a hierarchical database172 Figure Hierarchical database occurrences173 Figure Hierarchical database records in sequence173 Figure Network database schema174 Figure data definition statements for a network database175 Figure Network database occurrences176 Figure The generic to specific continuum178 Figure The cost-balance of flexible design180 Figure A metadata