Relational Data Model
Docsity.com
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Relational Data Model - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)
This lecture slide is very easy to understand and very helpful to built a concept about the foundation of computers and Database Design.The key points in these slides are:Relational Data Model, Unary Operations, Binary Operations, Cartesian Product, Materialized Views, Natural Join, Preservation Issue, Horizontal Subset of Tuples, First Relation, Second Attribute, Growth and Restructuring
Typology: Slides
2012/2013
1 / 15
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Relational Data Model - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Relational Data Model
Outline
• Sect. 6.4, Intro to Relational Algebra
Comprise of:
– Unary Operations: σ (selection) and ∏
(projection)
– Binary Operations: ∪ (union), - (difference),
∩ (intersection), x (cartesian product)
– Natural Join: * and Join: ⋈
• Sect. 6.5, Views and Materialized Views
• Sect. 6.6, Info of Preservation Issue
Unary: ∏ (projection)
Extract a vertical subset of columns from
a relation.
Name Age Salary
Harry 34 80,
Sally 28 90,
George 29 70,
Helena 54 54,
Peter 34 40,
Name
Harry
Sally
George
Helena
Peter
Employee ∏ (^) name (Employee)
σ (selection), ∏ (projection)
Name
Sally
∏ (^) name (б Salary>80,000 (Employee))
Name Age Salary
Harry 34 80,
Sally 28 90,
George 29 70,
Helena 54 54,
Peter 34 40,
Employee
Binary: - (difference)
Contains the tuples from the first relation
that are not in the second relation.
FN LN
John Smith Ricardo Brown
Francis Johnson
FN LN
Susan Yao
Ramesh Shah
Barbara Jones
Amy Ford
Jimmy Wang
FN LN
John Smith
Ricardo Brown
Susan Yao
Francis Johnson
Ramesh Shah
Student Professor
FN LN
Barbara Jones
Amy Ford
Jimmy Wang
Student - Professor
Professor - Student
Binary: ∩ (intersection)
Contains tuples common to the two
relations.
FN LN
Susan Yao
Ramesh Shah
Barbara Jones
Amy Ford
Jimmy Wang
FN LN
John Smith
Ricardo Brown
Susan Yao
Francis Johnson
Ramesh Shah
Student Professor
FN LN
Susan Yao
Ramesh Shah
Student ∩ Professor
Join: ⋈
• Used to combine related tuples from two
relations into single tuples.
• Allows us to process relationships among
relations.
• Example: to retrieve the name of the
manager of each department:
Step1) Dept_mgr ← Dept ⋈
Mgr_Ssn=SsnEmployee
Step2) Result ← πDname, Lname,
Fnamer(Dept_mgr)
Example Join: ⋈
EMPLOYEE
Fname Minit Lname Ssn
John B Smith 1.23E+
Franklin T Wong 3.33E+
Alicia J Zelaya 1E+
Jennifer S Wallace 9.88E+
Ramesh K Narayan 6.67E+
Joyce A English 4.53E+
Ahmad V Jabbar 9.88E+
James E Borg 8.89E+
DEPARTMENT
Dname Dnumber Mgr_ssn Research 5 3.33E+
Administration 4 9.88E+
Headquarters 1 8.89E+
DEPT_MGR
Dname Dnumb er
Mgr_SSN ... Fname Minit Lname SSN
Research 5 3.33E+08 ... Franklin T Wong 3.33E+
Administratn 4 9.88E+08 ... Jennifer S Wallace 9.88E+
Headquarters 1 8.89E+08 ... James E Borg 8.89E+
Example Natural Join: *
DEPARTMENT
Dname Dnumber Mgr_ssn
Research 5 3.33E+
Administration 4 9.88E+
Headquarters 1 8.89E+
DEPT_LOCATIONS
Dnumber Dlocation
1 Houston
4 Stafford
5 Bellaire
5 Sugarland
5 Houston
DEPT_LOCS
Dname Dnumber Mgr_ssn Dlocation Headquarters 1 8.89E+08 Houston
Administration 4 9.88E+08 Stafford
Research 5 3.33E+08 Bellaire Research 5 3.33E+08 Sugarland
Research 5 3.33E+08 Houston