Databases sql
Download
1 / 30

Databases SQL - PowerPoint PPT Presentation


  • 331 Views
  • Updated 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' - Melvin


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 l.jpg
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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg
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 l.jpg

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 l.jpg
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 l.jpg

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 l.jpg
The multi-layer system era?

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 l.jpg

early types: era?

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 l.jpg
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 l.jpg

    CODE LEV_NAAM ADRES WOONPLAATS era?

    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 l.jpg
    The anatomy of a table / era? 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 l.jpg
    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 l.jpg
    Identification problems era?

    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 l.jpg

    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 l.jpg
    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)

    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 l.jpg
    Establishing a relationship storage

    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 l.jpg
    Author: a multivalued attribute storageprovisional solutions

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


    And the problems l.jpg
    ... 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 l.jpg
    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

    Table BOOK-AUTHOR

    Rule: attributes contain only one simple value


    Complex data l.jpg
    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 l.jpg
    Relationship BOOK – AUTHOR: storagemany to many

    B1

    A1

    B2

    A2

    B3

    A3

    B4

    A4

    B5


    Library books and authors l.jpg
    Library: Books and authors storage

    2 entities

    and a link table

    (tussentabel)


    Database design l.jpg
    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 l.jpg
    Entity-Relation Diagram (Ch 2) storage

    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 l.jpg

    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

    Homework


    ad