PART A Logical and Physical Database Design Read Zealandia Driving School Case Study given in the Appendix A. There are three steps (Step 1 – Step 3) to be completed for Part A. Part A: Step 1 (25 marks) Create a logical (relational) data model (e.g. Database or ER diagram). Use J Developer or Visio to create this diagram. Use appropriate naming conventions while naming tables, attributes, relationships, etc. Also use appropriate data-types, data-sizes, etc. in the diagram and ensure to flag PKs, FKs, etc. There should be two business rules for each relationship (association) shown on diagram. State these business rules very clearly under a separate section (in your report). Similarly, state your reasonable assumptions (this helps us better evaluate your design) under a separate section. Examine each table’s (entity’s) attributes for dependencies and apply the rules of normalisation discussed in Chapter 6 of the textbook. You are required to show the detailed normalisation process {e.g. how you move from lower normal form (e.g. 1NF) to the next (e.g. 2NF)} in the submitted work. Use dependency diagrams for depicting all the relationships among a table’s attributes. Normalise tables up to the highest possible NF. Part A: Step 2 (9 marks) Generate physical model based on the logical model created in the Step 1. To create the required set of tables, relationships, constraints, etc. write SQL scripts using a text editor (e.g. Notepad). Use SQL Developer for initial checking / debugging and running of the code. Keep improving your code until you are happy with the design. Check carefully the DDL code in your script file and make sure that all the attribute definitions (e.g. names, data types, data sizes, domain values including defaults, etc.), table constraints (entity integrity, referential integrity, etc.), etc. are specified correctly. 158337, S1, 2017 (Internal) Page 2 of 6 Project Instructions (Part A) Validate your work by ensuring that query transactions {(a) – (p) listed towards the end of the case study} are supported by your design. Make sure that the physical design is a robust one and meets the requirements of a good design and remember normalisation alone does not produce a good design. In the end, ensure that both logical and physical designs are consistent with each other. Run your final scripts to generate the database (i.e. physical model). Use your Group Oracle a/c to implement the final code. Part A: Step 3 (6 marks) Now create appropriate test data to populate the tables you created in Step2. You can use SQL Developer to run a script file that contains SQL INSERT statements or directly enter data. There should be reasonably sufficient number of rows of test data across all the tables but no table should contain less than five rows. Make sure your test data appropriately and sufficiently reflects (this could sometimes mean adding more than 5 rows in some tables) and does not violate any of the constraints declared while creating the database. Set up all the tables and their constraints in the correct order before you load any data into the database. Note: In general, apply good naming conventions which are also self-explanatory (e.g. name create table script file as createTables.sql, annotate your code with appropriate comments to enhance readability, etc.). Assignment Submission Organise your final report to include complete requirements of the assignment. Make sure the report contents are also in the order of the laid requirements. There are three parts to this assignment submission, a physical printed report and two electronic resources – database and scripts. Put all the files used as solution to Part A into a “single” zip file and submit (one submission per group only) via the Stream Assignment (Part A) link. Checklist for physical report submission 1. A logical relational model (database or ER diagram) that is laid out clearly and legibly for grading. The text font size should not be very small (otherwise risk losing marks)*. The diagram should show all the entities, attributes (with appropriate data types), primary keys, foreign keys and relationship details (appropriate association names, cardinality, strength (identifying-non-identifying), participation (mandatory or optional)), etc. Make sure you have applied normalisation process to arrive at this model. 2. A list of reasonable assumptions. In your report, include a discussion of reasonable assumptions you made about the ambiguous aspects of the case study. We anticipate that each group’s design will differ** from those of other groups based on the underlying assumptions. 158337, S1, 2017 (Internal) Page 3 of 6 Project Instructions (Part A) 3. A list of business rules for each relationship on your diagram (clearly state two unambiguous rules for each relationship). 4. Readable printout of the SQL command file used to create your Oracle tables which include declarative constraints in the CREATE TABLE commands. 5. A formatted, readable printout of the contents of the tables. Also include printout of the script file you used to populate (INSERT commands) the tables. Checklist for submission under your Oracle a/c Physical design to Part A problem i.e. all the tables with test data must be available via your Oracle Group a/c. Checklist for Stream submission Database scripts for Part A should be uploaded in Stream as a single zip file under the Assignment link. Include your both IDs in the name of your zipped file. Check your project work and make sure that all scripts run without any errors. Check that appropriate names have been given to all files. Note: Enter your names, student id numbers on the project marking sheet (Appendix B). Submit the completed project report with a marking sheet as a well bound document. Make sure to provide the (Group) Oracle username in your report. Submit the script files as a single .zip file to the Stream assignment link. Include your physical database design along with data (Part A) under your Oracle a/c. Plagiarism and other project guidelines: * If print of your diagram is too small then ZERO mark would be awarded. Make sure that the layout of your diagram is good (e.g. avoid many criss-crosses, etc.) and the font size of the entities, attributes etc. is at least Arial 11 (or similar size if using a different font family, style). Make your diagram easy to read for the grader. You might want to print on multiple (A4) pages and glue these together or use bigger sized paper (A3). **A ZERO mark will be given to ALL the collaborating parties (no discussion on who did the original work and who copied). Any partial copying will also be awarded straight ZERO. Make sure all your work is complete. Graders will not be chasing you for the parts missed in your submission. Not adhering to the assignment requirements (e.g. no hard copy) will also be given straight ZERO. Note: Turnaround time for assignment could be up to three weeks. 158337, S1, 2017 (Internal) Page 4 of 6 Project Instructions (Part A) Appendix A Zealandia Driving School – Case Study Company Overview Zealandia Driving School was set up in Auckland city in the year 2010. The school has since grown steadily and now has offices in most suburbs of Auckland. As the school is growing, more and more administrative staff is being employed to cope with the increasing amount of paperwork. There are additional plans to open offices in other NZ cities (Wellington, Christchurch, etc.). However, the communication and sharing of information between offices is getting poor. The Managing Director of the school, Allan Davies, feels that too many mistakes are being made and that the success of the school will be short-lived if something is not done to remedy the current situation. He knows that a database could help in part to solve the problem and has approached you to create a database system to support the running of the driving school. The Managing Director has provided the following brief description of how the Zealandia Driving School operates. Data Requirements Each office has a Manager (usually also 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 asked to attend an interview with an instructor to assess their needs and to ensure that they hold a valid (Learner or Restricted) driving licence. Clients are free to book with a particular driving instructor or to request a change in instructor throughout the learning period. After interview, clients book their first lesson. Clients can opt to either book an individual lesson or block of lessons (e.g. 5 or 10) at a reduced fee. A 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 7 am and end as late as 8 pm. After each lesson, the instructor records the progress made by the client and also notes the kilometres used during the lesson. Driving school has a pool of cars, which are adapted for teaching purposes. Each instructor is allocated a particular car. Instructors can also use the car for personal use (part of perks and free advertisement for the school). For safety, the cars are inspected at regular intervals for any potential faults. Once a client is ready (after getting a signal from instructor), they apply for the driving test (e.g. Restricted, Full) to be held at a particular time and date at a local government testing centre. Instructor ensures the client is best prepared for the test and should be available to drop off and pick up the client before and after the test from the testing centre. After the test is completed, the client returns to the school and 158337, S1, 2017 (Internal) Page 5 of 6 Project Instructions (Part A) the test results are recorded. Instructor records the test outcome and reasons in the case of failure. Query Transactions The Managing Director has provided some examples of typical queries that the database system must support. However, you are not required to implement these (i.e. do not write the SQL queries for this assignment) but use these to validate (i.e. your design can support these potential queries) your database design. a) The city wise full addresses of all offices in New Zealand. b) The position-wise, names and phone numbers (work, home, etc.) of all the employees. c) The names of all female instructors based in the Albany office of Auckland city. d) The total number of clients registered at each office. e) The total number of staff at each office. f) The employee numbers and names of staff who are instructors and over 58 years old. g) The names of all clients who passed a driving test (e.g. Restricted or Full) in Feb, 2016. h) The names of all clients who have taken a driving test (e.g. Full) more than three times and have not yet passed. i) The average number of Kilometres driven during a lesson. j) The number of administrative staff working at each office. k) The names and telephone numbers of the Managers of each office. l) The timetable of instructor appointments for the coming week. m) The total number of male and female clients in each city (past and present). n) The details of interviews conducted by a particular instructor. o) The registration numbers of the cars used by Instructors in Albany office. p) The registration number of cars that have had no faults found during a particular period. The above case study is based on a fictitious company and is an adaptation from Thomas and Carolyn book on Database Systems. 158337, S1, 2017 (Internal) Page 6 of 6 Project Instructions (Part A)
The post A Logical and Physical Database Design Read Zealandia Driving School Case Study given in the Appendix A. There are three steps (Step 1 – Step 3) to be completed for Part A.A Logical and Physical Database Design Read Zealandia Driving School Case Study given in the Appendix A. There are three steps (Step 1 – Step 3) to be completed for Part A. appeared first on Homework Aider.
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐
"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."
"Order a custom Paper on Similar Assignment at essayfount.com! No Plagiarism! Enjoy 20% Discount!"
