The relational model
This presentation is the property of its rightful owner.
Sponsored Links
1 / 38

The Relational Model PowerPoint PPT Presentation


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

The Relational Model. Instructor: Mohamed Eltabakh [email protected] Relational Model. Another model for describing your data and application requirements Currently the most widely used model Vendors: Oracle, Microsoft, IBM Recent competitions

Download Presentation

The Relational Model

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


The relational model

The Relational Model

Instructor: Mohamed Eltabakh

[email protected]


Relational model

Relational Model

  • Another model for describing your data and application requirements

  • Currently the most widely used model

    • Vendors: Oracle, Microsoft, IBM

  • Recent competitions

    • Object Oriented Model: ObjectStore, Oracle

    • XML Databases : native and extensions

  • What about ER model

    • ER model is the first step in the design

    • ER model is then translated (mapped) to relational model


Design and build phases

Design and Build Phases

Phase 1

Phase 2

Phase 3

ER Model & ERD

Build the database

Relational Model


Relational model1

Relational Model

  • Structure:

    • Relations (also called Tables)

    • Attributes (also called Columns or Fields)


Attributes in relational model

Attributes in Relational Model

  • Attribute Name

    • Each attribute of a relation has a unique name within the relation

  • Attribute Domain

    • The set of allowed values for each attribute is called the domain of the attribute

    • E.g., integer, real, date, string {“Male”, “Female”}, etc.

  • Atomic Attributes

    • Attribute values are required to be atomic

      (primitive or derived)

    • Note: multivalued attributes are not atomic

    • Note: composite attributes are not atomic

    • We will see how to handle non-atomic attributes

  • Special Null Value

    • The special value null is a member of every domain

    • The null value means the values does not exist (unknown)


Relational schema

Relational Schema

  • Schemais the structure of the relation

  • Attributes A1, A2, ..., An of relation R

    • R=(A1,A2,...,An ) is the relations schema of R

  • Relational Schema of “customer” is

    • customer = (customer_name, customer_street, customer_city)

    • customer (customer_name, customer_street, customer_city)


Relational instance

Relational Instance

  • Instance is the current schema plus the current tuples in the relation

  • The current instance of “customer” has four tuples (rows or records)

  • Instances are frequently changing

    • E.g., inserting new tuples, deleting or updating existing tuples

  • Schemas may also change but not frequently


Arity cardinality of relations

Arity & Cardinality of Relations

  • Arity (Degree) of a relation

    • Number of attributes of that relation

  • Cardinality of a relation

    • Number of tuples in that relation

Arity = 3

Cardinality = 4


Relations are unordered

Relations are Unordered

  • Order of tuples is not important

  • Order of columns is not important

  • Think of a relation as a set (or bag)of tuples not as a list of tuples

    • Most current DBMSs allow bags

Student

Student

Student

These relations are equivalent


Keys of relations

Keys of Relations

  • Super Keys

    • Subset of attributes that uniquely identify each tuple

  • Candidate Keys

    • Minimal super key

  • Primary Keys

    • Choose any of the candidate keys

    • Represented by “underline” under the selected attributes

  • Set vs. Bag

    • If a relation has a primary key, then it is a set not a bag

Assuming the customer name is unique

  • customer (customer_name, customer_street,

  • customer_city)


The relational model

To create these relations (tables)

We use SQL


Relational query languages sql standard

Relational Query Languages (SQL Standard)

  • Developed by IBM (system R) in 1970s

  • Standard as used by many vendors (portable)

  • SQL (Structured Query Language)

  • Standards:

    • SQL-86

    • SQL-89 (minor revision)

    • SQL-92 (major revision)

    • SQL-99 (major extensions in OO, current standard)


Sql language

SQL Language

  • Data Definition Language (DDL)

    • Create tables, specifying the columns and their types of columns, the primary keys, etc.

    • Drop tables, add/drop columns, add/drop constraints – primary key, unique, etc.

  • Data Manipulation Language (DML)

    • Update, Insert, Delete tuples

    • Query the data


Sql ddl

SQL DDL

