databases sql
Download
Skip this Video
Download Presentation
Databases & SQL

Loading in 2 Seconds...

play fullscreen
1 / 30

Databases & SQL - PowerPoint PPT Presentation


  • 51 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Databases & SQL' - ena


An Image/Link below is provided (as is) to download presentation

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.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
databases sql
Databases & SQL

Teacher: Henny Klein

email

[email protected]

contact:

wednesday 14-16

room 11 - 426

All material and assignments on Nestor

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

Content of the course
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

Content of practical sessions
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!

Your own design!
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

The book
grading rules
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

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?

Card catalogue, textfile, DBMS
database management systems
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
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

Information Science and databases
the multi layer system
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

types of database systems
early types:

Hierarchical database

Netwerk database

currently most common:

Relational database

Integration of XML

developing:

Object oriented database

Types of database systems
database conceptual model of reality
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

relational db example of a table

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??)

the anatomy of a table relation
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

identification required
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?

identification problems
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?

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

Introduction of database design
preventing redundancy
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??

establishing a relationship
Establishing a relationship

BOOKS

referencing key /

verwijzende sleutel

Publisher and book are distinct entity classes, they need distinct tables

PUBLISHERS

Primary key / primaire sleutel

author a multivalued attribute provisional solutions
Author: a multivalued attributeprovisional solutions

a multivalued field? a repeated field? repeating the bookrecord?

and the problems
... 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

the relational solution for multiple values
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

complex data
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?

library books and authors
Library: Books and authors

2 entities

and a link table

(tussentabel)

database design
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

 - 

entity relation diagram ch 2
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  )

homework
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
ad