Question 3. . Consider the following two command C1 and C2 on the relation R from an SQL database: C1: drop table R; C2: delete from R; Which of the following statements is True? I. Both C1 and C2 delete the schema for R. II. C2 retains relation R, but deletes all tuples in R. III. C1 deletes not only all tuples of R, but also the schema for R.
Q: Question 7. Consider the following relational instance: B C 5 3 6 7 3 3 Which of the following…
A: A utilitarian reliance (FD) is an imperative between two arrangements of properties in connection…
Q: Which of the followings can possibly be a canonical cover Fc of a set F of functional dependencies?…
A: The answer to this question is option b) {A → D, AB → C, D → B} and d) {A → C, D → B, AB → C}
Q: Suppose you have the following relational DB. You may assume that no future instances of the…
A: πEname(σYOB>1981 AND Sex=M(Employee))
Q: Consider the database schema below for hotels in countries. A country have many hotels, and the same…
A: Lets see the solution.
Q: Consider the following relation: Customer (CID, name, email, address) Now write Mysql query to…
A: CREATE TRIGGER trg_EMAIL ON EMAIL_CHANGE AFTER INSERT, UPDATE AS IF ( UPDATE (cid, oldemail,…
Q: The WorkOrders table contains a foreign key, ClientNum, which must match the primary key of the…
A: A foreign key is a key that is used to link two or more tables together. It also helps in…
Q: onsider the database schema below for hotels in countries. A country have many hotels, and the same…
A: QUESTION 15 Consider the database schema below for hotels in countries. A country have many…
Q: Suppose that user U creates a relation R(A,B,C,D) and wants to grant the following privileges to us…
A: (a) SQL Statement: GRANT SELECT ON R TO W WITH GRANT OPTION; The above statement…
Q: Given the following relational schema (Hint: the bold text represents a primary key), Department…
A: Relational algebra is used to represent relational query using operators.
Q: Consider the database schema below for cities in countries. A country have many cities, and the same…
A: Domain Constraint: It specifies that the value taken b the attributes in a relation must contain an…
Q: Using the following DB schema for the PATIENT table, right a query that will return all fields for…
A: Answer: SELECT * from PATIENT WHERE State=“KS” and State LIKE ‘K%’ ORDER BY MRN asc;
Q: This homework aims to analyze schemas to determine dependencies and convert them to BCNF. Remember…
A: A database schema is a skeleton structure that represents a logical view of the entire database. It…
Q: the LIKE operator to match exactly one character, which one(s) of the following symbols should be…
A: The answer with correct options is given in step 2.
Q: Given the following relational schema (Hint: the bold text represents a primary key), Department…
A: Lets discuss the solution in the next steps
Q: Exercise 8.3 Consider a relation stored as a randomly ordered file for which the only index is an…
A:
Q: Question 7. Consider the following relational instance: A 5 3 2 4 4 3 Which of the following…
A: Functional dependencies of the given relation R(A,B,,C)
Q: hich of the following functional dependencies are satisfied (and not satisfied) by this relation and…
A: functional dependency in DBMS.
Q: PLZ help with the following: IN SQL Let R (A, B, C) be a relation schema. What happens if we…
A: Relational schema refers to the meta-data that describes the structure of data within a…
Q: Consider the following relation: Invoice(Order_ID, Order_Date, Customer_ID,…
A:
Q: Consider the relations Courses1, Courses2, _Enrollment and Students. Courses cid ename Al Calculus…
A: Write the following query in Relational Algebra. a) Find the sids of students who have enrolled in…
Q: Which of the following functional dependencies are satisfied (and not satisfied) by this relation…
A: (a) A → B False why ?? because you can't able to identify the value of B uniquely using A because…
Q: Study the relational schema: STUD(ID, Name, CID) COURSE(CID, PName, Price) Which of the following…
A: In step 2, you will get the answer. Join is used to mix rows from two or more tables based on the…
Q: For the dependency diagram shown, which of the fallowing options show correct parts of the logical…
A: According to the information given:- We have to choose the correct option in order to satisfy the…
Q: What are primary, foreign, external, surrogate, and substitute keys?
A: The solution for the given question is What are primary, foreign, external, surrogate, and…
Q: Hotel(hID, hName, city) primary key is hID Guest(gID, gName, gAddress) primary key is gID Room(hID,…
A: Select guests who stayed at least 1 day in New York City hotels between September 8, 2001, and…
Q: You are given the following database schema: Sailors(sid, sname, rating, age) Boats(bid, bname,…
A: Given Data : Number of pages in main memory = 9 Sailors : Tuples = 100000 pages = 1000 Sailors…
Q: For executing a query, which specifies a range of values in the WHERE clause, among Ordered indexing…
A: - We need to talk about the indexing to use for getting data from WHERE clause.
Q: Consider the relations: • PROJECT(proj#, proj_name) EMPLOYEE(emp#, emp_name) ASSIGNED(proj#, emp#)…
A: The SELECT statement is used to select data from a database. In order to show employee name and…
Q: What is the minimum total number of tuples across all relations that can be entered in the database…
A: Answer One Library book has at least 2 copies. one Library book can be written by one or many…
Q: QUESTION 6 Consider the following functional dependencies for a relation R(A, B, C, D) A -> (B, C,…
A: A super key whose any proper subset is not super key is called candidate key. closure table : (A)+…
Q: 4. Consider relation Presidents representing US presidents. Suppose that the following business…
A: Solution: Correct Answer is : Option- (d)
Q: A B C 10 a cl 12 b c3 D E cl C A 14 15 С c2 100 6 15 16 b cl 101 3 16 18 d c3 102 3 10 Relation re…
A: We know that Referential Integrity constraint is enforced when a foreign key references the primary…
Q: Given the relation below named Student_Registration , answer the following questions. S_id…
A: Relational Schema: A relational schema in a database gives a outline of how data is organized in a…
Q: Which of the following functional dependencies are satisfied (and not satisfied) by this relation…
A: Here in this question we have given. Stones FDs and we have asked that which of the following FDs…
Q: functional dependencies, what is the highest normal form the data model complies to? Note: Primary…
A: Primary key for the given relation is JobID A relation is said to be in 1NF if none of the attribute…
Q: Student ID Student_name Cource_ID Cource_name Grade 22 Noor НTML B+ C++ А- JAVA C++ 25 Ali A JAVA…
A: Provided the solution for all the above given questions with detailed step by step explanation as…
Q: UESTION 14 Consider the database schema below for cities in countries. A country have many…
A: No, violates key constraint. Key constraint is violated when value in the new tuple is already…
Q: Consider the following schema for a company database and answer the following questions: DEPARTMENT…
A: Q1. SQL DDL statements CREATE DATABASE EmpDepProjDBGOUSE EmpDepProjDBGO CREATE TABLE…
Q: Hotel(hID, hName, city) primary key is hID Guest(gID, gName, gAddress) primary key is gID Room(hID,…
A: The SQL Queries are as follows:
Q: Exercise 20.5 Consider the following BCNF relation, which lists the ids, types (e.g., nuts or…
A: 1. A heap file structure could be used for the relation Parts. A dense unclustered B+Tree index on…
Q: Q4) Create necessary tables, fields, foreign keys, primary keys, unique indexes, not null (NN)…
A: In the above ER Diagram, we can see that in the diagram. we have used some strong and weak entity…
Q: Assume a user creates a new relation r1 with a foreign key that links to another relation r2. What…
A: Introduction: A FOREIGN KEY is like a field (or set of fields) in one database that refers to the…
Q: Study the relational schema: EMP(ID, Name, DeptID) DEPT(DeptID, DName, Loc) Which of the following…
A: ANSWER: a. Emp.DeptID = Dept.DeptID
Q: Question 36 options: 1) A row, table, or an entire database 2) A row, table, or an entire…
A: 1) On application level we can restrict ( by implementing locking mechanism) other user to make…
Q: S, (3) C in U is a foreign key to T, (4) these relations do not contain size (i.e., the number of…
A: Here below, I am unable to use the join symbol used in the question. Hope you are able to…
Q: Let T(a,b,c) be a relation in which attribute a is not NULL. Consider the SQL query: SELECT * FROM T…
A: We are given a relational query and we are going to find its equivalent sql query. Note: Not In…
Q: Company (CompnayID: integer, CompnayName: String (unique), address) Department (DepartmentID:…
A: Employee is a table with columns ID of integer type, name of String type and DepartmentID of integer…
Q: 4.14 Consider the queryselect course id, semester, year, sec id, avg (tot cred)from takes natural…
A: The "natural join" keyword automatically adds the required join clause for columns with the same…
12
Step by step
Solved in 2 steps
- Consider the relational database below, where the primary keys are underlined.employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give a query for each of the following:a Give all managers in database a 10 percent raise.b Give managers in the database a 10 percent raise, unless the salary would be greaterthan $100,000. In such cases, give only 3 percent raise.c Delete all tuples in the works relation for employees of Small Bank Corporation?(?,?,?,?,?,?,?,?) ?????????? ??????????:??→?,?→??,?→???,?→?,?→?? (i) Find all the minimal super keys of the relational table ?. List the derivations of all minimal keys. (ii) Identify the highest normal form of the relational table ?. Remember that the identification of a normal form requires analysis of the valid functional dependencies. (iii) Decompose the relational table ? into minimal number of normalized relational tables in BCNF. Remember to indicate the primary key and foreign keys (if any).Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1. Given the preceding information, answer Questions a through e. How many database requests can you identify for an inventory update for both PRODUCT and PART? Using SQL, write each database request you identified in Step a. Write the complete transaction(s). Write the transaction log, using Table 10.1 as your template. Using the transaction log you created in Step d, trace its use in database recovery.
- Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1.Given the preceding information, answer Questions a through e.a. How many database requests can you identify for an inventory update for both PRODUCT and PART?b. Using SQL, write each database request you identified in Step a.c. Write the complete transaction(s).d. Write the transaction log, using Table 10.1 as your template.e. Using the transaction log you created in Step d, trace its use in database recovery.Consider the following relational schemaemployee (ename, street, city)works (ename, cname, salary, jdate)company (cname, city)manages (ename, mname)Write down the following queries in SQL 1. Find the names of all employees who earn more than every employee of Square Pharma. 2. Give all managers in this database a 4 percent salary raise, unless the salary would be greaterthan Tk.100,000. In such cases, give only a 3 percent raise.Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas. C(A,B,C,D,E,F,G,H) ?????????? ??????????: C,H > G A>BC B > CFH D > A F > EG (i) Find all the minimal super keys of the relational table ?. List the derivations of all minimal keys.(ii) Identify the highest normal form of the relational table ?. Remember that the identification of a normal form requires analysis of the valid functional dependencies.(iii) Decompose the relational table ? into minimal number of normalized relational tables in BCNF. Remember to indicate the primary key and foreign keys (if any).
- Consider a scenario in which the SQL statement is of the form SELECT DISTINCT c1, c2, c3 from T. Why might it make sense to create an index to optimize this query?Consider the following relations: Employee(E_id: integer, E_name: string, Age: integer, Salary: real) Works(E_id: integer, Dep_id: integer, Start_date: date) Department(Dep_id: integer, Dep_name: string, Budget: real, Manager_E_id: integer) What referential integrity constraints exist between these relations? What are the options for enforcing these constraints when a user attempts to delete a Dept tuple?QUESTION 14 Consider the database schema below for cities in countries. A country have many cities, and the same city name can be found in different countries. Country (ID: integer, Name: String (unique), SN: serialNumber (int)(unique)) City (serialNo: integer (unique, not null), CountryID: Integer, CityName: String, Population: integer) The same country (ID , name) can be added twice in Country relation with different SN? No, violates entity Integrity constraint No, violates referential integrity constraint. No, violates key constraint. Yes. No, violates domain constraints.
- Student ID | Student_name | Cource ID | Cource name | Grade 2 Noor 1 HIML B+ 7 C++ A 3 JAVA B 25 Al 2 C+ A 3 JAVA B 27 Sara 1 HIML A 3 JAVA A 28 Abmed 2 C++ B+ 1. What update anomalies may occur in the selation? 2. In which normal form (NF) the above relation? Provide an explanation to support your answer. 3. Decompose the relation so that the resut relations are in the third normal form (3NF).Consider the following relational schema: s (i, w, x) t (k,I, w) and the following tuples: s = {(1,5,3), (2,8,2)} t= {(1,1,7), (9, null, 2), (5,0,5), (2,2,7)} complete the SQL expression to yield the result below: SQL Expression SELECT .......................... t.w FROM t WHERE t.i .......................... (1,2,7,9); RESULT t.w 7Consider the following relational schema:Student (sid, sname, dept)Enroll (cid, sid, grade, semester)Course (cid, cname, dept, credit)Prereq (cid, pid) Write the following queries in Relational Algebra expression: a) For students who have received an A in a course, return name of students and titleof the course. b) Find name and id of students taken at least one of the prerequisite of CS595course and got an A grade in this prerequisite course.