2. Consider the following database and answer the questions below SPORT ENROLLMENT STUDENT COURSE ST ST...
Consider the following database and answer the questions below. a) Write the SQL statement the displays the names of the students who have enrolled in the Database Systems course b) Write the SQL statement that displays a list of the students whose names start with 'S' c) Write the SQL statement that displays the Student ID, Student Name, Course ID, and Grade for students that passed at least one course with a grade better than B+ (i.e. A-, A, and...
Consider the following relations for course-enrollment database in a university: STUDENT(S-ID,S-Name, Department, Birth-date) COURSE(C-ID, C-Name, Department) ENROLL(S-ID, C-ID, Grade) TEXTBOOK(B-ISBN, B-Title, Publisher, Author) BOOK-ADOPTION(C-ID, B-ISBN) (a) Draw the database relational schema and show the primary keys and referential integrity constraints on the schema. (b) How many superkeys does the relation TEXTBOOK have? List ALL of them. (c) Now assume each COURSE has distinct C-Name. (i) If C-ID is a primary key, what are the candidate keys and the unique keys...
Consider the database schema of college database. Students have a major department and take classes where faculty can be met. Student (cwid int, name, text, age int, majorDept text) Department (name text, chair text) Faculty (name text, deptName text) Enrollment (facName text, studID int) Note that the underlined attributes are primary keys. studID, chair, deptName of Faculty, facName and majorDept of Student are foreign keys to cwid, name of Faculty, name of Department, name of Faculty and also name of...
Question: Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course. 1- Draw an ER diagram of ONLINE COURSE REGISRATION database, that captures all the given below requirements. Specify key attribute(s) of each entity set. For each relationship set, specify structural constraints and participation constraints. a. For each USER, the portal maintains user ID, Name, E-mail. Each user has a unique ID. Name is a Composite attributes with Frame, Midname,...
31. (15 pts) Consider the following relations and relationship: Student (studentID, name, address, gender, major) Course (courselD, title, hour, department) Enrollment (StudentID, courselD, date Write the SQL statements to perf orm the following operations: List all the students' information for those majoring in "Computer Science". (2 pts) List the majors, and the numbers of the students in every major. (2 pts) List the departments, and the total number of hours of the courses offered by every department. (2 pts) List...
1. Consider the following database. Note that it distinguishes between a course and the class of that course in a particular year. Courses can have prerequisites (aka a prereq), which is again a course. Person(UPI, givenname, surname) with key UPI Courses(course, title, program) with key course Classes(classnr, course, year) with key classnr Enrollments(UPI, classnr) Grades(UPI, classnr, grade) Prereqs(course, prereq) Teachers(UPI, classnr) With natural foreign key constraints given by common attribute names. 1. List all UPIs of all teachers who teach...
Someone please answer all of these. I need these badly. The submission date is knocking at the door. Experiment 1: SQL data definition and data insertion 46 hours) 1. CREATE TABLE. The database schema consists of the three relations, whose schemas are: S (Spa, Sname. Sgender, Sage, Sdert? // students(SID, name, gender, age, department) SC (Spa, Cne. Grade) //Course(SID, CID, grade) C (One Cname Crno. Ceredit) l/courses (CID, course name, prerequisite courses, credit) 2. DROP TABLE, ALTER TABLE, CREATE INDEX,...
10- Specify the following queries in SQL on the database schema of Figure 2. a. Retrieve the names of all senior students majoring in ‘CS’ (Computer Science). b. Retrieve the names of all courses taught by Professor King in 2004 and 2005. c. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section. d. Retrieve the name and transcript of each senior student (Class =4) majoring in CS. A...
what sql statements does the following from a table like this? ex 2.4 course Id Name semester semester Grade 2.4 OL Jenent at creates the TRANSCRIPT table. Using the TRANSCRIPT table, write an SQL statement that a. Deregisters the student with Id - 123456789 from the course CS305 for the fall of 2001 b. Changes to an A the grade assigned to the student with Id - 123456789 for the course CS305 taken in the fall of 2000 C. Returns...
Consider the below schema of the university database (keys are in bold and underline): Students(stuID: Integer, stuName: String, gender: String, age: Integer, gpa: Float) Departments(deptName: String, numPhDs: Integer) ProfessorWorks(profID: Integer, profName: String, deptName: String) CoursesOffer(cNo: String, cTitle: String, deptName: String) Majors(deptName: String, stuID: Integer, degreeProgram: String; attendYear: String, attendSemester: String) Sections(cNo: String, academicYear: String, semester: String, sectNo: Integer, profID: Integer) Enrolls(stuID: Integer, cNo: String, academicYear: String, semester: String, sectNo: Integer, grade: String) Write the following queries in Relational algebra. Print...