Databases sql
1 / 30

Databases & SQL - PowerPoint PPT Presentation

  • Uploaded on

Databases & SQL. Teacher: Henny Klein email 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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



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


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 time is spent on explanation and examples.

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

  • 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: era?

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

Presentation of the data


Access: Forms, reports, VBA, SQL

Outside Access: (web)applicaties

Logical description of the data

(conceptual level)


Operating system (host)

Physical storage of data

Types of database systems

early types: era?

Hierarchical database

Netwerk database

currently most common:

Relational database

Integration of XML


Object oriented database

Types of database systems

Database conceptual model of reality
Database: conceptual model of reality era?

  • ???

  • 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


    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 / era? relation

    attribuut, veldnaam

    attribute, field name


    column heading

    tabelkop, schema

    table heading, scheme

    record, entiteit

    record, entity

    rij, tupel

    row, tuple







    attribute value



    NB An attribute has a data type and a domain

    Identification required
    Identification required era?

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

    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?

    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 storage

    The table asserts several times that Big House has Phone xxx

    Problems with data redundancy:

    file size

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

    Establishing a relationship
    Establishing a relationship storage


    referencing key /

    verwijzende sleutel

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


    Primary key / primaire sleutel

    Author a multivalued attribute provisional solutions
    Author: a multivalued attribute storageprovisional solutions

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

    And the problems
    ... and the problems storage

    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 storage

    Authors are split up in an additional table

    Each record connects one AUTHOR to a BOOK

    Table BOOKS


    Rule: attributes contain only one simple value

    Complex data
    complex data storage

    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?

    Relationship book author many to many
    Relationship BOOK – AUTHOR: storagemany to many










    Library books and authors
    Library: Books and authors storage

    2 entities

    and a link table


    Database design
    Database design storage

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


    published by/





    written by/



    Start from one record in a table and write the

    relationship type at the other table (1 or  )


    Read and note your questions about storage

    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