Database Design Database Development Lifecycle Database Design Intro

Database Design Database Development Lifecycle

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 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. Database Design 2

Data Redundancy Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 3

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 Systems: Design, Implementation, & Management: Rob & Coronel Database Design 4

Database Development System Construction System Analysis • Establish the need and extent of an information system System Development • Design & implement the information system Database Construction Planning & Analysis 기획분석 Analyze data environment & Define database objectives Database Development • Design 설계 → Construct a data model & establish data management processes • Implementation 구현 → Create the storage structure (e. g. , tables) & user interface for data management • Maintenance 정비 → Database Design (corrective & adaptive) modification, backup & recovery 5

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 6

Database Lifecycle: Planning & Analysis Database Design Implement Maintain Define Database Objectives 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 7

Database Lifecycle: Database Design Planning & Analysis Database Design Implement Maintain Create a Database Model → 1. 2. 3. q 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 ← Need to understand how business works & what role data plays Database Design 8

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 & draw an ER Diagram Database Design 9

Conceptual Design: Business Rules Planning & Analysis Database Design Implement Maintain What Brief, precise, and unambiguous descriptions of operations in an organization • Based on policies, procedures, or principles within a specific organization Why Promote creation of an accurate data model ← Enhance understanding & facilitate communication How (sources) Interviews, Documentation, Observation Examples • • • A painter can draw many paintings. A painting is drawn by a single painter. TEACHER 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. Database Design 1 PAINTER 1 draws teaches M PAINTING M COURSE N takes M STUDENT 10

Conceptual Design: Data Modeling Planning & Analysis Database Design Implement Maintain 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? To understand identify essential data elements How? Identify Entities 개체, Attributes 속성, & Relationships 관계 ID STUDENT GPA Major Name TEACHER email Office Database Design 11

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 12

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 13

Database Design: Lifecycle 1. Define Database Objectives What information will the database provide? u 2. Examine the Data Environment How does the business work & what data are used? u 3. Construct the Data Model a. b. c. d. e. 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. Process for evaluating & designing good table structures Verify the Data Model u Þ Database Design Does the data model support the database objectives? If not, modify the Data Model 14

Data Model: University Example Database Design 15

Exercises

Data Modeling Exercises 1. Draw an E-R Diagram of the data model described by the business rules below. One region can be the location for many stores. Each store is located in only one region. Each store employs one or more employees. Each employee is employed by one store. A job can be assigned to many employees. -- e. g. , The "Sales Representative" job can be assigned to more than one employee at a time. Each employee can have only one job assignment. REGION JOB 1 1 is assigned to is location for M STORE Database Design M 1 employees M EMPLOYEE 17

Data Modeling Exercises 2. Identify the business rules and draw an E-R diagram of the situation described below. For each professor, there may be multiple advisees. A professor teaches many classes and a class is taught by one professor. Business Rules A professor can advise many students. A student is advised by one professor. A professor can teach many classes. A class is taught by one professor M 1 PROFESSOR teaches CLASS 1 advises M STUDENT Database Design 18

Data Modeling Exercises 3. Sample DB Create a data model (i. e. ERD) for the school database described below. DB will keep track of advising and class information. DB should provide following information. - The list of students advised for a given year & dates of advising sessions for each students - Course listing for each year & student grades for each class Business Rules A professor can advise many students. A student is advised by one professor. A professor can teach many classes. A class is taught by one professor. A student can take many classes. A class can have many students. A course can generate many classes. A class is generated by one course. A professor can meet many times with an advisee. A student can meet many times with the advisor. M 1 PROFESSOR CLASS teaches 1 1 N M ADVISING advises takes ENROLL M 1 M STUDENT Database Design M 1 M generates M M 1 1 COURSE 19