Database Design and Implementation A Practical Introduction Using Oracle SQL (Gould H.) (Z-Library)

📄 File Format: PDF

💾 File Size: 13.6 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

2 Howard Gould Database Design and Implementation A practical introduction using Oracle SQL

📄 Page 3

3 Database Design and Implementation: A practical introduction using Oracle SQL 1st edition © 2015 Howard Gould & AvE4EvA ISBN-10: 87-403-1046-9 ISBN-13: 978-87-403-1046-7 Reviewed by Dr Mark Dixon, senior lecturer, School of Computing, Creative Technologies & Engineering, Leeds Beckett University

📄 Page 4

Database Design and Implementation: A practical introduction using Oracle SQL 4 Contents Contents Acknowledgements 8 Foreword 9 1 Introduction to database development 10 1.1 Conceptual data modelling 11 1.2 The Entity Relationship Diagram (ERD) 12 1.3 Entity types 12 1.4 Producing the ERD 14 1.5 Entity attributes 14 1.6 Entity selection and validation 15 1.7 Entity definitions 19 1.8 Validating the model 20

📄 Page 5

Database Design and Implementation: A practical introduction using Oracle SQL 5 Contents 2 Entity Relationships 21 2.1 Introduction 21 2.2 Relationships 21 2.3 Relationship cardinality 24 2.4 Relationship optionality 27 3 Complex relationships 31 3.1 Introduction 31 3.2 Associative relationships 32 3.3 Link entity identifier 34 3.4 Recursive relationships 37 3.5 Sub types 40 3.6 Exclusive relationships 42 3.7 Summary 42 4 Logical Database Design 44 4.1 Introduction 44 4.2 Relations 44 4.3 Keys 45 4.4 Identifying relations 46 © Deloitte & Touche LLP and affiliated entities. Discover the truth at www.deloitte.ca/careers 360° thinking. Discover the truth at www.deloitte.ca/careers © Deloitte & Touche LLP and affiliated entities. 360° thinking. © Deloitte & Touche LLP and affiliated entities. 360° thinking. Discover the truth at www.deloitte.ca/careers

📄 Page 6

Database Design and Implementation: A practical introduction using Oracle SQL 6 Contents 4.5 Resolving many-to-many relationships 49 4.6 Resolving one-to-many relationships with optionality 51 4.7 Resolving one-to-one relationships 52 4.8 Recursive relationships 54 4.9 Exclusive relationships 55 4.10 Identification Dependency 55 4.11 Modelling problems 57 4.12 Summary 58 5 Normalisation 62 5.1 Introduction 62 5.2 Un-normalised form (UNF) 63 5.3 First Normal Form (1NF) 69 5.4 Second Normal Form (2NF) 72 5.5 Third Normal Form (3NF) 77 5.6 Denormalisation 80 5.7 Checking the model 80 5.8 Summary 80 6 Introduction to Oracle SQL 85

📄 Page 7

Database Design and Implementation: A practical introduction using Oracle SQL 7 Contents 7 Using Foreign Keys 104 8 Selecting data from a table 108 9 Selecting data from multiple tables 115 10 Subqueries and group functions 123 11 Creating pages & reports 129 12 Appendices 153 12.1 Appendix A. UML Modelling Notation 153 12.2 Appendix B. Music System Specification (ERD and Tables) 156 12.3 Appendix C. Order System Specification (ERD and Tables) 160 12.4 Appendix D. Normalisation Template 163 13 Bibliography 164 Maersk.com/Mitas �e Graduate Programme for Engineers and Geoscientists Month 16 I was a construction supervisor in the North Sea advising and helping foremen solve problems Real work International opportunities �ree work placements I wanted real r Month 16 I was a construction supervisor in the North Sea advising and helping foremen solve problems Real work International opportunities �ree work placements I wanted real responsibili� I joined MITAS because Maersk.com/Mitas �e Graduate Programme for Engineers and Geoscientists

📄 Page 8

Database Design and Implementation: A practical introduction using Oracle SQL 8 Acknowledgements Acknowledgements I should like to express my gratitude to colleagues at Leeds Beckett University (formerly known as Leeds Metropolitan University) for reviewing the manuscript and making helpful suggestions. Particular thanks go to Dr Mark Dixon for developing the QSEE CASE tool. The idea for this book evolved from teaching an introductory level databases module to undergraduate computing students for many years. The main material used by the module was a workbook developed and delivered by a number of staff at Leeds Beckett University, and this book is based on some of the ideas and content of the workbook; I would like to thank colleagues past and present for their contributions to the original student workbook, and apologise to those who I have been unable to formally acknowledge here. Trademarks Some of the product and company names used in this book have been used for the purpose of identification only and may be trademarks or registered trademarks of their respective manufacturers and sellers. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries. Unified Modelling Language and UML are either registered trademarks or trademarks of Object Management Group, Inc. in the United States and/or other countries.

