Theory questions/problems
Q.1 [25 points] Theory questions/problems
a. [ 5 points] Define (i) tuple (ii) domain (iii) schema of a relation (iv) state of a relation, and (v) relational database system.
b. [ 5 points] What is a referential integrity or foreign key constraint? Give an example.
c. [ 5 points] What are three UPDATE operations in a database relation? Does the integrity constraints necessary in UPDATE operation?
d. [ 5 points] List the data types that are allowed for SQL attributes.
e. [ 5 points] Explain the basic form of the SELECT statement. Show the extended version of SELECT statement for ordering Query Results.
Q.2 [25 points] Practical/lab problems
Specify the following queries in SQL on the COMPANY relational database schema shown in Figure 5.5. Show the result of each query if it is applied to the COMPANY database in Figure 5.6.
a. [ 5 points] Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
b. [5 points] List the names of all employees who have a dependent with the same last name as themselves.
c. [ 5 points] Find the names of all employees who are directly supervised by Mr. Wong.
Part2: Consider that the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 6.2 is changed to read as follows: CONSTRAINT EMPSUPERFK
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE CASCADE ON UPDATE CASCADE,
Now, Answer the following questions:
d. [5 points] What happens when the following command is run on the database state shown in Figure 5.6? DELETE EMPLOYEE WHERE Lname = ‘Borg’
b. [5 points] Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint
ON DELETE?
FIGURE 5.5
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
