1 / 62

Database Design

Database Design. 建構一個能滿足某一應用需求的資料庫結構. Steps in database. Requirements formulation & analysis. Requirements Spec. conceptual schema. Logical database design. Information structure. ERD or 3NF relations. Logical (or implementation) design. Physical database design.

joey
Télécharger la présentation

Database Design

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. Database Design 建構一個能滿足某一應用需求的資料庫結構

  2. Steps in database Requirements formulation & analysis Requirements Spec. conceptual schema Logical database design Information structure ERD or 3NF relations Logical (or implementation) design Physical database design Logical database structure(DBMS processible) Physical design Access methods, Storage structures

  3. Requirement Formation& Analysis • Purpose: identify and describe the data that are required by the organization • Inputs: user information requirements, data items(attributes), data association, processing requirements (reports frequencies, response time requirements, etc.) • Outputs: a set of requirements specifications for conceptual design

  4. Conceptual Design • Purpose: synthesize the various user views and information requirements into a global database design. • The result is a conceptual schema in ERD or normalized telations

  5. Implementation Design • Purpose: map the conceptual data model into logical schema that can be processed by a particular DBMS. • Conceptual model is mapped into hierarchical, network or relational data model. • Schemas & subschemas are developed using DDL

  6. Physical Design • Designing stored formats, selecting access methods (index), security, integrity, backup & recovery. • Output: internal schema

  7. Steps in Requirements Analysis • Identifying & documenting what data user requires • Study the data flows&decision-making process, particularly answering the following questions: • User views? • Data elements (attributes) required in the views? • Primary keys? • Relationships among data elements? • Operational requirements such as security, integrity & response time?

  8. Data-oriented Approach • Tools: view analysis, definition, normalization

  9. Data-oriented Approach Define database scope Establish metadata collection standards • Steps: Identify user views For logical database design Build data dictionary Identify data volumes and Usage For physical Database design Identify operational requirements

  10. Define database scope • Define the scope of the application • Mini-world Univ. Student Registration system Mini-world

  11. Establish metadata collection standards • Use consensus metadata collection forms

  12. Typical forms

  13. Typical forms

  14. Identify user views • Identify the user views in the application • Used for logical database design

  15. User views • A user view is a subset of data required by a particular user to make a decision or carry out some action, can be derived from • Reports, displays, files • Determine semantic rules • Standard form for recording information about user views

  16. A user view example

  17. Build a data dictionary • Each data item type that appears in a user view must be defined and described in details. • Standard form can be used.

  18. Identify data volume and usage • Data volumes and usage pattern is required for physical database design • It is done after the conceptual model has been completed

  19. Identify operational requirements • Security • Integrity • Response time • Back & recovery • Archiving-how long must data be retained? In what form?

  20. View analysis using normalization • Normalization (正規化) • 當我們得到user views(經由RA)之後, 每個view內含多個data items ,分別屬於不 同的entity, 或relationship

  21. Normalization • 例 : Grade-Report 內含 STUDENT#, STUDENT-NAME, MAJOR,COURSE# COURSE-TITLE, INSTRUCTOR-NAME,INSTRUCTOR- LOCATION , GRADE • 分別屬於 STUDENT , COURSE , 和 INSTRUCTOR ENTITY TYPE

  22. Normalization • Normalization provides a foundation for logical database design • Normalization can be used to reduce complex user views to a set of small, stable conceptual schema

  23. Normalization 的步驟 • 定出user views →表示為Unnormalized relations →除去重複群 (1NF) →除去部分相依 (2NF) →除去遞移性相依 (3NF) →view integration (conceptual schema)

  24. User views Unnormalized relations Remove repeating groups Normalized relations (1NF) Remove partial dependency Second normal form(2NF) relations Remove transitive dependency Third normal form(3NF) relations

  25. Unnormalized relation • Relation中含有repeating groups. • 例: Grade-report • 表示法 GRADE-REPORT (STUDENT#,STUDENT-NAME,MAJOR,{COURSE#,COURSE-TITLE,INSTRUCTOR-NAME,INSTRUCTOR-LOCATION,GRADE})

  26. Student# Student name Major Course# Course Title Instructor name Instructor Location grade 38214 Bright IS IS 350 IS 465 database sys anal CODD KEMP B104 B213 A C 69173 Smith PM IS 465 PM 300 QM 440 sys anal prod mgt op res B213 D317 B213 A B C … KEMP LEWIS KEMP Example of unnormalized relation

  27. Normalized relation(1NF) • 中每一個列與行的交會處只能放一個single value . 亦即每一個attribute只能有一個single value • Atomic attribute • 又叫做1NF的表格

  28. Normalization • 經由Normalization的分析，可以由一堆views之中整理出一個conceptual data model (schema), 此conceptual schema 能完整(completely),簡單地(simply)支持 所有的user views.

  29. Normalization • Normalization 的一般用途是免除在對表格作insert , update,和delete時所造成的不方便或異常(anomaly)

  30. Unnormalized relation 的正規化 • 分離repeating group使成一新的relation. 例: STUDENT(STUDENT# , STUDENT-NAME , MAJOR) STUDENT-COURSE(STUDENT# , COURSE# ,COURSE-TITLE , INSTRUCTOR-NAME , INSTRUCTOR-LOCATION , GRADE) • 要帶foreign key以保持損切割 • 找出 Primary key ---STUDENT-COURSE 中 COURSE# 不能獨立形成primary key, 需 (STUDENT# , COURSE#) 聯合成為primary key

  31. Remove repeating group

  32. Problems in STUDENT-COURSE • Data redundancy---IS 465 • Insertion anomaly---insert a new course e.g., BA200, INTR DP, • We cannot do this unless one student registers in BA200 • Deletion anomaly---課程若只有一個人修，若該學生退選，則delete該tuple會導致資訊遺失 • 例如，delete student#=69173修Prod mgt，會遺失Lewis教Prod mgt的訊息。 • Update anomaly---改IS465的課名為sys anal&Des，必須修改全部有關的tuple以免造成不一致。

  33. Reasons for anomaly • Some attributes do not fully depend on the primary key STUDENT-COURSE表格的primary key為 (STUDENT#, COURSE#) COURSE#→ COURSE-TITLE, INSTRUCTOR-NAME, INSTRUCTOR-LOCATION • 我們說COURSE-TITLE,INSTRUCTOR-NAME , INSTRUCTOR-LOCATION partially dependent（部份相關）on the primary key .

  34. Functional dependency(FD) • 正規化是在分析函數相依關係(Functional dependency)，因此介紹函數相依關係 • 定義：Given a relation R , attribute Y of R is functionally dependent on attribute X of R if and only if each X-value in R has associated with it precisely one Y-value in R (at any time) • 函數相依關係是一種語意規則，不能以某一時間表格的內容論定

  35. Functional dependency X Y R y1 y1 x1 x2 X determines Y Y functional dependent on X X→Y X 與Y 的關係為多對一

  36. 第二正規化(2NF) • 定義：A relation is said to be in 2NF , if it is already 1NF , and all non-key attributes are all fully dependent on the primary key • 正規化的方法：將partially dependent 的attributes分到另外一個表格中

  37. 第二正規化範例

  38. Dependency diagram COURSE TITLE STUDENT# ｝ GRADE INSTRUCTOR NAME COURSE# INSTRUCTOR LOCATION Partially function dependency

  39. 表格第二正規化 Student-Course REGISTERATION COURSE-INSTRUCTOR 2NF 3NF

  40. Anomaly in 2NF relation • insert 一個新的instructor data 必須instructor開授某一課程 • delete 某一個course可能會失去一個instructor的資料 , 例 : delete IS350 CODD的資料會遺失 • update 由於instructor的資料重複 ,改instructor的location 較不易

  41. FD in COURSE-INSTRUCTOR COURSE# COURSE TITLE INSTRUCTOR NAME INSTRUCTOR LOCATION Transitive dependency

  42. Primary key A B Transitive dependency • A transitive dependency occurs when one non-key attribute is dependent on one or more non-key attributes Transitive dependency ｝ Primary key →A A→B Primary key→B

  43. 3NF • A relation is in 3NF, if it is already in 2NF and no transitive dependency exists. 3NF的FD型態 … Primary key Attribute2 Attribute1 Attribute n Each non-key attribute is fully dependent on the primary key and there is no transitive dependency.

  44. 3NF的正規化 • 將造成transitive dependency 的attributes分離至另外一個relation 中.

  45. 3NF的正規化 • Conversion of a relation to third normal form (3NF) by removing transitive functional dependency (FD) COURSE-INSTRUCTOR COURSE INSTRUCTOR 3NF 3NF

  46. 3NF的正規化 • Instructor-name 必須放入COURSE relation 之中 以保存COURSE-INSTRUCTOR relationship，如此COURSE relation 之中才可以參考到INSTRUCTOR. • INSTRUCTOR-NAME 為COURSE中的一個foreign key • Normalization 到3NF就可以結束, 因為3NF排除了大部分的anomaly, 每個entity都由各自的一個relation 表示.insert ,delete , 或update一個entity不須參考到別的entity.因為一個relation 只代表一個entity，或relationship 可以繼續作Normalization至4NF, BCNF , 5NF , DKNF … , 但會產生太多的小relation ,通常不必.

  47. 表格分析後的結果 Summary of 3NF relations for GRADE-REPORT STUDENT ( STUDENT#, STUDENT-NAME , MAJOR ) INSTRUCTOR ( INSTRUCTOR-NAME , INSTRUCTOR-LOCATION ) COURSE ( COURSE# , COURSE-TITLE , INSTRUCTOR-NAME) REGISTRATION ( STUDENT# , COURSE# , GRADE )

  48. ER Diagram STUDENT M GRADE N INSTRUCTOR COURSE

  49. Data volume analysis Normalization 做完之後就可以作view integration . 若只有一個view , 可以作Conceptual Model . 例 : Conceptual Model 中可以表示Mapping的關係及Data Volumes . Data Volume : relation中最多tuple時的tuple個數 . 例 : 1.3000 Student 2.每個Student平均選3門課 9000個註冊(修課) 3.100個Instructors 4.平均一個Instructor教3門課 300門課 (班) 5. 平均一班有30人(9000/300=30) - Data volumes 在 conceptual 中表示. 同時mapping 中也表示對應的元素個數.

  50. Data structure diagram Data Structure Diagram : STUDENT 3000 REGISTRATION 3 9000 30 COURSE 300 3 INSTRUCTOR 100

More Related