📄 Page 9

Database Design and Implementation: A practical introduction using Oracle SQL 9 Foreword Foreword This book has been written to provide a practical introduction to relational database design and database development for students studying computing-related courses and anyone else who needs to work with relational databases, either as users, designers or developers. Similarly, people who are commissioning a database may benefit from an understanding of this content. This book is based on an approach that has been used successfully over a number of years to teach many undergraduate computing students, and is presented in a concise form that will make it easy for you to grasp the essential principles and techniques and apply these in any relational database environment. An established data modelling methodology is explained which includes Entity Relationship models. These are presented using the long-established crow’s foot notation; however, as the Unified Modelling Language (UML) has become an industry standard, the Class Diagram notation is also introduced to show how it can also be used for ER modelling, though there are differences (Hay & Lynott, 2008). The modelling diagrams used in this book have been drawn using the QSEE SuperLite v1.1.2 CASE tool which is available to download from http://www.leedsbeckett.ac.uk/qsee/. The Oracle database management system and Oracle Application Express (APEX) development environment are used to introduce the industry standard Structured Query Language (SQL). An APEX user account can be obtained from apex.oracle.com for web access; alternatively, a copy of Oracle Database 11g Express Edition and Oracle APEX can be downloaded from Oracle.com for installation on your own system. Further supporting materials can be found at the author’s website howard-gould.co.uk.

📄 Page 10

Database Design and Implementation: A practical introduction using Oracle SQL 10 Introduction to database development 1 Introduction to database development On completion of this chapter you should be able to: • be aware of the database development lifecycle • understand the purpose of data modelling • identify the key terms used in data modelling. Databases are at the centre of most information systems in everyday use, therefore it is important that they are designed and built using appropriate methods to ensure that they meet users’ requirements whilst being robust and maintainable. A database system is usually regarded as the database which contains related tables of data maintained by a database management system (DBMS), along with applications that provide controlled access to the database. In order to build an effective database system it is important to understand and apply the database development lifecycle, which includes the following phases:- 1. Strategy and planning 2. Requirements analysis 3. Design 4. Development 5. Deployment/implementation 6. Operations and maintenance. 1. Strategy and planning – typically the cycle starts with the strategy and planning phase to identify the need and scope of a new system. 2. Requirements analysis phase – a more detailed requirements analysis will be carried out which will include identifying what the users require of the system; this will involve conceptual analysis. 3. Design phase – this will involve producing a conceptual, logical and physical design. To undertake these processes it is important to be able to understand and apply the data modelling techniques which are covered in this book. When a suitable logical design has been obtained the development phase can begin.

📄 Page 11

Database Design and Implementation: A practical introduction using Oracle SQL 11 Introduction to database development 4. Development phase – this involves creating the database structure using an appropriate Database Management System (DBMS) and usually includes the development of applications that provide a user interface consisting of forms and reports which will allow controlled access to the data held in the database. This book will show how the Oracle relational database management system and the Oracle Application Express (APEX) application developer tool can be used for this purpose. 5. Deployment/implementation – when the system has been developed it will be tested, it will then be deployed ready for use. 6. Operations and maintenance – following the system release for use it will be maintained until it reaches the end of its useful life, at this stage the development lifecycle may restart. 1.1 Conceptual data modelling Why do you need to model? In many environments modelling is used to ensure that a product will satisfy the user’s requirements before it is produced. For example, an architect may use a scale model of a building so the client can see what it will look like before it is built. This allows for any changes to be made to the design following feedback and before any expensive building work takes place. Similarly, a modelling approach is needed when designing a database system so that interested parties can check that the design will satisfy the requirements. How do you model a database system? In order to design an effective database system you need to be able to understand an organisation’s information needs and, in particular, identify the data needed to satisfy these needs. Entity Relationship modelling (Chen P. 1976) is an important top-down analysis technique which is used to show the structure of the data used by a system. Initially, a conceptual model is produced which is independent of any hardware or DBMS system; this is achieved by using an Entity Relationship Diagram (ERD) or alternatively a UML Class Diagram (CD). This modelling technique will be used to determine how this business data is structured and show the relationships between the different data entities. The model forms the basis for the design of the database system that will be built. It is crucial to model an information system accurately before building it, to avoid costly mistakes.

📄 Page 12

Database Design and Implementation: A practical introduction using Oracle SQL 12 Introduction to database development 1.2 The Entity Relationship Diagram (ERD) The Entity Relationship Diagram (ERD) shows “entities” and the “relationships” that link them. The entities represent the data items needed by the system and the relationships show how the entities are related to one another. An “entity” is formally called an “entity type” and can be defined as: “A group of objects with the same properties which are identified by the enterprise as having an independent existence.” (Connolly & Begg 2015, p. 406) There are a number of notations used for drawing ERDs; this book will show you how to use the commonly used Crow’s foot notation (Barker 1990). In addition, as the Unified Modelling Language (UML) (www.uml.org) is becoming more widely established, Appendix A shows how the UML Class Diagram can also be used for data modelling. Using the Crow’s foot notation, each entity type is modelled on the ERD as a round-cornered box with the entity name inside it e.g. Entity type You should always use UPPER case letters for entity names, and the name of the entity type should be written in the singular, e.g. INVOICE not INVOICES. Remember, the entity symbol is used to represent the entity type, not the number of occurrences; this information will be added to the ERD later. An example ERD for the Music System from Appendix B is shown below. This shows four entities – represented by round edge boxes – which are needed; production COMPANY, their music CDs which consist of TRACKs (i.e. songs). Each track is classified by a music CATEGORY (e.g. Pop, Rock). The lines and their symbols linking the entities are the relationships which provide further information about the entities. Music System ERD 1.3 Entity types In order to produce an ERD you need to identify all the entity types that are relevant to the system being modelled. Do not confuse an entity type with the occurrence of an entity.

📄 Page 13

Database Design and Implementation: A practical introduction using Oracle SQL 13 Introduction to database development If modelling using the Unified Modelling Language (UML) then the term “instance” is used to refer to an entity occurrence. You should consider the entity type as the definition or template of what data is to be held, and an occurrence as a single set of actual data e.g. entity type: STUDENT, occurrence “Mike Jones”. Often many entities can be identified, although they are not always relevant to the needs of the system being considered, so care needs to be taken to ensure that only those that are needed are added to the ERD. The following are examples of typical entity types: For a business system: CUSTOMER, ORDER, INVOICE. For a university system: STUDENT, LECTURER, COURSE. Entities often fall into one of the following categories: Physical – CAR, BUILDING Human – CUSTOMER, EMPLOYEE Place – FACTORY, SCHOOL Group – DEPARTMENT, TEAM Document – INVOICE, PAYSLIP

📄 Page 14

Database Design and Implementation: A practical introduction using Oracle SQL 14 Introduction to database development 1.4 Producing the ERD When you have identified the entity types, these need to be added to the Entity Relationship Diagram (ERD). Although ERDs can be drawn by hand, it is good practice to use a Computer Aided Software Engineering (CASE) tool to ensure your models can be amended easily and presented in a professional form to others. There are many CASE tools available to support modelling. The QSEE tool can be used to draw ERDs and UML Class Diagrams and is available to download from http://www.leedsbeckett.ac.uk/qsee/ CASE tools are more than just drawing tools; they are used to hold information about the data entities and their attributes (Meta data) and can be used to assist in the development stage of the database development lifecycle. Exercise 1 Identify which of the following are likely to be entity types and which occurrences. If an occurrence, suggest a suitable entity type; if an entity type suggest a suitable occurrence: PARIS, MODULE, AMIN KHAN, CUSTOMER, STUDENT, CITY Exercise 1 feedback ENTITY TYPE ENTITY OCCURRENCE CITY Paris MODULE Introduction to Databases CUSTOMER or STUDENT Amin Khan 1.5 Entity attributes When you have identified your entity types you then need to identify their attributes. An attribute is defined as follows: “A property of an entity or a relationship type” (Connolly & Begg 2015, p. 413). Each entity will usually have a number of attributes. These are the individual items of data that you need to hold for each occurrence of an entity type. In some situations a relationship between a pair of entities may also yield attributes; this situation will be discussed later. The entity type INVOICE may include the following attributes: Invoice number, Invoice date, Invoice amount and Customer code.

📄 Page 15

