Its232 introduction to database management systems
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

ITS232 Introduction To Database Management Systems PowerPoint PPT Presentation


  • 83 Views
  • Uploaded on
  • Presentation posted in: General

ITS232 Introduction To Database Management Systems . Siti Nurbaya Ismail Faculty of Computer Science & Mathematics, Universiti Teknologi MARA (UiTM), Kedah | [email protected] | http://www.sitinur151.wordpress.com | | A2-3039 | ext:2561 | 012-7760562 |. CHAPTER 3

Download Presentation

ITS232 Introduction To Database Management Systems

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Its232 introduction to database management systems

ITS232Introduction To Database Management Systems

Siti Nurbaya Ismail

Faculty of Computer Science & Mathematics,

Universiti Teknologi MARA (UiTM), Kedah

| [email protected] | http://www.sitinur151.wordpress.com |

| A2-3039 | ext:2561 | 012-7760562 |

CHAPTER 3

The Relational Model


Its232 introduction to database management systems

Chapter 3: The Relational Data Model

3.0 THE RELATIONAL MODEL

3.1 A Logical View Of Data

3.2 Keys

3.3 Integrity Rules Revisited

3.4 Data Dictionary And System Catalogue

3.5 Relationship Within The Relational Database

3.6 Data Redundancy Revisited

3.7 Indexes


3 0 the relational model a glance of the big concept

3.0 The Relational ModelA Glance Of The Big Concept


3 0 the relational model 3 1 a logical view of data

3.0 The Relational Model3.1A Logical View Of Data

  • Relational model

    • Enables programmer to view data logically rather than physically

  • Table

    • Has advantages of structural and data independence

    • Resembles a file from conceptual point of view

    • Easier to understand than its hierarchical and network database


3 0 the relational model 3 1 a logical view of data table and their characteristics

3.0 The Relational Model3.1A Logical View Of Data: Table and Their Characteristics

  • Table:

    • two-dimensional structure composed of rows and columns

    • Contains group of related entities = an entity set

  • Terms entity set and table are often used interchangeably

  • Table also called a relation because the relational model’s creator, Codd, used the term relation as a synonym for table

  • Think of a table as a persistent relation:

    • A relation whose contents can be permanently saved for future use


3 0 the relational model 3 1 a logical view of data table and their characteristics1

3.0 The Relational Model3.1 A Logical View Of Data: Table and Their Characteristics


3 0 the relational model 3 1 a logical view of data table and their characteristics2

3.0 The Relational Model3.1 A Logical View Of Data: Table and Their Characteristics

BIKE TABLE

primary key

foreign key

keys

rows (tuple)

>represents an entity

(data Value)

columns

>represents an attribute

>each column has a distinct name


3 0 the relational model 3 1 a logical view of data table and their characteristics3

3.0 The Relational Model3.1 A Logical View Of Data: Table and Their Characteristics

MEMBER TABLE

keys

primary key

rows

(tuple)

>an entity

columns

>represents an attribute

>each column has a distinct name


3 0 the relational model 3 1 a logical view of data table and their characteristics4

3.0 The Relational Model3.1 A Logical View Of Data: Table and Their Characteristics


3 0 the relational model 3 2 keys the concepts

3.0 The Relational Model3.2 Keys: The Concepts

  • Consists of one or more attributes that determine other attributes

  • Key’s role is based on determination

    • If you know the value of attribute A, you can look up (determine) the value of attribute B

    • A  B


3 0 the relational model 3 2 keys types

3.0 The Relational Model3.2 Keys: Types


3 0 the relational model 3 2 keys types1

3.0 The Relational Model3.2 Keys: Types

Examples:

Table name: member

IDmember (PK in table member)

IDmember (FK in table bikes)

Table name: bikes

IDbike (PK in table bikes)


3 0 the relational model 3 2 keys nulls

