1 / 92

Database Design Fundamentals for Application Development

Explore ER diagrams, schema design, and database user roles. Learn requirements analysis, schema refinement, and security design in CMU's DB applications course. Develop applications for university administration with SQL.

vernal
Download Presentation

Database Design Fundamentals for Application Development

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. Carnegie Mellon Univ.Dept. of Computer Science15-415/615 - DB Applications C. Faloutsos - A. Pavlo Lecture#2: E-R diagrams

  2. Administrivia • Course url: • http://15415.courses.cs.cmu.edu/ • Course policies • http://15415.courses.cs.cmu.edu/fall2016/policies.html • Foils in pps: • http://15415.courses.cs.cmu.edu/fall2016/slides-pps/ CMU SCS 15-415/615

  3. Course Topics ✔ • Introduction to DBMSs • Data Models • Query Language (SQL) • Database Design • Query Optimization & Indexing • Transaction Management • Advanced Topics CMU SCS 15-415/615

  4. Problem • Develop an application for U.G. admin: • Student info • Who-takes-what class • Class rosters • Transcripts • How do you proceed? • (Which role(s) are you playing?) CMU SCS 15-415/615

  5. Database users • ‘naive’ users • casual users • application programmers • [ DBA (Data base administrator)] CMU SCS 15-415/615

  6. Casual users select * from student DBMS and meta-data = catalog data CMU SCS 15-415/615

  7. ``Naive’’ users Pictorially: app. (eg., report generator) DBMS and meta-data = catalog data CMU SCS 15-415/615

  8. App. programmers • Authors of applications (like the ‘report generator’) app. (eg., report generator) DBMS and meta-data = catalog data CMU SCS 15-415/615

  9. DB Administrator (DBA) • Duties? DBMS and meta-data = catalog data CMU SCS 15-415/615

  10. DB Administrator (DBA) • Duties? DBMS and meta-data = catalog data CMU SCS 15-415/615

  11. DB Administrator (DBA) • schema definition (‘logical’ level) • physical schema (storage structure, access methods • schema modifications • granting authorizations • integrity constraint specification CMU SCS 15-415/615

  12. Problem • Develop an application for U.G. admin: • Student info • Who-takes-what class • Class rosters • Transcripts • How do you proceed? • (Which role(s) are you playing?) CMU SCS 15-415/615

  13. Database users • ‘naive’ users • casual users • application programmers • [ DBA (Data base administrator)] CMU SCS 15-415/615

  14. Database Design • Requirements Analysis • Conceptual Design • Logical Design • Schema Refinement • Physical Design • Security Design CMU SCS 15-415/615

  15. Database Design ✔ user’s needs high level (ER) Tables Normalization Indices etc Access controls • Requirements Analysis • Conceptual Design • Logical Design • Schema Refinement • Physical Design • Security Design CMU SCS 15-415/615

  16. Problem’ Maintain • Develop an application for U.G. admin: • Student info • Who-takes-what class • Class rosters • Transcripts • If you are the *new* DBA, what would you rather inherit: CMU SCS 15-415/615

  17. This or this ? drop table if exists student; create table student (ssn fixed, name char(20)); drop table if exists takes; create table takes (ssn fixed, cid char(10), grade fixed); Student Takes Course CMU SCS 15-415/615

  18. True story • Health insurance company • Wants to catch (some of the abundant) fraud • Schema: • patients, visit doctors, get medicine, • Doctors perform operations, … • Nurses monitor patients, … • etc etc • Q: How many tables do you think it spans? CMU SCS 15-415/615

  19. True story • Schema: • patients, visit doctors, get medicine, • Doctors perform operations, … • Nurses monitor patients, … • etc etc • Q: How many tables do you think it spans? 10? 20? 30? CMU SCS 15-415/615

  20. True story • Schema: • patients, visit doctors, get medicine, • Doctors perform operations, … • Nurses monitor patients, … • etc etc • Q: How many tables do you think it spans? 10? 20? 30? • A: 120 PAGES of schema CMU SCS 15-415/615

  21. Motivation & upcoming conclusion: • E-R diagrams are excellent documentation tools Student Takes • drop table if exists student; • create table student • (ssn fixed, • name char(20)); • drop table if exists takes; • create table takes • (ssn fixed, • cid char(10), • grade fixed); Course CMU SCS 15-415/615

  22. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • ER modeling questions Basic Advanced/ rare CMU SCS 15-415/615

  23. Tools Entities (‘entity sets’) Student Relationships (‘rel. sets’) and mapping constraints N M Takes P attributes Course CMU SCS 15-415/615

  24. Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets CMU SCS 15-415/615

  25. Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets CMU SCS 15-415/615

  26. Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets CMU SCS 15-415/615

  27. ... name STUDENT ssn INSTRUCTOR primary key = unique identifier -> underline issn CMU SCS 15-415/615

  28. ... name STUDENT c-id ssn c-name COURSE INSTRUCTOR issn but: sections of course (with different instructors)? CMU SCS 15-415/615

  29. ssn STUDENT c-id COURSE SECTION s-id but: s-id is not unique... (see later) INSTRUCTOR issn CMU SCS 15-415/615

  30. ssn STUDENT c-id COURSE SECTION s-id Q: how to record that students take courses? INSTRUCTOR issn CMU SCS 15-415/615

  31. ssn STUDENT N c-id takes M COURSE s-id SECTION INSTRUCTOR issn CMU SCS 15-415/615

  32. STUDENT N c-id takes M s-id SECTION COURSE N teaches 1 INSTRUCTOR CMU SCS 15-415/615

  33. STUDENT N c-id takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR CMU SCS 15-415/615

  34. Cardinalities 1 1 • 1 to 1 (example?) • 1 to N • N to M 1 N N M CMU SCS 15-415/615

  35. Cardinalities 1 1 has CAPITAL COUNTRY 1 N owns PERSON CAR N M takes SECTION STUDENT CMU SCS 15-415/615

  36. Cardinalities has CAPITAL COUNTRY Book’s notation: owns PERSON CAR takes SECTION STUDENT CMU SCS 15-415/615

  37. Cardinalities 1 1 has CAPITAL COUNTRY Book’s notation vs 1 to N notation 1 N owns PERSON CAR N M takes SECTION STUDENT CMU SCS 15-415/615

  38. Cardinalities 1 1 has CAPITAL COUNTRY 1 N owns PERSON CAR N M takes SECTION STUDENT CMU SCS 15-415/615

  39. ‘Total/partial’ participation 1:1 1:1 total, total has CAPITAL COUNTRY ?:1 ?:N owns PERSON CAR ?? ?:N ?:M takes SECTION ?? STUDENT CMU SCS 15-415/615

  40. ‘Total/partial’ participation 1:1 1:1 total, total has CAPITAL COUNTRY 1:1 0:N owns PERSON CAR partial, total ?:N ?:M takes SECTION ?? STUDENT CMU SCS 15-415/615

  41. ‘Total/partial’ participation 1:1 1:1 total, total has CAPITAL COUNTRY 1:1 0:N owns PERSON CAR partial, total 1:N 0:M takes SECTION partial, total STUDENT CMU SCS 15-415/615

  42. ‘Total/partial’ participation Is it ‘legal’? partial, total 1:1 0:N owns PERSON CAR CMU SCS 15-415/615

  43. ‘Total/partial’ participation Is it ‘legal’? partial, total NO! why not? 1:1 0:N owns PERSON CAR CMU SCS 15-415/615

  44. Subtle concept: Weak entities • ‘section’ has no unique-id of its own!(?) c-id 1 N s-id has COURSE SECTION CMU SCS 15-415/615

  45. Weak entities • ‘weak’ entities: if they need to borrow a unique id from a ‘strong entity - thick box. • ‘c-id’ + ‘s-id’: unique id for SECTION • partial key (eg., ‘s-id’) - dashed-underline • identifying relationship (eg., ‘has’) c-id 1 N s-id has SECTION COURSE CMU SCS 15-415/615

  46. Weak entities • Other example(s) of weak entities? ??id 1 N ?? ?? ??? ??? CMU SCS 15-415/615

  47. Weak entities • Other example(s) of weak entities? e-id 1 N name has Dependent Employee CMU SCS 15-415/615

  48. More details • self-relationships - example? ?? ?? ?? ?? CMU SCS 15-415/615

  49. More details • self-relationships - example? manages 1 EMPLOYEE N CMU SCS 15-415/615

  50. More details • self-relationships - example? Has-friend ?? FB user ?? CMU SCS 15-415/615

More Related