Question? Call Us  +1 (817) 546-4770

QAC020C155A Data Modelling And SQL Language

Academic Anxiety?

Get an original paper within hours and nail the task

156 experts online

Free Samples

QAC020C155A Data Modelling And SQL Language

.cms-body-content table{width:100%!important;} #subhidecontent{ position: relative;
overflow-x: auto;
width: 100%;}

QAC020C155A Data Modelling And SQL Language

0 Download6 Pages / 1,331 Words

Course Code: QAC020C155A
University: University Of Roehampton

MyAssignmentHelp.com is not sponsored or endorsed by this college or university

Country: United Kingdom

Question:

On successful completion of this module students will be able to
1.Understand fundamental concepts of database management systems such data independence, data models and database technologies
2.Be able to design and implement relational database systems
3.Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS)
4.Be able to test and document relational database systems
3.Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS)
4.Be able to test and document relational database systems
Kindly see the SQL script in appendix to create the above database / tables and to insert the data)
1.WriteanSQL statement to list all employees with their full names, hire date and title
2.Write anSQL statement to show the salaryof all employees and their department name.
3.Write anSQL statement to show the full names and gendersof HR department staff.
4.Write anSQL statement to showthe all departments’ name and their departments’ managers.
5.Write anSQL statement to show a list of department managers who were hired after 1986
6.Write anSQL statement to change anyemployee’s title. Assume the employee has just phoned in with his/her last name.
7.Write anSQL statement to delete employee’s record who belongs to department ‘d004’ and ID 10003.
8.Create a database view to list full names of all employees, their department managersand salaries.
9.Create a database view to list alldepartments and their department managers, who were hired between 1980 and 1990.
10.Write anSQL statement to increase salaries of all employees up to 10% who are working in marketing department

Answer:

This query is used to fetch the full names of an employee, the date that the employee was hired, and the title of the employee. Because the full names are saved as first name and last name the query uses an inbuilt sql function called concat which concatenates the two columns while adding a space between the two columns. To get the title a join between employees table and titles table is needed. The type of join used is inner join using the emp_no column in both tables thus the query returns all columns for which the emp_no in the employees table matches the emp_no in the titles table.

SQL to show salaries and department name of all employees

This query is used to show the full names of each employee, their current salary and the department name the employee works in. Concat function is used to show the full names of the employee by concatenating the first name and the last name of the employee. The salary of an employee is found in the salaries table thus an inner join is done between the salaries table and the employee table using the emp_no column in both tables. To get the department name that an employee works in, a join between employees table and departments table is done using the joint table dept_emp.

SQL to show full names and genders of HR department staff

This query is used to show the full names and the gender of all employees working in the human resources department. The first name and the last name are concatenated using the concat function. To join the employees and departments tables a join of the dept_emp supporting table is done. Finally a condition is used to filter only employees working in the human resources department.

SQL to show all department name and their department managers

Results:
This  query is used to show the names of all departments and the names of the manager for each respective department. To get the full names of the manager, concatenation of the first and last name of the manager is done using the concat function. An inner join between the departments table and dept_manager table is done and another inner join between the dept_manager and employees table is done. The dept_manager is the joint table joining departments and employees table.

SQL to show department managers that were hired after 1986

This query Is used to get the department name, the name of the manager, the date that the manager was hired for all managers that were hired after 1986 which is from 1st of January 1987. To get the full names of the manager, concatenation of the first and last name of the manager is done using the concat function. An inner join between the departments table and dept_manager table is done and another inner join between the dept_manager and employees table is done. The dept_manager is the joint table joining departments and employees table.A condition is used to filter all managers who were hired after 1986.

SQL to change an employee’s title using their lastname

This query is used to update the date of birth of an employee using their last name. The first query shows the details of the employees before the update is done. The second query updates only the date of birth of the employee with the last name as Simmels that is why according to the screenshot, only 1 row has been affected. The last query shows the details of the employee after the update query is done.

SQL to delete an employee belonging to department d004 with id 10003

SQL Code:
This query is used to delete an employee with employee id 10003 who is working in department d004. The employee

SQL to create view to list full names of all employees, their department managers and salaries

SQL Code:
Fetching the results of the query.
This view is created to get the full names of employees, their salaries and the full names of the department that the employee works in.

SQL to create vies to list all departments and their department managers who were hired between 1980 and 1990.

SQL Code:
Select all the rows in the view

SQL to increase salaries for all employees working in marketing department by 10%.

