Basic Definitions Relational Database Concepts Relational Database Management Systems

1. Oracle Programming Week 1 Review on the Relational Data Model Basic Definitions Relational Database Concepts Relational Database Management Systems Chapter 5 Elmasri & Navathe book

2. User2 User1 Database User4 User3 Basic Definitions • Data: Known facts that can be recorded and have an implicit meaning. • Database : A collection of related data • Database management system (DBMS): a collection of computer programs, which enables users to create and maintain databases

3. Basic Definitions (Cont.) • Schema: description of data at some level (e.g., tables, attributes, constraints, domains) • Model: tools and language for describing: • Conceptual schema • Data definition language (DDL) • Integrity constraints, domains (DDL) • Operations on data • Data manipulation language (DML) • Directives that influence the physical schema (affects performance, not semantics) • Storage definition language (SDL)

4. Relation • Relation is a set of tuples/records • Tuple ordering irrelevant • Cardinality of relation = number of tuples • All tuples in a relation have the same structure; constructed from the same set of attributes • Attributes named (=> ordering irrelevant) • Value of an attribute drawn from the attribute’s domain

5. General format of a relation when represented as a table Attribute or Column Name Attribute or Column Name Row ortuple values

6. Example

7. Relation as A Table (cont.) • Mathematical entity corresponding to a table • row ~ tuple • column ~ attribute • Values in a tuple are related to each other • John lives at 123 Main

8. Relational Database Management System (RDBMS) • Finite set of relations • Database schema = set of relations (and other things)

9. Database Schema (Example) • Student (Id: INT, Name: STRING, Address: STRING, Status: STRING) • Professor (Id: INT, Name: STRING, DeptId: DEPTS) • Course (DeptId: DEPTS, CrsName: STRING, CrsCode: COURSES) • Transcript (CrsCode: COURSES, StudId: INT, Grade: GRADES, Semester: SEMESTERS) • Department(DeptId: DEPTS, Name: STRING)

10. Three-schema Architecture • Internal Level  Internal Schema • Describes the physical storage structure of the database • Conceptual Level  Conceptual Schema • Describes the structure of the whole database for all users. It hides the storage details. • Concerned of entities (tables), data types, relationships, user operations and constraints. • External or View level  External Schema • Describes part of the database for a group of users.

11. Three-schema Architecture – Cont. External View External View DCL Conceptual Schema DDL, DML Internal Schema DSL Stored Database

12. Data Definition Language (DDL) • Used by DBAs or designers to define schema • A compiler compiles this language to construct the database and store its constraints in DBMS catalog • Examples of DDL Commands: • CREATE • ALTER • DROP • TRUNCATE (Deletes all records in a table)

13. Data Manipulation Language (DML) • Some of its commands: • SELECT (Retrieves data) • INSERT • UPDATE • DELETE

14. Data Control Language (DCL) • Used to create roles, permissions, and referential integrity and to control access on a database. • Example of its commands: • GRANT (Grants user privileges) • REVOKE (Withdraws privileges)

15. Other languages • Data Storage Language (DSL) • Specifies Internal Schema • Transactional Control Language (TCL) • Used to manage different transactions occurring within a database. • COMMIT (Saves your work) • ROLLBACK (Restore database to original state since the last COMMIT) • SAVE TRANSACTION (Sets a savepoint within a transaction)

16. Integrity Constraints • Part of schema • Restriction on state (or sequence of states) of database • Enforced by DBMS • Intra-relational - involve only one relation • Part of relation schema • e.g., all Ids are unique • Inter-relational - involve several relations • Part of relation schema or database schema

17. Database Integrity • Implies that the data held in the tables of the database is consistent in terms of the Relational Data Model • Two Types • Entity integrity (PK) • Referential Integrity (FK)

18. Relation Keys Constraints • Key Constraint: Values in a column (or columns) of a relation are unique: at most one row in a relation instance can contain a particular value(s) • What is a Key? • A minimal set of attributes satisfying key constraint

