Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / There are 5 parts in the final exam
There are 5 parts in the final exam.
PART A
Suppose you are given three relations: course, student and takes.
Create tabke course
(course_id varchar(8),
Title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
primary key (course_id),
);
create table student
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0) check (tot_cred > =0),
primary key (ID),
);
Create table takes
(ID varchar(5),
Course_id varchar(8),
Sec_id varchar(8),
Semester varchar(6),
Year numeric(4,0),
Grade numeric(4,0)
Foreign key (ID) references student (ID) on delete cascade,
Foreign key (course_id) references course (course_id) on delete cascade
);
Write each of the following queries on SQL.
(1) Find the total grade earned by the student with ID 52378, across all course taken by the student.
(2) Find the average (GPA) for the student with ID 52378, the total grade divided by the total credits for the associated course.
(3) Find the ID and average grade of every student.
(4) Find the ID and name of student who took all course.
(5) Find the ID and name of student who didn’t take any course.
PART B
Consider the SQL query
Select p.a
From p, m, n
Where p.a = m.a or p.a = n.a
(5) Under what conditions does the preceding query select values of p.a that are either in m or in n?
PART C
We’ve created a small sample database to use for this assignment. It contains four relation:
Person(name, age, gender) // name is a key
Frequents(name, pizzeria) //[name,pizzerial] is a key
Eats(name, pizza) // [name,pizza] is a key
Serves(pizzeria, pizza, price) //[pizzeria,pizza] is a key
Write each of the following queries in Relational Algebra.
(6) Find all pizzas eaten by at least one female over the age of 20. (5 points)
(7)Find all pizzas eaten by all female over the age of 20. (5 points)
(8) Find all pizzerias that serve at least one pizza for less than $10 that both Amy and Fay eat. (5 points)
(9) Find the names of all people who eat at least one pizza served by Dominos but who do not frequent Dominos. (5 points)
PART D
Consider the following relational schema:
UnivInfo (studID, studName, course, textbook, profID, title, salary, profOffice)
Each tuple in relation UnivInfo encodes the fact that the student with the given ID and name took the given course from the professor with the given ID and office. Assume that students have unique IDs but not necessarily unique names, and professors have unique IDs but not necessarily unique offices. Each student has one name; each professor has one office. Each professor has one title. Each course uses one textbook. Assume that professors with the same title must have the same salary. A student can take different courses given by the same professor.
(10) Specify a set of completely nontrivial functional dependencies for relation UnivInfo that encodes the assumptions described above and no additional assumptions. (10 points)
(11) Based on your functional dependencies in part (a), specify all minimal keys for relation UnivInfo. (10 points)
(12) Is UnivInfo in Boyce-Codd Normal Form (BCNF) according to your answers to (a) and (b)? If not, give a decomposition of UnivInfo into BCNF. (10 points)
PART E (10 points)
Consider the following two relational schemas:
Schema 1: R(A,B,C,D)
Schema 2: R1(A,B,C), R2(B,D)
(13) Consider Schema 1 and suppose that the only functional dependencies that hold on the relations in this schema are A → B, C → D, and all dependencies that follow from these. Is Schema 1 in Boyce-Codd Normal Form (BCNF)? (5 points)
(14) Consider Schema 2 and suppose that the only functional dependencies that hold on the relations in this schema are A → B, A → C, B → A, A → D, and all dependencies that follow from these. Is Schema 2 in BCNF? (5 points)