SQL Code:
Before Update:
Update Query
This query is used to update the salaries of all employees working in the marketing department. To update the salary you have to join the employees table to the supporting dept_emp table and then join it to the departments table and finally join it to the salaries table and restrict the update for only those employees working in the department with the department name marketing.
Task2: Database testing.

Why database testing is important.

Database testing is important because complex applications require complex backend databases to support the application. Thus there is a need to test the database to make sure it meets the all requirements required for the efficient and accurate operation of the front end part of the application. Testing in databases is done for;

Validation of the acid properties of the database.- This type of testing involves testing the Atomicity, Consistency, Isolation and durability of each database transaction. Atomicity is done to test whether a transaction passes or fails where it has to pass the all-or-nothing rule. Consistency involves testing whether each transaction will leave the database in a consistent state. Isolation property involves testing the database to make sure that if multiple transactions are executed at once then state of the database should be as if the transactions were executed in order. Finally durability involves testing that once a transaction is committed, no external factors can change its outcome.
Data integrity- Testing to ensure data integrity involves making sure that for all tables that are related CRUD operations on each of the tables should maintain consistent data in all the tables. For example an update in the parent table should result to changes in the child tables.

Testing process.

Field size validation

Field size validation involves testing the database to make sure that the size of the field specified for a specific column is not violated. For example if a column has varchar data type and is restricted to a size of 50 then no data should have data exceeding 50 for this column.
To test field size validation we insert data in departments table where the dept_no should have is char(4) so we insert a record with more than 4 characters.
The query executes successfully but the value inserted in the table is trimmed to 4 characters as shown in the results below.

Not null values

Not null validation ensures that a column that has a property of null must all always have a value during the insertion of a record. For example in the departments table the dept_name has not property thus inserting a null value would fail.
Query
When this query is executed it fails because dept_name should not have a null value.

Primary key

Primary key validation involves making sure no duplicate primary keys exist in a table because every primary key is supposed to be unique. For example in the departments table, inserting a duplicate entry key results to an error as shown in below.

Foreign key

Foreign key validation involves testing that no integrity constraints are violated. For example you cannot insert a record in the child table for a primary key that does not exist in the parent table. For example considering two tables; employees the parent table and salaries the child table, you cannot insert a salary record for an employee that does not exist as demonstrated in the screenshot below.
References
Sharma, R., Mohamed, A., Aryan, M., kuchana, r., Ramesh, M. and patra, s. (2018). Database Testing Guide (Why, How, and What About Database Testing). [online] Softwaretestinghelp.com. Available at: https://www.softwaretestinghelp.com/database-testing-process/ [Accessed 20 Nov. 2018].

Free Membership to World’s Largest Sample Bank

To View this & another 50000+ free samples. Please put
your valid email id.

E-mail

Yes, alert me for offers and important updates

Submit 

Download Sample Now

Earn back the money you have spent on the downloaded sample by uploading a unique assignment/study material/research material you have. After we assess the authenticity of the uploaded content, you will get 100% money back in your wallet within 7 days.

UploadUnique Document

DocumentUnder Evaluation

Get Moneyinto Your Wallet

Total 6 pages

PAY 4 USD TO DOWNLOAD

*The content must not be available online or in our existing Database to qualify as
unique.

Cite This Work
To export a reference to this article please select a referencing stye below:

APA
MLA
Harvard
OSCOLA
Vancouver

My Assignment Help. (2021). Data Modelling And SQL Language. Retrieved from https://myassignmenthelp.com/free-samples/qac020c155a-data-modelling-and-sql-language/systems-such-data-independence.html.

“Data Modelling And SQL Language.” My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/qac020c155a-data-modelling-and-sql-language/systems-such-data-independence.html.

My Assignment Help (2021) Data Modelling And SQL Language [Online]. Available from: https://myassignmenthelp.com/free-samples/qac020c155a-data-modelling-and-sql-language/systems-such-data-independence.html[Accessed 18 December 2021].

My Assignment Help. ‘Data Modelling And SQL Language’ (My Assignment Help, 2021) accessed 18 December 2021.

My Assignment Help. Data Modelling And SQL Language [Internet]. My Assignment Help. 2021 [cited 18 December 2021]. Available from: https://myassignmenthelp.com/free-samples/qac020c155a-data-modelling-and-sql-language/systems-such-data-independence.html.

