advertisement

Relational model for Databases

50 %
50 %
advertisement
Information about Relational model for Databases
Education

Published on March 8, 2014

Author: ShriramDesai

Source: slideshare.net

Description

Relational model for Databases of different companies
advertisement

The Relational Model Shriram Desai, ramraodesai1991@gmail.com 7411001044

Why Relational Model?  Currently  Vendors: Oracle, Microsoft, IBM  Older  models still used IBM’s IMS (hierarchical model)  Recent  the most widely used competitions Object Oriented Model: ObjectStore  Implementation   standard for relational Model SQL (Structured Query Language) SQL 3: includes object-relational extensions

Relational Model  Structures   Relations (also called Tables) Attributes (also called Columns or Fields)  Note:  Every attribute is simple (not composite or multi-valued) Constraints   Key and Foreign Key constraints (More constraints later) Eg: Student Relation (The following 2 relations are equivalent) Student Student sNumber sName sNumber sName 1 Dave 2 Greg 2 Greg 1 Dave Murali Mani Cardinality = 2 Arity/Degree = 2

Relational Model  Schema   Eg: Student (sNumber, sName) PRIMARY KEY (Student) = <sNumber>  Schema  for a database Schemas for all relations in the database  Tuples  for a relation (Rows) The set of rows in a relation are the tuples of that relation  Note: Attribute values may be null Murali Mani

Primary Key Constraints A   set of attributes is a key for a relation if: No two distinct tuples can have the same values in all key fields A proper subset of the key attributes is not a key.  Superkey: A proper subset of a superkey may be a superkey  If multiple keys, one of them is chosen to be the primary key.  Eg: PRIMARY KEY (Student) = <sNumber>  Primary key attributes cannot take null values Murali Mani

Candidate Keys (SQL: Unique)  Keys that are not primary keys are candidate keys.  Specified in SQL using UNIQUE  Attribute of unique key may have null values !  Eg: Student (sNumber, sName) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <sName> Murali Mani

Violation of key constraints A relation violates a primary key constraint if:  There is a row with null values for any attribute of primary key. (or) There are 2 rows with same values for all attributes of primary key   Consider R (a, b) where a is unique. R violates the unique constraint if all of the following are true  2 rows in R have the same non-null values for a Murali Mani

Keys: Example Student sNumber sName address 1 Dave 144FL 2 Greg 320FL Primary Key: <sNumber> Candidate key: <sName> Some superkeys: {<sNumber, address>, <sName>, <sNumber>, <sNumber, sName> <sNumber, sName, address>} Murali Mani

Foreign Key Constraints  To specify an attribute (or multiple attributes) S1 of a relation R1 refers to the attribute (or attributes) S2 of another relation R2  Eg: Professor (pName, pOffice) Student (sNumber, sName, advisor) PRIMARY KEY (Professor) = <pName> FOREIGN KEY Student (advisor) REFERENCES Professor (pName) Murali Mani

Foreign Key Constraints  FOREIGN KEY R1 (S1) REFERENCES R2 (S2)  Like a logical pointer  The values of S1 for any row of R1 must be values of S2 for some row in R2 (null values are allowed)  S2 must be a key for R2  R2 can be the same as R1 (i.e., a relation can have a foreign key referring to itself). Murali Mani

Foreign Keys: Examples Dept (dNumber, dName) Person (pNumber, pName, dept) Persons working for Depts PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (dept) REFERENCES Dept (dNumber) Person (pNumber, pName, father) PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (father) REFERENCES Person (pNumber) Murali Mani Person and his/her father

Violation of Foreign Key constraints  Suppose we have: FOREIGN KEY R1 (S1) REFERENCES R2 (S2)  This constraint is violated if   Consider a row in R1 with non-null values for all attributes of S1 If there is no row in R2 which have these values for S2, then the FK constraint is violated. Murali Mani

Relational Model: Summary  Structures   Relations (Tables) Attributes (Columns, Fields)  Constraints  Key    Primary key, candidate key (unique) Super Key Foreign Key Murali Mani

