Introduction to Database Session 3.pptx

Introduction to Database P R O F K A L LO L D A S Session 3 DATA MODELLING Purpose of Data Model Data modeling occurs in the requirements analysis. A data model is a plan, or blueprint, for a database design—it is a generalized, non-DBMS specific design. Changing a relationship during the da...

Introduction to Database P R O F K A L LO L D A S Session 3 DATA MODELLING Purpose of Data Model Data modeling occurs in the requirements analysis. A data model is a plan, or blueprint, for a database design—it is a generalized, non-DBMS specific design. Changing a relationship during the data modeling stage is just a matter of changing the diagram and related documentation. Changing a relationship after the database and applications have been constructed, however, is much more difficult.  Data must be migrated to the new structure, SQL statements will need to be changed, forms and reports will need to be altered, and so forth The Entity-Relationship Model  Entity--- a class of persons, places, objects, events, or concepts about which we need to capture and store data.  Entity Class: a class of persons, places, objects, events, or concepts about which we need to capture and store data. For example, EMPLOYEE entity class is the collection of all EMPLOYEE entities.  Entity Instance: An entity instance of an entity class is the occurrence of a particular entity, such as CUSTOMER 12345.  An entity class usually has many instances of an entity. For example, the entity class CUSTOMER has many instances—one for each customer represented in the database.  Attributes— Entities have attributes that describe their characteristics. Examples of attributes are EmployeeNumber, EmployeeName, Phone and Email ID. The E-R model assumes that all instances of a given entity class have the same attributes.  Identifiers— Entity instances have identifiers, which are attributes that name, or identify, entity instances. For example, EMPLOYEE instances can be identified by EmployeeNumber, SocialSecurityNumber. Similarly, customers can be identified by CustomerNumber or CustomerName, and sales orders can be identified by OrderNumber. E-R Model Cont.….  Relationships—  Entities can be associated with one another in relationships. The E-R model contains both relationship classes and relationship instances.  Relationship classes are associations among entity classes, and relationship instances are associations among entity instances.  A relationship class can involve two or more entity classes. The number of entity classes in the 1: relationship is the degree of the relationship. 1  Qualification relationship is of degree two because it involves two entity classes: EMPLOYEE and SKILL. This is binary relationships.  A relationship between different instances of the same entity is called a recursive relationship also called a unary relationship. It occurs when an entity type has a relationship to itself.  Three types of recursive relationships are possible: 1:1, 1:N, and N:M.  Assignment relationship is of degree three because it involves three entity classes: CLIENT, 1:N ARCHITECT, and PROJECT. This is ternary relationships. N: M  When transforming a data model into a relational database design, relationships of all degrees are treated as combinations of binary relationships.  The Assignment relationship in the adjacent figure is decomposed into three binary relationships  Cardinality  Cardinality: In the entity-relationship model, relationships are classified by their cardinality, a word that means “count.”  Maximum Cardinality: the maximum number of entity instances that can participate in a relationship instance.  There are the three basic maximum cardinalities in the E-R model.  one-to-one (abbreviated 1:1) relationship: an entity instance of one type is related to at most one entity instance of the other type. one EMPLOYEE instance with one BADGE instance  one-to-many (abbreviated 1:N) relationship: a single instance of EMPLOYEE can be associated with many instances of COMPUTER, but a COMPUTER instance is associated with just one instance of EMPLOYEE.  many-to-many (abbreviated N:M) relationship: This is also called as Nonspecific relationship. Nonspecific relationships must be resolved. Most nonspecific relationships can be resolved by introducing an associative entity. Qualification relationship, an EMPLOYEE instance can be associated with many SKILL instances, and a SKILL instance can be associated with many EMPLOYEE instances.  Minimum Cardinality: the minimum number of entity instances that must participate in a relationship instance. Cardinality Cont.…  In the Employee_Identity relationship in the ‘I’ marks indicate that an EMPLOYEE is required to have a BADGE, and a BADGE must be allocated to an EMPLOYEE.  Such a relationship is referred to as a mandatory-to-mandatory (M-M) relationship because entities are required on both sides.  The Computer_Assignment relation ship is an optional-to-optional (O-O) relationship. This means that an EMPLOYEE need not have a COMPUTER, and a COMPUTER need not be assigned to an EMPLOYEE. The Computer_Assignment relationship is thus a 1:N, O-O relationship.  The Qualification relationship is thus an N:M, optional to- mandatory (O-M) relationship. Here an EMPLOYEE must be assigned to at least one SKILL, but a SKILL may not necessarily be related to any EMPLOYEE. Because the circle is in front of EMPLOYEE, it means that the employee is optional in the relationship. E-R Diagrams Using the IE Crow’s Foot Model Data Modelling Concepts: Identification Candidate key – one of a number of keys that may serve as the primary key of an entity. Also called a candidate identifier. Primary key – a candidate key that will most commonly be used to uniquely identify a single entity instance. Alternate key – a candidate key that is not selected to become the primary key is called an alternate key. A synonym is secondary key. Surrogate Key –  A surrogate key is a DBMS-supplied identifier of each row of a table.  Surrogate key values are unique within the table, and they never change.  Surrogate key are assigned when the row is created, and they are destroyed when the row is deleted.  Data Modelling Concepts: Identification Cont.…. Foreign key – a primary key of an entity that is used in another entity to identify instances of a relationship. Action with Foreign Key Constraint - The ON UPDATE and ON DELETE specify which action will execute when a row in the parent table is updated and deleted. The following are permitted actions :  NO ACTION: SQL server raises an error and rolls back the update/delete action on the row in the parent table.  CASCADE: SQL server deletes/updates the rows in the child table that is corresponding to the row deleted/updated from the parent table  SET NULL: SQL server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted/updated. Note that to execute this action the foreign key columns must be nullable.  SET DEFAULT: SQL server sets the rows in child table to their default values if the corresponding rows in the parent table are deleted/updated. To execute this action, the foreign key columns must have default definitions. Note that nullable column has a default value of NULL if no default value specified By default, SQL server applies ON DELETE ACTION if not explicitly specified. Strong Entities and Weak Entities A strong entity is an entity that represents something that can exist on its own. E.g. PERSON, AUTOMOBILE Weak entity is defined as any entity whose existence depends on the presence of another entity.  ID-Dependent Entities: An ID dependent entity is an entity whose identifier includes the identifier of another entity. The identifier of such an entity is a composite (BuildingName, ApartmentNumber)  Non-ID-Dependent Weak Entities: Some entities that are weak are not ID-dependent.  Each VEHICLE is assigned a sequential number as it is manufactured.  VEHICLE has an identity of its own and therefore is not ID- dependent. Yet the VEHICLE is an AUTO_MODEL, and if that particular AUTO_MODEL did not exist, the VEHICLE itself would never have existed. Therefore, VEHICLE is now a weak but non-ID- dependent entity. The ambiguity of the Weak Entity If a STUDENT must have an ADVISER, then STUDENT is a weak entity because a STUDENT entity cannot be stored without an ADVISER. A STUDENT is not physically dependent on an ADVISER (unlike an APARTMENT to a BUILDING) A STUDENT is not logically dependent on an ADVISER Therefore, STUDENT should be considered a strong entity. APARTMENT is a weak entity, but STUDENT is not. An APARTMENT cannot exist without a BUILDING in which it is located. However, a STUDENT can logically exist without an ADVISER, even if a business rule requires it. Summarization of Weak Entity To summarize: A weak entity is an entity whose existence depends on another entity. An ID-dependent entity is a weak entity whose identifier includes the identifier of another entity. Indentifying relationships are used to represent ID-dependent entities. Some entities are weak, but not ID-dependent. Using data modeling tools, they are shown with nonidentifying relationships, with separate documentation indicating they are weak.  Data Modeling Concepts: Specialization and Generalization Specialization : In some situations, an entity type may include sub-groupings of its entities in such a way that entities of one subgroup are distinct in some way from the entities of other subgroups. The entity type BOOK can be classified further into three types, namely, TEXTBOOK, LANGUAGE_BOOK, and NOVEL. These entity types are described by a set of attributes that includes all the attributes of the entity type BOOK and some additional set of attributes that differentiate them from each other. These additional attributes are also known as local or specific attributes. The entity type containing the common attributes is known as the superclass. BOOK is a superclass. The entity type, which is a subset of the superclass, is known as its subclass. TEXTBOOK, LANGUAGE_BOOK, and NOVEL are the subclasses. This process of refining the higher-level entity types (superclass) into lower-level entity types (subclass) by adding some additional features to each of them is a top-down design approach. Generalization : This design process is reverse of Specialization. It is a bottom-up approach in which multiple lower-level entity types are combined on the basis of common features to form higher-level entity types. The database designer may first identify the entity types TEXTBOOK, LANGUAGE_BOOK, and NOVEL and then combine the common attributes of these entity types to form a higher-level entity type BOOK. Generalization Hierarchy:  An entity that is a member of a subclass inherits all the attributes of the entity as a member of the superclass.  It also inherits all the relationships in which the superclass participates.  Table Representation:  Three tables:  ACCOUNT (acc_no, balance)  SAVINGS_ACC (acc_no, int_rate)  CURR_ACC (acc_no, overdraft_amt)  In case of disjoint generalization, we can safely reduce this to two tables  SAVINGS_ACC (acc_no, int_rate, balance)  CURR_ACC (acc_no, overdraft_amt, balance) Thank You