Database Design and Implementation for Desklib

COVER PAGE

Contents
Task L01.......................................................................................................................................................3
1.1 Data models and schema in data analysis.........................................................................................3
1.2 Types of database management systems..........................................................................................6
1.3 Database design approaches.............................................................................................................8
L02: Design and implementation of relational database systems...............................................................8
2.1 Design................................................................................................................................................8
2.2 Ms Sql relational database code......................................................................................................11
2.2 User interface design.......................................................................................................................13
L03: Data Manipulation and Query tools...................................................................................................14
3.1 SQL features and functionalities......................................................................................................14
3.2 Applying DML..................................................................................................................................15
3.3 Features of a query tool...................................................................................................................18
L04: Testing and documenting relational database systems.....................................................................18
4.1 Database testing methods;..............................................................................................................18
4.2 Test plan..........................................................................................................................................18
4.3 Importance of technical and user documentation...........................................................................21
4.4 Verification and validation...............................................................................................................22
4.5 Control mechanisms........................................................................................................................22
References.................................................................................................................................................22

Task L01
1.1 Data models and schema in data analysis
A data model is a simplified representation of complex real world data structures. In database
management systems there are different types of data models each of each has its own advantages and
disadvantages. Types of data models include;
 Hierarchical data model
 Network and relational data model
 Entity-relationship model
 Relational model
 Schema (Conceptual, Logical and pysical schema).
Hierarchical data model
This type of data model is used to make a hierarchical database. A hierarchical database is designed to
use one-to- many relationships for entities making the database. The database uses a tree like structure
where an entity acts as a parent and other child entities linked to the parent entity are the child entities.
The concept behind hierarchical database model is useful in specific types of databases but this model
lacks versatility because it is limited to the hierarchical nature of the data where by an entity cannot
have more than one parent node. The model has to maintain a hierarchy. An example of data that can
be represented using hierarchical model is shown in the figure below;
Figure 1: hierarchical data model example
Network data model
The network data model was designed as an extension of the hierarchical data model. This model
improved on the hierarchical model by allowing an entity to have more than one parent entity thus
resulting to a graph like structure compared to the tree like structure of the hierarchical model.
Databases designed using the network model have more relationships because of the many

relationships existing between the data. Because of the many relationships, the network model makes it
easier and fast to access data. The figure below shows an example of a graph like network model.
Figure 2: network data model example
Entity-relationship model
Entity relationship data model involves designing the database by designing objects into entities and
then designing the characteristics of the object into attributes. The entities are related with one another
through relationships. Entity relationship diagrams are commonly used in this data model to model the
database. The entities represented in the entity relationship diagram can then be turned into tables to
form a relational database. A good example is designing a database for a school where a student is an
object. The student object can be designed as an entity having attributes like student id, name, address
and age as shown in the figure below;

Figure 3: Entity-relationship model
Relational Model
Relational model involves organizing data in two dimensional tables and maintaining the relationship by
through a common field. E.F Codd introduced this model in 1970 and it’s the most widely used data
model to date. Tables in relational model are known as relations which is the basic structure of data. The
concept of normalization is widely used in relational model to achieve highly normalized relations. The
figure below shows sample data in a relational model;

Figure 4: Relational model sample data
Schema
Conceptual, logical and physical data model are used to model data. All of them contain entities,
relationships but they are different in terms of their purpose and the target audience they are intended
for. In general, the conceptual and logical data models are to model data in a business oriented manner
while the physical data model is used by the database designer to represent the physical database
structure for implementation of the database.
Conceptual model is specifically used to model data gathered from business requirements. The entities
and relationships in this model are designed based on the business requirements.
Logical model is an advanced conceptual model that is also modelled based on the business
requirements. It differs from the conceptual model because it shows columns and data types although
the column type can be optional.
Physical model represents the final blueprint of the relational database that will be used to implement
the database in a relational database management system. This model is used by the database designer
and is supposed to be followed as it is.
1.2 Types of database management systems
 Traditional file processing system
This type of database management system was developed in an attempt to shift from the
manual filing system (Thakur, 2013). It offers a method through which files can be stored and