The Relational Model

1. The Relational Model Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

2. 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

3. Design and Build Phases Phase 1 Phase 2 Phase 3 ER Model & ERD Build the database Relational Model

4. Relational Model • Structure: • Relations (also called Tables) • Attributes (also called Columns or Fields)

5. 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)

6. 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)

7. 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

8. 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

9. 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

10. 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)

11. To create these relations (tables) We use SQL

12. 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)

13. 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

14. 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

15. 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> (…) );

16. 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))

17. 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), …)

18. 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

19. 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

20. 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));

21. 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

22. 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)

23. 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

24. 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>

25. 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.

26. 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

27. 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

28. 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));

29. 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”

30. 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));

31. 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

32. Enforcing Referential Integrity • Deletion: What if an Enrolled tuple is deleted? • No problem. It is allowed

33. 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

34. 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);

35. 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

36. 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

37. 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

38. 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”;