Assignment: Fundamentals of Databases
Question One
Normalize the following schema, with given constraints, to BCNF.
books(accessionno, isbn, title, author, publisher)
users(userid, name, deptid, deptname)
accessionno isbn
isbn → title
isbn → publisher
isbn → author
userid → name
userid → deptid
deptid → deptname
Question Two
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 $3000.
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).