What is Data Dictionary in database system

Data dictionary ppt slide is here for overall understanding

1. Data Dictionary The data dictionary also known as data repository or system catalog ,consists of two words, data, which represents data collected from several sources, and dictionary, which represents where this data is available. The data dictionary is an important part of the relational database because it provides additional information about the relationship between several tables in the database. A data dictionary in a DBMS helps users manage data in an orderly and orderly manner, thereby preventing data redundancy. There is less information and details provided by data models. So, a data dictionary is essential and needed to have proper knowledge and usage of contents. Data Dictionary provides all information about names that are used in system models. Data Dictionary also provides information about  entity, relationship, and attributes, that are present in the system model .

2. Field Name Data Type Field Size for Display Description Example EmployeeID Integer 8 Unique ID of each employee 100025 FullName Text 30 Full name of the employee Emily Johnson DOB Date/Time 10 Date of birth of employee 1990-05-15 PhoneNumber Integer 10 Phone number of employee 555-123-456 Below is a data dictionary that describes the table that contains employee details.

3. Some of the advantages of using a data dictionary are: In DBMS, the  data model provides very little information about the database, so the data dictionary is very important to get the right knowledge about the entities, relationships, and attributes that exist in the data model. The data dictionary provides consistency by reducing data redundancy in data collection and use among different team members. Data dictionaries provide structured analysis and design tools by implementing data standards. Data standards are sets of rules that govern the collection, recording, and presentation of data. Using a data dictionary helps define the naming convention used in the model.

4. Types Of Data Dictionary There are basically two types of data dictionaries in a database management system: Integrated Data Dictionary Stand Alone Data Dictionary Integrated Data Dictionary Every relational database has an Integrated Data Dictionary available in the DBMS. This integrated data dictionary acts as a system directory that is accessed and updated by the relational database. The old database does not have an integrated data dictionary, so the database administrator must use the Stand Alone Data Dictionary. An Integrated Data Dictionary in a DBMS can link metadata. The integrated data dictionary can be further divided into two types :

5. Active : When any changes are made to the database, the active data dictionary is automatically updated by the DBMS. It is also known as a self-updating dictionary because it continuously updates its data. Passive : Unlike active dictionaries, passive dictionaries must be updated manually when there are changes in the database. This type of data dictionary is difficult to manage because it requires proper functionality. Else, the database and data dictionary will be synchronized . Stand Alone Data Dictionary This type of database in the DBMS is very adaptive because it grants the administrator in charge of the confidential information complete autonomy to define and manage all crucial data. Whether the information is printed or not has nothing to do with it. A data dictionary that has a stand-alone format enables database designers to have the flexibility to communicate with end users regardless of their data dictionaries format.

6. There is no standard format for data dictionaries. Here are some common elements: Data Elements: The data dictionary describes each data element by specifying the names, data types, storage formats and validation rules. Table: All information about the table, such as the user who created the table, the number of rows and columns, the date the table was created and entered, etc. Indexes: Indexes for database tables are stored in the data dictionary. The DBMS stores the index name used and index attributes, locations, and properties, as well as the creation date, in each index. Programs: Applications defined for database access, reports, application formats and screens, SQL queries, etc. also stored in the data dictionary. Relationships between data elements: A data dictionary stores relationship types; for example, if it is mandatory or optional, the nature of the relationship and connection, etc. Administration and End Users: The data dictionary stores all administrative and end user data.

7. Metadata in a DBMS, stored in a data dictionary, is like a monitor that controls database usage and whether users are allowed to access the database. Disadvantages of Data Dictionary Data dictionaries have little functional detail. Non-technical users may experience difficulties when using the data dictionary for the first time. Data dictionary relational diagrams are often not visible.

8. Data model The term “data model” refers to the way data is organized, documented, and defined within a database. A data model in DBMS is a set of concepts and rules that are used to describe and organize the data in a database. It defines the structure, relationships, and constraints of the data, and provides a way to access and manipulate the data. Different data models are used to represent different types of data and relationships, and each has its own set of advantages and disadvantages. It defines how data is stored, arranged, and accessed in a database system. The main components of a data model include entities, attributes, relationships, and constraints.

