Sadržaj Uvod . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 1. Osnovni koncepti . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 1.1 Podaci, informacije i znanje . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 1.2 Meta podaci . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 1.3 Baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 1.4 Sistemi za upravljanje bazama podataka . . . . . . . . . . . . . . . . . . . . . . . . 19 1.5 Korisnici baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 1.6 Životni ciklus baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 1.6.1 Analiza zahteva . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 1.6.2 Logički dizajn . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 1.6.3 Fizički dizajn . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 1.6.4 Implementacija . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 1.7 Mini studija slučaja – Onlajn testiranje . . . . . . . . . . . . . . . . . . . . . . . . . . 26 1.8 Pregled poglavlja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 2. ER model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 2.1 Osnovni koncepti ER modela . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 2.1.1 Entitet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 2.1.2 Veza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 2.1.3 Atribut . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 2.2 Vrste entiteta i veza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 2.3 Karakteristike veza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 2.3.1 Red (stepen) veze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 2.3.2 Kardinalnost veze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 2.3.3 Participacija . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 2.3.4 Strukturna ograničenja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 2.3.5 Strukturna ograničenja kod ternarnih veza . . . . . . . . . . . . . . . . . . 46 2.3.6 Asocijativni entitet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 2.4 EER model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 2.5 Mini studija slučaja – Onlajn testiranje . . . . . . . . . . . . . . . . . . . . . . . . . 56 2.6 Pregled poglavlja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 3. Relacioni model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 3.1 Šema relacije i relacija . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 3.2 Ključevi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 3.3 Funkcionalna zavisnost . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 3.3.1 Određivanje kandidata za ključ . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 3.4 Pravila integriteta . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 3.4.1 Domenski integritet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 3.4.2 Integritet entiteta . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 3.4.3 Referencijalni integritet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 3.5 Pregled poglavlja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 4. Mapiranje ER modela u relacioni model . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 4.1 Pravilo 1: Mapiranje jakih entiteta . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 4.2 Pravilo 2: Mapiranje atributa . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 4.3 Pravilo 3: Mapiranje binarnih veza sa kardinalnošću 1:1 . . . . . . . . . . . 86 4.3.1 Pravilo 3.1: kardinalnost 1:1 i obavezno učešće oba entiteta . . . . 86 4.3.2 Pravilo 3.2: kardinalnost 1:1 i obavezno učešće jednog entiteta . 87 4.3.3 Pravilo 3.3: kardinalnost 1:1 i neobavezno učešće oba entiteta . . 88 4.4 Pravilo 4: Mapiranje binarnih veza sa kardinalnošću 1:M . . . . . . . . . . 89 4.4.1 Pravilo 4.1: kardinalnost 1:M i obavezno učešće i na strani 1 i na strani M . 89 4.4.2 Pravilo 4.2: kardinalnost 1:M i opciono učešće na strani 1 . . . . . 90 4.4.3 Pravilo 4.3: kardinalnost 1:M i opciono učešće na strani M . . . . 91 4.4.4 Pravilo 4.4: kardinalnost 1:M i slab entitet . . . . . . . . . . . . . . . . . . 93 4.4.5 Pravilo 4.5: kardinalnost M:M . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 4.5 Pravilo 5: Mapiranje unarnih veza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 4.5.1 Pravilo 5.1: kardinalnost 1:M . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 4.5.2 Pravilo 5.2: kardinalnost M:M . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 4.6 Pravilo 6: Mapiranje ternarnih veza . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 4.7 Pravilo 7: Mapiranje generalizacije i specijalizacije (EER model) . . . . . 99 4.8 Mini studija slučaja – Onlajn testiranje . . . . . . . . . . . . . . . . . . . . . . . . 102 4.9 Pregled poglavlja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 5. Normalizacija podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.1 Redundansa podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.2 Anomalije ažuriranja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 5.2.1 Anomalija unosa . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 5.2.2 Anomalija izmena . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 5.2.3 Anomalija brisanja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 5.3 Normalizacija i funkcionalna zavisnost . . . . . . . . . . . . . . . . . . . . . . . . . 108 5.3.1 Tipovi funkcionalnih zavisnosti . . . . . . . . . . . . . . . . . . . . . . . . . . 109 5.3.2 Mini studija slučaja – Određivanje funkcionalnih zavisnosti . . . 111 5.4 Proces normalizacije . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 5.4.1 1NF – Prva normalna forma . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 5.4.2 2NF – Druga normalna forma . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 5.4.3 3NF – Treća normalna forma . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 5.4.4 BCNF – Bojs-Kodova normalna forma . . . . . . . . . . . . . . . . . . . . . 120 5.4.5 4NF – Četvrta normalna forma . . . . . . . . . . . . . . . . . . . . . . . . . . 121 5.4.6 Mini studija slučaja – Normalizacija podataka za prodaju artikala . . 122 5.5 ER model i normalizacija . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 5.6 Denormalizacija . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 5.7 Pregled poglavlja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 6. Implementacija baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 6.1 Kreiranje baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 6.2 SQL tipovi podataka . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 6.3 Kreiranje tabela . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 6.4 Mini studija slučaja – Implementacija baze podataka Prodaja . . . . . . 139 6.5 Mini studija slučaja – Onlajn testiranje . . . . . . . . . . . . . . . . . . . . . . . . 141 6.6 Pregled poglavlja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 7. Studije slučaja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 7.1 Studija slučaja: Praćenje atletskih trka . . . . . . . . . . . . . . . . . . . . . . . . . 149 7.1.1 Specifikacija zahteva . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 7.1.2 Entiteti i atributi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 7.1.3 Veze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 7.1.4 Strukturna ograničenja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 7.1.5 ER dijagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 7.1.6 Mapiranje ER modela u relacioni model . . . . . . . . . . . . . . . . . . . 159 7.1.7 Implementacija baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . 161 7.2 Studija slučaja: Muzej likovne umetnosti . . . . . . . . . . . . . . . . . . . . . . . 166 7.2.1 Specifikacija zahteva . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 7.2.2 Entiteti i atributi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 7.2.3 Veze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 7.2.4 Strukturna ograničenja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 7.2.5 ER dijagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 7.2.6 Mapiranje ER modela u relacioni model . . . . . . . . . . . . . . . . . . . 174 7.2.7 Implementacija baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . 175 7.3 Studija slučaja: Auto-servis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 7.3.1 Specifikacija zahteva . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 7.3.2 Entiteti i atributi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 7.3.3 Veze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 7.3.4 Strukturna ograničenja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 7.3.5 ER dijagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 7.3.6 Mapiranje ER modela u relacioni model . . . . . . . . . . . . . . . . . . . 190 7.3.7 Implementacija baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . 191 7.4. Studija slučaja: Auto-škola . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 7.4.1 Specifikacija zahteva . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 7.4.2 Entiteti i atributi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 7.4.3 Veze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 7.4.4 Strukturna ograničenja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203 7.4.5 ER dijagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 7.4.6 Mapiranje ER modela u relacioni model . . . . . . . . . . . . . . . . . . . 205 7.4.7 Implementacija baze podataka . . . . . . . . . . . . . . . . . . . . . . . . . . 206 7.5 Studija slučaja: Srednja škola . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 7.5.1 Deo 1 – Škole, smerovi, predmeti i profesori . . . . . . . . . . . . . . . . 213 7.5.2 Deo 2 – Nastavni udžbenici . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 7.5.3 Deo 3 – Školska ekskurzija . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 7.5.4 Objedinjena šema baze podataka . . . . . . . . . . . . . . . . . . . . . . . . 219 7.6 Pregled poglavlja . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Literatura . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225

