Database_ Review and Paraphrase Assignment

Student Details:

Name: ###

Fundamentals of Databases IT-403 2 Marks

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

Answer 1NF :

Select primary key for each relation books(accessionno, isbn, title, author, publisher) users(userid, name, deptid, deptname)

2NF: All relations are in 2NF because the primary key is single attribute

books(accessionno, isbn, title, author, publisher) users(userid, name, deptid, deptname)

3NF

Prevent any transitively dependent in the 2NF relations Also all relations are in BCNF

book(isbn, title, publisher,author) 


accession(accessionno, isbn) 
 user(userid, name, deptid) 
 department(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)

3 Marks

Answer

a- create table person

( driver_id char(10) primary key, name varchar(40) not null, address varchar(60)

)

create table car (

license char(10) primary key, model varchar(20) not null, year int

)

create table accident (

report_number char(10) primary key, date varchar(20), location varchar(40),

) create table owns (

driver_id char(10) references driver, license char(10) references car, primary key(driver_id, license)

) create table participated (

report_number char(10) primary key, license char(10) references car, driver_id char(10) references driver, damage_amount dec(6,2),

)

b-

select count (distinct accident.report_number) Accidents 
 from person, accident, participated 
 where participated .report_number= accident.report_number
 and participated.driver_id = person. driver_id

and person.name ="John Smith" c- update participated 


set damage_amount = 3000
 where report_number ="AR2197"

"and license = "AABB2000