9. Key Components of a Data Model: Entities :  Objects or concepts in the real world that are represented in the database. For example, in a university database, entities could be students, courses, professors, etc. Attributes:  Characteristics or properties of entities. For instance, a student entity may have attributes such as student ID, name, date of birth, etc. Relationships:  Associations between entities. These define how entities are related to each other. For example, a student entity can be related to a course entity through an enrollment relationship. Constraints:  Rules that define the valid values and relationships for data elements. Constraints ensure data integrity and enforce rules such as uniqueness (e.g., unique student IDs) and referential integrity (e.g., ensuring that every course enrollment is associated with an existing student and course).

10. Types of data models:

11. Advantages or Benefits of Data Models: Data models are essential in database design and management for several reasons: Clarity and Understanding:  They provide a clear and structured way to understand the organization and relationships of data within a database system. This clarity helps both developers and users comprehend how data is structured and accessed. Database Design:  Data models serve as a blueprint for designing databases. They guide the creation of database schemas, tables, relationships, and constraints based on the requirements of the application or business domain. Communication:  Data models facilitate communication among stakeholders such as developers, database administrators, and business analysts. They provide a common language and visual representation to discuss and refine data requirements and structures.

12. Advantages or Benefits of Data Models(Con..) Data Integrity and Consistency:  By defining constraints and relationships, data models ensure that data stored in the database is accurate, consistent, and reliable. This helps in maintaining data quality over time. Scalability and Performance:  Well-designed data models contribute to efficient data retrieval and manipulation operations. They optimize database performance by organizing data in a manner that aligns with the typical queries and operations performed on the data. Flexibility and Adaptability:  Data models can evolve over time to accommodate changes in business requirements or technological advancements. They provide a foundation that can be modified or extended as needed without disrupting existing data structures and applications.

13.  Conceptual Data Model The conceptual data model describes the database at a very high level and is useful to understand the needs or requirements of the database. It is this model, that is used in the requirement-gathering process i.e. before the Database Designers start making a particular database. One such popular model is the  entity relationship model.The E/R model specializes in entities, relationships, and even attributes that are used by database designers. In terms of this concept, a discussion can be made even with non-computer science(non-technical) users and stakeholders, and their requirements can be understood. Entity-Relationship Model( ER Model):  It is a high-level data model which is used to define the data and the relationships between them. It is basically a conceptual design of any database which is easy to design the view of data.

14.  Conceptual Data Model(con…) Components of ER Model: Entity:  An entity is referred to as a real-world object. It can be a name, place, object, class, etc. These are represented by a rectangle in an ER Diagram. Attributes:  An attribute can be defined as the description of the entity. These are represented by Ellipse in an ER Diagram. It can be Age, Roll Number, or Marks for a Student. Relationship:  Relationships are used to define relations among different entities. Diamonds and Rhombus are used to show Relationships.

15. Characteristics of a conceptual data model Offers Organization-wide coverage of the business concepts. This type of Data Models are designed and developed for a business audience. The conceptual model is developed independently of hardware specifications like data storage capacity, location or software specifications like DBMS vendor and technology. The focus is to represent data as a user will see it in the “real world.” Conceptual data models known as Domain models create a common vocabulary for all stakeholders by establishing basic concepts and scope

16. Representational Data Model This type of data model is used to represent only the logical part of the database and does not represent the physical structure of the database. The representational data model allows us to focus primarily, on the design part of the database. A popular representational model is a  Relational model . The relational Model consists of  Relational Algebra  and  Relational Calculus . In the Relational Model, we basically use tables to represent our data and the relationships between them. It is a theoretical concept whose practical implementation is done in Physical Data Model.  The advantage of using a Representational data model is to provide a foundation to form the base for the Physical model.

17. Characteristics of Representational Data Model Represents the logical structure of the database. Relational models like Relational Algebra and Relational Calculus are commonly used. Uses tables to represent data and relationships. Provides a foundation for building the physical data model.

18. Physical Data Model  The physical Data Model is used to practically implement Relational Data Model. Ultimately, all data in a database is stored physically on a secondary storage device such as discs and tapes. This is stored in the form of files, records, and certain other data structures. It has all the information on the format in which the files are present and the structure of the databases, the presence of external data structures, and their relation to each other. Here, we basically save tables in memory so they can be accessed efficiently. In order to come up with a good physical model, we have to work on the relational model in a better way.  Structured Query Language (SQL)  is used to practically implement Relational Algebra. This Data Model describes HOW the system will be implemented using a specific DBMS system. This model is typically created by DBA and developers. The purpose is actual implementation of the database.

19. Characteristics of a physical data model: The physical data model describes data need for a single project or application though it maybe integrated with other physical data models based on project scope. 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