Data Analysis, Design and Implementation of RDBMS - A Case Study

This report provides a comprehensive analysis of relational database management systems (RDBMS), covering essential concepts and practical implementation details. It begins with a critical comparison of various data models and database technologies, including hierarchical, network, relational, and object-oriented models, highlighting their benefits and limitations. The report then delves into different approaches to database design, such as top-down and bottom-up methodologies. A significant portion of the report focuses on the design and implementation of a relational database system, including the creation of an ERD model, database normalization to eliminate redundancy, and the construction of database tables using a top-down approach. Furthermore, it explores data manipulation and querying tools, demonstrating the use of query languages to extract meaningful data. Finally, the report addresses crucial aspects of testing and documentation, including user feedback, test case plans, user documentation, verification and validation processes, and control mechanisms to ensure the reliability and integrity of the implemented RDBMS. This document serves as a detailed guide to understanding and implementing relational databases, with a focus on practical application and real-world considerations. Desklib provides access to this and many other solved assignments for students.

Data Analysis and design

Table of Contents
Introduction......................................................................................................................................2
LO1 Understand data models and database technologies................................................................3
1.1 Critically compare different data models and schemas....................................................3
1.2 Critically discuss the benefits and limitations of different database technologies................6
1.3 Analyse different approaches to database design..................................................................8
LO2 Be able to design and implement relational database systems................................................9
2.1 Design a relational database system to meet a given requirement........................................9
2.2 Build a relational database system based on a prepared design..........................................14
2.3 Apply a range of database tools and techniques to enhance the user interface...................18
LO3 Be able to use manipulation and querying tools....................................................................26
3.1 Explain the benefits of using manipulation and query tools in a relational database system
...................................................................................................................................................26
3.2 Implement a query language into the relational database system that has been
implemented..............................................................................................................................29
3.3. Critically evaluates how meaningful data has been extracted through the use of query
tools for the given case study.....................................................................................................39
LO4 Be able to test and document relational database systems....................................................45
4.1 Critically reviews and tests the implemented relational database system...........................45
4.2 Create documentation to support the implementation and testing of a relational database
system........................................................................................................................................48
4.3 Create user documentation for a developed relational database system..............................52
4.4 Explain how verification and validation has been addressed..............................................56
4.5 Explain how control mechanisms have been used..............................................................59
Conclusion.....................................................................................................................................64
1

Introduction
This report, it has been discussed about the issues which are important and the concepts of the
RDBMS. In the beginning it has been discussed about the schemas and models taken in use.
Technologies of database and various approach has been discussed. In the next part there has
been a discussion on ERD model and database normalization. Implementation of database has
been done. Tools and techniques of database have been discussed.
In the next part data manipulation has been discussed and languages and tools of query. In the
last part, the conversation is on several feedbacks which are going to be observed by many users,
and various test case plan, the user documentation, verification and validation, and control
mechanism will be explained properly. The main aim of this assignment is to discuss the
concepts of designing and analysis of the data.
2

LO1 Understand data models and database technologies
1.1 Critically compare different data models and schemas
Data model:
The data model is considered as an abstract model which explains the data which has been
accessed and represented. The data model assimilates elements of data group together in the
form of records.
There are several data models mentioned below:
1. Hierarchical Model:
The hierarchical model is the one where data is arranged in the form of a tree. In this
structure, the information is replaced by parent-child relation every individual parent can
have more than one child but every individual child will have only one parent which is
also known as one to many relationships.
Benefits :
1. The relationship present between the different layers is simple logically.
2. The hierarchical used is secured fully .
3. The relationship in the model looks like the relationship of parent-child because of
which there is a powerful connection between the child selector and parent sector
beneath it.
4. This model is efficient as the database has one too many relationships.
2. Network model:
It makes use of fundamental concepts named sets and records which help in organizing
the data. Records have fields in it and sets describes the one-to-many relationships among
records. In this model, there is more than one parent model which has been organized in
the form of a graph.
Benefits:
1. It is easy and simple in terms of implementation.
2. It can manage many to many and one to one relationship.
3. It is easy to access in comparison with the hierarchical model.
3

4. As it is based on the relationship among the parent and child there is a connection
between child sector and parent sector beneath it.
5. When data independence comes into consideration then it is better than hierarchical
model.
3. Relational model:
A relational model gives permission to the operations of storage and retrieval, integrity
constraints and description of data structures. In this model, the relation among the data is
organized in the form of tables.
Benefits:
1. It is considered simple in comparison with other.
2. If there is a change in one data sector so there is no need to change in the other.
3. Implementation of data is easier.
4. It is a collection of more than one column when combined has a value which is unique.
4. Object-oriented model:
In this type of data model, the data and relationship both are combined into one structure
which is considered an object. In the object-oriented model, the attributes which the
object have describes the traits of the object.
Benefits:
1. It gives support to features including abstraction, inheritance, encapsulation, and
polymorphism.
2. It is considered in terms of searching any records of data and identifying.
3. It maintains the code bases easily.
Database Schema:
4

