1 / 22

The relational model

The relational model. Concepts and terminology Operations (relational algebra) Integrity constraints. Relational Databases. All data is organised in tables with atomic values Associations are represented by primary key/foreign key connections

cirila
Download Presentation

The relational model

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. The relational model Concepts and terminology Operations (relational algebra) Integrity constraints FEN 2014-02-06

  2. Relational Databases • All data is organised in tables with atomic values • Associations are represented by primary key/foreign key connections • Every operation operates on tables and returns tables Small exercise (5 min..): • Describe step by step how you will retrieve this information: • When did Mr Smith attend the “Intro to Computer Science” course, who was the instructor and what grade did he receive? FEN 2014-02-06

  3. 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 2014-02-06

  4. 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 2014-02-06

  5. 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 2014-02-06

  6. 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 CPR numbers, 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 2014-02-06

  7. 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 2014-02-06

  8. 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 2014-02-06

  9. Associations Between Relations • Associations are 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 (normallly the same domain). Any foreign keys here? FEN 2014-02-06

  10. 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 2014-02-06

  11. Example: MiniBank • Two tables: • Customers • Accounts • Associated: • An account belongs to one customer Association Any constraints here? (primarykeys– foreignkeys) FEN 2014-02-06

  12. 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 2014-02-06

  13. Example: MiniBank Table definitions (schemas): Constraint FEN 2014-02-06

  14. 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 2014-02-06

  15. 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 2014-02-06

  16. Relational Algebra - Overview FEN 2014-02-06

  17. 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 Whatifwe have more thanonecustomercalled ‘Tommy’? And whatifwe have no ‘Tommy’? FEN 2014-02-06

  18. Definitions - Summary FEN 2014-02-06

  19. Another example: Company: FEN 2014-02-06

  20. Company: Sample data FEN 2014-02-06

  21. Company: Sample data – cont… FEN 2014-02-06

  22. Company: Exercise • Do exercise 1 onCompanyExercise.pdf • (Exercise 2, phase 1 and 2) FEN 2014-02-06

More Related