COMP 3005代写、代写C++语言、代做Database、c/c++程序设计调试

日期：2020-02-15 09:05

COMP 3005: Database Management Systems (Due: Friday Feb. 14, 2019 (11:59 PM))

Assignment #2

Instructor: Ahmed El-Roby Name: , ID:

Instructions: Read all the instructions below carefully before you start working on the assignment, and before

you make a submission.

? The accepted formats for your submission are: pdf, docx, txt, and java. More details below.

? You can either write your solutions in the tex file (then build to pdf) or by writing your solution by hand

or using your preferred editor (then convert to pdf or docx). However, you are encouraged to write your

solutions in the tex file (5% bonus). If you decide not to write your answer in tex, it is your responsibility

to make sure you write your name and ID on the submission file.

? If you use the tex file, make sure you edit line 28 to add your name and ID. Only write your solution and

do not change anything else in the tex file. If you do, you will be penalized.

? All questions in this assignment use the university schema discussed in class (on culearn), unless otherwise

stated.

? For SQL questions, upload a text file with your queries in the format shown in the file “template.txt”

uploaded on culearn. An example submission is in the file “sample.txt”. You will be penalized if the

format is incorrect or there is no text file submission.

? For programming questions, upload your .java file.

? Late submissions are allowed for 24 hours after the deadline above with a penalty of 10% of the total

grade of the assignment. Submissions after more than 24 are not allowed.

Q 1: (3 points)

Consider the following DDL statements:

create table takes

(ID varchar(5),

course_id varchar(8),

sec_id varchar(8),

semester varchar(6),

year numeric(4,0),

grade varchar(2),

primary key (ID, course_id, sec_id, semester, year),

foreign key (course_id,sec_id, semester, year) references section

on delete cascade,

foreign key (ID) references student

on delete cascade

);

create table section

(course_id varchar(8),

sec_id varchar(8),

semester varchar(6)

check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)),

year numeric(4,0)

check (year > 1701 and year < 2100),

building varchar(15),

room_number varchar(7),

1

– Assignment #2 2

time_slot_id varchar(4),

primary key (course_id, sec_id, semester, year),

foreign key (course_id) references course

on delete cascade,

foreign key (building, room_number) references classroom

on delete set null

);

Now, consider the following SQL query:

select course_id, semester, year, sec_id, avg (tot_cred)

from takes natural join student

where year = 2017

group by course_id, semester, year, sec_id

having count (ID) >= 2;

Will appending natural join section in the from clause change the returned result? Explain why?

Q 2: (2 points)

Write an SQL query using the university schema to find the names of each instructor who has never taught a

course at the university. Do this using no subqueries and no set operations.

Q 3: (2 points)

Rewrite the following query to replace the natural join with an inner join with using condition:

select *

from section natural join classroom;

Q 4: (5 points)

Using the university schema, define a view tot credits (year, num credits), giving the total number of credits

taken in each year. Then, explain why insertions would not be possible into this view.

Q 5: (10 points)

Write a Java program that finds all prerequisites for a given course using JDBC. The program should:

? Takes a course id value as input using keyboard.

? Finds the prerequisites of this course through a SQL query.

? For each course returned, repeats the previous step until no new prerequisites can be found.

? Prints the results.

Don’t forget to handle the case for cyclic prerequisites. For example, if course A is prerequisite to course B,

course B is prerequisite to course C, and course C is prerequisite to course A, do not infinite loop.

Q 6: (5 points)

Consider the following schema:

employee(emp name, street, city)

works(emp name, company name, salary)

Write a function avg sal that takes a company name as input and finds the average salary of employees in the

company. Then, write a SQL query that uses this function to find companies whose employees earn (on average)

higher salary than the company “Losers Inc.”.

Q 7: (10 points)

– Assignment #2 3

Design a database for a hospital with a set of patients and a set of medical doctors. The database should

capture each encounter between a doctor and a patient on each visit. Associate with each patient a log of the

various tests and examinations conducted. Those tests can be ordered by a doctor, but this is not mandatory.

The database should keep track of the dates of the tests as well as their results. Draw an ER-diagram for your

design. Underline the primary key in the entity sets.

Q 8: (6 points)

Reduce your ER-diagram to relations. Underline primary keys.

Q 9: (5 points)

If any test must be ordered by only one doctor, what needs to be changed in your answer to Q7? Either

draw a new ER-diagram, or describe the changes. How will the reduced relations change?