Comprehensive Report: Database Design and Development for ULKOM System

This report details the design and development of a relational database system for the ULKOM database. It begins with an introduction to database concepts and then delves into the database design requirements, including an Entity Relationship Diagram (ERD) and a data dictionary. The report covers data validation techniques, normalization processes (1NF, 2NF, 3NF), and the effectiveness of the database design in relation to system and user requirements. The report also includes database development aspects, such as database creation, SQL queries implementation, and the relationship between tables. Furthermore, it discusses testing strategies and recommendations for improved database development, along with an assessment of the database solution's effectiveness. Finally, the report provides technical and user documentation, including a flowchart, and concludes with final recommendations.

DATABASE DESIGN AND
DEVELOPMENT

Contents
INTRODUCTION...........................................................................................................................1
LO 1.................................................................................................................................................1
Database design requirements of ULKOM database...................................................................1
Entity Relationship Diagram.......................................................................................................2
Data dictionary.............................................................................................................................2
Normalization..............................................................................................................................8
D1 Understand the effectiveness of design in relation to the system and user requirements......9
LO 2...............................................................................................................................................10
Database development...............................................................................................................10
Database Creation:.....................................................................................................................10
Database query...........................................................................................................................13
Database Creation......................................................................................................................14
Database Insertion.....................................................................................................................15
SQL queries Implementation:....................................................................................................17
Relationship between tables......................................................................................................18
LO 3...............................................................................................................................................19
Testing.......................................................................................................................................19
Test recommendation for better database development............................................................20
D2 Effectiveness of the database solution.................................................................................21
LO 4...............................................................................................................................................21
Technical and user documentation............................................................................................21
Flow chart..................................................................................................................................22
Final recommendation...............................................................................................................23
CONCLUSION..............................................................................................................................23
REFERENCES..............................................................................................................................24

INTRODUCTION
Database can be defined as a process through which information or data is stored in a
structured manner. A database consists of multiple tables with different fields. Each field has
different data type. But in order to develop a database it is important to design a database with
the help of database model. This database design and development process help the organization
to put all of their requirement properly in a sequential manner in a well-planned database.
Database design helps in collecting all the business requirements, segregate them category wise
and put it into a well-planned database. It is a step by step procedure that helps the organization
fulfil all of their requirement and functionalities in a proper and appropriate manner. After
designing and developing a database, validations are applied in order to understand and make
sure that all the data stored in the database are stored sensibly this helps in maintaining
effectivity and efficiency of the database.
This assignment will lay emphasis on Designing of relational database system, describing
data validations, test plan, describing effectiveness of test plan, production of user and technical
documentation, development of relational database and effectiveness of data design and
database. Database used for designing the relational database is Microsoft SQL server 2017.
Microsoft SQL server is a kind of relational database that has been developed by Microsoft. It is
a kind of SQL server database engine that controls data processing, storage, security etc. its
primary function is to retrieve and store data as per the requires made by application software
that might be present on the same computer or on another network present in the same network.
LO 1
Database design requirements of ULKOM database
Relational database management system is a kind of system in which all the data stored in
the databases are in a structured format that uses rows and columns. In order to develop a
relational database, it is important to design a relational database design (RDD). RDD helps in
modelling data or information within tables that consists of columns and rows. Each row of the
table represents records stored within the database and each of the column of the table represents
all kinds of attributes of the data. In order to explain database design Entity relationship diagram
will be used. This ER diagram will help in representing data structure and relationship between
each table. Entity relationship model is a diagram that helps in explaining inter-relation between
each of the database entities and attributes. ER diagram of this database has been shown below:
1

Entity Relationship Diagram
Figure 1 ER diagram
Data dictionary
It is a type of entire schema of the database has been explained below: Explain foreign
key, primary key
Tables Attributes
Student table  Student id (PK)
 Student_name
 Student_address
 Student_phoneNo
 Payment_id (FK)
 Property_number (FK)
Property table  Property number (PK)
 Property_type
 Payment_id (FK)
2

Payment table  Payment id (PK)
 Payment_date
 Payment_status
 Payment_type
Agent table  Agent id (PK)
 Agent_name
 Agent_type
 Property_number
 Renting_number
Renting table  Renting_number (PK)
 Rent_date
 Rent_time
 Rent_rate
 Property_number (FK)
 Student_id (FK)
Table:1
 Primary key: primary key is a kind of key which is never null and consist of uniquely
identified data or it can also be said that values inserted in the table field are unique and
not null. All the tables of a database can have only one primary key i.e. no table can have
more than one primary key.
 Foreign Key: Foreign key is a kind of key present in a table which is primary key of
another table. This key can be null or can have same or duplicate values.
Data Validations
Validation is a type of process that mainly entered the data into DB which make sure that
become sensible. Data validation is a kind of process in which data is entered into the database in
order to make sure that data entered is sensible and correct. It is the better ways to check its
utility and cannot enter invalid data in it. Validation process is trying to identify number of errors
at the time of data input. This type of data validation process can be categorised into different
groups:
3

 Data type: it makes sure that specific field consists of specific suitable data type as per
their suitability. It won’t allow to input any letter and other type of non-numeric
characters. For Example- date data type will ensure that inputted existed at some points
and not exists in future.
 Range Check: it is validated the range of data field that can be applied on it, it also
makes sure that numbers within certain domain entered into particular field. For
Example- it can be used the specific format such as driver’s license number which
contained integer data type and range become specified (Mkrttchian and et.al., 2019).
 Unique Identifier: It is an essential part of database that contains one record which are
recognised from other records. In database table, Student_id is a primary key which only
contain unique value and doesn’t matters that large number of records in database system.
it only carried different range of data into multiple tables.
 Referential Integrity: when if it can be used relational database that must require to
validate referential integrity and always validate inputs. It might be checking information
in different fields against values into another database tables.
Specification of each table (design view of each table)
4

Design view of Agent table:
5

Design view of payment table
Design view of property table
6

Design view of renting table
Design view of student table
7

Normalization
Normalization in database can be defined as a type of technique that can be used to organize
tables in an appropriate manner in order to reduce redundancy and dependency of data (Allanson
and et.al.,2016). It helps in dividing larger tables into smaller tables and link them using
relationship. Mainly three types of Normalization forms are used 1NF, 2NF, 3NF. All the three
types of normalization form will be explained with the help of a random example.
 1NF: First normal form is a kind of normal form in which domain name of each attribute
contains only one atomic value. There are few rules that are required to be followed for 1NF
such as: each table should contain only one value and each record should be unique.
Name Address Movie Rented Salutation
Janet Jones 1st Street 33 Pirates of Caribbean Ms.
Janet jones 1st Street 33 Clash of the Titans Ms.
Robert Phil 4th Street 39 Clash of the Titans Mr.
Table:2
 2NF: Table should be in 1NF in order to be converted into 2NF. There are few rules that are
required to be followed for 2NF such as: first table should be in first normal form and second
is that at least one column should be of primary key.
Membership Id Name Address Salutation
1 Janet Jones 1st Street 33 Ms.
2 Robert Phil 4th Street 39 Mr.
Table:3
Membership Id Movie Rented
1 Pirates of Caribbean
1 Clash of the Titans
2 Clash of the Titans
Table:4
8

 3NF: Third normal form is used to reduce duplicity of data in order to ensure referential
integrity. There are few rules that are required to be followed for 3NF are: entity should be in
2NF and it should not have Transitive Dependency
Membership Id Name Address Salutation id
1 Janet Jones 1st Street 33 2
2 Robert Phil 4th Street 39 1
Table:5
Membership Id Movie Rented
1 Pirates of Caribbean
1 Clash of the Titans
2 Clash of the Titans
Table:6
Salutation Id Salutation
1 Mr.
2 Ms.
3 Mrs.
4 Dr.
Table:7
D1 Understand the effectiveness of design in relation to the system and user requirements.
A database consists of effective design because it contains information regarding the
particular subject. It should maintain large collection of data within system and becoming easily
for user to track information in different sources. A well-designed database is helping for fast
data retrieval and updates, before build the tables and other type of objects. It will make up the
system which important to take some time to design overall structure in proper manner. An
effective professional database design mainly server various type of facilities for user to search
information by using SQL command (Ganguly and Sarkar, 2019).
9

At that time, it modelling the overall structure and plan to implement code which is highly
recommendable. On the other hand, it is important to employ and establish the naming
conventions. In this way, it can be understanding that effective designing and maintaining data in
proper manner.
LO 2
Database development
A Database system is a type of software that allows for creation, manipulation of database
where users easily store, process and analyse the data (Glatte, 2019). relational database provides
with an effective interface and tool to perform different types of operations. It also provides a
protection and security which help for maintain consistency in case of various users.
User interfaces
A user interface is representing the view of database system that has seen by users. it
often maintains graphical user interface which constructed and offer facilities to interact with the
data or information through database. The users are searching particular information in easily
because data might be contained in form of table which easier for retrieving.
Database Creation:
Agent_table
10