21. Decompose the relation Customers into a set of BCNF relations. Customers Customer_ID Customer_Name Rank Discount Prefecture City 100123 Jun Aoki Silver 10% Shiga Otsu 100143 Bob Smith Gold 20% Kyoto Кyoto 100233 Mary Brown Bronze 5% Shiga Otsu 100115 Jack Lee Silver 10% Shiga Kusatsu 100262 Aki Ito Gold 20% Kyoto Uji

21. Decompose the relation Customers into a set of BCNF relations. Customers Customer_ID Customer_Name Rank Discount Prefecture City 100123 Jun Aoki Silver 10% Shiga Otsu 100143 Bob Smith Gold 20% Kyoto Кyoto 100233 Mary Brown Bronze 5% Shiga Otsu 100115 Jack Lee Silver 10% Shiga Kusatsu 100262 Aki Ito Gold 20% Kyoto Uji

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter1: Overview Of Database Concepts

Section: Chapter Questions

Problem 18MC: Which of the following defines a relationship in which data can have multiple occurrences in each...

See similar textbooks

Similar questions

EmployeeID is the primary key for the relation below. Which column violates a rule (characteristic) of a relation? Employee Relation EmployeeID Department PhoneNum PhoneNum 300 Finance 321-299-1234 407-999-2300 200 Sales 407-222-9090 321-365-0912 400 Sales 407-807-1124 407-453-2294 100 HR 321-934-2034 Group of answer choices The column named PhoneNum because you can’t have two columns with the same name. All the columns are valid. The EmployeeID column because the ID numbers are not in order. The Department column.
After setting up all relations, you need to apply following queries to get data from given relations: (Write queries and also paste screenshots in answer file) Get all employees data who are older than ‘Jones’ in company. (Hint: Older employees are those whom hire date is less than others)Get sum of salaries of all ‘Boston’ employees.Get all employees who take grade 1 or grade 3 salary. Using oracle APEX we are creating a table now - CREATE TABLE DEPT( DEPTNO NUMBER(10) NOT NULL, DNAME VARCHAR(50) NOT NULL, LOC VARCHAR2(30), CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO) ); We use NOT NULL constraint to make the cell occupied with something related to column line for example; we can see a lot of applications where we see (field is mandatory) Step 2 NOW EMP TABLE- CREATE TABLE EMP( EMPNO NUMBER(10)NOT NULL, ENAME VARCHAR2(50)NOT NULL, JOB VARCHAR2(50), MGR VARCHAR2(50), HIREDATE DATE, SAL NUMBER(6), COMM VARCHAR2(50) DEPTNO NUMBER(5)NOT NULL, CONSTRAINT EMP_FK FOREIGN KEY(…
Which of the following functional dependencies are satisfied (and not satisfied) by this relation and Why? (g) AD → BC WHY? (h) D → B WHY? (i) D → C Why? (j) C → D Why?
After setting up all relations, you need to apply following queries to get data from givenrelations: (Write queries and also paste screenshots in answer file) Get all the employees work with ‘Martin’ or ‘Scott’ in their departments. Get all employees work under management of ‘Blake’. Sort in descending order withrespect to employee number. Get all employees with letter ‘A’ in their name, who work in New York.Get all employees data whom salaries are more than combined salaries of Adams andSmith. Using oracle APEX we are creating a table now - CREATE TABLE DEPT( DEPTNO NUMBER(10) NOT NULL, DNAME VARCHAR(50) NOT NULL, LOC VARCHAR2(30), CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO) ); We use NOT NULL constraint to make the cell occupied with something related to column line for example; we can see a lot of applications where we see (field is mandatory) Step 2 NOW EMP TABLE- CREATE TABLE EMP( EMPNO NUMBER(10)NOT NULL, ENAME VARCHAR2(50)NOT NULL, JOB VARCHAR2(50), MGR VARCHAR2(50),…
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?
The table STUDENT has the attributes STUDENT_ID, NAME, SCHOOL_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes? a. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL b. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL USING(SCHOOL_ID) c. SELECT * SCHOOL_ID FROM STUDENT NATURAL JOIN SCHOOL d. SELECT * SCHOOL_ID FROM STUDENT, SCHOOL WHERE SCHOOL_ID = SCHOOL_ID
Primary key identifies a unique tuple of a relation, true or false?
) Consider the relations r(A, B, D), and s(B, D, F), in which A, B, D and F are their attributes. Thefollowing SQL query based on these two relations has a nested subquery. Provide an equivalent SQL querywhich does not have any nested subquery.select A, Bfrom rwhere r.B < some (select Bfrom swhere s.D= r.D)
Consider the following relations for a university: Student (sid: integer, sname: string(100 chars), GPA: real, dateOfBirth: date, Did: integer, FirstYear: integer)Department (did: integer, dname: string(30 chars), capacity: integer)Course (cid: integer, cname: string(50 chars), credit: integer)Enroll (studentID: integer, courseID: integer, departmentID: integer, enrollmentDate: date, finalGrade: real) studentID, courseID, and departmentID in Enroll are foreign keys referencing the primary keys of the student, course, and department relations, respectively. Did in Student is a foreign key referencing the primary key of the department relation. 1) For each student with GPA between 2.5 (included) and 3.5 (included), display the student’s name, GPA,and his/her department ID. Sort the results by the students’ IDs in descending order. 2) For each student who joined the Computer Science department between 2018 (included) and 2020(included), display the…
Assume a user creates a new relation r1 with a foreign key that links to another relation r2. What degree of r2 access does the user need? Why can't something be done without prior authorization?
Let's say a user creates a new relation r1 with a foreign key pointing to relation r2 as an example. What level of authorization is needed by the user on r2? Why can't this be done without getting permission at all?
Given the following relation that stores details about students' activities: StudentID StudentName Activity ActivityFee AmountPaid 100 Jones Golf 65.00 65.00 100 Jones Skiing 200.00 0.00 200 Davis Skiing 200.00 0.00 200 Davis Swimming 50.00 50.00 300 Garrett Skiing 200.00 100.00 300 Garrett Swimming 50.00 50.00 400 Jones Golf 65.00 65.00 400 Jones Swimming 50.00 50.00