Database Management Systems: A Relational Database Project for Unit 38

Desklib provides past papers and solved assignments for students. This project focuses on relational database design and implementation.

Unit 38 – Database Management Systems

Table of Contents
Unit 38 – Database Management Systems.........................................................................1
Introduction.......................................................................................................................5
LO1 Analyze different types of database management systems.........................................6
P1 Compare and contrast the different types of database models..............................................6
M1 Assess how relational database models and the process of normalization can provide
reliable and efficient data structures........................................................................................10
LO2 Design a database management system using a relational model to meet client
requirements...................................................................................................................14
P2 Produce a design for a relational database management system to meet client requirements
................................................................................................................................................14
M2 Analyse how the design will optimize system performance................................................16
LO3 Develop a database management system using a suitable platform.........................19
P3 Develop a fully functional system which meets client and system requirements, using an
open source language (with an application software e.g. MySQL with front end Microsoft
Access).....................................................................................................................................19
P4 Test the system for functionality and performance..............................................................26
M3 Implement effective features in the solution to handle concurrency, security, user
authorizations, and data recovery............................................................................................27
LO4 Demonstrate the system administration and management tools available on the
chosen platform...............................................................................................................29
P5 demonstrate the tools available in the system to monitor and optimize system performance
and examine the audit logs.......................................................................................................29
P6 Demonstrate the tools available in the system to manage security and authorizations........30
M4 Assess the effectiveness of the system administration and management tools available on
the platform identifying any shortcomings of the tools.............................................................31
Conclusion.......................................................................................................................33
2

Reference List..................................................................................................................34
List of Figures
Figure 1: Hierarchical Model.....................................................................................................6
Figure 2: Network Model...........................................................................................................7
Figure 3: Object-Oriented Model...............................................................................................7
Figure 4: Hybrid Model.............................................................................................................8
Figure 5: List of Tables............................................................................................................15
Figure 6: ERD..........................................................................................................................15
Figure 8: User Form.................................................................................................................19
Figure 9: User Form Data Type...............................................................................................19
Figure 10: Category Form........................................................................................................20
Figure 11: Category Data Type................................................................................................20
Figure 12: Category Data.........................................................................................................21
Figure 13: Product Form..........................................................................................................21
Figure 14: Product Data Type..................................................................................................22
Figure 15: Product Data...........................................................................................................22
Figure 16: Sale Form................................................................................................................23
Figure 17: Sale Data Type.......................................................................................................23
Figure 18: Sale Data.................................................................................................................23
Figure 19: Cart Form................................................................................................................24
Figure 20: Cart Data Type.......................................................................................................24
Figure 21: Cart Data.................................................................................................................25
Figure 22: Payment Form........................................................................................................25
Figure 23: Payment Data Type................................................................................................25
Figure 24: Payment Details......................................................................................................26
List of Tables
Table 1: Difference between Database Models.........................................................................9
Table 2 – Database models......................................................................................................10
Table 3: Software Member Info Table.....................................................................................10
Table 4: Member Table after 1NF...........................................................................................11
Table 5: Software Table after 1NF..........................................................................................11
3

Table 6: Member Table after 2NF...........................................................................................12
Table 7: Software Table after 2NF..........................................................................................12
Table 8: Soft Info Table after 2NF...........................................................................................12
Table 9: Member Table after 3 NF..........................................................................................13
Table 10: Soft Info Table after 3 NF........................................................................................13
Table 11: Soft Info Table after 3 NF........................................................................................13
Table 12: Software Details Table.............................................................................................13
Table 13: Relational Database Basic.......................................................................................14
Table 14: Testing Table for Results.........................................................................................26
Table 15: Software for Back up Table.....................................................................................27
4

Introduction
The database is very necessary software as it is containing all information and connection for
making a solution related to the problem. Here in LO 1, we have to understand all different
data models and normalization rules. In LO 2, it is understandable that how we can create
ERD of the database management system. In LO 3, it has an implementation of the system
with testing techniques and features like concurrency and authorization. In LO 4, we able to
learn tools which are used for back up and securing information from attackers. The
relational model is one of the famous & popular models which are used for database
operation & creation. It is mainly used for better designing & excluding the problems in
hierarchical & network database model like complexity & not working on all platforms
(Azhar, 2017).
5

