the relational model
Download
Skip this Video
Download Presentation
The Relational Model

Loading in 2 Seconds...

play fullscreen
1 / 38

The Relational Model - PowerPoint PPT Presentation


  • 130 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The Relational Model' - trynt


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)
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 (

,

,

,

[CONSTRAINT ] PRIMARY KEY (…),

[CONSTRAINT ] UNIQUE (…),

[CONSTRAINT ] FOREIGN KEY (…)

REFERENCES (…)

);

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 ;
  • Altering a table to add a column
    • ALTER TABLE ADD ;
    • The initial value of the column is Null
  • Altering a table to drop a column
    • ALTER TABLE DROP COLUMN ;
    • 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 ADD CONSTRAINT
  • Dropping a constraint
    • ALTER TABLE DROP CONSTRAINT
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
      • End Transaction

Alter Table S Disable Constraint ;

Alter Table S Enable Constraint ;

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”;
ad