Software engineering 180: Database Systems

Presentation Transcript

Software engineering 180: Database Systems Arthur M. Keller Email: ark@cse.ucsc.edu (most ideal approach to contact me) Office: Baskin Engineering 153a, (831) 459-1485 Office Hours: Tuesdays 4:30–5:30pm and by arrangement Class website page: http://www.cse.ucsc.edu/classes/cmps180 My site page: http://www.cse.ucsc.edu/~ark Assignments due most Tuesdays; Project Parts due most Thursdays T.A. – T.J. Steed Arthur Keller – CS 180

Textbooks Required: Database Systems: The Complete Book , by Garcia-Molina, Ullman, and Widom (first version), Prentice Hall, 2002. Suggested: (one of these) A Guide to the SQL Standard: A User's Guide to the Standard Database Language SQL , (fourth release), by C.J. Date and Hugh Darwen, Addison-Wesley, 2000. SQL: 1999 - Understanding Relational Language Components , (first release), by Melton and Simon, Morgan Kaufmann, 2002. PostgreSQL: Introduction and Concepts , Bruce Momjian, Addison-Wesley, 2001. You may likewise need: Books on Unix, Perl, PHP, and CGI. Arthur Keller – CS 180

Grading Assignments: 8 @ 2% each = 16% of review. Extend: 7 @ 5% each = 35% of review. Midterm: Feb. 14 (TH) in class. 14% of review. Last: Mar. 16 (Sa) 12–3PM in class. 35% of review. Arthur Keller – CS 180

Project You will assemble an application utilizing a social database framework (PostgreSQL) got to by means of the web. The venture has 7 sections (due most Thursdays), beginning with outline and winding up with an entire application. The early programming assignments ought to be composed in C, C++ or Java. A few understudies thought that it was useful to change to PHP or Perl for the last venture. Arthur Keller – CS 180

Warning This class is a great deal of work. Yet, it is justified, despite all the trouble. Of all courses you take at UCSC, this might be the one that lands you a position. Arthur Keller – CS 180

Schedule Today: Jan. 3 (TH) Intro, Entity-Relationship Model. Perused Chapter 1 and Sections 2.1-2.2. Jan. 8 (T) Weak Entity Sets, Entity-Relationship Design. Perused Sections 2.3-2.4. Jan. 10 (TH) Relational Model, Functional Dependencies. Perused Sections 3.1-3.5. Jan. 15 (T) Normal Forms, Multivalued Dependencies. Perused Sections 3.6-3.7. Task 1 due. Arthur Keller – CS 180

Syllabus The foundation and history of database administration frameworks. The essentials of utilizing a database administration frameworks. Industry measures utilized for database administration frameworks. Hypothetical foundation of the social model. Questions and Updates. Rationale databases. Exchanges and Security. Question arranged, protest social, semi-organized and XML database frameworks. Intervention and warehousing. Arthur Keller – CS 180

What is a Database Management System? 1. Oversees a lot of information. 2. Underpins productive access to a lot of information. 3. Underpins simultaneous access to a lot of information. Case: bank and its ATM machines. 4. Bolsters secure, nuclear access to a lot of information. Differentiate two individuals altering a similar UNIX record – last to compose "wins" – with the issue if two individuals deduct cash from a similar record by means of ATM machines in the meantime – new adjust isn't right whichever composes last. Arthur Keller – CS 180

Relational Model Based on tables, as: acct # name balance 12345 Sally 1000.21 34567 Sue 285.48 … … … Today utilized as a part of generally DBMS's. Arthur Keller – CS 180

The DBMS Marketplace Relational DBMS organizations – Oracle, Sybase – are among the biggest programming organizations on the planet. IBM offers its social DB2 framework. With IMS, a nonrelational framework, IBM is by a few records the biggest DBMS merchant on the planet. Microsoft offers SQL-Server, in addition to Microsoft Access for the shabby DBMS on the desktop, replied by "lite" frameworks from different contenders. Social organizations likewise tested by "protest arranged DB" organizations. Be that as it may, countered with "protest social" frameworks, which hold the social center while permitting sort expansion as in OO frameworks. Arthur Keller – CS 180

Three Aspects to Studying DBMS's 1. Demonstrating and outline of databases. Permits investigation of issues before focusing on a usage. 2. Programming: inquiries and DB operations like overhaul. SQL = "intergalactic dataspeak." 3. DBMS execution. CS180 = (1) + (2), while (3) is secured incompletely in CS277. Arthur Keller – CS 180