What You Will Learn Study the roles within a Dynamics NAV partner and within a customer’s company Create reusable data migration packages Work with the debugger to pinpoint error messages Get to grips with the key tables used in data reporting and analysis Successfully upgrade your installation to the latest version Manage and expand your existing installation with additional functionalities Explore the free third-party add-ons that can leverage your existing installation Book Description Microsoft Dynamics NAV 2013 is an Enterprise Resource Planning (ERP) application used in all kinds of organizations around the world. It provides a great variety of functionality out-of-the-box in different topics such as accounting, sales, purchase processing, logistics, or manufacturing. It also allows companies to grow the application by customizing the solution to meet specific requirements. This book is a hands-on tutorial on working with a real Dynamics NAV implementation. You will learn about the team from your Microsoft Dynamics NAV partner as well as the team within the customer’s company. This book provides an insight into the different tools available to migrate data from the client’s legacy system into Microsoft Dynamics NAV. If you are already live with Microsoft Dynamics NAV, this books talks about upgrades and what to expect from them. We’ll also show you how to implement additional or expanding functionalities within your existing Microsoft Dynamics NAV installation, perform data analysis, debug error messages, and implement free third-party add-ons to your existing installation. This book will empower you with all the skills and knowledge you need for a successful implementation. Authors Alex Chow Alex Chow has been working with Microsoft Dynamics NAV, formerly Navision, since 1999. Over the years, he has conducted hundreds of implementations across multiple industries. His customers range from $2-million-a-year small enterprises to $500-million-a-year multinational corporations. Over the course of his Dynamics NAV career, he has often been designated as the primary person responsible for the success and failure of Dynamics NAV implementations. The fact that Alex is still in the Dynamics NAV business means that he's been pretty lucky so far. His extensive career in the Dynamics NAV business is evidence of his success rate and expertise. With a background in implementing all functions and modules inside and outside of Microsoft Dynamics NAV, Alex has encountered and resolved the most practical to the most complex requirements and business rules. Through these experiences, he has learned that sometimes you have to be a little crazy to have a competitive edge. Believing that sharing these experiences and knowledge would benefit the Dynamics NAV community, Alex writes about his journey at www.dynamicsnavconsultant.com. He founded AP Commerce (www.apcommerce.com) in 2005. It is a full-service Dynamics NAV service center. In addition, Alex has written a book about Dynamics NAV titled Getting Started with Dynamics NAV 2013 Application Development by Packt Publishing. He lives in southern California with his beautiful wife and two lovely daughters. He considers himself the luckiest man in the world. Table of Contents

Danas se kao uslov za većinu radnih mesta traži poznavanje rada na računaru. Šta se tačno podrazumeva pod tim pojmom kada je u pitanju rad s bazama podataka, saznaćete ako pročitate knjigu "ECDL 5.0 Modul 5: baze podataka". Zahvaljujući ovom sistematičnom, razumljivom i konciznom udžbeniku, steći ćete nivo znanja potreban za polaganje ispita za Modul 5 međunarodno priznatog sertifikata ECDL. Prema nastavnom planu za ECDL Modul 5 – Baze podataka, od kandidata se zahteva da razume osnovne pojmove baza podataka i pokaže sposobnost njihovog korišćenja na računaru. Kandidat treba da bude u stanju da razume šta je baza podataka, kako je organizovana i kako funkcioniše; napravi jednostavnu bazu i pregleda njen sadržaj na različite načine; napravi tabelu, definiše i izmeni polja i njihova svojstva, unese podatke u tabele i ispravlja ih; sortira i filtrira tabelu ili obrazac; napravi, izmeni i pozove upit kako bi dobio tražene podatke iz baze; razume šta je obrazac i napravi obrazac za unos, izmenu i brisanje zapisa i podataka u zapisima; napravi izveštaje i pripremi ih za distribuciju. Posebna prednost ovog udžbenika je to što je pisan tačno prema nastavnom planu za ECDL Modul 5, kako bi se kandidati lakše pripremili za polaganje ispita za sertifikat ECDL. Istovremeno, ovaj udžbenik je dobar priručnik za sve one koji žele da savladaju i koriste Access 2007, bez obzira na to pod kojim je operativnim sistemom instaliran. Sadržaj Uvod Poglavlje 1: Upoznavanje s bazama podataka Poglavlje 2: Korišćenje aplikacije Poglavlje 3: Tabele Poglavlje 4: Dobijanje informacija Poglavlje 5: Objekti Poglavlje 6: Izlazi Prilog A: Nastavni plan za ECDL 5.0 Modul 5 - baze podataka Prilog B: Probni testovi za Modul 5 Indeks O autoru Mr Milorad Marković je dipl. mašinski inženjer i magistar tehničkih nauka u oblasti proizvodnih tehnologija i upravljanja projektima. Magistrirao je na Cranfield Institute of Technology, Engleska. Ima dvadesetogodišnje iskustvo u radu s računarima i korišćenju paketa Microsoft Office. Radio je preko šest godina u inostranstvu gde je stekao bogato iskustvo u korišćenju Office paketa, koji permanentno izučava i primenjuje u praksi.

