Chapter3-relational Database2

Chapter 3 The Relational Database Model (Part 2)

OBJECTIVE: After learn this chapter, students will •Explain the logical view of the data in relational database model •Define the relational model’s basic components. •Explain how entities and their attributes are organized into tables. •Analyses the difference between data dictionary and system catalog •Explain how data redundancy is handled in the relational database model.

A Logical View of Data • Relational database model’s structural and data independence enables us to view data logically rather than physically. • The logical view allows a simpler file concept of data storage. • The use of logically independent tables is easier to understand. • Logical simplicity yields simpler and more effective database design methodologies.

A Logical View of Data • Entities and Attributes – An entity is a person, place, event, or thing for which we intend to collect data. • University -- Students, Faculty Members, Courses • Airlines -- Pilots, Aircraft, Routes, Suppliers

– Each entity has certain characteristics known as attributes. • Student -- Student Number, Name, GPA, Date of Enrollment, Data of Birth, Home Address, Phone Number, Major • Aircraft -- Aircraft Number, Date of Last Maintenance, Total Hours Flown, Hours Flown since Last Maintenance

A Logical View of Data • Entities and Attributes – A grouping of related entities becomes an entity set. • The STUDENT entity set contains all student entities. • The FACULTY entity set contains all faculty entities. • The AIRCRAFT entity set contains all aircraft entities.

A Logical View of Data • Tables and Their Characteristics – A table contains a group of related entities -- i.e. an entity set. – The terms entity set and table are often used interchangeably. – A table is also called a relation.

Keys • Controlled redundancy (shared common attributes) makes the relational database work. • The primary key of one table appears again as the link (foreign key) in another table. • If the foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key are said to exhibit referential integrity.

Keys

• A key helps define entity relationships. – The key’s role is based on a concept known as determination, which is used in the definition of functional dependence. • The attribute B is functionally dependent on A if A determines B. • An attribute that is part of a key is known as a key attribute. • A multi-attribute key is known as a composite key. • If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

Relational Database Keys

Table 2.3

Integrity Rules Revisited

Table 2.4

The Data Dictionary and the System Catalog • Data dictionary contains metadata to provide detailed accounting of all tables within the database. • System catalog is a very detailed system data dictionary that describes all objects within the database. – System catalog is a system-created database whose tables store the database characteristics and contents. – System catalog tables can be queried just like any other tables. – System catalog automatically produces database documentation.

A Sample Data Dictionary

Table 2.6

Relationships within the Relational Database

• E-R Diagram (ERD) – Rectangles are used to represent entities. – Entity names are nouns and capitalized. – Diamonds are used to represent the relationship(s) between the entities. – The number 1 is used to represent the “1” side of the relationship. – The letter M is used to represent the “many” sides of the relationship.

The Relationship Between Painter and Painting

Figure 2.17

An Alternate Way to Present the Relationship Between Painter and Painting

Figure 2.18

A 1:M Relationship: The CH2_MUSEUM Database

Figure 2.19

The 1:M Relationship Between Course and Class

Figure 2.20

The M:N Relationship Between Student and Class

Figure 2.22

Sample Student Enrollment Data

Table 2.7

A Many-to-Many Relationship Between Student and Class

Figure 2.23

Changing the M:N Relationship to Two 1:M Relationships

Figure 2.25

The Expanded Entity Relationship Model

Figure 2.26

The Relational Schema for the Entity Relationship Diagram in Figure 2.26

Figure 2.27

Data Redundancy Revisited • Proper use of foreign keys is crucial to exercising data redundancy control. • Database designers must reconcile three often contradictory requirements: design elegance, processing speed, and information requirements. (Chapter 4) • Proper data warehousing design even requires carefully defined and controlled data redundancies, to function properly. (Chapter 13)

Figure 2.29 The Relational Schema for the Invoicing System in Figure 2.28

The redundancy is crucial to the system’s success. Copying the product price from the PRODUCT table to the LINE table means that it is possible to maintain the historical accuracy of the transactions.