Database Assessment for VET Solution Company
Added on 2024-05-21
52 Pages4900 Words369 Views
|
|
|
Database Assessment
Table of Contents
Introduction..............................................................................................................................5
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem....................................................................................................................................6
P1. Design a relational database system using appropriate design tools and techniques. .6
Requirement of database administration framework........................................................6
Client Requirement...........................................................................................................6
Framework Requirement..................................................................................................7
Software Requirements....................................................................................................7
Hardware requirements....................................................................................................7
ER diagram.......................................................................................................................7
Relationship......................................................................................................................8
M1 Produce a comprehensive design for a fully functional system.....................................9
Data Validation..................................................................................................................9
Data Dictionary...............................................................................................................10
Data Normalization.........................................................................................................14
LO2 Develop a fully functional relational database system...................................................19
P2. Develop the database system using MS SQL Server 2014.........................................19
Relationship diagram......................................................................................................19
Design view of tables......................................................................................................20
P3. Produce a query language, with queries across multiple tables for the database.......27
M2......................................................................................................................................31
LO3 Test the system against user and system requirements:...............................................32
P4 Create a test plan to test the database:........................................................................32
M4 Create and populate tables, validation, verification, data types and referential integrity
...........................................................................................................................................36
LO4 Produce technical and user documentation...................................................................38
P5. Produce fully technical and user documentation for your designed database for VET
SOLUTION Company........................................................................................................38
1
Introduction..............................................................................................................................5
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem....................................................................................................................................6
P1. Design a relational database system using appropriate design tools and techniques. .6
Requirement of database administration framework........................................................6
Client Requirement...........................................................................................................6
Framework Requirement..................................................................................................7
Software Requirements....................................................................................................7
Hardware requirements....................................................................................................7
ER diagram.......................................................................................................................7
Relationship......................................................................................................................8
M1 Produce a comprehensive design for a fully functional system.....................................9
Data Validation..................................................................................................................9
Data Dictionary...............................................................................................................10
Data Normalization.........................................................................................................14
LO2 Develop a fully functional relational database system...................................................19
P2. Develop the database system using MS SQL Server 2014.........................................19
Relationship diagram......................................................................................................19
Design view of tables......................................................................................................20
P3. Produce a query language, with queries across multiple tables for the database.......27
M2......................................................................................................................................31
LO3 Test the system against user and system requirements:...............................................32
P4 Create a test plan to test the database:........................................................................32
M4 Create and populate tables, validation, verification, data types and referential integrity
...........................................................................................................................................36
LO4 Produce technical and user documentation...................................................................38
P5. Produce fully technical and user documentation for your designed database for VET
SOLUTION Company........................................................................................................38
1
User documentation........................................................................................................38
Technical documentation................................................................................................45
M5 Produce technical and user documentation for a fully functional system, including
diagrams showing movement of data................................................................................50
Conclusion.............................................................................................................................51
References.............................................................................................................................52
List of figures
Figure 1 ER diagram................................................................................................................8
Figure 2 Data validation 1........................................................................................................9
Figure 3 Data validation 2......................................................................................................10
Figure 4 relationship diagram.................................................................................................19
Figure 5 owner design view...................................................................................................20
Figure 6 owner datasheet view..............................................................................................20
Figure 7 owner pet design view.............................................................................................20
Figure 8 owner pet datasheet view........................................................................................20
Figure 9 Vet appointment design view...................................................................................21
Figure 10 vet appointment datasheet view............................................................................21
Figure 11 Vet bill design view................................................................................................21
Figure 12 vet bill datasheet view............................................................................................21
Figure 13 vet branch design view..........................................................................................22
Figure 14 vet branch datasheet view.....................................................................................22
Figure 15 vet consultation design view..................................................................................22
Figure 16 vet consultation datasheet view.............................................................................23
Figure 17 Vet department design view...................................................................................23
Figure 18 vet department datasheet view..............................................................................23
Figure 19 Vet drug design view..............................................................................................23
Figure 20 vet drug datasheet view.........................................................................................24
Figure 21 vet manager design view.......................................................................................24
Figure 22 vet manager datasheet view..................................................................................24
Figure 23 Vet prescription design view..................................................................................24
Figure 24 vet prescription datasheet view..............................................................................25
Figure 25 vet staff design view...............................................................................................25
Figure 26 vet staff datasheet view.........................................................................................25
Figure 27 Vet treatment design view......................................................................................25
2
Technical documentation................................................................................................45
M5 Produce technical and user documentation for a fully functional system, including
diagrams showing movement of data................................................................................50
Conclusion.............................................................................................................................51
References.............................................................................................................................52
List of figures
Figure 1 ER diagram................................................................................................................8
Figure 2 Data validation 1........................................................................................................9
Figure 3 Data validation 2......................................................................................................10
Figure 4 relationship diagram.................................................................................................19
Figure 5 owner design view...................................................................................................20
Figure 6 owner datasheet view..............................................................................................20
Figure 7 owner pet design view.............................................................................................20
Figure 8 owner pet datasheet view........................................................................................20
Figure 9 Vet appointment design view...................................................................................21
Figure 10 vet appointment datasheet view............................................................................21
Figure 11 Vet bill design view................................................................................................21
Figure 12 vet bill datasheet view............................................................................................21
Figure 13 vet branch design view..........................................................................................22
Figure 14 vet branch datasheet view.....................................................................................22
Figure 15 vet consultation design view..................................................................................22
Figure 16 vet consultation datasheet view.............................................................................23
Figure 17 Vet department design view...................................................................................23
Figure 18 vet department datasheet view..............................................................................23
Figure 19 Vet drug design view..............................................................................................23
Figure 20 vet drug datasheet view.........................................................................................24
Figure 21 vet manager design view.......................................................................................24
Figure 22 vet manager datasheet view..................................................................................24
Figure 23 Vet prescription design view..................................................................................24
Figure 24 vet prescription datasheet view..............................................................................25
Figure 25 vet staff design view...............................................................................................25
Figure 26 vet staff datasheet view.........................................................................................25
Figure 27 Vet treatment design view......................................................................................25
2
Figure 28 vet treatment datasheet view.................................................................................26
Figure 29 vet bill details design view......................................................................................26
Figure 30 vet bill details datasheet view................................................................................26
Figure 31 Query 1..................................................................................................................27
Figure 32 query 2...................................................................................................................27
Figure 33 query 3...................................................................................................................28
Figure 34 query 4...................................................................................................................28
Figure 35 query 5...................................................................................................................29
Figure 36 query 6...................................................................................................................29
Figure 37 query 7...................................................................................................................30
Figure 38 Test case 1............................................................................................................32
Figure 39 Test case 2............................................................................................................33
Figure 40 Test case 3............................................................................................................33
Figure 41 Test case 4............................................................................................................34
Figure 42 Test case 5............................................................................................................35
Figure 43 Validation 1............................................................................................................36
Figure 44 validation 2.............................................................................................................37
Figure 45 treatment table.......................................................................................................38
Figure 46 Vet staff table.........................................................................................................38
Figure 47 prescription table....................................................................................................39
Figure 48 Manager table........................................................................................................39
Figure 49 drug table...............................................................................................................39
Figure 50 Department details.................................................................................................40
Figure 51 consultation table...................................................................................................40
Figure 52 Branch table...........................................................................................................40
Figure 53 Bill details table......................................................................................................41
Figure 54 Bill ID......................................................................................................................41
Figure 55 Appointment table..................................................................................................41
Figure 56 pet table.................................................................................................................41
Figure 57 owner table............................................................................................................42
Figure 58 Query 1..................................................................................................................43
Figure 59 Query 2..................................................................................................................43
Figure 60 query 3...................................................................................................................44
Figure 61 Query 4..................................................................................................................44
Figure 62 query 5...................................................................................................................45
Figure 63 owner design view.................................................................................................45
Figure 64 owner pet design view...........................................................................................45
3
Figure 29 vet bill details design view......................................................................................26
Figure 30 vet bill details datasheet view................................................................................26
Figure 31 Query 1..................................................................................................................27
Figure 32 query 2...................................................................................................................27
Figure 33 query 3...................................................................................................................28
Figure 34 query 4...................................................................................................................28
Figure 35 query 5...................................................................................................................29
Figure 36 query 6...................................................................................................................29
Figure 37 query 7...................................................................................................................30
Figure 38 Test case 1............................................................................................................32
Figure 39 Test case 2............................................................................................................33
Figure 40 Test case 3............................................................................................................33
Figure 41 Test case 4............................................................................................................34
Figure 42 Test case 5............................................................................................................35
Figure 43 Validation 1............................................................................................................36
Figure 44 validation 2.............................................................................................................37
Figure 45 treatment table.......................................................................................................38
Figure 46 Vet staff table.........................................................................................................38
Figure 47 prescription table....................................................................................................39
Figure 48 Manager table........................................................................................................39
Figure 49 drug table...............................................................................................................39
Figure 50 Department details.................................................................................................40
Figure 51 consultation table...................................................................................................40
Figure 52 Branch table...........................................................................................................40
Figure 53 Bill details table......................................................................................................41
Figure 54 Bill ID......................................................................................................................41
Figure 55 Appointment table..................................................................................................41
Figure 56 pet table.................................................................................................................41
Figure 57 owner table............................................................................................................42
Figure 58 Query 1..................................................................................................................43
Figure 59 Query 2..................................................................................................................43
Figure 60 query 3...................................................................................................................44
Figure 61 Query 4..................................................................................................................44
Figure 62 query 5...................................................................................................................45
Figure 63 owner design view.................................................................................................45
Figure 64 owner pet design view...........................................................................................45
3
Figure 65 Vet appointment design view.................................................................................45
Figure 66 Vet bill design view................................................................................................46
Figure 67 vet branch design view..........................................................................................46
Figure 68 vet consultation design view..................................................................................46
Figure 69 Vet department design view...................................................................................46
Figure 70 Vet drug design view..............................................................................................47
Figure 71 vet manager design view.......................................................................................48
Figure 72 Vet prescription design view..................................................................................48
Figure 73 vet staff design view...............................................................................................48
Figure 74 Vet treatment design view......................................................................................48
Figure 75 vet bill details design view......................................................................................49
Figure 76 Relationship diagram.............................................................................................49
Figure 77 functional dependency...........................................................................................50
List of tables
Table 1 Data dictionary..........................................................................................................10
Table 2 1 NF..........................................................................................................................16
Table 3 2 NF..........................................................................................................................17
Table 4 3 NF..........................................................................................................................18
Table 5 Test cases.................................................................................................................32
4
Figure 66 Vet bill design view................................................................................................46
Figure 67 vet branch design view..........................................................................................46
Figure 68 vet consultation design view..................................................................................46
Figure 69 Vet department design view...................................................................................46
Figure 70 Vet drug design view..............................................................................................47
Figure 71 vet manager design view.......................................................................................48
Figure 72 Vet prescription design view..................................................................................48
Figure 73 vet staff design view...............................................................................................48
Figure 74 Vet treatment design view......................................................................................48
Figure 75 vet bill details design view......................................................................................49
Figure 76 Relationship diagram.............................................................................................49
Figure 77 functional dependency...........................................................................................50
List of tables
Table 1 Data dictionary..........................................................................................................10
Table 2 1 NF..........................................................................................................................16
Table 3 2 NF..........................................................................................................................17
Table 4 3 NF..........................................................................................................................18
Table 5 Test cases.................................................................................................................32
4
Introduction
This report fundamental target is to outline and build up the VET solution database of
Cambridgeshire. The VET solution is an organization that offers veterinary services that give
the care administrations to animals. The organization requires a database for data
stockpiling, recovery, and refresh and adjust. It can be overseen by building up a database
administration framework in organization. In addition, organization requires a database for
enhancing the correspondence and data distribution amongst the Company. The designed
database for the organization will be created utilizing the software name MS SQL server
2014 which gives incredible database administration belongings to the organization. This
report will incorporate an outlining system of the VET solution database, improvement of
database and testing of the database. Lastly, a technical document and full user
documentation will be accommodated of the planned database.
5
This report fundamental target is to outline and build up the VET solution database of
Cambridgeshire. The VET solution is an organization that offers veterinary services that give
the care administrations to animals. The organization requires a database for data
stockpiling, recovery, and refresh and adjust. It can be overseen by building up a database
administration framework in organization. In addition, organization requires a database for
enhancing the correspondence and data distribution amongst the Company. The designed
database for the organization will be created utilizing the software name MS SQL server
2014 which gives incredible database administration belongings to the organization. This
report will incorporate an outlining system of the VET solution database, improvement of
database and testing of the database. Lastly, a technical document and full user
documentation will be accommodated of the planned database.
5
LO1 Use an appropriate design tool to design a relational database
system for a substantial problem
P1. Design a relational database system using appropriate design tools
and techniques
For planning the VET arrangement organization's database, initially a suitable outlined will
be set up as the model like ERD chart utilizing a planning instrument. The organization VET
solution is expanding its outlet in various nations, it is important for organization to move to
the database administration framework. The clients and employee of the organization are
expanding step by step so to deal with this circumstance organization chooses to move to
the database administration framework rather than the database done manually on paper.
Organization needs a database which could include and adjust the patient points of interest
and employee detail, likewise, the distinct database must deal with the data of all the VET
solution branches. It is required to build a database for making an appropriate printed invoice
for the clients (Oracle, 2017).
Requirement of database administration framework
The vital goal of an organization to picks the database administration framework is to create
and protect the relationship that is present in the VET solution organization information
(Elmasri, and Navathe, 2010).
Client Requirement
It is necessary for the database to have the capacity to alter the data embedded.
Database made must record and oversee detail data of Pets (i.e. patients).
Database must be ready to manage and add the employee record.
Database made must record and oversee detail data of Owners to which the pet
belongs too.
Database made ought to have the capacity to include and deal with the receipt subtle
elements to Owner of the pet for medicines of their pet.
Database must have the capacity to manage and add the bill details of pets at all
centers of VET solution.
Database must have the capacity to manage and include all the pet details like
treatment given, diagnosis done, drugs and more.
Database must have the capacity to include and deal with the points of interest of
administrations (i.e. services) offer to pet.
6
system for a substantial problem
P1. Design a relational database system using appropriate design tools
and techniques
For planning the VET arrangement organization's database, initially a suitable outlined will
be set up as the model like ERD chart utilizing a planning instrument. The organization VET
solution is expanding its outlet in various nations, it is important for organization to move to
the database administration framework. The clients and employee of the organization are
expanding step by step so to deal with this circumstance organization chooses to move to
the database administration framework rather than the database done manually on paper.
Organization needs a database which could include and adjust the patient points of interest
and employee detail, likewise, the distinct database must deal with the data of all the VET
solution branches. It is required to build a database for making an appropriate printed invoice
for the clients (Oracle, 2017).
Requirement of database administration framework
The vital goal of an organization to picks the database administration framework is to create
and protect the relationship that is present in the VET solution organization information
(Elmasri, and Navathe, 2010).
Client Requirement
It is necessary for the database to have the capacity to alter the data embedded.
Database made must record and oversee detail data of Pets (i.e. patients).
Database must be ready to manage and add the employee record.
Database made must record and oversee detail data of Owners to which the pet
belongs too.
Database made ought to have the capacity to include and deal with the receipt subtle
elements to Owner of the pet for medicines of their pet.
Database must have the capacity to manage and add the bill details of pets at all
centers of VET solution.
Database must have the capacity to manage and include all the pet details like
treatment given, diagnosis done, drugs and more.
Database must have the capacity to include and deal with the points of interest of
administrations (i.e. services) offer to pet.
6
Framework Requirement
For planning and dealing with the database, a framework with several requirements is
essential. There are certain software and programming necessities for building up the
database for the VET solution;
Database needs to work proficiently and adequately.
Database ought to have unmistakable recognizable proof and proper introduction
including name, address, contact etc.
Database must have the capacity to perform quickly and have great ease of use
(Stonebraker, Deng and Brodie, 2017).
For database development of the VET solution some hardware and software requirements
are;
Software Requirements
a. Windows or Linux operating system.
b. MS SQL server
c. Several tools for designing like UML, draw.io.
Hardware requirements
a. Minimum size of hard disk must be 2GB.
b. Minimum 2GB of RAM.
c. Processor of either 32 or 64-bit.
d. Display resolution of 1024*468.
ER diagram
It is an abbreviation for entity relationship chart. ER graph is the best top-down methodology
for planning the database for organizations. It is the theoretical graph that is utilized to
characterize the connection among the few entities. For constructing an ER chart first the
elements are distinguished and subsequently, the relations are found among the recognized
entities. Certain more information is included for every element called traits which
characterizes the attributes of an Entity (Datanamic, 2017).
Entity
It is an accumulation of items having comparative qualities that are, distinguished by an
association as having an unrestricted presence.
Attribute:
Each identified entity has particular qualities called as attributes.
7
For planning and dealing with the database, a framework with several requirements is
essential. There are certain software and programming necessities for building up the
database for the VET solution;
Database needs to work proficiently and adequately.
Database ought to have unmistakable recognizable proof and proper introduction
including name, address, contact etc.
Database must have the capacity to perform quickly and have great ease of use
(Stonebraker, Deng and Brodie, 2017).
For database development of the VET solution some hardware and software requirements
are;
Software Requirements
a. Windows or Linux operating system.
b. MS SQL server
c. Several tools for designing like UML, draw.io.
Hardware requirements
a. Minimum size of hard disk must be 2GB.
b. Minimum 2GB of RAM.
c. Processor of either 32 or 64-bit.
d. Display resolution of 1024*468.
ER diagram
It is an abbreviation for entity relationship chart. ER graph is the best top-down methodology
for planning the database for organizations. It is the theoretical graph that is utilized to
characterize the connection among the few entities. For constructing an ER chart first the
elements are distinguished and subsequently, the relations are found among the recognized
entities. Certain more information is included for every element called traits which
characterizes the attributes of an Entity (Datanamic, 2017).
Entity
It is an accumulation of items having comparative qualities that are, distinguished by an
association as having an unrestricted presence.
Attribute:
Each identified entity has particular qualities called as attributes.
7
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Database Assignment: Designing and Developing a Relational Database for VET Solutionlg...
|46
|4388
|345
Database Design and Development for VET SOLUTIONlg...
|44
|3882
|307
Database Design and Development for Shoengalleric Art Gallerylg...
|66
|5608
|329
Dominican College Course Management Database: Design, Implementation, and Documentationlg...
|59
|6147
|338
Database Design and Development for Shoengalleric Art Gallerylg...
|74
|6401
|66
VET Solutions Database System: Design, Implementation, and Testinglg...
|34
|4716
|260