What You Will Learn Implement PostgreSQL features for reliability and performance Harness the power of the latest PostgreSQL 9.4 features Manage PostgreSQL versions 9.0, 9.1, 9.2, 9.3, and 9.4 Advanced technical tips for experienced users Explore best practices for planning and designing live databases Select and implement robust backup and recovery techniques Concise and clear guidance on replication and high availability Latest details on Logical Replication and Bi-Directional Replication Book Description PostgreSQL is a powerful, open source, object-relational database system, fast becoming one of the world's most popular server databases with an enviable reputation for performance and stability and an enormous range of advanced features. Starting with short and simple recipes to get you back up and running with an exploration of the ins and outs of your database, you will soon dive into core features such as configuration, server control, tables, and data. This is a practical guide aimed at giving sysadmins and database administrators the necessary toolkit to be able to set up, run, and extend powerful databases with PostgreSQL. Authors Simon Riggs Simon Riggs is CTO of 2ndQuadrant and an active PostgreSQL committer. He has contributed to open source PostgreSQL as a major developer for more than 10 years, having written and designed many new features in every release over that period. Feature credits include replication, performance, business intelligence, management, and security. Under his guidance, 2ndQuadrant is now the leading developer of open source PostgreSQL and a Platinum Sponsor of the PostgreSQL project, serving hundreds of clients in the US, Europe, Asia Pacific, Middle East, and Africa. Simon is a frequent speaker at many conferences and is well known for his talks on PostgreSQL futures and different aspects of replication. He has also worked with many different databases as a developer, architect, data analyst, and designer with companies across the US and Europe for nearly 30 years. Gianni Ciolli Gianni Ciolli is a principal consultant for 2ndQuadrant Italia, where he has worked since 2008 as a developer, consultant, and trainer. He is a speaker at PostgreSQL conferences in Europe and abroad, and his other IT skills include functional languages and symbolic computing. Gianni has a PhD in mathematics and is the author of published research on algebraic geometry, theoretical physics, and formal proof theory. He previously worked at the University of Florence as a researcher and teacher. Gianni has been working with free and open source software for almost 20 years. From 2001 to 2004, he was president of the PLUG – Prato Linux User Group. He organized many editions of the Italian PostgreSQL conference, and in 2013 was elected to the board of ITPUG, the Italian PostgreSQL Users Group. He currently lives in London with his son; his other interests include music, drama, poetry, and sport, in particular athletics, as he often competes in combined events. Continue reading Hannu Krosing Hannu Krosing is a principal consultant at 2ndQuadrant and a technical advisor at Ambient Sound Investments. As the original database architect at Skype Technologies, he was responsible for designing the SkyTools suite of replication and scalability technology. He has worked with and contributed to the PostgreSQL project for more than 12 years. Gabriele Bartolini A long-time open source programmer, Gabriele Bartolini is a principal consultant with 2ndQuadrant and an active member of the international PostgreSQL community. He has a degree in statistics from the University of Florence. His areas of expertise are data mining and data warehousing, having worked on web traffic analysis in Australia and Italy. Gabriele currently lives in Prato, a small but vibrant city located in the northern part of Tuscany, Italy. His second home is Melbourne, Australia, where he studied at Monash University and worked in the ICT sector. His hobbies include playing his Fender Stratocaster electric guitar and "calcio" (football or soccer, depending on which part of the world you come from). Table of Contents Chapter 1: First Steps Chapter 2: Exploring the Database Chapter 3: Configuration Chapter 4: Server Control Chapter 5: Tables and Data Chapter 6: Security Chapter 7: Database Administration Chapter 8: Monitoring and Diagnosis Chapter 9: Regular Maintenance Chapter 10: Performance and Concurrency Chapter 11: Backup and Recovery Chapter 12: Replication and Upgrades

What You Will Learn Perform regular maintenance tasks to keep your database steady and achieve optimal performance Design and implement various high availability and replication features to provide redundancy, fault tolerance, and failover Diagnose and troubleshoot CPU, memory, and I/O related database performance issues Perform database operations using languages such as Perl and Python Discover the different backup and recovery strategies that can be implemented in PostgreSQL Implement connection pooling methods to achieve load balancing Book Description PostgreSQL is an open source database management system. It is used for a wide variety of development practices such as software and web design, as well as for handling large datasets (big data). With the goal of teaching you the skills to master PostgreSQL, the book begins by giving you a glimpse of the unique features of PostgreSQL and how to utilize them to solve real-world problems. With the aid of practical examples, the book will then show you how to create and manage databases. You will learn how to secure PostgreSQL, perform administration and maintenance tasks, implement high availability features, and provide replication. The book will conclude by teaching you how to migrate information from other databases to PostgreSQL. Authors Chitij Chauhan Chitij Chauhan currently works as a senior database administrator at an IT-based MNC in Chandigarh. He has over 10 years of work experience in the field of database and system administration, with specialization in MySQL clustering, PostgreSQL, Greenplum, Informix DB2, SQL Server 2008, Sybase, and Oracle. He is a leading expert in the area of database security, with expertise in database security products such as IBM InfoSphere Guardium, Oracle Database Vault, and Imperva. Table of Contents Chapter 1: Managing Databases and the PostgreSQL Server Chapter 2: Controlling Security Chapter 3: Backup and Recovery Chapter 4: Routine Maintenance Tasks Chapter 5: Monitoring the System Using Unix Utilities Chapter 6: Monitoring Database Activity and Investigating Performance Issues Chapter 7: High Availability and Replication Chapter 8: Connection Pooling Chapter 9: Table Partitioning Chapter 10: Accessing PostgreSQL from Perl Chapter 11: Accessing PostgreSQL from Python Chapter 12: Data Migration from Other Databases and Upgrading the PostgreSQL Cluster

What You Will Learn Refresh your knowledge of SQL with PostgreSQL-supported DDL and DML Explore native PostgreSQL procedural languages and learn how to write custom functions Leverage the power of indexes for optimal database efficiency Unfold tricky trigger concepts and implement them in native procedural languages Discover how to implement views, partitioned tables, and window functions in PostgreSQL Design efficient queries with query optimization and query analysis Communicate with PostgreSQL servers using foreign data wrappers such as LibPQ and ECPG Extend database powers using foreign data wrappers and achieve maintainability via PostgreSQL extensions Book Description PostgreSQL is an enterprise-level database that competes among proprietary database vendors, owing to its remarkable feature set and reliability without the expensive licensing overhead. This book is a comprehensive and pragmatic guide to developing databases in PostgreSQL. Beginning with a refresher of basic SQL skills, you will gradually be exposed to advanced concepts, such as learning how to program in native PostgreSQL procedural languages, implementing triggers, custom functions, and views. You will learn database optimization techniques such as query optimization and indexing while exploring extensive code examples. Lastly, you will explore foreign data wrappers, implementing extensibility, and improving maintainability. Authors Ibrar Ahmed Ibrar Ahmed has over 15 years of experience in software development. He is currently working as a software architect for a PostgreSQL-based company. He is an open source evangelist. He has contributed features such as pg_migrator (now called pg_upgrade) and Index Only Scans to PostgreSQL and has contributed to Google Chromium projects such as platform-independent FTP implementations of the Google Chrome browser, Chromium test framework enhancements, and porting of listen_socket and telnet_server to Linux. He has a lot of experience in implementing network protocols and writing Linux kernel modules. Apart from his professional life, he delivers lectures at universities and reads books. Asif Fayyaz Asif Fayyaz is an open source technology lover and practitioner. He is currently working as a senior software quality assurance engineer at a leading PostgreSQL based company. He has been learning and using PostgreSQL for the last 7 years. His areas of interest are exploring PostgreSQL replication solutions and software test automation solutions for desktop, web, and mobile applications. His future endeavors include joining a sports channel for set-top box automation in Python. Apart from his professional activities, he, along with his dedicated friends, is keen to find ways that can make life easier for those who are facing the worst living conditions. His other passions include, but are not limited to, traveling to different places, trying different cuisines, and reading books if somehow permitted by his loving family. Continue reading Amjad Shahzad Amjad Shahzad has been working in the open source software industry for the last 10 years. He is currently working as a senior quality assurance engineer at a leading PostgreSQL-based company, which is the only worldwide provider of enterprise-class products and services based on PostgreSQL. Amjad's core expertise lies in the areas of pg_upgrade, slony and streaming replication, Cloud database, and database partitioning. His future endeavors include exploring PostgreSQL replication solutions. Apart from his professional activities, he is also involved in doing social activities that involve helping people stand on their feet. In his free time, he likes to explore nature by doing outdoor activities, including hiking, trekking, and nature photography. Table of Contents Chapter 1: Getting Started with PostgreSQL Chapter 2: The Procedural Language Chapter 3: Working with Indexes Chapter 4: Triggers, Rules, and Views Chapter 5: Window Functions Chapter 6: Partitioning Chapter 7: Query Optimization Chapter 8: Dealing with Large Objects Chapter 9: Communicating with PostgreSQL Using LibPQ Chapter 10: Embedded SQL in C – ECPG Chapter 11: Foreign Data Wrapper Chapter 12: Extensions

