Database Systems ( 資料庫系統 )

Database Systems(資料庫系統) October 4, 2005 Lecture #3

Course Administration • Please download the updated HW #1 • This lecture: • R&G Chapter 3 • Next week reading: • R&G Chapter 4.1 ~ 4.2, 5

Ubicomp Project of the Week:I/O Brush (Ryokai, MIT Media Lab) • Remove the boundary between digital & physical world • Digital Painting with Physical World Ink

Relational Model Chapter 3

Lecture Outline • Relational Model • Definitions: schema, instance, tuple, field, domain, etc. • Basic SQL Commands (Data Definition Language) • Integration Constraints • ER-Diagram to Relational Tables

Relational Model • Mostly widely used model • Vendors: Oracle, Microsoft, IBM (DB2), Sybase, … • Simple • A relational database is a collection of relations. • Each relation is a table with rows and columns. • Why do people like it? • Simple tabular data representation, easy to understand. • Ease of expressing complex query (using SQL) on the data • Efficient query evaluation (using query optimization)

Example of a Relation • This is a “Students relation”. • A relation has two parts: • Schema defines column heads of the table. • Instance contains the data rows (called tuples or records) of the table.

Relational Schema (1) • A relational schema specifies: • name of the relation: Students • names of fields: (sid, name, login, age, gpa) • domain of each field: it is type of possible values (some of built-in types in SQL are integer, real, string, and date.) • A field can also be called an attribute or a column. Students

Relational Schema (2) • We can refer to the field by • Field name (more common): the order of fields does not matter. • Position of the field (less common): the order of fields matters. • A relational schema can be written using the following notation: • relation-name (field-name-1: domain-name-1, field-name-2: domain-name-2, …, field-name-n: domain-name-n) • Example: Students(sid: string, name: string, login: string, age: integer, gpa: real)

Relational Instance • A relation instance contains a set of tuples • A relation instance is referred to as a relation. • A tuple can also be called a record or a row. • A relation instance is a set, and it hasno duplicate tuples. • Order of tuples is not important.

Degree and Cardinality • Degree is the number of fields in schema (=5 in the table below) • Cardinality is the number of tuples in relation (=3 in the table below)

Domain Constraints • Values in the tuples’ fields must satisfy the specified domain in the schema. • Similar to type matching in compilation • Example of domain constraint violation: • Schema: Students(sid: string, name: string, login: string, age: integer, gpa: real) • A tuple: <sid: ‘50000’, name: 38, login: ‘dave@cs’, age: 18.5, gpa: 3.3> • There are other types of integrity constraints: • Key constraints, foreign key constraints, and general constraints.

Outline on SQL Basics • History • Basic commands • Integrity constraints

SQL History • It is a query language for relational databases. • Developed by IBM (system R) in the 1970s • Need for a standard since it is used by many database vendors. • Two standard organizations • ANSI (American National Standard Institutes) • ISO (International Organization for Standardization) • Standards: • SQL-86, SQL-89, SQL-92, SQL-99 (current standard)

SQL Basic Commands • create table: create a table • drop table: delete a table • alter table: alter a field in a table • insert: add a tuple • delete: delete a tuple • update: change field values in a tuple

