Q1:When is a table of Project Code, the values for Project Code, Project Title,

Question

Q1:When is a table of Project Code, the values for Project Code, Project Title, Project Manager and Project Budget, employee No. ,employee Name, department No. ,department name, Hourly rate in BCNF?

Q2:Convert following un-normalized table into normalized tables( The table is Project Code, the values for Project Code, Project Title, Project Manager and Project Budget, employee No. ,employee Name, department No. ,department name, Hourly rate )?

Q3: Write the following queries in SQL, using the university schema.                                              (1.5 Point)

classroom(building, room number, capacity)

department(dept name, building, budget)

course(course id, title, dept name, credits)

instructor(ID, name, dept name, salary)

section(course id, sec id, semester, year, building, room number, time slot id)

teaches(ID, course id, sec id, semester, year)

student(ID, name, dept name, tot cred)

takes(ID, course id, sec id, semester, year, grade)

advisor(s ID, i ID)

time slot(time slot id, day, start time, end time)

prereq(course id, prereq id)

a.Find the titles of courses in the Comp. Sci. department that have 3 credits.

b.Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result

c.Find the maximum enrollment, across all sections, in Autumn 2009.

d.Find the IDs and names of all students who have not taken any course offering before spring 2009

e.Increase the salary of each instructor in the Comp. Sci. department by 10%.

f.Delete all courses that have never been offered (that is, do not occur in the section relation).

g.Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.

h.Create a new course “CS-001”, titled “Weekly Seminar”,with 0 credits.

i.Create a section of this course in autumn 2009, with sec id of 1.

j.Enroll every student in the Comp. Sci. department in the above section.

Explanation / Answer

1. BCNF: BCNF stands for Boyce-Codd Normal Form. It is the next level of 3NF and handles the case of multiple overlapping candidate keys. A table is said to be in BCNF iff:

a) If it is already in 3NF.

b) For each functional dependency of type P -> Q, P is the super key.

2. To covert a table into BCNF, following step need to be performed:

a) If table is not in 1NF, then covert it into 1NF i.e. remove repeating groups.

b) At second step, check whether it is in 2NF. It not, then convert it into 2NF i.e. remove partial key dependencies.

c) Now check if it is in 3NF. If not, then convert it into 3NF i.e. remove transitive dependencies.

d) At last, convert table into BCNF, if it is not i.e. remove overlapping candidate keys.

Let given table be represented by R. Then:

---------------------------------------

R: Project Code, the values for Project Code, Project Title, Project Manager and Project Budget, employee No. ,employee Name, department No. ,department name, Hourly rate

--------------------------------------------

a) 1NF - Remove repeating groups:

This table is not in 1NF as clear from the structure of the table. In table, column 'the values for Project Code' will have repeating values. In 1NF, each row should be unique although it may increase data redundancy. On the same lines, columns 'Project Title', 'Project Manager and Project Budget' etc. will also have repeating values. Based on this observation, structure of given table can be altered as:

------------------------------------------------------------------------

R: Project Code, Project Code Value, Project Title, Project Manager, Project Budget, employee No. ,employee Name, department No. ,department name, Hourly rate

-----------------------------------------------

2. 2NF - Remove partial key dependencies:

In the structure obtained after converting table in 1NF, no single column can be used as a primary key and in case of a concatenated primary (For ex. <Project Code, Employee No.>, there will be partial dependencies. Hence, the structure is not in 2NF. For 2NF, we can use the concept of foreign key. We can create following new structures and each will be in 2NF:

-------------------------------------------------------------------------

R1: Project Code, Project Code Value

R2: Project Code, Project Title, Project Manager, Project Budget, Hourly Rate

R3: Employee No., Employee Name, Department No., Project Code

R4: Department No., Department Name

-----------------------------------------------------------------

c) 3NF - Remove transitive dependencies, if any:

The structures obtained after step-2 don't contain any transitive dependencies. Hence, these structures are in 3NF.

d) BCNF: Remove overlapping candidate keys, if any:

It is proved structures R1, R2, R3 and R4 are in 3NF. And they don't have any overlapping candidate keys either. Hence they are in BCNF.

3) SQL queries:

a) Select titles from course where DepartmentName="Computer Sc." and credits=3

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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