“Given the authors’ reputations, I expected to be impressed. I was blown away! . . . Most SQL books sit on my shelf. This one will live on my desk.” –Roger Carlson, Microsoft Access MVP (2006-2015) “Rather than stumble around reinventing wheels or catching glimpses of the proper approaches, do yourself a favor: Buy this book.” —Dave Stokes, MySQL Community Manager, Oracle Corporation Effective SQL brings together practical solutions and insights so you can solve complex problems with SQL and design databases that simplify data management in the future. It’s the only modern book that brings together advanced best practices and realistic example code for all of these versions of SQL: IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL. Drawing on their immense experience as world-class database consultants and instructors, the authors identify 61 proven approaches to writing better SQL. Wherever SQL versions vary, the authors illuminate the key nuances, so you can get the most out of whatever version you prefer. This full-color guide provides clear, practical explanations; expert tips; and plenty of usable code. Going far beyond mere syntax, it addresses issues ranging from optimizing database designs to managing hierarchies and metadata. If you already know SQL’s basics, this guide will help you become a world-class SQL problem-solver. Craft better logical data models, and fix flawed models Implement indexes that improve query performance Handle external data from sources you don’t control Extract and aggregate the information you need, as efficiently as possible Write more flexible subqueries Analyze and retrieve metadata using your database platform of choice Use Cartesian Products and Tally Tables to solve problems you can’t address with conventional JOINs Model hierarchical data: managing SQL’s tradeoffs and shortcomings Features 61 practical, easy-to-use solutions -- all in full-color, with simple explanations and no jargon! Designed for all leading SQL implementations, with additional techniques for widely used versions. (Tested on IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.) Identifies common misconceptions and pitfalls when authoring solutions using SQL and provides effective solutions to common scenarios Table of Contents Foreword xiii Acknowledgments xv About the Authors xvii About the Technical Editors xix Introduction 1 A Brief History of SQL 1 Database Systems We Considered 5 Sample Databases 6 Where to Find the Samples on GitHub 7 Summary of the Chapters 8 Chapter 1: Data Model Design 11 Item 1: Verify That All Tables Have a Primary Key 11 Item 2: Eliminate Redundant Storage of Data Items 15 Item 3: Get Rid of Repeating Groups 19 Item 4: Store Only One Property per Column 21 Item 5: Understand Why Storing Calculated Data Is Usually a Bad Idea 25 Item 6: Define Foreign Keys to Protect Referential Integrity 30 Item 7: Be Sure Your Table Relationships Make Sense 33 Item 8: When 3NF Is Not Enough, Normalize More 37 Item 9: Use Denormalization for Information Warehouses 43 Chapter 2: Programmability and Index Design 47 Item 10: Factor in Nulls When Creating Indexes 47 Item 11: Carefully Consider Creation of Indexes to Minimize Index and Data Scanning 52 Item 12: Use Indexes for More than Just Filtering 56 Item 13: Don’t Go Overboard with Triggers 61 Item 14: Consider Using a Filtered Index to Include or Exclude a Subset of Data 65 Item 15: Use Declarative Constraints Instead of Programming Checks 68 Item 16: Know Which SQL Dialect Your Product Uses and Write Accordingly 70 Item 17: Know When to Use Calculated Results in Indexes 74 Chapter 3: When You Can’t Change the Design 79 Item 18: Use Views to Simplify What Cannot Be Changed 79 Item 19: Use ETL to Turn Nonrelational Data into Information 85 Item 20: Create Summary Tables and Maintain Them 90 Item 21: Use UNION Statements to “Unpivot” Non-normalized Data 94 Chapter 4: Filtering and Finding Data 101 Item 22: Understand Relational Algebra and How It Is Implemented in SQL 101 Item 23: Find Non-matches or Missing Records 108 Item 24: Know When to Use CASE to Solve a Problem 110 Item 25: Know Techniques to Solve Multiple-Criteria Problems 115 Item 26: Divide Your Data If You Need a Perfect Match 120 Item 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and Time 124 Item 28: Write Sargable Queries to Ensure That the Engine Will Use Indexes 127 Item 29: Correctly Filter the “Right” Side of a “Left” Join 132 Chapter 5: Aggregation 135 Item 30: Understand How GROUP BY Works 135 Item 31: Keep the GROUP BY Clause Small 142 Item 32: Leverage GROUP BY/HAVING to Solve Complex Problems 145 Item 33: Find Maximum or Minimum Values Without Using GROUP BY 150 Item 34: Avoid Getting an Erroneous COUNT() When Using OUTER JOIN 156 Item 35: Include Zero-Value Rows When Testing for HAVING COUNT(x) < Some Number 159 Item 36: Use DISTINCT to Get Distinct Counts 163 Item 37: Know How to Use Window Functions 166 Item 38: Create Row Numbers and Rank a Row over Other Rows 169 Item 39: Create a Moving Aggregate 172 Chapter 6: Subqueries 179 Item 40: Know Where You Can Use Subqueries 179 Item 41: Know the Difference between Correlated and Non-correlated Subqueries 184 Item 42: If Possible, Use Common Table Expressions Instead of Subqueries 190 Item 43: Create More Efficient Queries Using Joins Rather than Subqueries 197 Chapter 7: Getting and Analyzing Metadata 201 Item 44: Learn to Use Your System’s Query Analyzer 201 Item 45: Learn to Get Metadata about Your Database 212 Item 46: Understand How the Execution Plan Works 217 Chapter 8: Cartesian Products 227 Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the First 227 Item 48: Understand How to Rank Rows by Equal Quantiles 231 Item 49: Know How to Pair Rows in a Table with All Other Rows 235 Item 50: Understand How to List Categories and the Count of First, Second, or Third Preferences 240 Chapter 9: Tally Tables 247 Item 51: Use a Tally Table to Generate Null Rows Based on a Parameter 247 Item 52: Use a Tally Table and Window Functions for Sequencing 252 Item 53: Generate Multiple Rows Based on Range Values in a Tally Table 257 Item 54: Convert a Value in One Table Based on a Range of Values in a Tally Table 261 Item 55: Use a Date Table to Simplify Date Calculation 268 Item 56: Create an Appointment Calendar Table with All Dates Enumerated in a Range 275 Item 57: Pivot Data Using a Tally Table 278 Chapter 10: Modeling Hierarchical Data 285 Item 58: Use an Adjacency List Model as the Starting Point 286 Item 59: Use Nested Sets for Fast Querying Performance with Infrequent Updates 288 Item 60: Use a Materialized Path for Simple Setup and Limited Searching 291 Item 61: Use Ancestry Traversal Closure for Complex Searching 294 Appendix: Date and Time Types, Operations, and Functions 299 IBM DB2 299 Microsoft Access 303 Microsoft SQL Server 305 MySQL 308 Oracle 313 PostgreSQL 315 Index 317 Authors John L. Viescas is an independent database consultant with more than 45 years of experience. He began his career as a systems analyst, designing large database applications for IBM mainframe systems. He spent six years at Applied Data Research in Dallas, Texas, where he directed a staff of more than 30 people and was responsible for research, product development, and customer support of database products for IBM mainframe computers. While working at Applied Data Research, John completed a degree in business finance at the University of Texas at Dallas, graduating cum laude. John joined Tandem Computers, Inc., in 1988, where he was responsible for the development and implementation of database marketing programs in Tandem’s U.S. Western Sales region. He developed and delivered technical seminars on Tandem’s relational database management system, NonStop SQL. John wrote his first book, A Quick Reference Guide to SQL (Microsoft Press, 1989), as a research project to document the similarities in the syntax among the ANSI-86 SQL standard, IBM’s DB2, Microsoft’s SQL Server, Oracle Corporation’s Oracle, and Tandem’s NonStop SQL. He wrote the first edition of Running Microsoft® Access (Microsoft Press, 1992) while on sabbatical from Tandem. He has since written four editions of Running, three editions of Microsoft® Office Access Inside Out (Microsoft Press, 2003, 2007 and 2010—the successor to the Running series), and Building Microsoft® Access Applications (Microsoft Press, 2005). He is also the best-selling author of SQL Queries for Mere Mortals®, Third Edition (Addison-Wesley, 2014). John currently holds the record for the most consecutive years being awarded MVP from Microsoft, having received the award from 1993-2015. John makes his home with his wife of more than 30 years in Paris, France. Douglas J. Steele has been working with computers, both mainframe and PC, for almost 45 years. (Yes, he did use punch cards in the beginning!) He worked for a large international oil company for more than 31 years before retiring in 2012. Databases and data modeling were a focus for most of that time, although he finished his career by developing the SCCM task sequence to roll Windows 7 out to over 100,000 computers worldwide. Recognized by Microsoft as an MVP (Most Valuable Professional) for more than 16 years, Doug has authored numerous articles on Access, was co-author of Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs (Wiley Publishing, 2010), and has been technical editor for a number of books. Doug holds a master’s degree in systems design engineering from the University of Waterloo (Ontario, Canada), where his research centered on designing user interfaces for non-traditional computer users. (Of course, this was in the late ’70s, so few people were traditional computer users at the time!) This research stemmed from his background in music (he holds an associateship in piano performance from the Royal Conservatory of Music, Toronto). He is also obsessed with beer, and is a graduate of the brewmaster and brewery operations management program at Niagara College (Niagara-on-the-Lake, Ontario). Doug lives with his lovely wife of more than 34 years in St. Catharines, Ontario. Doug can be reached at [email protected]. Ben G. Clothier is a Solution Architect with IT Impact, Inc., a premier Access and SQL Server development shop based in Chicago, Illinois. He has worked as a freelance consultant with notable shops including J Street Technology and Advisicon, and has worked on Access projects from small, one-man solutions to company-wide line of business applications. Notable projects include job tracking and inventory for a cement company, a Medicare insurance plan generator for an insurance provider, and order management for an international shipping company. Ben is an administrator at UtterAccess and was a coauthor, with Teresa Hennig, George Hepworth and Doug Yudovich of Microsoft® Access 2013 Programming (Wiley 2013), and with Tim Runcie and George Hepworth, of Microsoft® Access in a SharePoint World (Advisicon, 2011), and a contributing author for Access 2010 Programmer’s Reference (Wiley, 2010). He holds certifications for Microsoft SQL Server 2012 Solution Associate and MySQL 5.0 Certified Developer, among others. He has been a Microsoft MVP since 2009. Ben lives in San Antonio, Texas, with his wife, Suzanne, and his son, Harry.