DDL allows the specification of:

  • The schema for each relation

  • The type and domain of values associated with each attribute

  • Integrity constraints

    • Will see many types of these constraints

  • The set of indices to be maintained for each relation

  • Security and authorization information for each relation

  • The physical storage structure of each relation on disk


Ddl creating tables

DDL: Creating Tables

CREATE TABLE <tableName> (

<col> <type>,

<col> <type>,

<col> <type>,

[CONSTRAINT <cName>] PRIMARY KEY (…),

[CONSTRAINT <cName>] UNIQUE (…),

[CONSTRAINT <cName>] FOREIGN KEY (…)

REFERENCES <tableName> (…)

);


Ddl creating relations

DDL ---- Creating Relations

  • Create “Courses” relation

  • Create “Students” relation

CREATE TABLE Students

(sid: CHAR(20),

name: CHAR(20),

login: CHAR(10),

age: INTEGER,

gpa: REAL);

CREATE TABLE Courses

(cid: Varchar2(20),

name: Varchar2(50),

maxCredits : integer,

graduateFlag: char(1));

  • Create “Enrolled” relation

  • Observe that type (domain) of each field is specified

  • Types are enforced by DBMS whenever tuples are added or modified

CREATE TABLE Enrolled

(sid: CHAR(20),

cid: Varchar2(20),

enrollDate: date,

grade: CHAR(2))


Domain types in sql

Domain Types in SQL

  • String Data:

    • char(n): Fixed length character string, with user-specified length n

    • varchar2(n): Variable length character strings, with user-specified maximum length n

  • Numbers:

    • int: Integer

    • Real: floating point number

    • Number(p,s): Number with precision p and scale s

  • Boolean:

    • Make it char(1) [Y/N or T/F], or number(1) [1/0]

  • Date & Time:

    • Date: Date field with certain format

    • Timestamp: Date plus time with a certain format

  • customer (name: varchar2(100), DoB: Date, Phone: Char(10), …)


Dropping altering tables

Dropping & Altering Tables

  • Dropping (Deleting) a table

    • DROP TABLE <tableName>;

  • Altering a table to add a column

    • ALTER TABLE <tableName> ADD <col> <type>;

    • The initial value of the column is Null

  • Altering a table to drop a column

    • ALTER TABLE <tableName> DROP COLUMN <col>;

    • Some DBMSs do not support dropping columns


Integrity constraints ics

