On successful completion of the laboratory work, student should be able to: 1. Appreciate the importance...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
On successful completion of the laboratory work, student should be able to: 1. Appreciate the importance of conceptual modelling and apply the entity-relationship modelling approach to a realistic scenario. 2. Understand and apply the essential concepts of relational databases, (including keys, normalization and referential integrity) to sample data sets. 3. Given a business scenario, demonstrate the ability to apply best practice in the development of an appropriate and effective database system. 4. Identify and effectively apply appropriate activities involved in database administration. 5. Appreciate the significance of security in relation to Database Management Systems. COURSEWORK 1 [50 marks] The Global Motoring and Driving School was established in a makeshift kiosk at Lapaz in 2007. The School has since then grown steadily and now has several plush offices in most of the major towns in Accra. The School is now so large that the communication and sharing of information between offices is poor, hence more administrative staff are being employed to cope with the ever- increasing amount of paperwork. The Director of the School, Dr. Kofi Nkansah, feels that too many mistakes are being made and that the success of the School will be short-lived if nothing is done to remedy the situation. Understanding that a database could help in part solve the problem of increased paperwork; he has approached you to draw up the logical design of his intended database to support the running of the Global Motoring and Driving School. To help understand the workings of the school, Dr. Kofi Nkansah has provided you with the following brief description of how the Global Motoring and Driving School operates. Each office has a Manager [who tends to also be a Senior Instructor], several Senior Instructors, Instructors, and administrative staff. The Manager is responsible for the day- to-day running of the office. Clients must first register at an office and this requires that they complete an application form, which records their personal details. Before the first lesson, a client is requested to attend an interview with an Instructor to assess the needs of the client and to ensure that the client holds a valid provisional driving license. A client is free to ask for a particular Instructor or to request that an Instructor to be changed at any stage throughout the process of learning to drive. After the interview, the first lesson is booked. A client may request individual lessons or book a block of lessons for a reduced fee. An individual lesson is for one hour, which begins and ends at the office. A lesson is with a particular Instructor in a particular car at a given time. Lessons can start as early as 8am and as late as 6:30pm. After each lesson, the Instructor records the progress made by the client and notes the mileage used during the lesson. The School has a pool of cars, which are adapted for the purposes of teaching. Each Instructor is allocated to a particular car. As well as teaching, the Instructors are free to use the cars for personal use. The cars are inspected at regular intervals for faults. Once ready, a client applies for a driving test date. To obtain a full driving license the client must pass both the MTN LTE Û Done Lab Work For the Database... use. The cars are inspected at regular intervals for faults. Once ready, a client applies for a driving test date. To obtain a full driving license the client must pass both the practical and theoretical parts of the test. It is the responsibility of the Instructor to ensure that the client is best prepared for all parts of the test. The Instructor is not responsible for testing the client and is not in the car during the test but should be available to drop off and pick up the client before and after the test at the DVLA Testing Centre. If a client fails to pass, the Instructor must record the reasons for the failure. REQUIREMENTS: (i) Using an appropriate CASE tool (e.g. Erwin data modeler, Microsoft Visio or ER Assistant) create an ERD using crow's foot notation for The Global Motoring and Driving School specifying the key attributes of each entity, and the structural constraints on each association. (You may note any unspecified requirements from the scenario, and make appropriate assumptions to make the specification complete). [30 marks] (ii) Describe the entity integrity & referential integrity rules in your data model. [10 marks] 6:32 AM (iii) At a car garage, a customer can place an order for a car in three modes, via telephone, web page or in person. Before an order can be accepted and processed, sales staff must verify all information to be accurate. Based on the scenario, the necessary data requirement has been organized below. Select a primary key and translate the data below to the third normal form explaining each form. [10 marks] carMakeModel YearNote) Order (orderNumber, orderDate, customerName, orderCreationMethodName, verifyingEmployeeName, totalSalesAmount, carSerialNumber, carColourNames, 2A.[10 marks] (i) @95% (ii) (iv) COURSEWORK 2 [50 marks] Page | 2 Using an SQL data definition language, write a query to transform all entities in your ERD in coursework 1 into relational database tables. Create a view that lists students with their corresponding department names. Modify client class and add a new attribute JOB. Modify staff class, add a new attribute salary. Specify a constraint on staff salary to ensure it is always positive 3 MTN LTE Done Lab Work For the Database... (i) 2B. [30 marks] Implement a database application for the driving school using MySQL RDBMS and answer the following queries. (ii) 6:32 AM (iii) (iv) (v) (vi) (vii) (viii) @ 95% Create a Cartesian product by using two tables. How many turples are in individual tables and how many turples are in the result set respectively How many staff is hired currently and what is their average salary. What is the smallest and biggest monthly salary? How much must be paid each month for the salaries? List staff with the vehicles they inspect or vehicles they manage under their offices. List all client that took driving test in Accra center but came from Tema Generate a table that lists all vehicles that has been assigned to a lesson and all clients who have enrolled for that lesson. Create a readable view that contains all data. Select the first 10 toyota avensis vehicles for inspection. Select all clients who have their first name as the first name of the staff who interviewed them. 2C. [10 marks] To assure the effective administration of your implemented database, discuss how you propose to manage the following database administration activities: (i) End-user support (ii) (iii) Data security, privacy and integrity Data backup and recovery Page | 3 DELIVERABLE: Coursework 2b, must be shown to the Teaching Assistant for validation of your queries and reports. NOTE: Copying (full/part) from any other source without acknowledgement or outsourcing this work is strictly prohibited and any such act shall result in an award of a 0 (zero) mark for the project. On successful completion of the laboratory work, student should be able to: 1. Appreciate the importance of conceptual modelling and apply the entity-relationship modelling approach to a realistic scenario. 2. Understand and apply the essential concepts of relational databases, (including keys, normalization and referential integrity) to sample data sets. 3. Given a business scenario, demonstrate the ability to apply best practice in the development of an appropriate and effective database system. 4. Identify and effectively apply appropriate activities involved in database administration. 5. Appreciate the significance of security in relation to Database Management Systems. COURSEWORK 1 [50 marks] The Global Motoring and Driving School was established in a makeshift kiosk at Lapaz in 2007. The School has since then grown steadily and now has several plush offices in most of the major towns in Accra. The School is now so large that the communication and sharing of information between offices is poor, hence more administrative staff are being employed to cope with the ever- increasing amount of paperwork. The Director of the School, Dr. Kofi Nkansah, feels that too many mistakes are being made and that the success of the School will be short-lived if nothing is done to remedy the situation. Understanding that a database could help in part solve the problem of increased paperwork; he has approached you to draw up the logical design of his intended database to support the running of the Global Motoring and Driving School. To help understand the workings of the school, Dr. Kofi Nkansah has provided you with the following brief description of how the Global Motoring and Driving School operates. Each office has a Manager [who tends to also be a Senior Instructor], several Senior Instructors, Instructors, and administrative staff. The Manager is responsible for the day- to-day running of the office. Clients must first register at an office and this requires that they complete an application form, which records their personal details. Before the first lesson, a client is requested to attend an interview with an Instructor to assess the needs of the client and to ensure that the client holds a valid provisional driving license. A client is free to ask for a particular Instructor or to request that an Instructor to be changed at any stage throughout the process of learning to drive. After the interview, the first lesson is booked. A client may request individual lessons or book a block of lessons for a reduced fee. An individual lesson is for one hour, which begins and ends at the office. A lesson is with a particular Instructor in a particular car at a given time. Lessons can start as early as 8am and as late as 6:30pm. After each lesson, the Instructor records the progress made by the client and notes the mileage used during the lesson. The School has a pool of cars, which are adapted for the purposes of teaching. Each Instructor is allocated to a particular car. As well as teaching, the Instructors are free to use the cars for personal use. The cars are inspected at regular intervals for faults. Once ready, a client applies for a driving test date. To obtain a full driving license the client must pass both the MTN LTE Û Done Lab Work For the Database... use. The cars are inspected at regular intervals for faults. Once ready, a client applies for a driving test date. To obtain a full driving license the client must pass both the practical and theoretical parts of the test. It is the responsibility of the Instructor to ensure that the client is best prepared for all parts of the test. The Instructor is not responsible for testing the client and is not in the car during the test but should be available to drop off and pick up the client before and after the test at the DVLA Testing Centre. If a client fails to pass, the Instructor must record the reasons for the failure. REQUIREMENTS: (i) Using an appropriate CASE tool (e.g. Erwin data modeler, Microsoft Visio or ER Assistant) create an ERD using crow's foot notation for The Global Motoring and Driving School specifying the key attributes of each entity, and the structural constraints on each association. (You may note any unspecified requirements from the scenario, and make appropriate assumptions to make the specification complete). [30 marks] (ii) Describe the entity integrity & referential integrity rules in your data model. [10 marks] 6:32 AM (iii) At a car garage, a customer can place an order for a car in three modes, via telephone, web page or in person. Before an order can be accepted and processed, sales staff must verify all information to be accurate. Based on the scenario, the necessary data requirement has been organized below. Select a primary key and translate the data below to the third normal form explaining each form. [10 marks] carMakeModel YearNote) Order (orderNumber, orderDate, customerName, orderCreationMethodName, verifyingEmployeeName, totalSalesAmount, carSerialNumber, carColourNames, 2A.[10 marks] (i) @95% (ii) (iv) COURSEWORK 2 [50 marks] Page | 2 Using an SQL data definition language, write a query to transform all entities in your ERD in coursework 1 into relational database tables. Create a view that lists students with their corresponding department names. Modify client class and add a new attribute JOB. Modify staff class, add a new attribute salary. Specify a constraint on staff salary to ensure it is always positive 3 MTN LTE Done Lab Work For the Database... (i) 2B. [30 marks] Implement a database application for the driving school using MySQL RDBMS and answer the following queries. (ii) 6:32 AM (iii) (iv) (v) (vi) (vii) (viii) @ 95% Create a Cartesian product by using two tables. How many turples are in individual tables and how many turples are in the result set respectively How many staff is hired currently and what is their average salary. What is the smallest and biggest monthly salary? How much must be paid each month for the salaries? List staff with the vehicles they inspect or vehicles they manage under their offices. List all client that took driving test in Accra center but came from Tema Generate a table that lists all vehicles that has been assigned to a lesson and all clients who have enrolled for that lesson. Create a readable view that contains all data. Select the first 10 toyota avensis vehicles for inspection. Select all clients who have their first name as the first name of the staff who interviewed them. 2C. [10 marks] To assure the effective administration of your implemented database, discuss how you propose to manage the following database administration activities: (i) End-user support (ii) (iii) Data security, privacy and integrity Data backup and recovery Page | 3 DELIVERABLE: Coursework 2b, must be shown to the Teaching Assistant for validation of your queries and reports. NOTE: Copying (full/part) from any other source without acknowledgement or outsourcing this work is strictly prohibited and any such act shall result in an award of a 0 (zero) mark for the project.
Expert Answer:
Answer rating: 100% (QA)
Solution 1 2 3 4 ER Model Case Studies 1 Suppose you are given the following requirements for a simp... View the full answer
Related Book For
Applying International Financial Reporting Standards
ISBN: 978-0730302124
3rd edition
Authors: Keith Alfredson, Ken Leo, Ruth Picker, Paul Pacter, Jennie Radford Victoria Wise
Posted Date:
Students also viewed these databases questions
-
Coursework 1 The Global Motoring and Driving School was established in a makeshift kiosk at Lapaz in 2007. The School has since then grown steadily and now has several plush offices in most of the...
-
The Global Motoring and Driving School was established in a makeshift kiosk at Lapaz in 2007. The School has since then grown steadily and now has several plush offices in most of the major towns in...
-
Danny just accepted a job offer as a full-time junior analyst and he works 40 hours per week and works 52 weeks per year. His annual salary is $45,000 per year Calculate the CPP, EI and Income tax...
-
Express the following directions by two other means; set up and fill in a table with three columns, one for bearing, one for azimuthN (AzimN), and one for azimuths (Azims): a. N 3040'E b. AzimN...
-
On January 3. 2012, Allen Corporation and Barkely Corporation invested $5 million LO 2 each in cash to form Albar Enterprises, a joint venture that develops new products benefitting both...
-
For a large Internet service provider (ISP), web virus attacks occur at a mean rate of 150 per day. (a) Estimate the probability of at least 175 attacks in a given day. (b) Estimate the probability...
-
The owner of an ice cream store asks 75 people which flavor of ice cream they prefer. Thirteen of them say strawberry, 11 say chocolate, 24 say vanilla, and 27 provide a flavor other than strawberry,...
-
Ashley runs a small business in Boulder, Colorado, that makes snow skis. She expects the business to grow substantially over the next three years. Because she is concerned about product liability and...
-
Besides the effect on the tax rates that will apply, filing status also has an impact on the amount of the standard deduction that is allowed. A taxpayer who is unmarried or separated from their...
-
Tax Software Assignment - Fall 2023 Ms. Jeanette Letourneau (SIN 123-456-789) was born in Montreal on December 15, 1979. She has spent most of her working life a song writer but also has a part-time...
-
1) Which ISDN channel carries signaling information? 2) What device must be installed on a DSL network to protect the sound quality of phone calls? 3) What type of network combines fiber with coax?...
-
Indicate whether each of the following statements is true or false by writing T or F in the answer c olumn. There is no distinction between lost and mislaid personal property.
-
An agency coupled with an interest. a. agent b. agency by necessity c. agency by ratifi cation d. apparent a uthority e. attorney in fact f. express a uthority g. contract of agency h. disclosed pr...
-
A principal whose existence and identity are not known to third parties. a. agent b. agency by necessity c. agency by ratifi cation d. apparent a uthority e. attorney in fact f. express a uthority g....
-
The agency that results when a person fails to support his or her spouse or minor children. a. agent b. agency by necessity c. agency by ratifi cation d. apparent a uthority e. attorney in fact f....
-
A party to a contract who transfers his or her rights to a third party. a. assignee b. assignment c. assignor d. bankrupt e. delegation f. guarantor g. incidental benefi ciary h. novation i....
-
Find the inverse function of f informally. f(x) = x + 2, x 0 f-1(x) = x-2 Verify that f(f1(x)) = x. f(f-1(x)) = 2 +2 = X Verify that f(f(x)) = x and f (f(x)) = x. 1(f(x)) = f1 ( = X - 2
-
Chloroplasts are illuminated until the levels of the Calvin cycle intermediates reach a steady state. The light is then turned off. How does the level of RuBP vary after this point?
-
What differences occur between asset-by-asset or class of asset bases in accounting for revaluation increases and decreases?
-
Which of the following is included in determining the fair value of a biological asset that does not have an active market and which has a 5 year production cycle? 1. Revenue from sale in 5 years'...
-
Where are the earnings per share figures for a parent entity presented?
-
What minimum information must be extracted from a video clip of a moving object in order to quantify the object's motion?
-
Figure P2.4 shows a graph of position versus frame number from a video clip of a moving object. Describe this motion from beginning to end, and state any assumptions you make. Data from Figure P2.4...
-
The sequence in Figure P2.2 represents a ball rolling into a wall and bouncing off of it. The ball is \(10 \mathrm{~mm}\) in diameter. Make a graph showing the distance from the leading edge of the...
Study smarter with the SolutionInn App