19. Key Constraint (con’t) • Every relation has a key • Candidate Key- • No two tuples of the relation will have identical entries in all attributes of the key. • The number of attributes that comprises the key must be minimal. • Primary Key-Since a table may have more than one candidate key, one should be designated as the primary key (PK) of the relation. Examples: • primary key (Id in Student) – • candidate key ((Name, Address) in Student)

20. Candidate Keys & Primary Keys • A RDBMS allows only one primary key per table. • Once a PK has been selected, any remaining candidate keys are called alternate keys. • A primary key may be composed of • a single attribute (single primary key) • E.g. ID • More than one attribute (composite primary key) • E.g. Code + Serial • An attribute that is a primary key can not have a null value • An attribute that is part of any key is called a prime attribute.

21. Example 1 Employee Table DepartmentTable

22. Example 2 Employee -ProjectTable

23. Candidate Keys & Primary Keys (Cont.) • Primary keys are • defined using Data Definition Language (DDL) • Automatically enforced by the RDBMS • Generally are defined at the time the tables are created. • When selecting primary keys, we need to choose attributes that satisfy the uniqueness and minimalist conditions for all permissible data.

24. NULL • What isNULL? • a NULL value is used to represent missing information, unknown, or inapplicable data. • A NULL value is not a zero value • A NULL value doesn’t represent a particular value within the computer.

25. Foreign Key Constraint • Referential integrity => Item named for primary key attribute/s in one relation must correspond to tuple(s) in another that describes the item • Employee (ProjId) references Project(ProjId) • Professor(DeptId) references Department(DeptId) • a1 is a foreign key of R1 referring to a2 in R2 => if v is a value of a1, there is a unique tuple of R2 in which a2 has value v • This is a special case of referential integrity: a2 must be a candidate key of R2 (DeptId is a key of Department) • If no row exists in R2 => violation of referential integrity • Not all rows of R2 need to be referenced: relationship is not symmetric • Value of a foreign key might not be specified (DeptId column of some professor might be null)

26. Foreign Key Constraint (Example) a2 v3 v5 v1 v6 v2 v7 v4 a1 v1 v2 v3 v4 -- v3 R1 R2 Foreign key Candidate/Primary key

27. 5.7

28. Foreign Key (con’t) • Names of a1 and a2 need not be the same. • With SQL tables: Foreign key <name> professor (DeptId) references Department (Dno) DeptId attribute of Professor Table references to Dno attribute in Department Table • R1 and R2 need not be distinct. • The attributes of where the referential integrity exists must have the same data type and length.

29. Foreign Key (con’t) • Foreign key might consist of several columns • (CrsCode, Semester) of Transcript references (CrsCode, Sem) of Teaching • R1(a1, …an) references R2(b1, …bn) • There exists a 1 - 1 relationship between a1,…an and b1,…bn • ai and bi have same domains (although not necessarily the same names) • For every tuple T in R1 over ai’s there exists a unique tuple S in R2 over bi’s, with T = S • b1,…bn is a candidate key of R2

30. Semantic Constraints • Domain, primary key, and foreign key are examples of structural (syntactic) constraints • Semantic constraints express rules of application: • e.g., number of registered students  maximum enrollment • SQL calls them Check constraint

31. SQL • Language for describing database schema and operations on tables • Data Definition Language (DDL): sublanguage of SQL for describing schema and constraints

32. Tables • SQL entity that corresponds to a relation • An element of the database schema • SQL is current standard • Database vendors generally deviate from standard

33. Table Creation • Steps in table creation: • Identify data types for attributes • Identify columns that can and cannot be null • Identify columns that must be unique (candidate keys) • Identify primary key-foreign key mates • Determine default values • Identify constraints on columns (domain specifications) • Create the table and associated indexes

34. Common SQL Data Types (from Oracle) • String types • CHAR(n) – fixed-length character data, n characters long Maximum length = 2000 bytes • VARCHAR2(n) – variable length character data, maximum 4000 bytes • LONG – variable-length character data, up to 4GB. Maximum 1 per table • Numeric types • NUMBER(p,q) – general purpose numeric data type • Numeric (p, q)- general purpose numeric data type • INTEGER(p) – signed integer, p digits wide • FLOAT(p) – floating point in scientific notation with p binary digits precision • Date/time type • DATE – fixed-length date/time in dd-mm-yy form