LO1 Analyze different types of database management systems
P1 Compare and contrast the different types of database models
Database designing is characterized as a process of interconnection with a meaningful
relationship of elements of data. For the increasing quality of database management,
nowadays various database techniques are used but a mainly relational database is used. The
output is sent only and only after fetching information or data from a database according to
its query & after that delivered to the output window. This provides a completely unique &
independent feature for data elements. There are mainly three types of database models which
are widely used according to their need & use and these are:
 Network Model
 Relational Model
 Hierarchical Model
 Object-Oriented Model
 Hybrid Model
Hierarchical Model
Hierarchy model is also a type of database model which is followed by the hierarchy & tree
structure of the database. Here, these elements are structured in tree form or hierarchy. Let
take an example of the student library system, in which a student can issue a book & also
allowed for giving online exams of different subjects online via the library portal. So, student
information is root data, exams and issuing book are the second layer of data & individual
subjects are the third layer of data & information related to book like author name, subject
name, issuing date etc. And, they all are interconnected or interrelated to each other.
Figure 1: Hierarchical Model
6

Network Model
Network Model is also a database model in which a data or record is owned & accessed by
more than one user. And here in this, one user can contain more than one record with itself. It
cannot use any type of layer of the network. It is very difficult to implement this model
because it has a very complex data structure which cannot easily understand. Its complexity
is also increased as well as the number of data is increased. The network model is used only
and only when there is no other option available by viewing difficulty.
Figure 2: Network Model
Object-Oriented Model
It is introduced in the 1980s which is fulfilling the requirement for processing object &
classes which are created and designed. It also manages all operation by using a class such
that they can be reusable. Its grow thing rate is 40%-50%. Here some examples of object-
oriented are there: Gemstone, Jasmine, Object Store, Versant, POET & ONTOS etc.
Figure 3: Object-Oriented Model
7

Hybrid Model
It is introduced in the 1990s. it is having all hybrid form of layers of database models which
are interrelated. One top root element has sub-elements and further a hierarchy or sequence is
followed for accessing all features of previous elements via the concept of classes & objects
(S., 2013).
Figure 4: Hybrid Model
Relational Model
The relational model is one of the famous & popular models which are used for database
operation & creation. It is mainly used for better designing & excluding the problems in
hierarchical & network database model like complexity & not working on all platforms. In
this, we can easily work on many to many relationships between entities & process easily. It
is completely secure & anyone can work on this easily if they have a little bit of knowledge
regarding the database. In this each & every database deal with record, tuple & relation with
some constraint like key constraint & referential integrity constraint which are interconnected
to each other.
Difference between Database Models
Here below is the difference model which has differences between these above-mentioned
database models according to their specification, work & implementation:
8

Table 1: Difference between Database Models
Comparing
Attribute
Database Model
Network Relational Hierarchical Hybrid Object
Oriented
Discovery 1960-1970 1970-till now 1960-1970 1990-till
now
1980-present
Definition A data or
record is
owned &
accessed by
more than one
user. And here
in this, one
user can
contain more
than one record
with itself.
Removing
the
complexity
of the
database.
Dealing with
tuples,
relations &
data
elements.
It is followed
by the
hierarchy &
tree structure
of the
database.
Here, in this
elements are
structured in
tree form or
hierarchy
Layers of
database
models
which are
interrelate
d. It is
also
having
nested
operating
system for
fetching
informatio
n.
Processing
object &
classes which
are created
and designed.
It is also
providing
combining
feature of
function for
data elements
& data
relation.
Current
Info
Communicatio
n via satellite
& reservation
system.
Widely used
the database
for creating a
dynamic
website.
Bulk SMS
and email
using some
portals like
MSG 71 etc.
It is
completed
related to
future
prediction
and
according
to need &
use.
It is
increasing
day by day
with
providing
various
platforms on
which they
all are secure.
Illustration Reservation
system like
Visual
Studio, VB,
The
information
Universal
Database,
Gemstone,
Jasmine,
9

