l Normalize the following schema, with given constraints, to BCNF. (2 Point) boo

Question

l Normalize the following schema, with given constraints, to BCNF. (2 Point) books (accessionn, isbn, title, author, publisher) users (useri name, deptid, deptname) accessionno isbn isbn title isbn publisher isbn author userid name userid deptid deptid dept name 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 each of the following queries: (0.5 0.5- 1 Point) a. Find all employees who work directly for "STC" b. Find all cities of residence of all employees 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 (0.5+0.5+1-2 Points) 3 Consider the following database: person (driver id, name, address) car (license, model, year) accident (report number, date, location) owns (driver id, license) participated (report number, license, driver id, damage amount) 1. Write SQL DDL corresponding to the following schema. Make any reasonable assumptions about data types, and be sure to declare primary and foreign keys. 2. Find the number of accidents in which the cars belonging to "John Smith were involved. 3. Update the damage amount for the car with license number "AABB2000" in the accident with report number "AR2197 to S3000.

Explanation / Answer

Answer 1)

books(isbn,title,publisher,author)

accession(accessionn,isbn)

users(userid,name,deptid)

departments(deptid,deptname)

Answer 2)

a) SELECT * FROM employee a, work b,
ON a.person_name ==b.person_name,
WHERE b.company_name=”STC”;

Or

person­_name ( company_name = “STC (works))

b) SELECT city FROM employee a, work b,
ON a.person_name ==b.person_name,
WHERE b.company_name=”STC”;

or

a)

{t | m manages (t[person_name] = m[person_name] m[company_name] = ’STC’)}

b)

{t | m manages e employee(e[person_name] = m[person_name] m[company_name] = ’STC’ t[city] = e[city])}

Answer 3)

2)select count(distinct*) from accident

where exists (select * from participated, person where participated.driver_id = person.driver_id and person.name = ’John Smith’and accident.report_number=participated.report_number)

3) update participated set damage_amount = 3000 where report_number =“AR2197” and driver_id in(select driver_id from owns where license =“AABB2000”)

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.