Learning Data Modeling

Expand data model thinking by examining all inputs and outputs and all information sources, not just customer interviews, while including business experts, technologists, and managers.

Learn how UML and IEEE data modeling notations express similar content with different focuses, using class models, tables, keys, and indices to design databases.

Explore conceptual, logical, and physical data models from high-level overviews to tables, keys, and constraints, and learn how UML and IEEE notations complement each other in iterative development.

Clarify how objects and classes relate, showing objects as data and classes as metadata, and build a data model with UML and IEEE notation using attributes and primary keys.

Define domains and data types, showing how domains generalize types and support constraints; create and assign domains such as object identifier, name, long string, money, IEEE physical models.

Learn how UML uses association to relate classes, distinguishing links from associations, and model multiplicities and non-identifying relationship types in a frequent flyer example.

Explore IEEE physical data modeling by distinguishing independent and dependent entity types, identifying versus non-identifying relationships, and propagating primary keys in airline and frequent flyer accounts.

Explore how UML and IEEE differ in association naming, showing when to name associations for clarity, how relationship type names differ, and compare bidirectional traversal to foreign key joins.

Explore UML associations and roles between classes, rename foreign keys using end names to prevent clashes, and distinguish multiple relationships between same classes, such as airline and frequent flyer accounts.

Explore UML multiplicity from one-to-one to many-to-many by modeling a frequent flyer system; show how associations link customers, accounts, activities, statements, and flights.

Explore multiplicity in the IEEE model, illustrating one-to-one, zero-or-one, and many relationships between customers and frequent flyer accounts, and compare IEEE with UML approaches.

Apply UML generalization to connect a superclass with its subclasses, organizing common attributes and specialized information. See flight activity and other activity as examples and compare arrow and tree notations.

Apply generalization in the IEEE model by linking supertype and subtype with discriminator attributes to distinguish flight activity from other activity, propagate primary keys, and tidy the diagram.

Differentiate abstract versus concrete superclasses in UML, noting that abstract supertypes must have corresponding subclasses and appear in italics. Add abstract annotation to the model to enforce a uniform structure.

Clarify scope by understanding the customer's problem and ensure the data model serves customer needs and business rationale, standardize naming, document terminology, and manage associations, multiplicity, and generalization.

Participate in a self-assessment on basic data modeling, exploring why we prepare data models, class-to-entity mappings, and model choices like birth date versus age and single-record designs.

Explore how data model identity works, comparing existence-based primary keys generated by sequences with value-based keys formed from real-world attributes, and discuss their implications for foreign keys and semantics.

Explore how to derive data from other attributes and associations, apply the slash notation in UML, and differentiate derived data from base data using the mileage monthly amount example.

Explore current versus historical data models and time attributes. Decide when to store current addresses versus address histories and use expiration dates for flights.

Learn how UML association classes attach to associations to store attributes such as start and end dates for airline partnerships, and compare UML and IEEE implementations.

Model an ordered association for a flight's sequence of flight legs, linking legs to origin and destination airports in a one-to-many relation, using a sequence number to ensure order.

Define and apply qualified associations in UML by using qualifiers such as account number and statement closing date to distinguish between many related objects, converting many-to-one relationships.

Apply a constraint by adding an alternate key to enforce unique combinations, such as issue id plus account number for a frequent flyer account, and note qualifiers shaping the model.

Explore large taxonomies and multi-level generalizations in data modeling, examining stability, complexity, and development effort, and learn metadata-driven approaches to organize equipment and financial concepts without hard-coded hierarchies.

Learn how large models are organized into packages in UML and IEEE. Use the frequent flyer example to see packages, classes, associations, and cross-package references.

Review the abridged uml model, illustrating classes, associations, association classes, attributes with domains, multiplicity, qualifiers, and generalization from superclass to subclasses.

Explore IEEE relationship types and UML concepts, focusing on directed relationships with propagated primary keys and how identifying targets are dependent or arise from generalization.

Apply practical tips to build clear data models, prioritizing existence-based identity and surrogate keys. Avoid derived data, use UML slash prefixes for derived elements, and keep abstractions simple and symmetric.