×
.close{position: absolute;right: 5px;z-index: 999;opacity: 1;color: #ff8b00;}

×

Thank you for your interest
The respective sample has been mail to your register email id

×

CONGRATS!
$20 Credited
successfully in your wallet.
* $5 to be used on order value more than $50. Valid for
only 1
month.

Account created successfully!
We have sent login details on your registered email.

User:

Password:

MyAssignmenthelp.com has appointed best assignment experts who are wizards of words. Our writers know every trick of crafting high quality write-ups within a short period. With years of experiences, we have become one of the most prolific assignment help services in the USA. We deliver custom-made help to students with writing different types of assignments. We guarantee total need-based and timely service, and this is why increasing numbers of students prefer to buy assignment online.

Latest Management Samples

div#loaddata .card img {max-width: 100%;
}

MPM755 Building Success In Commerce
Download :
0 | Pages :
9

Course Code: MPM755
University: Deakin University

MyAssignmentHelp.com is not sponsored or endorsed by this college or university

Country: Australia

Answers:
Introduction
The process of developing a successful business entity requires a multidimensional analysis of several factors that relate to the internal and external environment in commerce. The areas covered in this current unit are essential in transforming the business perspective regarding the key commerce factors such as ethics, technology, culture, entrepreneurship, leadership, culture, and globalization (Nzelibe, 1996; Barza, 2…
Read
More

SNM660 Evidence Based Practice
Download :
0 | Pages :
8

Course Code: SNM660
University: The University Of Sheffield

MyAssignmentHelp.com is not sponsored or endorsed by this college or university

Country: United Kingdom

Answers:
Critical reflection on the objective, design, methodology and outcome of the research undertaken Assessment-I
Smoking and tobacco addiction is one of the few among the most basic general restorative issues, particularly to developed nations such as the UK. It has been represented that among all risk segments smoking is the fourth driving purpose behind infections and other several ailments like asthma, breathing and problems in the l…
Read
More
Tags:
Australia Maidstone Management Business management with marketing University of New South Wales Masters in Business Administration 

BSBHRM513 Manage Workforce Planning
Download :
0 | Pages :
20

Course Code: BSBHRM513
University: Tafe NSW

MyAssignmentHelp.com is not sponsored or endorsed by this college or university

Country: Australia

Answer:
Task 1
1.0 Data on staff turnover and demographics
That includes the staffing information of JKL industries for the fiscal year of 2014-15, it can be said that the company is having problems related to employee turnover. For the role of Senior Manager in Sydney, the organization needs 4 managers; however, one manager is exiting. It will make one empty position which might hurt the decision making process. On the other hand, In Brisba…
Read
More

MKT2031 Issues In Small Business And Entrepreneurship
Download :
0 | Pages :
5

Course Code: MKT2031
University: University Of Northampton

MyAssignmentHelp.com is not sponsored or endorsed by this college or university

Country: United Kingdom

Answer:
Entrepreneurial ventures
Entrepreneurship is the capacity and willingness to develop, manage, and put in order operations of any business venture with an intention to make profits despite the risks that may be involved in such venture. Small and large businesses have a vital role to play in the overall performance of the economy. It is, therefore, necessary to consider the difference between entrepreneurial ventures, individual, and c…
Read
More
Tags:
Turkey Istanbul Management University of Employee Masters in Business Administration 

MN506 System Management
Download :
0 | Pages :
7

Course Code: MN506
University: Melbourne Institute Of Technology

MyAssignmentHelp.com is not sponsored or endorsed by this college or university

Country: Australia

Answer:
Introduction
An operating system (OS) is defined as a system software that is installed in the systems for the management of the hardware along with the other software resources. Every computer system and mobile device requires an operating system for functioning and execution of operations. There is a great use of mobile devices such as tablets and Smartphones that has increased. One of the widely used and implemented operating syste…
Read
More
Tags:
Australia Cheltenham Computer Science Litigation and Dispute Management University of New South Wales Information Technology 

Next

Need an essay written specifically to meet your requirements?

Choose skilled experts on your subject and get an original paper within your deadline

156 experts online

Your time is important. Let us write you an essay from scratch

Tips and Tricks from our Blog

PROJ6016 Employer Based Project

Free Samples PROJ6016 Employer Based Project .cms-body-content table{width:100%!important;} #subhidecontent{ position: relative; overflow-x: auto; width: 100%;} PROJ6016 Employer

Read More »

PPMP20009 Marking Rubric

Free Samples PPMP20009 Marking Rubric .cms-body-content table{width:100%!important;} #subhidecontent{ position: relative; overflow-x: auto; width: 100%;} PPMP20009 Marking Rubric

Read More »