Transcript Databases & SQL

Databases & SQL
Teacher: Henny Klein
email
[email protected]
contact:
wednesday 14-16
room 11 - 426
All material and assignments on Nestor
DB&SQL 1-
1
Content of the course
• 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
DB&SQL 1-
2
Content of practical sessions
• 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
DB&SQL 1-
3
Your own design!
• 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!
DB&SQL 1-
4
The book
• 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
DB&SQL 1-
5
Grading rules
Design+Project
30%
SQL/VBA
30%
Exam
40%
Design exercises as homework
Individual project
SQL and VBA: practical exercises
Tentamination: mostly theory, some SQL, no VBA
DB&SQL 1-
6
Card catalogue, textfile, DBMS
• 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?
DB&SQL 1-
7
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
DB&SQL 1-
8
Information Science and databases
• 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
DB&SQL 1-
9
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
DB&SQL 1-10
Types of database systems
• early types:
– Hierarchical database
– Netwerk database
• currently most common:
– Relational database
– Integration of XML
• developing:
– Object oriented database
DB&SQL 1-11
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
DB&SQL 1-12
Relational db: example of a table
Scheme: Leveranciers (code, lev_naam, adres, woonplaats)
Tabel Leveranciers (=suppliers)
CODE
004
009
011
013
014
019
LEV_NAAM
Hovenier G.H.
Baumgarten R.
Struik BV
Spitman en Zn.
Dezaaier L.J.A.
Mooiweer FA.
ADRES
Zandweg 50
Taksstraat 13
Bessenlaan 1
Achtertuin 9
De Gronden 101
Verlengde Zomerstr. 24
WOONPLAATS
Lisse
Hillegom
Lisse
Aalsmeer
Lisse
Aalsmeer
Each row represents the data of 1 supplier
Each row is unique
Data are split up in simple items (comments??)
DB&SQL 1-13
The anatomy of a table / relation
attribuut, veldnaam
attribute, field name
kolomkop
column heading
record, entiteit
record, entity
rij, tupel
row, tuple
tabelkop, schema
table heading, scheme
Naam
Adres
J.Jansen
Poolweg 13 Gorkum
P.Putten
Kaaplaan 6
Breda
J.Jansen
Weerd 18
Breda
attribuutwaarde
attribute value
Woonplaats
kolom
column
gegevens
data,
record
set,
body
NB An attribute has a data type and a domain
DB&SQL 1-14
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?
DB&SQL 1-15
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?
DB&SQL 1-16
Introduction of database design
• 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
DB&SQL 1-17
Library as a flat table: redundant data
ISBN
1-1111-1111-1
0-99-999999-9
0-91-335678-7
0-91-045678-5
0-103-45678-9
0-12-345678-6
0-99-777777-7
0-555-55555-9
0-11-345678-9
0-12-333433-3
0-321-32132-1
0-55-123456-9
0-123-45678-0
1-22-233700-0
Title
C++
Emma
Fairie Queene
Hamlet
Iliad
Jane Eyre
King Lear
Macbeth
Moby Dick
On Liberty
Balloon
Main Street
Ulysses
Visual Basic
PubName
Big House
Big House
Big House
Alpha Press
Big House
Small House
Alpha Press
Alpha Press
Small House
Big House
Small House
Small House
Alpha Press
Big House
PubPhone
123-456-7890
123-456-7890
123-456-7890
999-999-9999
123-456-7890
714-000-0000
999-999-9999
999-999-9999
714-000-0000
123-456-7890
714-000-0000
714-000-0000
999-999-9999
123-456-7890
Price
$29.95
$20.00
$15.00
$20.00
$25.00
$49.00
$49.00
$12.00
$49.00
$25.00
$34.00
$22.95
$34.00
$25.00
Author
data
DB&SQL 1-18
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??
DB&SQL 1-19
Relationship Publisher-Book: 1 to many
DB&SQL 1-20
Establishing a relationship
BOOKS
ISBN
0-321-32132-1
1-1111-1111-1
0-99-999999-9
0-91-335678-7
0-91-045678-5
0-103-45678-9
0-12-345678-6
0-99-777777-7
Title
Balloon
C++
Emma
Fairie Queene
Hamlet
Iliad
Jane Eyre
King Lear
PUBLISHERS
PubID PubName
1
Big House
2
Alpha Press
3
Small House
Price
$34.00
$29.95
$20.00
$15.00
$20.00
$25.00
$49.00
$49.00
PubPhone
123-456-7890
999-999-9999
714-000-0000
Primary key / primaire sleutel
PubID
3
1
1
1
2
1
3
2
referencing key /
verwijzende sleutel
Publisher and book
are distinct entity
classes, they need
distinct tables
DB&SQL 1-21
Author: a multivalued attribute
provisional solutions
a multivalued field? a repeated field? repeating the bookrecord?
ISBN
0-321-32132-1
Title
Balloon
Author(s)
Sleepy, Snoopy, Grumpy
ISBN
0-321-32132-1
Title
Balloon
Author1
Sleepy
ISBN
0-321-32132-1
0-321-32132-1
0-321-32132-1
Title
Balloon
Balloon
Balloon
Author
Sleepy
Snoopy
Grumpy
Author2
Snoopy
Field1
A
Author3
Grumpy
Field1
A
A
A
Field2
B
Field1
A
Field2
B
B
B
DB&SQL 1-22
... and the problems
ISBN
0-321-32132-1
Title
Balloon
Author(s)
Sleepy, Snoopy, Grumpy
Field1
A
Problems:
find an author, sort on an author
ISBN
0-321-32132-1
Title
Balloon
Problems:
find an author, sort on an author,
number of author fields? empty fields
ISBN
0-321-32132-1
0-321-32132-1
0-321-32132-1
Title
Balloon
Balloon
Balloon
Problems:
redundancy: integrity problems, filesize
Author1
Sleepy
Author
Sleepy
Snoopy
Grumpy
Author2
Snoopy
Author3
Grumpy
Field1
A
A
A
Field2
B
Field1
A
Field2
B
B
B
DB&SQL 1-23
The relational solution for multiple values
Authors are split up in an additional table
Each record connects one AUTHOR to a BOOK
Table BOOKS
ISBN
0-321-32132-1
..
Title
Balloon
..
Field1
A
..
Field2
B
..
Table BOOK-AUTHOR
ISBN
0-321-32132-1
0-321-32132-1
0-321-32132-1
0-91-045678-5
0-555-55555-9
..
Author
Sleepy
Snoopy
Grumpy
Shakespeare
Shakespeare
..
Rule: attributes
contain only one
simple value
DB&SQL 1-24
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?
DB&SQL 1-25
Relationship BOOK – AUTHOR:
many to many
B1
B2
B3
A1
A2
B4
A3
B5
A4
DB&SQL 1-26
Library: Books and authors
ISBN
0-99-999999-9
0-99-777777-7
0-11-345678-9
0-12-345678-6
0-91-335678-7
0-12-333433-3
1-22-233700-0
0-55-123456-9
0-55-123456-9
0-555-55555-9
0-123-45678-0
0-103-45678-9
0-91-045678-5
0-321-32132-1
0-321-32132-1
0-321-32132-1
1-1111-1111-1
AuID
1
5
2
1
7
8
4
9
10
5
6
3
5
11
12
13
4
AuID
1
2
3
4
5
6
7
8
9
10
11
12
13
AuName
Austen
Melville
Homer
Roman
Shakespeare
Joyce
Spencer
Mill
Smith
Jones
Snoopy
Grumpy
Sleepy
ISBN
0-321-32132-1
0-91-045678-5
0-555-55555-9
..
AuPhone
111-111-1111
222-222-2222
333-333-3333
444-444-4444
555-555-5555
666-666-6666
777-777-7777
888-888-8888
123-222-2222
123-333-3333
321-321-2222
321-321-0000
321-321-1111
Title
Balloon
Hamlet
Iliad
..
…
2 entities
and a link table
(tussentabel)
DB&SQL 1-27
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
-
DB&SQL 1-28
Entity-Relation Diagram (Ch 2)

Book
published by/
publishes
1
Publisher

written by/
writes

Author
Method
Start from one record in a table and write the
relationship type at the other table (1 or  )
DB&SQL 1-29
Homework
1. 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
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
DB&SQL 1-30