Relational Database

1. Relational Database

2. Relational Database • Relational database:a set of relations • Relation: made up of 2 parts: • Schema :specifiesthename of relations, plus name and type of each column. • E.G. Students (sid: string, name: string, login: string, age: integer, gpa: real). • Instance : a table, with rows and columns. #Rows = cardinality, #fields = degree / arity. • Can think of a relation as a setof rows or tuples (i.e., all rows are distinct).

3. Example Instance of Students Relation • Cardinality = 3, degree = 5, all rows are distinct.

4. Integrity Constraints (ICs) • IC: condition that must be true for any instance of the database. • ICs are specified when schemas are 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. • If the DBMS checks ICs, stored data is more faithful to real-world meaning. • Avoids data entry errors, too!

5. Two Common Types of ICs • Primary Key Constraints • Referential Integrity Constraint

6. Primary Key • Key: minimal set of the fields of a relation that can uniquely identify a tuple • E.g., {SSN} is a key • If there’s more than one key for a relation, one of the keys is chosen (by DBA) to be the primary key. The other keys are called candidate keys. • Superkey: set of the fields of a relation that can uniquely identify a tuple • If {Au, Av,.., Az} is a superkey, thenfor any two distinct tuples t1 and t2 of a relation R, t1[Au, Av,.., Az] != t2[Au, Av,…, Az] • E.g., {SSN, Name, Age} is a superkey.

7. Foreign Key • Foreign key (FK) : Set of fields in one relation that is used to `refer’ to a tuple in another relation. (Must correspond to primary key of the second relation.) • FK is Like a `logical pointer’. EMPLOYEE NAME SSN BDATE ADDRESS SALARY SUPERSSN DNO Employee: referencing relation Department: referenced relation DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE • DNO is a foreign key of EMPLOYEE.

8. Referential Integrity A set of attributes FK in relation schema R1, is a foreign key of R1, if it satisfies the following two rules: • Dom(FK) = Dom(PK) where PK is a set of primary key attributes of some relation R2. • Given a tuple t1R1, either • t1[FK]=null or such that t1[FK]=t2[PK]

9. The SQL Query Language • Data Definition Language (DDL) • enables creation, deletion, and modification of definitions for tables and views and integrity constraint specification. • Data Manipulation Language (DML) • allows users to query, to insert, or to delete rows.

10. Creating Relations in SQL • Creates the Students relation. Observe that the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified. • As another example, the Enrolled table holds information about courses that students take. CREATE TABLE Students (sidCHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpaREAL primary key (sid)) CREATE TABLE Enrolled (sidCHAR(20), cidCHAR(20), grade CHAR(2))

11. Primary and Candidate Keys in SQL • Possibly many candidate keys(specified using UNIQUE), one of which is chosen as the primary key. CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid) ) • “A student can take a same course only once.” • “Students can take only one course, and 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) )

12. Enrolled Students Foreign Keys in SQL • Only students listed in the 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) REFERENCESStudents )

13. Enforcing Referential Integrity • Consider Students and Enrolled; sid in Enrolled is a foreign key that references Students. • 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? • Also delete all Enrolled tuples that refer to it. • Disallow deletion of a Students tuple that is referred to. • Set sid in Enrolled tuples that refer to it to a default sid. • (In SQL, also: Set sid in Enrolled tuples that refer to it to a special value null, denoting `unknown’ or `inapplicable’.) • Similar if primary key of Students tuple is updated.

14. Referential Integrity in SQL/92 CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCESStudents ON DELETE CASCADE) • SQL/92 supports all 4 options on deletes and updates. • Default is NO ACTION (delete/update is rejected) • CASCADE (also delete all tuples that refer to the 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) REFERENCESStudents ON DELETE SET NULL)

15. Destroying and Altering Relations DROP TABLE Students • Destroys the relation Students. The schema information and the tuples are deleted. ALTER TABLE Students ADD firstYear integer • The schema of Students is altered by adding a new field; every tuple in the current instance is extended with a NULL value in the new field. ALTER TABLE Students dropcolumnfirstYear

16. Adding and Deleting Tuples • Insert a single tuple: INSERT INTO Students (sid, name, login, age, gpa) VALUES ('53688', 'Smith', 'smith@ee', 18, 3.2) • Can delete all tuples satisfying some conditions (e.g., name = Smith): DELETE FROM Students S WHERE S.name = 'Smith'

17. name ssn lot Employees Logical DB Design: ER to Relational • Entity sets to tables. CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) For each regular entity set E in the ER diagram, create a relation R that includes all the simple attributes of E. Include only the simple component attributes of a composite attribute. Choose one of the key attributes of E as the primary key of R. If the chosen key is composite, the simple attributes that form the key are taken together as the primary key of R.

18. since name dname ssn lot Employees Works_in Relationship Sets to Tables did budget Departments • In translating a relationship set to a relation, attributes of the relation must include: • Keys for each participating entity set (as foreign keys). • This set of attributes forms a key for the relation. • All descriptive attributes. 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)

19. since name dname ssn lot Employees Manages did budget Departments Translating ER Diagrams with Key Constraints • Map relationship to a table: • Note that did is the key now! • Separate tables for Employees and Departments. CREATE TABLE Manages( ssnCHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)

20. since name dname ssn lot Employees Manages did budget Departments Since each department has a unique manager, we could instead combine Manages and Departments. CREATE TABLE Dept ( did INTEGER, dname CHAR(20), budget REAL, mgrssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees)

21. since name dname ssn lot Employees Manages did budget Departments Translating ER Diagrams with Total Participation and Key Constraints CREATE TABLE Dept ( did INTEGER, dname CHAR(20), budget REAL, mgrssn CHAR(11), NOT NULL since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees)

22. name cost pname age ssn lot Policy Dependents Employees Translating Weak Entity Sets • 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 Dep_Policy ( ssn CHAR(11) NOT NULL, pname CHAR(20), age INTEGER, cost REAL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE)

23. Translating ISA Hierarchies to Relations • General approach: • 3 relations: Employees, Hourly_Emps and Contract_Emps. • Employee(ssn,name,lot); • Hourly_Emps (ssn,hourly_wages, hours_worked) • Contract_Emps(ssn,contractid) • Must delete Hourly_Emps tuple if referenced Employees tuple is deleted). • Queries involving all employees are easy. • Queries involving just Hourly_Emps require a join to get some attributes. • Alternative: Just Hourly_Emps and Contract_Emps. • Hourly_Emps (ssn, name, lot, hourly_wages, hours_worked) • Contract_Emps (ssn,name,lot,contractid) • Each employee must be in one of these two subclasses.