Complete the self-assessment by answering 5 UML questions in the working files, then watch the video for answers, covering identity, conferences, calls for papers, and movie roles.

Shift to data modeling by examining requirements inputs, building an online ordering model for Tim's sandwich shop with multi-location menus, customizable sandwiches, pricing with discounts, delivery and credit card payments.

Design a data model for an online sandwich shop by defining tentative classes such as order, sandwich shop, location, menu, and menu item, with attributes like bread, cheese, and meat.

Explore building a data model for a sandwich shop: establish one-to-many associations among shop, orders, locations, menus, and items, incorporate an association class for discounts, and keep the current menu.

Model optional delivery with diverse delivery types for orders, ensure address and payment associations are captured, and link customers to multiple credit cards and payments.

Add generalization to the sandwich shop model by making menu item the superclass with beverage, salad, soup, and sandwich as subclasses, illustrating top-down and bottom-up generalization.

Iterate the data model by introducing an order item class to capture quantities and costs, including discounts, taxes, tips, and delivery fees.

Refine the sandwich shop model by abstracting items, ingredients, and ingredient choices, storing soft-coded ingredient data in the database, and enforcing constraints in code to prevent incorrect orders.

Extend the data model by adding customer and order attributes, including delivery type, contact details, addresses, encrypted password, derived totals, and credit card data, while noting CVV is not stored.

Clean up the data model layout by aligning items and simplifying diagrams. Use left and right clicks, straight-line cursors, and center alignments to minimize bends and improve readability.

Identify and implement simplifications in data modeling by eliminating menu item subclasses and storing ingredients as master data, balancing a smaller, clearer model with flexible database design.

Evolve data models to mirror reality by borrowing ideas from other apps, supporting account and ad hoc customers with preferred addresses and credit cards.

Evolve the data model by adding ad hoc and account customer subclasses, defining cards and addresses, and clarifying preferred items with application-enforced constraints.

Create a blank enterprise architect data model in a project and seed a UML class model. Learn how the project browser and diagram panel keep packages and classes in sync.

Create and organize packages and diagrams in Enterprise Architect, add flight and airline packages with classes, and define a many-to-many association and a one-to-many link between airline and flight.

Discover how Enterprise Architect manages ownership of classes, associations, notes by package, the many-to-many multiplicity for association classes, and how deletions ripple across diagrams and packages.

Learn how to split a large UML and IEEE data model into two subject areas, order and order item, to manage closely coupled types and define ownership.

Map the UML model to the NIIT model by creating ID entity types for UML classes and associations, then place them in order and order item areas.

Explore creating domains for a sandwich shop data model in Erwin, selecting a target database, defining domains for identifiers, names, contact details, accounts, and menu items with constraints.

Add attributes to the UML model, set domains and implicit identifiers as primary keys, and propagate keys from customer to super types across related entities.

Continue adding attributes to the order item area, including quantity, amount, and keys, using domain names and object identifiers, while demonstrating how domain definitions propagate data types across UML entities.

Develop non identifying relationship types from the UML model, like one-to-many customers to orders and optional associations such as preferred addresses and credit cards, with explicit multiplicity.

Review relationship types and multiplicities in UML and IEEE models to ensure primary key propagation and correct address location associations.

Explore creating relationship types in the order items subject area, including a many-to-many relation via an intervening identifying entity, and observe key propagation and ownership across Irwin and ieee models.

Explore subtyping from customers to ad hoc and account customers, propagate primary key and attributes like address and credit card, and enforce a discriminator for customer type in database design.

Explores adding alternate keys as unique combinations of attributes for an entity, ensuring not null values, with examples such as delivery type name, sandwich shop name, and menu name.

Trim rounded lines and move the address to improve readability, then align diagram lines vertically for a cleaner IEEE model with improved layout in the URWIN working files.

Explore erwin techniques from creating a blank erwin data model to navigating logical and physical views, mastering reverse engineering, and selecting target databases such as sql server 2012.

Explore ERwin techniques for efficient data modeling, including zoom to fit the model, copying entity types, and moving attributes across subtypes and subject areas.

Explore how data model quality shapes database structure, data accuracy, and evolvability, using normal form guidelines, constraints, and graph-based complexity to assess and improve relational designs.

Explore normal forms as guidelines for relational database design that keep data consistent and reduce redundancy, illustrated with a Tim's sandwich shop model and IEEE examples.

Learn to model database constraints by distinguishing implicit and explicit constraints, capturing them in models with qualifiers, generalization discriminators, and alternate keys, and enforce rules via application logic and SQL.

Explore Hillard graph complexity, treating a data model as an undirected graph with tables as nodes and edges. Compute average degree and geodesic distance for a frequent flyer model.

Explore Hoberman's data model scorecard, a ten-question, 0–100 framework with weighted scoring, applied to the Tymms sandwich shop to evaluate normalization, requirements, readability, and naming standards.

Explore operational data models that power business apps and OLTP workloads. Use UML for abstraction and IEEE for database design as you evolve a UML model into an IEEE model.

Develop an enterprise data model to unify applications and reconcile conflicting data. Capture core concepts like product, customer, holding, and activity for scalable integration.

Discover how a data warehouse consolidates data from multiple operational applications into a denormalized, history-rich store for online analytical processing and reporting, using ETL scripts for scheduled loads.

Explore the star schema in data warehouses, organizing facts and dimensions for fast queries. Understand grain, surrogate keys, conformed dimensions, and historical data for accurate, aggregated measures.

Discover how data warehouses enable insight with star schema and data marts, bus architecture for conformed dimensions, and facts modeled at the lowest level of detail for cross-fact queries.

Master data models standardize and cleanse data to create a single version of the truth, unifying data across applications and enabling easier application integration.

Learn to evolve data models from conceptual, logical, to physical designs using UML and IEEE notations, and implement with the URWIN database design tool for a sandwich shop.

Explore how domains define data types for attributes, enforce nullability for primary and alternate keys, and manage attributes in the order subject area, including customer details and delivery type.

Extend data modeling by adding order item name to the order item, clarify null versus menu item name, and emphasize consistent approaches to avoid bugs.

Define default values for attributes, such as order date time using get date, and generate code for tables and attributes from the physical model.

Define primary keys as existence based identity and explain alternate keys as uniqueness constraints, then show implementing them in create table statements and alter table with identity generation.

Discover how indexes tune a relational database, speeding lookups and enforcing uniqueness, while slowing writes by twenty to thirty percent and boosting foreign key joins.

Learn how referential integrity uses foreign key references to primary keys to stay in sync, and configure delete and update actions such as cascade, restrict, set null, or set default.

Set referential integrity actions for the sandwich shop model, applying cascade and no action rules, and generate the related SQL alter statements for orders, menus, and metadata.

Address multiplicity in the Erwin and IEEE models by making account customer relationships to preferred address and preferred credit card optional, aligning with referential integrity and domain and check constraints.

Add attribute constraints for credit card expiration dates, pickup times, and minimum order item quantity. Apply table constraints to require order item names and a phone or email from customers.

Explore database views as virtual tables computed from SQL queries, enabling security views and convenience views, including ad hoc and generalized views that reconstitute normalized data.

Explore advanced design topics such as schema creation for iterative development, drop scripts and referential integrity, and practical considerations for permissions, triggers, and stored procedures within data modeling.

Answer self assessment questions on database design, covering foreign keys, primary and alternate keys, splitting and combining tables to optimize the schema, indexing strategies, referential integrity, and performance tuning.

Install sequel server, set up server management studio, explore system databases and tables to understand columns, data types, and security, then create a new database named Tim's sandwich shop.

Execute the schema by copying it into a sql server query window and running it, then delete and rerun if errors occur, and use a beautifier to format the code.

Explore how the object explorer reveals tables, keys, constraints, statistics, and indices in the sandwich shop database, including primary and foreign keys and query optimizer insights.

Load sample data for the sandwich shop database by applying schema revisions for account customers and ad hoc customers, then run sequential SQL scripts to populate tables.

Explore querying sample data from a sandwich shop data model, using UML and IEEE models to connect locations, menu items, item ingredients, and orders with pricing and discounts.