3.0 The Relational Model3.2 Keys: Nulls

  • Nulls:

    • No data entry

    • Not permitted in primary key

    • Should be avoided in other attributes

    • Can represent

      • An unknown attribute value

      • A known, but missing, attribute value

      • A “not applicable” condition

    • Can create problems when functions such as COUNT, AVERAGE, and SUM are used

    • Can create logical problems when relational tables are linked


3 0 the relational model 3 2 keys nulls1

3.0 The Relational Model3.2 Keys: Nulls

Examples:

Table name: member

** null


3 0 the relational model 3 2 keys controlled redundancy

3.0 The Relational Model3.2 Keys: Controlled Redundancy

  • Controlled redundancy:

    • Makes the relational database work

    • Tables within the database share common attributes that enable the tables to be linked together

    • Multiple occurrences of values in a table are not redundant when they are required to make the relationship work

    • Redundancy exists only when there is unnecessary duplication of attribute values

    • The importance keys for maintain controlled redundancy are:

      • Foreign key (FK)

      • Primary key (PK)

    • Controlled Redundancy may lead to;

      • Referential integrity

      • FK contains a value that refers to an existing valid tuple (row) in another relation


3 0 the relational model 3 2 keys controlled redundancy1

3.0 The Relational Model3.2 Keys: Controlled Redundancy

Table name: member

IDmember(10001) determine

biketype(Orange P7 & Diamonback X-Link)

Table name: bikes

** knowing IDmember in table member,

means that you are able to look up

(determine) what type of bikes that

certain member have

Examples:


3 0 the relational model 3 2 keys controlled redundancy2

3.0 The Relational Model3.2 Keys: Controlled Redundancy

  • knowing IDmember in table member, means that you are able to look up (determine) what type of bikes that certain member have

  • There for, using the attributes of the member table, we can represent the statement “IDmember determines biketype” by writing

    • IDmember biketype

  • In contrast, biketype is not determined by IDmember, because it is quite possible for several members to have Orange P7 bike types


3 0 the relational model 3 3 integrity rules revisited entity integrity

3.0 The Relational Model3.3 Integrity Rules Revisited: Entity Integrity


3 0 the relational model 3 3 integrity rules revisited referential integrity

3.0 The Relational Model3.3 Integrity Rules Revisited: Referential Integrity


3 0 the relational model 3 3 integrity rules revisited integrity rules

3.0 The Relational Model3.3 Integrity Rules Revisited: Integrity Rules


3 0 the relational model 3 4 data dictionary system catalog

3.0 The Relational Model3.4 Data Dictionary & System Catalog

  • Data dictionary

    • Provides detailed accounting of all tables found within the user/designer-created database

    • Contains (at least) all the attribute names and characteristics for each table in the system

    • Contains metadata—data about data

    • Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures


3 0 the relational model 3 4 a sample of data dictionary

3.0 The Relational Model3.4 A Sample Of Data Dictionary


3 0 the relational model 3 4 a sample of data dictionary1

3.0 The Relational Model3.4 A Sample Of Data Dictionary


3 0 the relational model 3 4 data dictionary and system catalog

3.0 The Relational Model3.4 Data Dictionary And System Catalog

  • System catalog

    • Contains metadata

    • Detailed system data dictionary that describes all objects within the database

    • Terms “system catalog” and “data dictionary” are often used interchangeably

    • Can be queried just like any user/designer-created table


3 0 the relational model 3 5 relationship within relational database

3.0 The Relational Model3.5 Relationship Within Relational Database

Relationship is a logical interaction among the entities in a relational database.

Operate in both directions

There are 3 basic relationship in a database;


3 0 the relational model 3 5 relationship within relational database one to many 1 m

3.0 The Relational Model3.5 Relationship Within Relational Database: one-to-many (1:M)

Relational database norm

Found in any database environment


3 0 the relational model 3 5 relationship within relational database one to many 1 m1

3.0 The Relational Model3.5 Relationship Within Relational Database: one-to-many (1:M)


