Databases & SQL

1. Databases & SQL Teacher: Henny Klein email E.H.Klein@rug.nl contact: wednesday 14-16 room 11 - 426 All material and assignments on Nestor

2. First block CH 1 t.m. 5 Basic knowledge for the design and manipulation of relational databases Second block CH 6 t.m. 10 other types of databases processes in a DBMS distributed systems Content of the course

3. Access: basic manipulations: tables, relations, QBE, forms, reports Access- SQL data definition data manipulation queries Programming in Access functions, event handling (VBA, DAO) Building an application Content of practical sessions

4. You will train with example databases but every student has also to build his/her own database during lectures we will discuss your design in practical exercises you can build your database, formulate queries, etc. Assignment for this week (details later): Think of a theme for your database! Your own design!

5. Gives the essential information, but concise, so in lectures time is spent on explanation and examples. Gives exercises, use them as a method of self assessment The book

6. Grading rules Design+Project SQL/VBA Exam 30% 30% 40% Design exercises as homework Individual project SQL and VBA: practical exercises Tentamination: mostly theory, some SQL, no VBA

7. How did people search books in a library before the computer era? What are advantages of an electronic system? What are advantages of a database system compared to a textfile? How did departments of organizations communicate before the computer era? What are advantages of a DBMS here? Card catalogue, textfile, DBMS

8. Database management systems • Systematic, structured data storage • each data item is stored once, no redundancy • data integrity and security ensured • data available for distinct applications • concurrent use of data • differents views on data • DBMS used in many environments: • administration (products, clients, employees) • information (catalogues) • research (data storage, data mining) • online applications

9. DBMS: the standard system to store information, so often important in your work, and even beforehand: many students have to handle databases in their ….. in public or private organizations in a research …….. or in in your own research project Information Science and databases

10. The multi-layer system Presentation of the data (views) Access: Forms, reports, VBA, SQL Outside Access: (web)applicaties Logical description of the data (conceptual level) DBMS Operating system (host) Physical storage of data

11. early types: Hierarchical database Netwerk database currently most common: Relational database Integration of XML developing: Object oriented database Types of database systems

12. Database: conceptual model of reality • ??? • Which information items (attributes) may be useful for the entity • BOOK • TREE • ??? The choice of attributes depends on the properties of the entities but also on the context A database is a model of reality

13. CODE LEV_NAAM ADRES WOONPLAATS 004 Hovenier G.H. Zandweg 50 Lisse 009 Baumgarten R. Taksstraat 13 Hillegom 011 Struik BV Bessenlaan 1 Lisse 013 Spitman en Zn. Achtertuin 9 Aalsmeer 014 Dezaaier L.J.A. De Gronden 101 Lisse 019 Mooiweer FA. Verlengde Zomerstr. 24 Aalsmeer Relational db: example of a table Scheme: Leveranciers (code, lev_naam, adres, woonplaats) Tabel Leveranciers (=suppliers) Each row represents the data of 1 supplier Each row is unique Data are split up in simple items (comments??)

14. The anatomy of a table / relation attribuut, veldnaam attribute, field name kolomkop column heading tabelkop, schema table heading, scheme record, entiteit record, entity rij, tupel row, tuple gegevens data, record set, body attribuutwaarde attribute value kolom column NB An attribute has a data type and a domain

15. Identification required In a relational database, duplication of data must be prevented. Why?? What are the problems?? It is important to choose a sound identification, for current but also for possible future Think of a appropriate identification for students (in Progress) books (in the library) members of a hockey club?

16. Identification problems Which problems may occur by using NAW-data (Naam Adres Woonplaats) ISBN day of birth? Often, a system-created unique number is used as the primary key (primaire sleutel). It is easy and makes searching faster. But does it really discern your entities?

17. In most cases, one table is not enough for structured data storage The next slides show basic principles of database design Later on, in Chapter 4, relational database design is discussed at length Introduction of database design

18. Library as a flat table: redundant data Author data

19. Preventing redundancy The table asserts several times that Big House has Phone xxx Problems with data redundancy: file size data integrity (update/insert anomalies) Solution: The phone number is an attribute of the publisher, not of the book So publisher is an entity on its own But how are book and publisher related??

20. Relationship Publisher-Book: 1 to many

21. Establishing a relationship BOOKS referencing key / verwijzende sleutel Publisher and book are distinct entity classes, they need distinct tables PUBLISHERS Primary key / primaire sleutel

22. Author: a multivalued attributeprovisional solutions a multivalued field? a repeated field? repeating the bookrecord?

23. ... and the problems Problems: find an author, sort on an author Problems: find an author, sort on an author, number of author fields? empty fields Problems: redundancy: integrity problems, filesize

24. The relational solution for multiple values Authors are split up in an additional table Each record connects one AUTHOR to a BOOK Table BOOKS Table BOOK-AUTHOR Rule: attributes contain only one simple value

25. complex data An author may have more attributes: first name family name birthday …. how can we design a database for books and authors as separate entities? what about the relationship?

26. Relationship BOOK – AUTHOR: many to many B1 A1 B2 A2 B3 A3 B4 A4 B5

27. Library: Books and authors 2 entities and a link table (tussentabel)

28. Database design Which entities, which attributes? Which primary key? Book: ISBN, Title, Price Publisher: PubID, PubName, PubPhone Author: AuID, AuName, AuPhone Which relations? A book has 1publisher A publisher publishes 1 or more books  : infinite many A book has 1 or more authors An author writes 1 of more books U B 1 -  S B  - 

29. Entity-Relation Diagram (Ch 2) 1  published by/ publishes Publisher Book  Author written by/ writes  Method Start from one record in a table and write the relationship type at the other table (1 or  )

30. Read and note your questions about Rolland: Ch 1, Ch 3.1 Challenge: table 1.1 is not the right representation of the data in fig 1.5 and 1.6. Try to correct it! Additional info: Brookshear 9.1 en 9.2 Think of a theme for your own database project (hobby, useful data, ..) and write down which entities you discern (at least 3) which attributes they have what relationships exist among them Copy your notes and bring them next lecture to discuss and to hand in! Next week: chapter 2: ER diagrams Homework