COP4710 Database Systems

1. COP4710Database Systems Relational Model Fall 2013

2. ER Model vs. Relational Model • Both are used to model data • ER model has many concepts • Entities, relationships, attributes, etc. • Well-suited for capturing the app. requirements • Not well-suited for computer implementation • Relational model • Has just a single concept: relation • World is represented with a collection of tables • Well-suited for efficient manipulations on computers

3. Behind the Scene: It’s All About Modeling • 1973 Charles W. Bachman • For his outstanding contributions to database technology • 1981 Edgar F. Codd • For his fundamental and continuing contributions to the theory and practice of database management systems • 1998 James Gray • For seminal contributions to database and transaction processing research and technical leadership in system implementation • And we certainly need more!

4. Relational Model • The Relational Model • A very simple database model • Used by ALL major commercial database systems, spawned many million-dollar industry • Efficient implementations • Query with high-level languages: simple yet expressive • Database = set of named relations (or tables) • Each relation has a set of named attributes (or columns) • Each tuple (or row) has a value for each attribute • Each attribute has a type (or domain)

5. Relation: An Example Name of Table (Relation) Column (Field, Attribute) Products Domain (Atomic type) Row (Record, Tuple)

6. Relations • Schema vs. instance = columns vs. rows • Schema: structural description of relations • Relation name • Attribute names • Attribute types (domains) • Schema of a database • A set of relation schemas • Questions • When do you determine a schema (instance)? • How often do you change your mind?

7. Relations • The database maintains a current database state • Updates to the data happen very frequently • add a tuple • delete a tuple • modify an attribute in a tuple • Updates to the schema are relatively rare, and rather painful. Why?

8. Defining a Database Schema • A database schema comprises declarations for the relations (“tables”) of the database • Simplest form of creation is: CREATE TABLE <name> ( <list of elements> ); • And you may remove a relation from the database schema by: DROP TABLE <name>;

9. Elements of Table Declarations • The principal element is a pair consisting of an attribute and a type • The most common types are: • INT or INTEGER (synonyms) • REAL or FLOAT (synonyms) • CHAR(n ) = fixed-length string of ncharacters • VARCHAR(n ) = variable-length string of up to ncharacters

10. Example: Create Table CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL );

11. Dates and Times • DATE and TIME are types in SQL • The form of a date value is DATE‘yyyy-mm-dd’ • Example: DATE ‘2002-09-30’ for Sept. 30, 2002 • The form of a time value is TIME‘hh:mm:ss’ with an optional decimal point and fractions of a second following • Example: TIME ‘15:30:02.5’ = two and a half seconds after 3:30PM

12. Declaring Keys • An attribute or list of attributes may be declared PRIMARY KEY or UNIQUE • whose value(s) is unique in each tuple • Each says the attribute(s) so declared functionally determines all the attributes of the relation schema • Single attribute keys CREATE TABLE Beers ( name CHAR(20) UNIQUE, manf CHAR(20) );

13. Multi-attribute Keys CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL, PRIMARY KEY (bar, beer) );

14. PRIMARY KEY vs. UNIQUE • Standard SQL requires these distinctions • There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes • No attribute of a PRIMARY KEY can ever be NULLin any tuple. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL • SQL standard also allows DBMS implementers to make their own distinctions between PRIMARY KEY and UNIQUE • Example: some DBMS might automatically create an index (data structure to speed search) in response to PRIMARY KEY, but not UNIQUE

15. Other Declarations for Attributes • Two other declarations we can make for an attribute are: • NOT NULL means that the value for this attribute may never be NULL • DEFAULT <value> says that if there is no specific value known for this attribute’s component in some tuple, use the stated <value> CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY, addrCHAR(50) DEFAULT ‘123 Monroe St.’, phone CHAR(16) );

16. Example for NULL and DEFAULT • Suppose we insert the fact that Sally is a drinker, but we know neither her address nor her phone • An INSERT with a partial list of attributes makes the insertion possible: INSERT INTO Drinkers(name) VALUES(‘Sally’); • If we had declared phone NOT NULL, this insertion would have been rejected

17. Foreign Keys • A Foreign Key is a field whose values are keys in another relation • Must correspond to primary key of the second relation • Like a `logical pointer’ Enrolled Students CREATE TABLE Enrolled ( sidCHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCESStudents, FOREIGN KEY (cid) REFERENCESCourses )

18. Referential Integrity • Consider relations 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 to NULL • Similar if primary key of Students tuple is updated

19. Adding Attributes • We may change a relation schema by adding a new attribute (“column”) by: ALTER TABLE <name> ADD <attribute declaration>; • Example: ALTER TABLE Bars ADD phone CHAR(16)DEFAULT ‘unlisted’;

20. Deleting Attributes • Remove an attribute from a relation schema by: ALTER TABLE <name> DROP<attribute>; • Example: we don’t really need the license attribute for bars: ALTER TABLE Bars DROP license;

21. Translating ER Diagram to Rel. Design • Basic cases • entity set E = relation with attributes of E • relationshipR = relation with attributes being keys of related entity sets + attributes of R • Special cases • combining two relations • translating weak entity sets • translating is-a relationships and subclasses

22. name ssn lot Employees Entity Set to Relation CREATE TABLE Employees ( ssnCHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY(ssn) )

23. Relationship Set to Relation • In translating a many-to-many 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 superkey for the relation • All descriptive attributes CREATE TABLE Works_In( ssnCHAR(1), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCESEmployees, FOREIGN KEY (did) REFERENCES Departments )

24. since name dname ssn lot Employees Manages Translating ER Diagrams with Key Constraints budget did Departments Translation to relational model? 1-to-1 1-to-Many Many-to-1 Many-to-Many

25. Translating ER Diagrams with Key Constraints • Map relationship set to a (virtual) Manages table: • Note that did is the key now! • Separate tables for Employees and Departments • Since each department has a unique manager, we could instead combineManages and Departments CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssnCHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees ) CREATE TABLE Manages( ssnCHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCESEmployees, FOREIGN KEY (did) REFERENCES Departments )

26. Participation Constraints • The participation of Departments in Manages is said to be total (vs. partial): • Every did value in Departmentsmust appear in a row of the Manages table (with a non-null ssnvalue) since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since

27. Participation Constraints CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION )

28. Translating Weak Entity Sets • A weak entity can be identified uniquely only by considering the primary key of another (owner) entity • Owner entity set and weak entity set must participate in a one-to-many relationship set (1 owner, many weak entities) • Weak entity set must have total participation in this identifying relationship set name cost pname age ssn lot Policy Dependents Employees

29. 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 ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE)

30. Translating ISA Hierarchies to Relations • Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed) • Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no) name ssn lot Employees hours_worked hourly_wages ISA contractid Contract_Emps Hourly_Emps

31. Translating ISA Hierarchies to Relations • General approach • 3 relations: Employees, Hourly_Emps and Contract_Emps • Hourly_Emps: Every employee is recorded in Employees. For hourly emps, extra info recorded in Hourly_Emps (hourly_wages, hours_worked, ssn) • must delete Hourly_Emps tuple if referenced Employees tuple is deleted • Queries involving all employees easy, those 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 • Each employee must be in one of these two subclasses

32. E2 Summary: ER Diagram to Relations Relational schema e.g.account=(bname, acct_no, bal) E/R diagram E = ( a1, …, an ) E a1 ….. an R1 E1 R1= ( a1, b1, c1, …, ck) a1 …. an c1 …. ck b1 …. bm

33. E2 Summary: ER Diagram to Relations • Could have : R1= ( a1, b1, c1, …, ck) • Put b1 as the key for R1, it is also the key for E2=(b1, …., bn) • Usual strategy(combination) • ignore R1 • Add a1, c1, …., ck to E2 instead, i.e. • E2=(b1, …., bn, a1, c1, …, ck) R1 E1 c1 …. ck a1 …. an b1 …. bm

34. E2 Summary: ER Diagram to Relations R1 ? ? E1 c1 …. ck a1 …. an b1 …. bm E2 = ( b1, …, bm ) E1 = ( a1, …, an ) R1 R1 = ( a1, b1, c1 …, ck ) E1 = ( a1, …, an ) R1 E2 = ( b1, …, bm , a1, c1, …, ck) E1 = ( a1, …, an , b1, c1, …, ck) R1 E2 = ( b1, …, bm ,) R1 Treat as n:1 or 1:m

35. Isa Summary: ER Diagram to Relations Weak Entity sets E1 = ( a1, …, an ) IR E2 E1 E2 = (a1, b1, …, bm ) a1 …. an b1 …. bm Sub-classes a1 … an Method 1: E = ( a1, …, an ) E1 S1 = (a1, b1, …, bm ) S2 = ( a1, c1 …, ck ) Method 2: S2 S1 S1 = (a1,…, an, b1, …, bm ) S2 = ( a1, …, an, c1 …, ck ) c1 …. ck b1 …. bm