3 0 the relational model 3 5 relationship within relational database one to many 1 m2

3.0 The Relational Model3.5 Relationship Within Relational Database: one-to-many (1:M)


3 0 the relational model 3 5 relationship within relational database one to many 1 m3

3.0 The Relational Model3.5 Relationship Within Relational Database: one-to-many (1:M)


3 0 the relational model 3 5 relationship within relational database one to one 1 1

3.0 The Relational Model3.5 Relationship Within Relational Database: one-to-one (1:1)

One entity can be related to only one other entity, and vice versa

Sometimes means that entity components were not defined properly

Could indicate that two entities actually belong in the same table

As rare as 1:1 relationships should be, certain conditions absolutely require their use


3 0 the relational model 3 5 relationship within relational database many to many m n

3.0 The Relational Model3.5 Relationship Within Relational Database: many-to-many (M:N)

Can be implemented by breaking it up to produce a set of 1:m relationships

Can avoid problems inherent to m:n relationship by creating a composite entity or bridge entity


3 0 the relational model 3 5 relationship within relational database many to many m n1

3.0 The Relational Model3.5 Relationship Within Relational Database: many-to-many (M:N)


3 0 the relational model 3 5 relationship within relational database many to many m n2

3.0 The Relational Model3.5 Relationship Within Relational Database: many-to-many (M:N)

Implementation of a composite entity

Yields required m:n to 1:m conversion

Composite entity table must contain at least the primary keys of original tables

Linking table contains multiple occurrences of the foreign key values

Additional attributes may be assigned as needed


3 0 the relational model 3 5 relationship within relational database many to many m n3

3.0 The Relational Model3.5 Relationship Within Relational Database: many-to-many (M:N)


3 0 the relational model 3 5 relationship within relational database many to many m n4

3.0 The Relational Model3.5 Relationship Within Relational Database: many-to-many (M:N)


3 0 the relational model 3 5 relationship within relational database many to many m n5

3.0 The Relational Model3.5 Relationship Within Relational Database: many-to-many (M:N)


3 0 the relational model 3 5 relationship within relational database many to many m n6

3.0 The Relational Model3.5 Relationship Within Relational Database: many-to-many (M:N)


3 0 the relational model 3 6 data redundancy revisited

3.0 The Relational Model3.6 Data Redundancy Revisited

  • Data redundancy leads to data anomalies

    • Such anomalies can destroy the effectiveness of the database

  • Foreign keys

    • Control data redundancies by using common attributes shared by tables

    • Crucial to exercising data redundancy control

  • Sometimes, data redundancy is necessary


3 0 the relational model 3 6 data redundancy revisited1

3.0 The Relational Model3.6 Data Redundancy Revisited


3 0 the relational model 3 7 indexes

3.0 The Relational Model3.7 Indexes

  • Arrangement used to logically access rows in a table

  • Index key

    • Index’s reference point

    • Points to data location identified by the key

  • Unique index

    • Index in which the index key can have only one pointer value (row) associated with it

  • Each index is associated with only one table


3 0 the relational model 3 7 indexes1

3.0 The Relational Model3.7 Indexes


3 0 the relational model 3 7 summary

3.0 The Relational Model3.7 Summary

  • Tables are basic building blocks of a relational database

  • Keys are central to the use of relational tables

  • Keys define functional dependencies

    • Superkey

    • Candidate key

    • Primary key

    • Secondary key

    • Foreign key


3 0 the relational model 3 7 summary1

3.0 The Relational Model3.7 Summary

Each table row must have a primary key which uniquely identifies all attributes

Tables can be linked by common attributes. Thus, the primary key of one table can appear as the foreign key in another table to which it is linked

The relational model supports relational algebra functions: SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

Good design begins by identifying appropriate entities and attributes and the relationships among the entities. Those relationships (1:1, 1:M, and M:N) can be represented using ERDs.


3 0 the relational model 3 7 summary2

3.0 The Relational Model3.7 Summary


  • Login