Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Use Oracle SQL to create a script file to declare relation schemas, inserts data records into the relations, and query the relations. 1. Create tables
Use Oracle SQL to create a script file to declare relation schemas, inserts data records into the relations, and query the relations. 1. Create tables CoursesDescription and coursesspring 2021, conforming to the following constraints: - In table CoursesDescription: - Every course has either 1, 2, 3, or 4 credit hours A course either has one prerequisite or no prerequisite all at. If it does have a prerequisite, the prerequisite must be an existing course When deleting a record of a course c1 which is a prerequisite of another course c2, the prerequisite field of c2 should be set to null. - In table Coursesspring2021: Every course offered in Spring 2021 has a unique course reference number (crn) By default, the seatCapacity of a course is 24 For every course, the value of seatTaken (number of seats that have been taken) should never exceed the value of seatcapactiy Every course (its cno) should be an existing course in table CourseDescription When deleting a record of a course c1 from table Coursespescription, all sections of course c1 should also be removed from table coursesspring 2021 CouresesSprina 2021 Note: All necessary constraints must be declared before any actual data is inserted into the relations. Hint: - You can define a foreign key for a relation that references attributes in its own Find cno and credits of the courses that have prerequisites. The query for the above given relations should return: Find the number of courses with 'csc' as the prefix in cno. The query for the above given relations should return: 4. List crn, cno and seatTaken of courses that are offered in Spring 2021 in the descending order of the course number (cno), and ascending order of the number of seats that have been taken if the course number is the same. The query for the above given relations should return: 5. Find the lowest enrollment among all sections of CSC 190 that are offered in Spring 2021. The query for the above given relations should return: 6. Find the total enrollment (i.g., total number of seats taken) in courses offered by CSC department in Spring 2021. The query for the above given relations should return: 7. Write and execute a PL/SQL function that - increases the seat capacity to 50 for all 100 -level courses (e.g., aso 110 , che 120 and inf100) - returns the average enrollment of 100 -level courses
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started