Database Management Systems

1. Database Management Systems

2. What is a DBMS? • Database management systems: • Provide efficient(speed and space) and secure access to large amount of data. • Address problems such as: • How to store the data efficiently • How to query data efficiently • How to update the data reliably and securely (by multiple users) • Contrast with using file systems for the same task

3. Student Course Term Charles SYSC3001 Fall, 2011 Dan SYSC4602 Summer, 2010 … … … Relational Databases • Based on the relational model • Separates the logical view from the physical view of the data.

4. Querying a Database • Find all the students who have taken SYSC3001 in Fall 2011. • S(tructured) Q(uery) L(anguage) • select E.name • from Enroll E • whereE.course=SYSC3001 and • E.term=“Fall_2011” • Query processor figures out how to answer the query efficiently.

5. Database Industry • Relational databases are a great success of theoretical ideas. • “Big 3” DBMS companies are among the largest software companies in the world. • IBM (with DB2) and Microsoft (SQL Server, Microsoft Access) are also important players. • $20B industry (several years old) • Challenged by object-oriented DBMS.

6. Why Use a DBMS? • Data independence and efficient access • Reduced application development time • Data integrity and security • Uniform data administration • Concurrent access and recovery from crashes

7. Functionalities of a DBMS • Storage management • Abstract data model • High level query and data manipulation language • Efficient query processing • Transaction (concurrency) processing • Resiliency: recovery from crashes • Interface with programming languages

8. The Study of DBMS • Some aspects: • Modeling and design of databases • Database programming: querying and update operations • Database implementation • DBMS study cuts across many fields of Computer Science and Engineering: OS, languages, software engineering, AI, Logic, multimedia, theory, ...

9. Database Modeling and Design • Why do we need it? • Agree on structure of the database before deciding on a particular implementation. • Consider issues such as: • What entities to model? • How entities are related? • What constraints exist in the domain? • How to achieve good design? • Performance, memory space, reliability, and security

10. Database Design Formalisms • Entity/Relationship model (E/R): • More relational in nature • Conceptually similar to OO analysis and design • Can be translated (semi-automatically) to relational schemas (with varying amount of pain). • New comers: UML and XML

11. Entity / Relationship Diagrams Objects entities Classes entity sets Attributes are the names of roles played by some domain (a set of atomic values)in a relation (a table of values or file of records). Relationships are associations among entities. Product address buys

12. name category name price makes Company Product stockprice buys employs Person name ssn address

13. Multi-way Relationships How do we model a purchase relationship between buyers, products and stores? Product Purchase Store Person

14. Roles in Relationships What if we need an entity set twice in one relationship? Product Purchase Store buyer salesperson Person

15. Attributes on Relationships date Product Purchase Store Person

16. The Relational Data Model Database Model (E/R, UML) Relational Schema Physical storage Complex file organization and index structures. Tables: row names: attributes rows: tuples Diagrams (E/R, UML)

17. Attribute names Name Price Category Manufacturer iPhone $459.99 phone Apple Vista $299.99 OS MS SingleTouch $149.99 photography Canon MultiTouch $203.99 household Hitachi tuples Terminology Product

18. Every attribute has an atomic type. Relation Schema: relation name + attribute names + attribute types Relation instance: a set of tuples. Only one copy of any tuple Database Schema: a set of relation schemas. Database instance: a relation instance for every relation in the schema. More Terminology

19. Formally, a mapping from attribute names to values: name iPhone price $449.99 category phone manufacturer Apple Sometimes we refer to a tuple by itself: (note order of attributes) (iPhone, $449.99, phone, Apple) or Product (iPhone, $449.99, phone, Apple). More on Tuples

20. Updates • The database maintains a current database state. • Updates to the data: • 1) add a tuple • 2) delete a tuple • 3) modify an attribute in a tuple • Updates to the data happen very frequently. • Updates to the schema: relatively rare. Rather painful. • Need good DB design • Speed and space (security)

21. From E/R Diagrams to Relational Schema - relationships are already independent entities - only atomic types exist in the E/R model. Entity setsrelations Relationshipsrelations Special care for weak entity sets – existence depends on existence of another entity. Example: Dependent of Employee .

22. name category name price makes Company Product Stock price buys employs Person name ssn address

23. Entity Sets to Relations name category price Product Product: Name Category Price iPhone phone $450

24. Relationships to Relations Start Year name category name makes Company Product Stock price Relation MAKES (watch out for attribute name conflicts) Product-name Product-Category Company-name Starting-year iPhone phone Apple2010

25. Mapping an UML Object Model to a Database UML object models can be mapped to relational databases: Some degradation occurs because all UML constructs must be mapped to a single relational database construct - the table Mapping of classes and attributes Each class is mapped to a table Each attribute is mapped onto a column in the table An instance of a class represents a row in the table Methods are not mapped.

26. Mapping a Class to a Table User table id:long firstName:text[25] login:text[8] email:text[32] User +firstName:String +login:String +email:String +id:long

27. Primary and Foreign Keys Any set of attributes that could be used to uniquely identify any data record in a relational table is called a candidatekey The actual candidate key that is used in the application to identify the records is called the primary key The primary key of a table is a set of attributes whose values uniquely identify the data records in the table A foreign key is an attribute (or a set of attributes) that references the primary key of another table.

28. Example for Primary and Foreign Keys Primary key fi r stName login email “alice” “am384” “am384@mail.org” “john” “js289” “john@mail.de” “bob” “bd” “bobd@mail.ch” Candidate key Candidate key name login “tictactoeNovice” “am384” “tictactoeExpert” “bd” “chessNovice” “js289” Foreign key referencing User table User tab le League table

29. Buried Association Associations with multiplicity “one” can be implemented using a foreign key LeagueOwner table League table id:long ... id:long ... o wner:long For one-to-many associations we add the foreign key to the table representing the class on the “many” end 1 * LeagueOwner League owner

30. Another Example for Buried Association Transaction Table Portfolio Table transactionID portfolioID portfolioID ... Portfolio portfolioID ... Transaction transactionID * Foreign Key

31. Mapping Many-To-Many Associations City cityName Airport airportCode airportName * Serves * Primary Key Primary Key Airport Table Serves Table City Table airportCode IAH HOU ALB MUC HAM airportCode IAH HOU ALB MUC HAM cityName Houston Houston Albany Munich Hamburg airportName Intercontinental Hobby Albany County Munich Airport Hamburg Airport cityName Houston Albany Munich Hamburg In this case we need a separate table for the association Separate table for the association “Serves”

32. Another Many-to-Many Association Mapping Tournament table Player table TournamentPlayerAssociation table id name ... id name ... 23 no vice 56 alice tournament player 24 e xper t 79 john 23 56 23 79 We need the Tournament/Player association as a separate table * * Tournament Player

33. Problems in Designing Schema Title ISBN Publisher Phone Address OS 1234-390-231 Wiley 312-1234567 87 1st Ave, NY, … DB 3234-390-241 Wiley 312-1234567 87 1st Ave, NY, … SE 5234-390-281 Wiley 312-1234567 87 1st Ave, NY, … …. Problems: - redundancy - update anomalies - deletion anomalies

34. Relation Decomposition Break the relation into two relations: Book Title ISBN Author Publisher OS 1234-390-231 xxx Wiley DB 3234-390-241 yyy Wiley SE 5234-390-281 aaa Wiley …. Publisher Name Phone Number Address Wiley (201) 555-1234 87 1st Ave, NY, … Wiley (201) 555-1234 87 1st Ave, NY, … McGraw (320) 234-9876 87 1st Ave, NY, … McGraw (320) 234-9876 87 1st Ave, NY, …

35. Anomalies • The updated programs will not operate correctly. • Examples: EMP_DEPT relation EName SIN BDate ADDR DnumberDnameDMgrSIN • Insertion anomalies: It is difficult to insert a new department that has no employees as yet in the EMP_DEPT relation. • Deletion anomalies: If we delete from the EMP_DEPT an employee tuple that happens to represent the last employee working for a particular department, the information concerning that department is lost from the database. • Update anomalies: In EMP_DEPT relation, if we want to change the value of one of the attributes of a particular department, say the manager of department 5, we must update the tuples of all employees who work in that department; otherwise, the database will become inconsistent.

36. Decompositions in General Let R be a relation with attributes A1, A2, …, An Create two relations R1 and R2 with attributes B , B , … B C , C , … C 1 2 m 1 2 l Such that:  = B , B , … B C , C , … C A , A , … A 1 2 m 1 2 l 1 2 n And -- R1 is the projection of R on -- R2 is the projection of R on B , B , … B 1 2 m C , C , … C 1 2 l

37. Boyce-Codd Normal Form A simple condition for removing anomalies from relations: A relation R is in BCNF if and only if: Whenever there is a nontrivial dependency A1, A2, …, An for R , it is the case that {A1, A2, …, An} a super-key for R. B 1 In English (though a bit vague): Whenever a set of attributes of R is determining another attribute, it should determine all the attributes of R.

38. Example Title ISBN Publisher Author Phone Addr OS 0-471-20284-3 Wiley xxx (201) 555-1234 1234 1st DB 0-471-20282-3 Wiley yyy (206) 572-4312 1234 1st SE 0-471-20267-8 Wiley aaa (201) 555-1234 1234 1st Netw. 0-471-20267-8 Wiley bbb (201) 555-1234 1234 1st What are the dependencies? What are the keys? Is it in BCNF?

39. And Now? Title ISBN Publisher Author OS 0-471-20284-3 Wiley xxx DB 0-471-20282-3 Wiley yyy SE 0-471-20267-8 Wiley aaaa Netw. 0-471-20267-8 Wiley bbb Publisher Phone Addr Wiley 555-1234 1234 1st St. …… McGraw 234-9876 9876 5th Ave. ….

40. More Examples • EMP_DEPT: ENAME SIN BDATE ADDR DNUM DNAME DMGRSIN • What’s wrong? • How to decompose? Functional dependency. • Decompose EMP_DEPT into: • EMP ENAME SIN BDATE ADDR DNUM • DEPT DNUM DNAME DMGRSIN

41. More Examples (cont’d) • Example: • EMP_PROJ SIN PNUMBER HOURS ENAME PNAME PLOCATOIN Can be decomposed into • EP1 SIN PNUMBER HOURS • EP2 SIN ENAME • EP3 PNUMBER PNAME PLOCATOIN

42. More Examples (cont’d) • EMP ENAME Proj_NAMEDep_NAME Smith X john Smith y anna Smith x anna Smith y john Brown w jim Brown x jim Brown y jim Brown z jim Brown w Joan Brown x joan Brown y joan Brown z joan Brown w bob Brown x bob Brown y bob Brown z bob Decompose EMP into:

43. More Examples (cont’d) • EMP_PROJECTS ENAME Proj_NAME Smith x Smith y Brown w Brown x Brown y Brown z • EMP_DEPENDENTS ENAME Dep_NAME Smith anna Smith john Brown jim Brown joan Brown bob

44. SQL Introduction Standard language for querying and manipulating data Structured Query Language Many standards out there: SQL92, SQL2, SQL3. Vendors support various subsets of these, but all of what we’ll be talking about. Basic form: (many many more bells and whistles in addition) Select attributes From relations (possibly multiple, joined) Where conditions (selections)

45. SQL Examples • Employee (FNAME, LNAME, SSN,BDATE, ADDR, SALARY, SUPERSSN, DNO) • Department (DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) • Research, 5, 333445555, 22-May-78 • Administration, 4, 987654321, 1-Jan-85 • Headquarters, 1, 888665555, 19-Jun-71 • Q1: Find John Smith’s birthday and address: • Q2: Find the salary of all employees: • Q3: Find all the attributes of all employees who work for department 5 • Q4: Find all employees who work for the Research department • Q5: For each employee, retrieve the employee’s first and last name, and the • first and last name of all employees who work in the same department. • Q6: For each employee, retrieve the employee’s first and last name, and the • first name and last name of his/her supervisor.

46. SQL Examples - 1 • Employee (FNAME, LNAME, SSN,BDATE, ADDR, SALARY, SUPERSSN, DNO) • Q1: Find John Smith’s birthday and address: • SELECT BDATE, ADDRESS • FROM EMPLOYEE • WHERE FNAME = ‘John’ AND LNAME = ‘Smith’ • Q2: Find the salary of all employees: • SELECT SALARY • FROM EMPLOYEE • Q3: Find all the attributes of all employees who work for department 5 • SELECT * • FROM EMPLOYEE • WHERE DNO = 5

47. SQL Examples - 2 • Employee (FNAME, LNAME, SSN,BDATE, ADDR, SALARY, SUPERSSN, DNO) • Department (DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) • Research, 5, 333445555, 22-May-78 • Administration, 4, 987654321, 1-Jan-85 • Headquarters, 1, 888665555, 19-Jun-71 • Q4: Find all employees who work for the Research department • SELECT FNAME, LNAME, ADDRESS • FROM EMPLOYEE, DEPARTMENT • WHERE DNAME = ‘Research’ AND DNUMBER = DNO

48. SQL Examples - 3 • Employee (FNAME, LNAME, SSN,BDATE, ADDR, SALARY, SUPERSSN, DNO) • Department (DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) • Q5: For each employee, retrieve the employee’s first and last name, and the • first and last name of all employees who work in the same department. • SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME • FROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.DNO = S.DNO • Q6: For each employee, retrieve the employee’s first and last name, and the • first name and last name of his/her supervisor. • SELECT E.FNAME, E. LNAME, S.FNAME, S.LNAME • FROM EMPLOYEE AS E, EMPLOYEE AS S • WHERE E.SUPERSSN=S.SSN

49. Selections SELECT * FROM Company WHERE country=“USA” AND stockPrice > 50 You can use: attribute names of the relation(s) used in the FROM. comparison operators: =, <>, <, >, <=, >= apply arithmetic operations: stockprice*2 operations on strings (e.g., “||” for concatenation). lexicographic order on strings. pattern matching: s LIKE p special stuff for comparing dates and times.

50. Projections Select only a subset of the attributes SELECT name, stock price FROM Company WHERE country=“USA” AND stockPrice > 50 Rename the attributes in the resulting table SELECT name AS company, stockprice AS price FROM Company WHERE country=“USA” AND stockPrice > 50