Query Languages Employee Department SELECT Manager FROM Employee, Department WHERE Employee.name = "Clark Kent" AND Employee.Dept = Department.Dept Query Language Data definition dialect (DDL) ~ like sort defs in C or Pascal Data Manipulation Language (DML) Query (SELECT) UPDATE < connection name > SET <attribute> = < new-value> WHERE <condition> Name Dept Manager SQL Arthur Keller – CS 180

Host Languages C, C++, Fortran, Lisp, COBOL Application prog. Have dialect is totally broad (Turing complete) but gives you no bolster Query dialect—less broad "non procedural" and optimizable DBMS Calls to DB Local Vars (Memory) (Storage) Arthur Keller – CS 180

Relational model is useful for: Large measures of information — > straightforward operations Navigate among little number of relations Difficult Applications for social model: VLSI Design (CAD as a rule) CASE • Graphical Data ALU ADDER CPU A FA Adder ALU ADDER Bill of Materials or transitive conclusion Arthur Keller – CS 180

Where number of "relations" is vast, connections are mind boggling Object Data Model Logic Data Model OBJECT DATA MODEL 1. Complex Objects – Nested Structure (pointers or references) 2. Encapsulation, arrangement of Methods/Access capacities 3. Object Identity 4. Inheritance – Defining new classes like old classes Object demonstrate: as a rule discover objects through unequivocal route Also inquiry dialect in a few frameworks Arthur Keller – CS 180

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) implies X + 0 = X (constantly valid 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 social Can Compute Transitive Closure edge(X,Y) path(X,Y) :- edge(X,Y) path(X,Z) :- path(X,Y) & edge(Y,Z) Arthur Keller – CS 180

60's 70's 80's 90's currently Hierarchical Network Relational Choice for most new applications Object Bases Knowledge Bases Arthur Keller – CS 180

Entity/Relationship Model Diagrams to speak to plans. Substance like question, = "thing." Entity set like class = set of "comparable" elements/objects. Trait = property of elements in a substance set, like fields of a struct. In graphs, element set  rectangle; characteristic  oval. name telephone ID Students stature Arthur Keller – CS 180

Relationships Connect at least two substance sets. Spoken to by jewels. Taking Students Courses Arthur Keller – CS 180

Relationship Set Think of the "esteem" of a relationship set as a table. One section for each of the associated substance sets. One line for every rundown of substances, one from every set, that are associated by the relationship. Students Courses Sally CS180 Sally CS111 Joe CS180 … … Arthur Keller – CS 180

Multiway Relationships Usually paired connections (interfacing two E.S.) suffice. Be that as it may, there are a few situations where at least three E.S. must be associated by one relationship. Illustration: relationship among understudies, courses, TA's (and graders). Conceivably, this E/R graph is OK: Taking Students Courses Assisting TA/Graders Arthur Keller – CS 180

Works in CS180, in light of the fact that every TA (or grader) is a TA of all understudies. Association understudy TA is just by means of the course. Yet, imagine a scenario where understudies were separated into areas, each headed by a TA. At that point, an understudy in CS180 would be identified with stand out of the TA's for CS180. Which one? Require a 3-path relationship to tell. Arthur Keller – CS 180

Courses Students Courses TAs Ann CS180 Jan Sue CS180 Pat Bob CS180 Jan … … … Enrolls Students TAs Arthur Keller – CS 180

Beers-Bars-Drinkers Example Our running case for the course. name addr permit Frequents Serves Bars Likes Beers Drinkers name manf name addr Arthur Keller – CS 180

Multiplicity of Relationships Representation of Many-One E/R: bolt indicating "one." Rounded bolt = "precisely one." Many-numerous Many One-one Arthur Keller – CS 180

Example: Drinkers Have Favorite Beers name addr permit Frequents Serves Bars Likes Beers Drinkers Favorite name manf name addr Arthur Keller – CS 180

One-One Relationships Put bolts in both headings. Plan Issue: Is the adjusted bolt supported? Plan Issue: Here, maker is an E.S. In prior outlines it is a property. Which is correct? Success Manfs Beers Arthur Keller – CS 180

Attributes on Relationships value Shorthand for 3-way relationship: Sells Bars Beers value Prices Sells Bars Beers Arthur Keller – CS 180

A genuine 3-way relationship. Cost depends mutually on brew and bar. See bolt tradition for multiway connections: "all different E.S. decide one of these." Not adequately broad to express any plausibility. Notwithstanding, if value, say, depended just on the lager, then we could utilize two 2-way connections: value brew and brew bar. On the other hand better: simply make value a characteristic of lager. Arthur Keller – CS 180

Converting Multiway to 2-Way Baroque in E/R, yet important in certain "protest arranged" models. Make another interfacing E.S. to speak to columns of a relationship set. E.g., (Joe's Bar, Bud, $2.50) for the Sells relationship. Numerous one relationsh