The schema here says that the design for implementing the database. It describes the
relationship between the entities and the attributes. It stocks the definition of data of all the
formulas which has been taken in use for implementing the database.
It has been categorizing into different levels:
Physical:
It has been explained how the database is going to be stored in indices form, files and in various
ways of the RAM. It also explains the different types of various files and layout of the records.
Conceptual:
This level is also considered as logical level. Here, how the representation of the rallies going to
be done. It displays the concept diagram.
External:
The relationship which the various entities have among them is shown . It also explains the view
of data according to logical or conceptual level.
5

1.2 Critically discuss the benefits and limitations of different database technologies
The different types of database technologies are:
File-based:
It is a type of database technology which stores the data in an unorganized and old way. This
technique was very popular when there were no computers available. The main benefit of this
database is that it minimizes the redundancy occurred in the data and is also secure. The
limitation of this database is that it is not easy to develop and design which results in consuming
more time to design.
Relational:
A relational database is that type of database which has a relationship with the data. The
advantage of this database is that it is an already established and flexible database and it is easy
to understand. The limitation of a relational database is that to represent it in the real world is a
complicated procedure.
Object-oriented:
The data stored in this database is in the organized format and those objects are created which
has some attributes and something defining the traits of object. The benefit of this database is
that it is scalable , simple and strong. The limitation of this database is that if there is a huge
amount of data which gets deleted it causes problem as it reduced the flexible property.
Distributed:
In the distributed database, it stores the database in several locations as there is no fixed physical
location. It is very much reliable. The benefit of the distributed database is faster and can be
accessed from anywhere and at any time. The limitation is that it is exposed to data integrity
because of which it is not that secure.
Data warehousing:
6

Data warehousing manages the database and cleans it and when there is a requirement it also
fetches the data. To obtain useful data it is the technology which is most required. The benefit of
this technology the datasets which have a very high capacity can be easily handled. The
limitation is that it is very costly and not as much compatible and flexible.
Web-based technology:
It is related to cloud computing where the database has been given by several companies . It is
fast and simple. The benefit in this is that data can be fetched at any time and from anywhere
only need to have an internet connection. The limitation is that it only have limited functions and
it requires net every time when there is an editing needs to be done in database.
7

1.3 Analyse different approaches to database design
They are different approaches to develop a database as mentioned below:
Top-down approach:
In top-down approach first, it moves in a general format and then it is going to move forward in a
specific way. In the beginning, only needs to be described as what the system needs and then
going to move forward to the bottom towards more specific details about the interaction of the
system. This process highlights the recognition of different types of entity and attribute of each
entity.
Bottom-up approach:
The bottom-up approach deals first with the specific details and then move forward with the
general. which is performed by identification of elements of data and then bind them in a data
sets. It first identifies attributes and then builds entities.
Difference between top-down approach and bottom-up approach:
Top-down approach Bottom-up approach
It consumes a lot of time and is more in cost in
comparison to bottom-up
It does not cost that much and design the
database in a short span of time.
In the beginning it requires a lot of planning . It does not require planning in the beginning.
It is considered complicated for designing the
database.
It is simpler in this approach to design the
database.
8

LO2 Be able to design and implement relational database systems
2.1 Design a relational database system to meet a given requirement
ERD Model:
ERD model is of different type of elements which includes:
Attributes:
Which explains the entity is considered as attributes.
Entities:
It is considered as the elements which are related to attributes and the relationship among them.
Relationships:
It defines the connection among the entities.
Fig. ERD Diagram
9

Chenn notations have been taken in use in the diagram to describe the relationship among the
entities.
Normalization:
It is a technique where the tables are organized and the redundancy and data dependency is
detached.
Types of Normalization:
Un-normalized form:
It is a form of the database which is not in any form
1NF (First Normal Form):
In this form, the atomicity is being detached from the table.
Person Study details
staff_id Projector
staff_name classroom_attendance
staff_email classroom_date
student_Name classroom_timing
student_contactNumber course_Name
student_DOB course_fee
student_type course _type
course_paymentMode
course_extrafees
coursespopular_Type
course_avgattendance
department_Name
department_HoD
department_contactnumber
10

Mark_type
mark_year
Mark_semesters
2NF (Second Normal Form):
The functional dependency is being detached in the second normal form.
Staff Classroom
staff_id classroom_id
staff_name projector
staff_email classroom_attendance
department_id classroom_date
classroom_timing
Student Mark_type
student_id mark_year
student_Name Mark_semesters
student_contactNumber
student_DOB Department details
student_type department_id
department_id department_Name
payment_mode department_HoD
department_contactnumber
course_Name
course_fee
course _type
11