Database Assignment: Designing and Developing a Relational Database for VET Solution
Added on 2024-05-21
46 Pages4388 Words345 Views
|
|
|
Database Assignment
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
Role of database management system............................................................................6
User Requirement............................................................................................................6
System Requirement........................................................................................................7
Hardware Requirement....................................................................................................7
Software requirement......................................................................................................7
ER diagram....................................................................................................................... 7
Entities:.............................................................................................................................8
Attributes:........................................................................................................................ 8
Relation:........................................................................................................................... 8
M1 produce a comprehensive design for a fully functional system which includes interface
and output designs, data validations, data normalisation and data dictionary....................9
Data Validation.................................................................................................................9
Data dictionary.................................................................................................................9
Data normalization.........................................................................................................12
Un-normalization............................................................................................................12
First Normalization.........................................................................................................13
Second Normalization.................................................................................................... 14
Third Normalization........................................................................................................14
LO2 Develop a fully functional relational database system, based on an existing system
design..................................................................................................................................... 16
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
Role of database management system............................................................................6
User Requirement............................................................................................................6
System Requirement........................................................................................................7
Hardware Requirement....................................................................................................7
Software requirement......................................................................................................7
ER diagram....................................................................................................................... 7
Entities:.............................................................................................................................8
Attributes:........................................................................................................................ 8
Relation:........................................................................................................................... 8
M1 produce a comprehensive design for a fully functional system which includes interface
and output designs, data validations, data normalisation and data dictionary....................9
Data Validation.................................................................................................................9
Data dictionary.................................................................................................................9
Data normalization.........................................................................................................12
Un-normalization............................................................................................................12
First Normalization.........................................................................................................13
Second Normalization.................................................................................................... 14
Third Normalization........................................................................................................14
LO2 Develop a fully functional relational database system, based on an existing system
design..................................................................................................................................... 16
1
P2. Once the ERD designs in P1 were accepted you have been asked to develop the
database system using MS SQL Server 2014......................................................................16
Relational Database........................................................................................................16
Design view of tables......................................................................................................17
P3. Produce a query language, with queries across multiple tables for the database.......22
M2 Implement a fully functional database system which includes system security and
database maintenance.......................................................................................................27
LO3 Test the system against user and system requirements:................................................28
P4 Create a test plan to test the database:........................................................................28
M4 Create and populate tables, validation, verification, data types and referential
integrity against user requirements...................................................................................32
LO4 Produce technical and user documentation...................................................................34
P5. Produce fully technical and user documentation for your designed database for VET
SOLUTION Company...........................................................................................................34
User documentation.......................................................................................................34
Technical documentation...............................................................................................39
M5 produce technical and user documentation for a fully functional system...................42
Conclusion.............................................................................................................................. 43
References..............................................................................................................................44
List of figures
Figure 1 ER diagram..................................................................................................................8
Figure 2 Data Validation for VET solution.................................................................................9
Figure 3 Relational Schema for VET solutions........................................................................16
Figure 4 Design view of Doctor...............................................................................................17
Figure 5 Datasheet view of Doctor.........................................................................................17
Figure 6 Design view of Clinic.................................................................................................17
Figure 7 Datasheet view of Clinic...........................................................................................18
2
database system using MS SQL Server 2014......................................................................16
Relational Database........................................................................................................16
Design view of tables......................................................................................................17
P3. Produce a query language, with queries across multiple tables for the database.......22
M2 Implement a fully functional database system which includes system security and
database maintenance.......................................................................................................27
LO3 Test the system against user and system requirements:................................................28
P4 Create a test plan to test the database:........................................................................28
M4 Create and populate tables, validation, verification, data types and referential
integrity against user requirements...................................................................................32
LO4 Produce technical and user documentation...................................................................34
P5. Produce fully technical and user documentation for your designed database for VET
SOLUTION Company...........................................................................................................34
User documentation.......................................................................................................34
Technical documentation...............................................................................................39
M5 produce technical and user documentation for a fully functional system...................42
Conclusion.............................................................................................................................. 43
References..............................................................................................................................44
List of figures
Figure 1 ER diagram..................................................................................................................8
Figure 2 Data Validation for VET solution.................................................................................9
Figure 3 Relational Schema for VET solutions........................................................................16
Figure 4 Design view of Doctor...............................................................................................17
Figure 5 Datasheet view of Doctor.........................................................................................17
Figure 6 Design view of Clinic.................................................................................................17
Figure 7 Datasheet view of Clinic...........................................................................................18
2
Figure 8 Design view of Staff..................................................................................................18
Figure 9 Datasheet view of Staff.............................................................................................18
Figure 10 Design view of Appointment..................................................................................18
Figure 11 Datasheet view of Appointment.............................................................................19
Figure 12 Design view of petOwner.......................................................................................19
Figure 13 Datasheet view of petOwner..................................................................................19
Figure 14 Design view of Patient............................................................................................20
Figure 15 Datasheet view of Patient......................................................................................20
Figure 16 Design view of veterinary.......................................................................................20
Figure 17 datasheet view of Veterinary.................................................................................20
Figure 18 design view of Invoice............................................................................................ 21
Figure 19 datasheet view of Invoice.......................................................................................21
Figure 20 Query1 output........................................................................................................22
Figure 21 Query2 output........................................................................................................23
Figure 22 Query3 output........................................................................................................23
Figure 23 Query4 output........................................................................................................24
Figure 24 Query5 output........................................................................................................25
Figure 25 Query6 output........................................................................................................26
Figure 26 Query7 output........................................................................................................26
Figure 27 Test case 1.............................................................................................................. 29
Figure 28 Test case 2.............................................................................................................. 29
Figure 29 Test case 3.............................................................................................................. 30
Figure 30 Test case 4.............................................................................................................. 30
Figure 31 Test case 5.............................................................................................................. 31
Figure 32 Validation 1.............................................................................................................32
Figure 33 Validation 2.............................................................................................................33
Figure 34 Doctor table............................................................................................................34
Figure 35 Clinic table.............................................................................................................. 34
Figure 36 Staff table............................................................................................................... 35
Figure 37 Invoice table........................................................................................................... 35
Figure 38 Appointment table................................................................................................. 35
Figure 39 Owner Table........................................................................................................... 35
3
Figure 9 Datasheet view of Staff.............................................................................................18
Figure 10 Design view of Appointment..................................................................................18
Figure 11 Datasheet view of Appointment.............................................................................19
Figure 12 Design view of petOwner.......................................................................................19
Figure 13 Datasheet view of petOwner..................................................................................19
Figure 14 Design view of Patient............................................................................................20
Figure 15 Datasheet view of Patient......................................................................................20
Figure 16 Design view of veterinary.......................................................................................20
Figure 17 datasheet view of Veterinary.................................................................................20
Figure 18 design view of Invoice............................................................................................ 21
Figure 19 datasheet view of Invoice.......................................................................................21
Figure 20 Query1 output........................................................................................................22
Figure 21 Query2 output........................................................................................................23
Figure 22 Query3 output........................................................................................................23
Figure 23 Query4 output........................................................................................................24
Figure 24 Query5 output........................................................................................................25
Figure 25 Query6 output........................................................................................................26
Figure 26 Query7 output........................................................................................................26
Figure 27 Test case 1.............................................................................................................. 29
Figure 28 Test case 2.............................................................................................................. 29
Figure 29 Test case 3.............................................................................................................. 30
Figure 30 Test case 4.............................................................................................................. 30
Figure 31 Test case 5.............................................................................................................. 31
Figure 32 Validation 1.............................................................................................................32
Figure 33 Validation 2.............................................................................................................33
Figure 34 Doctor table............................................................................................................34
Figure 35 Clinic table.............................................................................................................. 34
Figure 36 Staff table............................................................................................................... 35
Figure 37 Invoice table........................................................................................................... 35
Figure 38 Appointment table................................................................................................. 35
Figure 39 Owner Table........................................................................................................... 35
3
Figure 40 Patient table........................................................................................................... 36
Figure 41 Veterinary table......................................................................................................36
Figure 42 query 1................................................................................................................... 36
Figure 43 query 2................................................................................................................... 37
Figure 44 query 3................................................................................................................... 37
Figure 45 query 4................................................................................................................... 38
Figure 46 query 5................................................................................................................... 38
Figure 47 doctor table design view........................................................................................39
Figure 48 clinic table design view...........................................................................................39
Figure 49 staff table design view............................................................................................39
Figure 50 appointment table design view..............................................................................39
Figure 51 owner table design view.........................................................................................40
Figure 52 patient table design view....................................................................................... 40
Figure 53 veterinary table design view...................................................................................40
Figure 54 invoice table design view........................................................................................40
Figure 55 relationship diagram...............................................................................................41
Figure 56 Functional dependency..........................................................................................42
List of Tables
Table 1 Data dictionary for VET solution................................................................................10
Table 2 Un-normalization.......................................................................................................12
Table 3 First normalization.....................................................................................................13
Table 4 second normalization................................................................................................ 14
Table 5 third normalization....................................................................................................14
Table 6 Test Case....................................................................................................................28
4
Figure 41 Veterinary table......................................................................................................36
Figure 42 query 1................................................................................................................... 36
Figure 43 query 2................................................................................................................... 37
Figure 44 query 3................................................................................................................... 37
Figure 45 query 4................................................................................................................... 38
Figure 46 query 5................................................................................................................... 38
Figure 47 doctor table design view........................................................................................39
Figure 48 clinic table design view...........................................................................................39
Figure 49 staff table design view............................................................................................39
Figure 50 appointment table design view..............................................................................39
Figure 51 owner table design view.........................................................................................40
Figure 52 patient table design view....................................................................................... 40
Figure 53 veterinary table design view...................................................................................40
Figure 54 invoice table design view........................................................................................40
Figure 55 relationship diagram...............................................................................................41
Figure 56 Functional dependency..........................................................................................42
List of Tables
Table 1 Data dictionary for VET solution................................................................................10
Table 2 Un-normalization.......................................................................................................12
Table 3 First normalization.....................................................................................................13
Table 4 second normalization................................................................................................ 14
Table 5 third normalization....................................................................................................14
Table 6 Test Case....................................................................................................................28
4
Introduction
This assessment main objective is to design and develop a database for the VET solution of
Cambridgeshire. VET solution is a veterinary company that provides the care services for
animals. The company needs a database for information storage, retrieval, and update and
alter. It could be managed by developing a database management system in the company.
Moreover, company needs a database for improving the communication and information
sharing among the Company. The database for the company will be developed using the MS
SQL server 2014 that provides a great database management properties to the company.
This report is going to include designing procedure of database, development of database
and its testing and finally a full user and technical documentation will be provided for the
designed database.
5
This assessment main objective is to design and develop a database for the VET solution of
Cambridgeshire. VET solution is a veterinary company that provides the care services for
animals. The company needs a database for information storage, retrieval, and update and
alter. It could be managed by developing a database management system in the company.
Moreover, company needs a database for improving the communication and information
sharing among the Company. The database for the company will be developed using the MS
SQL server 2014 that provides a great database management properties to the company.
This report is going to include designing procedure of database, development of database
and its testing and finally a full user and technical documentation will be provided for the
designed 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 designing the VET solution company’s database, firstly an appropriate designed will be
prepared as the prototype like ERD diagram using a design tool. As the VET solution is
increasing its branch in different countries, it is must for the company to shift on the
database management system (Pavlo, Angulo, Arulraj, 2017). The customers and staff of the
company are increasing day by day so to handle this situation company decided to go for a
database management system instead of a manual database on paper. The company
requires a database that could add and alter the patient details and staff detail, also the
single database must handle the information of all the branches of the company. The
database is needed for creating a properly printed bill for the customers (Krishnamurthy,
Thombre, Conway, et.al, 2014).
Role of database management system
The important objective for which the company chooses the database management system
is to develop and preserve the relationships that exist among the data of the VET solution
company.
User Requirement
Database must be able to edit the information inserted.
Database created should record and manage detail information of Patients (i.e.
Pets).
Database should able to add manage the record of staff.
Database created should record and manage detail information of Pet Owners.
Database created should be able to add and manage the invoice details to pet
Owner for their pet treatments.
Database must be able to add and manage the appointment details of the patient at
all clinics.
6
a substantial problem
P1. Design a relational database system using appropriate design tools and
techniques
For designing the VET solution company’s database, firstly an appropriate designed will be
prepared as the prototype like ERD diagram using a design tool. As the VET solution is
increasing its branch in different countries, it is must for the company to shift on the
database management system (Pavlo, Angulo, Arulraj, 2017). The customers and staff of the
company are increasing day by day so to handle this situation company decided to go for a
database management system instead of a manual database on paper. The company
requires a database that could add and alter the patient details and staff detail, also the
single database must handle the information of all the branches of the company. The
database is needed for creating a properly printed bill for the customers (Krishnamurthy,
Thombre, Conway, et.al, 2014).
Role of database management system
The important objective for which the company chooses the database management system
is to develop and preserve the relationships that exist among the data of the VET solution
company.
User Requirement
Database must be able to edit the information inserted.
Database created should record and manage detail information of Patients (i.e.
Pets).
Database should able to add manage the record of staff.
Database created should record and manage detail information of Pet Owners.
Database created should be able to add and manage the invoice details to pet
Owner for their pet treatments.
Database must be able to add and manage the appointment details of the patient at
all clinics.
6
Database must be able to add and manage the diagnosis and treatment details given
to the Pet.
Database must be able to add and manage the details of services (i.e. treatment)
given to pet (Coronel, and Morris, 2016).
System Requirement
For designing and managing a database a system with least specifications is required. There
are some hardware and software necessities for developing the database.
Database must work efficiently and effectively.
Database should have distinctive identification and appropriate presentation with
name, contact, address and more.
Database must be able to perform fast and have high usability (Coronel, and Morris,
2016).
For designing the VET solution database some requirements are needed that are;
Hardware Requirement
1. A 64-bit processor.
2. Hard disk of minimum 2 GB is required.
3. RAM of minimum 2 GB is required.
4. Resolution display of 1024*468.
Software requirement
1. An operating system like Windows, Linux etc.
2. Design tool like Draw.io, star UML for ERD design.
3. MS SQL server 2014.
ER diagram
It is an acronym for entity relationship diagram. ER diagram is a top-down approach to
designing the database. It is a conceptual diagram that is used to define the relationships
among the several entities. For building an ER diagram first the entities are identified and
after that relation is represented among the identified entities. Some more data is added to
each entity called attributes which defines the characteristics of the Entities (Lucidchart,
2017).
7
to the Pet.
Database must be able to add and manage the details of services (i.e. treatment)
given to pet (Coronel, and Morris, 2016).
System Requirement
For designing and managing a database a system with least specifications is required. There
are some hardware and software necessities for developing the database.
Database must work efficiently and effectively.
Database should have distinctive identification and appropriate presentation with
name, contact, address and more.
Database must be able to perform fast and have high usability (Coronel, and Morris,
2016).
For designing the VET solution database some requirements are needed that are;
Hardware Requirement
1. A 64-bit processor.
2. Hard disk of minimum 2 GB is required.
3. RAM of minimum 2 GB is required.
4. Resolution display of 1024*468.
Software requirement
1. An operating system like Windows, Linux etc.
2. Design tool like Draw.io, star UML for ERD design.
3. MS SQL server 2014.
ER diagram
It is an acronym for entity relationship diagram. ER diagram is a top-down approach to
designing the database. It is a conceptual diagram that is used to define the relationships
among the several entities. For building an ER diagram first the entities are identified and
after that relation is represented among the identified entities. Some more data is added to
each entity called attributes which defines the characteristics of the Entities (Lucidchart,
2017).
7
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Database Design and Development for VET SOLUTIONlg...
|44
|3882
|307
Database Assessment for VET Solution Companylg...
|52
|4900
|369
Database Assignment: Designing a Relational Database System for VET Solution Companylg...
|42
|4665
|372
VET Solution Database System Design and Implementationlg...
|43
|5158
|484
Database Assignment: Designing and Implementing a Relational Database System for VET Solutionslg...
|41
|4497
|118
Database Design and Development for Shoengalleric Art Gallerylg...
|66
|5608
|329