Integrity Constraints (ICs)

  • IC: condition that must be Truefor any instance of the database

    • ICs are specified when schema is defined

    • ICs are checked when relations are modified

  • A legalinstance of a relation is one that satisfies all specified ICs.

    • DBMS should not allow illegal instances.

  • Examples:

    • Student ID must be unique

    • The combination of first + middle + last names must be unique

    • The grade must be one of ( A, B, C, D)

    • The gender must be (M, F)

    • Account balance must be >= 0


  • Ic key constraints

    IC: Key Constraints

    • Two types of key constraints can be defined

      • Primary Key

      • Unique(The other candidate keys)

    • Each relation may have only one primary key but, possibly many unique keys

    • Primary key attributes do not accept Null, Unique attributes accept Null

    • Create “Students” relation

    • Create “Courses” relation

    CREATE TABLE Students

    (sid: CHAR(20) Primary Key,

    name: CHAR(20),

    login: CHAR(10) Unique,

    age: INTEGER,

    gpa: REAL);

    CREATE TABLE Courses

    (cid: Varchar2(20) Primary Key,

    name: varchar2(50),

    maxCredits : integer,

    graduateFlag: char(1));


    Ic key constraints cont d

    IC: Key Constraints (Cont’d)

    • Another way to define Keys

    CREATE TABLE Students

    (sid: CHAR(20),

    name: CHAR(20),

    login: CHAR(10),

    age: INTEGER,

    gpa: REAL,

    Constraint pk_sid Primary Key (sid),

    Constraint u_loginUnique (login));

    • Create “Students” relation

    CREATE TABLE Enrolled

    (sid: CHAR(20),

    cid: Varchar(20),

    enrollDate: date,

    grade: CHAR(2),

    Constraint pk_cols Primary Key (sid, cid, enrollDate));

    • Create “Enrolled” relation


    Violations of key constraints

    Violations of Key Constraints

    • DBMS prevents this from happening

    • Example

      customer (name: varchar2(50), Phone: Char(10), address: varchar2(100));

      (“Mike”, “1765432123”, “NY”); Inserted

      (“Mike”, “1999887665”, “CA”); Raises an error (duplication)

      (null , “1999887665”, “CA”); Raises an error (null value)


    Violation of key constraints

    Violation of key constraints

    • Primary key violation if:

      • There is a row with null values for any attribute of primary key, OR

      • Two rows with same values for all attributes of primary key

    • Unique key violation if:

      • Two rows have the same non-null values in the unique attributes

      • Null values are not equal to each other

    • DBMS will prevent this from happening


    Adding dropping constraints

    Adding/Dropping Constraints

    • Can be done after creating tables

    • Adding a constraint

      • ALTER TABLE <tableName> ADD CONSTRAINT <cName> <constraint type> …

    • Dropping a constraint

      • ALTER TABLE <tableName> DROP CONSTRAINT <cName>


    Constraints limits the data

    Constraints Limits the Data

    • Create “Enrolled” relation

    • Create “Enrolled” relation

    CREATE TABLE Enrolled

    (sid: CHAR(20),

    cid: Varchar2(20),

    enrollDate: date,

    grade: CHAR(2),

    Constraint pk Primary Key (sid, cid));

    CREATE TABLE Enrolled

    (sid: CHAR(20),

    cid: Varchar2(20),

    enrollDate: date,

    grade: CHAR(2),

    Constraint pk Primary Key (sid, cid, enrollDate));

    A student can take a course only once.

    A student can take a course many times on different dates.


    Not null default constraint types

    Not Null & Default Constraint Types

    • Create “Students” relation

    • Create “Courses” relation

    CREATE TABLE Students

    (sid: CHAR(20),

    name: CHAR(20) NOT NULL,

    login: CHAR(10),

    age: INTEGER,

    gpa: REAL Default 0,

    Constraint pk Primary Key (sid),

    Constraint u1 Unique (login));

    CREATE TABLE Courses

    (cid: Varchar2(20),

    name: varchar2(50),

    maxCredits : integer,

    graduateFlag: char(1) Default ‘Y’);

    NOT NULL & DEFAULT constraints


    Domain values constraint

    Domain Values Constraint

    • Create “Enrolled” relation

    CREATE TABLE Enrolled

    (sid: CHAR(20),

    cid: Varchar2(20),

    enrollDate: date,

    grade: CHAR(2),

    Constraint pk Primary Key (sid, cid, enrollDate),

    Constraint gradeVal check (grade in (‘A+’, ‘A-’, ‘B+’, ‘B-’)) );

    Domain constraint


    Ic foreign key constraints referential integrity

    IC: Foreign Key Constraints(Referential Integrity )

    • Create “Students” relation

    • Create “Courses” relation

    CREATE TABLE Students

    (sid: CHAR(20),

    name: CHAR(20),

    login: CHAR(10),

    age: INTEGER,

    gpa: REAL);

    CREATE TABLE Courses

    (cid: Varchar2(20),

    name: varchar2(50),

    maxCredits : integer,

    graduateFlag: char(1));

    Foreign key

    Foreign key

    • Create “Enrolled” relation

    CREATE TABLE Enrolled

    (sid: CHAR(20),

    cid: Varchar2(20),

    enrollDate: date,

    grade: CHAR(2));


    Foreign keys referential integrity

    Enrolled (referencing relation)

    Students (referenced relation)

    Primary Key

    Foreign Key

    Foreign Keys, Referential Integrity

    • Foreign key : Set of fields in one relation that "refer" to a tuple in another relation (like a pointer)

    • Foreign key :

      • FK in referencingrelation must match PK of referenced relation.

      • Match = same number of columns, compatible data types (column names can be different)

      • The relationship is many-to-one from the “referencing” to the “referenced”


    Foreign keys in sql

    Foreign Keys in SQL

    • Create “Students” relation

    • Create “Courses” relation

    CREATE TABLE Students

    (sid: CHAR(20) Primary Key,

    name: CHAR(20),

    login: CHAR(10),

    age: INTEGER,

    gpa: REAL);

    CREATE TABLE Courses

    (cid: Varchar2(20) Primary Key,

    name: varchar2(50),

    maxCredits : integer,

    graduateFlag: char(1));

    • Create “Enrolled” relation

    CREATE TABLE Enrolled

    (sid: CHAR(20) Foreign Key References Students (sid),

    cid: Varchar2(20),

    enrollDate: date,

    grade: CHAR(2),

    Constraint fk_cidForeign Key (cid) References Courses (cid));

    sid & cid have to be already defined as primary keys

    Alter Table Enrolled Add Constraints fk_cidForeign Key cid References Courses(cid));


    Violation of foreign key constraints

    Enrolled (referencing relation)

    Students (referenced relation)

    Primary Key

    Foreign Key

    Violation of Foreign Key Constraints

    • DBMS will prevent foreign key values that does not exist in the primary key column(s)

    Will be rejected


    Enforcing referential integrity

    Enforcing Referential Integrity

    • Deletion: What if an Enrolled tuple is deleted?

      • No problem. It is allowed


    Enforcing referential integrity cont d

    Enforcing Referential Integrity (Cont’d)

    • Deletion: What if a Student tuple is deleted?

      • Cascading -- Also delete all Enrolled tuples that refer to it

      • No Action -- Disallow deletion of a Students tuple since there are dependent tuples

      • Set Default -- Set sid in Enrolled tuples that refer to it to a default sid

      • Set Null -- Set sid in Enrolled tuples that refer to it to a special value null, denoting `unknown’ (Not always applicable)

    • Same rules apply when updating Students.sid

    The two most common actions are: Cascading or No Action


    Referential integrity in sql

    Referential Integrity in SQL

    • SQL/99 supports all 4 options on deletes & updates:

    CREATE TABLE Enrolled

    (sid: CHAR(20) Foreign Key References Students (sid),

    cid: Varchar2(20),

    enrollDate: date,

    grade: CHAR(2),

    Constraints fk_cidForeign Key cid References Courses (cid)

    ON DELETE CASCADE

    ON UPDATE NO ACTION);


    Cyclic dependencies

    Cyclic Dependencies

    • What if cyclic dependencies between two tables exists?

      • Table R has a foreign key to Table S (Say R.a references S.b)

      • Table S has a foreign key to Table R (Say S.c references R.d)

    • At insertion time:

      • When insert into R  will be rejected because S.b does not yet exits

      • When insert into S  will be rejected because R.d does not yet exits

    • Solution (Many) – One option:

      • Disable the constraint on one table, say S

      • Insert the tuple into S, then insert into R

      • Enable the constraint on S again

    • Another solution

      • Group the insertions in one transaction

        • Begin Transaction

          • <the inserts>

        • End Transaction

    Alter Table S Disable Constraint <name>;

    Alter Table S Enable Constraint <name>;

    Constraints are checked at the end of the transaction


    Where do ics come from

    Where do ICs Come From?

    • ICs are based upon semantics of real-world enterprise being described in database relations.

    • We can check a database instance to see if an IC is violated ?

      • In typical cases, DBMS should make sure all constraints are enforced

    • Given an instance, can we tell what are the ICs?

      • No, NEVER !

      • An IC is a statement about all possible instances!

      • They need to be checked from the schema definition not from a given instance


    Relational model summary

    Relational Model: Summary

    • Structure

      • Relations (Tables)

      • Attributes (Columns, Fields)

    • Constraints + Constraint Enforcement

      • Domain, Default, and Not Null Constraint

      • Key Constraint

        • Primary key, candidate key (unique)

      • Foreign Key Constraint

    • SQL DDL commands


    Inserting updating deleting data

    Inserting, Updating, Deleting Data

    • This is performed using Data Manipulation Language of SQL (DML)

    • Insertion

      • Insert into Students values (“1111”, …);

    • Deletion

      • Delete from Students;

      • Delete from Students Where sid = “1111”;

    • Update

      • Update Students Set GPA = GPA + 0.4;

      • Update Students Set GPA = GPA + 0.4 Where sid = “1111”;


  • Login