Database Design and Development

This document provides an overview of the database design and development process. It covers topics such as entity relationship diagram, data dictionary, normalization, and the effectiveness of design in relation to system and user requirements. It also includes information on database development, SQL queries, and database creation and insertion.

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

Payment_table
Property_table
11

Renting_table
Student_table
12

Database query
Structured Query language is based on the programming which performed the different
operations to organize and retrieves information in relational database. It is the most effective
and standards computer language in terms of relation for managing and controlling data in proper
manner (Baik, Jagadish and Li, 2019). It can perform different operations such as update, insert,
modify and delete. It mainly supports that added more benefits for database administrators. It
often to support in terms of storage across multiple platform. SQL standard has gone through a
lot of changes in overall database which added great deals with perform new functionality as per
standards. It mainly supporting the various element which convenience of SQL developers for all
necessary command for generating output (Ganguly and Sarkar, 2019).
SQL queries: It is most common approach in term of database design where it performs
structural query operations. It also searches DB form information which required for executing
the task in proper manner.
 Form: In database, it is indicating the tables where search will be done by using from
command.
 Where: it can be used to define the particular row in table while search will be carried
and this type of clause is not become true but will be executed (Gao and et.al., 2019).
 Order By: it is only term to sort result in Structure query language otherwise it will
return data as randomly orders.
For Example-
Select * from
Where active
Order by name, address
Business uses SQL (Structure Query language)
 It useful for accessing and manipulating information or data within database system
 It can be generated reports which aid decision making process.
 It became easier to export and import data from various type of systems.
 SQL facilitate for supporting and finding the specific role through analysis.
 The SQL programming language provide the ACID constraints to entire data assets.
There are different ways to divide SQL statements within database system.
13

 Data Definition language: It can be used as standards command which help for
performing different operations (Egea and Dania, 2019). In this way, it easier for
modifying and creating the structure of Database. It mainly involves tables, indexes,
schemas and views. It always assists for storing metadata in database system. data
definition language commands: Create, Drop and Alter.
 Data Manipulation Language: this type of command is mainly used for permitting
users to manipulate data in database system. it including the insertion data in the form of
tables which easily modifying existing data or information (Baik, Jagadish and Li, 2019).
There are some common commands used in DML such as insert, delete, update, select
and update.
Database Creation
14

Database Insertion
15

16

SQL queries Implementation:
By using Inner Join
Group By
17

Relationship between tables
18

LO 3
Testing
It is important to test the database in order to ensure that all the values stored into the
database or values that are being retrieved from the database are appropriate and correct. Without
testing the database user cannot verify that all the data stored or retrieved from the database are
correct and appropriate or not. In order to test the database, it is important that both the tester and
database user have proper and appropriate knowledge of SQL queries (Delplanque and et.al.,
2018). Testing of a database cannot be completed until and unless tester understands the
application completely. It helps the database user to assure the overall quality of the database and
the data stored within it.
Database testing not only includes testing of data but it also includes checking schemas,
triggers, tables and many more under database test. Testing of the database can be done in many
ways like: creating complex queries for checking load or stress testing as well as to check
responsiveness or by checking the validations of attributes. It is also known as data testing or
back end testing (Neethidevan and Chandrasekaran, 2018). There are various kinds of database
testing techniques that can be used for testing the database but most commonly done database
testing are:
Database schema testing: this testing is used to test each object in the schema. It is used to
check or verify following things such as:
 Verify database name
 Verify database device
 Verify database options
 Check name of all the tables
 Check null value
 Check primary and foreign key in the tables and many more.
Storage procedure testing: this testing is done to check whether the stored procedure is defined
and all the output results are being compared or not. This type of testing is used to check
following things such as:
 Storage procedure name
 Check error message
 Check storage parameters
19

 Check return values and many more
Test recommendation for better database development
Test Description Expected Outcome Actual Outcome
Check data type in
database tables
The expected outcome is to
successfully used an appropriate
data types such as integer,
character etc.
Data validation
testing
The expected result or outcome is
used an appropriate character as
per specific range other wise it
will generate error message.
Primary key
checking
The expected outcome is to be
successfully generating value if it
can be showing an error when
same values type in one column.
Table: 8
20

