1. Refer to Figure 1 below, to answer the following questions: Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 characters) FacultyID (integer, primary key) FacultyName (25 characters) CourselD (8 characters, primary key) CourseName (15 characters) Write SQL statements for creating the tables Student, Faculty and Course, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing any other constraints you may have identified for the relations. STUDENT (StudentlD, StudentName) StudentiD StudentName Letersky Altvater 38214 54907 66324 Aiken Marra 70042 FACULTY (FacultylD, FacultyName) FacultyD FacultyName Birkin Berndt Collins 2143 3467 4756 COURSE (CourselD, CourseName) CourselD CourseNarme ISM 3113 ISM 3112 ISM 4212 ISM 49a0 Syst Analysis Syst Design Database Networking Figure 1 Class Scheduling Relations

1. Refer to Figure 1 below, to answer the following questions: Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 characters) FacultyID (integer, primary key) FacultyName (25 characters) CourselD (8 characters, primary key) CourseName (15 characters) Write SQL statements for creating the tables Student, Faculty and Course, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing any other constraints you may have identified for the relations. STUDENT (StudentlD, StudentName) StudentiD StudentName Letersky Altvater 38214 54907 66324 Aiken Marra 70042 FACULTY (FacultylD, FacultyName) FacultyD FacultyName Birkin Berndt Collins 2143 3467 4756 COURSE (CourselD, CourseName) CourselD CourseNarme ISM 3113 ISM 3112 ISM 4212 ISM 49a0 Syst Analysis Syst Design Database Networking Figure 1 Class Scheduling Relations

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

How is the processing of SQL DDL statements (such as CREATE TABLE) different from the processing required by DML statements?
Hospital tables Assignment #1 Create tables based on the below given relational model. Define your own data type to the attributes and apply your own constraints to the attributes Identify the primary, foreign keys apply it to the attributes. Populate the data in all the tables using SQL commands/queries. Hospital tables: patients (patient-id, name, insurance, date-admitted, date-checked-out) doctors (doctor-id, name, specialization) test (testid, testname, date, time, result) doctor-patient (patient-id, doctor-id) test-log (testid, patient-id) performed-by (testid, doctor-id) Assignment #2 Function: Create a function which accepts patient id and display the patients name, date admitted. Sample output is: The patient name is Maryam, she got admitted on 12-Feb-2020 Procedure Write a Procedure that accepts patient id and display the patient id, name, test name and result. Package Create a package which contains the above created procedure in the specification. Create package…
Consider the following relational schema and answer the following questions. Employee (Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id) a. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager. b. Write a query to get the department ID and the total salary payable in each department. c. Write a query to get the average salary for each job ID excluding programmer. d. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only.
Consider the following relational data model. Employee (empid, ename, address, title, headid) Works (Empid,Deptid,joindate) Department (Deptid, dname, block) Payment (title, salary) A) Write relational algebra for Display the employee name and department name of each employee. Display the employee who works as senior lecturer and belongs to BEI or BCT department. Display the employee name with corresponding department head. B) Write sql query for Creating the above relations; including appropriate versions of all primary and foreign key constraints Find the employee name and address whose address name ends with substring ‘pur’. Update the salary of senior lecturer by 25%.(Solve A - 1,2,3 only)
Question 1 Write SQL statements to answer questions 1.1, 1.2, 1.3 and 1.4 based on the following schema. Primary keys are( BOLD and italicized) and attributes with the same names in different tables are foreign keys. Doctors(doctorId, doctorLastName, doctorFirstName,specialtyID, numberOfPatientsSeen) Patients(patientId, patientLastName, patientFirstName, sex, patientBalance, numberOfVisits, patientDateOfBirth ) Records(doctorId, patientId, date, diagnosis, visitCost) Specialty(specialtyId, specialtyName, specialtyCost) 1.1) Create a view, called PatientSmithDoctors, to list all the doctors a patient with the last name of Smith has seen (be careful on this one). 1.2) Write a trigger, each time a row is inserted in the table Records, the numberOfVisits in the corresponding Patient row is updated by 1 (i.e. add 1 to the numberOfVisits) and the numberOfPatientsSeen in the Doctors table is also updated by 1. 1.3) Find the Patient’s full name with the…
Hospital tables Assignment ITDR2106-21SP Assignment #1  Create tables based on the below given relational model.  Define your own data type to the attributes and apply your own constraints to the attributes  Identify the primary, foreign keys apply it to the attributes.  Populate the data in all the tables using SQL commands/queries. Hospital tables: patients (patient-id, name, insurance, date-admitted, date-checked-out) doctors (doctor-id, name, specialization) test (testid, testname, date, time, result) doctor-patient (patient-id, doctor-id) test-log (testid, patient-id) performed-by (testid, doctor-id) Assignment #2 Function:  Create a function which accepts patient id and display the patients name, date admitted. Sample output is: The patient name is Maryam, she got admitted on 12-Feb-2020 Procedure Write a Procedure that accepts patient id and display the patient id, name, test name and result. Package  Create a package which contains the above created procedure in the…
Consider the following relational schema and answer the following questions. Employee (Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id) a. Write a query to get the average salary and number of employees working the department 90. b. Write a query to get the highest, lowest, sum, and average salary of all employees. c. Write a query to get the number of employees with the same job. d. Write a query to get the difference between the highest and lowest salaries.
Hospital tables Create tables based on the below given relational model. Define your own data type to the attributes and apply your own constraints to the attributes Identify the primary, foreign keys apply it to the attributes. Populate the data in all the tables using SQL commands/queries. Hospital tables: patients (patient-id, name, insurance, date-admitted, date-checked-out) doctors (doctor-id, name, specialization) test (testid, testname, date, time, result) doctor-patient (patient-id, doctor-id) test-log (testid, patient-id) performed-by (testid, doctor-id) Function: Create a function which accepts patient id and display the patients name, date admitted. Sample output is: The patient name is Maryam, she got admitted on 12-Feb-2020 Procedure Write a Procedure that accepts patient id and display the patient id, name, test name and result. Package Create a package which contains the above created procedure in the specification. Create package body of the above given…
consider the following schema: Product(pid, name, type, manufacturer, price) Buys(cid, pid) Customer(cid, cname, age, gender) 1) write the following query in relational algebra; find the names of all customers who have purchased all products manufactured by sears. 2)write the following in sql: find the names of all the customers who have not purchased the most expensive product. 3) Write the following query in SQL. Find the names and cids of all customers who have purchased the second most expensive product. You can assume that no two products have the same prices.
PLz help with the following: Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriate data types for each attribute and include primary key and foreign key constraints, Check and Not Null constraints.
Consider the relational schema shown in Figure 8.8. Give a schema definition in SQL corresponding to the relational schema but using references to express foreign-key relationships.

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781285867168

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

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

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781285867168

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS