1 / 43

Database Systems

Database Systems. Lecture/Workshop: Mon 1-3 LIB 1316; Wed 11 – 12, LH2 Lab: Thu 10-12, ACC, additional help: Mon or Tue? Judy Cushing - judyc@evergreen.edu Office Hours: Thursdays 1-2pm, Lab I, 1003, 867-6652

jackie
Download Presentation

Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Systems • Lecture/Workshop: Mon 1-3 LIB 1316; Wed 11 – 12, LH2 • Lab: Thu 10-12, ACC, additional help: Mon or Tue? • Judy Cushing - judyc@evergreen.edu • Office Hours: Thursdays 1-2pm, Lab I, 1003, 867-6652 • Class web page: off the SOS web page, or academic.evergreen.edu/r/ricdan16/SOSdatabase/databaseHome.html • Assignments due most Wednesdays • Project Parts due Mondays or Thursdays • Lab Aids: Dan Rice, Jeff Jones Judy Cushing, Art Keller, Jeff Ullman

  2. Textbooks Required: • Database Systems: The Complete Book, by Garcia-Molina, Ullman, and Widom (first edition), Prentice Hall, 2002. Recommended: (one of these) • SQL in a Nutshell : A Desktop Quick Reference, Kevin E. Kline, Daniel Kline, O’Reilly. • A Guide to the SQL Standard. C. J. Date and H. Darwen, Addison-Wesley, 1999. It is more succinct but perhaps a more useful summary, than the Melton-Simon book. • SQL3 Complete, A Guide to the SQL Standard: A User's Guide to the Standard Database Language SQL, (fourth edition), by C.J. Date and Hugh Darwen, Addison-Wesley, 2000. • SQL: 1999 - Understanding Relational Language Components, (first edition), by Melton and Simon, Morgan Kaufmann, 2002. If you are using PostgreSQL: Books on Unix, Perl, PHP, and CGI, PostgreSQL: Introduction and Concepts, Bruce Momjian, Addison-Wesley, 2001. This might also be on the web. Judy Cushing, Art Keller, Jeff Ullman

  3. Evaluations I will evaluate you on your ability to design and implement a database application, and on your understanding of database concepts. I will consider: • Assignments and in class participation: about 30%. • Project: about 35%. • Exams: about 35%. Judy Cushing, Art Keller, Jeff Ullman

  4. Project • Build an application using a relational database system (SQL Server or PostgreSQL) accessed via Java and the web. • The project has 8 parts (due Mondays or Thursdays), starting with design and ending with a complete application. • The early programming assignments should be written in SQL, Java. • Some students found it helpful to switch to JavaScript, HTML (PHP or Perl if Postgres) for the web-accessible part. Judy Cushing, Art Keller, Jeff Ullman

  5. Project - tentative schedule • Project Part 1 due Jan. 14 (Mon) Choose a project and design an E/R diagram. • Project Part 2 due Jan. 24 (TH) Relational design. • Project Part 3 due Jan. 31 (TH) Create database in SQLServer. • Project Part 4 due Feb. 7 (TH) Queries, updates, and indexes. • Project Part 5 due Feb. 21 (TH) Embedded SQL. • Project Part 6 due Feb. 28 (TH) Views, constraints, and triggers. • Project Part 7 due Mar. 6 (Wed) Entire database application, in Java. • Project Part 8 due Mar. 11 (Mon) Database application, accessed via the web. Judy Cushing, Art Keller, Jeff Ullman

  6. Typical Warning(for those taking this class elsewhere) • The database class is a lot of work. • But it is worth it. • Of all courses you take, it may be the one that gets you a job…. We know that SOS gets people jobs, but database expertise does help…. Data is key to many applications…. Judy Cushing, Art Keller, Jeff Ullman

  7. Schedule Date Topic Reading Week 1 Intro, E/R model Weak enitity sets, E/R design 1, 2.1, 2.3 2.2, 2.4 Week 2 Relational model, Functional Dependencies Normal forms, Multivalued dependencies 3.1-3.5 3.6-3.7 • Today: Jan. 7 • Intro, Entity-Relationship Model. • Read Chapter 1 and Sections 2.1, 2.3. • Jan. 9 (W) • Weak Entity Sets, Entity-Relationship Design. • Read Sections 2.2, 2.4. • Jan. 14 (M) • Relational Model, Functional Dependencies. • Read Sections 3.1-3.5. Project 1 due. • Jan. 16 (W) • Normal Forms, Multivalued Dependencies. • Read Sections 3.6-3.7. Assignment 1 due. Judy Cushing, Art Keller, Jeff Ullman

  8. Syllabus • The background and history of database management systems. • The fundamentals of using a database management systems. • Industry standards used for database management systems. • Theoretical background of the relational model. • Queries and Updates. • Logic databases? Constraints and Triggers. • Transactions and Security. • Object-oriented, object-relational, semi-structured and XML database systems. • Mediation and warehousing. Judy Cushing, Art Keller, Jeff Ullman

  9. What is a Database Management System? 1. Manages very large amounts of data. 2. Supports efficient access to very large amounts of data. 3. Supports concurrent access to very large amounts of data. • Example: bank and its ATM machines. 4. Supports secure, atomic access to very large amounts of data. • Contrast two people editing the same UNIX file – last to write “wins” – with the problem if two people deduct money from the same account via ATM machines at the same time – new balance is wrong whichever writes last. Judy Cushing, Art Keller, Jeff Ullman

  10. Relational Model • Based on tables, as: acct # name balance 12345 Sally 1000.21 34567 Sue 285.48 … … … • Today used in most DBMS's. Judy Cushing, Art Keller, Jeff Ullman

  11. The DBMS Marketplace • Relational DBMS companies – Oracle, Sybase – are among the largest software companies in the world. • IBM offers its relational DB2 system. With IMS, a nonrelational system, IBM is by some accounts the largest DBMS vendor in the world. • Microsoft offers SQL-Server, plus Microsoft Access for the cheap DBMS on the desktop, answered by “lite” systems from other competitors. • Relational companies also challenged by “object-oriented DB” companies, and XML data stores. • But countered with “object-relational” systems, which retain the relational core while allowing type extension as in OO systems. Judy Cushing, Art Keller, Jeff Ullman

  12. Three Aspects to Studying DBMS's 1. Modeling and design of databases. • Allows exploration of issues before committing to an implementation. 2. Programming: queries and DB operations like update. • SQL = “intergalactic dataspeak.” • DBMS implementation. Consider DBMS system components, p. 11. Judy Cushing, Art Keller, Jeff Ullman

  13. Query Languages Employee Department SELECT ManagerFROM Employee, DepartmentWHERE Employee.name = "Clark Kent” AND Employee.Dept = Department.Dept Query Language Data definition language (DDL) ~ like type defs in C or Pascal Data Manipulation Language (DML) Query (SELECT) UPDATE < relation name > SET <attribute> = < new-value> WHERE <condition> Name Dept Dept Manager SQL Judy Cushing, Art Keller, Jeff Ullman

  14. Host Languages C, C++, Fortran, Lisp, COBOL Application prog. Host language is completely general (Turing complete) but gives you no support Query language—less general "non procedural" and optimizable DBMS Calls to DB Local Vars (Memory) (Storage) Judy Cushing, Art Keller, Jeff Ullman

  15. Relational model is good for: Large amounts of data —> simple operations Navigate among small number of relations Difficult Applications for relational model: • VLSI Design (CAD in general) • CASE • Graphical Data ALU ADDER CPU A FA Adder ALU ADDER Bill of Materials or transitive closure Judy Cushing, Art Keller, Jeff Ullman

  16. Where number of "relations" is large, relationships are complex • Object Data Model • Logic Data Model OBJECT DATA MODEL 1. Complex Objects – Nested Structure (pointers or references) 2. Encapsulation, set of Methods/Access functions 3. Object Identity 4. Inheritance – Defining new classes like old classes Object model: usually find objects via explicit navigation Also query language in some systems Judy Cushing, Art Keller, Jeff Ullman

  17. LOGIC (Horn Clause) DATA MODEL • Prolog, Datalog if A1 and A2 then B prolog B:- A1 and A2 Functions s(5) = 6 (successor) Predicates with Arguments sum(X,Y,Z) X + Y = Z sum(X,0,X) means X + 0 = X (always true for all X) sum(X,s(Y),s(Z)):-sum(X,Y,Z) means X+(Y+1) = (Z+1) if X + Y = Z More power than relational Can Compute Transitive Closure edge(X,Y) path(X,Y) :- edge(X,Y) path(X,Z) :- path(X,Y) & edge(Y,Z) Judy Cushing, Art Keller, Jeff Ullman

  18. 60’s 70's 80's 90’s now Hierarchical Network Relational Choice for most new applications Object Bases Knowledge Bases Multi-tiered (web) Architectures XML Data Transfers and Transformations Judy Cushing, Art Keller, Jeff Ullman

  19. Entity/Relationship Model Diagrams to represent designs. • Entity like object = “thing.” • Entity set like class = set of “similar” entities/objects. • Attribute = property of entities in an entity set, similar to fields of a struct. • In diagrams, entity set  rectangle;attribute  oval. name phone ID Students height Judy Cushing, Art Keller, Jeff Ullman

  20. Relationships • Connect two or more entity sets. • Represented by diamonds. Taking Students Courses Judy Cushing, Art Keller, Jeff Ullman

  21. Relationship Set Think of the “value” of a relationship set as a table. • One column for each of the connected entity sets. • One row for each list of entities, one from each set, that are connected by the relationship. Students Courses Sally CS180 Sally CS111 Joe CS180 … … Judy Cushing, Art Keller, Jeff Ullman

  22. Multiway Relationships Usually binary relationships (connecting two E.S.) suffice. • However, there are some cases where three or more E.S. must be connected by one relationship. • Example: relationship among students, courses, Lab Aids. Possibly, this E/R diagram is OK: Taking Student Course Assisting Lab Aid Judy Cushing, Art Keller, Jeff Ullman

  23. Works in CS180, because each Lab Aid works for all students. Connection student-LabAid is only via the course. • But what if students were divided into sections, each headed by a LabAid? • Then, a student in CS180 would be related to only one of the LabAids for CS180. Which one? • Need a 3-way relationship to tell. Judy Cushing, Art Keller, Jeff Ullman

  24. Course Student Course LabAid Ann CS180 Jan Sue CS180 Pat Bob CS180 Jan … … … Enrolls Student LabAid Judy Cushing, Art Keller, Jeff Ullman

  25. Beers-Bars-Drinkers Example • Our running example for the course. name addr license Frequents Serves Bar Likes Beer Drinker name manf name addr Judy Cushing, Art Keller, Jeff Ullman

  26. Multiplicity of Relationships Representation of Many-One • E/R: arrow pointing to “one.” • Rounded arrow = “exactly one.” Many-many Many-one One-one Judy Cushing, Art Keller, Jeff Ullman

  27. Example:Drinkers Have Favorite Beers name addr license Frequents Serves Bar Likes Beer Drinker Favorite name manf name addr Judy Cushing, Art Keller, Jeff Ullman

  28. One-One Relationships Put arrows in both directions. Design Issue: Is the rounded arrow justified? Design Issue: Here, manufacturer is an E.S. In earlier diagrams it is an attribute. Which is right? Best-seller Manufacturer Beer Judy Cushing, Art Keller, Jeff Ullman

  29. Attributes on Relationships price • Shorthand for 3-way relationship: Sells Bar Beer price Price Sells Bar Beer Judy Cushing, Art Keller, Jeff Ullman

  30. A true 3-way relationship. • Price depends jointly on beer and bar. • Notice arrow convention for multiway relationships: “all other E.S. determine one of these.” • Not sufficiently general to express any possibility. • However, if price, say, depended only on the beer, then we could use two 2-way relationships: price-beer and beer-bar. • Or better: just make price an attribute of beer. Judy Cushing, Art Keller, Jeff Ullman

  31. Converting Multiway to 2-Way • Baroque in E/R, but necessary in certain “object-oriented” models. • Create a new connecting E.S. to represent rows of a relationship set. • E.g., (Joe's Bar, Bud, $2.50) for the Sells relationship. • Many-one relationships from the connecting E.S. to the others. BBP The-Bar The-Beer The-Price Bar Beer Beer Judy Cushing, Art Keller, Jeff Ullman

  32. Roles Sometimes an E.S. participates more than once in a relationship. • Label edges with roles to distinguish. Husband Wife d1d2 d3 d4 … … Married husband wife Drinker Judy Cushing, Art Keller, Jeff Ullman

  33. Buddy1 Buddy2 d1d2 d1 d3 d2d1 d2 d4 … … Buddies • Notice Buddies is symmetric, Married not. • No way to say “symmetric” in E/R. Design Question Should we replace husband and wife by one relationship spouse? 1 2 Drinker Judy Cushing, Art Keller, Jeff Ullman

  34. More Design Issues 1. Subclasses. 2. Keys. 3. Weak entity sets. (Next class.) Judy Cushing, Art Keller, Jeff Ullman

  35. Subclasses Subclass = special case = fewer entities = more properties. • Example: Ales are a kind of beer. In addition to the properties (= attributes and relationships) of beers, there is a “color” attribute for ales. Judy Cushing, Art Keller, Jeff Ullman

  36. E/R Subclasses • Assume subclasses form a tree (no multiple inheritance). • isa triangles indicate the subclass relation. Beer name manf isa Ale color Judy Cushing, Art Keller, Jeff Ullman

  37. Different Subclass Viewpoints 1. E/R viewpoint: An entity has a component in each entity set to which it logically belongs. • Its properties are the union of the properties of these E.S. 2. Contrasts with object-oriented viewpoint: An object (entity) belongs to exactly one class. • It inherits propertiesof its superclasses. Beer name manf isa Pete’s Ale Ale color Judy Cushing, Art Keller, Jeff Ullman

  38. Multiple Inheritance Theoretically, an E.S. could be a subclass of several other entity sets. name manf name manf Beers Wines isa isa Grape Beers Judy Cushing, Art Keller, Jeff Ullman

  39. Problems How should conflicts be resolved? • Example: manf means vintner for wines, bottler for beers. What does manf mean for “grape beers”? • Need ad-hoc notation to resolve meanings. • In practice, we shall assume a tree of entity sets connected by isa, with all “isas” pointing from child to parent. Judy Cushing, Art Keller, Jeff Ullman

  40. Keys A key is a set of attributes whose values can belong to at most one entity. • In E/R model, every E.S. must have a key. • It could have more than one key, but one set of attributes is the “designated” key. • In E/R diagrams, you should underline all attributes of the designated key. Judy Cushing, Art Keller, Jeff Ullman

  41. Example • Suppose name is key for Beers. • Beer name is also key for ales. • In general, key at root is key for all. Beers name manf isa Ales color Judy Cushing, Art Keller, Jeff Ullman

  42. Example: A Multiattribute Key • Possibly, the combination of hours + room also forms a key, but we have not designated it as such. number hours Courses dept room Judy Cushing, Art Keller, Jeff Ullman

  43. In Class exercise (if time)or Assignment for Wednesday(for those who did SOS this Fall) Convert the UML for EU-Bid and EU-Lease to an ER diagram. Merge them into a design for one database. Judy Cushing, Art Keller, Jeff Ullman

More Related