Database Management System Assignment: DBMS Fundamentals

Question

Task: Students are required to analyse the weekly lecture material of weeks 1 to 11 by creating concise summaries of the theoretical concepts contained in the course lecture slides and providing their own reflections over it.

Answer

Introduction and Background to the theme of database management system assignment:
Week-1 Theory, Concepts, and Outcomes:
Important Topics Covered in this database management system assignment:
1. Data and Information comparison
2. Database types, design and their importance in decision making
3. Difference between Database and file system
4. Function of DBMS
5. Role of open source database systems
6. Data Governance and Data Quality

Definitions:
Data Quality and Governance: Herein database management system assignment, data quality is meant by the accuracy, relevancy, uniqueness, integrity, security, accessibility, and reliability of data, which free from any anomalies and redundancies, Data Governance is the strategies and policies defined by an organization in order to ensure data quality. Database: Database is an integrated structure that stores user metadata in the form of a table containing attributes and tuples to store data in a systematic way (structure) to simplify user access to the data. DBMS: As per the research on database management system assignment, the database management system is the collection of programs that define, manipulate, abstract, retrieves, and efficiently manages data in a database and provide an understandable version of havoc data to the application user.

Reflection:
For instance, if we take an example of an e-commerce system selling products from multiple 3rd party merchants to the end-user customers, a DBMS will be required to store, record, maintain (update), eliminate inconsistency, enable the user to quickly and accurately access data by retrieving the user logical data from the client end, and processing the query request (physical data format) from the server end and quickly sending the results back to the client end-user.

Outcome:
Throughout this database management system assignment, we have learned the importance of Database design, data abstraction, anomalies, redundancies, the importance of the use of DBMS over traditional file systems for storing and using data. How poorly designed databases can give rise to confusion leading to catastrophic consequences to an organization. Week-2 Theory, Concepts, and Outcomes:

Important Topics Covered within this database management system assignment:
1. Data Models evolution, classification and basic building blocks (components)
2. Business rules and their influence in database design

Definitions:
Data Models: As discussed in the database management system assignment, it represents the logical structure of a database consisting of the entity, attribute, relationship, and constraint into a real-world model by simplifying the complex data structures into an easier format. It defines how data are stored and processed inside the Database system and act as fundamental entities that enforce data abstraction in DBMS.

Basic building blocks: entity attributes relationship and constrains
Data Model evolution &classification: Hierarchical, Network, Relational, Entity Relationship, and Object-Oriented Business Rules: The database management system assignmentexamines that it is a set of precise and unambiguous descriptions of policies, procedures, or principles within a specific organization that is recorded and updated for maintaining the data quality and data governance in the organization.

Reflection:
The Ecommerce system that we discussed earlier needs to be compared with the real-world scenario to simplify it, in terms of both the users and the programmers, which can be achieved with the help of data models and robust business rules of the company. Data models explored in the database management system assignment hide the non-important data through different level (degrees) of data abstraction among users andprogrammers to simplify the database system and make it consistent while performing similar and repetitive operations to produce detailed query results,

Outcome obtained in the database management system assignment:
The importance of data models and business rules in an organization with the evolution of the data models, such as hierarchical model which was traditional data model with a lot of limitations is replaced by the network, relational and entity relational models which introduced the concept of schema, attributes, SQL, ERD’s which are still used to handle huge user data and reduce data inconsistency, but when the user data becomes enormous and even more complex to record and access, the final data model called the Object-oriented data (semantic data) model is introduced which is the basic foundation of modern programming, processing, and representation of data.

Week-3 Theory, Concepts, and Outcomes:
What are the important topics covered within this database management system assignment?
1. Relational Database Model- basic components, sub-components, how redundancy is handled and importance of indexing
2. Relations (Tables in DBMS) and its constituents
3. Relational database operators, data dictionary, and system catalogue

Definitions:
Table or Relations: As noted herein database management system assignment, it is the standard data structure consisting of attributes (columns) and tuples (rows) that logically represent data and records in a 2D structure and also forms as a medium to connect other relations in the database. The set of attributes (Keys) inside a relation plays a major role in identifying, accessing, processing data and information within the database environment.

Keys: Keys are the set of attributes that are the fundamental constituents that give meaning to the relational schema. Different type of keys mentioned in the context of database management system assignment such as primary key, composite key, super key, candidate key, foreign key, and secondary key serves different functions and defines the relational database while making the database design consistent.

Data Dictionary:A data dictionary or system catalogdefines different objects within a database, it helps the programmer or user to access all the relational database relationships in a hierarchical manner.

