Use Oracle Forms to create a database application. This application is based on the following table...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Use Oracle Forms to create a database application. This application is based on the following table types, with primary keys underlined and foreign keys in italics: Student (sld, surname, forenames, address, mobile Phone, cCode, cYear, dob) Sessions (sCode, sType, mCode, tutor, SDT, EDT, Room) Attendance (aNo, sld, sCode, CSDT) Student sid surname forenames address mobilePhone cCode cYear dob Sessions sCode SType mCode tutor SDT EDT Room Attendance aNo sld sCode CSDT 17 char maximum (e.g. Johnson B) 15 char maximum 15 char maximum 50 char maximum 11 digit number (e.g. 07654321234) 7 char (e.g. COMP101) 1 digit number (range 1,2,3,4,5) date number (3 digits) Lecture, Lab, Tutorial, Seminar 6 char maximum (e.g. CO4703) 20 char maximum date date 10 char maximum (e.g. CM210) number (5 digits) 17 char maximum number (3 digits) date (student ID) (course code) (course year) (date of birth) (session code) (session type) (module code) (tutor name) (start date and time) (end date and time) (date and time of card scanning) You are expected to create these tables using SQL and then populate them with appropriate and sufficient test data of your own (hint: use varchar2 for sld and also for all variable length character strings). To gain a pass mark for Task 1 you will achieve basic functionality for forms A, B and C as outlined below. To gain higher marks you will create a more user-friendly interface (e.g. good screen layout, appropriate use of colours, use of buttons, clear messages), and will achieve the functionality as italicised for each form. Form A-Create/Amend a student (20 marks) 1. Enter details; all alphabets should be in upper case automatically 2. Save a new record 3. Year should be 1 to 5 only (inclusive) 4. Date of birth (they should be on or past their 18th birthday) 5. Buttons that perform functions (SAVE, CLEAR, DELETE etc.) 6. A list of values for the course code 7. Any other functionality you deem to be appropriate Form B-Display a student's attendance details (20 marks) A two block form showing full details of a student and all the sessions she/he has attended. 1. Enter a sld and the basic attendance details are displayed 2. This form is read only and updating should be disallowed 3. sld is selected from a list of values 4. For each session attendance the session type, module code and tutor should be displayed 5. Display the total number of sessions attended by the student 6. Addition of functional buttons 7. Any other functionality that you deem to be appropriate Form C-Menu (10 Marks) 1. Two buttons which call up Forms A and B 2. When Forms A and B are exited, control should pass back to the menu 3. A password is required for form A (but not B). Form A should be accessed only if the correct password is entered. 4. Any other functionality that you deem to be appropriate. For Task 1, you should submit relevant and detailed documentation in a word-processed file demonstrating the design and implementation of the above three forms. The documentation should have the following: you may include additional items that you deem to be appropriate. 1. SQL table creation and insertion statements for all tables. 2. For each of the functions listed above under Form A, B and C, explain how the function is implemented. Show and explain trigger codes and other techniques (list of values, alert etc.) wherever appropriate. 3. Include screenshots to illustrate your explanation wherever appropriate. The screenshots should show the date and time when they are taken, which are normally displayed in the Windows taskbar. Because Task 1 is assessed through this documentation only, anything that is not included in it will not be given marks. Task 2 (50 marks) You should submit one single word-processed file including the previous Task 1 and the current Task 2. (a) Design a relational database for the table fragment shown below. You should assume that the data shown in the table is a typical snapshot of data for these records and demonstrates all the enterprise rules which apply, even though some rules may be simpler than those in reality. Normalise this table into Third Normal Form showing the intermediary stages of First and Second Normal Form. Primary keys and foreign keys in all the three forms should be indicated clearly. You are expected to start the normalisation process by first choosing the booking number alone as a primary key. Your normalised tables should only include the attributes shown in the table fragment; do not add any new attribute. Justify your solution to this problem. (30 marks) (b) Critically evaluate normalisation as a technique for database design. For each advantage or disadvantage identified, you should use the A2Z data to illustrate and explain it. You may extend these data in order to identify suitable examples to illustrate your point, however you should only do so if the current data do not provide for such examples. The evaluation should not exceed 500 words (excluding the list of references). Booking number date customer customer code location B2462735 12/09/20 C25 B2675766 13/06/20 C27 B2768769 01/01/21 C34 B2768769 01/01/21 C34 82818194 10/03/21 C25 B2110898 05/02/20 C36 B2709156 14/02/21 C41 B2053788 14/02/21 C21 B2888485 11/01/21 C21 B2456277 11/04/20 C27 B2757350 12/09/20 C34 82573577 10/10/20 C29 Sheffield Liverpool Lancaster Lancaster Sheffield Preston Sheffield Liverpool Liverpool Liverpool Lancaster Manchester Note: Each booking is assigned a unique booking number. A2Z Ltd. Lorry registration No V226 PRS V235 PRS V234 PRS V229 PRS V258 STV V278 PRS V234 PRS V228 PRS V278 PRS V234 PRS V229 PRS V258 STV type capacity licenseNo mobile PhoneNo (tonnes) Open Deck Panel Panel 20 Open Deck Tipper Refrigerated 10 Panel 1 Open Deck Refrigerated 10 Panel Open Deck Tipper 10 1 5 10 1 1 5 Driver 20 D93470 29403794 D24356 93497349 D25367 91345267 D25345 82037921 D29301 None D25367 91345267 D25367 91345267 D93470 29403794 D29301 None D25367 91345267 D25367 91345267 D29301 None Learning Outcomes being assessed: 1. Implement a functional relational database, giving consideration to integrity and validation constraints, using appropriate database environment. 2. Apply and justify database design techniques for the construction of a conceptual data model. 3. Critically review work with respect to underpinning theories. Use Oracle Forms to create a database application. This application is based on the following table types, with primary keys underlined and foreign keys in italics: Student (sld, surname, forenames, address, mobile Phone, cCode, cYear, dob) Sessions (sCode, sType, mCode, tutor, SDT, EDT, Room) Attendance (aNo, sld, sCode, CSDT) Student sid surname forenames address mobilePhone cCode cYear dob Sessions sCode SType mCode tutor SDT EDT Room Attendance aNo sld sCode CSDT 17 char maximum (e.g. Johnson B) 15 char maximum 15 char maximum 50 char maximum 11 digit number (e.g. 07654321234) 7 char (e.g. COMP101) 1 digit number (range 1,2,3,4,5) date number (3 digits) Lecture, Lab, Tutorial, Seminar 6 char maximum (e.g. CO4703) 20 char maximum date date 10 char maximum (e.g. CM210) number (5 digits) 17 char maximum number (3 digits) date (student ID) (course code) (course year) (date of birth) (session code) (session type) (module code) (tutor name) (start date and time) (end date and time) (date and time of card scanning) You are expected to create these tables using SQL and then populate them with appropriate and sufficient test data of your own (hint: use varchar2 for sld and also for all variable length character strings). To gain a pass mark for Task 1 you will achieve basic functionality for forms A, B and C as outlined below. To gain higher marks you will create a more user-friendly interface (e.g. good screen layout, appropriate use of colours, use of buttons, clear messages), and will achieve the functionality as italicised for each form. Form A-Create/Amend a student (20 marks) 1. Enter details; all alphabets should be in upper case automatically 2. Save a new record 3. Year should be 1 to 5 only (inclusive) 4. Date of birth (they should be on or past their 18th birthday) 5. Buttons that perform functions (SAVE, CLEAR, DELETE etc.) 6. A list of values for the course code 7. Any other functionality you deem to be appropriate Form B-Display a student's attendance details (20 marks) A two block form showing full details of a student and all the sessions she/he has attended. 1. Enter a sld and the basic attendance details are displayed 2. This form is read only and updating should be disallowed 3. sld is selected from a list of values 4. For each session attendance the session type, module code and tutor should be displayed 5. Display the total number of sessions attended by the student 6. Addition of functional buttons 7. Any other functionality that you deem to be appropriate Form C-Menu (10 Marks) 1. Two buttons which call up Forms A and B 2. When Forms A and B are exited, control should pass back to the menu 3. A password is required for form A (but not B). Form A should be accessed only if the correct password is entered. 4. Any other functionality that you deem to be appropriate. For Task 1, you should submit relevant and detailed documentation in a word-processed file demonstrating the design and implementation of the above three forms. The documentation should have the following: you may include additional items that you deem to be appropriate. 1. SQL table creation and insertion statements for all tables. 2. For each of the functions listed above under Form A, B and C, explain how the function is implemented. Show and explain trigger codes and other techniques (list of values, alert etc.) wherever appropriate. 3. Include screenshots to illustrate your explanation wherever appropriate. The screenshots should show the date and time when they are taken, which are normally displayed in the Windows taskbar. Because Task 1 is assessed through this documentation only, anything that is not included in it will not be given marks. Task 2 (50 marks) You should submit one single word-processed file including the previous Task 1 and the current Task 2. (a) Design a relational database for the table fragment shown below. You should assume that the data shown in the table is a typical snapshot of data for these records and demonstrates all the enterprise rules which apply, even though some rules may be simpler than those in reality. Normalise this table into Third Normal Form showing the intermediary stages of First and Second Normal Form. Primary keys and foreign keys in all the three forms should be indicated clearly. You are expected to start the normalisation process by first choosing the booking number alone as a primary key. Your normalised tables should only include the attributes shown in the table fragment; do not add any new attribute. Justify your solution to this problem. (30 marks) (b) Critically evaluate normalisation as a technique for database design. For each advantage or disadvantage identified, you should use the A2Z data to illustrate and explain it. You may extend these data in order to identify suitable examples to illustrate your point, however you should only do so if the current data do not provide for such examples. The evaluation should not exceed 500 words (excluding the list of references). Booking number date customer customer code location B2462735 12/09/20 C25 B2675766 13/06/20 C27 B2768769 01/01/21 C34 B2768769 01/01/21 C34 82818194 10/03/21 C25 B2110898 05/02/20 C36 B2709156 14/02/21 C41 B2053788 14/02/21 C21 B2888485 11/01/21 C21 B2456277 11/04/20 C27 B2757350 12/09/20 C34 82573577 10/10/20 C29 Sheffield Liverpool Lancaster Lancaster Sheffield Preston Sheffield Liverpool Liverpool Liverpool Lancaster Manchester Note: Each booking is assigned a unique booking number. A2Z Ltd. Lorry registration No V226 PRS V235 PRS V234 PRS V229 PRS V258 STV V278 PRS V234 PRS V228 PRS V278 PRS V234 PRS V229 PRS V258 STV type capacity licenseNo mobile PhoneNo (tonnes) Open Deck Panel Panel 20 Open Deck Tipper Refrigerated 10 Panel 1 Open Deck Refrigerated 10 Panel Open Deck Tipper 10 1 5 10 1 1 5 Driver 20 D93470 29403794 D24356 93497349 D25367 91345267 D25345 82037921 D29301 None D25367 91345267 D25367 91345267 D93470 29403794 D29301 None D25367 91345267 D25367 91345267 D29301 None Learning Outcomes being assessed: 1. Implement a functional relational database, giving consideration to integrity and validation constraints, using appropriate database environment. 2. Apply and justify database design techniques for the construction of a conceptual data model. 3. Critically review work with respect to underpinning theories.
Expert Answer:
Related Book For
Accounting Information Systems
ISBN: 978-1133935940
10th edition
Authors: Ulric J. Gelinas, Richard B. Dull
Posted Date:
Students also viewed these databases questions
-
what is the difference between an ordinary commercial bank and expanded commercial bank? explain
-
On 1 January 2018 Daly Sdn Bhd enters into a lease for a photocopier that has a fair value of RM20,000. The lease term is for three years. The photocopier will be returned to the lessor at the end of...
-
A 500 mm diameter closed-end steel pipe pile is to be driven to a depth of 17 m into the following soil profile. Depth (m) 0-2.0 2.0-6.5 6.5 16.0 16.0 17.5 Soil Classification Silty sand Fine to...
-
Explain why the economic order quantity is the intersection of the ordering-cost curve and the carrying-cost curve.
-
Outline some criteria that may be used to select suppliers. Explain how these criteria can also be used to evaluate supplier performance.
-
Figure Q26.6 shows three wires wrapped around an iron core. The number of turns and the directions of the windings in the figure should be care- fully noted. At one particular instant, V A - V B = 20...
-
Prepare a one-hour action pack T.V. episode with the main character a forensic accountant or auditor. The main character should be called Dane Striker or Sloane Striker. Your project can include a...
-
Francisco Company uses the columnar cash journals illustrated in the textbook. In April, the following selected cash transactions occurred. 1. Made a refund to a customer as an allowance for damaged...
-
Gyro Company sells its only product for $71. Variable costs per unit are $28 Total fixed costs are $300,000 The company is currently selling 12,000 units. Determine the operating leverage factor at...
-
Online Security Buddies provides consulting services to small businesses that require computer security but are too small to have their own IT person on staff. The business had the following account...
-
4. Employees: Your design must store basic contact information for each employee, including their full name, home mailing address, phone number, and email address. Besides this data, Jeff also wants...
-
Why is employee engagement an important strategy to foster within an organization? What experience have you had or what examples have you seen or heard about that have helped foster a positive work...
-
(1 point) Angus goes to one of two coffee shops in his home town. He goes to Tarbucks 62% of the time and otherwise goes to Costly Coffee. Either way, he buys a latte 57 % of the time, regardless of...
-
Why would you not expect to use an item's price as its primary key?
-
Why do only 4 5 % of Americans take part in the political process OR VOTE?
-
What is the orientation of the chloro groups in the isomer shown in the image on the left?
-
ABC Inc. is considering a new project whose data are shown below. The equipment would be used for three years with straight - line depreciation, and would have zero salvage value. No change in net...
-
You deposit $10,000 in a savings account that earns 7.5% simple interest per year. What is the minimum number of years you must wait to double your balance? Suppose instead that you deposit the...
-
Discuss why the control matrix is custom-tailored for each business process.
-
The Nile Company is considering taking customers orders on its Web site. a. What information would Nile collect from the customer during this process? b. What information would need to come from...
-
For the case assigned by your instructor, complete the following requirements, using software where flowcharts are required: a. Draw a systems flowchart. b. Prepare a control matrix, including...
-
Listed below are ten separate situations. For each item indicates whether the difference is (1) temporary creating a deferred tax asset or a deferred tax liability or (2) permanent. Item 1. Pension...
-
Fox Corporation purchased a machine on January 1,2020 , that cost \(\$ 40,000\). The machine had an estimated service life of five years and no residual value. Fox uses straight-line depreciation for...
-
Wittco Company reports pretax GAAP income in 2020, its first year of operations, of \(\$ 100,000\). Temporary differences in the GAAP basis and tax basis of assets arose in 2020 from the following...
Study smarter with the SolutionInn App