Database Design and Development

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