ER schema → Relational schema Simple Algorithm   Entity type E → Relation E’  Attribute of E → Attribute as E’  Key for E → Primary Key for E’ For relationship type R between E1, E2, …, En    Create separate relation R’ Attributes of R’ are primary keys of E1, E2, …, En and attributes of R Primary Key for R’ is defined as:    If the maximum cardinality of any Ei is 1, primary key for R’ = primary key for Ei Else, primary key for R’ = primary keys for E1, E2, …, En Define “appropriate” foreign keys from R’ to E1, E2, …, En Murali Mani

Simple algorithm: Example 1 pNumber dNumber Person (1, *) Works For (0, *) pName Dept dName years Person (pNumber, pName) Dept (dNumber, dName) WorksFor (pNumber, dNumber, years) PRIMARY KEY (Person) = <pNumber> PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (WorksFor) = <pNumber, dNumber> FOREIGN KEY WorksFor (pNumber) REFERENCES Person (pNumber) FOREIGN KEY WorksFor (dNumber) REFERENCES Dept (dNumber) Murali Mani

Simple Algorithm: Example 2 Supplier (sName, sLoc) Consumer (cName, cLoc) Product (pName, pNumber) Supply (supplier, consumer, product, price, qty) pNumber pName Product (0, *) sName cName Supplier (1, *) Supply (0, *) Consumer sLoc cLoc price qty PRIMARY Key (Supplier) = <sName> PRIMARY Key (Consumer) = <cName> PRIMARY Key (Product) = <pName> PRIMARY Key (Supply) = <supplier, consumer, product> FOREIGN KEY Supply (supplier) REFERENCES Supplier (sName) FOREIGN KEY Supply (consumer) REFERENCES Consumer (cName) FOREIGN KEY Supply (product) REFERENCES Product (pName) Murali Mani

Simple Algorithm: Example 3 pN um ber pN am e P a rt s u p e rP a rt (0 , *) s u b P a rt (0 , 1 ) Part (pName, pNumber) Contains (superPart, subPart, quantity) C o n t a in s q u a n t ity PRIMARY KEY (Part) = <pNumber> PRIMARY KEY (Contains) = <subPart> FOREIGN KEY Contains (superPart) REFERENCES Part (pNumber) FOREIGN KEY Contains (subPart) REFERENCES Part (pNumber) Murali Mani

Decreasing the number of Relations Technique 1  If the relationship type R contains an entity type, say E, whose maximum cardinality is 1, then R may be represented as attributes of E.   If the cardinality of E is (1, 1), then no “new nulls” are introduced If the cardinality of E is (0, 1) then “new nulls” may be introduced. Murali Mani

Example 1 sNumber pNumber Student (1,1) Has Advisor (0, *) sName Professor pName years Student (sNumber, sName, advisor, years) Professor (pNumber, pName) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (Professor) = <pNumber> FOREIGN KEY Student (advisor) REFERENCES Professor (pNumber) Note: advisor will never be null for a student Murali Mani

Example 2 pNumber dNumber Person (0,1) Works For (0, *) Dept pName dName years Person (pNumber, pName, dept, years) Dept (dNumber, dName) PRIMARY KEY (Person) = <pNumber> PRIMARY KEY (Dept) = <dNumber> FOREIGN KEY Person (dept) REFERENCES Dept (dNumber) Dept and years may be null for a person Murali Mani

Example 3 pNum ber pNam e P a rt s u p e rP a rt (0 , *) s u b P a rt (0 , 1 ) C o n t a in s q u a n tity Part (pNumber, pname, superPart, quantity) PRIMARY KEY (Part) = <pNumber> FOREIGN KEY Part (superPart) REFERENCES Part (pNumber) Note: superPart gives the superpart of a part, and it may be null Murali Mani

Decreasing the number of Relations Technique 2 (not recommended)   If the relationship type R between E1 and E2 is 1:1, and the cardinality of E1 or E2 is (1, 1), then we can combine everything into 1 relation. Let us assume the cardinality of E1 is (1, 1). We have one relation for E2, and move all attributes of E1 and for R to be attributes of E2.   If the cardinality of E2 is (1, 1), no “new nulls” are introduced If the cardinality of E2 is (0, 1) then “new nulls” may be introduced. Murali Mani

