Using the relational schema for the College Applications database, write relational algebra expressions that perform the...
Using the relational schema for the College Applications
database, write relational algebra expressions
that perform the following problems. Complete 5 out of 6 for full
credit.
Apply(sID, cName, major, decision)
Student(sID, sName, GPA, sizeHS)
College(cName, state, enrollment)
1. List the ids and names of the students that applied to the CS Dept at MIT.
2. For students that have applied to a college, list the
student’s id, name and number of different
colleges the students has applied to.
3. List the ids of all students who were accepted to a CS major
at a college with an enrollment over
25,000.
4. List the names and ids of the students that applied to both OSU
and U of O.
5. List the names and ids of the students that applied to OSU but did not apply to U of O.
6. List the ids of students who applied to all of the colleges.
Homework Answers
Answer is as follows :
As defined we answer the 5 queries :
if there is any query please ask in comments..
Add Answer to:
Using the relational schema for the College Applications
database, write relational algebra expressions
that perform the...
2. The following figure shows an ER schema for a university database. a) Map this schema...
2. The following figure shows an ER schema for a university database. a) Map this schema into a relational schema b)Show the SQL statements needed to create the relations, using proper constraints. If your translation cannot capture any constraints in the ER diagram, explain why. COffice CName CPhone COLLEGE 0. 1 D Rank Office IName IPhone INSTRUCTOR D EAN (0.NO CStartDate (0.1) <ADMINS . CHIAR (1,1) EMPLOYS (MName FName LName (SName DOB DName Sid IO.NO Name Poate DEPT on H...
Give the following queries in the relational algebra using the following relational schema: Student (sid, name,...
Give the following queries in the relational algebra using the following relational schema: Student (sid, name, age, address, email, cgpa) enrolledIn (sid, modulecode, grade) module (modulecode, modulename, credits, lecturername) a) What are the names of students enrolled in module code “cs3020”? b) Give the module code of all the modules that are taught by the teacher named “Altaf”? c) Who teaches the module with code”cs1500”? d) Give the name of all the teachers teaching the student with id”12f6778”.
Consider the following GRADEBOOK relational schema describing the data for a grade book of a particular...
Consider the following GRADEBOOK relational schema describing
the data for a grade
book of a particular instructor (Note: The attributes A, B, C,
and D store grade cutoffs.)
catalog(cno,ctitle)
students(sid,fname,lname,minit)
courses(term,secno,cno,A,B,C,D)
enrolls(sid,term,secno)
Specify and execute the following queries using the RA
interpreter on the GRADEBOOK
database schema.
a. Retrieve the names of students enrolled in the ‘Automata’
class in the term of Fall
1996.
b. Retrieve the SID values of students who have enrolled in
CSc226 as well as CSc227....
1. Given the following database schema owner(owner_name, age) owns(owner_name, num_cats) Write relational algebra for the following:...
1. Given the following database schema owner(owner_name, age) owns(owner_name, num_cats) Write relational algebra for the following: Find the name of all owners above the age of 50 Find the name of all owners who own fewer than 5 cats Find the name of all owners who are below the age of 24 and own more than 4 cats 2. Given the following database schema person(name, age, account_id) account(account_id, balance) Write a relational algebra for the following Find the name of...
5.10 Express each of the following queries in relational algebra and (ii) SQL using the Student...
5.10 Express each of the following queries in relational algebra and (ii) SQL using the Student Registration System schema of Figure 3.4. a. List all courses that are taught by professors who belong to the EE or MGT departments. Exercises 189 b. List the names of all students who took courses both in spring 1997 and fall 1998 38 CHAPTER 3 The Relational Data Model FIGURE 3.4 Fragment of the Student Registration database schema STUDENT (Id:INTEGER, Name:STRING, Address:STRING, Statu PROFESSOR...
Consider the below schema of the university database (keys are in bold and underline): Students(stuID: Integer,...
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...
Given the following relational database schema (primary keys are bold and underlined). Answer questions 2.1 to 2.4 Orde...
Given the following relational database schema (primary keys are bold and underlined). Answer questions 2.1 to 2.4 Orders(orderld, customerld, dateOrdered, dateRequired, status) Customer(customerld, customerLastName, customerStreet, customerCity, customerState, customer Lip OrderDetails(orderld.productld, quantity, lineNumber, amount) Products(productld, name, description, quantity, unitPrice) Account(accountNumber, customerld, dateOpened, creditCard, mailingStreet, mailingCity, mailingState, mailingZip) 2.1 (2 Points) List all possible foreign keys. For each foreign key list both the referencing and referenced relations. 2.2 (2 Points) Devise a reasonable database instance by filling the tables with data of...
Consider the database schema of college database. Students have a major department and take classes where...
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...
Given the following relational schema, write queries in SQL to answer the English questions. The Access...
Given the following relational schema, write queries in SQL to answer the English questions. The Access Database for the schema is available, as is a DDL file. It is also available on the MySQL server. You must only submit the SQL for your answers. You can get your answers without using a DBMS or by using Access or MySQL. Customer(cid: integer, cname: string, address: string, city: string, state: string) Product(pid: integer, pname: string, price: currency, inventory: integer) Shipment(sid: integer, cid:...
Consider the Schema diagram for a university database a. Find the IDs of all students who...
Consider the Schema diagram for a university database
a. Find the IDs of all students who have taken all of the CMPSC
courses. Use the division operation.
b. Do the same operation as in problem a, using only fundamental
operations.
student takes ID course id sec id semester 1er 4112 dept_name tot cred grade advisor section course_id sec id semester year building TOOM_10 time_slof_id course course id title dept name credits department dept name building budget time slot time slot...
2. The following figure shows an ER schema for a university database. a) Map this schema into a relational schema b)Show the SQL statements needed to create the relations, using proper constraints. If your translation cannot capture any constraints in the ER diagram, explain why. COffice CName CPhone COLLEGE 0. 1 D Rank Office IName IPhone INSTRUCTOR D EAN (0.NO CStartDate (0.1) <ADMINS . CHIAR (1,1) EMPLOYS (MName FName LName (SName DOB DName Sid IO.NO Name Poate DEPT on H...
Consider the following GRADEBOOK relational schema describing
the data for a grade
book of a particular instructor (Note: The attributes A, B, C,
and D store grade cutoffs.)
catalog(cno,ctitle)
students(sid,fname,lname,minit)
courses(term,secno,cno,A,B,C,D)
enrolls(sid,term,secno)
Specify and execute the following queries using the RA
interpreter on the GRADEBOOK
database schema.
a. Retrieve the names of students enrolled in the ‘Automata’
class in the term of Fall
1996.
b. Retrieve the SID values of students who have enrolled in
CSc226 as well as CSc227....
5.10 Express each of the following queries in relational algebra and (ii) SQL using the Student Registration System schema of Figure 3.4. a. List all courses that are taught by professors who belong to the EE or MGT departments. Exercises 189 b. List the names of all students who took courses both in spring 1997 and fall 1998 38 CHAPTER 3 The Relational Data Model FIGURE 3.4 Fragment of the Student Registration database schema STUDENT (Id:INTEGER, Name:STRING, Address:STRING, Statu PROFESSOR...
Given the following relational database schema (primary keys are bold and underlined). Answer questions 2.1 to 2.4 Orders(orderld, customerld, dateOrdered, dateRequired, status) Customer(customerld, customerLastName, customerStreet, customerCity, customerState, customer Lip OrderDetails(orderld.productld, quantity, lineNumber, amount) Products(productld, name, description, quantity, unitPrice) Account(accountNumber, customerld, dateOpened, creditCard, mailingStreet, mailingCity, mailingState, mailingZip) 2.1 (2 Points) List all possible foreign keys. For each foreign key list both the referencing and referenced relations. 2.2 (2 Points) Devise a reasonable database instance by filling the tables with data of...
Consider the Schema diagram for a university database
a. Find the IDs of all students who have taken all of the CMPSC
courses. Use the division operation.
b. Do the same operation as in problem a, using only fundamental
operations.
student takes ID course id sec id semester 1er 4112 dept_name tot cred grade advisor section course_id sec id semester year building TOOM_10 time_slof_id course course id title dept name credits department dept name building budget time slot time slot...
Most questions answered within 3 hours.
-
If the momentum of a system is conserved, is the momentum of
each particle in the...
asked 1 hour ago -
What does Socrates defense reveal about the values he lives by?
What matters most to Socrates?
asked 2 hours ago -
A sample of ore containing manganese weighed 1.2354 grams. This
sample was heated in a solution...
asked 2 hours ago -
In JAVA please (need answers in a few hours!)
Exercise #2: Design and implement a program...
asked 6 hours ago -
The mass spectrum of an organic compound shows the relative
abundances of M to be 53.76%...
asked 6 hours ago -
Coca Cola’s strategy of “think local, act local” represents a
__________ approach.
Question options:
1)
transnational...
asked 7 hours ago -
which of the following is not a category of project management
risk?
a) external
b) internal...
asked 7 hours ago -
Focus on Critical Thinking: Are citizen suit provisions an
effective way to achieve environmental objectives? Do...
asked 8 hours ago -
Gaseous butane CH3CH22CH3 will react with gaseous oxygen O2 to
produce gaseous carbon dioxide CO2 and...
asked 8 hours ago -
Required to construct counters using synchronous sequential
logic. Use one hex digit to display the result....
asked 9 hours ago -
(Ultra) Large-Scale Systems –Characteristics?
explain in detail
How the nature of an enterprise affect complex...
asked 9 hours ago -
Some of the antibiotic susceptible strains show colonies within
the clear zone. What it does this...
asked 9 hours ago