35. Table Declaration in SQL CREATE TABLE Student ( Id, numaric(5), Name CHAR(20), AddressVARCHAR(50), Status: Boolean, Constraint Pk_Id primary key(Id)); Id Name Address Status 101222333 John 10 Cedar St Freshman 234567890 Mary 22 Main St Sophomore Student

36. Primary, Unique, Check, Foreign, Null Keys Create table department ( Dno numeric(2), Dname varchar2(20), Loc varchar2(30) not null, Constraint Pk_Dno primary key(Dno) Constraint uk_name unique key(Dname)); Create table employee( Eno numeric(4), Ename varchar2(20), Salary numeric(7, 2) not null, Hire_Date Date not null, Extention char(4), DeptNo numeric(2), Constraint Pk_Eno primary key(Eno), Constraint Fk_Dno foreign key(DeptNo) reference department (Dno), Constraint ch_salary check (salary between 900 and 5000));

37. System Catalog • CREATE TABLE inserts information into the catalog • Catalog is another table that describes Objects created such as: • Table names • Constraint names • Role Names • Triggers, Sequences, Views, etc • Attribute names of different tables • Corresponding attribute types, etc. • Catalog schema is generally fixed by vendor • In Oracle SQL this catalog is called DICTIONARY

38. Circularity in Foreign Key Constraint a1 a2 a3 b1 b2 b3 x x y B A y candidate key : a1 foreign key : a3 references B(b1) candidate key : b1 foreign key : b3 references A(a1) Problem 1: Creation of A requires existence of B and vice versa Solution 1: CREATE TABLE A ( ……), (* no foreign key *) CREATE TABLE B ( ……), (* foreign key included *) ALTER TABLE A ADD CONSTRAINT cons FOREIGN KEY (a3) REFERENCES B (b1)

39. Circularity in Foreign Key Constraint • Problem 2 : Insertion of row in A requires prior existence of row in B and vice versa • Solution 2 : DEFERRED constraint check - insert both rows within a singletransaction CREATE TABLE Dept (….. DeptId: CHAR (4), MngrId : INTEGER, PRIMARY KEY DeptId, FOREIGN KEY (MngrId) REFERENCES Employee (Id) ) CREATE TABLE Employee ( …. DeptId : CHAR (4), Id : INTEGER, PRIMARY KEY Id, FOREIGN KEY (DeptId) REFERENCES Dept ( DeptId) )

40. Handling Foreign Key Violations/Anomalies Insert Anomaly: • Insertion into A: Reject if no row exists in Table B containing a primary key of the inserted row • Insertion into B: No problem A x B x

41. Handling Foreign Key Violations • Deletion from B: Reject if the to be removed is linked to other rows in A • Deletion from A: No problem Solution • SET NULL: Set value of foreign key in referencing row(s) in A to null • SET DEFAULT: Set value of foreign key in referencing row(s) in A to default value (y) • CASCADE: Delete referencing row(s) in A as well B A null B y A y

42. Handling Foreign Key Violations • Update a candidate/primary key in B: Reject if row exists in A containing for the old value • Update a foreign key in A (to z): Reject if no row exists in B containing the new value • Update a foreign key in A (to z): NO problem if a row in B exist, which contain the new updated value • Solution : • SET NULL: Set value of foreign key to null • SET DEFAULT: Set value of foreign key to default • CASCADE: Propagate z to foreign key

43. Specifying Actions CREATE TABLE Teaching ( ProfId INTEGER, CrsCode CHAR (6), Semester CHAR (6), PRIMARY KEY (CrsCode, Semester), FOREIGN KEY (ProfId) REFERENCES Professor (Id) ON DELETE CASCADE, FOREIGN KEY (CrsCode) REFERENCES Course (CrsCode) ON DELETE SET NULL ON UPDATE CASCADE )