Example 1 sNumber pNumber Student (0,1) Has Advisor (1,1) Professor sName pName years Student (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber, pName, and years can be null for students with no advisor Murali Mani

Example 2 sNumber pNumber Student (1,1) Has Advisor (1,1) Professor sName pName years Student (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber cannot be null for any student. Murali Mani

Other details  Composite  attribute in ER Include an attribute for every component of the composite attribute.  Multi-valued   attribute in ER We need a separate relation for any multi-valued attribute. Identify appropriate attributes, keys and foreign key constraints. Murali Mani

Composite and Multi-valued attributes in ER sNumber sName major Student sAge address street city state Student (sNumber, sName, sAge, street, city, state) StudentMajor (sNumber, major) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (StudentMajor) = <sNumber, major> FOREIGN KEY StudentMajor (sNumber) REFERENCES Student (sNumber) Murali Mani

Weak entity types  Consider     weak entity type E A relation for E, say E’ Attributes of E’ = attributes of E in ER + keys for all indentifying entity types. Key for E’ = the key for E in ER + keys for all the identifying entity types. Identify appropriate FKs from E’ to the identifying entity types. Murali Mani

Weak entity types: Example Dept (dNumber, dName) Course (cNumber, dNumber, cName) PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (Course) = <cNumber, dNumber> FOREIGN KEY Course (dNumber) REFERENCES Dept (dNumber) Murali Mani

ISA Relationship types: Method 1 sNumber sName Student (sNumber, sName) UGStudent (sNumber, year) GradStudent (sNumber, program) Student year ISA UGStudent ISA program GradStudent An UGStudent will be represented in both Student relation as well as UGStudent relation (similarly GradStudent) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (UGStudent) = <sNumber> PRIMARY KEY (GradStudent) = <sNumber> FOREIGN KEY UGStudent (sNumber) REFERENCES Student (sNumber) FOREIGN KEY UGStudent (sNumber) REFERENCES Student (sNumber) Murali Mani

ISA Relationship types: Method 2 sNumber sName Student year ISA ISA UGStudent program GradStudent Student (sNumber, sName, year, program) PRIMARY KEY (Student) = <sNumber> Note: There will be null values in the relation. Murali Mani

ISA Relationship types: Method 3 Student (sNumber, sName) UGStudent (sNumber, sName, year) GradStudent (sNumber, sName, program) UGGradStudent (sNumber, sName, year, program) sNumber sName PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (UGStudent) = <sNumber> PRIMARY KEY (GradStudent) = <sNumber> PRIMARY KEY (UGGradStudent) = <sNumber> Student year ISA UGStudent ISA program Any student will be represented in only one of the relations as appropriate. GradStudent Murali Mani

Add a comment

Related presentations

Related pages

Relational model - Wikipedia

The relational model (RM) for database management is an approach to managing data using a structure and language consistent with first-order predicate ...
Read more

Relational database - Wikipedia

A relational database is a digital database whose organization is based on the relational model of data, as proposed by E. F. Codd in 1970. The ...
Read more

What is a Relational Model? - Definition from Techopedia

The relational model is the conceptual basis of relational databases. Proposed by E.F. Codd in 1969, it is a method of structuring data using relations ...
Read more

The Relational Data Model - Stanford University

CHAPTER 8 The Relational Data Model One of the most important applications for computers is storing and managing information. The manner in which ...
Read more

Databases, Types, and the Relational Model: The Third ...

Chris J. Date - Databases, Types, and the Relational Model: The Third Manifesto jetzt kaufen. ISBN: 9780321399427, Fremdsprachige Bücher - Datenbanken
Read more

What are the Advantages of a Relational Database Model ...

Under the relational model, accessing data in a database does not require navigating a rigid pathway through a tree or hierarchy.
Read more

SQL: Relational Databases - scribd.com

SQL: Relational Databases - Download as PDF File (.pdf), ... all of which is formally described and organized according to the relational model.
Read more

Relation Data Model - Tutorialspoint

Relation Data Model - Learn DBMS in simple and easy steps starting from its overview, Architecture, data models, data schemas, data independence, ED ...
Read more

Relational Databases vs. Graph Databases: A Comparison

Explore the major differences between relational and graph databases, including data models, query languages, integration drivers and importing data.
Read more

Relationale Datenbank – Wikipedia

Edgar F. Codd: The Relational Model for Database Management. Version 2. Addison-Wesley, Reading u. a. 1990, ISBN 0-201-14192-2;
Read more