1 / 36

The Relational Model

This comprehensive guide covers the relational model, SQL query languages, data types, constraints, keys, and more. Learn about the importance and structure of relations within a database.

gertrudebaker
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Relational Model CS3431

  2. Why Relational Model? • Currently the most widely used • Vendors: Oracle, Microsoft, IBM • Older models still used • IBM’s IMS (hierarchical model) • Recent competitions • Object Oriented Model: ObjectStore, Oracle? cs3431

  3. Relational Query Languages (SQL Standard) • Developed by IBM (system R) in the 1970s • Need for a standard since it is 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) cs3431

  4. Relational Model • Structures: • Relations (also called Tables) • Attributes (also called Columns or Fields) • Note: Every attribute is simple (not composite or multi-valued) • Instance : • a table with rows (tuples) and columns (attributes) • #Rows = cardinality, • #attributes = degree / arity. cs3431

  5. Relational Model • Eg: Student Relation • Are 2 relations equivalent? • Think of a relation as a setof tuples i.e., all rows are distinct (not required by commercial database) Student Student Cardinality = 2 Arity/Degree = 2 cs3431

  6. Relational Model • Schema for a relation • E.g.: Student (sNumber:INTEGER, sName:CHAR(20)) • Schema for a database • Schemas for all relations in the database • Tuples (Rows) • Set of rows in a relation are tuples of that relation • Note: Attribute values may be null cs3431

  7. Datatypes in SQL • INT (or) INTEGER • FLOAT (or) REAL • DECIMAL (n, m) • CHAR (n) • VARCHAR (n) • DATE, TIME cs3431

  8. SQL DDL • DDL = Data Definition Language • Create tables, columns of tables, types of columns, primary key constraints, unique constraints, foreign key constraints • Drop tables, add/drop columns, add/drop constraints – primary key, unique, foreign key cs3431

  9. Creating Tables CREATE TABLE <tableName> ( <col> <type>, <col> <type>, … <col> <type>, [CONSTRAINT <cName>] PRIMARY KEY (…), [CONSTRAINT <cName>] UNIQUE (…), [CONSTRAINT <cName>] FOREIGN KEY (…) REFERENCES <tableName> (…) ); cs3431

  10. DDL ---- Creating Relations • Creates Students relation. Observe that type (domain) of each field is specified, and enforced by DBMS whenever tuples are added or modified. • As another example, Enrolled table holds information about courses that students take. CREATE TABLE Students (sid: CHAR(20), name: CHAR(20), login: CHAR(10), age: INTEGER, gpa: REAL) CREATE TABLE Enrolled (sid: CHAR(20), cid: CHAR(20), grade: CHAR(2)) cs3431

  11. Dropping tables DROP TABLE <tableName> cs3431

  12. Adding/Dropping Columns ALTER TABLE <tableName> ADD <col> <type>; ALTER TABLE <tableName> DROP COLUMN <col>; cs3431

  13. Adding/Dropping Constraints ALTER TABLE <tableName> ADD [CONSTRAINT <cName>] … ALTER TABLE <tableName> DROP CONSTRAINT <cName> cs3431

  14. SQL DML: Basic • DML = Data Manipulation Language • Consists of Queries and Modification • Modification: Inserting/Deleting values from table • Insert a value into a table INSERT INTO <tableName> VALUES (…) eg: INSERT INTO Student VALUES (1, ‘Dave’); • Delete all values from a table DELETE FROM <tableName> cs3431

  15. Integrity Constraints (ICs) • IC: condition that must be true for 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. cs3431

  16. Constraints in Relational Model • Key constraints • Foreign Key constraints • (More constraints later) cs3431

  17. Key Constraints • A set of attributes is a key for a relation if: • Unique: No two distinct tuples can have the same values in all key fields • Minimal: A proper subset of the key attributes is not a key. INTUITION : Minimal subset of columns of the relation that uniquely identify the tuple. cs3431

  18. Keys: Example Student Is <sNumber> a key ? cs3431

  19. Primary Key Constraints • If multiple keys, one of them is chosen to be the Primary Key. • E.g.: PRIMARY KEY (Student) = <sNumber> • Restriction: • Primary key attributes cannot take null values cs3431

  20. Candidate Keys (SQL: Unique) • Keys that are not primary keys are Candidate Keys. • Specified in SQL using UNIQUE • E.g.: Student (sNumber, sName) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <sName> • Note: Attribute of unique key may have null values ! cs3431

  21. More on Key Constraints • Superkey K: • if values for K are sufficient to identify a unique tuple of relation r(R) • REMINDER: A set of attributes is a key if: • No two distinct tuples can have the same values in all key fields • A proper subset of the key attributes is not a key. • Superkey: • Contains some key in its attribute set • A proper subset of a superkey may be a superkey again ? cs3431

  22. Violation of key constraints • A relation violates a primary key constraint if: • There is a row with null values for any attribute of primary key, or, • There are 2 rows with same values for all attributes of primary key • A relation violates the unique constraint if: • 2 rows in R have the same non-null values for any unique attribute R.a cs3431

  23. Keys: Example Student Primary Key: <sNumber> Candidate key: <sName> Some superkeys: {<sNumber, address>, <sName>, <sNumber>, <sNumber, sName> <sNumber, sName, address>} cs3431

  24. Primary & Candidate Keys in SQL • Possibly many candidate keys(specified using UNIQUE), one chosen as primary key. CREATE TABLE Student (sid CHAR(20), ssn INTEGER, cid CHAR(20), PRIMARY KEY (sid,cid) UNIQUE (cid) cs3431

  25. Primary and Candidate Keys in SQL CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid) ) “For a given student and course, there is a single grade.” “Students can take only one course, and receive a single grade for that course; further, no two students in a course receive the same grade.” CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade) ) Used carelessly, an IC can prevent the storage of database instances that arise in practice! cs3431

  26. Enrolled (referencing relation) Students (referenced relation) Primary Key Foreign Key Foreign Keys, Referential Integrity • Foreign key : Set of fields in one relation that is used to "refer" to a tuple in another relation. • Like a `logical pointer’. • Foreign key : • FK in referencing relation must match PK of referenced relation. • Match = same number of columns, compatible data types (column names can be different). cs3431

  27. Foreign Keys in SQL • Only students listed in Students relation should be allowed to enroll for courses. CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCES Students (sid)) • If all foreign key constraints are enforced, referential integrity is achieved, i.e., no dangling references. cs3431

  28. Foreign Keys: More Examples Dept (dNumber, dName) Person (pNumber, pName, dept) PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (dept) REFERENCES Dept (dNumber) Persons working for Depts Person (pNumber, pName, father) PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (father) REFERENCES Person (pNumber) Person and his/her father cs3431

  29. Violation of Foreign Key Constraints • Suppose we have: • FOREIGN KEY R1 (S1) REFERENCES R2 (S2) • This constraint is violated if • Consider a row in R1 with non-null values for all attributes of S1 • If there is no row in R2 which have these values for S2, then the FK constraint is violated. cs3431

  30. Enforcing Referential Integrity • Consider Students and Enrolled; sid in Enrolled is a foreign key that references Students. • Insertion: What if a new Student tuple is inserted? • Insertion: What should be done if an Enrolled tuple with a non-existent student id is inserted? • Reject it Enrolled (referencing relation) Students (referenced relation) Primary Key Foreign Key cs3431

  31. Enforcing Referential Integrity Enrolled (referencing relation) Students (referenced relation) Deletion: What if an Enrolled tuple is deleted? Primary Key Foreign Key cs3431

  32. Enforcing Referential Integrity Enrolled (referencing relation) Students (referenced relation) Deletion: What if a Students tuple is deleted? • Cascading -- Also delete all Enrolled tuples that refer to it. • No Action -- Disallow deletion of a Students tuple that is referred to. • 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) Similar if primary key of Students tuple is updated. Primary Key Foreign Key cs3431

  33. Referential Integrity in SQL • SQL/99 supports all 4 options on deletes & updates: • Default is NO ACTION (delete/update is rejected) • CASCADE (also delete all tuples that refer to deleted tuple) • SET NULL / SET DEFAULT (sets foreign key value of referencing tuple) CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE SET DEFAULT ) cs3431

  34. Constraint Violation • What if cyclic dependencies between two tables? • Solution: • 1. Group both insertions into one trasaction • 2. Tell DBMS not to check the constraints until after the whole transaction has finished and is committed • How: • Follow declaration of constraint by “DEFERRABLE” • Plus “INITIALLY DEFERRED” • defer just before transaction commits • Or plus “INITIALLY IMMEDIATE” • check made immediately after each statement cs3431

  35. 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 ? • We can infer that an IC is true by looking at an instance ? • No, NEVER ! • An IC is a statement about all possible instances! • From example, we know name is not a key, but the assertion that sid is a key is given to us. cs3431

  36. Relational Model: Summary • Structures • Relations (Tables) • Attributes (Columns, Fields) • Constraints + Constraint Enforcement • Domain Constraint • Key • Primary key, candidate key (unique) • Super Key • Foreign Key cs3431

More Related