DM534: Introduction to Relational Databases 2019 Slides by Christian Wiwie (edits by Rolf Fagerberg)
Some perspectjve ... What are Databases? ● Repository for large data amounts ● Describes a logical structure of contained data ● Guarantees data integrity by enforcing constraints ● Allows for effjcient access ● Consistent and safe storage DB “ SDU” 2 Nov 19, 2019
Database Management System (DBMS) ● A DBMS manages databases ● Access to database only via DBMS User App Administrator DBMS ... DB DB DB “ SDU” “DTU” “...” 3 Nov 19, 2019
Why learn about Databases? ● Used almost everywhere ● Crucial for safety & integrity of stored data ● Jobs exist dealing specifjcally with databases ● Increasingly relevant – Technical advances → More & larger data amounts 4 Nov 19, 2019
Where are Databases used? ● Wherever large amounts of data are managed ● Ofuen multjple DBMS in use that cater specifjc needs ● Google uses Bigtable for web indexing, Google Maps, ... ● Facebook uses MySQL; TAO for graph search, … ● Other applicatjons – Corporate data: payrolls, inventory, sales, customers, ... – Web search: Google, Live, Yahoo, ... – Social networks: Facebook, Twituer, ... – Scientjfjc and medical databases 5 Nov 19, 2019
Features of a modern DBMS ● Highly effjcient access to stored data using indexes ● Backup/log mechanisms ensure data safety ● Security policies to manage access permissions ● Data consistency : Can enforce complex data constraints, including dependencies ● Flexible searching, sortjng, fjltering ● Ensures all the above with simultaneous multj-user access 6 Nov 19, 2019
Databases vs. storage in fjles ● File storage does not provide most of these features → Structure and constraints need to be imposed manually ● Complex operatjons – not trivial to do right → Error prone – are slow, e.g. searching, sortjng 7 Nov 19, 2019
Types of DBMS / databases ● Data can be modeled and organized difgerently ● Optjmized for specifjc kinds of operatjons ● Relatjonal DBMS (RDBMS) / databases the most wide- spread – Based on mathematjcal relatjons – Basically, a database is a collectjon of relatjons – e.g. MySQL, PostgreSQL, ... ● Graph DBMS / databases – Data is a network, with entjtjes and connectjons between them – e.g. neo4j 8 Nov 19, 2019
DBMS type popularity 9 Nov 19, 2019
Most widely used DBMS ● Ranking of most widely used DBMS Source: htups://db-engines.com/en/ranking 10 Nov 19, 2019
Internal Structure of a Database App App ● Multjple levels of abstractjon ● Higher levels View 1 View 2 View 3 independent of lower Database levels Conceptual Data Model ● Sofuware independent of how data is logically Logical Data Model and physically structured and stored Physical Data Model 11 Nov 19, 2019
Internal Structure of a Database App App View 1 View 2 View 3 Database Conceptual Data Model We will be looking at this part Logical Data Model Physical Data Model 12 Nov 19, 2019
From here on we are exam relevant Conceptual Data Model ● Semantjcs of stored data ● Which entjtjes (concepts) are stored? ● Which relatjonships exist between entjtjes? ● Independent of DBMS type and specifjc DBMS used 13 Nov 19, 2019
Conceptual Data Model Most widely used conceptual model: Entjty-Relatjonship (ER) diagrams Cardinality Cardinality Entjty Type 1 Entjty Type 2 Relatjonship Type Aturibute 1 Aturibute 1 Aturibute 2 Aturibute 2 Cardinality: How many entjtjes are involved in a relatjonship? 14 Nov 19, 2019
Conceptual Data Model ● Example Cardinalitjes 1..n Person Owns Car Name Color Brand Age ● Read: – One person owns one or more cars 15 Nov 19, 2019
Conceptual Data Model ● Example Cardinalitjes 1 Person Owns Car Name Color Brand Age ● Read: – One car is owned by exactly one person → Constraints do not necessarily hold in reality (joint ownership) 16 Nov 19, 2019
Logical Data Model ● Usually derived from conceptual data model ● Expressed in terms of data structures specifjc to type of DBMS – Relatjonal DBMS: relatjonal (logical) data model – Graph DBMS: a graph structure ● But: Stjll independent of specifjc DBMS used 17 Nov 19, 2019
We focus on relatjonal DBMS Relatjonal (Logical) Data Model ● Main structural concept: relatjons – Basically a table with rows and columns ● A relatjon has a relatjon schema – Specifjes structure of data that can be stored in relatjon ● relatjons != relatjonship – Relatjonship is part of conceptual data model – A relatjon can hold data for entjtjes or relatjonships 18 Nov 19, 2019
Relatjonal (Logical) Data Model ● A relatjon schema consists of: – a name – a set of aturibute names – Optjonally: aturibute types relatjon_name(aturibute 1 , aturibute 2 , …) or relatjon_name(aturibute 1 : type 1 , aturibute 2 : type 2 , …) 19 Nov 19, 2019
Relatjon Schemas ● A relatjon usually corresponds to – Real world entjty types (e.g. car, person, …) – Real world relatjonship types (e.g. person owns car) ● Example relatjon schemas : – Car(color, brand) – Person(name: CHAR(20),age: INTEGER) – Owns(name, age, color, brand) 20 Nov 19, 2019
Relatjon Instances ● A relatjon or relatjon schema does not specify which data is stored ● A relatjon instance is a realizatjon of a relatjon with data – Data must conform to relatjon’s schema ● Many relatjon instances can exist for the same relatjon 21 Nov 19, 2019
Tuples ● A data entry in a relatjon instance is called tuple ● A tuple is a realizatjon of the relatjon’s schema – Assigns values to the atuributes of the relatjon – Must conform to relatjon schema 22 Nov 19, 2019
Tuples ● Example tuples of the relatjon Car(color, brand) : – (‘red’, ‘Ford’) – (‘blue’, ‘Mercedes’) ● Example tuples of the relatjon Person(name, age) : – (‘Henry’, 36) – (‘Thomas’, 22) 23 Nov 19, 2019
Relatjon Instances ● Can be visualized by a table: Aturibute / Column } Name aturibute 1 attribute 2 ... ... ... { Tuple / Row Relatjon Instance / Table 24 Nov 19, 2019
Relatjon Instance ● Example relatjon instance of the person relatjon Person name age ‘Henry’ 36 ‘Thomas’ 22 25 Nov 19, 2019
Database Instance ● A database instance is the collectjon of all its relatjon instances – i.e. all relatjon schemas and their corresponding tuples Person Car name age color brand ... ‘Henry’ 36 ‘red’ ‘Ford’ ‘Thomas’ 22 ‘blue’ ‘Mercedes’ 26 Nov 19, 2019
From ER Diagrams to Relatjons Standard translatjon: Person Car Owns pID cID Brand Name Age Age Age Age Date ● Each entjty is converted directly to a relatjon (same atuributes and keys). ● Each relatjonship is converted to a relatjon with atuributes consistjng of the keys of its related entjtjes plus its own atuributes (if any). More on keys later. 27 Nov 19, 2019
From ER Diagrams to Relatjons Example: Person Car Owns pID cID Brand Name Age Age Age Age Date Person(pID: INTEGER, Name: CHAR(20)) Car(cID: INTEGER, Brand: CHAR(20)) Owns(pID: INTEGER, cID: INTEGER, Date: CHAR(10)) 28 Nov 19, 2019
Integrity Constraints (ICs) 29 Nov 19, 2019
Integrity Constraints (ICs) ● Conditjon that must be true for any database instance ● Specifjed when relatjon schemas are defjned ● Checked whenever relatjon instances are modifjed – i.e., when tuple is added, deleted, or modifjed 30 Nov 19, 2019
Domain constraints ● Domain of valid values for an aturibute – e.g., INTEGER, FLOAT, CHAR(20), … – correspond to data types in programming languages ● Example relatjon schema: Person(name: CHAR(20),age: INTEGER) name age ‘Henry’ 36 Domain constraint violatjon ‘Mads’ ‘Doe’ → DBMS will not allow insertjon of this tuple 31 Nov 19, 2019
Semantjc integrity constraints ● Semantjc restrictjons on the data – e.g., age >= 18 ● Example relatjon schema: Person(name: CHAR(20),age: INTEGER) name age ‘Henry’ 36 Constraint violatjon ‘Mads’ 16 → DBMS will not allow insertjon of this tuple 32 Nov 19, 2019
Primary Keys ● Set of relatjon atuributes – that uniquely identjfjes tuples of relatjon – all tuples need to have unique values for these atuributes ● Example: CPR is primary key of relatjon Person → There cannot be two tuples with same CPR number CPR Name Birthday Address ... ... ... ... Not allowed 1904651243 Svensson 19.04.1965 ... ... ... ... ... 1904651243 ... ... ... ... ... ... ... 33 Nov 19, 2019
Primary Keys ● Primary key “points” to exactly one tuple → can be used to lookup corresponding tuple → e.g., person can be looked up using CPR What is the name of the person with CPR Name Birthday Address CPR=1904651243 ? ... ... ... ... 1904651243 Svensson 19.04.1965 ... ... ... ... ... 34 Nov 19, 2019
Foreign Keys ● Allow to associate tuples in difgerent relatjons ● Tuple of source relatjon → tuple of target relatjon – Source and target relatjon can be the same – Can only point to a primary key in the target relatjon 35 Nov 19, 2019
Recommend
More recommend
