This project must be completed using an Oracle database with all statements manually typed into an SQL file and run as a batch in Oracle. The output must be written to a text (.txt) file.
Project 2 Relational Schema: see image.
I. A) Based on the 3NF relational schema from Project 1 (shown above), analyze the tables, their relationships, and the sample data you were given in Project 1. Create the tables shown, using the best data types based on your analysis. Follow each table name with an underscore and your first, middle, and last initials (if you do not have a middle initial, use X). For example, if your name is Alice B Chandler, you would name the Customer table Customer_abc. Also, keep in mind that foreign key relationships require the same data types on both sides (e.g., if you declare PatientID as NUMBER(5) in the Patient table, it must be declared as NUMBER(5) in the Rental table, too). All CREATE table statements and their resulting output (e.g., "Table created") must be included in your output file.
B) Execute a DESCRIBE statement for each of the tables. All DESCRIBE statements and their resulting output (i.e., the table structure) must be included in your output file).
II. A) Insert the sample data from Project 1 into each table. Execute a COMMIT statement to permanently save your changes. All INSERT statements and COMMIT statements, along with their resulting output (e.g., "Row inserted") must be included in your output file.
B) Execute a SELECT statement on each table to list all contents (all columns and all rows). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your output file.
III. Execute the transactions below to modify/add to the data entered in the previous step. Execute a COMMIT statement to permanently save your changes. All INSERT, UPDATE, and COMMIT statements, along with their resulting output (e.g., "Row inserted") must be included in your output file.
Patient table
Change the phone number of Patient 101 to '2145551234'
Add Patient 120 (Amanda Green, no phone number)
ApptStatus table
Add a new status:
ApptStatus ApptStatusDesc
X Cancelled
Appt table
Change the appointment time for Appt 110 to 11:30.
Change the appointment status for Appt 108 to Cancelled
ApptDetail table
Add the following:
ApptIDApptReasonCodeBlockCode
108 NP L1
IV. Execute a SELECT statement on each table to list all contents (all columns and all rows), sorted in ascending order by its primary key (in the ApptDetail table, sort by PatientID first, then by ApptReasonCode). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your output file.