Workbook-F19New.pdf - CST 8215 Database S Terai Algonquin...

This preview shows page 1 out of 191 pages.

Unformatted text preview: CST 8215 - Database S Terai Algonquin College of Applied Arts and Technology Ottawa, Ontario, Canada Edition 1.6-S Fall 2019 ii Contents Preface ix Acknowledgments xi To the Student xiii 1 Database - An Introduction 1 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.4 Types of Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.5 Learning Activities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.5.1 A Table and its Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.6 Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.7 ERD Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 1.8 Drawing Physical ERD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 1.9 Review Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 1.9.1 Written Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 1.9.2 Multiple Choice - Select one correct answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 1.9.3 Short answer questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 1.10 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2 Introduction to SQL 11 2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.2 Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.3 The SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.3.1 Processing Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.4 DDL, DML, & DCL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.5 Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2.6 Case Sensitivity in PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.7 Client-Server Architecture of a DBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 2.8 PostgreSQL Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 iii iv CONTENTS 2.9 Scalar and Vector Aggregates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.10 NULL values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.11 SQL String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 2.12 SQL Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 2.13 Difference between LIKE and = . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 2.14 SQL Logical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 2.15 SQL Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 2.16 Alias . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 2.17 VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 2.18 Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.19 Review Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 2.20 GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 2.20.1 GROUP BY and HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 2.20.2 Examples of Some Invalid Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 2.20.3 Review Questions - GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 2.21 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 3 JOIN 35 3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.2 Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.3 Working with more than one table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.4 JOIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.4.1 Exercise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Additional Example on JOIN Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 3.5.1 DDL & DML statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 3.5.2 Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 3.6 Review Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 3.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 3.5 4 SQL 51 4.1 Sub-query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 4.2 Learning Activities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.3 Transaction Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 4.4 Range Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 4.5 User Defined DataType - UDT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4.6 Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.7 Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.8 Trigger & Trigger Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.9 Comparison - Trigger, Function and Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 58 4.10 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 v CONTENTS 5 Database Design 59 5.1 Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 5.2 Properties of a Relation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 5.3 Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 5.4 Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 5.5 Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 5.6 Learning Activities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 5.6.1 First Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 5.6.2 Second Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 5.6.3 Third Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 5.6.4 Normalization Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 5.6.5 Boyce-Codd Normal Form (BCNF) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Guidelines on Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 5.7.1 Normalization Exercise - Garage Shop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 5.7.2 Normalization Exercise - Hotel Booking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 5.8 Review Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 5.9 Supplementary Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 5.10 Prime Key Identification - Exercise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 5.11 Further Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 5.7 6 Physical Database Design and Performance 75 6.1 Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 6.2 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 6.2.1 Creating and Deleting an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 6.2.2 Benefit and Overhead of Using an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Sequential File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 6.3.1 Sequential Unsorted File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 6.3.2 Sorted List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 6.4 Hashing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 6.5 Comparison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 6.6 Review Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 6.6.1 Additional Review Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 6.7 Written Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 6.8 Lookup Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 6.3 7 Modeling Data 89 7.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 7.2 Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 7.3 Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 7.4 Learning Activitiy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 vi CONTENTS 7.5 Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 7.6 Review Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 7.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 A Labs 97 A.1 Lab Submission Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 A.2 Lab 1 - Install PostgreSQL & Data Modeler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 A.3 Lab 2 - Practice Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 A.4 Lab 3 - Retreive data from world database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 A.5 Lab 4 - Queries on Part_T Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 A.6 Lab 5 - Queries on Part_T Table - Order of Precedence . . . . . . . . . . . . . . . . . . . . . . . . . 110 A.7 Lab 6 - The UPDATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 A.8 Lab 7 - Inventory Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 A.9 Lab 8 - Create a Physical Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 A.10 Lab 9 - Country-City Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 A.11 Lab 10 - Query Inventory Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 A.12 Lab 11 - GROUP BY & HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 A.13 Lab 12 - SQL JOIN statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 A.14 Lab 13 - Retrieve Data from world database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 A.15 Lab 14 - Normalization - Author-Publisher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 A.16 Lab 15 - Tutor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 A.17 Lab 16 - SQL SELF JOIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 A.18 Lab 17 - Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 A.19 Lab 18 - UPDATE with a sub query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 A.20 Lab 19 - Normalization Exercise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 A.21 Lab 20 - 3NF Exercise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 B Assignment 141 C Practical Exam - Fall 2019 151 D Database Files 155 D.1 File List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 D.2 Abstract . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 D.3 Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 E Navigating PostgreSQL 157 E.1 Keyboard Shortcut . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 E.1.1 Edit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 E.1.2 Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 E.2 Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 vii CONTENTS E.3 Misc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 E.4 psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 E.5 Venn Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 E.6 Link . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 E.7 Tips and Traps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 F Evaluation & List of Quizzes 161 F.1 Earning Credit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 F.2 List of Quizzes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 F.2.1 Hybrid Quizzes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 F.2.2 Lab Quizzes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 G Course Section Information (CSI) 165 H Reading Assignment 173 Index 176 viii CONTENTS Preface The sequence of reading assignments are deliberate. For example, reading the summary of a chapter earlier than later, is suggested. The order of chapters covered in lessons are not sequential, this facilitates completion of labs with practice in SQL early in the course followed by theory on normalization. During lecture we will reference sections or subsections from other lessons. Treat this as a reference manual, workbook and a guide. It is difficult to read the owners manual of a new car from start to finish, at different times we need sections relevant to our needs, some this this analogy applies to this book. Students are encouraged to write on this workbook, space is provided for answers. Sections written as Aside: provide additional thought, analogy or comparison to the idea discussed. The reader may defer reading these on the second pass; continuity is maintained if these sections are not read. The agony and joy of creating fictitious names, I have been spared with; the pattern for person names is Consonant, Vowel ending with a consonant, or other similar pattern. Names are either five or three letters. A spreadsheet generates the names for me; a familiar name is coincidental. This document is typeset in LATEX. Feedback in improving this workbook including errors, I would welcome and gladly accept; please email me at, [email protected] ix x PREFACE Acknowledgments Kumari Gurusamy has identified several corrections in the Spring 2015 edition. Sanaa Issa has used these notes for her labs and lectures, has helped in editing in the Fall 2017 Edition. Lab 2 was first developed by Patricia Murphy and other instructors. It has been modified to work on databases that have changed over the years, in particular the user interface. In an earlier writing assignment, Louisa Lambregts had provided valuable suggestions and comments, they are incorporated in this document. He is eloquent, patient and knowledgeable. I am thankful that Mel Sanschagrin had agreed to review the document. All of his suggestions are indispensable. Over the semesters, many students have identified errors and suggestions. They have been an invaluable source in improving this document. In 2001 I had the opportunity to attend a seminar on The ICE Approach [14]. Many pedagogical ideas have been incorporated from their book in my teaching. Part of this document was prepared during my sabbatical year while at Algonquin College. Dedicated to students – past and present. This document has been possible because of you. You have been patient – Thank You. xi xii ACKNOWLEDGMENTS To the Student Consistent and deliberate practice will give you confidence and a sound understanding of database theory and its query language. Make full use of your lab hours and use them wisely. Your learning is consolidated by doing the lab exercises and repeating them. Aquaint yourself with at least one of the following study techniques. The link leads you to a short document. Alternatively you may refer to wikipedia or search the web for the techniques. 1. SQ3R - Details can be found at . Lately SQ4R is proposed as an extension to SQ3R. The last R in 4R is for wRite 2. PQRST - The lab and lecture time is intended to be collaborative, not competitive. You will learn the topic in a different way by helping your fellow students. Do not hesitate to ask questions and get clarifications. xiii xiv TO THE STUDENT 1 Database - An Introduction 1.1 Introduction The core of any information system is a database. It is used by all computing applications, its size, structure and type vary. Formal knowledge of a database, especially relational database, is an important skill in your IT career. This lesson begins introducing a few terms, differentiating between data and information. Data when processed is ...
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture