Database Design Database Development Lifecycle Why Database From

Database Design Database Development Lifecycle

Why Database? From Data … Simple dumping of data on the storage medium provides little value. CUSTOMER id name address country pay due 100 523 800 12 High Rd. , Leeds 52 Ln. Muncie, IN Box. 9, Miami, FL UK USA 33. 75 0 12. 50 J. Rodney E. Hoover M. Old PRODUCT product_id title cost sale price 123 -19 -20 169 -15 -34 354 -90 -33 Joy of Living Learning Judo Your Dream Home 12. 50 20. 00 18. 25 19. 25 25. 00 24. 25 SALE id 100 523 100 800 Database Design zone product_id quantity total price UK USMW UK USSE 123 -19 -20 354 -90 -33 169 -15 -34 123 -19 -20 2 1 1 1 38. 50 24. 25 25. 00 19. 25 2

Why Database? … Towards Information The goal is not just storage of data, but ultimately the extraction of information to support decision making by key people and groups in the organization. u Data Information (e. g. reports, tabulations, graphs) Decisions * - Summarized fact or information * In the UK ZONE, Joy of Living had a sale of $38. 50, and Learning Judo had a sale of $25. ** UK ZONE had the total sale of $63. 50, USMW had $24. 25, and USSE had $19. 25. SALE by Zone** Title Joy of Living Learning Judo Your Dream Home UK 38. 50 25. 00 63. 50 Database Design USMW 24. 25 USSE 19. 25 total 57. 75 25. 00 24. 25 107. 00 3

Why Database? … For Decision Making (DBMS) Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 4

Database Design: Intro Database System Provides data collection 수집, storage 저장, and retrieval 검색 Composed of people, hardware, software, database(s), procedures, and application programs Database Design Foundation of a successful database system 휼륭한 DB system의 기반 Should promote • Data integrity 데이터 무결성 • Prevent data redundancies 반복 & anomalies 오류 Must yield a database that • is efficient in its provision of data access 효율적인 데이터 접근 • meet the objectives of the database system and serves the needs of its users DB시스템의 목적과 이용자의 요구 만족 Database Design 5

Data Redundancy (데이터의 반복성) Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 6

Data Anomaly 데이터의 오류 § Update Anomalies 수정오류 - Data inconsistencies resulting from “islands of information” problem § Insertion Anomalies 삽입오류 - Creation of bogus record when adding new data (e. g. new agent) § Deletion Anomalies 삭제오류 - Unintended deletion of related data (e. g. agent data when deleting customer) Database Design Database Systems: Design, Implementation, & Management: Rob & Coronel 7

Database Development System Construction 시스템 구축 System Analysis • 시스템 분석 Establish the need and extent of a system System Development • 시스템 개발 Design & implement the system Database Construction Planning & Analysis DB 구축 기획분석 Analyze data environment & Define database objectives Database Development • Design → • 구현 Create the storage structure (e. g. , tables) & user interface for data management Maintenance → Database Design Construct a data model & establish data management processes Implementation → • 설계 유지보수 (corrective & adaptive) modification, backup & recovery 8

Database Development Lifecycle Planning & Analysis Database Objectives 기획분석 Maintenance Design 정비 개념 설계 Modification Backup & Recovery Security Business Rules Data Model Implementation 구현 DBMS Tables User Interface SQL Queries Database Design 9

Database Lifecycle: Planning & Analysis Database Design Implement Maintain Define Database Objectives DB목적 설정 u What will the database (DB) do? What tasks 업무/작업 will the DB support? → What information will the DB provide? → ← Data Entry, Update, Search Q&A Study the DB environment How does the business operate? Who are the players? How does the information flow? ← Examine the data Database Design Sample Q&A: Recipe Database Query Result recipe name recipe info (ingredients, steps, …) ingredients main ingredient (meat, fish, …) recipe type (diet, diabetic, …) recipe names recipe info recipe category (soup, desert, …) budget, time 10

Database Lifecycle: Database Design Planning & Analysis Database Design Create a Database Model → 1. 2. 3. q Implement Maintain DB 모델 제작 that can achieve the database objectives Conceptual Design – ER Modeling Logical Design – Relational Schema & Normalization Physical Design – Schema into DBMS (Table Creation) What is a Database Model? → Abstract data structure of the real-world items 실제 항목의 추상적인 data 구조 ← Need to understand how business works & what role data plays Database Design 11

Database Design: Conceptual Design Planning & Analysis Database Design Implement Maintain Conceptual Design Steps 1. Enumerate the Business Rules How does the business work? 2. Construct the Data Model Identify Entities, Relationship, Attributes & draw an ER Diagram 3. Verify the Data Model Does the data model support the fulfillment of database objectives? Database Design 12

Conceptual Design: Business Rules Planning & Analysis Database Design Implement Maintain Conceptual Design What Identify Businee Rules 조직 내 정확한 업무 기술 Brief, precise, and unambiguous descriptions of operations in an organization • Why Based on policies, procedures, or principles within a specific organization 정확한 data model 설계를 위하여 Promote creation of an accurate data model ← Enhance understanding & facilitate communication How (sources) 1 PAINTER draws M PAINTING Interviews, Documentation, Observation Examples • • • TEACHER 1 teaches M COURSE A painter can draw many paintings. A painting is drawn by a single painter. N A teacher can teach 0 to 6 courses per semester. A course is taught by a single teacher. A student must take at least 1 and at most 6 courses. A course can have 10 to 40 students. M Database Design takes STUDENT 13

Conceptual Design: Data Modeling Planning & Analysis Database Design Implement Maintain Conceptual Design What is Data Modeling? A model is a representation of reality that retains only carefully selected essential details. Logical organization of data for optimum information extraction and data manipulation Why Model? Identify Business Rules Construct Data Model 주요한 data 요소들을 이해하고 인식하기 위하여 To understand identify essential data elements How? Identify Entities 개체, Attributes 속성, & Relationships 관계 ID STUDENT GPA Major Name TEACHER email Office Database Design 14

Conceptual Design: E-R Model Verification Planning & Analysis Database Design Implement Maintain Conceptual Design E-R model is verified against proposed system processes. u u u Corroboration that intended processes can be supported by the database model Careful reevaluation of the entities and detailed examination of attributes Verification of business transactions as well as system and user requirements May reveal additional entity and attribute details. Verification process is iterative. 1. 2. 3. 5. 6. Construct Data Model Verify Data Model identify ER model’s central entity identify modules/subsystems & components identify transaction requirements 4. Identify Businee Rules update/insert/delete/query/report user interface verify all processes against ER model make necessary changes repeat steps 2 through 5 Database Design Database Systems: Design, Implementation, & Management: Rob & Coronel 15

Database Design: Relational Schema Planning & Analysis Database Design Implement Maintain Ø Specification of the overall structure/organization of a database § Relations § § Entities w/ Attributes Primary key • Unique identifier § Foreign key § § PK of related table Relationship type (connectivity) 1: M, M: N, 1: 1 https: //www. coursera. org/learn/analytics-mysql/lecture/h. DLIS/relational-schemas Database Design 16

Database Design: Data Dictionary Planning & Analysis Database Design Implement Maintain Detailed description of a data model Lists attribute names and characteristics for each table in the database Blueprint & documentation of a database Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 17

Database Design: Lifecycle 1. Define Database Objectives What information will the database provide? u 2. Examine the Data Environment Construct the Data Model a. b. c. d. e. data모델 구축 Enumerate the Business Rule. Identify Entities & Relationships between them. Define Attributes (and primary & foreign keys) for each entity Create an initial E-R diagram Normalize the entities 개체 정규화 4. data 환경 조사 How does the business work & what data are used? u 3. DB 목적 설정 Process for evaluating & designing good table structures Verify the Data Model u Þ Database Design data모델 확인 Does the data model support the database objectives? If not, modify the Data Model 18

Data Model: University Example Database Design 19