Databases & SQL. Teacher: Henny Klein email [email protected] contact: wednesday 14-16 room 11 - 426. All material and assignments on Nestor. First block C H 1 t.m. 5 Basic knowledge for the design and manipulation of relational databases Second block C H 6 t.m. 10
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Programming in Access
functions, event handling (VBA, DAO)
Building an applicationContent of practical sessions
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!
Gives exercises, use them as a method of self assessmentThe book
Design+Project SQL/VBA Exam
30% 30% 40%
Design exercises as homework
SQL and VBA: practical exercises
Tentamination: mostly theory, some SQL, no VBA
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
Presentation of the data
Access: Forms, reports, VBA, SQL
Outside Access: (web)applicaties
Logical description of the data
Operating system (host)
Physical storage of data
The choice of attributes depends
on the properties of the entities
on the context
A database is a model of reality
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 AalsmeerRelational 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??)
attribute, field name
table heading, scheme
NB An attribute has a data type and a domain
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?
Which problems may occur by using
NAW-data (Naam Adres Woonplaats)
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?
The next slides show basic principles of database design
Later on, in Chapter 4, relational database design is discussed at lengthIntroduction of database design
The table asserts several times that Big House has Phone xxx
Problems with data redundancy:
data integrity (update/insert anomalies)
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??
referencing key /
Publisher and book are distinct entity classes, they need distinct tables
Primary key / primaire sleutel
a multivalued field? a repeated field? repeating the bookrecord?
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
Authors are split up in an additional table
Each record connects one AUTHOR to a BOOK
Rule: attributes contain only one simple value
An author may have more attributes:
how can we design a database for books and authors as separate entities?
what about the relationship?
and a link table
Which entities, which attributes? Which primary key?
Book: ISBN, Title, Price
Publisher: PubID, PubName, PubPhone
Author: AuID, AuName, AuPhone
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
Start from one record in a table and write the
relationship type at the other table (1 or )
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 diagramsHomework