Consider the following relational database schema-
Doctor(SSN,FirstName,LastName,Speciality,YearsOfExperience,PhoneNum)
Patient(SSN,FirstName,LastName,Address,DOB,PrimaryDoctor_SSN)
Medicine(TradeName,UnitPrice,GenericFlag)
Prescription(Id,Date,Doctor_SSN,Patient_SSN)
Prescription_Medicine(Prescription_Id,Trade_Name,NumOfUnits)
Write Relational Algebra expression for the following:
List the first and last name of patients whose primary doctor named “Rahul kumar”
List the first and last name of doctors who are not primary doctors of any patient
List the first and last name of patients who have no prescription written by doctors other than the primary doctors
List the trade name of generic medicine with unit price greater than Rs. 50
List the SSN of Patients who have “Disprin” prescriped to them by doctor name “Suresh Verma”
find average YearsofExperience based on Speciality of doctors
1.
Patient.FirstName,Patient.LastName(
PrimaryDoctor_SSN = 'Rahul Kumar' Patient
PrimaryDoctor_SSN = SSN Doctor)
2.
Doctor.FirstName,Doctor.LastName(
PrimaryDoctor_SSN != SSN Patient
Doctor)
here
is theta join not equi join
3.
Patient.FirstName,Patient.LastName(
Patient
PrimaryDoctor_SSN = SSN Doctor
SSN = Doctor_SSN Prescription)
4.
Medicine.TradeName(
UnitPrice > 50 Prescription_Medicine
Trade_Name = TradeName Medicine )
5.
Patient.SSN(
Trade_Name = 'Disprin' and Doctor.FirstName = 'Suresh' and
Doctor.LastName = 'Verma' Patient
PrimaryDoctor_SSN = SSN Doctor
SSN = Doctor_SSN Prescription)
6.
avg(YearsOfExperience)
group by Specilality(Doctor )
Do ask if any doubt. Please upvote.
Consider the following relational database schema- Doctor(SSN,FirstName,LastName,Speciality,YearsOfExperience,PhoneNum) Patient(SSN,FirstName,LastName,Address,DOB,PrimaryDoctor_SSN) Medicine(TradeName,UnitPrice,GenericFlag) Prescription(Id,Date,Doctor_S
QUESTION B1. (7 marks) This question assumes the following relational schema Employee (employeeNumber, lastName, firstName, DOB, HireDate, Position Salary, Dept) Primary Key: employeeNumber Foreign key: Dept refers to DeptID in Department Department (DeptID, DeptName, DeptLocation) Primary Key: DeptID You have been given the following MySQL stored procedure: CREATE PROCEDURE Find_EmployeeName (IN employeeNo INT (11), OUT employeeName VARCHAR (60)) BEGIN SELECT concat(firstName, '', lastName) INTO employeeName FROM employees WHERE employeeNumber employeeNo; END (a) (2 marks) Name the two types of parameters...
Question 3 refers to the relational database with a schema described below: The hospital database contains information about the treatments of patients performed by the doctors. The database also contains information on the prescriptions ordered by the doctors. The schemas of relational tables, the meanings of attributes and specifications of primary, candidate, and foreign keys are given below. HOSPITAL HospitalCd Name Address Estate PostalCode EstablishedDate Hospital Code Name of the hospital Address of the hospital The estate where the hospital...
Consider the following information about a patient database: A patient is identified by patient id, and admission date. A patient must be either an Emergency patient or a resident patient. We must record the checkback date for Emergency patient. Only resident patient is assigned to one room. Room has a unique number, degree, and floor number. For each room there are a number of beds each of which has a bed number, specification. If a room is deleted, you need...
Consider the relational database schema for a company below. EMPLOYEE/NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNA) TA DEPARTMENT(DNAME, DNUMBER. MESSINS, MGRSTARTDATE) DEPT_LOCATIONS(DNUMBER. DLOCATION PROJECT(PNAME, PNUMBER. PLOCATION, DNLIM) WORKS_ONCESSN.PNG, HOURS) DEPENDENTESSN, DEPENDENT-NAME, SEX, BDATE, RELATIONSHIP) Write SQL statements for the following queries: a) List the names of those employees who work in the "Production" department (6 marks). b) Find the maximum salary, minimum salary, and the average salary among employees who work for the "Production department (6 marks). Count the...
CHAPTER 1: DATABASES AND DATABASE USERS Given the simple Doctor-Treats_Patient database schema which contains three files describing information about doctors and patients they treat as follows, answer the following questions with regards to this database. (Total for que 1 is 10 marks) Doctor (DocID: string, DName: string, DAddress: string, Numpatients: integer) Treats (DocId: string, SSN: string, illness: string, day: string, cost: real) Patient (SSN: string, PatientName: string, PAddress: string, Age:integer) Note : DocID, DName, DAddress and Numpatients...
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 following relations: Patient (pid: integer, lname: string, fname: string, primary_did: integer, age: integer) Doctor (did: integer, lname: string, fname: string, deptid: integer, age: integer, salary: integer) Nurse (nid: integer, lname: string, fname: string, deptid: integer, rank: string, age: integer) Department (deptid: integer, name: string, budget: integer) Menu (menuid: integer, pid: integer, caloriecount: integer, saltlevel: integer, vegetarian: bit, diabetic: bit, nauseaSafe: bit, notes: string) Vaccination (vaccinationid: integer, pid: integer, vaccinationname: string, dategiven: date, dateexpires: date) Prescription (presid: integer, pid:integer,...