SQL: create table create table Students(sid char(20), name char(20), login char(10), age integer, gpa real) • Use built-in types: integer, real, char(#) • Similar to type definition in programming language • You can define your own types (describe in CH5)

SQL: delete table drop table Students

SQL: alter table • Add a new field in a table alter table Students add dept char[20] • Delete a field in a table alter table Students drop gpa

SQL: insert insert into Students (sid, name, login, age, gpa) values (‘53688’, ‘Smith’, ‘smith@cs’, 18, 3.2) or you can omit the fields insert into Students values (‘53688’, ‘Smith’, ‘smith@cs’, 18, 3.2)

SQL: delete delete from Students as S where S.name = ‘Smith’ or you can omit as and the tuple variable S delete from Students where name = ‘Smith’

SQL: update update Students S set S.age = S.age + 1, S.gpa = S.gpa – 1 where S.sid = 53688 19 2.2

Integrity Constraints (IC) • IC: condition that must be true for any instance of the database; e.g., domain constraints. • Why ICs? • Prevent data entry errors or command errors. • ICs are specified when schema is defined, (create table) • ICs are checked when relations are modified (add, remove, and update). • A legal instance of a relation is one that satisfies all specified ICs. • Should not allow illegal instances.

Types of Integrity Constraints • Domain constraint • Key constraint • Foreign key constraint (referential integrity) • Other constraints

Key Constraint • A key is a set of minimal fields that can uniquely identify a tuple in a relation. 1. No two distinct tuples can have same values in all key fields, and 2. It is minimal, (no subset of the key is another key). • Part 2 false? A superkey. • If #key >1, one of the keys is chosen (by DBA) to be the primary key. • Why is this a constraint? • When a table is modified (e.g., by adding a new tuple), DBMS checks to make sure that the specified keys remain valid keys (e.g., the new tuple has a unique key value).

Examples of Keys • Valid keys: {sid}, {name, age} and {login} • Invalid keys: {name} and {age} • Valid Superkeys: {sid, gpa}, {sid, age}, and {sid, name, login, age, gpa)

Primary and Candidate Keys • A relation can have many possible keys, called candidate keys. But only one is chosen as the primary key. • DBMS may create an index on primary key to optimize tuple lookup (using primary key). • Specify keys in SQL: create table Students (sid char(20), name char(20), login char(10), age integer, gpa real, unique (name, age), constraint StudentsKey primary key (sid)) constraint name

Invalid studid Foreign Key Constraint • Specify constraint: • Only the Students listed in the Students relation can enroll for courses. • Fields [studid] in one relation refer to some fields [sid] in another relation • Why is it a constraint? • Delete a tuple from Students? • A tuple in Enrolled relation becomes invalid. Why? They are related Enrolled Relation Students Relation

Foreign Key (Definition) • Studid is called a foreign key. • A foreign key is like a “pointer” in C, referencing a unique tuple / field in the referenced relation. • A foreign key constraint makes sure that there is no dangling pointer. Primary key Foreign key Students Relation Enrolled Relation

More on Foreign Key • The foreign key must refer to primary key in the referenced relation • Why? • Must be able to uniquely identify the tuple in the referenced relation. • The foreign key needs not be a candidate key. • Why? • Only used to uniquely identify a tuple in the referenced relation. • If all foreign key constraints are enforced, referential integrity is achieved. • A data model w/o referential integrity? • Bad links in HTML

Primary key Foreign key sid name login age gpa cid grade studid 53666 Jones Jones@cs 18 3.3 Database141 B 53666 53688 Smith Smith@cs 18 3.2 Topology112 A 53650 53650 Smith Smith@math 19 3.7 Students Relation Enrolled Relation Specify Foreign Keys in SQL • Constraint: only students listed in the Students relation should be allowed to enroll for courses. create table Enrolled (studid char(20), cid char(20), grade char(20), primary key (studid, cid), foreign key (studid) references Students)

54321 Foreign Key Constraint Violations • When can they happen? Delete? insert? update? Primary key Foreign key insert

Self-Referral Foreign Key • A foreign key can refer to the same relation. • Example: each student could have a partner. • If a student hasn’t found a partner, the value can be set to null. • It is ok to have null value in foreign key field. • Is it okay to have null value in primary key field? Primary key Foreign key

General Constraints • An example : students ages must be over 16 years old. create table Students ( sid char(20), name char(20), login char(10), age integer, gpa real, unique (name, age), constraint StudentsKey primary key (sid), check (age > 16) )

More on General Constraints • Two types • Table constraint: associate with a single table • Assertions: associate with multiple tables • if 1/3 of courses taken by a student has a grade = F in the Enrolled relation, the status of the student in the Students relation must be set to “In Trouble”.

Enforcing Referential Integrity • What should be done if an Enrolled tuple with a non-existent student id is inserted? • Reject it! • What should be done if a Students tuple is deleted while leaving a dangling enrolled tuple? • Option 1: Also delete all Enrolled tuples that refer to it • Option 2: Disallow deletion • Option 3: Set studid in Enrolled tuples that refer to it to a default sid. • Option 4: Set studid in Enrolled tuples that refer to it to NULL. Primary key Foreign key sid name login age gpa cid grade studid 53666 Jones Jones@cs 18 3.3 Database141 B 53666 53688 Smith Smith@cs 18 3.2 Topology112 A 53650 53650 Smith Smith@math 19 3.7 Enrolled Relation Students Relation

Option 1: CASCADE (also delete all tuples that refer to deleted tuple) Option 2: Default is NO ACTION (delete/update is rejected) Options ¾: SET NULL / SET DEFAULT (sets foreign key value of referencing tuple) CREATE TABLE Enrolled (studid CHAR(20) default “00000”, cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE SET DEFAULT ) Referential Integrity in SQL

Translate ER Model to Relational Model • An entity set to table(s) • A relationship set without constraints to table(s) • A relationship set with only key constraints to table(s) • A relationship set with participation constraints to table(s) • A weak entity set to table(s) • ISA hierarchies to table(s) • Aggregates to table(s)

name ssn lot Employees Entity Sets to Tables key attribute attributes CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn))

Relationship Sets (without Constraints) to Tables descriptive attribute since CREATE TABLE Works_In( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) name ssn Works_In Employees dname budget did Departments

Relationship Sets to Tables CREATE TABLE Works_In( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) • Fields (attributes) of a table must include: • All descriptive attributes. • Keys for each participating entity set (as foreign keys).

since dname Employees Review: ER Key Constraints • Describe at most once (entitity) relationship • Manages relationship: each department has at most one manager (okay to have none). • One department can appear at most once in Manages relationship set, also calledone-to-many relation. name did budget ssn Departments Manages Finance Mary 3/3/93 Accounting Joe 2/2/92 Research Alice 3/1/92 Legal Peter

name did budget ssn since dname Departments Manages Employees Relationship Sets (with key Constraints) to Table • Map a relationship set to a table: • Note that did is the key now! Why? • Since each department has a unique manager, we could instead combine Manages and Departments. • Second approach: • Map Manages into the Departments table. CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), // can be null -> at most one since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees) CREATE TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)

since since name name dname dname ssn did did budget budget lot Departments Employees Manages Review: Participation Constraints • Describe all (entitity)participation relationship • Must every department have a manager? • If yes, this is a participation constraint • All Departments entities must participate in the Manages relationship set (total participation).

since since name name dname dname ssn did did budget budget lot Departments Employees Manages Participation Constraints to Table CREATE TABLE Dept_Mgr( did INTEGER; dname CHAR(20), budget REAL, ssn CHAR(11) NOT NULL, // must have one! since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees)

(Alicia) (2) name cost pname age ssn (Hao) Policy Dependents Employees Review: Weak Entities • A weak entitycan be identified uniquely only by considering the key of another (owner) entity. • Pname = partial key • Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities). • Weak entity set must have total participation in this identifying relationship set.

Weak Entity Sets to Table • Weak entity set and identifying relationship set are translated into a single table. • When the owner entity is deleted, all owned weak entities must also be deleted. CREATE TABLE Dependent_Policy ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE) name pname cost age ssn Policy Dependents Employees

Database Systems ( 資料庫系統 )