Oracle PL/SQL Recipes is your go to book for PL/SQL programming solutions. It takes a task-oriented approach to PL/SQL programming that lets you quickly look up a specific task and see the pattern for a solution. Then it's as simple as modifying the pattern for your specific application and implementing it. And you’re done and home for dinner. Oracle PL/SQL Recipes is another in Apress’ ongoing series of recipe books aimed at Oracle practitioners. The recipe format is ideal for the busy professional who just needs to get the job done. Covers the most common PL/SQL programming problems Presents solutions in ready-to-use format Stays short and to-the-point What you’ll learn Move business logic into the database Implement robust exception-handling Optimize and debug, taking advantage of Oracle SQL Developer Integrate PL/SQL with web-based applications Interface with Java code and dynamic languages in the database, and running externally Run the UTPLSQL unit-testing framework Who this book is for Oracle PL/SQL Recipes is database administrators and developers wanting to take advantage of Oracle Database’s built-in support for procedural logic. Database administrators can use PL/SQL to automate administrative tasks, audit access to sensitive data, and more. Developers will be able to optimize processing, push business logic into the database layer, and interface with Java and web applications. Table of Contents PL/SQL Fundamentals Essential SQL Looping and Logic Functions, Packages, and Procedures Triggers Type Conversion Numbers, Strings, and Dates Dynamic SQL Exceptions PL/SQL Collections and Records Automating Routine Tasks Oracle SQL Developer Analyzing and Improving Performance Using PL/SQL on the Web Java in the Database Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython Unit Testing With utPLSQL