Database Design and Implementation: A practical introduction using Oracle SQL 15 Introduction to database development An example entity occurrence of the entity type INVOICE would be as follows: Invoice number 1102 Invoice date 12-Jan-2015 Invoice amount 1000 Customer code C101 Exercise 2 Which of the following are likely to be attributes and which are likely to be entity types? CUSTOMER, PRODUCT, ORDER, ORDER DATE, SIZE, QUANTITY, NAME. Exercise 2 feedback ENTITY TYPES: CUSTOMER, PRODUCT, ORDER. ATTRIBUTES: ORDER DATE, SIZE, QUANTITY, NAME. 1.6 Entity selection and validation In order to produce the ERD you need to ensure you have identified the entities that are suitable for inclusion. The entities initially selected are usually referred to as “candidate entities” as not all may be suitable for inclusion. Entity names are normally nouns not verbs. The candidate entities are usually identified by referring to a written system description, a set of requirements, or perhaps the notes from a discussion with a person who has knowledge of the system under consideration. Read through the relevant documents and underline, highlight or draw a box around each noun (an item you can store information about). These nouns will form the candidate entity list. To ensure that a candidate entity is valid for inclusion on the ERD it should satisfy the following three checks: 1. It should not be the name of the system being modelled It is a common mistake to include an entity which has the name of the system or organisation that is being modelled. For example, if you were producing a model of “Yorkshire University” it would not be appropriate to include an entity type called YORKSHIRE UNIVERSITY or even UNIVERSITY as there is only one occurrence of this university. The whole model would, in reality, represent the university. However, if you were modelling a system that needed to hold data for more than one university, then you would need to include an entity type called UNIVERSITY.

📄 Page 16

Database Design and Implementation: A practical introduction using Oracle SQL 16 Introduction to database development 2. The object should be of importance to the system being studied There are likely to be many objects in the system being studied but you have to decide whether the object is relevant. This usually means determining if the system users are likely to need to retrieve information about the object. For example, if you were designing a university student information system is a “litter bin” likely to satisfy the check? The answer would be no, but are there any circumstances in which it might? If the purpose of the system was to record all university assets, then you might need to record information about the litter bins. In that case you would need an entity type to represent this information, though the entity type would be called ASSET and bin would be an entity occurrence. 3. There should be data attributes that can be associated with the entity There must be at least two attributes for an entity type. If you cannot identify any or only one attribute for the entity then you may need to consider whether, in fact, it is actually an attribute of another entity type. Exercise 3 Identify the candidate entities for the following brief business system description. Then remove any of the candidates that do not satisfy the checks for an entity. “Customers of the Yorkshire Supplies Co. order high and low value products. Most customers use a computer and so have an email address.”

📄 Page 17

Database Design and Implementation: A practical introduction using Oracle SQL 17 Introduction to database development Exercise 3 feedback “Customers of the Yorkshire Supplies Co. place orders for high and low value products. Most customers use a computer and so have an email address.” An initial search for candidate entities may produce the following list: CUSTOMER YORKSHIRE SUPPLIES CO. ORDER PRODUCT COMPUTER EMAIL ADDRESS If you now apply the three checks, YORKSHIRE SUPPLIES CO. would be eliminated as it fails the first check. There is only one, i.e. the system being modelled is the “Yorkshire Supplies Co.” COMPUTER would be eliminated as it would not satisfy the second check, as it is unlikely that there would be a need to store details about a customer’s computer. EMAIL ADDRESS would be eliminated too, as it fails the third check, however it is likely to be an attribute of CUSTOMER. So you would be left with the following entity types which would be added to the ERD: CUSTOMER, ORDER, PRODUCT. Once you have identified the entities needed for the system these can be added to the ERD. You should now try and identify the entity attributes. Following the entity checks some attributes may have come to light, either as a result of applying check two where the item may in fact be an attribute rather than an entity, or check three because you only have one attribute. To ensure you have identified all the required attributes you will need to analyse the system documentation which was used to identify the entities and discuss the system with its users to identify and extract all the items of data that are needed for each entity type. The entity and attribute information is usually recorded in a CASE tool repository to make it easier to reference during the system design and development phases. It is unlikely that you will identify all the attributes initially so you should always try and check with the users of the system to ensure that you have not missed any. All entities require an entity identifier. This is an identifying attribute (or attributes) which is used to uniquely identify an occurrence of an entity type. For example: A motor VEHICLE entity type can use the vehicle registration number as its unique identifier. Often an attribute presents itself as the natural identifying attribute as in the example above. However, if there is no natural candidate for this you must introduce an artificial one. This is usually achieved by creating a reference code, e.g. Client ID.

📄 Page 18

Database Design and Implementation: A practical introduction using Oracle SQL 18 Introduction to database development Q. Why can you not use ‘student name’ as the entity identifier for the STUDENT? A. Because it is possible that you could have two or more students with the same name. In this case, the artificial identifier student id could be used as the unique identifier. Exercise 4 What would be the identifying attribute in each of the following examples of entity types where some attributes have been identified? CUSTOMER – Customer name, Address, Postcode, Customer code, Phone number. INVOICE –. Invoice Date, Invoice number, Invoice Amount, Customer code. Remember, wherever possible you should use an existing attribute for the entity identifier and make sure that it will always uniquely identify an occurrence of an entity type. Exercise 4 feedback For CUSTOMER the obvious choice would be Customer code. Although Customer name might be considered, there may be more than one customer with the same name. Postcode may also be considered but this should be rejected as more than one customer may have the same postcode. Likewise, whilst no two customers should have the same phone number, this would not be a suitable choice as not all customers may have a phone number. For INVOICE, the obvious choice would be Invoice number as this would be unique for each invoice. The other attributes would not be suitable as their values could be duplicated amongst all the invoices. Exercise 5 For a university student information system list two entity types. 1. For each entity type list some of their attributes including an identifying attribute. 2. Produce a sample entity occurrence for each entity type. Exercise 5 feedback Entity type: STUDENT Attributes: Student ID, Name, Address, Post Code, Date of Birth Identifier: Student ID Occurrence: S101, Paul Adams, 4 Long Row Leeds, LS6 3QS, 12-Jan-1960 Entity: COURSE Attributes: Course code, Course name, Start date Identifier: Course code Occurrence: COMP, BSc. Computing, 01-Sep-2015

📄 Page 19

Database Design and Implementation: A practical introduction using Oracle SQL 19 Introduction to database development 1.7 Entity definitions It is important to ensure that anyone involved with designing the system is clear about the meaning of the entities being modelled. This is achieved by clearly documenting each entity type with a concise unambiguous definition. The list of definitions is referred to as the “data dictionary” or “data repository” and is usually stored within a CASE tool as this provides a central reference point and allows for easy searching, amendment and reporting. Other information relating to the entities and their attributes may also be added to the data dictionary such as the number of likely entity occurrences and the data types and sizes of the attributes. This information may be needed in the design phase. Here are examples of full definitions for some entity types. Entity type Entity definition Entity Attributes CUSTOMER A person or organisation who purchases products or services from the business. Customer name, Address, Postcode, Customer code, Phone number. INVOICE A request to a customer for payment for products or services supplied by the business. Invoice Date, Invoice number, Invoice Amount, Customer code.

📄 Page 20

Database Design and Implementation: A practical introduction using Oracle SQL 20 Introduction to database development 1.8 Validating the model The model should be checked with the client or system users to ensure that all relevant entities have been identified, along with the required attributes. This process may need to be repeated a number of times until everyone is satisfied that all requirements have been met. Remember it is easier and cheaper to change a model than it is to change a developed system. Exercise 6 For the following business descriptions identify the candidate entities and eliminate any which are not entities, giving a reason for this. For each remaining entity provide an entity definition and some suitable attributes including the identifying attribute. 1. A large business consists of a number of divisions. Each division has a number of departments. Each employee works for a department. 2. A car hire company’s customers make bookings to hire its cars. When a booking is made it is for a specific model of car and includes the collection and return dates. At the time of the booking the company may assign a particular car, however at the time of collection a different car may be provided. The company needs to keep records of which car each customer actually hired. Activity 6 feedback The identifying attributes are shown in bold. The # symbol is used to represent a number e.g. Employee number. 1. BUSINESS is eliminated as there is only one occurrence; you are modelling the business. DIVISION Attributes: Division Name, location, … Description: A major functional area of the business. DEPARTMENT Attributes: Department Name, office location, … Description: A functional section within a division of the business. EMPLOYEE Attributes: Employee#, name, office, grade, …. Description: A member of staff who works for one of the departments within the business. 2. CAR HIRE COMPANY is eliminated as you are only modelling the one. CUSTOMER Attributes: Customer#, name, address, Tel no. … Description: A person registered to hire cars from the company. BOOKING Attributes: Booking Ref, booking date, Customer#, car, collection date, return date, … Description: A booking of a car for use by a customer of the company.