Understanding and Execution of Database

Question: Describe about the Database Implementation and Queries? Answer: Understanding and Execution of Database The database management system (DBMS) is shielding the device data or system data from the other user of the machine or system. The database management system (DBMS) is more secure system for every user. Data Investigation In data investigation some specific methods has to be followed. Database layers of a program provide a bunch of ways for the developers to obtain the most out of the databases. Database management systems (DBMS) are compiled to enhance the motivation of the developers (Bhatnagar Bhatia, 2014). This is also provides to encourage the information access by the machine and the client. These documents acquire links together to the possible relevant documents, and it associates a number of possible tips that uses to optimize the relevant database usage. The guidelines based streamlining agents emulated a set of models to concentrate on the execution of plan for a specific SQL proclamation. The cost based optimizer analyses the data as well as arranges the possible SQL articulation where the expense confirms to the assessed asset which is utilized for a particular plan. By analyzing the data part the developer can come to a certain point at which describes the complete scenario as a whole. Therefore the data investigation part becomes so vital for understanding a particular database and execution of the database. That certainly provides influence of the database. Influence of Database The influence of database is certainly very important for the understanding of the database. Through a lot of analysis and testing it has to be ensured that the database strictly guarantee the honesty of changes of the database according to the test information. The application testing should also empower the user to conduct true and honest testing. The application must also certify the fact that the testing ensures minimization of the danger of insecurities that is connected with the changes in the framework (Pathak, 2008). Additionally, in true test situation there is a possibility of threat of showing touchy information to some non-generation clients to some extent. To minimize the danger associated with the database servers it it very important to empower the performing information which is also covering related information sub setting on the test information. To certify this, there are three specific segments add to this. This consists of SQL execution analyzer part, database re play as well as test information data management. For this entity relationship diagram (E-R diagram) is truly vital to analyze the complete test and that will ensure the way how to find the SQL statements or quarries relevant to the test. This section consists possibly the most important structure for the database system. Specific scenarios manipulating the database The particular scenarios which manipulate the database have to be discussed in this section. This particular segment is prepared to examine the important database records created when one has to issue a CREAT DATABASE articulation. In this segment there are some following methods which have to be maintained. One has to evaluate information documents and temp records, control the documents in a particular way and redo online log documents (Singh, 2006). Mostly a database occasion is prominently a situated of memory structure that is compiled to oversee database documents in a certain way. Thereafter a few components are made to access for distributing and dealing with the potentiality of the records. The structure includes Oracle programmed capacity administration, record framework, raw gadget and group record framework. The programmed capacity administration and capacity segments have to be examined in a certain theoretical process. Lastly in this part the programmed capacity adminis tration managers occurrence is thoroughly analyzed. To understand the total database and for perfect execution prospective one has to go through the complete section in a careful manner. To attain the SQL database queries part this segment is truly significant (Dhawan, 2010). SQL Database Queries According to the giving entity relationship diagram or E-R diagram, the SQL queries are shown in below. 1.create table client (client_number number(10), client_first_name varchar2(40), client_last_name varchar2(40), client_street1 varchar2(50), client_street2 varchar2(50), client_city varchar2(20), client_state varchar2(20), client_post_code number(10), client_contact number(15), client_contact_phone number(12), client_TBN varchar2(25), client_ABN varchar2(25), client_add_date date, structure_id varchar2(20), client_link hyperlink) ALTER TABLE client ADD CONSTRAINT pk_client_number PRIMARY KEY (client_number) 2.create table structure(structure_id varchar2(20), structure_description varchar2(200)) alter table structure add constraint pk_structure_id primary key (structure_id) ALTER TABLE client ADD CONSTRAINT fk_structure_id FOREIGN KEY (structure_id) REFERENCES structure(structure_id) 3.create table sector (sector_id varchar2(20), sector_description varchar2(200)) ALTER TABLE sector ADD CONSTRAINT pk_sector_id PRIMARY KEY (sector_id) 4.create table service (service_id varchar2(20), service_description varchar2(200)) ALTER TABLE service ADD CONSTRAINT pk_service_id PRIMARY KEY (service_id) 5.create table service_type (service_type_id varchar2(20), service_type_description varchar2(200)) ALTER TABLE service_type ADD CONSTRAINT pk_service_type_id PRIMARY KEY (service_type_id) 6.create table EMPLOYEE_TYPE (employee_type varchar2(20), employee_type_description varchar2(200)) ALTER TABLE EMPLOYEE_TYPE ADD CONSTRAINT pk_employee_type PRIMARY KEY (employee_type) 7.create table billing_employee (employee_number number(10), employee_type varchar2(20), employee_name varchar2(75)) ALTER TABLE billing_employee ADD CONSTRAINT pk_employee_number PRIMARY KEY (employee_number) ALTER TABLE billing_employee ADD FOREIGN KEY (employee_type) REFERENCES EMPLOYEE_TYPE(employee_type) 8.create table client_sector (client_number number(10), sector_id varchar2(20)) ALTER TABLE client_sector ADD FOREIGN KEY (client_number) REFERENCES client(client_number) ALTER TABLE client_sector ADD FOREIGN KEY (sector_id) REFERENCES sector(sector_id) 9.create table client_service(client_number number(10), service_id varchar2(20)) ALTER TABLE client_service ADD FOREIGN KEY (client_number) REFERENCES client(client_number) ALTER TABLE client_service ADD FOREIGN KEY (service_id) REFERENCES service(service_id) 10.create table employee_domain(employee_number number(10), service_id varchar2(20)) ALTER TABLE employee_domain ADD FOREIGN KEY (employee_number) REFERENCES billing_employee(employee_number) ALTER TABLE employee_domain ADD FOREIGN KEY (service_id) REFERENCES service(service_id) 11.Create table billing_employee_rate(employee_number number(10), billing_rate_effective_date date, billing_rate number(20)) ALTER TABLE billing_employee_rate ADD FOREIGN KEY (employee_number) REFERENCES billing_employee(employee_number) ALTER TABLE billing_employee_rate ADD CONSTRAINT pk_employee_rate_effective_date PRIMARY KEY (employee_rate_effective_date) 12.Create table billing_record(employee_number number(10), billing_date date, client_number number(10), billing_start_time timestamp, service_id varchar2(20), service_type_id varchar2(20), billing_comment varchar2(200), billing_end_time timestamp) ALTER TABLE billing_record ADD FOREIGN KEY (employee_number) REFERENCES billing_employee(employee_number) ALTER TABLE billing_record ADD CONSTRAINT pk_billing_date PRIMARY KEY (billing_date) ALTER TABLE billing_record ADD FOREIGN KEY (client_number) REFERENCES client(client_number) ALTER TABLE billing_record ADD CONSTRAINT pk_billing_start_time PRIMARY KEY (billing_start_time) ALTER TABLE billing_record ADD FOREIGN KEY (service_id) REFERENCES service(service_id) ALTER TABLE billing_record ADD FOREIGN KEY (service_type_id) REFERENCES service_type(service_type_id) 13.create table billing_on_cost_type(billing_on_cost_type_id varchar2(20), billing_cost_type_description varchar2(200)) ALTER TABLE billing_on_cost_type ADD CONSTRAINT pk_billing_on_cost_type_id PRIMARY KEY (billing_on_cost_type_id) 14.create table billing_on_cost(client_number number(10), billing_on_cost_date date, billing_on_cost_type_id varchar2(20), billing_on_cost_charge number(20)) ALTER TABLE billing_on_cost ADD FOREIGN KEY (client_number) REFERENCES client(client_number) ALTER TABLE billing_on_cost ADD CONSTRAINT pk_billing_on_cost_date PRIMARY KEY (billing_on_cost_date) ALTER TABLE billing_on_cost ADD FOREIGN KEY (billing_on_cost_type_id) REFERENCES billing_on_cost_type(billing_on_cost_type_id) The above SQL statements or queries are done in Oracle application and all the queries are compiling successfully, which is shown in below by the help of some screen shot, which is shown in below. References: Bhatnagar Bhatia, A. (2014).Database Management System. Oxford: Alpha Science International Ltd. Dhawan, R. (2010).Database management system. New Delhi: A P H Pub. Corp. Pathak, N. (2008).Database management system. Mumbai [India]: Himalaya Pub. House. Singh, Y. (2006).Database management system. New Delhi: Shree Publishers Distributors.