Computer Science homework help
Computer Science homework help. Final Exam
CSCI 6623.81/3268.21: Database Systems
Thursday, November 19, 2020 at 11:00 AM
Total Points: 100 Time: 2 Hours
Note: 1. Attempt all questions. The grade point of every question is written next to the question number.
2. If undergraduate students would like to earn extra or bonus points, you may attempt graduate student’s question (Q3).
3. You need to submit a single *.pdf file that must include answers of all question and screen shots of executed query (power shell/terminal).
Q1. (8*10 + 5 = 85 points) Consider student’s database (used in lectures) for following questions. Write SQL query for following questions.
(a) (10 points) Describe the result set you get when executing the following SQL statement. Format the result to make it more readable.
select * from grade_type;
(b) (10 points) Create a list of all students with middle initials (specifically, with a first name AND then a middle initial) AND remove the middle initial from the first name. Take care: some first names are a single initial only. [HINT: use the INSTR function embedded within other functions).
(c) (10 points) Display the course number, number of sections and total capacity for courses having more than 3 sections.
(d) (10 points) List Charles Lowry’s students (use the format: <last name>, <first name> in a single column) that live in New Jersey. Sort the result by last name.
(e) (10 points) Show the number of enrollments for section 1 of Course number 350. Display at least section AND course numbers.
(f) (10 points) (use subqueries or correlated subqueries concepts).
Show all students (use the format: <last name>, <first initial> in a single column) who are enrolled in more than two classes.
(g) (10 points). (use either join or subqueries or correlated subqueries concepts).
Show all the sections whose enrollment is greater than 4. Display course and section number.
(h) (10 points) Write a table creation script for a table called OWNER. Include the following:
· Column for the owner id; primary key
· Column for the owner username (i.e., the logon name); unique and not null
· Column for the owner’s first name; can be null
· Column for the owner’s last name; can be null
Determine the appropriate data types. And, Add a row to OWNER using the following values:
* Owner id = 20
* Owner username = Cartman_E
* Owner first name = Eric
* Owner last name = Cartman
(i) (5 points) List all instructors and how many sections they teach.
(a) Q2. (5+5+5 = 15 points) Normalize the EMPLOYEE schema, with given constraints, to INF, 2NF 3NF. Show your work.
| Emp# | EmpName | Project# | ProjectName | JobClass | ChargeHour | Hours |
| 276 | Jones | S26, M21 | Production | Sales, Manager | $30, $45 | 8 |
The candidate key: {Emp#, Project#} and the functional dependencies are:
Emp# EmpName
Project# ProjectName
Emp# JobClass
EMP# ChargeHour
Emp# Hours
JobClass ChargeHour
(Only for graduate students)
Q3. (5+5+5+5 = 20 points) Create an empty table called SECTION2 with the same structure as the SECTION table. Create a view on the SECTION2 table that will be used for updating the table and restricts updates to sections with capacities that are less than 25. Write two INSERT statements – one that succeeds and one that fails and that uses the view to insert into the SECTION2 table.
*****
Talk to us support@bestqualitywriters.com