Q. 1 Normalize the following schema, with given constraints, to BCNF. (2 Point)
ID: 3780440 • Letter: Q
Question
Q. 1 Normalize the following schema, with given constraints, to BCNF. (2 Point) books(accessionn, isbn, title, author, publisher) users (uscrid, name, deptid, deptnamc) accessionno isbn isbn title sbn publisher isbn author userid name userid deptid deptid deptname Q. 2 Consider the relational database given below, where the primary keys are underlined. Give an expression in Tuple Relational Calculus and Domain Relational Calculus for cach of the following queries: (0.5+0.5 1 Point) a. Find all employees who work directly for "STC" b. Find all citics of residence of all employccs who work directly for "STC" employee (person-name, street, city) works (person-name, company name, salary) company (company.name, city) manages (person-name, manager name)Explanation / Answer
Answer: 1
The BCNF Form Of the above Schema is Given Below:
books(isbn, title, publisher, author)
accession(accessionno, isbn)
users(userid, name, deptid)
departments(deptid, deptname)
In this isbn from books is the primary key
answer_2
SELECT*FROM employee a,Workb, ON a.person_name ==b.person_name ,street(person_name=”XYZ”/(employee))and(employee-a.person_name=”XYZ”/(employee))
SELECT*FROM work b, a.person_name, company_name,c.salary(company_name=”w”)/(works))and (works- a.company_name=”w”/(works))
The update syntax allows reference to a single relation only. Since this up-date requires access to both the relation to be updated (works) and the man-ages relation, we must use several steps. First we identify the tuples of works to be updated and store them in a temporary relation (a). Then we create a temporary relation containing the new tuples (b). Finally, we delete the tuples in a, fromworks and insert the tuples of b.aaworks.person_name,company_name,salary
(bworks.person_name=manager_name(works×manages))
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.