CSE 510 Web Data Engineering Database Design UB CSE 510 Web Data - PowerPoint PPT Presentation

CSE 510 Web Data Engineering Database Design UB CSE 510 Web Data Engineering

How to Design a Database and Avoid Bad Decisions • With experience… • Learn in CSE462 normalization rules of database design • Think entities and relationships – translate to relations 2 UB CSE 510 Web Data Engineering

E/R-Based Design Attribute DateCode FirstName Number StartTime UBID LastName Name EndTime Classes Students Enrollment Entity Relationship Credits 3 UB CSE 510 Web Data Engineering

E/R  Relational Schema: Basic Translation • For every entity, create corresponding table – Include an ID attribute even if not in E/R • For every relationship, create table – For each referenced entity E i include foreign key attribute referencing ID of E i 4 UB CSE 510 Web Data Engineering

Example Name Address Year StarsIn Stars Title Length Movies Name Address Owns Studios 5 UB CSE 510 Web Data Engineering

3-Way Relationship Name Address Year Stars Title Length Movies Contract Name Address Studios Fee • A studio has contracted with a particular star to act in a particular movie 6 UB CSE 510 Web Data Engineering

Relationships with Roles Year Title Length Original Movies SequelOf Sequel 7 UB CSE 510 Web Data Engineering

“Subclassing” Year Title Length Movies StarsIn Name Address Stars IsA Cartoons Voices … 8 UB CSE 510 Web Data Engineering

Transaction Management • Transaction: Collection of actions that maintain the consistency of the database if ran to completion & isolated • Goal: Guarantee integrity and consistency of data despite – Concurrency – Failures • Concurrency Control • Recovery 9 UB CSE 510 Web Data Engineering

Concurrency & Failure Problems • Consider the “John & Mary” checking & savings account – C: checking account balance C2S(X=100) Read(C) – S: savings account balance C:=C-100 • Check-to-Savings transfer Write(C) transaction moves $X from Read(S) C to S S:=S+100 Write(S) – If it runs in the system alone and to completion, the total sum of C and S stays the same 10 UB CSE 510 Web Data Engineering

Failure Problem & Recovery Module’s Goal • Database is in inconsistent state after machine restarts C2S(X=100) • It is not the developer’s Read(C) problem to account for C:=C-100 crashes Write(C) • Recovery module guarantees that all or none of a CPU Halts transaction happens and its Read(S) effects become “durable” S:=S+100 Write(S) 11 UB CSE 510 Web Data Engineering

Concurrency Problem & Concurrency Control Module’s Goals • If multiple Serial Schedule transactions run in Read(C) sequence, the C:=C+100 resulting database is Write(C) consistent Read(S) S:=S-100 • Serial schedules Write(S) – De facto correct Read(C) C:=C+50 Write(C) Read(S) S:=S-50 Write(S) 12 UB CSE 510 Web Data Engineering

Concurrency Problem & Concurrency Control Module’s Goals Good Schedule • Databases allow with Concurrency transactions to run in Read(C) parallel C:=C+100 Write(C) Read(C) C:=C+50 Write(C) Read(S) S:=S-100 Write(S) Read(S) S:=S-50 Write(S) 13 UB CSE 510 Web Data Engineering

Concurrency Problem & Concurrency Control Module’s Goals Bad Schedule • “Bad” interleaved with Concurrency schedules may leave Read(C) database in C:=C+100 inconsistent state Read(C) • Developer should not Write(C) have to account for C:=C+50 parallelism Write(C) • Concurrency control Read(S) module guarantees S:=S-50 serializability Write(S) – only schedules Read(S) equivalent to serial ones S:=S-100 happen Write(S) 14 UB CSE 510 Web Data Engineering