Understand the basics of relational databases, relational algebra, and data modeling Install a PostgreSQL server, create a database, and implement your data model Create tables and views, define indexes and stored procedures, and implement triggers Make use of advanced data types such as Arrays, hstore, and JSONB Connect your Python applications to PostgreSQL and work with data efficiently Identify bottlenecks to enhance reliability and performance of database applications PostgreSQL is one of the most popular open source database management systems in the world, and it supports advanced features included in SQL standards. This book will familiarize you with the latest features in PostgreSQL 11, and get you up and running with building efficient PostgreSQL database solutions from scratch. Learning PostgreSQL, 11 begins by covering the concepts of relational databases and their core principles. You’ll explore the Data Definition Language (DDL) and commonly used DDL commands supported by ANSI SQL. You’ll also learn how to create tables, define integrity constraints, build indexes, and set up views and other schema objects. As you advance, you’ll come to understand Data Manipulation Language (DML) and server-side programming capabilities using PL/pgSQL, giving you a robust background to develop, tune, test, and troubleshoot your database application. The book will guide you in exploring NoSQL capabilities and connecting to your database to manipulate data objects. You’ll get to grips with using data warehousing in analytical solutions and reports, and scaling the database for high availability and performance. By the end of this book, you’ll have gained a thorough understanding of PostgreSQL 11 and developed the necessary skills to build efficient database solutions. Monitor, secure, and fine-tune your PostgreSQL 11 database Learn client-side and server-side programming using SQL and PL/pgSQL Discover tips on implementing efficient database solutions Table of contents 1 Relational Databases Database management systems Relational and object-relational databases Relational algebra Data modeling Summary Questions 2 PostgreSQL in Action An overview of PostgreSQL PostgreSQL capabilities Installing PostgreSQL Summary Questions 3 PostgreSQL Basic Building Blocks Database coding PostgreSQL object hierarchy PostgreSQL database components PostgreSQL native data types The car web portal database Summary Questions 4 PostgreSQL Advanced Building Blocks Views Indexes Functions User-defined data types Trigger and rule systems Summary Questions 5 SQL Language SQL fundamentals Querying data with SELECT statements Changing the data in a database Summary Questions 6 Advanced Query Writing Common table expressions Window functions Advanced SQL techniques Summary Questions 7 Server-Side Programming with PL/pgSQL The SQL language and PL/pgSQL – a comparison Functions and procedures – a comparison PostgreSQL function parameters The PostgreSQL PL/pgSQL control statements Function predefined variables Exception handling Dynamic SQL Summary Questions 8 OLAP and Data Warehousing OLTP and OLAP Online analytical processing Partitioning Parallel query Index-only scans Summary Questions 9 Beyond Conventional Data Types Arrays The hash store data structure The JSON data structure A PostgreSQL full-text search Summary Questions 10 Transactions and Concurrency Control Transactions Explicit locking Transaction in functions and procedures Summary Questions 11 PostgreSQL Security Authentication in PostgreSQL Authentication best practices PostgreSQL default access privileges Role system and proxy authentication PostgreSQL security levels Encrypting data Summary Questions 12 The PostgreSQL Catalog The system catalog System catalog for administrators Cleaning up the database Tuning for performance Selective dump Summary Questions 13 Optimizing Database Performance PostgreSQL configuration tuning Tuning performance for write Tuning performance for read Detecting problems in query plans Common mistakes in writing queries Table partitioning Query rewriting Summary Questions 14 Testing Technical requirements Unit testing Database-abstraction interfaces Summary Questions 15 Using PostgreSQL in Python Applications Technical requirements Python DB-API 2.0 Low-level database access with Psycopg 2 Alternative drivers for PostgreSQL Object-relational mapping with SQLAlchemy Summary Questions 16 Scalability Technical requirements The problem of scalability and the CAP theorem Data replication in PostgreSQL Building a scalable PostgreSQL solution Summary Questions Further reading 17 What's Next? Fundamentals Working with PostgreSQL software Working with data in PostgreSQL databases Summary

Glavna tema ove knjige je pravljenje skladišta podataka, odnosno posebne vrste baze podataka koja privlači sve veće interesovanje u oblasti informacione tehnologije, a čija je svrha obezbeđivanje dostupnosti informacija. Autor daje kratak osvrt na istorijat skladišta, osnivače ovog koncepta Inmona i Kimballa, a se okreće prvoj generaciji skladišta podataka, prelazi na drugu i jednostavnom terminologijom objašnjava šta su skladišta podataka, kako se ona koriste, njihove glavne komponente, terminologiju koja se koristi u vezi sa skladištima i slično. Knjiga sadrži veliku količinu materijala o projektovanju, pogotovo u oblasti modela podataka, tretiranju vremena i koncepcijskom, logičkom i fizičkom nivou razvoja. Data je i kompletna metodologija koja pruža pomoć na svim nivoima razvoja. Fokus je na izradi modela usredsređenog na kupca koji je idealan za podršku složenim zahtevima upravljanja odnosima sa kupcima.

Ovaj udžbenik je napisan za studente Računarskog fakulteta u Beogradu, za predmet Informacioni sistemi, prema važećem nastavnom planu. Predmet razmatranja ovog udžbenika jeste sticanje osnovnih znanja o modeliranju procesa i podataka kao i projektovanju relacionih baza podataka odnosno, definisanja šeme baze podataka i korisničke aplikacije. Modeliranje procesa je izvedeno korišćenjem standarad IDEF0 realizovanog kroz CASE alat BPwin. Logičko modeliranje podataka izvedeno korišćenjem standarad IE realizovanog kroz CASE alat ERwin. Dalje su izložene aktivnosti za fizičko modeliranje podataka i izradu šeme baze podataka, kao osnova vezanih za relacione baze podataka uz korišćenje standarda IE, tj. CASE alata ERwin. U ovoj knjizi je usvojen, odnosno biće razmatran, MS Access i kao SUBP i kao programsko okruženje. Prednost ovog udžbenika je u tome što su sve aktivnosti objašnjene kroz jedan konkretan primer, tako da udžbenik može poslužiti i drugim zainteresovanim korisnicima da samostalno steknu znanje iz ove oblasti. Na kraju udžbenika dat je primer seminarskog rada koji se izvodi na vežbama i vezan je za poslovnu aktivnost „Praćenje ispita”.

Pri pisanju ove knjige bila su postavljena četiri osnovna cilja. Prvi je bio da se, polazeći od matematičkog pojma strukture, ukaže na specifičnosti i osnovne vrste struktura podataka. Drugi je bio da se definišu strukture i osobine različitih vrsta organizacije datoteka. Treći je bio da se definišu i izvedu mere performansi obrade svake od opisanih vrsta organizacije datoteka. Ove mere treba da posluže projektantu kao putokaz pri izboru najpogodnije organizacije datoteke. Četvrti cilj je bio da se prikažu algoritmi za formiranje, korišćenje i ažuriranje struktura podataka u operativnoj memoriji i datotekama. Ovi algoritmi predstavljaju osnov za praktično korišćenje struktura podataka na računaru

