1 / 24

Database Design (Data Modeling)

Database Design (Data Modeling). DCO11310 Database Systems and Design By Rose Chang. Analysis. Design. Implement. Testing. Production. System Development Life Cycle. Object-Oriented DBMS. ODL, UML, etc. Ideas. Relational DBMS. Relations. ER. Data Modeling.

howard-herrera
Download Presentation

Database Design (Data Modeling)

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 (Data Modeling) DCO11310 Database Systems and Design By Rose Chang

  2. Analysis Design Implement Testing Production System Development Life Cycle

  3. Object-Oriented DBMS ODL, UML, etc. Ideas Relational DBMS Relations ER Data Modeling • A particular way of representing data • Analysis of what information the database must hold • Relationships among components of that information

  4. Oracle server Data Models Entity model ofclient’s model Model ofsystemin client’smind Table modelof entity model Tables on disk

  5. Why Use Data Modelling ? • Programming relies very much on data model • Provides a method to reconcile the very different end-user views of the nature and role of data • Helps us to understand the complexity of a real world data environment • Yields a proper “blueprint” of the database design

  6. Steps in Data Modelling Analysis of Requirements Entity Relationship Modelling Transformation into Data Structures Normalisation of Data Structures

  7. Requirement Analysis • Must incorporate all necessary information from different major users • Identify data requirements • Description of data used or generated • Details of how data is to be used and generated • Describe information to be recorded • Combine into a single global view • Analyse the types of transactions to be performed

  8. ER Modelling of Requirements • Construct an ER model to describe the data objects and their interrelationships • Identify key attributes • Decide on the functionality of relationships (mandatory or optional) • Construct a schematic ER model which gives a global view of the entire database

  9. Transformation of the ER Model to a Schema • Map the ER model onto a set of data structure, or schema • Schema : the description of the organization of a database seen by the DBA

  10. Normalisation of the Schema • Reduce each relation to the highest stage of normalisation • BUT designer may wish to refine the resultant schema in order to improve processing efficiency

  11. Logical vs. Physical DB Design (II) Views Selected and derived tables and columns Program Data Logical DB Design Physical DB Design Tables and columns How the data is stored in the computer

  12. Components of Database Design • The four representations or abstractions will progressively get more details of the system : • Data requirements (Textual Information) • Conceptual data model (E-R Model) • Logical schema (Normalized Entity) • Physical Storage schema (Create Table Statements)

  13. Conceptual Data Model (I) • Identify entity types • Identify relationship types • Identify and associate attributes with entity or relationship types • Determine attribute domain • Determine candidate, primary and alternate ket attributes

  14. Conceptual Data Model (II) • Validate conceptual model against user transactions • Review conceptual data model with user

  15. Entity-attribute-relationship Models (EAR) • Used to produce conceptual data model • Consists of : • A formal description of each entity in terms of its attributes • Descriptions of the meanings of relationships • Descriptions of any constraints • The ER diagram

  16. DEPARTMENT number name location EMPLOYEE number name job title have Child entity Parent entity Entity Relationship Model • There are many notations for ERD such as UML, Chen, Crow’s feet • Create an entity relationship diagram from business specifications or narratives • A department have many employee

  17. Entities Relationship • Define associations among entities • All relationships are bi-directional • Degrees of relationships • One-to-one relationship (1:1) • One-to-many relationship (1:M) • Many-many relationship (M:N)

  18. Student-id Student-name Course-code Account-id usage password Student Email have Student-id Student-name Program-code Student Program Program-code Program-name have Degrees of Relationships One-to-one Relationship One-to-many Relationship Detail Entity Master Entity

  19. Student-id Student-name Program-code Course-code Student Course Course-code Course-name take have Many-to-many Relationship You should resolve it by adding link entity Master Entity Master Entity Course Course-code Course-name Student-id Student-name Program-code Student Student-id Course-code mark for belong to Mark get have Link Entity

  20. Optional vs. Mandatory Relationship “A lecture may teach one or many courses.” “A course must be taught by only one lecturer.” Lecturer Course teach

  21. Draw an ERD • Each company operates four departments, and each department belongs to one company. Each department employs one or more employees, and each employee works for one department. Each of the employee may or may not have one or more dependants, and each dependant belongs to one employee. Each employee may or may not have an employment history

  22. Q & A

More Related