railway &
airline.
DB2, Orcale,
Microsoft M
S Access.
system of
MNC IBM
which is
IMS.
Oracle 8
version.
Object Store,
Versant,
POET &
ONTOS
(Singh,
2018).
Table 2 – Database models
M1 Assess how relational database models and the process of normalization can
provide reliable and efficient data structures.
Relational Database Model
Relational database model was introduced by Mr. C.F. Codd in 1970s which is widely used
nowadays.
Normalization
Normalization is carrying the process of deletion, removing of redundancy & repetition of
data for fastest execution of the query and better outcome. By removing there is no group is
there which is repeated and all attributes are having single values. By this, a database has
very good quality & performance is increased in term of less execution time of query &
quality of output. There are mainly 5 rules or types of normalization are there and these are
First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), BCNF
(Boyce Codd Normal Form), Fourth Normal Form (4NF) (A. Poolet, 2019).
UNF
UNF is containing an un-normalized form of data from the database. In this there is no proper
arrangement of data like the repetition of data, one column is containing more than values
and grouping of information.
Table 3: Software Member Info Table
S.N. Name of Attribute Type of Data Constraints (If Any)
1. Mem Ref (Unique) Int Primary Key
2. Mem first name Char NOT NULL
3. Mem lastname Char NOT NULL
10

4. Flat Number Varchar NOT NULL
5. Address Locality Char NOT NULL
6. City Char NOT NULL
7. Pin Code Big Int NOT NULL
8. Mobile Number Varchar NOT NULL
9. Email Varchar NOT NULL
10. Soft Ref (Repetitive & Unique) Int Primary Key
11. Software (Repetitive) Varchar NOT NULL
12. Installation Date (Repetitive) Date NOT NULL
13. Cost (Repetitive) Int NOT NULL
14. Program ID (Repetitive) Int NOT NULL
15. Program Name (Repetitive) Varchar NOT NULL
1 NF (First Normal Form)
In this, it is required that each and every column has unique values which are identified as
one attribute which is constrained by key constraint in the relational database or primary key
in the normal database. In this, each and every attribute or column name is uniquely
identified. Here in this, software info table is dividing into two tables that is member table &
software table.
Table 4: Member Table after 1NF
S.N. Name of Attribute Type of Data Constraints (If Any)
1. Mem Ref (Unique) Int Primary Key
2. Mem first name Char NOT NULL
3. Mem lastname Char NOT NULL
4. Flat Number Varchar NOT NULL
5. Address Locality Char NOT NULL
Table 5: Software Table after 1NF
S.N. Name of Attribute Type of Data Constraints (If Any)
1. Soft Ref (Unique) Int Primary Key
11

2. Software Varchar NOT NULL
3. Installation Date Date NOT NULL
4. Cost Int NOT NULL
5. Program ID Int NOT NULL
6. Program Name Varchar NOT NULL
2 NF (Second Normal Form)
In this, form it is ensured that the table is already normalized by the first normal form.
Record or relation is completely free from partial dependency.
Table 6: Member Table after 2NF
S.N. Name of Attribute Type of Data Constraints (If Any)
1. Mem Ref (Unique) Int Primary Key
2. Mem first name Char NOT NULL
3. Mem lastname Char NOT NULL
4. Flat Number Varchar NOT NULL
5. Address Locality Char NOT NULL
Table 7: Software Table after 2NF
S.N. Name of Attribute Type of Data Constraints (If Any)
1. Mem Ref (Connected on Member
Table via Foreign Key)
Int Primary Key
2. Soft Ref (Unique) Int Primary Key
3. Installation Date Date NOT NULL
4. Cost Int NOT NULL
5. Program ID Int NOT NULL
6. Program Name Varchar NOT NULL
Table 8: Soft Info Table after 2NF
12