Reflections:
In terms of the E-commerce system that we have considered, given the enormous data that needs to be stored, processed, and utilized in a systematic manner, an object-oriented approach is must to reduce data redundancy in relations and using the keys properly for the right use of the database system. For instance, the customer records will be present in multiple relations such as registration information, order information, delivery information; the idea is to efficiently store and access the same data without creating inconsistencies.

Outcome obtained in the database management system assignment:
A good database design is the most important aspect in DBMS, which forms its foundation and depends upon comprehension of relevant entities, attributes, and relationships among entities. It is mentioned in this database management system assignment that this can be achieved by representing the entire database through the Entity-Relationship Diagrams (ERD).

Week-5 Theory, Concepts, and Outcomes:
Important Topics Covered within this database management system assignment:
1. Entity Relationship Components
2. Definition of Entity and its roll in database design
3. ERD components role in the implementation of database design
4. Compromises that the DB designers have to make due to the requirement of reconciliation of conflicting goals in the database design

Definitions:
Attributes:Attributes are the characteristics of entities that are generally atomic in nature (cannot be further divided into subparts), for instance, Customer is an entity to which customer_id, name, gender, contact information, address, etc are attributes. Attributes type ranges from the composite attribute, simple attribute, single-value attribute, multi-valued attributes, and their characteristics define their importance in Entity relationships.

Relationships: It forms the association between different entities by linking disparate data items in the context of databases, relationships exist among two relational database tables and ideally operates in both directions, ie: 1:1. 1:M, M:1, M: M. Its classification is difficult to establish as only one side of the relationship is known.A relationship is established through connectivity and cardinality along with the business rules. Relationship lifetime is dependent on the existence of entities. The strength of a relationship is defined by the Primary Key’s existence in the parent key entity.

Reflections:
As per the scenario of database management system assignment, the eCommerce platform depends upon the attributes and relationships for the entire thing to work flawlessly. For instance, the relationship between several tables such as products, customers_info, customer_order info, delivery_details, inventory_details (from where the products purchase data will be updated) are formed and maintained through the integrity of the entities and their respected relationships with different tables.

Outcome:
The objective of this chapter of database management system assignment is to reveal the importance of attributes, entities, relationships,relationship strengths, degrees, participation, dependencies, etc in databases. The above concepts apparently define the complexities of the database management system and its efficiency in strengthening the organization data with correct utilization of these concepts while database design and maintenance.

Week-6 Theory, Concepts, and Outcomes:
Important Topics Covered:
1. Extended Entity Relationship
2. Use of Entity Clusters
3. Selection of Primary Keys
4. Special Data Modelling Cases
5. Developing Data Models based on EER diagrams

Definitions:
Extended Entity-Relationship Model:EERM is a conceptual data model that integrates the extensions to the original ER model in the form of semantic constructs that were used in database design. To represent an EERM, an EER diagram (EERD) is used. Inheritance:It is an important feature of Generalization (1:M) and Specialization(M:1) entity-relationship. It allows lower-level entities to inherit the attributes of higher-level entities. This concept is derived from the basic object-oriented approach to programming. It enables entity subtype to inherit attributes and relationships of supertype (entity type that is related to single or multiple entity types having similar traits. At the implementation level, supertype) and its subtype(s) maintain a 1:1 relationship.

Entity Integrity: The integrity of the entity dependson the choice of primary keys and determines the efficiency and effectiveness of the Database, The primary key and foreign key collaboratively functions to implement relationships.

Reflection:
As per the scenario the e-commerce platform must be built upon the solid foundation of a flexible database design, thorough knowledge of data modeling cases is a requisite for the designer and developer of the system. All the special cases should be taken into account before implementation as huge systems such as E-commerce platforms requires a lot of data processing, and backup of an efficient database without which it may never return relevant results to customer queries with an increase in data anomalies thereby making the system inconsistent.

Outcome:
The objective of this chapter is to comprehend the complex data models with the help of specialization and generalization of attributes, use of relevant primary keys, and entity types, forming hierarchical relationships with the use of these keys to foster the special data modeling cases.

Week-7 Theory, Concepts, and Outcomes:
Important Topics Covered within this database management system assignment:
1. Introduction to Normalization and its role in Database design
2. Use of Normalization, ER modeling concurrently to enhance the database design
3. Use of denormalization in special cases to efficiently generate information

Definitions:
Normalization: Normalization is the process of inspecting and rectifying drawbacks in table structures to reduce data redundancies by minimizing data anomalies. It is enabled through dividing it into five forms, 1NF, 2NF, 3NF, 4NF, and BCNF. The process is defined as each table representing a single subject, the same data is eliminated from multiple tables and only one copy is stored in a single table, all attributes depends on the primary key, each table void of insertion, update, deletion anomalies (Vathy-Fogarassy and Hugyák, 2017).

