1 / 21

Introduction to databases

Introduction to databases. What is a database? Logiskt sammanhängande mängd av data, med en därtill hörande betydelse, strukturerad och försedd med data avsedda för ett visst ändamål, med en viss användargrupp i åtanke och återspeglande någon aspekt av världen.

tejano
Download Presentation

Introduction to databases

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to databases • What is a database? • Logiskt sammanhängande mängd av data, med en därtill hörande betydelse, strukturerad och försedd med data avsedda för ett visst ändamål, med en viss användargrupp i åtanke och återspeglande någon aspekt av världen. • What is a database management system? • A set of programs allowing a user to create and maintain databases.

  2. DBMS User / Programmer Database system Application programs / Queries DBMS Programs for query management Programs for data management Metadata Database

  3. Why databases? Persistence Sharing Data independence

  4. Types of databases Relational databases Hierarchical databases Network databases

  5. Relational databases A relation scheme is a set of attributes Example: PERSON(SS#, Name, Age, Salary) Every attribute has a domain Example: Name has the domain String, Age has the domain Integer A tuple for a relation scheme gives a value to each attribute in the scheme Example: (778899, John Smith, 26, 13000) The value for each attribute must be in the domain A relation is a set of tuples

  6. Relational databases PERSON Relation scheme SS# Name Age Salary 650101-2288 Eva Svensson 33 25000 750203-3133 Per Jonsson 23 20000 500107-5532 Sven Olsson 47 25000 800515-0044 Pia Eriksson 17 18000 Relation A relation can be viewed as a table without duplicates

  7. Reality Subset library borrower school pupil Model loan course book teacher subject library ((bName), address) school ((sNr), address) borrower ((lNr), firstName, surName) pupil ((eNr), firstName, surName) Database structure book ((ISBN), title) teacher ((lNr), firstName, surName) loan ((bName, lNr, ISBN, date) subject ((äNamn), courseBook) course ((sNr, eNr, lNr, cName) From reality to database

  8. Database design Library borrower library ((bNamn), address) borrower ((lNr), firstName, surName) loan book ((ISBN), title) loan ((bNamn, lNr, ISBN, date) book Why design? Why not a simple table? ISBN Title Author Library Address Borrower BorrowerAdd Date 12345 Mitt liv Pelle Stora Storgatan 19 Kalle Karlavägen 12 891102 23456 Ditt liv Lisa Stora Storgatan 19 Kalle Karlavägen 12 890723 12346 Vårt liv Pelle Stora Storgatan 19 Vera Verdandig 3 890809 12347 Vilket liv! Lisa Stora Storgatan 19 Mona Månvägen 7 891011 23412 Mitt liv Lisa Stora Storgatan 19 Kalle Karlavägen 12 891112 121212 Stickning Johan Stora Storgatan 19 Kalle Karlavägen 12 890909 1212 Matlagning Eva Stora Storgatan 19 Vera Verdandig 3 891010 100 Matematik Vera Stora Storgatan 19 Mona Månvägen 7 891102

  9. An unnormalised relation scheme Name Regno Share Salary Model Per Eriksson ABC123 50 25000 Volvo Eva Olsson ABC123 50 18000 Volvo Per Eriksson DEF456 100 25000 Mercedes Per Eriksson GHI789 50 25000 Toyota Pia Johnsson GHI789 50 30000 Toyota Pia Johnsson BCD321 100 30000 Ford Bo Persson CDE654 100 18000 Volvo

  10. Problems with unnormalised schemes Redundancy Update anomalies

  11. Functional dependencies A functional dependency means that one attribute uniquely determines another attribute. Example: Name --> Salary This functional dependency means that if two tuples have the same value on Name, then they must have the same value on Salary.

  12. Functional dependencies Which ones of the functional dependencies are satisfied by the relation above? A --> B AB --> C CD --> B CD --> E

  13. Keys A key in a relation scheme is an attribute (or a minimal set of attributes) that functionally determines all the other attributes in the scheme. Thus, a key uniquely identifies a tuple in a relation. What is the key in this relation scheme? MOVIE(Film, Theatre, Time, Price)

  14. A relation scheme is in first normal form if all attribute values are atomic. First normal form SS# Surname First name 750101-0032 Svensson Gunnar, Sven 550401-0044 Olsson Karin, Eva Not 1NF What are the keys? SS# Surname First name 750101-0032 Svensson Gunnar 750101-0032 Svensson Sven 550401-0044 Olsson Karin 550401-0044 Olsson Eva 1NF

  15. A relation scheme is in second normal form if every attribute is functionally dependent on the whloe key. Second normal form Name Regno Salary Per Eriksson ABC123 25000 Per Eriksson DEF456 25000 Pia Johnsson GHI789 30000 Pia Johnsson BCD321 30000 Name Regno Per Eriksson ABC123 Per Eriksson DEF456 Pia Johnsson GHI789 Pia Johnsson BCD321 Name Salary Per Eriksson 25000 Pia Johnsson 30000

  16. Third normal form A relation scheme is in third normal form if each attribute is functionally dependent on the key, the whole key, and nothing else than the key.

  17. Third normal form If an attribute does not satisfy the condition for 3NF, it is removed from the relation scheme. It will form a new relation scheme together with the attributes it is functionally dependent on. Example: PERSON(SS#, Name, Country, Number_of_inhabitants) SS# --> Name, Country Country --> Number_of_inhabitants Number_of_inhabitants does not satisfy the condition for 3NF. It is removed and will form a new relation schema together with Country: PERSON(SS#, Name, Country) COUNTRY(Country, Number_of_inhabitants)

  18. Decompose the following relation scheme to relation schemes in 3NF. BOOK(Copy#, SS#, Date-of-loan, Return-date, Library, Library_address, Person_address, Title) A copy of a book with a title is borrowed by a person, who has an address, at a date and is returned at another date. The copy of the book resides at a library with an address. Third normal form

  19. Reality Subset library borrower school pupil Model loan course book teacher subject library ((bName), address) school ((sNr), address) borrower ((lNr), firstName, surName) pupil ((eNr), firstName, surName) Database structure book ((ISBN), title) teacher ((lNr), firstName, surName) loan ((bName, lNr, ISBN, date) subject ((äNamn), courseBook) course ((sNr, eNr, lNr, cName) From reality to database

  20. Main steps 1. Every object type becomes a relation scheme 2. Every 1-1 and 1-m attribute of an object type becomes an attribute in the corresponding relation scheme 3. Every m-m attribute becomes a relation scheme - the attributes in this scheme are the key attributes in the associated relation schemes From conceptual schema to database

  21. From conceptual schema to database String String String Integer model name age regno owns (m,m,p,p) PERSON CAR

More Related