About This Book Learn about the PostgreSQL development life cycle including its testing and refactoring Build productive database solutions and use them in Java applications A comprehensive guide to learn about SQL, PostgreSQL procedural language and PL/pgSQL Who This Book Is For If you are a student, database developer or an administrator, interested in developing and maintaining a PostgreSQL database, then this book is for you. No knowledge of database programming or administration is necessary. Readers should, however, know SQL. What You Will Learn Learn concepts of data modelling and relation algebra Install and set up PostgreSQL database server and client software Implement data structures in PostgreSQL Manipulate data in the database using SQL Implement data processing logic in the database with stored functions, triggers and views Test database solutions and assess the performance Integrate database with Java applications Detailed knowledge of the main PostgreSQL building objects, most used extensions Practice database development life cycle including analysis, modelling, (documentation), testing, bug fixes and refactoring In Detail PostgreSQL is one of the most powerful and easy to use database management systems. It has strong support from the community and is being actively developed with a new release every year. PostgreSQL supports the most advanced features included in SQL standards. Also it provides NoSQL capabilities, and very rich data types and extensions. All that makes PostgreSQL a very attractive solution in various kinds of software systems. The book starts with the introduction of relational databases with PostegreSQL. It then moves on to covering data definition language (DDL) with emphasis on PostgreSQL and common DDL commands supported by ANSI SQL. You will then learn the data manipulation language (DML), and advanced topics like locking and multi version concurrency control (MVCC). This will give you a very robust background to tune and troubleshoot your application. The book then covers the implementation of data models in the database such as creating tables, setting up integrity constraints, building indexes, defining views and other schema objects. Next, it will give you an overview about the NoSQL capabilities of PostgreSQL along with Hstore, XML, Json and arrays. Finally by the end of the book, you'll learn to use the JDBC driver and manipulate data objects in the Hibernate framework. Authors Salahaldin Juba Salahaldin Juba has over 10 years of experience in industry and academia, with a focus on database development for large-scale and enterprise applications. He holds a master's degree of science in environmental management and a bachelor's degree of engineering in computer systems. Achim Vannahme Achim Vannahme works as a senior software developer at a mobile messaging operator, where he focuses on software quality and test automation. He holds a degree in computer science and has over 10 years of experience in using Java and PostgreSQL in distributed and high-performance applications. Continue reading Andrey Volkov Andrey Volkov pursued his education in information systems in the banking sector. He started his career as a financial analyst in a commercial bank. Here, Andrey worked with a database as a data source for his analysis and soon realized that querying the database directly is much more efficient for ad hoc analyses than using any visual report-generating software. He joined the data warehouse team, and after a while, he led the team by taking up the position of a data warehouse architect. Andrey worked mainly with Oracle databases to develop logical and physical models of finance and accounting data, created them in a database, implemented procedures to load and process data, and performed analytical tasks. He was also responsible for teaching users how to use data warehouse and BI tools, and SQL training was a part of his job as well. After many years of being greatly interested in the aspects of his job that were related to IT rather than accounting or banking, Andrey changed fields. Currently, he works as a database developer in a telecommunication company. Here, Andrey works mainly with PostgreSQL databases and is responsible for data modeling, implementing data structures in databases, developing stored procedures, integrating databases with other software components, and developing a data warehouse. Having worked with both Oracle and PostgreSQL—the former is a leading commercial and the latter is one of the most advanced open source RDBMSes—he is able to compare them and recognize and evaluate the key advantages of both. Andrey's extensive experience, therefore, made him able and willing to work on this book. Table of Contents Chapter 1: Relational Databases Chapter 2: PostgreSQL in Action Chapter 3: PostgreSQL Basic Building Blocks Chapter 4: PostgreSQL Advanced Building Blocks Chapter 5: SQL Language Chapter 6: Advanced Query Writing Chapter 7: Server-Side Programming with PL/pgSQL Chapter 8: PostgreSQL Security Chapter 9: The PostgreSQL System Catalog and System Administration Functions Chapter 10: Optimizing Database Performance Chapter 11: Beyond Conventional Data types Chapter 12: Testing Chapter 13: PostgreSQL JDBC Chapter 14: PostgreSQL and Hibernate

Objedinjeni jezik za modelovanje – UML – je standardna grafička notacija za modelovanje koje zadovoljava poslovne i softverske potrebe. Postao je i efikasna modelarska alatka u dizajnu baza podataka. Kada se koristi kao zajednički jezik za modelovanje u razvoju sistema, oblasti koja ima toliko različitih aspekata, UML služi kao objedinjujući radni okvir koji olakšava integrisanje modela baza podataka sa ostatkom sistemskog dizajna. Ovaj pragmatični vodič vas uvodi u UML i usmerava vas kroz proces modelovanja i dizajna u čijoj osnovi leži UML. Knjiga predstavlja različite tipove UML-ovih dijagrama, objašnjava kako se oni koriste u svetu baza podataka i pokazuje kako modelovanje podataka pomoću UML-a može da se uklopi u Rational Unified Process. UML za projektovanje baza podataka je knjiga strukturirana oko procesa dizajna baze podataka: modelovanje poslovnih korisničkih funkcija, modelovanje poslovnih objekata, definicije zahteva baza podataka, analize i preliminarni dizajn, i konačno dizajn detalja i raspoređivanje. Za svaku fazu razvoja u knjizi su opisani opšti ciljevi i proces rada u toj fazi, status konkretnog slučaja, relevantni UML-ovi konstrukcioni elementi i sve pojedinosti modelovanja i dizajna baze podataka sa UML-om. Koristeći svoje bogato iskustvo, autori otkrivaju probleme kroz koje prolaze timovi za razvoj baza podataka, šta iz toga može da se nauči i koji su najbolji putevi ka uspehu.

What You Will Learn A rapid overview of data types, applications for each type, best practices and high-level variations between platforms Review the most common data structures and build working examples in the languages used for mobile platform software development Understand advanced data structure concepts such as generic collections, searching and sorting algorithms, and recursion Learn to use Stacks (LIFO) and queues (FIFO) in your daily application Add/remove objects and nest arrays and dictionaries within another dictionary and understand why such architecture is often preferred or necessary Get acquainted with the tree structures such as heap, binary, and graphs, apply them to work Unleash the power of different sorting techniques such as bubble sort, quick sort, merge sort, insertion sort, and radix sort Perform searching operations on arrays, heaps, graphs, and binary trees in different languages Book Description If you want to learn different data structures and their real-world applications quickly through practical examples, then Everyday Data Structures is for you. This book can introduce you to new data structures and their potential applications through examples in languages common to mobile software development on the most popular platforms. The examples are presented with real-world concepts using language that everyone will understand. This book is logically divided into two parts; the first one covers the basic data structures that are built into most languages such as Objective-C, C#, Java, and Swift. It will cover detailed analysis of the common data structures such as arrays, lists, stacks, Queues, and heaps, typical applications, and specific concerns for each language. Each chapter will provide in-depth examples in several popular languages based on real-world applications. The second part will cover more advanced data structures such as generic collections, sorting, searching, and recursion and ways to use those structures in everyday applications. Authors William Smith William Smith has been writing software since 1988, but he began his professional career in the environmental field with degrees in Environmental Science and Business Administration. While working in this field, William continued writing software as a hobbyist before returning to the University of Maryland for a degree in Computer Science. William currently works as an independent software engineer and author. He is the owner of Appsmiths LLC, a development and consulting firm concentrating on mobile application and game development using native tools as well as cross-platform tools, such as Xamarin and Monogame. William lives in rural West Virginia with his beautiful wife and four sons, where they enjoy hunting, fishing, and camping as a family. Table of Contents Chapter 1: Data Types: Foundational Structures Chapter 2: Arrays: Foundational Collections Chapter 3: Lists: Linear Collections Chapter 4: Stacks: LIFO Collections Chapter 5: Queues: FIFO Collections Chapter 6: Dictionaries: Keyed Collections Chapter 7: Sets: No Duplicates Chapter 8: Structs: Complex Types Chapter 9: Trees: Non-Linear Structures Chapter 10: Heaps: Ordered Trees Chapter 11: Graphs: Values with Relationships Chapter 12: Sorting: Bringing Order Out Of Chaos Chapter 13: Searching: Finding What You Need

