Close Button
Back All services Close Button


Amount $ 25

cart 0

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 mar

Question Preview:

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 sho...

View complete question »

Question Preview:

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.
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
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
c) The names of all female instructors based in the Albany office of Auckland
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)

View less »

Solution preview

The database contains 8 tables namely, Branch, Staff, Client, CarAllocation, ClientInterview, CarInspection, DrivingTest and ClientLessonSchedule. Relationship exist between the tables such that the data in one table is referenced in another table.

Get solution

java assignment help

© Livewebtutors. All Rights Reserved 2018

Rated 4.9/5 based on 2480 reviews
Arrow up