assignemnt2

docx

School

Collin County Community College District *

*We aren’t endorsed by this school

Course

1091

Subject

Computer Science

Date

Feb 20, 2024

Type

docx

Pages

9

Uploaded by BarristerSnakePerson849

Report
Reverse Engineered ERD
Data Model Questions – Short Answer Questions 1. Find out which is the parent and child tables from above diagram Parent tables: zipcode, student, section, grade_type, grade_type_weight, enrollment, instructor, course Child tables: section, course, enrollment, instructor, grade, student, grade_type_weight 2. Describe cardinality for each table (one to one or one to many or many to many) Section & course (one to many) Section & enrollment (one to many) Section & instructor (one to many) Course & course (one to many) Enrollment & student (one to many) Enrollment & grade (one to many) Zipcode & student (one to many) Zipcode & instructor (one to many) Grade_type_weight & grade_type (one to many) Grade_type_weight & grade (one to many) Grade_type_weight & enrollment (many to many) Course & instructor (many to many) Section & student (many to many) 3. We want to find out all the courses taken by students. Also, we want to know section and instructor teaches those courses. Providing Joining detail. SELECT c.description,s.section_no, i.first_name || ', '|| i.last_name as "Teacher"FROM student st, enrollment e, section s, instructor i, course c WHERE e.section_id = c.course_no and st.student_id = e.student_id AND e.section_id = s.section_id AND i.instructor_id = s.instructor_id 4. Add a new entity called YOUR_ID_DEPARTMENT , have attributes for department as department_id, department_name, etc. create a relationship for department table with course and students. Provide explanation for the relationship you have chosen for those two tables. CREATE TABLE `your_id_department`( `dept_id` int NOT NULL, `dept_name` varchar(50) NOT NULL, `dept_description` varchar(50) Default null, PRIMARY KEY (`dept_id`) ); INSERT INTO `your_id_department` VALUES (1,'science','computer science'), (2,'medical','medical sciences');
Alter table `student` add FOREIGN KEY (`dept_id`) REFERENCES `your_id_department` (`dept_id`); Alter table `course` add FOREIGN KEY (`dept_id`) REFERENCES `your_id_department` (`dept_id`); I have create one to many relationship for both the student and course table with your_id_department because each department can have many students and each department can have many courses. Part 2 SELECT ‘KHS190003’ , current_user(), @@hostname, now(), ( SELECT Distinct state FROM students_db.zipcode Order by rand() limit 1) State, ( SELECT course_no FROM students_db.course where prerequisite is not null Order by rand() limit 1 ) Course_NO, (select last_name from students_db.instructor Order by rand() limit 1) last_name,
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
( select zip from students_db.zipcode order by rand() limit 1) zipcode1 , ( select zip from students_db.zipcode order by rand() limit 1) zipcode2, ( select zip from students_db.zipcode order by rand() limit 1) zipcode3 Output: Random Column Values: Your Id : KHS190003 STATE : OH COURSE_NO : 124 Instrutor : 102, 103, 104, 105 (these instructor ids teach course no 124) Zipcodes : 07417, 11228, 07107 SECTION 1: SQL Queries (provide screenshots of each code in MySQL with output) 1. Write a SELECT statement to list course data sorted by course number select * from course order by course_no; 2. Write a SELECT statement to list zip codes and their cities in STATE (replace with value you got from random column values) sorted by zip code select zip, city from zipcode group by state order by zip
3. Write a SELECT statement to list first and last names of all instructors sorted by last name. select first_name, last_name from instructor order by last_name; 4. Write a SELECT statement to list courses that have the course_no as a prerequisite. select * from course where prerequisite in (select course_no from course);
5. Write a SELECT statement to list the last and first names in last name order of students that are currently employed by the firm Electronic Engineers and have last names starting with "G". select first_name, last_name from student where employer = 'Electronic Engineers' and last_name like 'G%' order by last_name; 6. Write the same SELECT statement as #5 but include students who are Electronic Engineers employees with the last name beginning with S or B. (Hint: Use parentheses in the WHERE clause to isolate the OR logic). select first_name, last_name from student where employer = 'Electronic Engineers' and (last_name like 'S%' or last_name like 'B%') order by last_name; 7. Write a SELECT statement to list students that live either in one of zip codes select * from student where zip in (select zip from zipcode); 8. List all classes (course_no, section_no) taught by instructor . select section_id, course_no from section where instructor_id in (select instructor_id from instructor)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
9. Show the student roster (use the format: <last name>, <first name> in a single column) for each section that Todd Smythe teaches. Identify the section using course number AND section number. select last_name + first_name as full_name from student inner join enrollment on student.student_id = enrollment.student_id where enrollment.section_id in (select section_id from section where instructor_id in (select instructor_id from instructor where first_name = 'Todd' and last_name = 'Smythe')) 10. 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. SELECT st.last_name||', '||st.first_name Name FROM student st, enrollment e, section s, instructor i, zipcode z WHERE st.student_id = e.student_id AND e.section_id = s.section_id AND i.instructor_id = s.instructor_id AND i.last_name = 'Lowry' AND st.zip = z.zip AND z.state = 'NJ' ORDER BY st. last_name 11. List the sections taught by instructors who do not live in New Jersey, showing instructor and state along with course and section numbers. select i.last_name, z.state, s.course_no,s.section_no from section s, instructor i, zipcode z where s.instructor_id = i.instructor_id and i.zip = z.zip and z.state !='NJ';
12. Show the number of enrollments for section 1 of Course number 350. Display at least section AND course numbers. select section_id, count(*) from enrollment group by section_id in (select section_id from section where course_no=350); 13. Display course description, total capacity, and number of sections in each course, where there is more than 1 section select c.description, s.capacity, count(s.section_id) as "No of Sections" from students_db.course c, students_db.section s, students_db.enrollment e where e.section_id = s.section_id and c.course_no = s.course_no group by c.course_no having count(s.section_id)> 3;
14. List all instructors and how many sections they teach select instructor_id, count(section_id) as number_of_sections_teaching from section group by instructor_id 15. Display the course number, number of sections, and total capacity for courses having more than 3 sections. select description, SUM(s.capacity), count(s.section_no) from course c, section s where c.course_no = s.course_no group by description having count(s.section_no)> 1;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Assignment #6.docx
620 Exam.pdf
INFT 1207.Lab1.Advanced Python.pdf
StormNalinHW13.pdf
WK6.docx
Ethical use of AI.docx
ch 19 - lifespan.docx
4.2.1 Lecture - Start.sql
3.2.1 Lecture SQL - Start.sql
CYB 210 Network Configuration Scavenger Hunt Template.docx
lab_2_worksheet.docx
lab_2_worksheet.doc
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS