week2.pdf

Information and Data Model  Data Modeling the process of creating a visual representation of either a whole information system or parts of it to communicate connections between data points and structures. Goals Ensures that all data objects required by the database are ac...

Information and Data Model  Data Modeling the process of creating a visual representation of either a whole information system or parts of it to communicate connections between data points and structures. Goals Ensures that all data objects required by the database are accurately represented. A data model helps design the database at the conceptual, physical, and logical levels. Data Model structure helps to define the relational tables, primary and foreign keys, and stored procedures. It provides a clear picture of the base data and can be used by database developers to create a physical database. Helpful to identify missing and redundant data. It makes your IT infrastructure upgrade and maintenance cheaper and faster. Levels of Data Modeling Conceptual Logical Physical 3 Basic Tenants: Entity Attributes Relationship  Conceptual Model This model aims to establish the entities, their attributes, and their relationships.  Customer and Product are two entities. Customer number and name are attributes of the Customer entity Product name and price are attributes of product entity Sale is the relationship between the customer and product Characteristics of Conceptual Model Offers Organization-wide coverage of the business concepts. Designed and developed for a business audience. Developed independently of hardware specifications like data storage capacity, location or software specifications like DBMS vendor and technology. Focus is to represent data as a user will see it in the "real world."  Logical Model This model adds further information to the conceptual model elements. It defines the structure of the data elements and set the relationships between them.  This model provides a foundation to form the base for the Physical model. But the modeling structure remains generic. At this Data Modeling level, no primary or secondary key is defined. This needs to verify and adjust the connector details that were set earlier for relationships. Characteristics of Logical Model Describes data needs for a single project but could integrate with other logical data models based on the scope of the project. Designed and developed independently from the DBMS. Data attributes will have datatypes with exact precisions and length. Normalization processes to the model is applied typically till 3NF.  Physical Model This model describes the database specific implementation of the data model. It offers an abstraction of the database and helps generate schema.  This type of data model also helps to visualize database structure. It helps to model database columns keys, constraints, indexes, triggers, and other DBMS features. Characteristics of Physical Model: The physical data model describes data need for a single project or application. Data Model contains relationships between tables that which addresses cardinality and nullability of the relationships. Developed for a specific version of a DBMS, location, data storage or technology to be used in the project. Columns should have exact datatypes, lengths assigned and default values. Primary and Foreign keys, views, indexes, access profiles, and authorizations, etc. are defined. Advantages and Disadvantages of Data Model Advantages Data objects are represented accurately. Detailed enough to be used for building the physical database. Can be used for defining the relationship between tables, primary and foreign keys, and stored procedures. Helps business to communicate the within and across organizations. Helps to recognize correct sources of data to populate the model Disadvantages To develop Data model, one should know physical data stored characteristics. Produces complex application development, management. Thus, it requires a knowledge of the biographical truth. Even smaller change made in structure require modification in the entire application. There is no set data manipulation language in DBMS. Entity-Relation Model Features of ER Model Graphical Representation: It is very easy and simple to understand so it can be used by the developers to communicate with the stakeholders. ER Diagram: used as a visual tool for representing the model. Database Design: This model helps the database designers to build the database and is widely used in database design. Advantages Simple: Conceptually ER Model is very easy to build. Effective Communication Tool: This model is used widely by the database designers for communicating their ideas. Easy Conversion to any Model: This model maps well to the relational model and can be easily converted relational model by converting the ER model to the table. Disadvantages No industry standard for notation: There is no industry standard for developing an ER model. So, one developer might use notations which are not understood by other developers. Hidden information: Some information might be lost or hidden in the ER model. As it is a high-level view so there are chances that some details of information might be hidden. ER Diagram Importance of ERD Helps define terms related to entity relationship modeling Provide a preview of how all your tables should connect, what fields are going to be on each table Helps to describe entities, attributes, relationships ER diagrams are translatable into relational tables which allows you to build databases quickly ER diagrams can be used by database designers as a blueprint for implementing data in specific software applications The database designer gains a better understanding of the information to be contained in the database with the help of ERP diagram ERD is allowed you to communicate with the logical structure of the database to users Components of ERD Entity Attributes Primary Key Foreign Key Relation Cardinality o One-to-One o One-to-Many o Many-to-Many Entity A definable thing or concept within a system, such as a person/role (e.g. Student), object (e.g. Invoice), concept (e.g. Profile) or event (e.g. Transaction) Attributes Also known as a column, an attribute is a property or characteristic of the entity that holds it. Primary Key - Also known as PK, a primary key is a special kind of entity attribute that uniquely defines a record in a database table. Foreign Key - Also known as FK, a foreign key is a reference to a primary key in a table. It is used to identify the relationships between entities. Relationship A relationship between two entities signifies that the two entities are associated with each other. For example, a student might enroll in a course. The entity Student is therefore related to Course, and a relationship is presented as a connector connecting between them. Cardinality defines the possible number of occurrences in one entity which is associated with the number of occurrences in another. One-to-One Cardinality One-to-Many Cardinality Many-to-Many cardinality Steps to Create an ERD In a university, a Student enrolls in Courses. A student must be assigned to at least one or more Courses. Each course is taught by a single Professor. To maintain instruction quality, a Professor can deliver only one course Entity Identification We have three entities Student Course Professor Relationship Identification We have the following two relationships The student is assigned a course Professor delivers a course Cardinality Identification For them problem statement we know that, A student can be assigned multiple courses A Professor can deliver only one course Identify Attributes You need to study the files, forms, reports, data currently maintained by the organization to identify attributes. You can also conduct interviews with various stakeholders to identify entities. Create the ERD A more modern representation of ERD Diagram Best Practices for Developing Effective ER Diagrams Eliminate any redundant entities or relationships You need to make sure that all your entities and relationships are properly labeled There may be various valid approaches to an ER diagram. You need to make sure that the ER diagram supports all the data you need to store You should assure that each entity only appears a single time in the ER diagram Name every relationship, entity, and attribute are represented on your diagram Never connect relationships to each other You should use colors to highlight important portions of the ER diagram