1 / 16

Seminar: Introduction to relational databases

Seminar: Introduction to relational databases. Introduction to the database field: The Relational Model. The Relational Model. A sound theoretical data model ( Codd , 1970). Based on the mathematical theory of relations, sets and first order predicate logic.

melyssa-mosley
Download Presentation

Seminar: Introduction to relational 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. Seminar:Introduction to relational databases Introduction to the database field: The Relational Model FEN 2012-08-14

  2. The Relational Model • A sound theoretical data model (Codd, 1970). • Based on the mathematical theory of relations, sets and first order predicate logic. • De facto standard since the late eighties. • Many-many implementations – most SQL-based. The Notorious Supplier-Part Database (Date) • For instance: • Oracle • MySQL • MS SQL Server • PostgreSQL FEN 2012-08-14

  3. The Relational Model: Concepts The Notorious Supplier-Part Database (Date) Central concepts: • Tables (relations). • Columns (attributes). • Type (domain). • Rows (tuples). • Tuples are unordered. • Tuples are unique. • A relation is a set (mathematical) of tuples. • Primary and foreign keys FEN 2012-08-14

  4. The Relational Model • Data is organised in a number of tables (relations). • Each table has a number (>=1) columns (attributes). • Attributes are atomic and defined over some domain. • A table holds a number (maybe none) rows (tuples). • Tuples are unordered. • Tuples are unique (existence of a key is guaranteed). • A relation is a set (mathematical) of tuples. FEN 2012-08-14

  5. Attributes and Domains • A domain defines the valid value of an attribute. • Domains are based on the built-in standard data types (int, charetc.) offered by the DBMS. • Theoretically it should be possible to define problem specific domains as account numbers, IP addresses etc. and complex aggregate (structured) domain as maps, diagrams, pictures, sound bites, video clips etc. • More attributes may be defined over the same domain. • An attribute may have the value “empty” (not known /not defined for this instance). Empty is notated NULL. FEN 2012-08-14

  6. Properties of a Relation Follows from the fact that relations are (mathematically) sets: • Tuples must be unique within a relation (hence a primary key always exists) • Tuples are unordered (vertically) • Attributes are unordered (horizontally) • Attribute values are atomic Note the difference to the usual notion of a table FEN 2012-08-14

  7. Keys • A key is a combination of attributes that is: • Unique and • Minimal • An attribute combination that is unique, but not minimal is called a superkey • The set of all attributes will always be a superkey, hence a superkey (and a key) always exists. • A relation (table) may have several candidate keys. • One these is appointed primary key. Any primary keys here? FEN 2012-08-14

  8. Associations Between Relations • Is represented by foreign keys. • A foreign key is an attribute (combination) that corresponds to an attribute (combination) of the primary key of some other relation. • A foreign key references a tuple in another relation and indicates that here is more information about the entity. • Foreign key attributes and corresponding primary key attributes must be defined over compatible domains (or even the same domain). Any foreign keys here? FEN 2012-08-14

  9. Integrity Constraints • Domain constraints • Attributes may only hold valid values • Entity Integrity • Primary key attributes may not hold NULL-values • Referential Integrity (foreign key constraint) • A foreign key must either be NULL or reference an existing primary key in the other relation • Semantic Integrity • Constraints depending on the problem domain Any constraints here? FEN 2012-08-14

  10. Example: MiniBank • Two tables: • Customers • Accounts • Associated: • An account belongs to one customer Association Any constraints here? (primarykeys– foreignkeys) FEN 2012-08-14

  11. Example: MiniBank • What happens if: • We try to insert a customer with an existing custNo? • We try to insert an account with a not existing custNo? • Let’s try in MS SQL Server FEN 2012-08-14

  12. Example: MiniBank Table definitions (schemas): Constraint FEN 2012-08-14

  13. Quering a relational database • Database Languages: • Data Definition DDL • Should provide constructs for defining all the previous (as “create table) • Data Manipulation DML (queries, insert, delete, update) • procedural (How?) • nonprocedural (What?) • The Relational Algebra is a procedural DML • SQL includes a (sort of) nonprocedural DML FEN 2012-08-14

  14. The Relational Algebra Data Manipulation in the Relational Model • Operates on relations, which are input to the operations is tables and the result is a table • Operations • Row selection (RESTRICT/SELECT) • Column selection (PROJECT) • Combining tables (JOIN) • Set operations (UNION, INTERSECTION, DIFFERENCE, PRODUCT) • More advanced operations (OUTER (LEFT/RIGTH) JOIN) FEN 2012-08-14

  15. Relational Algebra - Overview FEN 2012-08-14

  16. Example: MiniBank • Retrieve information about customer number 3: • Row selection on custNo = 3 from Customer • Retrieve account number, balance and customer number for accounts with a balance between 1000 and 2000: • Row selection on 1000 <= balance and balance <= 2000 from Account • Column selection on accNo, balance, custNo • Retrieve information about customer Tommy and his accounts: • Row selection on name= ‘Tommy’ from Customer • Join with Account on custNo FEN 2012-08-14

More Related