week 2 lecture the relational database model samuel connsamuel conn, faculty suggestions for using...
DESCRIPTION
3 Logical View of Data Relational Database Designer focuses on logical representation rather than physical Use of table advantageous Structural and data independence Related records stored in independent tables Logical simplicity Allows for more effective design strategiesTRANSCRIPT
![Page 1: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/1.jpg)
Week 2 LectureThe Relational Database Model Samuel Conn, Faculty
Suggestions for using the Lecture Slides
![Page 2: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/2.jpg)
2
Critical Questions & Areas of Study
How does the relational database model take a logical view of data? Learning that the relational database model’s basic components are entities and their attributes, and relationships among entities Learning how entities and their attributes are organized into tables Study about relational database operators, the data dictionary, and the system catalog Study how data redundancy is handled in the relational database model
Study why indexing is important
![Page 3: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/3.jpg)
3
Logical View of Data
Relational Database Designer focuses on logical representation
rather than physical Use of table advantageous
• Structural and data independence • Related records stored in independent tables • Logical simplicity
Allows for more effective design strategies
![Page 4: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/4.jpg)
4
Logical View of Data (con’t.) Entities and Attributes
Entity is a person, place, event, or thing about which data is collected
Attributes are characteristics of the entity Tables
Holds related entities or entity set Also called relations Comprised of rows and columns
![Page 5: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/5.jpg)
5
Table Characteristics
• Two-dimensional structure with rows and columns
• Rows (tuples) represent single entity • Columns represent attributes • Row/column intersection represents
single value • Tables must have an attribute to
uniquely identify each row
![Page 6: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/6.jpg)
6
Table Characteristics (con’t.)
• Column values all have same data format
• Each column has range of values called attribute domain
• Order of the rows and columns is immaterial to the DBMS
![Page 7: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/7.jpg)
7
Keys
One or more attributes that determine other attributes
Key attribute Composite key
Full functional dependence Entity integrity
Uniqueness No ‘null’ value in key
![Page 8: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/8.jpg)
8
Example Tables
Figure 2.1
![Page 9: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/9.jpg)
9
Simple Relational Database
Figure 2.2
![Page 10: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/10.jpg)
10
Keys (con’t.) Superkey
Uniquely identifies each entity Candidate key
Minimal superkey Primary key
Candidate key to uniquely identify all other attributes in a given row
Secondary key Used only for data retrieval
Foreign key Values must match primary key in another table
![Page 11: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/11.jpg)
11
Integrity Rules
Entity integrity Ensures all entities are unique Each entity has unique key
Referential integrity Foreign key must have null value or match
primary key values Makes it impossible to delete row whose
primary key has mandatory matching foreign key values in another table
![Page 12: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/12.jpg)
12
Relational Database Operators
Relational algebra determines table manipulations Key operators
SELECT PROJECT JOIN
Other operators INTERSECT UNION DIFFERENCE PRODUCT DIVIDE
![Page 13: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/13.jpg)
13
Union
Combines all rows
Figure 2.5
![Page 14: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/14.jpg)
14
Yields rows that appear in both tables
Intersect
Figure 2.6
![Page 15: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/15.jpg)
15
Yields rows not found in other tables
Difference
Figure 2.7
![Page 16: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/16.jpg)
16
Yields all possible pairs from two tables
Product
Figure 2.8
![Page 17: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/17.jpg)
17
Yields a subset of rows based on specified criterion
Select
Figure 2.9
![Page 18: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/18.jpg)
18
Yields all values for selected attributes
Project
Figure 2.10
![Page 19: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/19.jpg)
19
Information from two or more tables is combined
Join
Figure 2.11
Figure 2.14
![Page 20: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/20.jpg)
20
Links tables by selecting rows with common values in common attribute(s)
Three-stage process Product creates one table Select yields appropriate rows Project yields single copy of each
attribute to eliminate duplicate columns
Natural Join Process
![Page 21: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/21.jpg)
21
Other Joins EquiJOIN
Links tables based on equality condition that compares specified columns of tables
Does not eliminate duplicate columns Join criteria must be explicitly defined
Theta JOIN EquiJOIN that compares specified columns of each
table using operator other than equality one Outer JOIN
Matched pairs are retained Unmatched values in other tables left null Right and left
![Page 22: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/22.jpg)
22
Requires user of single-column table and two-column table
Divide
Figure 2.17
![Page 23: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/23.jpg)
23
Data Dictionary and System Catalog Data dictionary
Provides detailed account of all tables found within database
Metadata Attribute names and characteristics
System catalog Detailed data dictionary System-created database Stores database characteristics and contents Tables can be queried just like any other tables Automatically produces database documentation
![Page 24: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/24.jpg)
24
Relationships within Relational Database
Relationship classifications 1:1 1:M M:N
E-R Model ERD Maps E-R model Chen Crow’s Feet
![Page 25: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/25.jpg)
25
ERD Symbols
• Rectangles represent entities • Diamonds represent the relationship(s)
between the entities • “1” side of relationship
– Number 1 in Chen Model – Bar crossing line in Crow’s Feet Model
• “Many” relationships – Letter “M” and “N” in Chen Model – Three pronged “Crow’s foot” in Crow’s Feet
Model
![Page 26: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/26.jpg)
26
Example 1:M Relationship
Figure 2.18
![Page 27: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/27.jpg)
27
Example 1:M Relationship
Figure 2.20
![Page 28: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/28.jpg)
28
Example M:N Relationship
Figure 2.23
![Page 29: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/29.jpg)
29
Example M:N Relationship
Figure 2.24
![Page 30: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/30.jpg)
30
Converting M:N Relationship to Two 1:M Relationships
Figure 2.25
![Page 31: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/31.jpg)
31
Converting M:N Relationship to Two 1:M Relationships (con’t.)
Figure 2.26
![Page 32: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/32.jpg)
32
Converting M:N Relationship to Two 1:M Relationships (con’t.)
Figure 2.27
![Page 33: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/33.jpg)
33
Converting M:N Relationship to Two 1:M Relationships (con’t.)
Figure 2.28
![Page 34: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/34.jpg)
34
Data Redundancy Revisited
Foreign keys can reduce redundancy
Some redundancy is desirable Called controlled redundancy Speed Information requirements
![Page 35: Week 2 Lecture The Relational Database Model Samuel ConnSamuel Conn, Faculty Suggestions for using the…](https://reader035.vdocuments.us/reader035/viewer/2022070616/5a4d1bfd7f8b9ab0599ecf28/html5/thumbnails/35.jpg)
35
Points to location Makes retrieval of data faster
Indexes
Figure 2.31