Reflection:
For huge databases to maintain enormous records of an Ecommerce business, the tables designed should be normalized to all levels of normalization in order to remove all kinds of data anomalies and inconsistencies. By evaluating the primary key assignments, naming conventions, derived attributes, refining attribute atomicity, new attributes and relationships identification, and maintaining historical accuracy the overall database structure can be enhanced for increased efficiency and consistency of the entire system.

Outcome:
The objective noted herein database management system assignment was to introduce an important concept called normalization in database systems to further improve the database designs by constantly evaluating the relationships and normalize them through different levels of normalization to make them free from anomalies that continue the process till all the remaining anomalies are eliminated from the database.

Week-8 Theory, Concepts, and Outcomes:
Important Topics Covered in the present context of database management system assignment:
1. Introduction to Structured Query Language
2. Use of SQL by DBA’s
3. Role of SQL in data manipulation
4. Trigger an SQL query to retrieve relevant information

Definitions:
SQL: Structured query language is a domain-specific language used in programming and supports most of the programming languagesto define, manipulate and use data in the relational database management system (RDBMS), it is further classified into two basic categories:

DDL (Data Definition Language) and DML (Data Manipulation Language). The best part of SQL is it easy to implement due to the simple syntax and it uses basic commands to operate and categorized as a non-procedural language (Wylotet al. 2018).

Database Schema: It is the basic structure of the entire database that depicts the logical view of the database. It formulates the organization of data and association of relations among them. It defines all the constraints that need to be applied to the data. Authentication is required in order to access the database, which is controlled by the DBA, who assigns registered users and validates their id, each time they request access.

Reflection:
As per the given scenario SQL act as the medium for the Ecommerce website that enables the communication between the client end-user interface application, server end programming language and the Database that receives the request sent by the front end user through the application software via the server end layer, which triggers relevant functions to access certain information and process it with the available resources and send it back to the client end. From ordering a certain product to making the final payments, every event trigger specific functions in the DBMS to record and process data accordingly to fulfill the request.

Outcome:
The objective of this chapter is to define the power of the introduction of a non-procedural language in DBMS to handle complex queries efficiently and with minimum processing time. SQL is not only easy to apply in a different programming language but also a powerful database tool for efficient data handling in complex RDBMS through different SQLcommands.

Week-9 Theory, Concepts, and Outcomes:
Important Topics Covered:
1. Use of database design to synergize with the information system.
2. To follow the SDLC framework for implementing successful information systems
3. Database Life Cycle (DBLC)
4. Conducting evaluation and revision in SDLC and DBLC frameworks
5. Role of DBA

Definitions:
SDLC: System Development Life Cycle model is a framework used in information systems for efficient management of the system following an object-oriented approach towards creation, development, and maintenance of the database (Wu, Sakr and Zhu, 2017). It is useful for an organization that wants to implement a complex database with huge data and records to use SDLC for designing the database. SDLC has five phases, planning, analysis, detailed systems design, implementation, and maintenance. It is an iterative approach towards the development and maintenance of the DBMS that is repeated throughout the entire DBLC.

DBLC: The Database Life Cycle has six phases, database initial study, database design, implementation and loading, testing and evaluation, operation, and maintenance and evolution. It goes in order with SDLC.

Reflection:
For large real-life applications like e-commerce websites where multiple users log on at a time and access data from it, very complex systems are running behind the UI's to serve the purpose while optimizing the results of the end-user requests with efficient DBMS working behind the servers powered by efficient frameworks such as SDLC that uses the Object-oriented concepts to maintain the database system.

Outcome: The objective of the database management system assignmentis to learn about the SDLC framework in DBMS, by breaking the problems and constraints of an organization with different views (business and designer view), identifying the problems, objectives, scopes and limitations of the database, before planning the design phase (keeping the basic design overviews in mind, such as logical and physical design), database security measures, testing and evaluation of the DBMS, operation, maintenance and evolution phase to further enhance it. The Role of DBA in implementing and enforcing company standards and administrating utilities and applications.

References:
Vathy-Fogarassy, Á. and Hugyák, T., 2017. Uniform data access platform for SQL and NoSQL database systems. Information Systems, 69, pp.93-105.

Wu, D., Sakr, S. and Zhu, L., 2017.Big data storage and data models.In Handbook of big data technologies (pp. 3-29).Database management system assignmentSpringer, Cham.

Wylot, M., Hauswirth, M., Cudré-Mauroux, P. and Sakr, S., 2018. RDF data storage and query processing schemes: A survey. ACM Computing Surveys (CSUR), 51(4), pp.1-36.