Homework Help is Here – Start Your Trial Now!
Engineering
Computer Engineering
12345

12345

Computer Networking: A Top-Down Approach (7th Edition)
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
See similar textbooks
icon
Related questions
Question

C). 12345

 

 

Consider the schemas of the following relational database for a university. It has four tables, contains data about students, courses, department, and enrollment information: Student (studentID, firstName, lastName, gender, majorDept, class, GPA) Department (deptId, deptName, college) Course (courseID, courseName) Enrollment (studentID, courseID) FK – PK RELATIONSHIPS: Student (majorDept) →Department (deptID) Enrollment (studentID) → Student (studentID) Enrollment (courseID) → Course (courselD) Domain: gender = {'F', 'M'} class = {'Freshman', "Sophomore', 'Junior", "Senior'} GPA has value between 0.00 to 4.00 (values outside the range are invalid) A student belongs to one department only. If a student's major is undecided, then majorDept = NULL. A student can enroll many courses and a course is enrolled by many students. (A) TABLE CREATION Create the above 4 tables using ACCESS. Use the appropriate data type (and length) of each attribute. Construct the FK to PK relationships between tables. (B) DATA INSERTION Insert at least 10 tuples into the Student table, at least 5 tuples into the Department table, at least 8 tuples into the Course table, and 30 tuples into the Enrollment table. You can add more tuples if you want. Use your imagination to make up varieties of data for the attributes.
Transcribed Image Text:Consider the schemas of the following relational database for a university. It has four tables, contains data about students, courses, department, and enrollment information: Student (studentID, firstName, lastName, gender, majorDept, class, GPA) Department (deptId, deptName, college) Course (courseID, courseName) Enrollment (studentID, courseID) FK – PK RELATIONSHIPS: Student (majorDept) →Department (deptID) Enrollment (studentID) → Student (studentID) Enrollment (courseID) → Course (courselD) Domain: gender = {'F', 'M'} class = {'Freshman', "Sophomore', 'Junior", "Senior'} GPA has value between 0.00 to 4.00 (values outside the range are invalid) A student belongs to one department only. If a student's major is undecided, then majorDept = NULL. A student can enroll many courses and a course is enrolled by many students. (A) TABLE CREATION Create the above 4 tables using ACCESS. Use the appropriate data type (and length) of each attribute. Construct the FK to PK relationships between tables. (B) DATA INSERTION Insert at least 10 tuples into the Student table, at least 5 tuples into the Department table, at least 8 tuples into the Course table, and 30 tuples into the Enrollment table. You can add more tuples if you want. Use your imagination to make up varieties of data for the attributes.
(C) QUERY EXECUTION Design and run the following queries. Save each query as Query1, Query2, .., Query5. NOTE: You must write your SQL using the syntax you learned in class and in your textbook (lecture notes). If you generate your SQL from QBE, you will get zero point. For example, if you use INNER JOIN for JOIN operation, you will get zero point. Please use your last name as your file name and submit (upload) your .accdb file to Blackboard (single submission only). Submission via email will be ignored. Please check your syllabus for late assignment policy. (1) List the name of students (firstName and lastName), their major (deptName), and their class who have a GPA of 3.0 or better (higher). (2) Display a table of enrollment information. Your output table consists of studentID, student name (firstName and lastName), and course name (courseName). (3) Find the average GPA of all the students in each class. That is, the average GPA for Freshman, the average GPA for Sophomore, the average GPA for Junior, and the average GPA for Senior. Your resulting table consists of 2 columns: Class and Average GPA. (4) Count the total number of students in this university. Your query should display a table with a column heading called “Total_Number_of_Students". (5) Display a table consists of studentID, student name (firstName and lastName), gender, major department name (deptName), and GPA. The table should be sorted by student's GPA in descending order.
Transcribed Image Text:(C) QUERY EXECUTION Design and run the following queries. Save each query as Query1, Query2, .., Query5. NOTE: You must write your SQL using the syntax you learned in class and in your textbook (lecture notes). If you generate your SQL from QBE, you will get zero point. For example, if you use INNER JOIN for JOIN operation, you will get zero point. Please use your last name as your file name and submit (upload) your .accdb file to Blackboard (single submission only). Submission via email will be ignored. Please check your syllabus for late assignment policy. (1) List the name of students (firstName and lastName), their major (deptName), and their class who have a GPA of 3.0 or better (higher). (2) Display a table of enrollment information. Your output table consists of studentID, student name (firstName and lastName), and course name (courseName). (3) Find the average GPA of all the students in each class. That is, the average GPA for Freshman, the average GPA for Sophomore, the average GPA for Junior, and the average GPA for Senior. Your resulting table consists of 2 columns: Class and Average GPA. (4) Count the total number of students in this university. Your query should display a table with a column heading called “Total_Number_of_Students". (5) Display a table consists of studentID, student name (firstName and lastName), gender, major department name (deptName), and GPA. The table should be sorted by student's GPA in descending order.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY
SEE MORE TEXTBOOKS