1 / 56

CSS/416

CSS/416. Data Design and Retrieval Workshop 3. Database Management Systems. Terminology. Database management Systems. Relational model Based on 1970 paper by E.F. Codd User sees all data in tables Tables can be combined using “set” operations ( linked via columns )

valeria
Download Presentation

CSS/416

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. CSS/416 Data Design and Retrieval Workshop 3

  2. Database Management Systems Terminology CSS/416 Workshop 3

  3. Database management Systems • Relational model • Based on 1970 paper by E.F. Codd • User sees all data in tables • Tables can be combined using “set” operations (linked via columns) • Metadata also stored in tables as above • Logical design separate from physical implementations CSS/416 Workshop 3

  4. Database Management Systems • Functional Dependencies • A -> B; A determines B (A is Determinant) • Partial (PK = AB, B -> C) • CustID -> Customer Name CSS/416 Workshop 3

  5. Database Management Systems • Keys • Derived from entity identifier • One or more attributes (more = composite key) • Uniquely determine a row • Functionally determine an entire row’s attributes CSS/416 Workshop 3

  6. Database management systems Modification Anomalies (multiple themes) - Can’t insert fact that scuba diving cost $175 - Cant update swimming fee - Delete SID 100=>lose skiing data CSS/416 Workshop 3

  7. Database Normalization • Entities should have common theme • Stages • First Normal form • Second • Third • Others (BCNF, 4th, 5th, DK) • Impact on referential integrity • Denormalize for performance (city, state, zip is classic example) CSS/416 Workshop 3

  8. Database Normalization • 1st normal form – eliminate multi-valued attributes(repeating groups) • 2nd normal – no partial key functional dependencies • 3rd normal – eliminate transitive dependencies CSS/416 Workshop 3

  9. Database Normal Forms Violates first normal – why? CSS/416 Workshop 3

  10. Database Normalization 1st Normal satisfied Still violates 2nd normal– why? CSS/416 Workshop 3

  11. Database Normalization 2nd OK but 3rd? CSS/416 Workshop 3

  12. Database Normalization Satisfies 3rd normal form CSS/416 Workshop 3

  13. Database Normalization • BCNF – Every determinant is a candidate key • Fourth – Multi-valued dependencies • Fifth – Can recombine relations CSS/416 Workshop 3

  14. Database Normalization • Domain Key (DK/NF) • Provably free from anomalies • But no one way to generate this form • All constraints follow from domain definitions and keys CSS/416 Workshop 3

  15. Database Normalization • Domain Key (DK/NF) • “The key, the whole key, and nothing but the key” CSS/416 Workshop 3

  16. Database Design • Translating a model to the Database • Entities -> tables • Establish primary & foreign keys • Many-to-many relations ->Junction table • Business rules -> triggers, constraints, etc. • Surrogate keys • Security • Typically done with a “CASE” tool CSS/416 Workshop 3

  17. Database Design One-to-one Auto # Employee Has Auto 1 1 Emp # CSS/416 Workshop 3

  18. Database Design One-to-many N 1 Mfgr Makes Equipment Mfgr # CSS/416 Workshop 3

  19. Database Design One-to-many (w/ ID Dependency PK = inv#, item# N 1 Invoice Has Line Item Inv # CSS/416 Workshop 3

  20. Database Design Many-to-many Mfgr_Eqpt Mfg # Eqpt ID Equipment Mfgr M N CSS/416 Workshop 3

  21. Database Design Recursive 1 Member Referred by 1 Member # CSS/416 Workshop 3

  22. Database Design IS-A relationship (Subscriptions) Member Member# Print Online CSS/416 Workshop 3

  23. Database Design • Learning teams to implement sample relations for above E-R relationships: • 1 to 1 • 1 to many • Many to many • 1 to many w/ ID Dependency • Recursive • IS-A CSS/416 Workshop 3

  24. SQL • Two main language components • Data definition language (DDL) • Data manipulation language (DML) CSS/416 Workshop 3

  25. SQL • Data definition language (DDL) • Create, alter, drop, etc. • Frequently implemented via various CASE tools: Visio, Embarcadero, ERWin, etc. • But – also very useful for database administration CSS/416 Workshop 3

  26. SQL Create Statement Create table patient (name varchar2(35) not null, age smallint, gender varchar2(1), account_number integer not null, primary key account_number) <..additional constraints such as foreign keys, etc. ..> CSS/416 Workshop 3

  27. SQL Select (Projection) Select plantname from equipment Select (Restriction) Select * from equipment where eqpt_name = ‘feed heater’ CSS/416 Workshop 3

  28. SQL Select (Selection + projection) Select eqpt_name from equipment where plantname = “styrene” Sorting (Ordering) Select * from manufacturers order by eqpt_mfgr CSS/416 Workshop 3

  29. SQL • There are many built in functions • Some are called aggregate functions • Count • Max • Avg • Etc. Example Select max(age) from dependents CSS/416 Workshop 3

  30. SQL Joins (old syntax) Select m.mfgr_addr from manufacturers m, equipment e where m.eqpt_mfgr = e.eqpt_mfgr and e.plantname = ‘styrene’ CSS/416 Workshop 3

  31. SQL Joins (new syntax) Select m.mfgr_addr from manufacturers m inner join equipment e On e.name = m.name where e.plantname = ‘styrene’ CSS/416 Workshop 3

  32. SQL Subqueries Select m.mfg_addr from manufacturers m where eqpt_mfgr in (select e.eqpt_name from equipment e) CSS/416 Workshop 3

  33. SQL Insert Insert into equipment values(‘propylene’, ‘feed heater’, ‘ABC Exchanger’) Update Update manufacturers Set address = ‘18 Front Road’ Where mfgr_name = ‘ABC Exchanger’ CSS/416 Workshop 3

  34. SQL Delete Delete from equipment Where plant_name = ‘styrene’ And eqpt_name = ‘feed cooler’ How many rows could this delete? CSS/416 Workshop 3

  35. SQL Exercise • Create the tables in the design exercise • Create a few sample SQL selects, etc. CSS/416 Workshop 3

  36. Functions of a Database Application Page 238 Figure 10-1 © 2000 Prentice Hall

  37. CRUD “the first function of a database application is to CRUD views” • Create • Read • Update • Delete Page 237 CSS/416 Workshop 3

  38. Format or Materialize views “the second function of a database application; the appearance of the content” Page 238 CSS/416 Workshop 3

  39. Other database functions • Enforce constraints • Provide for security and control • Execute business logic Page 238 CSS/416 Workshop 3

  40. E-R Diagram Page 240 Figure 10-3b © 2000 Prentice Hall

  41. Relational Design Page 240 Figure 10-3c © 2000 Prentice Hall

  42. Relational Design(w/ Surrogate Keys) Page 240 Figure 10-3d © 2000 Prentice Hall

  43. Relational Diagram Page 241 Figure 10-3e © 2000 Prentice Hall

  44. View “A structured list of data items (attributes) from the entities or semantic objects defined in the data model” A view can be materialized or formatted as a form or report Page 242 CSS/416 Workshop 3

  45. Recordset “the result of an SQL statement” Page 243 CSS/416 Workshop 3

  46. CRUD actions on a view Read SELECT CUSTOMER.CustomerID, CUSTOMER.Name FROM CUSTOMER, WORK WHERE CUSTOMER.CustomerID = WORK.CustomerID Page 243 CSS/416 Workshop 3

  47. CRUD actions on a view Create INSERT INTO CUSTOMER (CUSTOMER.Name, CUSTOMER.City) VALUES (NewCust.CUSTOMER.Name, NewCust.CUSTOMER.City) Page 244 CSS/416 Workshop 3

  48. CRUD actions on a view Update INSERT INTO CUSTOMER (CUSTOMER.Name, CUSTOMER.City) VALUES (NewCust.CUSTOMER.Name, NewCust.CUSTOMER.City) Page 246 CSS/416 Workshop 3

  49. CRUD actions on a view Delete Cascading deletions depends on relationship cardinality Page 247 Figure 10-6 © 2000 Prentice Hall

  50. Form “a screen display used for data entry and edit” Forms should... • reflect the view structure • make data associations graphically clear • encourage appropriate action Page 248 CSS/416 Workshop 3

More Related