Oracle® PL/SQL by Example, Fifth Edition, teaches all the PL/SQL skills you’ll need, through real-world labs, extensive examples, exercises, and projects. Now fully updated for the newest version of PL/SQL, it covers everything from basic syntax and program control through the latest optimization and security enhancements. Step by step, you’ll walk through every key task, mastering today’s most valuable Oracle 12cPL/SQL programming techniques on your own. Start by downloading projects and exercises from informit.com/title/0133796787. Once you’ve done an exercise, the authors don’t just present the answer: They offer an in-depth discussion introducing deeper insights and modern best practices. This book’s approach fully reflects the authors’ award-winning experience teaching PL/SQL to professionals at Columbia University. New database developers and DBAs can use it to get productive fast; experienced PL/SQL programmers will find it to be a superb Oracle Database 12csolutions reference. New in This Edition Updated code examples throughout Result-caching of invoker’s right functions for better performance Extended support for PL/SQL-only data types in dynamic SQL, OCI, and JDBC Security enhancements, including ACCESSIBLE BY whitelists, improved privilege control, and Invisible Columns Other topics covered Mastering basic PL/SQL concepts and language fundamentals, and understanding SQL’s role in PL/SQL Using conditional and iterative program control, including CONTINUE and CONTINUE WHEN Efficiently handling errors and exceptions Working with cursors and triggers, including compound triggers Using stored procedures, functions, and packages to write modular code that other programs can run Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced features Preface xvii Acknowledgments xxi About the Authors xxiii Introduction to PL/SQL New Features in Oracle 12c xxv Invoker’s Rights Functions Can Be Result-Cached xxvi More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause xxvii ACCESSIBLE BY Clause xxvii FETCH FIRST Clause xxviii Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms xxix More Data Types Have the Same Maximum Size in SQL and PL/SQL xxx Database Triggers on Pluggable Databases xxx LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause xxx Implicit Statement Results xxxi BEQUEATH CURRENT_USER Views xxxii INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges xxxii Invisible Columns xxxiii Objects, Not Types, Are Editioned or Noneditioned xxxiv PL/SQL Functions That Run Faster in SQL xxxiv Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE xxxvi Compilation Parameter PLSQL_DEBUG Is Deprecated xxxvii Chapter 1: PL/SQL Concepts 1 Lab 1.1: PL/SQL Architecture 2 Lab 1.2: PL/SQL Development Environment 9 Lab 1.3: PL/SQL: The Basics 18 Summary 25 Chapter 2: PL/SQL Language Fundamentals 27 Lab 2.1: PL/SQL Programming Fundamentals 28 Summary 41 Chapter 3: SQL in PL/SQL 43 Lab 3.1: DML Statements in PL/SQL 44 Lab 3.2: Transaction Control in PL/SQL 49 Summary 55 Chapter 4: Conditional Control: IF Statements 57 Lab 4.1: IF Statements 58 Lab 4.2: ELSIF Statements 63 Lab 4.3: Nested IF Statements 67 Summary 70 Chapter 5: Conditional Control: CASE Statements 71 Lab 5.1: CASE Statements 71 Lab 5.2: CASE Expressions 80 Lab 5.3: NULLIF and COALESCE Functions 84 Summary 89 Chapter 6: Iterative Control: Part I 91 Lab 6.1: Simple Loops 92 Lab 6.2: WHILE Loops 98 Lab 6.3: Numeric FOR Loops 104 Summary 109 Chapter 7: Iterative Control: Part II 111 Lab 7.1: CONTINUE Statement 111 Lab 7.2: Nested Loops 118 Summary 122 Chapter 8: Error Handling and Built-in Exceptions 123 Lab 8.1: Handling Errors 124 Lab 8.2: Built-in Exceptions 126 Summary 132 Chapter 9: Exceptions 133 Lab 9.1: Exception Scope 133 Lab 9.2: User-Defined Exceptions 137 Lab 9.3: Exception Propagation 141 Summary 147 Chapter 10: Exceptions: Advanced Concepts 149 Lab 10.1: RAISE_APPLICATION_ERROR 149 Lab 10.2: EXCEPTION_INIT Pragma 153 Lab 10.3: SQLCODE and SQLERRM 155 Summary 158 Chapter 11: Introduction to Cursors 159 Lab 11.1: Types of Cursors 159 Lab 11.2: Cursor Loop 165 Lab 11.3: Cursor FOR LOOPs 175 Lab 11.4: Nested Cursors 177 Summary 181 Chapter 12: Advanced Cursors 183 Lab 12.1: Parameterized Cursors 183 Lab 12.2: Complex Nested Cursors 185 Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors 187 Summary 190 Chapter 13: Triggers 191 Lab 13.1: What Triggers Are 191 Lab 13.2: Types of Triggers 205 Summary 211 Chapter 14: Mutating Tables and Compound Triggers 213 Lab 14.1: Mutating Tables 213 Lab 14.2: Compound Triggers 217 Summary 223 Chapter 15: Collections 225 Lab 15.1: PL/SQL Tables 226 Lab 15.2: Varrays 235 Lab 15.3: Multilevel Collections 240 Summary 242 Chapter 16: Records 243 Lab 16.1: Record Types 243 Lab 16.2: Nested Records 250 Lab 16.3: Collections of Records 253 Summary 257 Chapter 17: Native Dynamic SQL 259 Lab 17.1: EXECUTE IMMEDIATE Statements 260 Lab 17.2: OPEN-FOR, FETCH, and CLOSE Statements 271 Summary 280 Chapter 18: Bulk SQL 281 Lab 18.1: FORALL Statements 282 Lab 18.2: The BULK COLLECT Clause 291 Lab 18.3: Binding Collections in SQL Statements 299 Summary 309 Chapter 19: Procedures 311 Benefits of Modular Code 312 Lab 19.1: Creating Procedures 312 Lab 19.2: Passing Parameters IN and OUT of Procedures 315 Summary 319 Chapter 20: Functions 321 Lab 20.1: Creating Functions 321 Lab 20.2: Using Functions in SQL Statements 327 Lab 20.3: Optimizing Function Execution in SQL 329 Summary 331 Chapter 21: Packages 333 Lab 21.1: Creating Packages 334 Lab 21.2: Cursor Variables 344 Lab 21.3: Extending the Package 353 Lab 21.4: Package Instantiation and Initialization 366 Lab 21.5: SERIALLY_REUSABLE Packages 368 Summary 371 Chapter 22: Stored Code 373 Lab 22.1: Gathering Information about Stored Code 373 Summary 382 Chapter 23: Object Types in Oracle 385 Lab 23.1: Object Types 386 Lab 23.2: Object Type Methods 394 Summary 404 Chapter 24: Oracle-Supplied Packages 405 Lab 24.1: Extending Functionality with Oracle-Supplied Packages 406 Lab 24.2: Error Reporting with Oracle-Supplied Packages 419 Summary 429 Chapter 25: Optimizing PL/SQL 431 Lab 25.1: PL/SQL Tuning Tools 432 Lab 25.2: PL/SQL Optimization Levels 438 Lab 25.3: Subprogram Inlining 444 Summary 453 Appendix A: PL/SQL Formatting Guide 455 Case 455 White Space 455 Naming Conventions 456 Comments 457 Other Suggestions 457 Appendix B: Student Database Schema 461 Table and Column Descriptions 461 Index 469