Database Management Systems

Higher National Diploma in Computing

Assignment - Arbab Enterprises

Learning Outcome 1: Analyse different types of database management systems.

Learning Outcome 2: Design a database management system using a relational model to meet client requirements.

Learning Outcome 3: Develop a database management system using a suitable platform.

Learning Outcome 4: Demonstrate the system administration and management tools available on the chosen platform

Assignment Brief - Case study

Section 1:
Digitware is a business analytics and Digital Solution Provider. You are working in the organization as a Database Designer.

A client, Arbab Enterprises, has approached your company for the development of their Information System. The CEO of the company is a tech-savvy and is quite interested in knowing the proposed solutions in a technical context.

The Project Manager handling Arbab Enterprises has assigned your team lead to handle the requirements regarding DBMS, who has assigned this task to you for developing a formal report that would provide the following information:

Brief explanation of various Database Models like Entity-Relationship, Relational, Hierarchical, Network, Object oriented, and Object-relational

Comparison of these Database models based on their properties like data structure, relations, attributes, domain, tuples, cardinality etc.; constraints like key, domain, referential integrity; Modelling Languages like Query Language, DDL, DML, relational languages.

Analyze the relational database models in the context of normalization and assess how they can provide a reliable and efficient data structures.

Analyze different Database Management Systems like MS SQL Server, MySQL, Oracle etc. Your selection of DBMS should include both the vendor-specific and opensource systems and critically analyze their functions. There approach in managing the factors like transactions and concurrency.

Document the justification of the selected criteria used for this DBMS analysis.

Section 2:

Arbab Enterprises, a rapidly growing organization, has conducted an investigation and found that over the years as the company kept growing, its many database management systems have become disparate. The company now finds itself using many different platforms. The technology supporting its system was being purchased and added onto the infrastructure on a needs basis without too much controlled planning. The database management systems have served the company well but with the increasing use of cloud- based systems and the move towards data centres, the CEO would like your company to design and develop a RDBMS system.

In this regard, you team has conducted the requirement gathering and analysis of the existing system and has identified that the client has 3 different applications being used for various purposes.

They have a Payroll system which manages its employees records and payroll.

They have an inventory control system which maintains the records of stock and equipment being used in the company and assigned to different users

They have a billing system that generates invoices and stock management

Based on the analysis of these system, design a relational database and include the logical design elements like tables, data elements, data types, keys and indexes, developing ERD, designing DFD, flowcharts and Entity Relation Modelling

Analyse the impact of this new design on the system performance i.e. How these changes would optimize the existing system and how it will be beneficial for the user.

Based on the design, develop a fully functional system that meets client requirements using an open source DB like MySQL or MariaDB etc. Use MS Access as front end to develop your forms and reports however, you may also use PHP or any other interface or programming language that suits you as front end.

Prepare a Test Plan and test the functionalities of the system so that it can be confirmed that it is meeting the client's requirements and offer complete functionalities as desired and expected.

You may also implement features like data validation and verification, system checks,

record checks etc. to ensure that data is securely and properly storing into the system. You can apply cryptography to secure critical information (i.e. encryption or password hashing etc.) and also setup User Login feature so that user authentication can be ensured.

Critically evaluate the effectiveness of the system design and development against client and system requirements (i.e. hardware and software requirement for new system etc.)

Section 3:
You team has developed a system and it is ready to deploy however, your management has asked you to prepare a demonstration where you will operate the system and particularly with regards to the security implementation and optimization of the DBMS.

You are required to demonstrate:

the tools (i.e. server management; setting up and managing data storage for servers and users, Backup and restore, manage authorizations, import and export of data etc.) available in the system to monitor and optimize system performance and examine the audit logs and the tools (i.e. managing security and encryptions, trace database activities, audit trails, managing alerts, notifications, maintenance etc.) available in the system to manage security and authorization.

Demonstrate your assessment for the effectiveness of the system administration and management tools available on the platform and also identify any shortcomings of the tools.

Analyze and document any future improvements that may be required to ensure the continued effectiveness of the database system.