CN5000 Database Systems Assignment Sample
DESIGN, DEVELOP AND DOCUMENT A PROTOTYPE DATABASE SYSTEM
Introduction
This report focuses on the design and implementation of a database system using the oracle interface. According to the requirement the database is going to solve the purpose of storing data related to the covid-19 vaccines and the patients who are going to take an appointment for the upcoming covid-19 vaccination slot. The name of the schema is taken as “medical” and there is only one table present in the database. This table contains several details of the patients and the table namely “patients” also preserves information about the vaccination. In order to develop the patients details table some of the prerequisites have to be checked such as this table must have at least one column represented as the primary key. There must be fields acquiring integrity constraints.
Moreover, this database is going to be designed to serve purposes of data warehousing for the “NHS Health Services”. Since covid-19 is a vital issue worldwide and many countries have already started their agendas for providing vaccination to their country’s people. This UK-based health service centre has also taken such initiatives to distribute vaccines to their patients and for that reason they need to maintain such infrastructure where they can record all the vital details of their patients in order to make a successful vaccination event for each and every covid patient.
Design and Implementation:
This section is going to assess on the design and implementation of the criteria’s mentioned to develop the database system to record the covid vaccination data. In order to build a database using oracle, first one needs to install oracle software on their system (Petković, 2017). Oracle database 11g express edition is used for implementing the database and the following table creation. With this software, a database schema is created by clicking and giving all relevant details to the application express and then clicking to login will redirect to the object browser section.
Figure 1: SQL Workshop Interface
(Created by Learner)
From the object browser, a table is going to be created and after the creation of the schema of the table named patients commit the task in order to save the table schema. For this particular table the columns are taken as mentioned in the requirement. The columns used for making this table are the patient Id, name, date of birth, age, gender, address, phone, email, appointment date, vaccinated, vaccine name, batch number, dose number, vaccine ordered, and vaccine price. Here, patient Id is the only primary key and some of the other fields are imposed some integrity constraints to make the field’s record as practical as possible. In case of fully vaccinated people the status of the vaccinated is recorded as “yes” otherwise those who still did not take any vaccine or those who have taken only their first vaccine is going to be recorded as a “no”.
Integrity Constraints
Integrity constraints are defined in a simpler way as a set of rules applied on the columns to make the columns take input in a more specific way. Therefore, the quality of taking inputs to the specific columns is enhanced. An example of predefined integrity constraints in the oracle software is “NOT NULL” constraint. This constraint ensures that the column is going to have any input given by the user otherwise there will be no new row created for that particular value insertion. Other than that there are some of the user defined integrity constraints that are given as the requirement of the task (Al-Dhaqm et al. 2020). These constraints are going to be applied to each column of the table in order to run this database correctly.
Figure 2: Creation of the table patients
(Created by Learner)
The first criteria of the integrity constraint to be applied on a specific column of the table are to take an input of the gender column as ‘M’ for male and as ‘F’ for female. The command used for creating the constraint is “alter table “PATIENTS” add check (GENDER IN (‘M’, ‘F’))”. The next constraint is applied on the name column of the patients table which states that the name field should avoid using any numeric values within it. The corresponding command used for generating this integrity constraint is “alter table “PATIENTS” add check (name NOT LIKE ‘%[^0-9]%’)”. Third integrity constraint is used for the column email and the constraint added for this field is that email id must have ‘@’ within each of the records. The respective command for this constraint is alter table “”PATIENTS” add check (EMAIL LIKE (‘%@%’))”. Similarly, like this way all the other integrity constraints are written on the SQL command window and run successfully.
SQL Queries
SQL or Structured Query Language is a query based language where numerous data can be fetched at a time by writing a single query in this language. The query of SQL is used to collect a set of data present in a particular table by inquiring with a similar attribute value present within the table. Here seven SQL queries need to be written in the SQL command window in order to gather some specific information related to the SQL queries. This is the process of fetching the data after the insertion of data within the table. As influenced by Wee and Nayak, 2019, this is always an easier process to access data from a table containing numerous numbers of data. So for that purpose, first one needs to know about the query to display a suitable result for the respective query. The first query of the task is to display all the patients’ details ordered by their names.
Figure 3: Output of the First Query
(Created by Learner)
The command used to implement this query is “select * from patients order by name;” the next query is to display the patient’s name by their gender. The query written for this question is “select name from patients where gender=’M’;” the screenshots are given below with their respective outputs.
Figure 4: Output of the Second Query
(Created by Learner)
Figure 5: Output of the Third Query
(Created by Learner)
Figure 6: Output of the Fourth Query
(Created by Learner)
Figure 7: Output of the Fifth Query
(Created by Learner)
Trigger
The trigger is mentioned in the requirement to create a new table namely fully_vaccinated for those who have received two dosages and the status of the vaccinated column is ‘yes’ the creation of the trigger will generate another table row for those who have validated these two criteria’s and in this case after trigger is called (Deng et al. 2018). The trigger can insert, update and delete the table data if required. However, if the trigger does not match any of the two criteria then there will be an error message generated for violating the trigger code.
Figure 10: Code of the Trigger query
(Created by Learner)
Evaluation of the work
This section is going to describe the evaluation and analysis of the work carried out in the oracle software. The purpose of this assessment is mentioned above and as per the requirement an oracle based database has been designed in order to save data within the table of the database. A SQL based queries has been run on the SQL command window in order to fetch the required data from the table and also a correct execution of the SQL queries are the sign of the creating a flawless database table which can derive results correctly form the table as per the user’s requirement. The study says that SQL is a widely used language for creating databases, creating tables within that database, inserting new data within the column of the table, deleting and updating entries present in the table of the database (Hamzah et al. 2019). This is a comparatively easy and less affordable procedure than storing and searching data manually which is used be followed traditionally. Specifically, this report is meant for developing a medical database for storing data within a table namely patients regarding the details of the patients and their covid vaccination schedule in order to maintain a track record of each of the patients visited that health care centre. The above discussion has clearly mentioned the processes of completing each of the requirements provided on the task.
Conclusion
In conclusion, this can be stated that oracle is used for maintaining a collection of data in a unit. The data can be stored as numeric format or character format or textual format or can be preserved in any other data type format. The main purpose of using oracle database rather than any other database software is that oracle gives an user friendly interface to their users to access the data of the database. Additionally, this provides a high efficiency of storing data and provides an authenticity of accessing datasets by keeping them in a password protected interface. Another good quality of using oracle is the “failure recovery feature” of the oracle interface.
References
Caldarelli, G. and Ellul, J., 2021. The Blockchain Oracle Problem in Decentralized Finance—A Multivocal Approach. Applied Sciences, 11(16), p.7572.
Deng, Y., Zhu, L., Cai, H., Wang, G. and Liu, B., 2018. Autophagic compound database: a resource connecting autophagy‐modulating compounds, their potential targets and relevant diseases. Cell proliferation, 51(3), p.e12403.
Fabri-Ruiz, S., Saucède, T., Danis, B. and David, B., 2017. Southern Ocean Echinoids database–An updated version of Antarctic, Sub-Antarctic and cold temperate echinoid database. ZooKeys, (697), p.1.
HAMZAH, M.L., Rukun, K., Rizal, F. and PURWATI, A.A., 2019. A review of increasing teaching and learning database subjects in computer science. Revista ESPACIOS, 40(26).
Petković, D., 2017. JSON integration in relational database systems. Int J Comput Appl, 168(5), pp.14-19.
Wee, C.K. and Nayak, R., 2019. Adaptive load forecasting using reinforcement learning with database technology. Journal of Information and Telecommunication, 3(3), pp.381-399.
………………………………………………………………………………………………………………………..
Know more about UniqueSubmission’s other writing services:
