Required features
The factInternetsales have been provided in this fact table and the customer product and date dimensions are used for this table.
Users and Permissions (describing the permissions and access rights)
Relational database system:
A relational database system is one of the common type of database system where the data stored in the tables. In the current time all the database that are used in the businesses are the relational system. The relational database has the quality to handle the complex queries. But the relational database takes more space for the flat files. As the relational system takes the most of the memory in the system so this can be called less efficient. The databases which are modern uses multiple table as the standard. The data in the relational system is store in many more tables or we can say in ‘relations’. The tables or the relations are divided in to two parts the first part id record that is the row and the second part is the fields that is the columns. There are several characteristics of the relational system like one record of the table can be linked with an another record of the table, one record can be linked with many more records with another table and many records can be linked to many records of the another table. Relational system also known as SQL (Structured Query Language). The SQL query is capable of to create, modify and delete a table as well as it can insert and select the data also. The examples of the relational databases are MySQL, PostgreSQL, Oracle DB, SQL Server, etc. the advantages of the relational system is manageability, error avoidance and flexibility.
Non-relational database system:
A non-relational database is a system where tables and the keys are not incorporated. The non-relational databases requires manipulation techniques of the data and some processes that are designed to provide big data problem solutions that a large companies are faced. The most emerging non-relational database is known as the NoSQL (Not Only SQL). Most of the non-relational system are mainly used in the websites like the Google, Facebook, and Amazon. These websites faces huge traffic so they need a database that is non-relational. The several advantage of the NoSQL are it can use object oriented programming, it is efficient, agile sprints, can handle a large volume of data that are structured, unstructured and semi-structured.
Relational database system
Reason for preference: Non-relational Model
The main difference from the relational (SQL) system is the explicit data scheme is lacking. The NoSQL database is infer the scheme from the data that is stored, if the requirement is there and that depends on which model is used in the system. The main benefit for what this non-relational is got preference is the work these models do is done efficiently and perfectly. The system can’t perform the task what they are not designed from. This is the main thing to understand that which can be done by the NOSQL system and if this type of task given then it will be performed successfully.
Reason for preference: Relational Model
When we want to create data storage design that easily stores the data also scalable and flexible then the relational model is the best option. The relational model is easy to manipulate. The data of the each table can be updated without affecting another table. Certain sets of data can be shared. The extension of the database is very simple. If the records are increasing in the table then it will automatically scalable to grow with the data. The mistakes are easy to check and can be corrected in the relational database model.
Mongo DB:
The MongoDB is a database which is open source in nature. The Mongo database uses a data model which is document-oriented. Mongo database come under the banner of NoSQL. Mongo database is built on an architecture of the collections and documents instead of using the tables like the relational database. The basic units of the Mongo database is the documents that comprises the sets of the key value pairs. The collected contains of the sets of the documents and the functions they are same as the relational database model. The Mongo database supports the design schema that is dynamic, and allow the documents in a collection to have the different structures. BSON is the database data interchange format that provides a representation in binary of the JSON documents. The MongoDB was created by Dwight Merriman and Eliot Horowitz, during the development they have faced different issues like scalability. The issues was mainly seemed when the database was applying in the web applications. This database released in the open source in the year 2009. The Mongo database is available as the free software foundation’s GNU AGPL V3.0 commercial license. There are many users of the Mongo database in the current market like the MetLife insurance company is using the database for their customer service, the CERN that is a lab of physics using the database for data aggregation, The New York Times that is a newspaper uses the database to support a from- building for the photo submission application.
Non-relational database system
Advantages of non-relational systems: MongoDB
The several advantages of the MongoDB are the schema is less in this database, the structure of the single object is clear, there are no complex joins, supports the dynamic quality, it is easy to scale, tuning, uses the internal memory for storing.
Disadvantaged of non-relational system: MongoDB
There are several disadvantages of the MongoDB like some wrong operation in the database can lock the total database, many things have to update manually, lots of RAM is used by the servers, there may be some performance issue as the technology is new, as mongo database is not a relational database so it is very difficult to write query. To access the Mongo database a database administration need who must know how to control the database and this will increase the cost of the company.
The term CAP consist of the three term that are Consistency, Availability, and Partition tolerance. CAP theorem is named by Eric Brewer and the theorem is also known as the Brewer’s theorem. The CAP theorem says that any system that is distributed cannon guaranty the three aspects of the CAP simultaneously, however, the trade-offs must made in time to achieve the level of the availability and the performance that is required for a particular task.
Consistency: all the nodes can see the data in the same time. When performing a operation of read it will return the most recent value of the write operation because all the notes will return the same data. A system will have the consistency if only started transaction is continued to a stable state, and end with consistent state with the system. Some of the consistent relational databases are: SQL server, MySQL and the PostgreSQL.
Availability: for the each request it gets a response whether the request is successful or it has failed. To achieve the availability in the distributed system the main thing is that the system need to do the operation constantly all the time. Each n every client will get their response regardless the state of the nodes in the system. This metric measure is trivial: either the user can submit the read and write command or the user cannot.
Partition Tolerance: the system will continues to work the loss of the message or failure. Most of the people thinks that their data are stored in the single node in the network. The data records are sufficiently replicated across the all combinations of the nodes and to the networks to keep up the system through the intermittent outages. Note that the failure of a single node can count as a ‘partition’.
Reason for preference: Non-relational Model
To secure the server there are some permission that has been set. There are two tope of method by which the permission are assigned. The first method is using the T-SQL. The another method is the SSMS that is the SQL Server management Studio. To set the permission using the SSMS there are several steps. The steps are given below:
Step 1:
First we have to connect to the instance and we have to expand the folders.
Step 2:
There will be a folder named TestUser and we have to right click on that and open the properties. Then a pop up window will come.
Step 3:
Then we have to click on the search and have to select the specific options. Then click on the object types, then click on to the tables and the click on the browse. Then select the TestTable and then click OK. Then another screen will appear.
Step 4:
Select checkbox for Grant column under Select permission and click OK.
Step 5 Select permission on ‘TestTable’ of TestDB database granted to ‘TestUser’. Click OK.
GRANT
When a user want to do something, he or she should be given permission to do it. We do this in the SQL using the GRANT command. If the user is authenticated then the GRANT option will have to apply.
Revoke:
The REVOKE command undoes the permission, whether the decisions is the GRANT or the DENY. After the REVOKE command is called and if after that we check the permission we can see the grant is there and if we again revoke then the user cannot able to do the query again as the GRANT will be revoked.
DENY:
DENY option blocks the access. This option is trumps over the all other accesses. If the user put the both GRANT and the DENY option together then the priority of the DENY will be high. The DENY will be taken when the un authorised user tries to access the SQL server.
The main objective of the developing the health care record is to managing the data regarding the patient . The information that will be kept in the health record will include basic information about the medical condition of the patient and the past medical history and the existing medical problems. The record will also include the details about the insurance coverage of the patient. The classification of the insurance is needed to be done while implementing the health care record. The system containing the data base of the patient can be accessed through online. The development of the entity relation diagram model has been followed on the basis of the requirements specified in the case study.
Reason for preference: Relational Model
There are ten entities present in the database. The name of these entities are – insurance provider, patient, condition, condition_ info,Patient_Treatment, Ttreat_Location, Treatment, Medication, Medicione_Treatment,Doctor. All these entities has certain attributes. The entities has different individual functions are connected to each other. The Insurance_Provider has the attributes which includes type of the insurance, name of the insurance provider address and the service provider’s id. The entity patient contains the id of the patient, which includes first and last name of the patient, age, address, gender of the patient along with occupation and the health condition. The identification of the patient is made easier with the use of insurance id of the following patient which is also included as the attribute. The primary key for the patient entity is the id of the patient. The condition id and the insurance provider_id works as a foreign key in this entity. The condition entity has the primary key as condition_id, whereas the patient_id and the condition _info works as the foreign key. The condition_info holds the mane and the online resources. The patient entity is connected with the Patient_treatment which has the attributes patient_id and treatment_Id. Here both the attributes act as a foreign key. The patient_treatment is further connected to the Treatment entity which has primary _key treatment_id and doctor_id,condition_id and treat_location_id act as foreign keys. The Treatment entity is connected with the Treat_location, medicine_treatmet, doctor and medication. In treat _location primary key is treat_location_idand the foreign key is location provider. The primary key for the doctor entity is the id of the doctor.
The database serves its purpose effectively and it meets all the requirements mentioned in the case study. However, certain improvement can be made adding the additional features in the data base such as the addition of the new entity which will help to merge more than one entity. This will help to make the database more flexible and easy to maintain.
The future improvements and the extension of the database regarding the medical health record can be done. Certain features like listing of the necessary drugs for the emergency case can be stored in the database, so that the patients can select the right medicine in the emergency case.
The SQL queries are written which supports the entity relationship diagram. The heath related problems can be accessed from the blood pressure and the disease table by using the patient id. The patient can update the information and the updated information will be stored in the Patient Education Resource table. The patient advice query will generate information from the suggestion advice. The treatment report can be accessed by the patient and the hospitals using the treatment report id number and the digenesis id number respectively. The insurance table can access the information from the prescription table through the prescription id. The prescription can also be accessed by the patient. Patient insurance coverage is connected with the patient and the insurance cover. The insurance cover is further connected to the insurance plan. The queries are running in the SQL server successfully and the data fetching from one table to another table is done in the right way.
Mongo DB
The SQL queries are working properly and is meeting all the requirements mentioned in the case study. However, further improvement can be made using the less tables and more accurate use of keys, which will generate the same result but will less time for the execution of the queries. The changing and the merging of the entities after the improvisation will require more attention for the selection of the primary and foreign keys.
Additionally, it should also be noted that the OLAP cube has been created for the factInternetsales table. The table would provide the information about the sales in the database and also the queries would provide the sales information along with the information from the products and the time of the sale.
The learning from the semester enables the students on use and understand the database clearly. The concept of the database along with its internal structure has been taught. Apart from the theoretical knowledge, the practical implementation of the database has been done through the use of the Microsoft SQL server. The broad structure and the use of the Microsoft SQL server has been discussed in this semester. In order to develop the database and its practical implementation some of the steps are needed to be followed. One of these requirements is creating the entity relation ship model. Writing a simple table in the SQL server may not need the entity relationship diagram, however for the complex database structure like developing the medical report mentioned in the case study needs the creation of the ERD. The ERD helps understand the relation between the different entities and the attributes for each entity along with the keys are also mentioned in the ERD diagram. The execution of the queries depend on the accuracy of the creation of E-R diagram.
References
Agosti, M., Ferro, N. and Silvello, G., 2018. Digital Libraries: From Digital Resources to Challenges in Scientific Data Sharing and Re-Use. In A Comprehensive Guide Through the Italian Database Research Over the Last 25 Years (pp. 27-41). Springer, Cham.
Aho, M., 2017. Optimisation of Ad-hoc analysis of an OLAP cube using SparkSQL.
Amato, F., Santo, A.D., Moscato, V., Persia, F., Picariello, A., Silvestro Roberto, P. and Sperlì, G., 2015. 23rd Italian Symposium on Advanced Database Systems. In 23rd Italian Symposium on Advanced Database Systems SEBD 2015 (pp. 184-191). non so.
Hellman, M., 2016. Improving traveling habits using an OLAP cube.
Ibragimov, D., Hose, K., Pedersen, T.B. and Zimányi, E., 2015. Towards exploratory OLAP over linked open data–a case study. In Enabling Real-Time Business Intelligence (pp. 114-132). Springer, Berlin, Heidelberg.
Jakawat, W., Favre, C. and Loudcher, S., 2016, January. OLAP Cube-based Graph Approach for Bibliographic Data. In SOFSEM (Student Research Forum Papers/Posters) (pp. 87-99).
Keivani, N., Maatuk, A.M., Aljawarneh, S. and Ali, M.A., 2015. Towards the maturity of object-relational database technology: promises and reality. International Journal of Technology Diffusion (IJTD), 6(4), pp.1-19.
Kumar, R., Charu, S. and Bansal, S., 2015. Effective way to handling big data problems using NoSQL Database (MongoDB). Journal of Advanced Database Management & Systems, 2(2), pp.42-48.
Mills, R.J., 2017. Using Analogical Problem Construction As An Advance Organizer To Teach Advanced Database (SQL) Nomenclature. The Review of Business Information Systems (Online), 21(1), p.1.
Obe, R.O. and Hsu, L.S., 2017. Postgresql: Up and Running: a Practical Guide to the Advanced Open Source Database. ” O’Reilly Media, Inc.”.
Sautot, L., Faivre, B., Journaux, L. and Molin, P., 2015. The hierarchical agglomerative clustering with gower index: a methodology for automatic design of olap cube in ecological data processing context. Ecological Informatics, 26, pp.217-230.
Upreti, K., Singla, A. and Gupta, M., 2017. Integrating OLAP CUBE with UML.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
