1 / 32

Module 1 Designing a Conceptual Database Model

Module 1 Designing a Conceptual Database Model. Overview of Database Design Gathering Database Requirements Creating a Conceptual Database Design Overview of Entity Framework. Module Overview. Database Design Process Best Practices for Database Design

jacoba
Download Presentation

Module 1 Designing a Conceptual Database Model

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. Module 1 Designing a Conceptual Database Model

  2. Overview of Database Design Gathering Database Requirements Creating a Conceptual Database Design Overview of Entity Framework Module Overview

  3. Database Design Process Best Practices for Database Design Best Practices for Managing the Scope of a Database Design Project Lesson 1: Overview of Database Design

  4. Database Design Process Identify and record database requirements Create a conceptual model Transform the conceptual model into a logical model Implement a physical model from the logical model Refine the physical model Build a prototype and test the database design 1 2 3 4 5 6

  5. Best Practices for Database Design Validate database design goals against project scope Ensure a clear definition of the database portion Choose a professional data-modeling tool Choose a database design methodology Use a source control system Compare business needs against the ideal design and quality Retain application features of the existing database in the new design Begin with a conceptual model

  6. Best Practices for Managing the Scope of a Database Design Project Obtain an authoritative statement of the project scope Ensure that the scope is realistic, feasible, and consistent Isolate the database portion of the project scope Clearly identify scope boundaries Identify the stakeholders and decision makers Implement a change-control process Identify database deliverables Identify risk factors Compile all available scoping information into a single document Avoid scope creep

  7. Lesson 2: Gathering Database Requirements Strategies for Identifying Database Requirements Best Practices for Documenting Database Requirements Considerations for Modifying an Existing Database Discussion: What Are the Methodologies for Maintaining a Database?

  8. Strategies for Identifying Database Requirements Identify and interview domain experts Isolate business objects, rules, and data flows ü ü Establish a consistentnaming standard forbusiness objects and rules Determine the transaction rate and projected growth requirements ü ü Estimate current capacity and projected growth requirements ü

  9. Best Practices for Documenting Database Requirements Ensure that the database requirements are adequately represented in the requirements document Document the requirements concisely and accurately Clarify and revise ambiguous or unclear requirements Make the requirements document readable for stakeholders Obtain an explicit agreement from all stakeholders

  10. Considerations for Modifying an Existing Database Develop a deployment strategy ü Identify the database requirements related to thecurrent project Review and validate existing database documentation ü ü Reverse engineer the existing database design, if required Plan for migrating the data to the new design ü ü

  11. Discussion: What Are the Methodologies for Maintaining a Database? How do you perform bug tracking in your organization? How do you implement change logs in your projects? What tools are used in your organization?

  12. Lesson 3: Creating a Conceptual Database Design Considerations for Choosing a Conceptual Modeling Methodology Guidelines for Conceptual Modeling by Using ORM Demonstration: How To Create an ORM Diagram Guidelines for Conceptual Modeling by Using ER Demonstration: How To Create an ER Diagram Guidelines for Conceptual Modeling by Using UML

  13. Considerations for Choosing a Conceptual Modeling Methodology The methodology should support the database design at all levels The data-modeling tools should support the methodology The methodology must be acceptable to project sponsors

  14. Guidelines for Conceptual Modeling by Using ORM Identify ORM components • Identify elementary facts • Determine fact types • Identify types and subtypes ü Identify ORM roles • Determine database objects and roles • Identify mandatory and optional roles ü Identify ORM constraints ü • Identify uniqueness constraints • Identify other constraints

  15. Demonstration: How To Create an ORM Diagram • In this demonstration, you will see how to: • Create an ORM diagram by using the ORM template

  16. Guidelines for Conceptual Modeling by Using ER Choose entities, attributes, and relationships based on requirements ü Identify cardinality and constraints ü Identify subtypes and supertypes ü Produce a conceptual ER diagram ü Students Course Entity Relationship (ER) PK StudentNo PK CourseNo FirstName LastName CourseName Level

  17. Demonstration: How To Create an ER Diagram • In this demonstration, you will see how to: • Create an ER diagram by using the Database Model Diagram template

  18. Guidelines for Conceptual Modeling by Using UML Course Student attends - StudentNo - FirstName - LastName - ClassNo - ClassName - Level Unified Modeling Language (UML) Develop Use Case diagrams to represent relationships Use Class diagrams to represent the conceptual model Use Activity diagrams to build the conceptual model

  19. Lesson 4: Overview of Entity Framework Introduction to Entity Framework Entity Framework Components Selecting an Entity Data Model Using Entity Data Model Types Mapping a Conceptual Model to a Storage Schema Entity Data Model Relationships Overview of Entity Mapping What Is Entity SQL? Using Entity Data Model Tools and Stored Procedures Using Entity SQL Components

  20. Introduction to Entity Framework Entity Framework helps developers create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema Benefits of Entity Framework Applications • Applications can work in terms of a more application-centric conceptual model • Applications are freed from hard-coded dependencies on a particular data engine or storage schema • Mappings between the conceptual model and the storage-specific schema can change without changing the application code • Developers can work with a consistent application object model that can be mapped to various storage schemas • Multiple conceptual models can be mapped to a single storage schema • LINQ support provides compile-time syntax validation for queries against a conceptual model

  21. Entity Framework Components Language-Integrated Query (LINQ) to Entities Entity SQL Query Entity SQL Query IEnumerable <T> Object Services EntityDataReader EntityClient Data Provider ADO.NET Data Provider Command Tree Command Tree Data Source Conceptual Model Mapping DBDataReader Logical Model

  22. Selecting an Entity Data Model < xml > < xml > Designing EDM Types in Schemas • EDM types preserve the logical integrity of data and the constraints of applications • Object models reflect the languages in which they are implemented • Data structure in an EDM application is specified by using a conceptual schema Implementing Object Services from Schemas • EDM entities and relationships are formally defined in XML syntax in a design schema • EDM design schema is mapped to another schema containing metadata describing the storage model • CLR data types used in the application code are built from the design schema

  23. Using Entity Data Model Types Complex Types Simple Types • Describe the primitive data types • Have no internal properties • Have an internal structure • Help to implement a property that has internal properties of its own EDM Types Nullable Constraint Default Attribute • Specifies whether the value of the property can be unassigned, or Null • Specifies value for a property if no value is supplied when an instance of the entity is created EDM-Type Constraints

  24. Mapping a Conceptual Model to a Storage Schema The Entity Framework provides an object-centric view of tabular data, expressed as entity types The Storage Model The Mapping Specification • A separate data model uses store schema definition language (SSDL) to describe the logical model for persistent data, usually stored in a relational database • The data types of properties declared in SSDL files are those of the storage model • A mapping specification uses mapping specification language (MSL) to connect the types declared in the conceptual model to the database metadata declared in the storage model • This MSL fragment demonstrates a one-to-one mapping between the conceptual and storage models

  25. Entity Data Model Relationships Unary One to One Is_Married_To One to Many MANAGER Person EMPLOYEE Tertiary Binary One to One Supplier Parking Space Is_Assigned Person Product Warehouse Ship One to Many Orderline ORDER Contains Quantity Characteristics of EDM relationships: Degree of a relationship is the number of types among which the relationship exists ü Multiplicity is the number of instances of a type that are related ü Direction represents the association between the types ü

  26. Overview of Entity Mapping Logical Model Conceptual Model <? Xml ?><ssdl /> Entity Mapping • EDM uses MSL to connect the types declared in conceptual schema definition language (CSDL) to database metadata • Namespace Name and EntityContainer Name declared in the CSDL schema identify entities and associations contained by the conceptual schema • Entity sets that are contained by entity containers are mapped by using syntax similar to that used for entity containers <? Xml ?><csdl /> Mapping <? Xml ?><msl />

  27. Using Entity Data Model Tools and Stored Procedures Entity Data Model Wizard Entity Designer Update Model Wizard Entity Data Model Tools EDM Stored Procedure Support • Security • Encapsulation • Predictability • Performance

  28. What Is Entity SQL? • Entity SQL is similar to the Transact-SQL language, and it is provided by the ADO.NET Entity Framework to support the EDM • Entity SQL supports EDM constructs, enabling users to effectively query the data represented by an entity model • The Entity Framework works with storage-specific data providers to translate generic Entity SQL into storage-specific queries Example of Entity SQL SELECT customers.Name FROM customers AS c

  29. Using Entity SQL Components EDM Components • Support inheritance and relationships • Support collections • Support expressions • Treat subqueries uniformly • Avoid implicit coercions for subqueries • Avoid the implicit row wrapper • Use left correlation and aliasing • Reference column properties of tables • Navigate through objects • Support aliasing for group by keys • Support collection-based aggregates • Allow ORDER BY clauses • Compare identifiers • Avoid support for unqualified syntax

  30. Lab 1: Designing a Conceptual Database Model Exercise 1: Formulating a Conceptual Database Design Exercise 2: Creating a Conceptual Database Design Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd Logon Information Estimated time: 60 minutes

  31. Lab Scenario You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp2008 sample database in SQL Server 2008. The main goals of the HR VASE project are as follows: Provide managers with current and historical information about employee vacation and sick leave. Grant view rights to individual employees to view their vacation and sick leave balances. Provide permission to selected employees in the HR department to view and update the vacation and sick leave details of employees. Grant the HR manager with the view and update rights to all the data. You need to identify and gather database requirements, and then design a conceptual database model for the HR database based.

  32. Module Review and Takeaways • Review Questions • Real-world Issues and Scenarios • List of Tools

More Related