Part 1: True or False, please explain why. (2 points each) 1, SQL (Structured Qu
ID: 3575657 • Letter: P
Question
Part 1: True or False, please explain why. (2 points each) 1, SQL (Structured Query Language) is both DDL (Data Definition Language) and DML (Data Manipulation Language).
2, View is not updatable.
3, For Character data type, value NULL is equivalent to empty string ‘’.
4, A table has only one primary key, but it can have more than one foreign keys.
5, In SQL, an asterisk (*) can be used to express “all columns”.
6, If a schema satisfies BCNF, then it also satisfies 3NF.
7, A secondary index can be either dense or sparse.
8, Given a hash function h and two search-key values, Ki and kj, if kikj, then h(ki)h(kj)
. 9, Lname, Fname, Salary(salary>30000(Employee)) is equivalent to salary>30000(Lname, Fname, Salary(Employee)).
10, since Natural-join operations are associative, (E1 E2) E3 = E1 (E2 E3), the ordering of natural-join is not important for query optimization.
Part 2: Case study The following tables form part of a database held in a relational DBMS: Employee(Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno) Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date) Project(Pname, Pnumber, Plocation, Dnum) Works_on(Essn, Pno, Hours) The underlined attribute(s) in each relational schema is primary key. Employee contains employee details, Super_ssn is the SSN of supervisor, which is a foreign key that refers to Ssn in table Employee, and Dno is the department number, which is another foreign key that refers to Dnumber in table Department. Department contains department details and Mgr_ssn is SSN of the department manager, which is a foreign key that refers to Ssn in table Employee. Project contains project details, and Dnum is a foreign key which refers to Dnumber in table Department. Works_on records who work on which project, Essn is a foreign key that refers to Ssn in table Employee, and Pno is another foreign key that refers to Pnumber in table Project. Based on the schema defined above, write SQL statements to answer the following queries: (2 points each)
1, Retrieve the birth date and address of the employee(s) whose name is ‘John B. Smith’.
2, Retrieve the name and address of all employees who work for the ‘Research’ department.
3, For each employee, retrieve the employee’s first name and last name and the first name and last name of his or her immediate supervisor.
4, Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary.
5, For each project, retrieve the project number, the project name, and the number of employees who work on that project. Describe what the following SQL statements return and give the equivalent relational algebra expressions. (3 points each)
6, Select Fname, Lname, Sex, Address From Employee;
7, Select Fname, Lname From Employee Where Sex=’M’; Describe what the following relational algebra expressions return and give the equivalent SQL statements. (2 points each)
8, Lname, Fname, Salary(salary>30000(Employee))
9, Dname, Lname, Fname(Department Mgr_ssn=SsnEmployee)
Part 3: Questions 1, (4 points) Use Armstrong’s axioms to prove the soundness of the union rule. Union rule: If ab holds and ac holds, then abc holds.
2, (6 points) For B+ Tree below, show the steps involved in the following queries:
a. Find records with a search-key value which is great than 18.
b. Find records with a search-key value which is between 5 and 19, inclusively.
Explanation / Answer
1, SQL (Structured Query Language) is both DDL (Data Definition Language) and DML (Data Manipulation Language).
A) True , SQL is both DDL and DML because by using SQL we can define new definitions like tables,views e.t.c and also we can manipulate i.e. we can change or update the existing definition or else just we can add the data to the existing definition.
2.View is not updatable.
A) False, View is updatable a view can be updated by using the statement create or replace view view_name as <condition>.In the place of <Condition> we will write the select statement.
3.For Character data type, value NULL is equivalent to empty string ‘’.
A) True, But there is always a difference between NULL and empty string i.e. NULL is referred as presently there is no data in it but there is a chance to add in future where as empty string refers to that there is no data in it. Even though there is difference character datatype refers same as equal.
4) A table has only one primary key, but it can have more than one foreign keys.
A) TRUE, yes a table must contain only one primary key because primary key is a column which represents unique row data. this is very important while dataprocessing on the other hand we can have many foreign keys in one table.
5)In SQL, an asterisk (*) can be used to express “all columns”.
A) TRUE, an astrerik is used to get all the columns from the table this is defined by the developers in such a way and there is no particular reason we need one operator to give all the columns once and they have defined this * operator.
6, If a schema satisfies BCNF, then it also satisfies 3NF.
A) TRUE, If we say the table is in BCNF it must satisfy 3NF as BCNF is the highest normal form than 3NF it must satisy all the Normal Forms which are below for the accuracy and performance.
7, A secondary index can be either dense or sparse.
A) FALSE, A secondary index must be dense because it has to contain all the records contained in the index even there is similar ones also because secondary index is not a primary one which holds only unique records, but in secondary index there is no rule like that it must contains all the records. On the other hand Sparse which holds only some records which satisfy search key.
8, Given a hash function h and two search-key values, Ki and kj, if kikj, then h(ki)h(kj)
A) TRUE, Because hash value is a samll unique value and is used to uniquely identifies the data so,obviously the two keys is not at all equal.
9, Lname, Fname, Salary(salary>30000(Employee)) is equivalent to salary>30000(Lname, Fname, Salary(Employee)).
A) TRUE, The first is equalent to the second one because the second one is written in relational algebra. I.e. if we have to convert the first statement into relational albegra statement the equalent will be salary>30000(Lname, Fname, Salary(Employee)).In relational algebra select statement is written as '' only.
10, since Natural-join operations are associative, (E1 E2) E3 = E1 (E2 E3), the ordering of natural-join is not important for query optimization.
A) TRUE, the ordering of natural-join is not important for query optimization because it has associative property,i.e.associative property means we can add do any operation reagardless of how it grouped . grouped in the sence how the paranthesis is used.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.