Transcript DT FMA Review

DT FMA Planning Guide
Task
• For your FMA you are required to
implement the database that you designed
for your TMA
– Create the appropriate tables
– Import sample data
– Create and run queries to retrieve specified
information
– Extend the database to store data for
instructors and schools
– Write a report
Database
• A database named yourusernamefma has
been created for you to use for this
assignment
• Check that you can access this database
– Inform your tutor if you have problems with
this
• This database should only contain tables
relating to this assignment
• You should not modify it once you have
submitted your assignment
Steps
1.
Read the assignment brief carefully and ensure that
you understand what is required for the FMA
Review feedback for the design you submitted for your
TMA and modify as necessary
Make a plan
2.
3.
•
4.
5.
6.
7.
8.
9.
Estimate how long each of the following steps will take you to
complete
Create the tables and import the data
Create and run the specified queries
Design the school and instructor tables
Implement these tables in your database
Write your report
Submit all deliverables via Blackboard by the FMA
deadline
Entity-relationship diagram
Parent/carer
Child
ChildActivity
Activity
Tables
You need the following tables and columns.
Define primary and foreign keys as required
Ensure you use an appropriate data type for each column
Parent
(id, title, firstname, surname, address1, address2, postcode,
phone)
Child
(id, firstname, surname, gender,date-of-birth, parent_id)
Activity
(id, activity_name, day)
ChildActivity
(child_id, activity_id)
Table Creation
• Write and save a CREATE TABLE statement for
each table
• Ensure that you have included columns
appropriate to the sample data you have been
given
• Define foreign keys
• Use InnoDB engine for all tables
• Include foreign key and engine definition in
CREATE TABLE statement
• Think carefully about the order in which you
need to create the tables
Data Import
• Data for all tables is included in a single
spreadsheet table
• Cannot be directly imported into the
database tables
• Create .csv files and import into tables
• Think carefully about the order in which
you need to insert the data
Query 1
A list of children registered for a specified activity together with
details of their carer.
This should show the activity name, first name and surname for each
child (shown in a single column) registered for that activity together with
the title, first name and surname of their parent/carer (again in a single
column) and phone number of the parent/carer. Output should be
sorted alphabetically by child surname, firstname.
Rather than having separate queries for each activity, the play-scheme manager requires
a single query which can be modified by the user when running the query from the
Query Browser. The output from this query will then be exported to a spreadsheet to
provide a register for the activity instructor(s).
To meet this requirement you should write a query that will produce a listing of children
registered for Art and include a comment instructing a user how to modify the query to
produce a similar listing for one of the other activities.
Query 2
A count of the number of children of each
gender registered for each activity.
• This should show the activity name,
gender and count for each activity.
• Output should be sorted alphabetically by
activity name.
Query 3
A list showing the name and age (in years) of each
child on the day that the query is run.
The list should be sorted in descending order of age (i.e.
oldest children first) with children of the same age listed
alphabetically by surname, first name.
Once you have run the query check the output carefully to
ensure that each child’s age has been calculated correctly.
Hint: This query is not as straightforward as you might
think at first. You might need to search the Internet for a
solution.
(Don’t forget to include your sources in the Reference
section of your report.)
Query 4
• The amount payable by each parent/carer.
The playscheme is implementing a charge of
£1.50 per activity for each child (e.g. the cost will
be £3.00 for a child registered for two activities).
The manager wishes to calculate the total cost
payable by each parent/carer (e.g. for a
parent/carer with two children registered with
one child registered for one activity and the
other registered for two the total payable would
be £4.50).
Extend the database to store data
for schools and instructors
Schools
• Store name, address and phone number for each school
• Record which school each child attends
• What is the relationship between schools and children?
Instructors
• Store name, phone number and date of last CRB check
• Record which instructors are currently running each
activity
• Instructors may run more than one activity
• Activities may require more than one instructor
• Some instructors may not currently be running any
activities
• What is the relationship between instructors and
activities?
Report
Include the following sections:
• Introduction
• Entity-relationship diagram
• Database relationships
• Data entry
• Data validation
• Calculation of age from date of birth
• Conclusion
• Appendix
Pay attention to presentation - layout, formatting, grammar and
spelling!
Word count: Max 2,000 words
Word .doc or .docx or .pdf format
Deliverables
1. A file for each of your queries
•
Name each query appropriately so the examiner
can see which requirement it relates to
2. Your Report
•
•
•
.doc, .docx or .pdf
yourusername_dtfma_report.doc (or .docx, .pdf
Submit all deliverables as a single zip file
•
•
yourusername_dtfma.zip
Must be a Winzip .zip file. Other compressed file
formats (e.g. .rar are not acceptable)