D2 Effectiveness of the database solution
One of the most common way to check effectiveness of database solution is to check its high
performance. But there are many other factors that are required to be checked in order to check
effectiveness of the database. First is size of the database i.e. user should be able to check or
enter millions of rows or records within the database. Second is to check accuracy of data
entered into the database i.e. to represent entering of inaccurate data. Entry of incorrect data into
database can reduce effectiveness of the database (Fritchey, 2018). Third is to check security of
the database. It is important because if a database consist of any kind of sensitive information
then it becomes important to restrict the access on the information. This also helps increasing the
effectiveness of the database. Another factor that can increase effectiveness of database solution
is to reduce redundancy of data within database so that situation of conflict reduces. Other than
this there are various other factors that are required to be observed and checked so that
effectiveness of the database solution can be enhanced.
LO 4
Technical and user documentation
Documentation helps in providing instructions to use the database. It includes two main
types of documentations. First is technical documentations in which requirements of the database
are explained and second is user documentation in which a manual is prepared for the end user to
use the database or applications (Wang and et.al., 2019).
21

Flow chart
Technical documentation (for the admin):
 Open Microsoft SQL server and enter userid and password.
 Select the database in order to store or retrieve information from it
 Select the database table in which user wants to enter data or access information from
 Apply filters according to the requirements and desired outcome
 Import or export database of database tables as per the requirements of the user
22

 Create user type, password and role i.e. permission required for the access.
 After completion logout from Microsoft SQL server.
User documentation:
 Students can insert their details such as name, number, type of property required by them
etc. and other than this they can also check status of their query for renting a property
(Kaur and Sehra, 2015).
 After selection of property students can move to payment options where they will be
providing their payment details so that they can rent the property in a proper and efficient
manner.
 Agents can login to the database and check students queries for renting a property.
 They will be checking available property for the students as per their requirements and
after this they will be updating availability of property as per their student’s requirements.
Final recommendation
Database users in future can work on providing access to all the users as per their
requirements so that security of the database can be ensured. They can also work on limiting
access to all kinds of sensitive information such as agent or students’ personal details. This will
help the user of the database to enhance its security.
CONCLUSION
From the above report it has concluded that implementation of database has been completed
in a proper an effective manner with the help of database design. Database design has helped in
development of database in such a manner that all the requirements of the organization were
fulfilled. With the help of data validation insertion of data within database has been done in a
proper a sensible manner such that values entered are correct. Test plan has also helped in testing
the database against users’ requirements in such a manner that effectivity of database was
maintained. Other than this all kinds of technical documentations have been done in a proper
manner and based on those documentations future improvements have also been defined in order
to ensure the continued effectiveness of the database system.
23

REFERENCES
Book and Journals
Allanson, E.R., and et.al., 2016. The WHO application of ICD‐10 to deaths during the perinatal
period (ICD‐PM): results from pilot database testing in South Africa and United Kingdom.
BJOG: An International Journal of Obstetrics & Gynaecology. 123(12). pp.2019-2028.
Fritchey, G., 2018. Database Performance Testing. In SQL Server 2017 Query Performance
Tuning (pp. 825-837). Apress, Berkeley, CA.
Kaur, T. and Sehra, S.K., 2015. Designing and development of database testing tool.
International Journal of Computer Applications. 120(19).
Neethidevan, V. and Chandrasekaran, G., 2018. Database Testing using Selenium Web Driver–A
Case Study. International Journal of Pure and Applied Mathematics. 118(8). pp.559-566.
Wang, G., and et.al., 2019. Improving Cross-database Face Presentation Attack Detection via
Adversarial Domain Adaptation. In International Conference on Biometrics (ICB).
Gao, P. and et.al., 2019. A query system for efficiently investigating complex attack behaviors
for enterprise security. Proceedings of the VLDB Endowment. 12(12). pp.1802-1805.
Egea, M. and Dania, C., 2019. SQL-PL4OCL: an automatic code generator from OCL to SQL
procedural language. Software & Systems Modeling. 18(1). pp.769-791.
Ganguly, R. and Sarkar, A., 2019. An Approach to Develop a Transactional Calculus for Semi-
Structured Database System. International Journal of Computer Network and Information
Security. 11(9). p.24.
Glatte, T., 2019. Development of CRE data systems: A case study for heterogeneous property
portfolios. Corporate Real Estate Journal. 8(3). pp.210-227.
Mkrttchian, V. and et.al., 2019. Model of Benchmarking for Development of Firm's Competitive
Advantage to Improve Economic Growth in Cities. In Optimizing Regional Development
Through Transformative Urbanization (pp. 118-140). IGI Global.
24

Delplanque, J., and et.al., 2018, September. Relational Database Schema Evolution: An
Industrial Case Study. In 2018 IEEE International Conference on Software Maintenance
and Evolution (ICSME) (pp. 635-644). IEEE.
Baik, C., Jagadish, H.V. and Li, Y., 2019, April. Bridging the semantic gap with SQL query logs
in natural language interfaces to databases. In 2019 IEEE 35th International Conference on
Data Engineering (ICDE) (pp. 374-385). IEEE.
25