Consider the following ERD for a Dental Clinic and solve the following questions. Dentist PK dentNum:...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Consider the following ERD for a Dental Clinic and solve the following questions. Dentist PK dentNum: char(5) grade: char(2) denName: vrachar(25) address: varchar(25) yearsOfService: char(2) Nurse PK nursNum: char(5) grade: char(2) nursName: varchar(25) address: varchar(25) Patient PK patNum:char(6) patName varchar(25) address varchar(25) age: char(3) has has can take Appointment PK appNum: char(5) date date dateOfStartTreatment date dateOfEndTreatment date FK dentNum: char(5) FK nursNum: char(5) FK patNum:char(6) Procedure PK proNum:char(10) Section A: DDL and Relational Algebra description varchar(200) cost decimal(0,2) has contains AppointmentDetails PK appNum:char(5) PK proNum:char(10) date: date FK appNum char(5) FK proNum char(10) (7marks) 1. Create the database by using SQL DDL commands and fill the tables with at least 3 rows. (5marks) 2. Code solutions to the following problems as a relational-algebraic expression: (2marks) a. Display all patients' names who have an appointment on 1-1-2020 b. Find dentists number and name who have 10 years of service and living in Muscat. Section C: PL/SQL (8marks) 1. Write a procedure called patient_delete_procedure to delete the patient "ahmed102 once he gets well, given the patient number as a parameter. Then test the procedure. (4marks) 2. Code a function called Dentist Services Function to return the number of years of services of a given dentist. (4marks) Sign in to edit and save changes to this file. Dentist PK dentNum: char(5) grade: char(2) denName: vrachar(25) address: varchar(25) yearsOfService: char(2) Nurse PK nursNum: char(5) grade: char(2) nursName: varchar(25) address: varchar(25) Patient PK patNum:char(6) patName:varchar(25) address:varchar(25) age: char(3) has has can take Appointment PK appNum: char(5) date: date dateOfStartTreatment date dateOfEndTreatment date FK FK FK patNum:char(6) dentNum: char(5) nursNum: char(5) Procedure PK proNum:char(10) description:varchar(200) cost decimal(9,2) has contains AppointmentDetails PK appNum:char(5) PK proNum:char(10) date: date FK appNum:char(5) FK proNum:char(10) Sign in Consider the following ERD for a Dental Clinic and solve the following questions. Dentist PK dentNum: char(5) grade: char(2) denName: vrachar(25) address: varchar(25) yearsOfService: char(2) Nurse PK nursNum: char(5) grade: char(2) nursName: varchar(25) address: varchar(25) Patient PK patNum:char(6) patName varchar(25) address varchar(25) age: char(3) has has can take Appointment PK appNum: char(5) date date dateOfStartTreatment date dateOfEndTreatment date FK dentNum: char(5) FK nursNum: char(5) FK patNum:char(6) Procedure PK proNum:char(10) Section A: DDL and Relational Algebra description varchar(200) cost decimal(0,2) has contains AppointmentDetails PK appNum:char(5) PK proNum:char(10) date: date FK appNum char(5) FK proNum char(10) (7marks) 1. Create the database by using SQL DDL commands and fill the tables with at least 3 rows. (5marks) 2. Code solutions to the following problems as a relational-algebraic expression: (2marks) a. Display all patients' names who have an appointment on 1-1-2020 b. Find dentists number and name who have 10 years of service and living in Muscat. Section C: PL/SQL (8marks) 1. Write a procedure called patient_delete_procedure to delete the patient "ahmed102 once he gets well, given the patient number as a parameter. Then test the procedure. (4marks) 2. Code a function called Dentist Services Function to return the number of years of services of a given dentist. (4marks) Sign in to edit and save changes to this file. Dentist PK dentNum: char(5) grade: char(2) denName: vrachar(25) address: varchar(25) yearsOfService: char(2) Nurse PK nursNum: char(5) grade: char(2) nursName: varchar(25) address: varchar(25) Patient PK patNum:char(6) patName:varchar(25) address:varchar(25) age: char(3) has has can take Appointment PK appNum: char(5) date: date dateOfStartTreatment date dateOfEndTreatment date FK FK FK patNum:char(6) dentNum: char(5) nursNum: char(5) Procedure PK proNum:char(10) description:varchar(200) cost decimal(9,2) has contains AppointmentDetails PK appNum:char(5) PK proNum:char(10) date: date FK appNum:char(5) FK proNum:char(10) Sign in
Expert Answer:
Answer rating: 100% (QA)
To fulfill the requirements presented in the question images well break down the tasks into several parts First well address Section A and Section C a... View the full answer
Related Book For
Fraud examination
ISBN: 978-0538470841
4th edition
Authors: Steve Albrecht, Chad Albrecht, Conan Albrecht, Mark zimbelma
Posted Date:
Students also viewed these databases questions
-
Thad, a single taxpayer, has taxable income before the QBI deduction of $201,000. Thad, a CPA, operates an accounting practice as a single member LLC (which he reports as a sole proprietorship)....
-
What are the major resource dependencies for high-end restaurants? Who do they depend on most for the critical resources needed to survive? 2) The high-end restaurants appear almost helpless at the...
-
Case Study: Quick Fix Dental Practice Technology requirements Application must be built using Visual Studio 2019 or Visual Studio 2017, professional or enterprise. The community edition is not...
-
In Exercises solve the first-order differential equation by any appropriate method. y' = 2x1 - y
-
Gamma Manufacturing manufactures 256GB SD cards (memory cards for mobile phones, digital cameras, and other devices). Price and cost data for a relevant range extending to 200,000 units per month are...
-
Give the decoding key for the encoding keys in Problems 2536. Add 7, divide by 2, then multiply by 3.
-
The PCAOB main responsibilities include the following, except: (a) Registration of accounting firms (including non-US firms) that audit public companies (including non-US issuers) trading in US...
-
On September 1, Roshek Office Supply had an inventory of 30 calculators at a cost of $22 each. The company uses a perpetual inventory system. During September, the following transactions occurred....
-
Answer the following . Problem 4 The following information was included in the bank reconciliation for Bay Company for October and November 2020: Checks and charges recorded by bank in November,...
-
The decedent used his own funds in the amount of $80,000 to acquire stock naming himself and his daughter as joint tenants with right of survivor ship. When the father died, the stock was worth...
-
Complete the reaction. Part C 1. LDA, THF 2. OPh 3. H,0 queneh Draurtt
-
It is important that companies assess their risks of workplace fraud. Managers must also possess the skills to identify workplace fraud and the skills to establish internal controls to effectively...
-
See the option quote on IBM from the CBOE Web site on the next page showing options expiring in March and April 2022. a. Which option contract had the most trades that day? b. Which option contract...
-
For each of these five separate cases, identify the principle(s) of internal control that is violated. Recommend what the business should do to ensure adherence to principles of internal control. 1....
-
Assume you are the owner of a small business. You are planning on borrowing money from a local bank. What are some features of the Sarbanes-Oxley Act that you could adopt to help persuade the banker...
-
Review the opening feature of this chapter that highlights Todd Graves and his company Raising Canes Chicken Fingers. Required: 1. List the seven principles of internal control and explain how Graves...
-
USE EXCEL TO ANSWER: Given the following financial statement info for Cracker Barrel. Calculate the common size value of selling, general and administrative expenses for FY2019. (Enter percentages as...
-
Explain why it is not wise to accept a null hypothesis.
-
Jason works at a new software development company. The company has been in existence for only two years. Since the company is new, everybody is working extra hours and spending all of their time...
-
Peter Jones, a senior accountant, and Mary Miller, a junior accountant, were the only accountants for XYZ Company, a medium-size business. Peter had been with the company for over four years and was...
-
What are the three different categories into which disclosure frauds can be categorized?
-
The following information for Great Oaks Furniture, a retail furniture and design firm, relates to Exercises 6 through 11. Cash flow from mmm operations was \($645,590\) for 2008 and \($795,823\) for...
-
Comparative income statements for Cramer Carpets, a carpet retailer, are given below: The president is concerned that net income is down in 2009 even though sales have increased during the year. The...
-
Safeway, Inc. is a large food and drug retailer with more than 1,700 stores in the U.S. and Canada. The following financial information relates to fiscal 2009 and 2008. Required Calculate inventory...
Study smarter with the SolutionInn App